# 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 utf8mb4_bin 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 403 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 403 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 403 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;