| 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 |
|