json_extract fails with non-quoted path

[kaeverens]

Bug Report

this has happened since upgrading from v6.1.1 to v6.2.0. when I select a JSON field on a valid path that does not need quotes, it fails.

it fails with both json_extract() and with the -> shortcut as well

1. Minimal reproduce step (Required)

MySQL [user32]> create table test1(meta json)default charset utf8mb4;
Query OK, 0 rows affected (0.665 sec)

MySQL [user32]> insert into test1 values('{"job_ref_customer":"234"}');
Query OK, 1 row affected (0.119 sec)

MySQL [user32]> select meta->'$.job_ref_customer' from test1;
+----------------------------+
| meta->'$.job_ref_customer' |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.033 sec)

MySQL [user32]> select meta->'$."job_ref_customer"' from test1;
+------------------------------+
| meta->'$."job_ref_customer"' |
+------------------------------+
| "234"                        |
+------------------------------+
1 row in set (0.025 sec)

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

MySQL [user32]> select meta->'$.job_ref_customer' from test1;
+----------------------------+
| meta->'$.job_ref_customer' |
+----------------------------+
| "234"                      |
+----------------------------+
1 row in set (0.033 sec)

3. What did you see instead (Required)

MySQL [user32]> select meta->'$.job_ref_customer' from test1;
+----------------------------+
| meta->'$.job_ref_customer' |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.033 sec)

4. What is your TiDB version? (Required)

MySQL [user32]> SELECT tidb_version() \G
*************************** 1. row ***************************
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:47:05
GoVersion: go1.18.5
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[kaeverens]

I tested on a second cluster that has the exact same tidb_version() results and it worked properly, so this might not be specific to the version.

I don't know what else it could be, though?

[kaeverens]

the issue appears to have stopped in the original database where it was happening. I think that maybe there was a failed query that somehow got cached, and when the cache cleared, a fresh query worked as expected. does that sound plausible?

[xiongjiwei]

yes, there is a cache about json path, it looks like some unknown source value has overwritten the cached value. can you please provide more information about how you use the job_ref_customer? Is there any usage in json_insert , json_set or some other json functions?

[kaeverens]

the example that I posted (step 1) was a simple creation of a table, then I inserted JSON as a string. there were no other operations. it failed on the very first select. What I posted in step 1 was a direct copy/paste of what was displayed in my client.

in other databases (in the same cluster), I use all sorts of operations on the JSON field. I don't think I can narrow it down - it's used in a lot of places in a lot of different ways.

But I think it's interesting that it failed on a simple table creation.

If there is a cache on the json path - is that cache cross-database? It's interesting that it would fail in two separate databases within the same cluster. that might be the clue you need.

[xiongjiwei]

yes, the cache will cross-database. I believe the root cause is the cached data is broken, actually, I have checked all the possibilities that could break the data but still find nothing. I will write some code to simulate the scenario to see if I can find something.

[kaeverens]

is there a way to clear the cache, in case this happens again?

[xiongjiwei]

is there a way to clear the cache, in case this happens again?

the only way now is restart the server

[xiongjiwei]

close due to we have a fix, feel free to open it if it happens again