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