Incompatible issue related to JSON functions

[js00070]

Bug Report

Seems there's an incompatible issue related to JSON functions

1. What did you do?

select json_extract("\"asd\"","$") in ("asd","abcd");

2. What did you expect to see?

in MySQL:5.7

mysql> select json_extract("\"asd\"","$") in ("asd","abcd");
+----------------------------------------------+
| json_extract("\"asd\"","$") in ("asd","abc") |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

in MySQL:8

mysql> select json_extract("\"asd\"","$") in ("asd","abcd");
+-----------------------------------------------+
| json_extract("\"asd\"","$") in ("asd","abcd") |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead?

MySQL [(none)]> select json_extract("\"asd\"","$") in ("asd","abcd");
ERROR 3140 (22032): Invalid JSON text: The document root must not be followed by other values.
MySQL [(none)]>

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

Release Version: v4.0.0-alpha-940-g92e774913
Git Commit Hash: 92e774913a2f6166b104d3d8eaed5513733f1830
Git Branch: master
UTC Build Time: 2019-11-24 03:10:38
GoVersion: go version go1.13.1 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[wjhuang2016]

Thanks for your report, could you fire a PR to solve it?

[js00070]

Thanks for your report, could you fire a PR to solve it?

ok, I will take a try.

[Deardrops]

related issue #11883

[js00070]

the behaviour of MySQL:8 is a little strange.
in MySQL:8, we will see

mysql> select json_extract('"asd"',"$"),json_extract('"asd"',"$") in ('"asd"','"abcd"');
+---------------------------+-------------------------------------------------+
| json_extract('"asd"',"$") | json_extract('"asd"',"$") in ('"asd"','"abcd"') |
+---------------------------+-------------------------------------------------+
| "asd"                     |                                               0 |
+---------------------------+-------------------------------------------------+
1 row in set (0.00 sec)

in TiDB master branch, we have:

MySQL [(none)]> select json_extract('"asd"',"$"),json_extract('"asd"',"$") in ('"asd"','"abcd"');
+---------------------------+-------------------------------------------------+
| json_extract('"asd"',"$") | json_extract('"asd"',"$") in ('"asd"','"abcd"') |
+---------------------------+-------------------------------------------------+
| "asd"                     |                                               1 |
+---------------------------+-------------------------------------------------+
1 row in set (0.00 sec)

I think the TiDB's result of this case is more reasonable. How should we handle this condition?
@wjhuang2016

[zz-jason]

@js00070 We can file a bug issue to the MySQL Team through https://bugs.mysql.com/

[wjhuang2016]

@zz-jason , No, it's our bug, currently json_extract always return json, and there are some bugs/issues related to this.

[js00070]

more incompatible cases

in MySQL:8

mysql> select json_extract(1, "$");
ERROR 3146 (22032): Invalid data type for JSON data in argument 1 to function json_extract; a JSON string or JSON type is required.

in TiDB master

MySQL [(none)]> select json_extract(1, "$");
+----------------------+
| json_extract(1, "$") |
+----------------------+
| 1                    |
+----------------------+
1 row in set (0.00 sec)

---

in MySQL:8

mysql> select json_extract('{"a": 1}', "$") in ('{"a": 1}',"abcd");
+------------------------------------------------------+
| json_extract('{"a": 1}', "$") in ('{"a": 1}',"abcd") |
+------------------------------------------------------+
|                                                    0 |
+------------------------------------------------------+
1 row in set (0.00 sec)

in TiDB master

MySQL [(none)]> select json_extract('{"a": 1}', "$") in ('{"a": 1}',"abcd");
ERROR 3140 (22032): Invalid JSON text: The document root must not be followed by other values.

---

in MySQL:8

mysql> select json_extract('{"a": 1}', "$") in ('{"a": 1}','"abcd"');
+--------------------------------------------------------+
| json_extract('{"a": 1}', "$") in ('{"a": 1}','"abcd"') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

in TiDB master

MySQL [(none)]> select json_extract('{"a": 1}', "$") in ('{"a": 1}','"abcd"');
+--------------------------------------------------------+
| json_extract('{"a": 1}', "$") in ('{"a": 1}','"abcd"') |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

[js00070]

MySQL 8.0 document (https://dev.mysql.com/doc/refman/8.0/en/json.html#json-comparison)
says that IN function is not yet supported with JSON values.

and mysql uses an workaround

A workaround for the comparison operators and functions just listed is to cast JSON values to a native MySQL numeric or string data type so they have a consistent non-JSON scalar type. 

Should we implement this workaround in TiDB? @zz-jason @wjhuang2016

[ichn-hu]

/assign @ichn-hu

[ichn-hu]

This bug can also be fixed by #21903

[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