mirror of
https://github.com/MariaDB/server.git
synced 2025-04-09 08:45:33 +02:00
401 lines
14 KiB
Text
401 lines
14 KiB
Text
# Check various condition types
|
|
create table t1 (a int, vcol1 int as (a+1), index(vcol1));
|
|
insert into t1 (a) select seq from seq_1_to_100;
|
|
explain select * from t1 where a+1=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref vcol1 vcol1 5 const 1
|
|
# Try renaming the table
|
|
explain select * from t1 as TBL where TBL.a+1=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE TBL ref vcol1 vcol1 5 const 1
|
|
explain select * from t1 where a+1<=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range vcol1 vcol1 5 NULL 1 Using index condition
|
|
explain select * from t1 where a+1<2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range vcol1 vcol1 5 NULL 1 Using index condition
|
|
explain select * from t1 where a+1>100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range vcol1 vcol1 5 NULL 6 Using index condition
|
|
explain select * from t1 where a+1>=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range vcol1 vcol1 5 NULL 13 Using index condition
|
|
explain select * from t1 where a+1 between 10 and 12;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range vcol1 vcol1 5 NULL 2 Using index condition
|
|
explain select * from t1 where (a+1) IS NULL;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref vcol1 vcol1 5 const 1 Using index condition
|
|
explain select * from t1 force index(vcol1) where (a+1) IS NOT NULL;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range vcol1 vcol1 5 NULL 100 Using index condition
|
|
explain select * from t1 where (a+1) in (1,2,3,4);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range vcol1 vcol1 5 NULL 4 Using index condition
|
|
# Check UPDATE/DELETE:
|
|
explain delete from t1 where a+1=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range vcol1 vcol1 5 NULL 1 Using where
|
|
explain update t1 set a=a+1 where a+1=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range vcol1 vcol1 5 NULL 1 Using where; Using buffer
|
|
# Try merged VIEWs:
|
|
create view v1 as select * from t1;
|
|
explain select * from v1 where a+1=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref vcol1 vcol1 5 const 1
|
|
create view v2 as select a as A_COL from t1;
|
|
explain select * from v2 where A_COL+1=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref vcol1 vcol1 5 const 1
|
|
drop view v1;
|
|
drop view v2;
|
|
set names utf8mb4;
|
|
select @@collation_connection;
|
|
@@collation_connection
|
|
utf8mb4_uca1400_ai_ci
|
|
# Check VARCHAR
|
|
create table t2 (
|
|
a varchar(32),
|
|
vcol1 varchar(32) as (concat('hello-',a)),
|
|
index(vcol1)
|
|
);
|
|
insert into t2 (a) select seq from seq_1_to_100;
|
|
select collation('aaa'), collation(vcol1) from t2 limit 1;
|
|
collation('aaa') collation(vcol1)
|
|
utf8mb4_uca1400_ai_ci utf8mb4_uca1400_ai_ci
|
|
set @tmp_trace=@@optimizer_trace;
|
|
set optimizer_trace=1;
|
|
# This won't work:
|
|
explain select * from t2 where concat('bye-', a)='hello-5';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where
|
|
# This will work:
|
|
explain select * from t2 where concat('hello-', a)='hello-5';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ref vcol1 vcol1 131 const 1 Using index condition
|
|
select
|
|
json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
|
|
from
|
|
information_schema.optimizer_trace;
|
|
json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
|
|
[
|
|
{
|
|
"condition": "WHERE",
|
|
"resulting_condition": "t2.vcol1 = 'hello-5'"
|
|
}
|
|
]
|
|
# Try also ON expressions
|
|
explain
|
|
select *
|
|
from t1 left join t2 on concat('hello-', t2.a)='hello-5'
|
|
where
|
|
t1.a+1=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref vcol1 vcol1 5 const 1
|
|
1 SIMPLE t2 ref vcol1 vcol1 131 const 1 Using where
|
|
select
|
|
json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
|
|
from
|
|
information_schema.optimizer_trace;
|
|
json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
|
|
[
|
|
{
|
|
"condition": "WHERE",
|
|
"resulting_condition": "t1.vcol1 = 2"
|
|
},
|
|
{
|
|
"condition": "ON expression",
|
|
"resulting_condition": "t2.vcol1 = 'hello-5'"
|
|
}
|
|
]
|
|
create table t3 (a int);
|
|
insert into t3 values (1),(2);
|
|
explain
|
|
select *
|
|
from
|
|
t3 left join
|
|
(t1 join t2 on concat('hello-', t2.a)='hello-5' and t1.a+1=2)
|
|
on t3.a<3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 2
|
|
1 SIMPLE t1 ref vcol1 vcol1 5 const 1 Using where
|
|
1 SIMPLE t2 ref vcol1 vcol1 131 const 1 Using where
|
|
select
|
|
json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
|
|
from
|
|
information_schema.optimizer_trace;
|
|
json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
|
|
[
|
|
{
|
|
"condition": "ON expression",
|
|
"resulting_condition": "t3.a < 3 and t2.vcol1 = 'hello-5' and t1.vcol1 = 2"
|
|
}
|
|
]
|
|
drop table t1,t2,t3;
|
|
set optimizer_trace=@tmp_trace;
|
|
#
|
|
# Implicit type/charset conversions
|
|
#
|
|
create table t3 (
|
|
a varchar(32) collate utf8mb4_general_ci,
|
|
vcol1 int as (concat('100',a)),
|
|
vcol2 varchar(32) collate utf8mb4_unicode_ci as (concat('hello-',a)),
|
|
index(vcol1),
|
|
index(vcol2)
|
|
);
|
|
insert into t3 (a) select seq from seq_1_to_100;
|
|
# Type conversion
|
|
explain select * from t3 where concat('100', a)=10010;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 100 Using where
|
|
Warnings:
|
|
Note 1105 Cannot substitute virtual column expression concat('100',`t3`.`a`) -> vcol1 due to type mismatch
|
|
# Character set change
|
|
explain select * from t3 where concat('hello-', a)='abcd';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 100 Using where
|
|
Warnings:
|
|
Note 1105 Cannot substitute virtual column expression concat('hello-',`t3`.`a`) -> vcol2 due to collation mismatch
|
|
drop table t3;
|
|
# Try JSON_EXTRACT
|
|
create table t1 (a int, js1 blob);
|
|
insert into t1
|
|
select seq, concat('{"size":', seq, ', "color":"hue', seq ,'"}') from seq_1_to_100;
|
|
select * from t1 limit 3;
|
|
a js1
|
|
1 {"size":1, "color":"hue1"}
|
|
2 {"size":2, "color":"hue2"}
|
|
3 {"size":3, "color":"hue3"}
|
|
alter table t1 add size1 int as (cast(json_extract(js1, '$.size') as int));
|
|
alter table t1 add index(size1);
|
|
explain select * from t1 where cast(json_extract(js1,'$.size') as int)=5 ;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref size1 size1 5 const 1
|
|
alter table t1 add
|
|
color varchar(100) COLLATE utf8mb3_general_ci
|
|
as (json_unquote(json_extract(js1, '$.color')));
|
|
alter table t1 add index(color);
|
|
select * from t1 limit 3;
|
|
a js1 size1 color
|
|
1 {"size":1, "color":"hue1"} 1 hue1
|
|
2 {"size":2, "color":"hue2"} 2 hue2
|
|
3 {"size":3, "color":"hue3"} 3 hue3
|
|
# Index is used:
|
|
explain select * from t1 where json_unquote(json_extract(js1, '$.color'))='hue5';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref color color 303 const 1 Using index condition
|
|
explain select * from t1 where json_unquote(json_extract(js1, '$.color')) IS NULL;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref color color 303 const 1 Using index condition
|
|
explain select * from t1 force index(color)
|
|
where json_unquote(json_extract(js1, '$.color')) IS NOT NULL;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range color color 303 NULL 100 Using index condition
|
|
alter table t1 drop column color;
|
|
alter table t1 add
|
|
color2 varchar(100)
|
|
as (json_unquote(json_extract(js1, '$.color')));
|
|
alter table t1 add index(color2);
|
|
# Index is not used due to collation mismatch:
|
|
explain select * from t1 where json_unquote(json_extract(js1, '$.color'))='hue5';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
|
|
Warnings:
|
|
Note 1105 Cannot substitute virtual column expression json_unquote(json_extract(`t1`.`js1`,'$.color')) -> color2 due to collation mismatch
|
|
drop table t1;
|
|
#
|
|
# Tests with JSON_VALUE
|
|
#
|
|
create table t1 (a int, js1 json);
|
|
insert into t1
|
|
select seq, concat('{"size":', seq, ', "color":"hue', seq ,'"}') from seq_1_to_100;
|
|
select * from t1 limit 3;
|
|
a js1
|
|
1 {"size":1, "color":"hue1"}
|
|
2 {"size":2, "color":"hue2"}
|
|
3 {"size":3, "color":"hue3"}
|
|
alter table t1
|
|
add size1 int as (cast(json_value(js1, '$.size') as int)),
|
|
add index(size1);
|
|
explain
|
|
select * from t1 where size1=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref size1 size1 5 const 1
|
|
# The "JSON" datatype uses binary collation.
|
|
# JSON_VALUE will produce output in binary collation, too:
|
|
select collation(json_value(js1, '$.color')) from t1 limit 1;
|
|
collation(json_value(js1, '$.color'))
|
|
utf8mb4_bin
|
|
# If one is fine with _bin comparisons, they can use index access:
|
|
alter table t1
|
|
add color1 varchar(100) collate utf8mb4_bin as (json_value(js1, '$.color')),
|
|
add index(color1);
|
|
explain select * from t1 where json_value(js1, '$.color')='hue10';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref color1 color1 403 const 1 Using index condition
|
|
alter table t1 drop column color1;
|
|
# Using different collation in column substitution prevents
|
|
# the optimization from working:
|
|
alter table t1
|
|
add color2 varchar(100) collate utf8mb4_unicode_ci as (json_value(js1, '$.color')),
|
|
add index(color2);
|
|
explain select * from t1 where json_value(js1, '$.color')='hue10';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
|
|
Warnings:
|
|
Note 1105 Cannot substitute virtual column expression json_value(`t1`.`js1`,'$.color') -> color2 due to collation mismatch
|
|
alter table t1 drop column color2;
|
|
# Explicitly specifying the collation helps:
|
|
alter table t1
|
|
add color3 varchar(100) collate utf8mb4_unicode_ci as
|
|
(json_value(js1, '$.color') collate utf8mb4_unicode_ci),
|
|
add index(color3);
|
|
explain select * from t1
|
|
where
|
|
json_value(js1, '$.color') collate utf8mb4_unicode_ci='hue10';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref color3 color3 403 const 1 Using index condition
|
|
#
|
|
# Alternatively, one can store JSON in a column with the same
|
|
# collation as default and then casts are not needed:
|
|
#
|
|
create table t2(
|
|
js1 longtext COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL CHECK (json_valid(`js1`))
|
|
);
|
|
insert into t2 select js1 from t1;
|
|
alter table t2
|
|
add color3 varchar(100) as (json_value(js1, '$.color')),
|
|
add index(color3);
|
|
explain select * from t2
|
|
where
|
|
json_value(js1, '$.color')='hue10';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ref color3 color3 403 const 1 Using index condition
|
|
drop table t1,t2;
|
|
#
|
|
# Test interplay with sargable_casefold optimization:
|
|
#
|
|
create table t1 (
|
|
a varchar(100) collate utf8mb3_general_ci,
|
|
vcol varchar(100) collate utf8mb3_general_ci as (UPPER(a)),
|
|
index(a),
|
|
index(vcol)
|
|
);
|
|
insert into t1 (a) select seq from seq_1_to_100;
|
|
# Note that possible_keys doesn't include 'vcol'.
|
|
# Sargable_casefold is applied before vcol substitution:
|
|
explain select * from t1 where UPPER(a)='abc';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a a 303 const 1 Using index condition
|
|
explain select * from t1 ignore index(vcol) where UPPER(a)='abc';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a a 303 const 1 Using index condition
|
|
explain select * from t1 ignore index(a) where UPPER(a)='abc';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
|
|
set optimizer_switch='sargable_casefold=off';
|
|
explain select * from t1 ignore index(a) where UPPER(a)='abc';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref vcol vcol 303 const 1 Using index condition
|
|
set optimizer_switch=default;
|
|
drop table t1;
|
|
#
|
|
# Test interplay with Sargable YEAR/DATE optimization:
|
|
#
|
|
create table t1 (
|
|
a date,
|
|
vcol int as (year(a)),
|
|
index(a),
|
|
index(vcol)
|
|
);
|
|
insert into t1 (a) select date_add('2024-01-01', interval (seq*365) day) from seq_1_to_100;
|
|
# Note that possible_keys doesn't include 'vcol'.
|
|
# Sargable Year is applied before vcol substitution:
|
|
explain format=json select * from t1 where year(a)=2025;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": 0.002574553,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "range",
|
|
"possible_keys": ["a"],
|
|
"key": "a",
|
|
"key_length": "4",
|
|
"used_key_parts": ["a"],
|
|
"loops": 1,
|
|
"rows": 1,
|
|
"cost": 0.002574553,
|
|
"filtered": 100,
|
|
"index_condition": "t1.a between '2025-01-01' and '2025-12-31'"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
# Check that vcol would work if Sargable Year didn't disable it:
|
|
alter table t1
|
|
add vcol2 int as (year(a)+1),
|
|
add index(vcol2);
|
|
explain format=json select * from t1 where year(a)+1=2025;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": 0.002024411,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ref",
|
|
"possible_keys": ["vcol2"],
|
|
"key": "vcol2",
|
|
"key_length": "5",
|
|
"used_key_parts": ["vcol2"],
|
|
"ref": ["const"],
|
|
"loops": 1,
|
|
"rows": 1,
|
|
"cost": 0.002024411,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
drop table t1;
|
|
#
|
|
# MDEV-35833: Assertion `marked_for_read()' failed for query with vcols
|
|
#
|
|
CREATE TABLE t1 (
|
|
id int,
|
|
v2 int AS (id),
|
|
v3 int AS (id+0),
|
|
a1 int AS (v2 + v3),
|
|
KEY a1 (a1)
|
|
);
|
|
insert t1(id) values (1),(2);
|
|
explain format=json DELETE FROM t1 WHERE v2+v3 > 'a';
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"delete": 1,
|
|
"table_name": "t1",
|
|
"access_type": "range",
|
|
"possible_keys": ["a1"],
|
|
"key": "a1",
|
|
"key_length": "5",
|
|
"used_key_parts": ["a1"],
|
|
"rows": 2,
|
|
"attached_condition": "t1.a1 > 'a'"
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DECIMAL value: 'a'
|
|
drop table t1;
|