362 lines
14 KiB
Plaintext
362 lines
14 KiB
Plaintext
set session internal_tmp_mem_storage_engine='memory';
|
|
# This specifically tests WITH RECURSIVE queries which have
|
|
# required changes in InnoDB (done as WL#9248).
|
|
create table t1(a int);
|
|
insert into t1 values(1),(2),(3);
|
|
# Force InnoDB tmp table, not MEMORY
|
|
set big_tables=1;
|
|
# Problem required this:
|
|
set optimizer_switch='block_nested_loop=off';
|
|
# with one reader, there was no problem:
|
|
with qn as (select * from t1 limit 3) select * from qn;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
# The problems were in the following scenarios.
|
|
# First all writes are done then two readers read
|
|
with qn as (select * from t1 limit 3) select * from qn, qn qn1;
|
|
a a
|
|
1 1
|
|
1 2
|
|
1 3
|
|
2 1
|
|
2 2
|
|
2 3
|
|
3 1
|
|
3 2
|
|
3 3
|
|
# Second, with one reader and one writer (reader is the query block
|
|
# after UNION ALL, writer is the derived materialization code)
|
|
with recursive qn as (
|
|
select 0 as n
|
|
union all
|
|
select n+1 from qn where n<10)
|
|
select * from qn;
|
|
n
|
|
0
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
# With two readers (the two recursive query blocks after UNION ALL)
|
|
# and one writer (the derived materialization code).
|
|
with recursive qn as (
|
|
select 0 as n
|
|
union all
|
|
select 2*n+2 from qn where n<50
|
|
union all
|
|
select 2*n+1 from qn where n<50
|
|
)
|
|
select count(n),max(n) from qn;
|
|
count(n) max(n)
|
|
101 100
|
|
set optimizer_switch=default;
|
|
# Test overflow from MEMORY engine to INNODB:
|
|
set big_tables=1;
|
|
# Set baseline, disk-based results:
|
|
set cte_max_recursion_depth=5000;
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q;
|
|
min(b) max(b) avg(b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Test when conversion to InnoDB affects recursive references which
|
|
# are not open yet (those of q1):
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(q.b),max(q.b),avg(q.b) from q, q as q1;
|
|
min(q.b) max(q.b) avg(q.b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Same, but make q1 the writer; this is to test overflow when
|
|
# the writer isn't first in the 'tmp_tables' list
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(q.b),max(q.b),avg(q.b) from q right join q as q1 on 1;
|
|
min(q.b) max(q.b) avg(q.b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Test when outer query reads CTE with an index.
|
|
# Overflow doesn't happen at same row as queries above, as this
|
|
# table has an index which makes it grow faster.
|
|
explain with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q where b=300;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> NULL ref <auto_key0> <auto_key0> 9 const 1 100.00 Using index
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION q NULL ALL NULL NULL NULL NULL 2 50.00 Recursive; Using where
|
|
Warnings:
|
|
Note 1003 with recursive `q` (`b`) as (/* select#2 */ select 1 AS `1` union all /* select#3 */ select (1 + `q`.`b`) AS `1+b` from `q` where (`q`.`b` < 2000)) /* select#1 */ select min(`q`.`b`) AS `min(b)`,max(`q`.`b`) AS `max(b)`,avg(`q`.`b`) AS `avg(b)` from `q` where (`q`.`b` = 300)
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 2
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q where b=300;
|
|
min(b) max(b) avg(b)
|
|
300 300 300.0000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 3
|
|
# Verify that rows come out in insertion order.
|
|
# If they didn't, the sequences of @c and of 'b'
|
|
# would not be identical and the sum wouldn't be
|
|
# 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000
|
|
set @c:=1;
|
|
flush status;
|
|
with recursive q (b, c) as
|
|
(select 1, 1 union all select (1+b), (@c:=(@c+1)) from q where b<2000)
|
|
select sum(b*c) from q;
|
|
sum(b*c)
|
|
2668667000
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
set big_tables=0;
|
|
# Set different limits, to have different overflow scenarios;
|
|
# we have overflow at record #122, #1481, #937 depending on
|
|
# limits and queries.
|
|
set @@tmp_table_size=1024,@@max_heap_table_size=16384;
|
|
set cte_max_recursion_depth=5000;
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q;
|
|
min(b) max(b) avg(b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Test when conversion to InnoDB affects recursive references which
|
|
# are not open yet (those of q1):
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(q.b),max(q.b),avg(q.b) from q, q as q1;
|
|
min(q.b) max(q.b) avg(q.b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Same, but make q1 the writer; this is to test overflow when
|
|
# the writer isn't first in the 'tmp_tables' list
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(q.b),max(q.b),avg(q.b) from q right join q as q1 on 1;
|
|
min(q.b) max(q.b) avg(q.b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Test when outer query reads CTE with an index.
|
|
# Overflow doesn't happen at same row as queries above, as this
|
|
# table has an index which makes it grow faster.
|
|
explain with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q where b=300;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> NULL ref <auto_key0> <auto_key0> 9 const 1 100.00 Using index
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION q NULL ALL NULL NULL NULL NULL 2 50.00 Recursive; Using where
|
|
Warnings:
|
|
Note 1003 with recursive `q` (`b`) as (/* select#2 */ select 1 AS `1` union all /* select#3 */ select (1 + `q`.`b`) AS `1+b` from `q` where (`q`.`b` < 2000)) /* select#1 */ select min(`q`.`b`) AS `min(b)`,max(`q`.`b`) AS `max(b)`,avg(`q`.`b`) AS `avg(b)` from `q` where (`q`.`b` = 300)
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q where b=300;
|
|
min(b) max(b) avg(b)
|
|
300 300 300.0000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 2
|
|
# Verify that rows come out in insertion order.
|
|
# If they didn't, the sequences of @c and of 'b'
|
|
# would not be identical and the sum wouldn't be
|
|
# 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000
|
|
set @c:=1;
|
|
flush status;
|
|
with recursive q (b, c) as
|
|
(select 1, 1 union all select (1+b), (@c:=(@c+1)) from q where b<2000)
|
|
select sum(b*c) from q;
|
|
sum(b*c)
|
|
2668667000
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
set @@tmp_table_size=30000,@@max_heap_table_size=30000;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect max_heap_table_size value: '30000'
|
|
set cte_max_recursion_depth=5000;
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q;
|
|
min(b) max(b) avg(b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Test when conversion to InnoDB affects recursive references which
|
|
# are not open yet (those of q1):
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(q.b),max(q.b),avg(q.b) from q, q as q1;
|
|
min(q.b) max(q.b) avg(q.b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Same, but make q1 the writer; this is to test overflow when
|
|
# the writer isn't first in the 'tmp_tables' list
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(q.b),max(q.b),avg(q.b) from q right join q as q1 on 1;
|
|
min(q.b) max(q.b) avg(q.b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Test when outer query reads CTE with an index.
|
|
# Overflow doesn't happen at same row as queries above, as this
|
|
# table has an index which makes it grow faster.
|
|
explain with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q where b=300;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> NULL ref <auto_key0> <auto_key0> 9 const 1 100.00 Using index
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION q NULL ALL NULL NULL NULL NULL 2 50.00 Recursive; Using where
|
|
Warnings:
|
|
Note 1003 with recursive `q` (`b`) as (/* select#2 */ select 1 AS `1` union all /* select#3 */ select (1 + `q`.`b`) AS `1+b` from `q` where (`q`.`b` < 2000)) /* select#1 */ select min(`q`.`b`) AS `min(b)`,max(`q`.`b`) AS `max(b)`,avg(`q`.`b`) AS `avg(b)` from `q` where (`q`.`b` = 300)
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q where b=300;
|
|
min(b) max(b) avg(b)
|
|
300 300 300.0000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 2
|
|
# Verify that rows come out in insertion order.
|
|
# If they didn't, the sequences of @c and of 'b'
|
|
# would not be identical and the sum wouldn't be
|
|
# 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000
|
|
set @c:=1;
|
|
flush status;
|
|
with recursive q (b, c) as
|
|
(select 1, 1 union all select (1+b), (@c:=(@c+1)) from q where b<2000)
|
|
select sum(b*c) from q;
|
|
sum(b*c)
|
|
2668667000
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
set @@tmp_table_size=60000,@@max_heap_table_size=60000;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect max_heap_table_size value: '60000'
|
|
set cte_max_recursion_depth=5000;
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q;
|
|
min(b) max(b) avg(b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
# Test when conversion to InnoDB affects recursive references which
|
|
# are not open yet (those of q1):
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(q.b),max(q.b),avg(q.b) from q, q as q1;
|
|
min(q.b) max(q.b) avg(q.b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
# Same, but make q1 the writer; this is to test overflow when
|
|
# the writer isn't first in the 'tmp_tables' list
|
|
flush status;
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(q.b),max(q.b),avg(q.b) from q right join q as q1 on 1;
|
|
min(q.b) max(q.b) avg(q.b)
|
|
1 2000 1000.5000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
# Test when outer query reads CTE with an index.
|
|
# Overflow doesn't happen at same row as queries above, as this
|
|
# table has an index which makes it grow faster.
|
|
explain with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q where b=300;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> NULL ref <auto_key0> <auto_key0> 9 const 1 100.00 Using index
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION q NULL ALL NULL NULL NULL NULL 2 50.00 Recursive; Using where
|
|
Warnings:
|
|
Note 1003 with recursive `q` (`b`) as (/* select#2 */ select 1 AS `1` union all /* select#3 */ select (1 + `q`.`b`) AS `1+b` from `q` where (`q`.`b` < 2000)) /* select#1 */ select min(`q`.`b`) AS `min(b)`,max(`q`.`b`) AS `max(b)`,avg(`q`.`b`) AS `avg(b)` from `q` where (`q`.`b` = 300)
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
with recursive q (b) as
|
|
(select 1 union all select 1+b from q where b<2000)
|
|
select min(b),max(b),avg(b) from q where b=300;
|
|
min(b) max(b) avg(b)
|
|
300 300 300.0000
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 1
|
|
# Verify that rows come out in insertion order.
|
|
# If they didn't, the sequences of @c and of 'b'
|
|
# would not be identical and the sum wouldn't be
|
|
# 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000
|
|
set @c:=1;
|
|
flush status;
|
|
with recursive q (b, c) as
|
|
(select 1, 1 union all select (1+b), (@c:=(@c+1)) from q where b<2000)
|
|
select sum(b*c) from q;
|
|
sum(b*c)
|
|
2668667000
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
show status like 'Created_tmp_disk_tables';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
# cleanup
|
|
drop table t1;
|
|
set session internal_tmp_mem_storage_engine=default;
|