Bug #117089 Inconsistent results using equivalent predicates in two queries.

• Submitted: 1 Jan 8:37
• Modified: 1 Jan 9:28
• Reporter: wang jack
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.4.1, 8.0.40
• OS: Windows (windows 11)
• CPU Architecture: x86 (x86_64)

[1 Jan 8:37] wang jack

Description:
Here are two queries.
Query 1 uses (NULL) IS NOT NULL as part of the predicate, while Query 2 uses FALSE as part of the predicate. According to my understanding, these two predicates should be equivalent.
However, the results of Query 1 and Query 2 are inconsistent.

-- Query1
mysql> SELECT TRUE FROM t2 WHERE ((CAST(t2.c0 AS JSON)) IN ((NULL) IS NOT NULL)) ;
+------+
| TRUE |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
-- Query 2
mysql> SELECT TRUE FROM t2 WHERE ((CAST(t2.c0 AS JSON)) IN (FALSE));
Empty set (0.01 sec)

How to repeat:
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(c0 MEDIUMINT);
INSERT INTO t2(c0) VALUES(0);

SELECT (NULL) IS NOT NULL;

SELECT TRUE FROM t2 WHERE ((CAST(t2.c0 AS JSON)) IN ((NULL) IS NOT NULL)) ;
SELECT TRUE FROM t2 WHERE ((CAST(t2.c0 AS JSON)) IN (FALSE));

[1 Jan 9:28] MySQL Verification Team

Hello wang jack,

Thank you for the report and test case.

regards,
Umesh