MySQL Bugs: #112600: The inconsistencies between the expr in the SELECT clause and WHERE clause

Bug #112600	The inconsistencies between the expr in the SELECT clause and WHERE clause
Submitted:	3 Oct 2023 13:56	Modified:	5 Oct 2023 12:34
Reporter:	Jin Wei	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In the second SELECT statement, the three rows with the condition ( c2 LIKE FALSE AND c3 LIKE '0' ) IS NULL all have a value of 0 (false). However, comparing this to the third and fourth SELECT statements where the condition after the WHERE clause is ( c2 LIKE FALSE AND c3 LIKE '0' ) IS NULL, inconsistent results are generated.

How to repeat:
mysql> CREATE TABLE t0 ( c1 INT ( 11 ) DEFAULT NULL, c2 BIGINT ( 36 ) UNSIGNED DEFAULT NULL, c3 CHAR ( 10 ) NOT NULL DEFAULT '' ) ENGINE = MyISAM;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> INSERT INTO t0 VALUES ( 0, 0, 0 ), ( NULL, NULL,'' ), ( 0, 0, 0 );
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t0;
+------+------+----+
| c1   | c2   | c3 |
+------+------+----+
|    0 |    0 | 0  |
| NULL | NULL |    |
|    0 |    0 | 0  |
+------+------+----+
3 rows in set (0.01 sec)

mysql> SELECT  ( c2 LIKE FALSE AND c3 LIKE '0' ) IS NULL FROM t0;
+-------------------------------------------+
| ( c2 LIKE FALSE AND c3 LIKE '0' ) IS NULL |
+-------------------------------------------+
|                                         0 |
|                                         0 |
|                                         0 |
+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT MAX( t0 . c3 + t0 . c2 + t0 . c1 ) = 1.000000 OR COUNT( * ) = FALSE FROM t0;
+---------------------------------------------------------------------+
| MAX( t0 . c3 + t0 . c2 + t0 . c1 ) = 1.000000 OR COUNT( * ) = FALSE |
+---------------------------------------------------------------------+
|                                                                   0 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT  MAX( t0 . c3 + t0 . c2 + t0 . c1 ) = 1.000000 OR COUNT( * ) = FALSE FROM t0 WHERE ( c2 LIKE FALSE AND c3 LIKE '0' ) IS NULL;
+---------------------------------------------------------------------+
| MAX( t0 . c3 + t0 . c2 + t0 . c1 ) = 1.000000 OR COUNT( * ) = FALSE |
+---------------------------------------------------------------------+
|                                                                   1 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

HI Mr. Wei,

Thank you for your bug report.

We have got the same results as you have.

We also think that this is a bug.

Verified as reported.

Hi Mr. Wei,

Sorry, but it turns out not to be a bug.

There are valid reasons for this decision and they are the following ones ....

For the last SELECT statement, the WHERE clause evaluates to FALSE for all 3 rows (as verified by the first statement), thus the MAX part of the selected expression is NULL, and COUNT(*) is equal to 0, which in our evaluation is the same as FALSE, so we get SELECT NULL OR 0 = 0 which is true.

Not a bug.