json_unquote && json_extract with asterisk push down tikv bug

[lizhenhuan]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table test.t1 (a varchar(64));
mysql> insert into test.t1 values('hello');
Query OK, 1 row affected (0.01 sec)

mysql> select a from test.t1;
+-------+
| a     |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)

mysql> select json_unquote(json_extract('[{"optUid":1}]', "$[*].optUid")) = '[1]';
+---------------------------------------------------------------------+
| json_unquote(json_extract('[{"optUid":1}]', "$[*].optUid")) = '[1]' |
+---------------------------------------------------------------------+
|                                                                   1 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select a from test.t1 where json_unquote(json_extract('[{"optUid":1}]', "$[*].optUid")) = '[1]';
Empty set (0.00 sec)

mysql> explain analyze select json_unquote(json_extract('[{"optUid":1}]', "$[*].optUid")) = '[1]';
+-------------------+---------+---------+------+---------------+----------------------------------------+---------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                | estRows | actRows | task | access object | execution info                         | operator info                                                                                                             | memory  | disk |
+-------------------+---------+---------+------+---------------+----------------------------------------+---------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_3      | 1.00    | 1       | root |               | time:23.5µs, loops:2, Concurrency:OFF  | eq(json_unquote(cast(json_extract(cast([{"optUid":1}], json BINARY), $[*].optUid), var_string(16777216))), [1])->Column#1 | 0 Bytes | N/A  |
| └─TableDual_4     | 1.00    | 1       | root |               | time:167ns, loops:2                    | rows:1                                                                                                                    | N/A     | N/A  |
+-------------------+---------+---------+------+---------------+----------------------------------------+---------------------------------------------------------------------------------------------------------------------------+---------+------+
2 rows in set (0.00 sec)

mysql> explain analyze select a from test.t1 where json_unquote(json_extract('[{"optUid":1}]', "$[*].optUid")) = '[1]';
+-------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                      | estRows | actRows | task      | access object | execution info                                                                                                                                               | operator info                                                                                                         | memory    | disk |
+-------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+------+
| TableReader_7           | 0.80    | 0       | root      |               | time:249.2µs, loops:1, cop_task: {num: 1, max: 241.5µs, proc_keys: 1, rpc_num: 1, rpc_time: 234.3µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}    | data:Selection_6                                                                                                      | 200 Bytes | N/A  |
| └─Selection_6           | 0.80    | 0       | cop[tikv] |               | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 41, total_keys: 4, rocksdb: {key_skipped_count: 3, block: {}}}   | eq(json_unquote(cast(json_extract(cast("[{"optUid":1}]", json BINARY), "$[*].optUid"), var_string(16777216))), "[1]") | N/A       | N/A  |
|   └─TableFullScan_5     | 1.00    | 1       | cop[tikv] | table:t1      | tikv_task:{time:0s, loops:1}                                                                                                                                 | keep order:false, stats:pseudo                                                                                        | N/A       | N/A  |
+-------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------+------+
3 rows in set (0.00 sec)

TiDB Result:

MySQL Result:

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

This SQL should return one row : "hello" 
select a from test.t1 where json_unquote(json_extract('[{"optUid":1}]', "$[*].optUid")) = '[1]';

3. What did you see instead (Required)

This SQL return 0 row
select a from test.t1 where json_unquote(json_extract('[{"optUid":1}]', "$[*].optUid")) = '[1]';

4. What is your TiDB version? (Required)

select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.2.0
Edition: Community
Git Commit Hash: daf2b17cdfe30c02ce282361009fb5bdb05f2b0e
Git Branch: heads/refs/tags/v6.2.0
UTC Build Time: 2022-08-22 13:38:40
GoVersion: go1.18.5
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[xiongjiwei]

seems something wrong in tikv about json_unquote && json_extract. do we have PR fix this? /cc @YangKeao

[YangKeao]

seems something wrong in tikv about json_unquote && json_extract. do we have PR fix this? /cc @YangKeao

I'm not sure which PR 🤦‍♂️ , but this issue has been fixed in the nightly build:

MySQL [test]> create table test.t1 (a varchar(64));
Query OK, 0 rows affected (0.517 sec)

MySQL [test]> insert into test.t1 values('hello');
Query OK, 1 row affected (0.008 sec)

MySQL [test]> select a from test.t1 where json_unquote(json_extract('[{"optUid":1}]', "$[*].optUid")) = '[1]';
+-------+
| a     |
+-------+
| hello |
+-------+

MySQL [test]> select * from information_schema.cluster_info;
+---------+-----------------+-----------------+-------------+------------------------------------------+---------------------------+-----------------+-----------+
| TYPE    | INSTANCE        | STATUS_ADDRESS  | VERSION     | GIT_HASH                                 | START_TIME                | UPTIME          | SERVER_ID |
+---------+-----------------+-----------------+-------------+------------------------------------------+---------------------------+-----------------+-----------+
| tidb    | 127.0.0.1:4000  | 127.0.0.1:37673 | None        | None                                     | 2022-08-30T12:37:49+08:00 | 4m19.790072958s |   3821487 |
| pd      | 127.0.0.1:2379  | 127.0.0.1:2379  | 6.3.0-alpha | 2adb9797473844e094a4571e7ca2e5388187681b | 2022-08-30T12:32:30+08:00 | 9m38.790076868s |         0 |
| tikv    | 127.0.0.1:20160 | 127.0.0.1:20180 | 6.2.0-alpha | 68f99ae034376f5629d80fa7712796a61dc5d50e | 2022-08-30T12:33:00+08:00 | 9m8.790080668s  |         0 |
| tiflash | 127.0.0.1:3930  | 127.0.0.1:20292 | 6.3.0-alpha | 785712432094f9e0d3354a7ebd5bc2dc6d16f4c9 | 2022-08-30T12:34:03+08:00 | 8m5.790084458s  |         0 |
+---------+-----------------+-----------------+-------------+------------------------------------------+---------------------------+-----------------+-----------+