| Bug #112580 | Unexpected result in COUNT function | ||
|---|---|---|---|
| Submitted: | 30 Sep 2023 12:32 | Modified: | 4 Oct 2023 11:08 |
| Reporter: | Jin Wei | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.27 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | x86 | |
[30 Sep 2023 12:32]
Jin Wei
[2 Oct 2023 12:28]
MySQL Verification Team
Hi Mr. Wei, Thank you very much for your bug report. What you have presented is indeed a designed behaviour. In your second query, you intended to compare a scalar with a row, which must fail. In your third query, you added a magic keyword "AS UNSIGNED " which made a comparison possible. Not a bug.
[2 Oct 2023 17:07]
Jin Wei
Hello, We tried again. As the follow, the first SELECT statement, which evaluates c1 < ANY (VALUES ROW(0), ROW(0)), results in the value 1. Therefore, SELECT COUNT(*) FROM t0 WHERE c1 < ANY (VALUES ROW(0), ROW(0)); should seemingly return 1 instead of 0. mysql> CREATE TABLE t0(c1 INT); 16777215' );Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO t0 VALUES ( '16777215' ); Query OK, 1 row affected (0.02 sec) mysql> select c1 < ANY ( VALUES ROW ( 0 ), ROW ( 0 ) ) from t0; +------------------------------------------+ | c1 < ANY ( VALUES ROW ( 0 ), ROW ( 0 ) ) | +------------------------------------------+ | 1 | +------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT COUNT( * ) FROM t0 WHERE c1 < ANY ( VALUES ROW ( 0 ), ROW ( 0 ) ); +------------+ | COUNT( * ) | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) mysql> SELECT COALESCE( SUM( CAST( ( c1 < ANY ( VALUES ROW ( 0 ), ROW ( 0 ) ) ) <> 0 AS UNSIGNED ) ), 0 ) FROM t0; +---------------------------------------------------------------------------------------------+ | COALESCE( SUM( CAST( ( c1 < ANY ( VALUES ROW ( 0 ), ROW ( 0 ) ) ) <> 0 AS UNSIGNED ) ), 0 ) | +---------------------------------------------------------------------------------------------+ | 1 | +---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
[3 Oct 2023 10:13]
MySQL Verification Team
Hi, Please re-read our previous comment. It explains the results fully.
[3 Oct 2023 13:02]
Jin Wei
Hi, In the comment I added, there is a slight difference compared to the bug report. The query SELECT c1 < ANY (VALUES ROW(0), ROW(0)) FROM t0; (output 1) in the comment can verify that comparing a scalar with a row does not fail.
[3 Oct 2023 13:07]
MySQL Verification Team
HI Mr. Wei, The expressions in the SELECT list are not comparable with conditions in the WHERE clause. Not a bug.
[3 Oct 2023 13:59]
Jin Wei
Hi, Please explain the results of following queries: mysql> select c1 < ANY ( VALUES ROW ( 0 ), ROW ( 0 ) ) from t0; +------------------------------------------+ | c1 < ANY ( VALUES ROW ( 0 ), ROW ( 0 ) ) | +------------------------------------------+ | 1 | +------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT( * ) FROM t0 WHERE c1 < ANY ( VALUES ROW ( 0 ), ROW ( 0 ) ); +------------+ | COUNT( * ) | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) p.s. you can call me Ms. Wei :)
[4 Oct 2023 10:34]
MySQL Verification Team
HI Ms. Wei, The expressions in the SELECT list are not comparable with conditions in the WHERE clause. Not a bug.
[4 Oct 2023 11:08]
MySQL Verification Team
Hi Ms. Wei, We further analysed your report and concluded that this is indeed a bug. However, it has been already reported internally several months ago and it is now fixed. It is fixed in 8.0 release that is not published yet on dev.mysql.com. You should wait that next release of 8.0 comes out and you will see that this bug is now fixed. Duplicate.
