Unexpected result when using JSON_VALID

[suyZhong]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP DATABASE IF EXISTS db0;
CREATE DATABASE db0;
USE db0;

CREATE  TABLE  t0(c0 VARCHAR(500) , c1 INT );
CREATE VIEW v0(c0) AS SELECT 'a' FROM t0;
INSERT INTO t0(c0) VALUES ('b');

SELECT * FROM t0 NATURAL RIGHT JOIN v0; -- a NULL
SELECT (JSON_VALID(t0.c1)=0) FROM t0 NATURAL RIGHT JOIN v0; -- 1
SELECT * FROM t0 NATURAL RIGHT JOIN v0 WHERE (JSON_VALID(t0.c1)=0);
-- Expected: a NULL
-- Actual: empty

This could be reproduced on MySQL also, but still, I suspect it is unexpected.

2. What did you expect to see? (Required)

The second query returns a NULL value or the third query returns the row in the table after join.

3. What did you see instead (Required)

empty set

4. What is your TiDB version? (Required)

select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.4.0-alpha-244-ga5e07a2
Edition: Community
Git Commit Hash: a5e07a2ed360f29216c912775ce482f536f4102b
Git Branch: HEAD
UTC Build Time: 2024-09-25 05:10:21
GoVersion: go1.21.13
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[suyZhong]

/label fuzz/sqlancer

[YangKeao]

There is another case that the result is not compatible with MySQL:

SELECT * FROM t0 WHERE (JSON_VALID(t0.c1)=0);

TiDB (with TiKV) gives nothing, but TiDB (unistore) and MySQL gives one row. Maybe the implementation has some bugs in TiKV side.

[YangKeao]

SELECT * FROM t0 WHERE (JSON_VALID(t0.c1)=0);

For this case, I submitted a bug report for MySQL. The behavior is inconsistent for MySQL and its document: https://bugs.mysql.com/bug.php?id=116703, and it has been confirmed as a bug.

For TiDB, I suggest to temporarily modify the behavior of unistore to keep consistent with TiKV and document (which is different with the MySQL).

[YangKeao]

SELECT * FROM t0 WHERE (JSON_VALID(t0.c1)=0);

For this case, I submitted a bug report for MySQL. The behavior is inconsistent for MySQL and its document: https://bugs.mysql.com/bug.php?id=116703.

For TiDB, I suggest to temporarily modify the behavior of unistore to keep consistent with TiKV and document (which is different with the MySQL).

And it caused the unexpected behavior in TiDB. The Projection on TiDB side and the Selection in TiKV side has different implementation.

BTW, I don't know why this strange behavior exists in MySQL. Maybe you can also submit a bug for MySQL.