ClickHouse / ClickHouse/ClickHouse
When select all columns from distributed table immediately after insertion, the result is incorrect.
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe what's wrong**
When select all columns from distributed table immediately after insertion, the result is incorrect.
**How to reproduce**
* Which ClickHouse server version to use: `24.5.3.5`
* Which interface to use, if it matters: `MySQL Shell`
* Non-default settings, if any: `distributed_product_mode='allow'`
* Queries to run that lead to an unexpected result
```sql
create database ch_main on cluster default;
use ch_main;
-- sql #47
create table __t_elmbvl__ on cluster default (
c_ld7yut2msy Int32 primary key ,
c_rfad4lh7 String ,
c_mt6h0fe Bool ,
c_qqdw7 Bool ,
c_xrtu_99k Float64 ,
c_jo Bool not null ,
);
-- sql #48
create table t_elmbvl__ on cluster default as __t_elmbvl__ ENGINE = Distributed(default, ch_main, __t_elmbvl__, c_ld7yut2msy);
-- sql #55
alter table t_elmbvl__ on cluster default add column c_fjago Int32;
-- sql #56
alter table __t_elmbvl__ on cluster default add column c_fjago Int32;
-- sql #63
insert into t_elmbvl__ (c_ld7yut2msy, c_rfad4lh7, c_mt6h0fe, c_qqdw7, c_xrtu_99k, c_jo, c_fjago) values
(-775545040, 'u0mw', coalesce(false, true), coalesce((NOT NOT(cast( (12509309691728363 <= -22) as Nullable(Bool)))), false), 35.70, coalesce(true, false), -851431940),
(1126263007, 'ccbdd', coalesce(false, false), coalesce((NOT NOT(cast( (65.88 = 1937972318395406327) as Nullable(Bool)))), false), 51.48, coalesce((NOT NOT(cast( (cast(null as Nullable(Decimal)) > cast(null as Nullable(Decimal))) as Nullable(Bool)))), false), 65535),
(34, 'w', coalesce((NOT NOT(cast( (cast(null as Nullable(Int32)) > cast(null as Nullable(UInt8))) as Nullable(Bool)))), false), coalesce((true)
or ((NOT NOT(cast( (2118090269594213976 <= 40.64) as Nullable(Bool))))), true), 72.96, coalesce((NOT NOT(cast( (94.43 > cast(null as Nullable(Decimal))) as Nullable(Bool)))), true), -465198836),
(205931387, 'y632x', coalesce(false, true), coalesce((false)
or ((false)
and ((-152204195 between -360610446 and -945507412))), true), 72.73, coalesce(false, false), 1401197463);
-- sql #73
insert into t_elmbvl__ (c_ld7yut2msy, c_rfad4lh7, c_mt6h0fe, c_qqdw7, c_xrtu_99k, c_jo, c_fjago) values
(2147483647, 'bnuxtpb02n', coalesce((NOT NOT(cast( (5873265620921675005 != 33.23) as Nullable(Bool)))), false), coalesce((1580699122 between 1866394741 and -2006854787), true), 30.3, coalesce((904605091 is NULL), true), -2092186939),
(-2032679981, 'n05', coalesce((NOT NOT(cast( (6351600532512099348 < 256) as Nullable(Bool)))), false), coalesce((-2028540090 is not NULL), false), 10.70, coalesce((NOT NOT(cast( (cast(null as Nullable(Int16)) <= 1213897034) as Nullable(Bool)))), false), 32),
(-1152318580, 'eoqjjlyl4', coalesce(false, true), coalesce((-10281420 between -983787166 and 1378995206), true), -32769.0, coalesce((NOT NOT(cast( (716263129 > -17541) as Nullable(Bool)))), false), -1711766346),
(654967805, 'pgq6wiub0h', coalesce(false, false), coalesce((NOT NOT(cast( (4294967295.5 < 1137119677) as Nullable(Bool)))), false), 67.58, coalesce(false, false), -427315728);
-- sql #75
insert into t_elmbvl__ (c_ld7yut2msy, c_rfad4lh7, c_mt6h0fe, c_qqdw7, c_xrtu_99k, c_jo, c_fjago) values
(-2108699971, 'o', coalesce((NOT NOT(cast( (cast(null as Nullable(Int16)) != 48) as Nullable(Bool)))), false), coalesce(((NOT NOT(cast( (cast(null as Nullable(UInt16)) = 9156229030240177636) as Nullable(Bool)))))
and (((NOT NOT(cast( (7844108568336033621 = cast(null as Nullable(Int8))) as Nullable(Bool)))))
and ((NOT NOT(cast( (cast(null as Nullable(UInt64)) AND -7) as Nullable(Bool)))))), false), -1.6, coalesce((4 between 719103149 and 278694748), false), -1207250535),
(1123238126, 'pm_', coalesce((1816137027 between 1219888914 and -809535320), false), coalesce((-1766449752 is NULL), false), 58.34, coalesce(true, false), 32767),
(-1261308300, 'xk', coalesce((NOT NOT(cast( (76 <= cast(null as Nullable(Int64))) as Nullable(Bool)))), true), coalesce((-1816368542 is not NULL), false), 2147483647.5, coalesce((NOT NOT(cast( (847637622 AND -786837527) as Nullable(Bool)))), true), -906918576),
(-1167758264, 'ni_jim6mbk', coalesce((cast(null as Nullable(Int32)) is NULL), false), coalesce(true, true), 18446744073709551614.1, coalesce(((NOT NOT(cast( (-9062 = cast(null as Nullable(Decimal))) as Nullable(Bool)))))
or ((NOT NOT(cast( (cast(null as Nullable(Decimal)) = 6518) as Nullable(Bool))))), false), 292933933);
-- unexpected result
select * from t_elmbvl__;
-- after sleep
select sleep(1);
-- this result is correct
select * from t_elmbvl__;
```
previous SQL produces:
```
+--------------+------------+-----------+---------+----------------------+------+-------------+
| c_ld7yut2msy | c_rfad4lh7 | c_mt6h0fe | c_qqdw7 | c_xrtu_99k | c_jo | c_fjago |
+--------------+------------+-----------+---------+----------------------+------+-------------+
| -1152318580 | eoqjjlyl4 | 0 | 1 | -32769 | 1 | -1711766346 |
| -1261308300 | xk | 1 | 1 | 2147483647.5 | 1 | -906918576 |
| -1167758264 | ni_jim6mbk | 1 | 1 | 18446744073709552000 | 0 | 292933933 |
| 1123238126 | pm_ | 0 | 0 | 58.34 | 1 | 32767 |
| -775545040 | u0mw | 0 | 0 | 35.7 | 1 | -851431940 |
| 34 | w | 0 | 1 | 72.96 | 1 | -465198836 |
| 205931387 | y632x | 0 | 0 | 72.73 | 0 | 1401197463 |
| 1126263007 | ccbdd | 0 | 0 | 51.48 | 0 | 65535 |
+--------------+------------+-----------+---------+----------------------+------+-------------+
8 rows in set (0.00 sec)
Read 8 rows, 299.00 B in 0.004867 sec., 1643 rows/sec., 59.99 KiB/sec.
+----------+
| sleep(1) |
+----------+
| 0 |
+----------+
1 row in set (1.01 sec)
Read 1 rows, 1.00 B in 1.000639 sec., 0 rows/sec., 1.00 B/sec.
+--------------+------------+-----------+---------+----------------------+------+-------------+
| c_ld7yut2msy | c_rfad4lh7 | c_mt6h0fe | c_qqdw7 | c_xrtu_99k | c_jo | c_fjago |
+--------------+------------+-----------+---------+----------------------+------+-------------+
| -1152318580 | eoqjjlyl4 | 0 | 1 | -32769 | 1 | -1711766346 |
| -1261308300 | xk | 1 | 1 | 2147483647.5 | 1 | -906918576 |
| -1167758264 | ni_jim6mbk | 1 | 1 | 18446744073709552000 | 0 | 292933933 |
| 1123238126 | pm_ | 0 | 0 | 58.34 | 1 | 32767 |
| -775545040 | u0mw | 0 | 0 | 35.7 | 1 | -851431940 |
| 34 | w | 0 | 1 | 72.96 | 1 | -465198836 |
| -2108699971 | o | 0 | 0 | -1.6 | 0 | -1207250535 |
| 205931387 | y632x | 0 | 0 | 72.73 | 0 | 1401197463 |
| 1126263007 | ccbdd | 0 | 0 | 51.48 | 0 | 65535 |
| -2032679981 | n05 | 0 | 1 | 10.7 | 0 | 32 |
| 654967805 | pgq6wiub0h | 0 | 0 | 67.58 | 0 | -427315728 |
| 2147483647 | bnuxtpb02n | 1 | 0 | 30.3 | 0 | -2092186939 |
+--------------+------------+-----------+---------+----------------------+------+-------------+
12 rows in set (0.01 sec)
Read 12 rows, 465.00 B in 0.007834 sec., 1531 rows/sec., 57.97 KiB/sec.
```
**Expected behavior**
The first select statement should produce 12 rows just like the one after `sleep`.
**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
```xml
debug
/var/log/clickhouse-server/clickhouse-server.log
/var/log/clickhouse-server/clickhouse-server.err.log
1000M
3
cluster_2S_1R node 1
0.0.0.0
8123
9000
9004
9005
users.xml
/var/lib/clickhouse/access/
/clickhouse/task_queue/ddl
clickhouse-01
9000
clickhouse-02
9000
clickhouse-keeper-01
9181
clickhouse-keeper-02
9181
clickhouse-keeper-03
9181
01
01
```
users.xml
```xml
10000000000
0
in_order
1
allow
1
default
::/0
default
1
1
1
1
3600
0
0
0
0
0
```
keeper_config.xml:
```xml
information
/var/log/clickhouse-keeper/clickhouse-keeper.log
/var/log/clickhouse-keeper/clickhouse-keeper.err.log
1000M
3
::
10000
9181
3
/var/lib/clickhouse/coordination/log
/var/lib/clickhouse/coordination/snapshots
10000
30000
information
1
clickhouse-keeper-01
9234
2
clickhouse-keeper-02
9234
3
clickhouse-keeper-03
9234
```
#### 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.
Contributor guide
Assessment
This issue has not been assessed yet.