ClickHouse / ClickHouse/ClickHouse

Wrong results of SELECT DISTINCT statement when comparing 0 and -0

Open
#50,192 3 comments 0 reactions 0 assignees View on GitHub
comp-query-execution enhancement external
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

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.