Debian Codegraph @

mysql-test/suite/vcol/inc/vcol_select.inc

raw
1 ################################################################################
2 # inc/vcol_select.inc #
3 # #
4 # Purpose: #
5 # Testing different SELECTs. #
6 # #
7 # #
8 #------------------------------------------------------------------------------#
9 # Original Author: Andrey Zhakov #
10 # Original Date: 2008-09-18 #
11 # Change Author: Oleksandr Byelkin (Monty program Ab)
12 # Date: 2009-03-24
13 # Change: Syntax changed
14 ################################################################################
15
16 # Table t1 is used below to test:
17 # - Join type of ALL (sequential scan of the entire table)
18 # - Join type of Index
19 # - Join type of Range
20 # - Join type of Ref_or_null
21 create table t1 (a int,
22 b int as (-a),
23 c int as (-a) persistent,
24 index (c));
25 insert into t1 (a) values (2), (1), (1), (3), (NULL);
26
27 # Table t2 is used below to test:
28 # - Join type of system and const
29 create table t2 like t1;
30 insert into t2 (a) values (1);
31
32 # Table t3 is used below to test
33 # - Join type of Eq_ref with a unique virtual column
34 # - Join type of Const
35 create table t3 (a int primary key,
36 b int as (-a),
37 c int as (-a) persistent unique);
38 insert into t3 (a) values (2),(1),(3),(5),(4),(7);
39
40
41 --echo # select_type=SIMPLE, type=system
42 let $s = select * from t2;
43 eval $s;
44 eval explain $s;
45
46 let $s = select * from t2 where c=-1;
47 eval $s;
48 eval explain $s;
49
50 --echo # select_type=SIMPLE, type=ALL
51 let $s = select * from t1 where b=-1;
52 eval $s;
53 eval explain $s;
54
55 --echo # select_type=SIMPLE, type=const
56 let $s = select * from t3 where a=1;
57 eval $s;
58 eval explain $s;
59
60 --echo # select_type=SIMPLE, type=range
61 let $s = select * from t3 where c>=-1;
62 eval $s;
63 eval explain $s;
64
65 --echo # select_type=SIMPLE, type=ref
66 let $s = select * from t1,t3 where t1.c=t3.c and t3.c=-1;
67 eval $s;
68 eval explain $s;
69
70 --echo # select_type=PRIMARY, type=index,ALL
71 let $s = select * from t1 where b in (select c from t3);
72 eval $s;
73 eval explain $s;
74
75 --echo # select_type=PRIMARY, type=range,ref
76 let $s = select * from t1 where c in (select c from t3 where c between -2 and -1);
77 eval $s;
78 eval explain $s;
79
80 --echo # select_type=UNION, type=system
81 --echo # select_type=UNION RESULT, type=<union1,2>
82 let $s = select * from t1 union select * from t2;
83 eval $s;
84 eval explain $s;
85
86 --echo # select_type=DERIVED, type=system
87
88 set @tmp_optimizer_switch=@@optimizer_switch;
89 set optimizer_switch='derived_merge=off,derived_with_keys=off';
90
91 let $s = select * from (select a,b,c from t1) as t11;
92 eval $s;
93 eval explain $s;
94
95 set optimizer_switch=@tmp_optimizer_switch;
96
97 --echo ###
98 --echo ### Using aggregate functions with/without DISTINCT
99 --echo ###
100 --echo # SELECT COUNT(*) FROM tbl_name
101 let $s = select count(*) from t1;
102 eval $s;
103 eval explain $s;
104
105 --echo # SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name
106 let $s = select count(distinct a) from t1;
107 eval $s;
108 eval explain $s;
109
110 --echo # SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name
111 let $s = select count(distinct b) from t1;
112 eval $s;
113 eval explain $s;
114
115 --echo # SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name
116 let $s = select count(distinct c) from t1;
117 eval $s;
118 eval explain $s;
119
120 --echo ###
121 --echo ### filesort & range-based utils
122 --echo ###
123 --echo # SELECT * FROM tbl_name WHERE <vcol expr>
124 let $s = select * from t3 where c >= -2;
125 eval $s;
126 eval explain $s;
127
128 --echo # SELECT * FROM tbl_name WHERE <non-vcol expr>
129 let $s = select * from t3 where a between 1 and 2;
130 eval $s;
131 eval explain $s;
132
133 --echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr>
134 let $s = select * from t3 where b between -2 and -1;
135 eval $s;
136 eval explain $s;
137
138 --echo # SELECT * FROM tbl_name WHERE <indexed vcol expr>
139 let $s = select * from t3 where c between -2 and -1;
140 eval $s;
141 eval explain $s;
142
143 #### Remove for MyISAM due to a bug
144 #### when all the three records are returned (a=1,2,3)
145 #### instead of just two (a=1,2).
146 #### This bug is presumably in base SQL routines as the same happens
147 #### with this table:
148 #### create table t4 (a int primary key, b int, c int unique);
149 let $myisam_engine = `SELECT @@session.default_storage_engine='myisam'`;
150 if (!$myisam_engine)
151 {
152 --echo # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <non-indexed vcol>
153 let $s = select * from t3 where a between 1 and 2 order by b;
154 eval $s;
155 eval explain $s;
156 }
157
158 --echo # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol>
159 let $s = select * from t3 where a between 1 and 2 order by c;
160 eval $s;
161 eval explain $s;
162
163 --echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol>
164 let $s = select * from t3 where b between -2 and -1 order by a;
165 eval $s;
166 eval explain $s;
167
168 #### Remove for MyISAM due to a bug
169 #### when all the three records are returned (a=1,2,3)
170 #### instead of just two (a=1,2).
171 #### This bug is presumably in base SQL routines as the same happens
172 #### with this table:
173 #### create table t4 (a int primary key, b int, c int unique);
174 let $innodb_engine = `SELECT @@session.default_storage_engine='innodb'`;
175 if (!$innodb_engine)
176 {
177 --echo # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-vcol>
178 let $s = select * from t3 where c between -2 and -1 order by a;
179 eval $s;
180 eval explain $s;
181 }
182
183 --echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol>
184 let $s = select * from t3 where b between -2 and -1 order by b;
185 eval $s;
186 eval explain $s;
187
188 --echo # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol>
189 let $s = select * from t3 where c between -2 and -1 order by b;
190 eval $s;
191 eval explain $s;
192
193 --echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol>
194 let $s = select * from t3 where b between -2 and -1 order by c;
195 eval $s;
196 eval explain $s;
197
198 --echo # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol>
199 let $s = select * from t3 where c between -2 and -1 order by c;
200 eval $s;
201 eval explain $s;
202
203 --echo # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
204 let $s = select sum(b) from t1 group by b;
205 eval $s;
206 eval explain $s;
207
208 --echo # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
209 let $s = select sum(c) from t1 group by c;
210 eval $s;
211 eval explain $s;
212
213 --echo # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
214 let $s = select sum(b) from t1 group by c;
215 eval $s;
216 eval explain $s;
217
218 --echo # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
219 let $s = select sum(c) from t1 group by b;
220 eval $s;
221 eval explain $s;
222