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
21create table t1 (a int,
22                 b int as (-a),
23                 c int as (-a) persistent,
24                 index (c));
25insert 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
29create table t2 like t1;
30insert 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
35create table t3 (a int primary key,
36                 b int as (-a),
37		 c int as (-a) persistent unique);
38insert into t3 (a) values (2),(1),(3),(5),(4),(7);
39
40
41--echo # select_type=SIMPLE, type=system
42let $s = select * from t2;
43eval $s;
44eval explain $s;
45
46let $s = select * from t2 where c=-1;
47eval $s;
48eval explain $s;
49
50--echo # select_type=SIMPLE, type=ALL
51let $s = select * from t1 where b=-1;
52eval $s;
53eval explain $s;
54
55--echo # select_type=SIMPLE, type=const
56let $s = select * from t3 where a=1;
57eval $s;
58eval explain $s;
59
60--echo # select_type=SIMPLE, type=range
61let $s = select * from t3 where c>=-1;
62eval $s;
63eval explain $s;
64
65--echo # select_type=SIMPLE, type=ref
66let $s = select * from t1,t3 where t1.c=t3.c and t3.c=-1;
67eval $s;
68eval explain $s;
69
70--echo # select_type=PRIMARY, type=index,ALL
71let $s = select * from t1 where b in (select c from t3);
72eval $s;
73eval explain $s;
74
75--echo # select_type=PRIMARY, type=range,ref
76let $s = select * from t1 where c in (select c from t3 where c between -2 and -1);
77eval $s;
78eval explain $s;
79
80--echo # select_type=UNION, type=system
81--echo # select_type=UNION RESULT, type=<union1,2>
82let $s = select * from t1 union select * from t2;
83eval $s;
84eval explain $s;
85
86--echo # select_type=DERIVED, type=system
87
88set @tmp_optimizer_switch=@@optimizer_switch;
89set optimizer_switch='derived_merge=off,derived_with_keys=off';
90
91let $s = select * from (select a,b,c from t1) as t11;
92eval $s;
93eval explain $s;
94
95set 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
101let $s = select count(*) from t1;
102eval $s;
103eval explain $s;
104
105--echo # SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name
106let $s = select count(distinct a) from t1;
107eval $s;
108eval explain $s;
109
110--echo # SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name
111let $s = select count(distinct b) from t1;
112eval $s;
113eval explain $s;
114
115--echo # SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name
116let $s = select count(distinct c) from t1;
117eval $s;
118eval explain $s;
119
120--echo ###
121--echo ### filesort & range-based utils
122--echo ###
123--echo # SELECT * FROM tbl_name WHERE <vcol expr>
124let $s = select * from t3 where c >= -2;
125eval $s;
126eval explain $s;
127
128--echo # SELECT * FROM tbl_name WHERE <non-vcol expr>
129let $s = select * from t3 where a between 1 and 2;
130eval $s;
131eval explain $s;
132
133--echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr>
134let $s = select * from t3 where b between -2 and -1;
135eval $s;
136eval explain $s;
137
138--echo # SELECT * FROM tbl_name WHERE <indexed vcol expr>
139let $s = select * from t3 where c between -2 and -1;
140eval $s;
141eval 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);
149let $myisam_engine = `SELECT @@session.default_storage_engine='myisam'`;
150if (!$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>
159let $s = select * from t3 where a between 1 and 2 order by c;
160eval $s;
161eval explain $s;
162
163--echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol>
164let $s = select * from t3 where b between -2 and -1 order by a;
165eval $s;
166eval 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);
174let $innodb_engine = `SELECT @@session.default_storage_engine='innodb'`;
175if (!$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>
184let $s = select * from t3 where b between -2 and -1 order by b;
185eval $s;
186eval explain $s;
187
188--echo # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol>
189let $s = select * from t3 where c between -2 and -1 order by b;
190eval $s;
191eval explain $s;
192
193--echo # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol>
194let $s = select * from t3 where b between -2 and -1 order by c;
195eval $s;
196eval explain $s;
197
198--echo # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol>
199let $s = select * from t3 where c between -2 and -1 order by c;
200eval $s;
201eval explain $s;
202
203--echo # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
204let $s = select sum(b) from t1 group by b;
205eval $s;
206eval explain $s;
207
208--echo # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
209let $s = select sum(c) from t1 group by c;
210eval $s;
211eval explain $s;
212
213--echo # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
214let $s = select sum(b) from t1 group by c;
215eval $s;
216eval explain $s;
217
218--echo # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
219let $s = select sum(c) from t1 group by b;
220eval $s;
221eval explain $s;
222
223