Bug #117454 Unexpected results with JSON_VALID function in WHERE clause.

• Submitted: 13 Feb 2:08
• Modified: 13 Feb 5:45
• Reporter: wang jack
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S2 (Serious)
• Version: 8.4.1 9.1.0 , 8.0.41
• OS: Windows (windows 11)
• CPU Architecture: x86 (x86_64)

[13 Feb 2:08] wang jack

Description:
From my understanding, the first query below should return 1 row, but it is actually return empty.

It is uncertain whether this is a duplicate of https://bugs.mysql.com/bug.php?id=116703.

mysql> SELECT TRUE FROM t0 LEFT JOIN t1 ON FALSE WHERE (NOT (JSON_VALID(t1.c0)));
Empty set (0.00 sec)

mysql> SELECT SUM(((NOT (JSON_VALID(t1.c0)))) IS TRUE) FROM t0 LEFT JOIN t1 ON FALSE;
+------------------------------------------+
| SUM(((NOT (JSON_VALID(t1.c0)))) IS TRUE) |
+------------------------------------------+
|                                        1 |
+------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t0(c0 TIMESTAMP) ;
CREATE TABLE t1 LIKE t0;
INSERT INTO t0(c0) VALUES('2002-10-17 04:10:01');

SELECT TRUE FROM t0 LEFT JOIN t1 ON FALSE WHERE (NOT (JSON_VALID(t1.c0)));
SELECT SUM(((NOT (JSON_VALID(t1.c0)))) IS TRUE) FROM t0 LEFT JOIN t1 ON FALSE;

[13 Feb 5:45] MySQL Verification Team

Hello wang jack,

Thank you for the report and test case.
Verified as described.

regards,
Umesh