mirror of
https://github.com/MariaDB/server.git
synced 2025-02-16 02:15:33 +01:00
a61f0c392c
Estimate_scan_time() calculates the cost of scanning a derivied table.
The old code did not take into account that the temporary table heap table
may be converted to Aria.
Things fixed:
- Added checking if the temporary tables data will fit in the heap.
If not, then calculate the cost based on the designated internal
temporary table engine (Aria).
- Removed MY_MAX(records, 1000) and instead trust the optimizer's
estimate of records. This reduces the cost of temporary tables a bit
for small tables, which caused a few changes in mtr results.
- Fixed cost calculation for HEAP.
- HEAP costs->row_next_find_cost was not set. This does not affect old
costs calculation as this cost slot was not used anywhere.
Now HEAP cost->row_next_find_cost is set, which allowed me to remove
some duplicated computation in ha_heap::scan_time()
794 lines
26 KiB
Text
794 lines
26 KiB
Text
create table t1 (a int, b int);
|
|
insert into t1
|
|
values (1,2), (4,6), (9,7),
|
|
(1,1), (2,5), (7,8);
|
|
create table t2 (a int, b int, c int);
|
|
insert into t2
|
|
values (1,2,3), (5,1,2), (4,3,7),
|
|
(8,9,0), (10,7,1), (5,5,1);
|
|
create table t3 (a int, b varchar(16), index idx(a));
|
|
insert into t3 values
|
|
(1, "abc"), (3, "egh"), (8, "axxx"), (10, "abc"),
|
|
(2, "ccw"), (8, "wqqe"), (7, "au"), (9, "waa"),
|
|
(3, "rass"), (9, "ert"), (9, "lok"), (8, "aww"),
|
|
(1, "todd"), (3, "rew"), (8, "aww"), (3, "sw"),
|
|
(11, "llk"), (7, "rbw"), (1, "sm"), (2, "jyp"),
|
|
(4, "yq"), (5, "pled"), (12, "ligin"), (12, "toww"),
|
|
(6, "mxm"), (15, "wanone"), (9, "sunqq"), (2, "abe");
|
|
# optimization is not used
|
|
select * from t1 where a in (1,2);
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended select * from t1 where a in (1,2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (1,2)
|
|
# set minimum number of values in VALUEs list when optimization works to 2
|
|
set @@in_predicate_conversion_threshold= 2;
|
|
# single IN-predicate in WHERE-part
|
|
select * from t1 where a in (1,2);
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2)) as tvc_0
|
|
);
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended select * from t1 where a in (1,2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`_col_1` = `test`.`t1`.`a`
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2)) as tvc_0
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a`
|
|
select * from t1 where a in (1,2,2,2,3,4,5,6,7);
|
|
a b
|
|
1 2
|
|
4 6
|
|
1 1
|
|
2 5
|
|
7 8
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2),(2),(2),(2),(3),(4),(5),(6),(7)) as tvc_0
|
|
);
|
|
a b
|
|
1 2
|
|
4 6
|
|
1 1
|
|
2 5
|
|
7 8
|
|
explain extended select * from t1 where a in (1,2,2,2,3,4,5,6,7);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2),(2),(2),(3),(4),(5),(6),(7)) `tvc_0` join `test`.`t1` where `tvc_0`.`_col_1` = `test`.`t1`.`a`
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2),(2),(2),(2),(3),(4),(5),(6),(7)) as tvc_0
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2),(2),(2),(2),(3),(4),(5),(6),(7)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a`
|
|
# AND-condition with IN-predicates in WHERE-part
|
|
select * from t1
|
|
where a in (1,2) and
|
|
b in (1,5);
|
|
a b
|
|
1 1
|
|
2 5
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2)) as tvc_0
|
|
)
|
|
and b in
|
|
(
|
|
select *
|
|
from (values (1),(5)) as tvc_1
|
|
);
|
|
a b
|
|
1 1
|
|
2 5
|
|
explain extended select * from t1
|
|
where a in (1,2) and
|
|
b in (1,5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
1 PRIMARY <derived5> eq_ref distinct_key distinct_key 4 test.t1.b 1 100.00
|
|
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(5)) `tvc_1` join (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`_col_1` = `test`.`t1`.`a` and `tvc_1`.`_col_1` = `test`.`t1`.`b`
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2)) as tvc_0
|
|
)
|
|
and b in
|
|
(
|
|
select *
|
|
from (values (1),(5)) as tvc_1
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
1 PRIMARY <derived5> eq_ref distinct_key distinct_key 4 test.t1.b 1 100.00
|
|
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(5)) `tvc_1` join (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a` and `tvc_1`.`1` = `test`.`t1`.`b`
|
|
# subquery with IN-predicate
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select a
|
|
from t2 where b in (3,4)
|
|
);
|
|
a b
|
|
4 6
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select a from t2
|
|
where b in
|
|
(
|
|
select *
|
|
from (values (3),(4)) as tvc_0
|
|
)
|
|
);
|
|
a b
|
|
4 6
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select a
|
|
from t2 where b in (3,4)
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 MATERIALIZED <derived4> eq_ref distinct_key distinct_key 4 test.t2.b 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `tvc_0`.`_col_1` = `test`.`t2`.`b`
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select a from t2
|
|
where b in
|
|
(
|
|
select *
|
|
from (values (3),(4)) as tvc_0
|
|
)
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 MATERIALIZED <derived4> eq_ref distinct_key distinct_key 4 test.t2.b 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `tvc_0`.`3` = `test`.`t2`.`b`
|
|
# derived table with IN-predicate
|
|
select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
) as dr_table;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
) as dr_table;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived4> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`_col_1` = `test`.`t1`.`a`
|
|
explain extended select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived4> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a`
|
|
# non-recursive CTE with IN-predicate
|
|
with tvc_0 as
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
)
|
|
select * from tvc_0;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
) as dr_table;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended with tvc_0 as
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
)
|
|
select * from tvc_0;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived4> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (dual) join `test`.`t1` where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`_col_1` = `test`.`t1`.`a`
|
|
explain extended select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived4> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a`
|
|
# VIEW with IN-predicate
|
|
create view v1 as
|
|
select *
|
|
from t1
|
|
where a in (1,2);
|
|
create view v2 as
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
;
|
|
select * from v1;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
select * from v2;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended select * from v1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived4> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`_col_1` = `test`.`t1`.`a`
|
|
explain extended select * from v2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived4> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a`
|
|
drop view v1,v2;
|
|
# subselect defined by derived table with IN-predicate
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select 1
|
|
from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
)
|
|
as dr_table
|
|
);
|
|
a b
|
|
1 2
|
|
1 1
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select 1
|
|
from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
)
|
|
as dr_table
|
|
);
|
|
a b
|
|
1 2
|
|
1 1
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select 1
|
|
from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
)
|
|
as dr_table
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 MATERIALIZED <derived5> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `tvc_0`.`_col_1` = `test`.`t1`.`a`
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select 1
|
|
from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
)
|
|
as dr_table
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 MATERIALIZED <derived5> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
|
|
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `tvc_0`.`1` = `test`.`t1`.`a`
|
|
# derived table with IN-predicate and group by
|
|
select * from
|
|
(
|
|
select max(a),b
|
|
from t1
|
|
where b in (3,5)
|
|
group by b
|
|
) as dr_table;
|
|
max(a) b
|
|
2 5
|
|
select * from
|
|
(
|
|
select max(a),b
|
|
from t1
|
|
where b in
|
|
(
|
|
select *
|
|
from (values (3),(5))
|
|
as tvc_0
|
|
)
|
|
group by b
|
|
) as dr_table;
|
|
max(a) b
|
|
2 5
|
|
explain extended select * from
|
|
(
|
|
select max(a),b
|
|
from t1
|
|
where b in (3,5)
|
|
group by b
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived4> eq_ref distinct_key distinct_key 4 test.t1.b 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from (values (3),(5)) `tvc_0` join `test`.`t1` where `tvc_0`.`_col_1` = `test`.`t1`.`b` group by `test`.`t1`.`b`) `dr_table`
|
|
explain extended select * from
|
|
(
|
|
select max(a),b
|
|
from t1
|
|
where b in
|
|
(
|
|
select *
|
|
from (values (3),(5))
|
|
as tvc_0
|
|
)
|
|
group by b
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived4> eq_ref distinct_key distinct_key 4 test.t1.b 1 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from (values (3),(5)) `tvc_0` join `test`.`t1` where `tvc_0`.`3` = `test`.`t1`.`b` group by `test`.`t1`.`b`) `dr_table`
|
|
# prepare statement
|
|
prepare stmt from "select * from t1 where a in (1,2)";
|
|
execute stmt;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
execute stmt;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
deallocate prepare stmt;
|
|
# use inside out access from tvc rows
|
|
set @@in_predicate_conversion_threshold= default;
|
|
select * from t3 where a in (1,4);
|
|
a b
|
|
1 abc
|
|
1 todd
|
|
1 sm
|
|
4 yq
|
|
explain extended select * from t3 where a in (1,4);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 range idx idx 5 NULL 4 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4)
|
|
set @@in_predicate_conversion_threshold= 2;
|
|
select * from t3 where a in (1,4);
|
|
a b
|
|
1 abc
|
|
1 todd
|
|
1 sm
|
|
4 yq
|
|
explain extended select * from t3 where a in (1,4);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived3> ALL distinct_key NULL NULL NULL 2 100.00
|
|
1 PRIMARY t3 ref idx idx 5 tvc_0._col_1 3 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from (values (1),(4)) `tvc_0` join `test`.`t3` where `test`.`t3`.`a` = `tvc_0`.`_col_1`
|
|
# use vectors in IN predeicate
|
|
set @@in_predicate_conversion_threshold= 4;
|
|
select * from t1 where (a,b) in ((1,2),(3,4));
|
|
a b
|
|
1 2
|
|
explain extended select * from t1 where (a,b) in ((1,2),(3,4));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (values (1,2),(3,4)) `tvc_0` join `test`.`t1` where `tvc_0`.`_col_1` = `test`.`t1`.`a` and `tvc_0`.`_col_2` = `test`.`t1`.`b`
|
|
set @@in_predicate_conversion_threshold= 2;
|
|
# transformation works for the one IN predicate and doesn't work for the other
|
|
set @@in_predicate_conversion_threshold= 5;
|
|
select * from t2
|
|
where (a,b) in ((1,2),(8,9)) and
|
|
(a,c) in ((1,3),(8,0),(5,1));
|
|
a b c
|
|
1 2 3
|
|
8 9 0
|
|
explain extended select * from t2
|
|
where (a,b) in ((1,2),(8,9)) and
|
|
(a,c) in ((1,3),(8,0),(5,1));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 8 test.t2.a,test.t2.c 1 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from (values (1,3),(8,0),(5,1)) `tvc_0` join `test`.`t2` where `tvc_0`.`_col_1` = `test`.`t2`.`a` and `tvc_0`.`_col_2` = `test`.`t2`.`c` and (`test`.`t2`.`a`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9)))
|
|
set @@in_predicate_conversion_threshold= 2;
|
|
#
|
|
# mdev-14281: conversion of NOT IN predicate into subquery predicate
|
|
#
|
|
select * from t1
|
|
where (a,b) not in ((1,2),(8,9), (5,1));
|
|
a b
|
|
4 6
|
|
9 7
|
|
1 1
|
|
2 5
|
|
7 8
|
|
select * from t1
|
|
where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0);
|
|
a b
|
|
4 6
|
|
9 7
|
|
1 1
|
|
2 5
|
|
7 8
|
|
explain extended select * from t1
|
|
where (a,b) not in ((1,2),(8,9), (5,1));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 DEPENDENT SUBQUERY <derived3> unique_subquery distinct_key distinct_key 8 func,func 1 100.00 Using where; Full scan on NULL key
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`) in <temporary table> on distinct_key where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`)))))
|
|
explain extended select * from t1
|
|
where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 DEPENDENT SUBQUERY <derived3> unique_subquery distinct_key distinct_key 8 func,func 1 100.00 Using where; Full scan on NULL key
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`) in <temporary table> on distinct_key where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`2`)))))
|
|
select * from t1
|
|
where b < 7 and (a,b) not in ((1,2),(8,9), (5,1));
|
|
a b
|
|
4 6
|
|
1 1
|
|
2 5
|
|
explain extended select * from t1
|
|
where b < 7 and (a,b) not in ((1,2),(8,9), (5,1));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 DEPENDENT SUBQUERY <derived3> unique_subquery distinct_key distinct_key 8 func,func 1 100.00 Using where; Full scan on NULL key
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`) in <temporary table> on distinct_key where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`)))))
|
|
select * from t2
|
|
where (a,c) not in ((1,2),(8,9), (5,1));
|
|
a b c
|
|
1 2 3
|
|
5 1 2
|
|
4 3 7
|
|
8 9 0
|
|
10 7 1
|
|
explain extended select * from t2
|
|
where (a,c) not in ((1,2),(8,9), (5,1));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 DEPENDENT SUBQUERY <derived3> unique_subquery distinct_key distinct_key 8 func,func 1 100.00 Using where; Full scan on NULL key
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in <temporary table> on distinct_key where trigcond(<cache>(`test`.`t2`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t2`.`c`) = `tvc_0`.`_col_2`)))))
|
|
drop table t1, t2, t3;
|
|
set @@in_predicate_conversion_threshold= default;
|
|
#
|
|
# MDEV-14947: conversion to TVC with only NULL values
|
|
#
|
|
CREATE TABLE t1 (i INT);
|
|
INSERT INTO t1 VALUES (3), (2), (7);
|
|
SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
|
|
i
|
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL)
|
|
SET in_predicate_conversion_threshold= 5;
|
|
SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
|
|
i
|
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL)
|
|
SET in_predicate_conversion_threshold= default;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-14835: conversion to TVC with BIGINT or YEAR values
|
|
#
|
|
SET @@in_predicate_conversion_threshold= 2;
|
|
CREATE TABLE t1 (a BIGINT);
|
|
CREATE TABLE t2 (y YEAR);
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
INSERT INTO t2 VALUES (2009), (2010), (2011);
|
|
SELECT * FROM t1 WHERE a IN ('1','5','3');
|
|
a
|
|
1
|
|
3
|
|
SELECT * FROM t2 WHERE y IN ('2009','2011');
|
|
y
|
|
2009
|
|
2011
|
|
DROP TABLE t1,t2;
|
|
SET @@in_predicate_conversion_threshold= default;
|
|
#
|
|
# MDEV-17222: conversion to TVC with no names for constants
|
|
# conversion to TVC with the same constants in the first row
|
|
#
|
|
SET @@in_predicate_conversion_threshold= 2;
|
|
CREATE TABLE t1 (f BINARY(16)) ENGINE=MYISAM;
|
|
INSERT INTO t1 VALUES
|
|
(x'BAE56AF2B1C2397D99D58E2A06761DDB'), (x'9B9B698BCCB939EE8F1EA56C1A2E5DAA'),
|
|
(x'A0A1C4FE39A239BABD3E0D8985E6BEA5');
|
|
SELECT COUNT(*) FROM t1 WHERE f IN
|
|
(x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2',
|
|
x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB');
|
|
COUNT(*)
|
|
2
|
|
CREATE TABLE t2 (f1 BINARY(16), f2 BINARY(16)) ENGINE=MYISAM;
|
|
INSERT INTO t2 VALUES
|
|
(x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'),
|
|
(x'86052C062AAF368D84247ED0F6346A70', x'BF5C35045C6037C79E11026ABB9A3A4E');
|
|
SELECT COUNT(*) FROM t2 WHERE (f1,f2) IN
|
|
((x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2'),
|
|
(x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB'),
|
|
(x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'),
|
|
(x'1606014E7C4A312F83EDC9D91BBFCACA', x'33F6068E56FD3A1D8326517F0D81CB5A'));
|
|
COUNT(*)
|
|
1
|
|
CREATE TABLE t3 (f1 int, f2 int) ENGINE=MYISAM;
|
|
INSERT INTO t3 VALUES (2,5), (2,3), (1,2), (7,8), (1,1);
|
|
SELECT * FROM t3 WHERE (f1,f2) IN ((2, 2), (1, 2), (3, 5), (1, 1));
|
|
f1 f2
|
|
1 2
|
|
1 1
|
|
DROP TABLE t1,t2,t3;
|
|
SET @@in_predicate_conversion_threshold= default;
|
|
#
|
|
# MDEV-20900: IN predicate to IN subquery conversion causes performance regression
|
|
#
|
|
create table t1(a int, b int);
|
|
insert into t1 select seq-1, seq-1 from seq_1_to_10;
|
|
set in_predicate_conversion_threshold=2;
|
|
explain select * from t1 where t1.a IN ("1","2","3","4");
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
select * from t1 where t1.a IN ("1","2","3","4");
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
set in_predicate_conversion_threshold=0;
|
|
explain select * from t1 where t1.a IN ("1","2","3","4");
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
select * from t1 where t1.a IN ("1","2","3","4");
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
set in_predicate_conversion_threshold=2;
|
|
explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
set in_predicate_conversion_threshold=0;
|
|
explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
drop table t1;
|
|
SET @@in_predicate_conversion_threshold= default;
|
|
#
|
|
# MDEV-27937: Prepared statement with ? in the list if IN predicate
|
|
#
|
|
set in_predicate_conversion_threshold=2;
|
|
create table t1 (id int, a int, b int);
|
|
insert into t1 values (1,3,30), (2,7,70), (3,1,10);
|
|
prepare stmt from "
|
|
select * from t1 where a in (7, ?, 5, 1);
|
|
";
|
|
execute stmt using 3;
|
|
id a b
|
|
1 3 30
|
|
2 7 70
|
|
3 1 10
|
|
deallocate prepare stmt;
|
|
prepare stmt from "
|
|
select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
|
|
";
|
|
execute stmt using 30;
|
|
id a b
|
|
1 3 30
|
|
2 7 70
|
|
3 1 10
|
|
deallocate prepare stmt;
|
|
drop table t1;
|
|
set in_predicate_conversion_threshold=default;
|
|
# End of 10.3 tests
|
|
#
|
|
# MDEV-32575 MSAN / Valgrind errors in test_if_cheaper_ordering
|
|
# upon reaching in_predicate_conversion_threshold
|
|
#
|
|
create table t1 (a int, b int, c int, primary key (a, b));
|
|
insert into t1 (a, b) values (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
|
|
set in_predicate_conversion_threshold = 2;
|
|
select * from t1 where a in (1, 2) and b = 2 order by a, b;
|
|
a b c
|
|
1 2 NULL
|
|
drop table t1;
|
|
# End of 11.4 tests
|