JSON_EXTRACT fails to cast as bool

[kaeverens]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

in both MySQL and TiDB, run the following:

create database testjson;
use testjson;
create table testjson( id int auto_increment not null primary key, j json )default charset=utf8 engine=innodb;
insert into testjson set j='{"test":1}';
select id from testjson where json_extract(j, '$.test');

2. What did you expect to see?

MySQL returns this:

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.001 sec)

3. What did you see instead?

TiDB returns this:

ERROR 1105 (HY000): InvalidDataType("can\'t convert Json(1) to bool")

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

v3.0.0-rc.1-256-g1ddb31720

[ghost]

I tested this against master. The behavior has changed to return zero results instead of an error. This is worse, as it means wrong results according to MySQL type conversion rules. Here is a simplified test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int auto_increment not null primary key, j json);
INSERT INTO t1 SET j='{"test":1}';
SELECT * FROM t1 WHERE json_extract(j, '$.test');
SELECT * FROM t1 WHERE json_extract(j, '$.test') = 1;
..

mysql> SELECT * FROM t1 WHERE json_extract(j, '$.test');
Empty set (0.00 sec)

mysql> SELECT * FROM t1 WHERE json_extract(j, '$.test') = 1;
+----+-------------+
| id | j           |
+----+-------------+
|  1 | {"test": 1} |
+----+-------------+
1 row in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-771-gca41972fb
Edition: Community
Git Commit Hash: ca41972fbac068c8a5de107d9075f09ac68842ac
Git Branch: master
UTC Build Time: 2020-07-14 02:41:21
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[SunRunAway]

@lzmhhh123 PTAL if free. Thanks.

[SunRunAway]

I tested this against master. The behavior has changed to return zero results instead of an error. This is worse, as it means wrong results according to MySQL type conversion rules. Here is a simplified test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int auto_increment not null primary key, j json);
INSERT INTO t1 SET j='{"test":1}';
SELECT * FROM t1 WHERE json_extract(j, '$.test');
SELECT * FROM t1 WHERE json_extract(j, '$.test') = 1;
..

mysql> SELECT * FROM t1 WHERE json_extract(j, '$.test');
Empty set (0.00 sec)

mysql> SELECT * FROM t1 WHERE json_extract(j, '$.test') = 1;
+----+-------------+
| id | j           |
+----+-------------+
|  1 | {"test": 1} |
+----+-------------+
1 row in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-771-gca41972fb
Edition: Community
Git Commit Hash: ca41972fbac068c8a5de107d9075f09ac68842ac
Git Branch: master
UTC Build Time: 2020-07-14 02:41:21
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

I've investigated this difference. It is because of the different behaviors of TiKV Coprocessor and mocktikv Coprocessor.

W/ TiKV,

mysql> SELECT * FROM t1 WHERE json_extract(j, '$.test');
Empty set (0.00 sec)

W/ mocktikv,

mysql> SELECT * FROM t1 WHERE json_extract(j, '$.test');
ERROR 1105 (HY000): cannot convert type json.BinaryJSON to bool

We should fix them both.

[SunRunAway]

Coprocessor part is still needed to be fix.

[breezewish]

@iosmanthus PTAL

[wjhuang2016]

In master:

mysql> select id from testjson where json_extract(j, '$.test');
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

But using TiKV:

mysql> select id from testjson where json_extract(j, '$.test');
Empty set (0.00 sec)

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions