--source include/have_optimizer_trace.inc --source include/have_64bit.inc # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled --source include/no_protocol.inc --source include/not_hypergraph.inc # Does not output the same optimizer trace. SET optimizer_trace_max_mem_size=1048576; # 1MB SET optimizer_trace="enabled=on,one_line=off"; SET end_markers_in_json="on"; CREATE TABLE tmp ( pk INT PRIMARY KEY AUTO_INCREMENT, col1 CHAR (1) ); INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'); CREATE TABLE t1 ( uniq VARCHAR(10), col1 VARCHAR(10), col2 VARCHAR(1024) ) CHARSET utf8mb4; INSERT INTO t1 SELECT pk, col1, col1 FROM tmp; --source include/turn_off_only_full_group_by.inc # Uses MyISAM temporary table due to long VARCHAR in GROUP BY clause. SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE SELECT * FROM information_schema.OPTIMIZER_TRACE; # Uses @internal_tmp_mem_storage_engine temporary table --sorted_result SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE SELECT * FROM information_schema.OPTIMIZER_TRACE; select @@tmp_table_size; SET @old_size= @@tmp_table_size; SET SESSION tmp_table_size= 1024; SET SESSION internal_tmp_mem_storage_engine='memory'; INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp; # Uses HEAP temporary table. Converts it to MyISAM due to heap size limitation SELECT uniq, col1, col2 FROM t1 GROUP BY uniq; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE SELECT * FROM information_schema.OPTIMIZER_TRACE; SET SESSION internal_tmp_mem_storage_engine=default; SET GLOBAL tmp_table_size= @old_size; # Temp tables for I_S tables. Uses HEAP temporary table. # Converts it to MyISAM packed record format due to heap size limitation SELECT pool_id FROM information_schema.INNODB_BUFFER_PAGE LIMIT 1; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE innodb_buffer_page INNODB_BUFFER_PAGE SELECT * FROM information_schema.OPTIMIZER_TRACE; --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc DROP TABLE t1, tmp; --echo # --echo # Bug#17231940: THE OPTIMIZER STILL USES FIXED LENGTH TEMPORARY TABLES --echo # ON DISK --echo # CREATE TABLE t1 ( c1 INT AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(250) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO t1(c2) VALUES ('b'),('b'); INSERT INTO t1(c2) SELECT t1.c2 FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6; SET @@max_heap_table_size=1; SET @@group_concat_max_len= 500; SET SESSION internal_tmp_mem_storage_engine='memory'; # This query creates 4 tmp tables, 3 of them aren''t packed without this # fix. # Purpose Format without fix / with fix # union result fixed packed # derived tbl result fixed packed # join materialization fixed packed # group_concat internal tbl packed packed (not shown in the opt trace) --sorted_result SELECT c1,GROUP_CONCAT(c2) cc FROM (SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt GROUP BY c1 ORDER BY cc; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/ SELECT * FROM information_schema.OPTIMIZER_TRACE; DROP TABLE t1; --echo # # Clean up. SET SESSION internal_tmp_mem_storage_engine=default; --echo # --echo # Bug #30773218 ASSERTION IN SQL_TMP_TABLE.CC::TRACE_TMP_TABLE --echo # CREATE TABLE t1(name VARCHAR(64), type TEXT); INSERT INTO t1(name, type) VALUES ('t1', 'table'), ('t2', 'table'), ('t3', 'table'), ('t4', 'table'), ('t5', 'table'), ('t6', 'table'), ('t7', 'table'), ('t8', 'table'), ('t9', 'table'), ('t10', 'table'), ('t11', 'table'); CREATE TABLE t2(name VARCHAR(64), data TEXT); INSERT INTO t2(name, data) VALUES ('t1', 'data'), ('t2', 'data'), ('t3', 'data'), ('t4', 'data'), ('t5', 'data'), ('t6', 'data'), ('t7', 'data'), ('t8', 'data'); # We need this combination of settings in order to force a second execution # of the function trace_tmp_table. During join execution, we get # "join_execution": # "subselect_execution": # "materialize": # "converting_tmp_table_to_ondisk": # "cause": "memory_table_size_exceeded", # "tmp_table_info": # At this point we do have a QEP_TAB, but no corresponding JOIN_TAB. # If we use semijoin, no temporary table is instantiated. SET SESSION internal_tmp_mem_storage_engine=MEMORY; SET @@session.optimizer_trace='enabled=on'; SET @@session.tmp_table_size = 1024; SET @@optimizer_switch='semijoin=off'; SELECT name, data FROM t2 WHERE name IN ( SELECT name FROM t1 WHERE type='table'); DROP TABLE t1, t2; SET SESSION internal_tmp_mem_storage_engine=default; SET @@session.optimizer_trace='enabled=default'; SET @@session.tmp_table_size = default; SET @@optimizer_switch='semijoin=default';