Description:
Prepared statement does not use generated column index
Reported by GoldenDB Team
How to repeat:
create table t1(
c1 json,
c2 int,
gc varchar(50) as (c1->>'$.str') stored,
key k_gc(gc)
);
delimiter //
create procedure proc1(in begin int, in end int)
begin
declare i int;
set i = begin;
while i < end do
insert into t1(c1, c2) values(concat('{"str": "', i, '"}'), i);
set i = i + 1;
end while;
end//
delimiter ;
call proc1(0, 1000);
prepare p1 from "select * from t1 where json_extract(c1, '$.str') = ?";
set @a = '1';
mysql> set session optimizer_trace = "enabled=on";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where json_extract(c1, '$.str') = '1';
+--------------+------+------+
| c1 | c2 | gc |
+--------------+------+------+
| {"str": "1"} | 1 | 1 |
+--------------+------+------+
1 row in set (0.00 sec)
mysql> select * from information_schema.optimizer_trace\G
...
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`t1`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "k_gc",
"rows": 1,
"cost": 0.35,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "k_gc"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 0.35,
"chosen": true
}
]
},
...
mysql> execute p1 using @a;
+--------------+------+------+
| c1 | c2 | gc |
+--------------+------+------+
| {"str": "1"} | 1 | 1 |
+--------------+------+------+
1 row in set (0.08 sec)
mysql> select * from information_schema.optimizer_trace\G
...
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`t1`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 1000,
"access_type": "scan",
"resulting_rows": 1000,
"cost": 101.5,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1000,
"cost_for_plan": 101.5,
"chosen": true
}
]
},
...
The execution time of prepared statement is more than query statement. From optimizer trace we can see, for query statement, index for generated column is used, but for prepared statement, it's not.
Suggested fix:
sql/range_optimizer/range_optimizer.cc
bool comparable_in_index(Item *cond_func, const Field *field,
const Field::imagetype itype,
Item_func::Functype comp_type, const Item *value) {
...
/*
We can't use indexes when comparing to a JSON value. For example,
the string '{}' should compare equal to the JSON string "{}". If
we use a string index to compare the two strings, we will be
comparing '{}' and '"{}"', which don't compare equal.
The only exception is Item_json, which is a basic const item and is
used to contain value coerced to index's type.
*/
if (value->result_type() == STRING_RESULT &&
value->data_type() == MYSQL_TYPE_JSON && !value->basic_const_item())
return false;
return true;
}
https://dev.mysql.com/worklog/task/?id=9384
For Item_param, it is not a basic const item, so 'field' and 'value' are not comparable.
Description: Prepared statement does not use generated column index Reported by GoldenDB Team How to repeat: create table t1( c1 json, c2 int, gc varchar(50) as (c1->>'$.str') stored, key k_gc(gc) ); delimiter // create procedure proc1(in begin int, in end int) begin declare i int; set i = begin; while i < end do insert into t1(c1, c2) values(concat('{"str": "', i, '"}'), i); set i = i + 1; end while; end// delimiter ; call proc1(0, 1000); prepare p1 from "select * from t1 where json_extract(c1, '$.str') = ?"; set @a = '1'; mysql> set session optimizer_trace = "enabled=on"; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 where json_extract(c1, '$.str') = '1'; +--------------+------+------+ | c1 | c2 | gc | +--------------+------+------+ | {"str": "1"} | 1 | 1 | +--------------+------+------+ 1 row in set (0.00 sec) mysql> select * from information_schema.optimizer_trace\G ... { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "k_gc", "rows": 1, "cost": 0.35, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "k_gc" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.35, "chosen": true } ] }, ... mysql> execute p1 using @a; +--------------+------+------+ | c1 | c2 | gc | +--------------+------+------+ | {"str": "1"} | 1 | 1 | +--------------+------+------+ 1 row in set (0.08 sec) mysql> select * from information_schema.optimizer_trace\G ... { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 1000, "access_type": "scan", "resulting_rows": 1000, "cost": 101.5, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1000, "cost_for_plan": 101.5, "chosen": true } ] }, ... The execution time of prepared statement is more than query statement. From optimizer trace we can see, for query statement, index for generated column is used, but for prepared statement, it's not. Suggested fix: sql/range_optimizer/range_optimizer.cc bool comparable_in_index(Item *cond_func, const Field *field, const Field::imagetype itype, Item_func::Functype comp_type, const Item *value) { ... /* We can't use indexes when comparing to a JSON value. For example, the string '{}' should compare equal to the JSON string "{}". If we use a string index to compare the two strings, we will be comparing '{}' and '"{}"', which don't compare equal. The only exception is Item_json, which is a basic const item and is used to contain value coerced to index's type. */ if (value->result_type() == STRING_RESULT && value->data_type() == MYSQL_TYPE_JSON && !value->basic_const_item()) return false; return true; } https://dev.mysql.com/worklog/task/?id=9384 For Item_param, it is not a basic const item, so 'field' and 'value' are not comparable.