| Bug #112583 | Inconsistencies have been observed using COUNT and COALESCE(SUM…) | ||
|---|---|---|---|
| Submitted: | 2 Oct 2023 2:47 | Modified: | 2 Oct 2023 16:55 |
| Reporter: | Jin Wei | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: CSV | Severity: | S3 (Non-critical) |
| Version: | 8.0.27 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | x86 | |
[2 Oct 2023 2:47]
Jin Wei
[2 Oct 2023 12:42]
MySQL Verification Team
Hi Mr. Wei, Thank you for your bug report. However, we can not repeat it. These are the results that we got: +------------+ | COUNT( * ) | +------------+ | 1 | +------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------+ | COALESCE( SUM( CAST( ( c2 LIKE c2 ESCAPE ( SELECT COUNT( * ) FROM t0 WHERE ( 127.4 ) <> ( c1 XOR c2 >= '0' AND c2 ) ) ) <> 0 AS UNSIGNED ) ), 0 ) | +---------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | +---------------------------------------------------------------------------------------------------------------------------------------------------+ We did change your mistake of trying to insert a floating point number into an integer columns. Can't repeat.
[2 Oct 2023 12:51]
MySQL Verification Team
On additional note. We have tested this with several storage engines, including CSV and we have got the same output.
[2 Oct 2023 16:55]
Jin Wei
Hello, Here is the process I followed to reproduce it (we try it on MySQL 8.0.27): Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test_sqlaser; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> CREATE TABLE t0 ( c1 INT2 ( 4 ) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, c2 INT2 ( 4 ) UNSIGNED DEFAULT '0' NOT NULL ) ENGINE = CSV; VALUES ( 9410, 9412 ); INSERT INTO t0 VALUES ( '12Query OK, 0 rows affected, 3 warnings (0.01 sec) mysql> INSERT INTO t0 VALUES ( 9410, 9412 ); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t0 VALUES ( '127.4', '127.4' ); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT COUNT( * ) FROM t0 WHERE c2 LIKE c2 ESCAPE ( SELECT COUNT( * ) FROM t0 WHERE ( 127.4 ) <> ( c1 XOR c2 >= '0' AND c2 ) ); +------------+ | COUNT( * ) | +------------+ | 2 | +------------+ 1 row in set (0.01 sec) mysql> SELECT COALESCE( SUM( CAST( ( c2 LIKE c2 ESCAPE ( SELECT COUNT( * ) FROM t0 WHERE ( 127.4 ) <> ( c1 XOR c2 >= '0' AND c2 ) ) ) <> 0 AS UNSIGNED ) ), 0 ) FROM t0; +---------------------------------------------------------------------------------------------------------------------------------------------------+ | COALESCE( SUM( CAST( ( c2 LIKE c2 ESCAPE ( SELECT COUNT( * ) FROM t0 WHERE ( 127.4 ) <> ( c1 XOR c2 >= '0' AND c2 ) ) ) <> 0 AS UNSIGNED ) ), 0 ) | +---------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | +---------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
