ClickHouse / ClickHouse/ClickHouse

When select all columns from distributed table immediately after insertion, the result is incorrect.

Open
#66,096 3 comments 0 reactions 0 assignees View on GitHub
comp-distributed external unexpected behaviour
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.