mirror of
https://github.com/MariaDB/server.git
synced 2026-02-22 10:38:40 +01:00
b91a6da292- Note in optimizer trace - Change processor name from intersect_vcol_index_coverings to intersect_field_part_of_key - Iterate order by items using the next field rather than through item index, given this is how it is done elsewhere in the code
95 lines
2.5 KiB
Text
95 lines
2.5 KiB
Text
--echo #
|
|
--echo # MDEV-36132 Optimizer support for functional indexes: handle GROUP/ORDER BY
|
|
--echo #
|
|
|
|
--source include/have_sequence.inc
|
|
create table t (c int, key (c));
|
|
insert into t select seq from seq_1_to_10000;
|
|
alter table t
|
|
add column vc int as (c + 1),
|
|
add index(vc);
|
|
|
|
explain select c from t order by c;
|
|
explain select vc from t order by vc;
|
|
explain select vc from t order by vc limit 10;
|
|
|
|
explain select c + 1 from t order by c + 1;
|
|
explain select c + 1 from t order by vc;
|
|
explain select vc from t order by c + 1;
|
|
|
|
explain select vc from t order by c;
|
|
explain select c from t order by vc;
|
|
|
|
explain select c from t order by c + 1;
|
|
|
|
explain select vc from t order by c + 1 limit 2;
|
|
explain select c + 1 from t order by c + 1 limit 2;
|
|
explain select c + 1 from t order by vc limit 2;
|
|
|
|
## optimizer trace
|
|
|
|
set @old_optimizer_trace=@@optimizer_trace;
|
|
set optimizer_trace=1;
|
|
explain select c + 1 from t order by c + 1;
|
|
select
|
|
json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
|
|
from
|
|
information_schema.optimizer_trace;
|
|
set optimizer_trace=@old_optimizer_trace;
|
|
|
|
drop table t;
|
|
|
|
# vcol on vcol
|
|
|
|
create table t (c int, key (c));
|
|
insert into t select seq from seq_1_to_10000;
|
|
alter table t
|
|
add column vc1 int as (c + 1),
|
|
add index(vc1);
|
|
alter table t
|
|
add column vc2 int as (vc1 * 2),
|
|
add index(vc2);
|
|
explain select c from t order by vc2;
|
|
explain select vc2 from t order by vc1 * 2;
|
|
explain select vc2 from t order by vc1 * 2 limit 2;
|
|
drop table t;
|
|
|
|
# vcol not depending on other col
|
|
|
|
create table t (c int, vc int generated always as (1 + 1) virtual, key (c));
|
|
insert into t values (42, default), (83, default);
|
|
explain select vc from t order by vc;
|
|
select vc from t order by vc;
|
|
drop table t;
|
|
|
|
# multiple indexes
|
|
|
|
create table t (c int);
|
|
insert into t select seq from seq_1_to_10000;
|
|
alter table t
|
|
add column vc1 int as (c + 1);
|
|
alter table t
|
|
add column vc2 int as (1 - c),
|
|
add index(vc1, vc2);
|
|
explain select vc1, vc2 from t order by c + 1, 1 - c;
|
|
drop table t;
|
|
|
|
# multiple items in order by
|
|
|
|
create table t (c int, key (c));
|
|
insert into t select seq from seq_1_to_10000;
|
|
alter table t
|
|
add column vc1 int as (c + 1),
|
|
add index(vc1);
|
|
alter table t
|
|
add column vc2 int as (1 - c),
|
|
add index(vc2);
|
|
set @old_optimizer_trace=@@optimizer_trace;
|
|
set optimizer_trace=1;
|
|
explain select * from t order by c + 1, 1 - c;
|
|
select
|
|
json_detailed(json_extract(trace, '$**.virtual_column_substitution'))
|
|
from
|
|
information_schema.optimizer_trace;
|
|
set optimizer_trace=@old_optimizer_trace;
|
|
drop table t;
|