MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
subquery.inc
1 # Test for optimizer tracing of subqueries
2 
3 --source include/have_optimizer_trace.inc
4 --source include/have_64bit.inc
5 
6 SET optimizer_trace_max_mem_size=1048576; # 1MB
7 SET end_markers_in_json=on;
8 SET optimizer_trace="enabled=on,one_line=off";
9 
10 CREATE TABLE t1 (a INT);
11 CREATE TABLE t2 (a INT, b INT);
12 INSERT INTO t1 VALUES (2);
13 INSERT INTO t2 VALUES (1,7),(2,7);
14 
15 --echo # Subselect execute is traced every time it is executed
16 SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
17 SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
18 --echo
19 SELECT * FROM information_schema.OPTIMIZER_TRACE;
20 --echo
21 
22 --echo # Subselect execute is traced only the first time it is executed
23 SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=off";
24 SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
25 --echo
26 SELECT * FROM information_schema.OPTIMIZER_TRACE;
27 --echo
28 
29 DROP TABLE t1,t2;
30 SET @@optimizer_trace_features="default";
31 
32 
33 CREATE TABLE t1 (a FLOAT(5,4) zerofill);
34 CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
35 
36 # evaluate_subselect_cond_steps for build_equal_item()
37 SELECT t1.a
38 FROM t1
39 WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
40  t1.a= (SELECT a FROM t2 LIMIT 1) ;
41 --echo
42 SELECT * FROM information_schema.OPTIMIZER_TRACE;
43 --echo
44 
45 # evaluate_subselect_cond_steps for remove_eq_conds
46 SELECT 1 FROM DUAL
47 WHERE NOT EXISTS
48  (SELECT * FROM t2 WHERE a = 50 AND b = 3);
49 --echo
50 SELECT * FROM information_schema.OPTIMIZER_TRACE;
51 --echo
52 
53 # Distinct, order and group is removed from subquery. Note: For PS,
54 # removal happens during prepare so the only visible effect is that
55 # the subquery does not contain those clauses.
56 SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t2 GROUP BY a ORDER BY b);
57 --echo
58 SELECT * FROM information_schema.OPTIMIZER_TRACE;
59 --echo
60 
61 DROP TABLE t1,t2;
62 
63 --echo #
64 --echo # BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT
65 --echo # DISTINCT/MIN/JOIN/SUBQ QUERY
66 --echo #
67 
68 CREATE TABLE t1 (
69 pk INTEGER,
70 col_int_nokey INTEGER,
71 col_int_key INTEGER,
72 col_varchar_key VARCHAR(1),
73 col_varchar_nokey VARCHAR(1),
74 PRIMARY KEY (pk),
75 KEY (col_varchar_key,col_int_key)
76 ) ENGINE=MYISAM;
77 CREATE TABLE t2 (
78 pk INTEGER,
79 col_int_nokey INTEGER,
80 col_int_key INTEGER,
81 col_varchar_key VARCHAR(1),
82 col_varchar_nokey VARCHAR(1),
83 PRIMARY KEY (pk),
84 KEY (col_varchar_key,col_int_key)
85 ) ENGINE=MYISAM;
86 CREATE TABLE t3 (
87 pk INTEGER,
88 col_int_nokey INTEGER,
89 col_int_key INTEGER,
90 col_time_key TIME,
91 col_datetime_nokey DATETIME,
92 col_varchar_key VARCHAR(1),
93 col_varchar_nokey VARCHAR(1),
94 PRIMARY KEY (pk),
95 KEY (col_time_key),
96 KEY (col_varchar_key,col_int_key)
97 ) ENGINE=MYISAM;
98 CREATE TABLE t4 (
99 pk INTEGER,
100 col_int_nokey INTEGER,
101 col_int_key INTEGER,
102 col_date_key DATE,
103 col_date_nokey DATE,
104 col_time_key TIME,
105 col_time_nokey TIME,
106 col_datetime_key DATETIME,
107 col_datetime_nokey DATETIME,
108 col_varchar_key VARCHAR(1),
109 col_varchar_nokey VARCHAR(1),
110 PRIMARY KEY (pk),
111 KEY (col_varchar_key,col_int_key)
112 ) ENGINE=MYISAM;
113 INSERT INTO t4 (
114 col_int_key,col_int_nokey,
115 col_date_key,col_date_nokey,
116 col_time_key,col_time_nokey,
117 col_datetime_key,col_datetime_nokey,
118 col_varchar_key,col_varchar_nokey
119 ) VALUES
120 (8,7,'2008-10-02','2008-10-02','04:07:22.028954','04:07:22.028954','2001-10-08 00:00:00','2001-10-08 00:00:00','g','g');
121 CREATE TABLE t5 (
122 pk INTEGER AUTO_INCREMENT,
123 col_int_nokey INTEGER,
124 col_int_key INTEGER,
125 col_date_key DATE,
126 col_date_nokey DATE,
127 col_time_key TIME,
128 col_time_nokey TIME,
129 col_datetime_key DATETIME,
130 col_datetime_nokey DATETIME,
131 col_varchar_key VARCHAR(1),
132 col_varchar_nokey VARCHAR(1),
133 PRIMARY KEY (pk),
134 KEY (col_int_key),
135 KEY (col_varchar_key,col_int_key)
136 ) ENGINE=MYISAM;
137 INSERT INTO t5 (
138 col_int_key,col_int_nokey,
139 col_date_key,col_date_nokey,
140 col_time_key,col_time_nokey,
141 col_datetime_key,col_datetime_nokey,
142 col_varchar_key,col_varchar_nokey
143 ) VALUES
144 (8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
145 (7,8,'2008-05-03','2008-05-03','10:19:31.050677','10:19:31.050677','2007-10-06 17:56:40.056051','2007-10-06 17:56:40.056051','d','d'),
146 (8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
147 
148 set @old_opt_switch=@@optimizer_switch;
149 if (`select locate('semijoin', @@optimizer_switch) > 0`)
150 {
151 --disable_query_log
152  set optimizer_switch="semijoin=off";
153 --enable_query_log
154 }
155 
156 select distinct
157 alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as
158 field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1
159 as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on
160 (sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on
161 (sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where
162 exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from (
163 t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where
164 c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3
165 ,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as
166 field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as
167 field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4
168 as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on
169 (alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in
170 ( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1
171 inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on
172 (sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on
173 (sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where
174 sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and
175 sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and
176 alias1.`col_int_key` not in (214) group by field1,field2,field3,
177 field4,field5,field6;
178 
179 --replace_regex /("sort_buffer_size":) [0-9]+/\1 "NNN"/
180 select * from information_schema.optimizer_trace;
181 set optimizer_switch=@old_opt_switch;
182 drop table t1,t2,t3,t4,t5;
183 
184 --echo #
185 --echo # BUG#12905758 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON
186 --echo # SELECT/SUBQ/SUM QUERY
187 --echo #
188 
189 CREATE TABLE t1 (
190 pk INTEGER AUTO_INCREMENT,
191 col_int_nokey INTEGER,
192 col_int_key INTEGER,
193 col_date_key DATE,
194 col_date_nokey DATE,
195 col_time_key TIME,
196 col_time_nokey TIME,
197 col_datetime_key DATETIME,
198 col_datetime_nokey DATETIME,
199 col_varchar_key VARCHAR(1),
200 col_varchar_nokey VARCHAR(1),
201 PRIMARY KEY (pk),
202 KEY (col_varchar_key,col_int_key)
203 ) ENGINE=MYISAM;
204 INSERT INTO t1 (
205 col_int_key,col_int_nokey,
206 col_date_key,col_date_nokey,
207 col_time_key,col_time_nokey,
208 col_datetime_key,col_datetime_nokey,
209 col_varchar_key,col_varchar_nokey
210 ) VALUES
211 (8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
212 (8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
213 CREATE TABLE t2 (I INTEGER);
214 
215 select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from
216 t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2
217 on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where (
218 table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in
219 (192,18) order by field1 desc;
220 
221 select * from information_schema.optimizer_trace;
222 drop table t1,t2;