| Bug #105437 | Wrong answer when got multiple count(distinct) | ||
|---|---|---|---|
| Submitted: | 3 Nov 2021 7:53 | Modified: | 3 Nov 2021 9:05 |
| Reporter: | jiangtao guo | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.23 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[3 Nov 2021 8:14]
MySQL Verification Team
Hello jiangtao, Thank you for the report and test case. I quickly tried on 8.0.27 build and confirmed that there is no issue. I see that you are using very old build. Could you please check this on current GA i.e. MySQL Server 8.0.27 and inform us if you are still seeing the issue? Thank you! == ./mtr bug105437 --nocheck-testcases Logging: ./mtr bug105437 --nocheck-testcases MySQL Version 8.0.27 Checking supported features - Binaries are debug compiled Using 'all' suites Collecting tests Checking leftover processes Removing old var directory Creating var directory '/dev/shm/bug105423/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ CREATE TABLE t1 (a INTEGER); INSERT INTO t1 VALUES (1), (2); SELECT DISTINCT COUNT(DISTINCT t1.a) AS da FROM t1 JOIN t1 AS t2 ON t1.a = t2.a GROUP BY t1.a HAVING COUNT(DISTINCT t1.a) = 1; da 1 [ 50%] main.bug105437 [ pass ] 71 [100%] shutdown_report [ pass ] ------------------------------------------------------------------------------ regards, Umesh
[3 Nov 2021 9:05]
jiangtao guo
I also cannot reproduce on 8.0.27. Looks like the bug has been fixed. Thank you very much!
Description: DROP TABLE t1; CREATE TABLE t1 (a INTEGER); INSERT INTO t1 VALUES (1), (2); SELECT DISTINCT COUNT(DISTINCT t1.a) AS da FROM t1 JOIN t1 AS t2 ON t1.a = t2.a GROUP BY t1.a HAVING COUNT(DISTINCT t1.a) = 1; The result should be 1, but we got empty ret. Following is PG13's result: postgres=# SELECT DISTINCT COUNT(DISTINCT t1.a) AS da FROM t1 JOIN t1 AS t2 ON t1.a = t2.a postgres-# GROUP BY t1.a HAVING COUNT(DISTINCT t1.a) = 1; da ---- 1 (1 row) postgres=# select version(); version ------------------------------------------------------------------------------------------------------------------- PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by clang version 6.0.0-1ubuntu2 (tags/RELEASE_600/final), 64-bit (1 row) How to repeat: DROP TABLE t1; CREATE TABLE t1 (a INTEGER); INSERT INTO t1 VALUES (1), (2); SELECT DISTINCT COUNT(DISTINCT t1.a) AS da FROM t1 JOIN t1 AS t2 ON t1.a = t2.a GROUP BY t1.a HAVING COUNT(DISTINCT t1.a) = 1;