SET optimizer_trace_max_mem_size=1048576; 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; SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3; uniq col1 1 a 2 b 3 c SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`uniq` AS `uniq`,`t1`.`col1` AS `col1` from `t1` group by `t1`.`col2`,`t1`.`uniq` limit 3" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 8, "cost": 0.25 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 8, "access_type": "scan", "resulting_rows": 8, "cost": 1.05, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 8, "cost_for_plan": 1.05, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_group_by": { "original_clause": "`t1`.`col2`,`t1`.`uniq`", "items": [ { "item": "`t1`.`col2`" }, { "item": "`t1`.`uniq`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`col2`,`t1`.`uniq`" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 1, "write_method": "write_all_rows" } ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "", "in_plan_at_position": 1, "columns": 4, "row_length": 4190, "key_length": 8, "unique_constraint": true, "makes_grouped_rows": true, "cannot_insert_duplicates": false, "location": "TempTable" } /* tmp_table_info */ } /* creating_tmp_table */ }, { "materialize": { "select#": 1, "steps": [ ] /* steps */ } /* materialize */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ; uniq col1 col2 1 a a 2 b b 3 c c 4 d d 5 e e 6 f f 7 g g 8 h h SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT uniq, col1, col2 FROM t1 GROUP BY uniq { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`uniq` AS `uniq`,`t1`.`col1` AS `col1`,`t1`.`col2` AS `col2` from `t1` group by `t1`.`uniq`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 8, "cost": 0.25 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 8, "access_type": "scan", "resulting_rows": 8, "cost": 1.05, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 8, "cost_for_plan": 1.05, "sort_cost": 8, "new_cost_for_plan": 9.05, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_group_by": { "original_clause": "`t1`.`uniq`", "items": [ { "item": "`t1`.`uniq`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`uniq`" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 1, "write_method": "write_all_rows" } ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "", "in_plan_at_position": 1, "columns": 3, "row_length": 4182, "key_length": 43, "unique_constraint": false, "makes_grouped_rows": true, "cannot_insert_duplicates": false, "location": "TempTable" } /* tmp_table_info */ } /* creating_tmp_table */ }, { "materialize": { "select#": 1, "steps": [ ] /* steps */ } /* materialize */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 select @@tmp_table_size; @@tmp_table_size 16777216 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; SELECT uniq, col1, col2 FROM t1 GROUP BY uniq; uniq col1 col2 1 a a 10 b b 11 c c 12 d d 13 e e 14 f f 15 g g 16 h h 2 b b 3 c c 4 d d 5 e e 6 f f 7 g g 8 h h 9 a a SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT uniq, col1, col2 FROM t1 GROUP BY uniq { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`uniq` AS `uniq`,`t1`.`col1` AS `col1`,`t1`.`col2` AS `col2` from `t1` group by `t1`.`uniq`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 16, "cost": 0.25 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 16, "access_type": "scan", "resulting_rows": 16, "cost": 1.85, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 16, "cost_for_plan": 1.85, "sort_cost": 16, "new_cost_for_plan": 17.85, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_group_by": { "original_clause": "`t1`.`uniq`", "items": [ { "item": "`t1`.`uniq`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`uniq`" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 1, "write_method": "write_all_rows" } ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "", "in_plan_at_position": 1, "columns": 3, "row_length": 4182, "key_length": 43, "unique_constraint": false, "makes_grouped_rows": true, "cannot_insert_duplicates": false, "location": "memory (heap)", "row_limit_estimate": 1 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "materialize": { "select#": 1, "steps": [ { "converting_tmp_table_to_ondisk": { "cause": "memory_table_size_exceeded", "tmp_table_info": { "table": "", "in_plan_at_position": 1, "columns": 3, "row_length": 4182, "key_length": 43, "unique_constraint": false, "makes_grouped_rows": true, "cannot_insert_duplicates": false, "location": "disk (TMP_TABLE_ENGINE)", "record_format": "packed" } /* tmp_table_info */ } /* converting_tmp_table_to_ondisk */ } ] /* steps */ } /* materialize */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 SET SESSION internal_tmp_mem_storage_engine=default; SET GLOBAL tmp_table_size= @old_size; SELECT pool_id FROM information_schema.INNODB_BUFFER_PAGE LIMIT 1; pool_id 0 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT pool_id FROM information_schema.INNODB_BUFFER_PAGE LIMIT 1 { "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "INNODB_BUFFER_PAGE", "columns": 21, "row_length": 6863, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "memory (heap)", "row_limit_estimate": 1 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `information_schema`.`INNODB_BUFFER_PAGE`.`POOL_ID` AS `pool_id` from `information_schema`.`INNODB_BUFFER_PAGE` limit 1" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`information_schema`.`INNODB_BUFFER_PAGE`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`information_schema`.`INNODB_BUFFER_PAGE`", "table_scan": { "rows": 2, "cost": 2.525 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`information_schema`.`INNODB_BUFFER_PAGE`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 2, "access_type": "scan", "resulting_rows": 2, "cost": 2.725, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 2, "cost_for_plan": 2.725, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`information_schema`.`INNODB_BUFFER_PAGE`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`information_schema`.`INNODB_BUFFER_PAGE`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "converting_tmp_table_to_ondisk": { "cause": "memory_table_size_exceeded", "tmp_table_info": { "table": "`information_schema`.`INNODB_BUFFER_PAGE`", "in_plan_at_position": 0, "columns": 21, "row_length": 6863, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "disk (TMP_TABLE_ENGINE)", "record_format": "packed" } /* tmp_table_info */ } /* converting_tmp_table_to_ondisk */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 DROP TABLE t1, tmp; # # Bug#17231940: THE OPTIMIZER STILL USES FIXED LENGTH TEMPORARY TABLES # ON DISK # CREATE TABLE t1 ( c1 INT AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(250) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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; Warnings: Warning 1292 Truncated incorrect max_heap_table_size value: '1' SET @@group_concat_max_len= 500; SET SESSION internal_tmp_mem_storage_engine='memory'; SELECT c1,GROUP_CONCAT(c2) cc FROM (SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt GROUP BY c1 ORDER BY cc; c1 cc 1 a,b 10 a,b 11 a,b 12 a,b 13 a,b 14 a,b 15 a,b 16 a,b 17 a,b 18 a,b 19 a,b 2 a,b 20 a,b 21 a,b 22 a,b 23 a,b 24 a,b 25 a,b 26 a,b 27 a,b 28 a,b 29 a,b 3 a,b 30 a,b 31 a,b 32 a,b 33 a,b 34 a,b 35 a,b 36 a,b 37 a,b 38 a,b 39 a,b 4 a,b 40 a,b 41 a,b 42 a,b 43 a,b 44 a,b 45 a,b 46 a,b 47 a,b 48 a,b 49 a,b 5 a,b 50 a,b 51 a,b 52 a,b 53 a,b 54 a,b 55 a,b 56 a,b 57 a,b 58 a,b 59 a,b 6 a,b 60 a,b 61 a,b 62 a,b 63 a,b 64 a,b 65 a,b 66 a,b 7 a,b 8 a,b 9 a,b SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT c1,GROUP_CONCAT(c2) cc FROM (SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt GROUP BY c1 ORDER BY cc { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "join_preparation": { "select#": 3, "steps": [ { "expanded_query": "/* select#3 */ select `t1`.`c1` AS `c1`,'a' AS `a` from `t1`" } ] /* steps */ } /* join_preparation */ }, { "creating_tmp_table": { "tmp_table_info": { "table": "", "columns": 2, "row_length": 757, "key_length": 757, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": true, "location": "memory (heap)", "row_limit_estimate": 1 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "join_preparation": { "select#": 4, "steps": [ { "expanded_query": "/* select#4 */ select `c1` AS `c1`,`c2` AS `c2` from dual" } ] /* steps */ } /* join_preparation */ }, { "derived": { "table": " `tt`", "select#": 2, "materialized": true } /* derived */ }, { "expanded_query": "/* select#1 */ select `tt`.`c1` AS `c1`,group_concat(`tt`.`c2` separator ',') AS `cc` from (/* select#2 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` union /* select#3 */ select `t1`.`c1` AS `c1`,'a' AS `a` from `t1`) `tt` group by `tt`.`c1` order by `cc`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 66, "cost": 0.25 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 66, "access_type": "scan", "resulting_rows": 66, "cost": 6.85, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 66, "cost_for_plan": 6.85, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_optimization": { "select#": 3, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 66, "cost": 0.25 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 66, "access_type": "scan", "resulting_rows": 66, "cost": 6.85, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 66, "cost_for_plan": 6.85, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`t1`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_optimization": { "select#": 4, "steps": [ { "table_dependencies": [ { "table": "``", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "``", "table_scan": { "rows": 0, "cost": 2.5 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "``", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 0, "access_type": "scan", "resulting_rows": 0, "cost": 2.5, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 0, "cost_for_plan": 2.5, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "``", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "``" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": " `tt`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": " `tt`", "table_scan": { "rows": 132, "cost": 4.15 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": " `tt`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 132, "access_type": "scan", "resulting_rows": 132, "cost": 17.35, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 132, "cost_for_plan": 17.35, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": " `tt`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`cc`", "items": [ { "item": "group_concat(`tt`.`c2` separator ',')" } ] /* items */, "resulting_clause_is_simple": false, "resulting_clause": "`cc`" } /* simplifying_order_by */, "simplifying_group_by": { "original_clause": "`tt`.`c1`", "items": [ { "item": "`tt`.`c1`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`tt`.`c1`" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": " `tt`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 1, "filesort": { "adding_sort_to_table": "tt" } /* filesort */, "unknown_key_1": { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "columns": 1, "row_length": 753, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "memory (heap)", "row_limit_estimate": 1 } /* tmp_table_info */ } /* creating_tmp_table */ }, "write_method": "write_group_row_when_complete" }, { "adding_sort_to_table": "" } /* filesort */ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "tt", "in_plan_at_position": 0, "columns": 2, "row_length": 757, "key_length": 757, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": true, "location": "memory (heap)", "row_limit_estimate": 1 } /* tmp_table_info */ } /* creating_tmp_table */ }, { "materialize": { "select#": 2, "steps": [ { "converting_tmp_table_to_ondisk": { "cause": "memory_table_size_exceeded", "tmp_table_info": { "table": "tt", "in_plan_at_position": 0, "columns": 2, "row_length": 757, "key_length": 757, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": true, "location": "disk (TMP_TABLE_ENGINE)", "record_format": "packed" } /* tmp_table_info */ } /* converting_tmp_table_to_ondisk */ } ] /* steps */ } /* materialize */ }, { "materialize": { "select#": 3, "steps": [ ] /* steps */ } /* materialize */ }, { "sorting_table": "tt", "filesort_information": [ { "direction": "asc", "expression": "`tt`.`c1`" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "memory_available": 262144, "key_size": 8, "row_size": 769, "max_rows_per_buffer": 132, "num_rows_estimate": 132, "num_rows_found": 132, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::stable_sort", "sort_mode": "" } /* filesort_summary */ }, { "sorting_table": "", "filesort_information": [ { "direction": "asc", "expression": "`cc`" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "memory_available": 262144, "key_size": 1001, "row_size": 2512, "max_rows_per_buffer": 15, "num_rows_estimate": 15, "num_rows_found": 66, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } /* filesort_summary */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 0 0 DROP TABLE t1; # SET SESSION internal_tmp_mem_storage_engine=default; # # Bug #30773218 ASSERTION IN SQL_TMP_TABLE.CC::TRACE_TMP_TABLE # 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'); 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'); name data t1 data t2 data t3 data t4 data t5 data t6 data t7 data t8 data 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';