Debian Codegraph @

mysql-test/suite/opt_trace/t/temp_table.test

raw
1
2 --source include/have_optimizer_trace.inc
3 --source include/have_64bit.inc
4
5 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
6 --source include/no_protocol.inc
7 --source include/not_hypergraph.inc # Does not output the same optimizer trace.
8
9 SET optimizer_trace_max_mem_size=1048576; # 1MB
10 SET optimizer_trace="enabled=on,one_line=off";
11 SET end_markers_in_json="on";
12
13 CREATE TABLE tmp (
14 pk INT PRIMARY KEY AUTO_INCREMENT,
15 col1 CHAR (1)
16 );
17
18 INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
19
20 CREATE TABLE t1 (
21 uniq VARCHAR(10),
22 col1 VARCHAR(10),
23 col2 VARCHAR(1024)
24 ) CHARSET utf8mb4;
25
26 INSERT INTO t1 SELECT pk, col1, col1 FROM tmp;
27
28 --source include/turn_off_only_full_group_by.inc
29
30 # Uses MyISAM temporary table due to long VARCHAR in GROUP BY clause.
31 SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3;
32 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
33 SELECT * FROM information_schema.OPTIMIZER_TRACE;
34
35 # Uses @internal_tmp_mem_storage_engine temporary table
36 --sorted_result
37 SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ;
38 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
39 SELECT * FROM information_schema.OPTIMIZER_TRACE;
40
41 select @@tmp_table_size;
42 SET @old_size= @@tmp_table_size;
43 SET SESSION tmp_table_size= 1024;
44 SET SESSION internal_tmp_mem_storage_engine='memory';
45
46 INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp;
47
48 # Uses HEAP temporary table. Converts it to MyISAM due to heap size limitation
49 SELECT uniq, col1, col2 FROM t1 GROUP BY uniq;
50 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
51 SELECT * FROM information_schema.OPTIMIZER_TRACE;
52
53 SET SESSION internal_tmp_mem_storage_engine=default;
54 SET GLOBAL tmp_table_size= @old_size;
55
56 # Temp tables for I_S tables. Uses HEAP temporary table.
57 # Converts it to MyISAM packed record format due to heap size limitation
58 SELECT pool_id FROM information_schema.INNODB_BUFFER_PAGE LIMIT 1;
59 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE innodb_buffer_page INNODB_BUFFER_PAGE
60 SELECT * FROM information_schema.OPTIMIZER_TRACE;
61
62 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
63 DROP TABLE t1, tmp;
64
65 --echo #
66 --echo # Bug#17231940: THE OPTIMIZER STILL USES FIXED LENGTH TEMPORARY TABLES
67 --echo # ON DISK
68 --echo #
69 CREATE TABLE t1 (
70 c1 INT AUTO_INCREMENT PRIMARY KEY,
71 c2 VARCHAR(250)
72 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
73
74 INSERT INTO t1(c2) VALUES ('b'),('b');
75 INSERT INTO t1(c2) SELECT t1.c2 FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6;
76
77 SET @@max_heap_table_size=1;
78 SET @@group_concat_max_len= 500;
79 SET SESSION internal_tmp_mem_storage_engine='memory';
80
81 # This query creates 4 tmp tables, 3 of them aren''t packed without this
82 # fix.
83 # Purpose Format without fix / with fix
84 # union result fixed packed
85 # derived tbl result fixed packed
86 # join materialization fixed packed
87 # group_concat internal tbl packed packed (not shown in the opt trace)
88 --sorted_result
89 SELECT c1,GROUP_CONCAT(c2) cc FROM
90 (SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt
91 GROUP BY c1
92 ORDER BY cc;
93 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
94 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
95 SELECT * FROM information_schema.OPTIMIZER_TRACE;
96 DROP TABLE t1;
97 --echo #
98
99 # Clean up.
100 SET SESSION internal_tmp_mem_storage_engine=default;
101
102 --echo #
103 --echo # Bug #30773218 ASSERTION IN SQL_TMP_TABLE.CC::TRACE_TMP_TABLE
104 --echo #
105
106 CREATE TABLE t1(name VARCHAR(64), type TEXT);
107 INSERT INTO t1(name, type) VALUES
108 ('t1', 'table'),
109 ('t2', 'table'),
110 ('t3', 'table'),
111 ('t4', 'table'),
112 ('t5', 'table'),
113 ('t6', 'table'),
114 ('t7', 'table'),
115 ('t8', 'table'),
116 ('t9', 'table'),
117 ('t10', 'table'),
118 ('t11', 'table');
119
120 CREATE TABLE t2(name VARCHAR(64), data TEXT);
121 INSERT INTO t2(name, data) VALUES
122 ('t1', 'data'),
123 ('t2', 'data'),
124 ('t3', 'data'),
125 ('t4', 'data'),
126 ('t5', 'data'),
127 ('t6', 'data'),
128 ('t7', 'data'),
129 ('t8', 'data');
130
131 # We need this combination of settings in order to force a second execution
132 # of the function trace_tmp_table. During join execution, we get
133 # "join_execution":
134 # "subselect_execution":
135 # "materialize":
136 # "converting_tmp_table_to_ondisk":
137 # "cause": "memory_table_size_exceeded",
138 # "tmp_table_info":
139 # At this point we do have a QEP_TAB, but no corresponding JOIN_TAB.
140 # If we use semijoin, no temporary table is instantiated.
141
142 SET SESSION internal_tmp_mem_storage_engine=MEMORY;
143 SET @@session.optimizer_trace='enabled=on';
144 SET @@session.tmp_table_size = 1024;
145 SET @@optimizer_switch='semijoin=off';
146
147 SELECT name, data FROM t2 WHERE name IN
148 ( SELECT name FROM t1 WHERE type='table');
149
150 DROP TABLE t1, t2;
151
152 SET SESSION internal_tmp_mem_storage_engine=default;
153 SET @@session.optimizer_trace='enabled=default';
154 SET @@session.tmp_table_size = default;
155 SET @@optimizer_switch='semijoin=default';