# Disable hash join for this test, as it will trigger the new executor.
# The new executor will enable the StreamingIterator in many of the test
# cases, resulting in fewer materializations.
SET optimizer_switch="hash_join=off";
CREATE TABLE t (c VARCHAR(128));
INSERT INTO t VALUES
(REPEAT('a', 128)),
(REPEAT('b', 128)),
(REPEAT('c', 128)),
(REPEAT('d', 128));
ANALYZE TABLE t;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	OK
SHOW STATUS LIKE 'Created_tmp_tables';
Variable_name	Value
Created_tmp_tables	0
SHOW STATUS LIKE 'Created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	0
# ---------------------------------------------------------------------
# Scenario 1: (ENGINE=MEMORY, LIMIT=default, CASE=basic, RESULT=success)
#
SET @@internal_tmp_mem_storage_engine = MEMORY;
SELECT * FROM
t AS t1,
t AS t2,
t AS t3,
t AS t4,
t AS t5,
t AS t6
ORDER BY 1
LIMIT 2;
SELECT @@internal_tmp_mem_storage_engine;
@@internal_tmp_mem_storage_engine
MEMORY
SHOW STATUS LIKE 'Created_tmp_tables';
Variable_name	Value
Created_tmp_tables	1
SHOW STATUS LIKE 'Created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	0
SET @@internal_tmp_mem_storage_engine = default;
# ---------------------------------------------------------------------
# Scenario 2: (ENGINE=MEMORY, LIMIT=default, CASE=no-std-memory, RESULT=ondisk_fallback)
#
SET @@internal_tmp_mem_storage_engine = MEMORY;
SET @@max_heap_table_size = 16384;
SELECT * FROM
t AS t1,
t AS t2,
t AS t3,
t AS t4,
t AS t5,
t AS t6
ORDER BY 1
LIMIT 2;
SELECT @@internal_tmp_mem_storage_engine;
@@internal_tmp_mem_storage_engine
MEMORY
SHOW STATUS LIKE 'Created_tmp_tables';
Variable_name	Value
Created_tmp_tables	2
SHOW STATUS LIKE 'Created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	1
SET @@internal_tmp_mem_storage_engine = default;
SET @@max_heap_table_size = default;
# ---------------------------------------------------------------------
# Scenario 3: (ENGINE=TempTable, LIMIT=default, CASE=basic, RESULT=success)
#
SET @@internal_tmp_mem_storage_engine = TempTable;
SELECT count_alloc
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = 'memory/temptable/physical_disk'
    INTO @id1;
SELECT * FROM
t AS t1,
t AS t2,
t AS t3,
t AS t4,
t AS t5,
t AS t6
ORDER BY 1
LIMIT 2;
SELECT count_alloc
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = 'memory/temptable/physical_disk'
    INTO @id2;
SELECT @@internal_tmp_mem_storage_engine;
@@internal_tmp_mem_storage_engine
TempTable
SHOW STATUS LIKE 'Created_tmp_tables';
Variable_name	Value
Created_tmp_tables	3
SHOW STATUS LIKE 'Created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	1
SELECT (@id1=@id2);
(@id1=@id2)
1
# ---------------------------------------------------------------------
# Scenario 4: (ENGINE=TempTable, LIMIT=default, CASE=no-std-memory, RESULT=use_tempfiles)
#
SET @@internal_tmp_mem_storage_engine = TempTable;
SET GLOBAL temptable_max_ram = 2097152;
SELECT count_alloc
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = 'memory/temptable/physical_disk'
    INTO @id1;
SELECT * FROM
t AS t1,
t AS t2,
t AS t3,
t AS t4,
t AS t5,
t AS t6
ORDER BY 1
LIMIT 2;
SELECT count_alloc
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = 'memory/temptable/physical_disk'
    INTO @id2;
SELECT @@internal_tmp_mem_storage_engine;
@@internal_tmp_mem_storage_engine
TempTable
SHOW STATUS LIKE 'Created_tmp_tables';
Variable_name	Value
Created_tmp_tables	4
SHOW STATUS LIKE 'Created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	1
SELECT (@id1<@id2);
(@id1<@id2)
1
SET @@internal_tmp_mem_storage_engine = default;
SET GLOBAL temptable_max_ram = default;
# ---------------------------------------------------------------------
# Scenario 5: (ENGINE=TempTable, LIMIT=default, CASE=no-disk-memory, RESULT=error)
#
SET @@internal_tmp_mem_storage_engine = TempTable;
SET GLOBAL temptable_max_ram = 2097152;
SET debug = '+d,temptable_fetch_from_disk_return_null';
SELECT * FROM
t AS t1,
t AS t2,
t AS t3,
t AS t4,
t AS t5,
t AS t6
ORDER BY 1
LIMIT 2;
Got one of the listed errors
SELECT @@internal_tmp_mem_storage_engine;
@@internal_tmp_mem_storage_engine
TempTable
SHOW STATUS LIKE 'Created_tmp_tables';
Variable_name	Value
Created_tmp_tables	5
SHOW STATUS LIKE 'Created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	1
SET @@internal_tmp_mem_storage_engine = default;
SET GLOBAL temptable_max_ram = default;
SET debug = '-d,temptable_fetch_from_disk_return_null';
DROP TABLE t;
SET optimizer_switch="hash_join=on";