ClickHouse / ClickHouse/ClickHouse
Wrong results of SELECT DISTINCT statement when comparing 0 and -0
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe what's wrong**
The two semantically-equivalent SELECT statements should output the same results, but they did not.
**Does it reproduce on recent release?**
It can be reproduced in the latest version.
**How to reproduce**
Version: 23.5.1.1 (commit 3e6314675c6467bc4dd78f659bac862f7e9648f8)
Easy reproduce in ClickHouse fiddle: https://fiddle.clickhouse.com/8abc8f29-70d5-48b1-8886-1f9ba9ae1350
_Set up database_
```sql
create table t0 (vkey UInt32, c0 UInt32, c1 Float32, primary key(vkey))engine = MergeTree;
insert into t0 values (1, null, -0.0);
insert into t0 values (2, null, 0.0);
```
_SELECT statement 1_
```sql
select distinct
subq_0.c_4_c41_0 as c_1_c52_9,
subq_0.c_4_c42_1 as c_1_c54_11
from
(select
ref_2.c1 as c_4_c41_0,
ref_1.vkey as c_4_c42_1
from
t0 as ref_1
full outer join t0 as ref_2
on (ref_1.c0 = ref_2.vkey )
order by c_4_c41_0 desc, c_4_c42_1 asc) as subq_0
where case when (exists (select 1)) then true else true end;
```
As `case when (exists (select 1)) then true else true end` can be replaced with `true`, I get the semantically-equivalent SELECT statement:
_SELECT statement 2_
```sql
select distinct
subq_0.c_4_c41_0 as c_1_c52_9,
subq_0.c_4_c42_1 as c_1_c54_11
from
(select
ref_2.c1 as c_4_c41_0,
ref_1.vkey as c_4_c42_1
from
t0 as ref_1
full outer join t0 as ref_2
on (ref_1.c0 = ref_2.vkey )
order by c_4_c41_0 desc, c_4_c42_1 asc) as subq_0
where true;
```
**Expected behavior**
The two SELECT statements output the same results.
**Actual behavior**
They are different.
SELECT statement 1 outputs:
```
+-----------+------------+
| c_1_c52_9 | c_1_c54_11 |
+-----------+------------+
| 0 | 0 |
+-----------+------------+
| -0 | 0 |
+-----------+------------+
| 0 | 1 |
+-----------+------------+
| 0 | 2 |
+-----------+------------+
```
SELECT statement 2 outputs:
```
+-----------+------------+
| c_1_c52_9 | c_1_c54_11 |
+-----------+------------+
| 0 | 0 |
+-----------+------------+
| 0 | 1 |
+-----------+------------+
| 0 | 2 |
+-----------+------------+
```
**Additional context**
The earliest reproducible version is 23.4 in fiddle: https://fiddle.clickhouse.com/7c3fb0d7-dc55-4808-9e31-743ce9541ce0
Contributor guide
Assessment
This issue has not been assessed yet.