bitShiftLeft may produce unexpected result. #65516
Description
Describe what's wrong
bitShiftLeft produces unexpected result.
How to reproduce
-
Which ClickHouse server version to use:
24.3.3.102 -
Which interface to use, if it matters:
MySQL Shell -
Non-default settings, if any:
distributed_product_mode='allow' -
init database:
create database ch_main;
use ch_main;
-- sql #1
create table t_hwi130st4 (
c_wpg Int32 ,
c_s String ,
c_i8 String ,
c_seahk2u Float64 ,
c_b Float64 ,
c_w9_4ap Int32 primary key ,
);
-- sql #3
insert into t_hwi130st4 (c_wpg, c_s, c_i8, c_seahk2u, c_b, c_w9_4ap) values
(-1149739257, 'oqo0b9wn0y', 'uwiw', 50.62, 1.9, -56238719),
(2, 'x', 'yi4n6vrr', 76.98, 9223372036854775807.8, -3),
(11686210, 'mlp50m', 'tc17pov2', 4294967294.0, 93.15, -1271189987),
(-1403535781, 'ss75f', 'zf', 3.98, 56.36, 1445090706);
-- sql #21
insert into t_hwi130st4 (c_wpg, c_s, c_i8, c_seahk2u, c_b, c_w9_4ap) values
(-4, 'k3ab', 'vsi9mlt2f', -0.1, 99.68, -838905824),
(21797751, 'yiw', 'e707iszq', 256.5, -65537.5, 1545255954),
(-1173571010, 'u9', 'yh_dsb95d', -4294967295.8, -4294967295.1, -86953426),
(1084934805, 'tod', 'a52o13ea4', 43.49, -4294967294.9, 1338049738);
-- sql #22
insert into t_hwi130st4 (c_wpg, c_s, c_i8, c_seahk2u, c_b, c_w9_4ap) values
(-676988243, 'g', 'fy5bz', 17.61, 96.85, -528458811),
(-25, 'h6mcbk8', 'pp', 97.23, -18446744073709551614.9, 5),
(1671090888, 'v945', 'kw', 44.4, 17.83, -1314342888),
(65536, 'v0zewjo49u', 'askk_1h', 72.48, 81.15, 2);
-- sql #23
alter table t_hwi130st4 add column c_skwq8 String;
-- sql #25
insert into t_hwi130st4 (c_wpg, c_s, c_i8, c_seahk2u, c_b, c_w9_4ap, c_skwq8) values
(-2040837324, 'si8shj', 'b', 41.20, 3.10, 1291544549, 'kx68agni4'),
(-310404151, '__nc8rsl', 'tqosx0s', 32767.6, 29.14, 752873682, 'v971pfad'),
(3, 'rdj', 'p5w', 32769.6, -9223372036854775809.1, -486995628, 'hiqsow'),
(-6, 'gbs780nv', 'u8ojaqgpa', 4.44, 129.0, 1746224505, 't1tzo4w538');
-- sql #26
insert into t_hwi130st4 (c_wpg, c_s, c_i8, c_seahk2u, c_b, c_w9_4ap, c_skwq8) values
(-1580788152, 'e', 'w6i', -9223372036854775809.2, 13.32, 962706574, 'tjl7k5r'),
(3, 'j', 'ix', 88.10, 89.77, -610474587, 'mh_'),
(1897548408, 'jb', 'ddr9kbf', 54.21, 76.36, -5, 'ditpajx'),
(441043873, 'wvi16h', 'r', 92.8, 30.88, 2029856608, 'pb4');
-- sql #27
insert into t_hwi130st4 (c_wpg, c_s, c_i8, c_seahk2u, c_b, c_w9_4ap, c_skwq8) values
(16, 'o6z', 'aujtujgdon', 30.87, 22.11, 259350534, 'ql'),
(1669987147, 'i4n40ete9', 'kv155wtf', 35.47, 68.80, -1976235388, 'rihep_f57'),
(-1000436932, 'kkpa29zk_6', 'ksckscqhr5', 9223372036854775807.9, 75.97, 1706844447, 'fsj'),
(950656369, 'x73x', 'hg938x95r', 70.39, 49.36, -1951482829, 'lbvo843i');
- Queries to run that lead to an unexpected result
SELECT
ref_4.c_w9_4ap,
bitShiftLeft(ref_4.c_w9_4ap, -2122704019) as c3
FROM t_hwi130st4 as ref_4
order by 1,2
;Expected behavior
previous SQL produces:
mysql> SELECT
-> ref_4.c_w9_4ap,
-> bitShiftLeft(ref_4.c_w9_4ap, -2122704019) as c3
-> FROM t_hwi130st4 as ref_4
-> order by 1,2
-> ;
+-------------+------+
| c_w9_4ap | c3 |
+-------------+------+
| -1976235388 | 0 |
| -1951482829 | 0 |
| -1314342888 | 0 |
| -1271189987 | 0 |
| -838905824 | 0 |
| -610474587 | 0 |
| -528458811 | 0 |
| -486995628 | 0 |
| -86953426 | 0 |
| -56238719 | 0 |
| -5 | 0 |
| -3 | 0 |
| 2 | 0 |
| 5 | 0 |
| 259350534 | 0 |
| 752873682 | 0 |
| 962706574 | 0 |
| 1291544549 | 0 |
| 1338049738 | 0 |
| 1445090706 | 0 |
| 1545255954 | 0 |
| 1706844447 | 0 |
| 1746224505 | 0 |
| 2029856608 | 0 |
+-------------+------+
24 rows in set (0.00 sec)
Read 24 rows, 96.00 B in 0.001344 sec., 17857 rows/sec., 69.75 KiB/sec.
When using bitShiftLeft in a simple select statement:
mysql> select bitShiftLeft(cast(2 as Int32), -2122704019), bitShiftLeft(cast(2029856608 as Int32), -2122704019);
+---------------------------------------------+------------------------------------------------------+
| bitShiftLeft(CAST(2, 'Int32'), -2122704019) | bitShiftLeft(CAST(2029856608, 'Int32'), -2122704019) |
+---------------------------------------------+------------------------------------------------------+
| 16384 | -1528037376 |
+---------------------------------------------+------------------------------------------------------+
1 row in set (1.80 sec)
Read 1 rows, 1.00 B in 1.802078 sec., 0 rows/sec., 0.55 B/sec.
The results of two queries do not match.
Additional context
docker compose config:
version: '3.8'
services:
clickhouse-01:
image: "clickhouse/clickhouse-server:${CHVER:-latest}"
user: "101:101"
container_name: clickhouse-01
hostname: clickhouse-01
networks:
cluster_2S_1R:
ipv4_address: 10.0.7.1
volumes:
- ${PWD}/fs/volumes/clickhouse-01/etc/clickhouse-server/config.d/config.xml:/etc/clickhouse-server/config.d/config.xml
- ${PWD}/fs/volumes/clickhouse-01/etc/clickhouse-server/users.d/users.xml:/etc/clickhouse-server/users.d/users.xml
depends_on:
- clickhouse-keeper-01
- clickhouse-keeper-02
- clickhouse-keeper-03
clickhouse-02:
image: "clickhouse/clickhouse-server:${CHVER:-latest}"
user: "101:101"
container_name: clickhouse-02
hostname: clickhouse-02
networks:
cluster_2S_1R:
ipv4_address: 10.0.7.2
volumes:
- ${PWD}/fs/volumes/clickhouse-02/etc/clickhouse-server/config.d/config.xml:/etc/clickhouse-server/config.d/config.xml
- ${PWD}/fs/volumes/clickhouse-02/etc/clickhouse-server/users.d/users.xml:/etc/clickhouse-server/users.d/users.xml
depends_on:
- clickhouse-keeper-01
- clickhouse-keeper-02
- clickhouse-keeper-03
clickhouse-keeper-01:
image: "clickhouse/clickhouse-keeper:${CHKVER:-latest-alpine}"
user: "101:101"
container_name: clickhouse-keeper-01
hostname: clickhouse-keeper-01
networks:
cluster_2S_1R:
ipv4_address: 10.0.7.5
volumes:
- ${PWD}/fs/volumes/clickhouse-keeper-01/etc/clickhouse-keeper/keeper_config.xml:/etc/clickhouse-keeper/keeper_config.xml
clickhouse-keeper-02:
image: "clickhouse/clickhouse-keeper:${CHKVER:-latest-alpine}"
user: "101:101"
container_name: clickhouse-keeper-02
hostname: clickhouse-keeper-02
networks:
cluster_2S_1R:
ipv4_address: 10.0.7.6
volumes:
- ${PWD}/fs/volumes/clickhouse-keeper-02/etc/clickhouse-keeper/keeper_config.xml:/etc/clickhouse-keeper/keeper_config.xml
clickhouse-keeper-03:
image: "clickhouse/clickhouse-keeper:${CHKVER:-latest-alpine}"
user: "101:101"
container_name: clickhouse-keeper-03
hostname: clickhouse-keeper-03
networks:
cluster_2S_1R:
ipv4_address: 10.0.7.7
volumes:
- ${PWD}/fs/volumes/clickhouse-keeper-03/etc/clickhouse-keeper/keeper_config.xml:/etc/clickhouse-keeper/keeper_config.xml
networks:
cluster_2S_1R:
driver: bridge
ipam:
config:
- subnet: 10.0.7.0/24
gateway: 10.0.7.254
config.xml
<clickhouse replace="true">
<logger>
<level>debug</level>
<log>/var/log/clickhouse-server/clickhouse-server.log</log>
<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
<size>1000M</size>
<count>3</count>
</logger>
<display_name>cluster_2S_1R node 1</display_name>
<listen_host>0.0.0.0</listen_host>
<http_port>8123</http_port>
<tcp_port>9000</tcp_port>
<mysql_port>9004</mysql_port>
<postgresql_port>9005</postgresql_port>
<user_directories>
<users_xml>
<path>users.xml</path>
</users_xml>
<local_directory>
<path>/var/lib/clickhouse/access/</path>
</local_directory>
</user_directories>
<distributed_ddl>
<path>/clickhouse/task_queue/ddl</path>
</distributed_ddl>
<remote_servers>
<default>
<shard>
<replica>
<host>clickhouse-01</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>clickhouse-02</host>
<port>9000</port>
</replica>
</shard>
</default>
</remote_servers>
<zookeeper>
<node>
<host>clickhouse-keeper-01</host>
<port>9181</port>
</node>
<node>
<host>clickhouse-keeper-02</host>
<port>9181</port>
</node>
<node>
<host>clickhouse-keeper-03</host>
<port>9181</port>
</node>
</zookeeper>
<macros>
<shard>01</shard>
<replica>01</replica>
</macros>
</clickhouse>users.xml
<?xml version="1.0"?>
<clickhouse replace="true">
<profiles>
<default>
<max_memory_usage>10000000000</max_memory_usage>
<use_uncompressed_cache>0</use_uncompressed_cache>
<load_balancing>in_order</load_balancing>
<log_queries>1</log_queries>
<distributed_product_mode>allow</distributed_product_mode>
</default>
</profiles>
<users>
<default>
<access_management>1</access_management>
<profile>default</profile>
<networks>
<ip>::/0</ip>
</networks>
<password></password>
<quota>default</quota>
<access_management>1</access_management>
<named_collection_control>1</named_collection_control>
<show_named_collections>1</show_named_collections>
<show_named_collections_secrets>1</show_named_collections_secrets>
</default>
</users>
<quotas>
<default>
<interval>
<duration>3600</duration>
<queries>0</queries>
<errors>0</errors>
<result_rows>0</result_rows>
<read_rows>0</read_rows>
<execution_time>0</execution_time>
</interval>
</default>
</quotas>
</clickhouse>about us
We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the potential above-mentioned vulnerability that may lead to database logic error.