74 lines
2.0 KiB
Plaintext
74 lines
2.0 KiB
Plaintext
--source include/have_64bit.inc
|
|
--source include/no_valgrind_without_big.inc
|
|
|
|
set session internal_tmp_mem_storage_engine='memory';
|
|
|
|
--echo # This specifically tests WITH RECURSIVE queries which have
|
|
--echo # required changes in InnoDB (done as WL#9248).
|
|
|
|
create table t1(a int);
|
|
insert into t1 values(1),(2),(3);
|
|
|
|
--echo # Force InnoDB tmp table, not MEMORY
|
|
set big_tables=1;
|
|
|
|
--echo # Problem required this:
|
|
set optimizer_switch='block_nested_loop=off';
|
|
|
|
--echo # with one reader, there was no problem:
|
|
with qn as (select * from t1 limit 3) select * from qn;
|
|
|
|
--echo # The problems were in the following scenarios.
|
|
|
|
--echo # First all writes are done then two readers read
|
|
|
|
with qn as (select * from t1 limit 3) select * from qn, qn qn1;
|
|
|
|
--echo # Second, with one reader and one writer (reader is the query block
|
|
--echo # 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;
|
|
|
|
--echo # With two readers (the two recursive query blocks after UNION ALL)
|
|
--echo # 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;
|
|
|
|
set optimizer_switch=default;
|
|
|
|
--echo # Test overflow from MEMORY engine to INNODB:
|
|
|
|
set big_tables=1;
|
|
--echo # Set baseline, disk-based results:
|
|
--source include/with_recursive_wl9248.inc
|
|
|
|
set big_tables=0;
|
|
|
|
--echo # Set different limits, to have different overflow scenarios;
|
|
--echo # we have overflow at record #122, #1481, #937 depending on
|
|
--echo # limits and queries.
|
|
|
|
set @@tmp_table_size=1024,@@max_heap_table_size=16384;
|
|
--source include/with_recursive_wl9248.inc
|
|
|
|
set @@tmp_table_size=30000,@@max_heap_table_size=30000;
|
|
--source include/with_recursive_wl9248.inc
|
|
|
|
set @@tmp_table_size=60000,@@max_heap_table_size=60000;
|
|
--source include/with_recursive_wl9248.inc
|
|
|
|
--echo # cleanup
|
|
drop table t1;
|
|
set session internal_tmp_mem_storage_engine=default;
|