Generated columns index replacement rule not support JSON functions

[sunnyfun888]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE person (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL,
address_info JSON,
city VARCHAR(64) AS (JSON_EXTRACT(address_info, '$.city')),
KEY (city)
);
INSERT INTO person ( NAME, address_info) VALUES ( 'a', '{"city":"Beijing"}');

DESC SELECT NAME, id, city FROM person WHERE city = '"Beijing"';

This uses index:

id                                  estRows  task       access object                   operator info                                                    
----------------------------------  -------  ---------  ------------------------------  -----------------------------------------------------------------
Projection_4                        10.00    root                                       test.person.name, test.person.id, test.person.city               
└─Projection_12                     10.00    root                                       test.person.id, test.person.name, test.person.city               
  └─IndexLookUp_11                  10.00    root                                                                                                        
    ├─IndexRangeScan_9(Build)       10.00    cop[tikv]  table:person, index:city(city)  range:[""Beijing"",""Beijing""], keep order:false, stats:pseudo  
    └─TableRowIDScan_10(Probe)      10.00    cop[tikv]  table:person                    keep order:false, stats:pseudo                                   

But this will not:
DESC SELECT NAME, id, city FROM person WHERE JSON_EXTRACT(address_info, '$.city') = 'Beijing';

id                         estRows  task       access object  operator info                                                                       
-------------------------  -------  ---------  -------------  ------------------------------------------------------------------------------------
Projection_4               0.80     root                      test.person.name, test.person.id, test.person.city                                  
└─TableReader_7            0.80     root                      data:Selection_6                                                                    
  └─Selection_6            0.80     cop[tikv]                 eq(json_extract(test.person.address_info, "$.city"), cast("Beijing", json BINARY))  
    └─TableFullScan_5      1.00     cop[tikv]  table:person   keep order:false, stats:pseudo                                                      

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

Generated columns index replacement rule supports JSON functions, like MySQL.

3. What did you see instead (Required)

Current version of TiDB not support.

4. What is your TiDB version? (Required)

6.1.0

[aytrack]

This is not a bug, actually MySQL has the same behavior

mysql root@127.0.0.1:test> CREATE TABLE person (
                       ->  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                       ->  NAME VARCHAR(255) NOT NULL,
                       ->  address_info JSON,
                       ->  city VARCHAR(64) AS (JSON_EXTRACT(address_info, '$.city')),
                       ->  KEY (city)
                       ->  );
                       ->  INSERT INTO person ( NAME, address_info) VALUES ( 'a', '{"city":"Beijing"}');
Query OK, 0 rows affected
Time: 0.064s

Query OK, 1 row affected
Time: 0.010s
mysql root@127.0.0.1:test> explain analyze SELECT NAME, id, city FROM person WHERE JSON_EXTRACT(address_info, '$.city') = 'Beijing';
                       ->
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (json_extract(person.address_info,'$.city') = 'Beijing')  (cost=0.35 rows=1) (actual time=0.073..0.080 rows=1 loops=1)\n    -> Table scan on person  (cost=0.35 rows=1) (actual time=0.050..0.056 rows=1 loops=1)\n |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql root@127.0.0.1:test> select version();
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+

If you want to the function can use index, you should add an expression index for it. But expression is an experimental feature and not recommended for a production environment.

[aytrack]

I will close this issue. Please feel free to reopen this if you have some more questions.

[sunnyfun888]

Maybe I need another example to reproduce it.

I have opened another issue: #35490 .