1# 2# Test file for WL#1724 (Min/Max Optimization for Queries with Group By Clause). 3# The queries in this file test query execution via QUICK_GROUP_MIN_MAX_SELECT. 4# 5 6--source include/default_optimizer_switch.inc 7--source include/have_innodb.inc 8# 9# TODO: 10# Add queries with: 11# - C != const 12# - C IS NOT NULL 13# - HAVING clause 14 15--disable_warnings 16drop table if exists t1; 17--enable_warnings 18 19create table t1 ( 20 a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' ' 21); 22 23insert into t1 (a1, a2, b, c, d) values 24('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 25('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 26('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 27('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 28('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 29('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 30('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 31('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 32('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 33('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 34('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 35('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 36('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 37('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 38('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 39('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), 40('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 41('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 42('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 43('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 44('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 45('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 46('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 47('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 48('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 49('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 50('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 51('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 52('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 53('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 54('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 55('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); 56insert into t1 select * from t1; 57insert into t1 select * from t1; 58 59create index idx_t1_0 on t1 (a1); 60create index idx_t1_1 on t1 (a1,a2,b,c); 61create index idx_t1_2 on t1 (a1,a2,b); 62analyze table t1; 63 64# t2 is the same as t1, but with some NULLs in the MIN/MAX column, and 65# one more nullable attribute 66 67--disable_warnings 68drop table if exists t2; 69--enable_warnings 70 71create table t2 ( 72 a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' ' 73); 74insert into t2 select * from t1; 75# add few rows with NULL's in the MIN/MAX column 76insert into t2 (a1, a2, b, c, d) values 77('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 78('a','a','a',NULL,'xyz'), 79('a','a','b',NULL,'xyz'), 80('a','b','a',NULL,'xyz'), 81('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 82('d','b','b',NULL,'xyz'), 83('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 84('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'), 85('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 86('a','a','a',NULL,'xyz'), 87('a','a','b',NULL,'xyz'), 88('a','b','a',NULL,'xyz'), 89('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 90('d','b','b',NULL,'xyz'), 91('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 92('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'); 93 94create index idx_t2_0 on t2 (a1); 95create index idx_t2_1 on t2 (a1,a2,b,c); 96create index idx_t2_2 on t2 (a1,a2,b); 97analyze table t2; 98 99# Table t3 is the same as t1, but with smaller column lenghts. 100# This allows to test different branches of the cost computation procedure 101# when the number of keys per block are less than the number of keys in the 102# sub-groups formed by predicates over non-group attributes. 103 104--disable_warnings 105drop table if exists t3; 106--enable_warnings 107 108create table t3 ( 109 a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' ' 110); 111 112insert into t3 (a1, a2, b, c, d) values 113('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 114('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 115('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 116('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 117('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 118('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 119('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 120('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 121('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 122('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 123('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 124('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 125insert into t3 (a1, a2, b, c, d) values 126('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 127('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 128('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 129('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 130('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 131('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 132('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 133('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 134('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 135('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 136('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 137('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 138insert into t3 (a1, a2, b, c, d) values 139('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 140('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 141('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 142('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 143('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 144('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 145('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 146('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 147('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 148('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 149('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 150('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 151insert into t3 (a1, a2, b, c, d) values 152('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 153('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 154('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 155('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 156('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 157('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 158('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 159('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 160('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 161('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 162('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 163('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 164 165create index idx_t3_0 on t3 (a1); 166create index idx_t3_1 on t3 (a1,a2,b,c); 167create index idx_t3_2 on t3 (a1,a2,b); 168analyze table t3; 169 170 171# 172# Queries without a WHERE clause. These queries do not use ranges. 173# 174 175# plans 176explain select a1, min(a2) from t1 group by a1; 177explain select a1, max(a2) from t1 group by a1; 178explain select a1, min(a2), max(a2) from t1 group by a1; 179explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; 180explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; 181--replace_column 7 # 9 # 182explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; 183# Select fields in different order 184explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; 185explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; 186explain select min(a2) from t1 group by a1; 187explain select a2, min(c), max(c) from t1 group by a1,a2,b; 188 189# queries 190select a1, min(a2) from t1 group by a1; 191select a1, max(a2) from t1 group by a1; 192select a1, min(a2), max(a2) from t1 group by a1; 193select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; 194select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; 195select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; 196# Select fields in different order 197select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; 198select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; 199select min(a2) from t1 group by a1; 200select a2, min(c), max(c) from t1 group by a1,a2,b; 201 202# 203# Queries with a where clause 204# 205 206# A) Preds only over the group 'A' attributes 207# plans 208explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 209explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 210explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 211explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 212explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 213explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 214explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 215explain select a1,a2,b, max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 216explain select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 217explain select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 218explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; 219 220--replace_column 9 # 221explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; 222--replace_column 9 # 223explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b; 224--replace_column 9 # 225explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 226--replace_column 9 # 227explain select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 228--replace_column 9 # 229explain select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 230--replace_column 9 # 231explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 232--replace_column 9 # 233explain select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 234--replace_column 9 # 235explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 236--replace_column 9 # 237explain select a1,a2,b, max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 238--replace_column 9 # 239explain select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 240--replace_column 9 # 241explain select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 242--replace_column 9 # 243explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; 244 245# queries 246select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 247select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 248select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 249select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 250select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 251select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 252select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 253select a1,a2,b, max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 254select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 255select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 256select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; 257 258select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; 259select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b; 260select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 261select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 262select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 263select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 264select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 265select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 266select a1,a2,b, max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 267select a1,a2,b,min(c),max(c) from t2 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; 268select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 269select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; 270 271# B) Equalities only over the non-group 'B' attributes 272# plans 273explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 274explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 275explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; 276explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; 277explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; 278 279explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 280explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 281explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; 282explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; 283explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; 284 285# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() 286explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 287explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 288 289# queries 290select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 291select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 292select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; 293select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; 294select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; 295 296select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 297select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 298select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; 299select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; 300select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; 301 302# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() 303select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 304select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 305 306 307# IS NULL (makes sense for t2 only) 308# plans 309explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; 310explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; 311explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; 312explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; 313explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 314explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 315# queries 316select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; 317select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; 318select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; 319select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; 320select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 321select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 322 323# C) Range predicates for the MIN/MAX attribute 324# plans 325--replace_column 9 # 326explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; 327explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; 328explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; 329explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; 330explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; 331explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; 332explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; 333explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; 334explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 335explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 336explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 337explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 338explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 339explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 340explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; 341explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b; 342 343--replace_column 9 # 344explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; 345--replace_column 9 # 346explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; 347--replace_column 9 # 348explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; 349--replace_column 9 # 350explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; 351--replace_column 9 # 352explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; 353--replace_column 9 # 354explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; 355--replace_column 9 # 356explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; 357--replace_column 9 # 358explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; 359--replace_column 9 # 360explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 361--replace_column 9 # 362explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 363--replace_column 9 # 364explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 365--replace_column 9 # 366explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 367--replace_column 9 # 368explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 369--replace_column 9 # 370explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 371--replace_column 9 # 372explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; 373 374# queries 375select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; 376select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; 377select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; 378select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; 379select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; 380select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; 381select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; 382select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; 383select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 384select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 385select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 386select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 387select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 388select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 389select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; 390select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b; 391 392select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; 393select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; 394select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; 395select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; 396select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; 397select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; 398select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; 399select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; 400select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 401select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 402select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 403select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 404select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 405select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 406select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; 407 408# analyze the sub-select 409explain select a1,a2,b,min(c),max(c) from t1 410where exists ( select * from t2 where t2.c = t1.c ) 411group by a1,a2,b; 412 413select a1,a2,b,min(c),max(c) from t1 414where exists ( select * from t2 where t2.c = t1.c ) 415group by a1,a2,b; 416 417# the sub-select is unrelated to MIN/MAX 418explain select a1,a2,b,min(c),max(c) from t1 419where exists ( select * from t2 where t2.c > 'b1' ) 420group by a1,a2,b; 421 422select a1,a2,b,min(c),max(c) from t1 423where exists ( select * from t2 where t2.c > 'b1' ) 424group by a1,a2,b; 425 426# correlated subselect that doesn't reference the min/max argument 427explain select a1,a2,b,c,min(c), max(c) from t1 428where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' ) 429group by a1,a2,b; 430 431select a1,a2,b,c,min(c), max(c) from t1 432where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' ) 433group by a1,a2,b; 434 435SET @save_optimizer_switch=@@optimizer_switch; 436SET optimizer_switch='semijoin_with_cache=off'; 437explain select a1,a2,b,c,min(c), max(c) from t1 438where exists ( select * from t2 439 where t2.c in (select c from t3 where t3.c > t1.b) and 440 t2.c > 'b1' ) 441group by a1,a2,b; 442 443select a1,a2,b,c,min(c), max(c) from t1 444where exists ( select * from t2 445 where t2.c in (select c from t3 where t3.c > t1.b) and 446 t2.c > 'b1' ) 447group by a1,a2,b; 448SET optimizer_switch=@save_optimizer_switch; 449 450# correlated subselect that references the min/max argument 451explain select a1,a2,b,c,min(c), max(c) from t1 452where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' ) 453group by a1,a2,b; 454 455select a1,a2,b,c,min(c), max(c) from t1 456where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' ) 457group by a1,a2,b; 458 459SET @save_optimizer_switch=@@optimizer_switch; 460SET optimizer_switch='semijoin_with_cache=off'; 461explain select a1,a2,b,c,min(c), max(c) from t1 462where exists ( select * from t2 463 where t2.c in (select c from t3 where t3.c > t1.c) and 464 t2.c > 'b1' ) 465group by a1,a2,b; 466 467select a1,a2,b,c,min(c), max(c) from t1 468where exists ( select * from t2 469 where t2.c in (select c from t3 where t3.c > t1.c) and 470 t2.c > 'b1' ) 471group by a1,a2,b; 472SET optimizer_switch=@save_optimizer_switch; 473 474 475# A,B,C) Predicates referencing mixed classes of attributes 476# plans 477explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 478explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 479explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 480explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; 481explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; 482explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 483explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b; 484 485--replace_column 9 # 486explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 487--replace_column 9 # 488explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 489--replace_column 9 # 490explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 491--replace_column 9 # 492explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; 493--replace_column 9 # 494explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; 495--replace_column 9 # 496explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 497 498# queries 499select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 500select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 501select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 502select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; 503select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; 504select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 505select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b; 506 507select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 508select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 509select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 510select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; 511select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; 512select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 513 514 515# 516# GROUP BY queries without MIN/MAX 517# 518 519# plans 520explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 521explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 522explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 523explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 524 525--replace_column 9 # 526explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 527--replace_column 9 # 528explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 529--replace_column 9 # 530explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 531--replace_column 9 # 532explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 533 534# queries 535select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 536select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 537select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 538select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 539 540select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 541select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 542select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 543select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 544 545# 546# DISTINCT queries 547# 548 549# plans 550explain select distinct a1,a2,b from t1; 551explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); 552explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 553explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 554explain select distinct b from t1 where (a2 >= 'b') and (b = 'a'); 555 556--replace_column 9 # 557explain select distinct a1,a2,b from t2; 558--replace_column 9 # 559explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); 560explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 561--replace_column 9 # 562explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 563explain select distinct b from t2 where (a2 >= 'b') and (b = 'a'); 564 565# queries 566select distinct a1,a2,b from t1; 567select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); 568select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 569select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 570select distinct b from t1 where (a2 >= 'b') and (b = 'a'); 571 572select distinct a1,a2,b from t2; 573select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); 574select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 575select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 576select distinct b from t2 where (a2 >= 'b') and (b = 'a'); 577 578# BUG #6303 579select distinct t_00.a1 580from t1 t_00 581where exists ( select * from t2 where a1 = t_00.a1 ); 582 583# BUG #8532 - SELECT DISTINCT a, a causes server to crash 584select distinct a1,a1 from t1; 585select distinct a2,a1,a2,a1 from t1; 586select distinct t1.a1,t2.a1 from t1,t2; 587 588 589# 590# DISTINCT queries with GROUP-BY 591# 592 593# plans 594explain select distinct a1,a2,b from t1; 595explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 596explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 597explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 598explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 599 600--replace_column 9 # 601explain select distinct a1,a2,b from t2; 602--replace_column 9 # 603explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 604--replace_column 9 # 605explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 606--replace_column 9 # 607explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 608--replace_column 9 # 609explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 610 611# queries 612select distinct a1,a2,b from t1; 613select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 614select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 615select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 616select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 617 618select distinct a1,a2,b from t2; 619select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 620select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 621select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 622select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 623 624 625# 626# COUNT (DISTINCT cols) queries 627# 628 629explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); 630explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 631explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 632explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); 633explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); 634 635select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); 636select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 637select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 638select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); 639select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); 640 641# 642# Queries with expressions in the select clause 643# 644 645explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; 646explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; 647explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; 648explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 649explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; 650 651select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; 652select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; 653select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; 654select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 655select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; 656 657 658# 659# Negative examples: queries that should NOT be treated as optimizable by 660# QUICK_GROUP_MIN_MAX_SELECT 661# 662 663# select a non-indexed attribute 664explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b; 665 666explain select a1,a2,b,d from t1 group by a1,a2,b; 667 668# predicate that references an attribute that is after the MIN/MAX argument 669# in the index 670explain extended select a1,a2,min(b),max(b) from t1 671where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2; 672 673# predicate that references a non-indexed attribute 674explain extended select a1,a2,b,min(c),max(c) from t1 675where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b; 676 677explain extended select a1,a2,b,c from t1 678where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c; 679 680# non-equality predicate for a non-group select attribute 681explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; 682explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b; 683 684# non-group field with an equality predicate that references a keypart after the 685# MIN/MAX argument 686explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; 687select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; 688 689# disjunction for a non-group select attribute 690explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1; 691 692# non-range predicate for the MIN/MAX attribute 693explain select a1,a2,b,min(c),max(c) from t2 694where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b; 695 696# not all attributes are indexed by one index 697explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c; 698 699# other aggregate functions than MIN/MAX 700explain select a1,a2,count(a2) from t1 group by a1,a2,b; 701explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; 702explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; 703 704# 705# MDEV-4120 UNIQUE indexes should not be considered for loose index scan 706# 707 708create table t4 as select distinct a1, a2, b, c from t1; 709alter table t4 add unique index idxt4 (a1, a2, b, c); 710 711--echo # This is "superceded" by MDEV-7118, and Loose Index Scan is again an option: 712explain 713select a1, a2, b, min(c) from t4 group by a1, a2, b; 714select a1, a2, b, min(c) from t4 group by a1, a2, b; 715 716drop table t4; 717 718# 719# Bug #16710: select distinct doesn't return all it should 720# 721 722explain select distinct(a1) from t1 where ord(a2) = 98; 723select distinct(a1) from t1 where ord(a2) = 98; 724 725# 726# BUG#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX. 727# 728 729explain select a1 from t1 where a2 = 'b' group by a1; 730select a1 from t1 where a2 = 'b' group by a1; 731 732explain select distinct a1 from t1 where a2 = 'b'; 733select distinct a1 from t1 where a2 = 'b'; 734 735# 736# Bug #12672: primary key implcitly included in every innodb index 737# 738# Test case moved to group_min_max_innodb 739 740 741# 742# Bug #6142: a problem with the empty innodb table 743# 744# Test case moved to group_min_max_innodb 745 746 747# 748# Bug #9798: group by with rollup 749# 750# Test case moved to group_min_max_innodb 751 752 753# 754# Bug #13293 Wrongly used index results in endless loop. 755# 756# Test case moved to group_min_max_innodb 757 758 759drop table t1,t2,t3; 760 761# 762# Bug #14920 Ordering aggregated result sets with composite primary keys 763# corrupts resultset 764# 765create table t1 (c1 int not null,c2 int not null, primary key(c1,c2)); 766insert into t1 (c1,c2) values 767(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9); 768select distinct c1, c2 from t1 order by c2; 769select c1,min(c2) as c2 from t1 group by c1 order by c2; 770select c1,c2 from t1 group by c1,c2 order by c2; 771drop table t1; 772 773# 774# Bug #16203: Analysis for possible min/max optimization erroneously 775# returns impossible range 776# 777 778CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b)); 779INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4); 780OPTIMIZE TABLE t1; 781 782SELECT a FROM t1 WHERE a='AA' GROUP BY a; 783SELECT a FROM t1 WHERE a='BB' GROUP BY a; 784 785EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a; 786EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a; 787 788SELECT DISTINCT a FROM t1 WHERE a='BB'; 789SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%'; 790SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a; 791 792DROP TABLE t1; 793 794 795# 796# Bug #15102: select distinct returns empty result, select count 797# distinct > 0 (correct) 798# 799 800CREATE TABLE t1 ( 801 a int(11) NOT NULL DEFAULT '0', 802 b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '', 803 PRIMARY KEY (a,b) 804 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 805 806delimiter |; 807 808CREATE PROCEDURE a(x INT) 809BEGIN 810 DECLARE rnd INT; 811 DECLARE cnt INT; 812 813 WHILE x > 0 DO 814 SET rnd= x % 100; 815 SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd); 816 INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR)); 817 SET x= x - 1; 818 END WHILE; 819END| 820 821DELIMITER ;| 822 823CALL a(1000); 824 825SELECT a FROM t1 WHERE a=0; 826SELECT DISTINCT a FROM t1 WHERE a=0; 827SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0; 828 829DROP TABLE t1; 830DROP PROCEDURE a; 831 832# 833# Bug #18068: SELECT DISTINCT 834# 835 836CREATE TABLE t1 (a varchar(64) NOT NULL default '', KEY(a)); 837 838INSERT INTO t1 (a) VALUES 839 (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'), 840 ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'), 841 ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); 842INSERT INTO t1 SELECT * FROM t1; 843INSERT INTO t1 SELECT * FROM t1; 844INSERT INTO t1 SELECT * FROM t1; 845ANALYZE TABLE t1; 846 847EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; 848SELECT DISTINCT a,a FROM t1 ORDER BY a; 849 850DROP TABLE t1; 851 852# 853# Bug #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server 854# 855 856CREATE TABLE t1 (id1 INT, id2 INT); 857CREATE TABLE t2 (id2 INT, id3 INT, id5 INT); 858CREATE TABLE t3 (id3 INT, id4 INT); 859CREATE TABLE t4 (id4 INT); 860CREATE TABLE t5 (id5 INT, id6 INT); 861CREATE TABLE t6 (id6 INT); 862 863INSERT INTO t1 VALUES(1,1); 864INSERT INTO t2 VALUES(1,1,1); 865INSERT INTO t3 VALUES(1,1); 866INSERT INTO t4 VALUES(1); 867INSERT INTO t5 VALUES(1,1); 868INSERT INTO t6 VALUES(1); 869 870# original bug query 871SELECT * FROM 872t1 873 NATURAL JOIN 874(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) 875 ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); 876 877# inner join swapped 878SELECT * FROM 879t1 880 NATURAL JOIN 881(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2 882 ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); 883 884# one join less, no ON cond 885SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2); 886 887# wrong error message: 'id2' - ambiguous column 888SELECT * FROM 889(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) 890 ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)) 891 NATURAL JOIN 892t1; 893SELECT * FROM 894(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6))) 895 NATURAL JOIN 896t1; 897 898DROP TABLE t1,t2,t3,t4,t5,t6; 899 900# 901# Bug#22342: No results returned for query using max and group by 902# 903CREATE TABLE t1 (a int, b int, KEY (a,b), KEY b (b)); 904INSERT INTO t1 VALUES 905 (1,1),(1,2),(1,0),(1,3), 906 (1,-1),(1,-2),(1,-3),(1,-4); 907ANALYZE TABLE t1; 908 909explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; 910SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; 911SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a; 912CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); 913INSERT INTO t2 SELECT a,b,b FROM t1; 914explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; 915SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; 916 917DROP TABLE t1,t2; 918 919# 920# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements 921# 922 923CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); 924INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), 925 (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); 926ANALYZE TABLE t1; 927EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; 928FLUSH STATUS; 929SELECT max(b), a FROM t1 GROUP BY a; 930SHOW STATUS LIKE 'handler_read__e%'; 931EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; 932FLUSH STATUS; 933CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a; 934SHOW STATUS LIKE 'handler_read__e%'; 935FLUSH STATUS; 936SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; 937SHOW STATUS LIKE 'handler_read__e%'; 938FLUSH STATUS; 939(SELECT max(b), a FROM t1 GROUP BY a) UNION 940 (SELECT max(b), a FROM t1 GROUP BY a); 941SHOW STATUS LIKE 'handler_read__e%'; 942EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION 943 (SELECT max(b), a FROM t1 GROUP BY a); 944 945EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x 946 FROM t1 AS t1_outer; 947EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS 948 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 949EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 950 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; 951EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 952 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 953EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 954 a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 955EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 956 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 957 AND t1_outer1.b = t1_outer2.b; 958EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x 959 FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; 960 961CREATE TABLE t3 LIKE t1; 962FLUSH STATUS; 963INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; 964SHOW STATUS LIKE 'handler_read__e%'; 965DELETE FROM t3; 966FLUSH STATUS; 967INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) 968 FROM t1 LIMIT 1; 969SHOW STATUS LIKE 'handler_read__e%'; 970FLUSH STATUS; 971DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; 972SHOW STATUS LIKE 'handler_read__e%'; 973FLUSH STATUS; 974--error ER_SUBQUERY_NO_1_ROW 975DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 976 FROM t1) > 10000; 977SHOW STATUS LIKE 'handler_read__e%'; 978 979DROP TABLE t1,t2,t3; 980 981# 982# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint 983# for which loose scan optimization is applied 984# 985 986CREATE TABLE t1 (a int, INDEX idx(a)); 987INSERT INTO t1 VALUES 988 (4), (2), (1), (2), (4), (2), (1), (4), 989 (4), (2), (1), (2), (2), (4), (1), (4); 990ANALYZE TABLE t1; 991 992EXPLAIN SELECT DISTINCT(a) FROM t1; 993SELECT DISTINCT(a) FROM t1; 994EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; 995SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; 996 997DROP TABLE t1; 998 999# 1000# Bug #32268: Indexed queries give bogus MIN and MAX results 1001# 1002 1003CREATE TABLE t1 (a INT, b INT); 1004INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5); 1005INSERT INTO t1 SELECT a + 1, b FROM t1; 1006INSERT INTO t1 SELECT a + 2, b FROM t1; 1007ANALYZE TABLE t1; 1008 1009EXPLAIN 1010SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 1011SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 1012 1013CREATE INDEX break_it ON t1 (a, b); 1014 1015EXPLAIN 1016SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; 1017SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; 1018 1019EXPLAIN 1020SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 1021SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 1022 1023EXPLAIN 1024SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; 1025SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; 1026 1027DROP TABLE t1; 1028 1029# 1030# Bug#38195: Incorrect handling of aggregate functions when loose index scan is 1031# used causes server crash. 1032# 1033create table t1 (a int, b int, key (a,b), key `index` (a,b)) engine=MyISAM; 1034insert into t1 (a,b) values 1035(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6), 1036 (0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13), 1037(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6), 1038 (1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13), 1039(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6), 1040 (2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13), 1041(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6), 1042 (3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13); 1043insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a; 1044select * from t1; 1045explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a; 1046drop table t1; 1047 1048 1049# 1050# Bug #41610: key_infix_len can be overwritten causing some group by queries 1051# to return no rows 1052# 1053 1054CREATE TABLE t1 (a int, b int, c int, d int, 1055 KEY foo (c,d,a,b), KEY bar (c,a,b,d)); 1056 1057INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4); 1058INSERT INTO t1 SELECT * FROM t1; 1059INSERT INTO t1 SELECT * FROM t1; 1060INSERT INTO t1 SELECT a,b,c+1,d FROM t1; 1061ANALYZE TABLE t1; 1062 1063#Should be non-empty 1064EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4; 1065SELECT DISTINCT c FROM t1 WHERE d=4; 1066 1067DROP TABLE t1; 1068 1069--echo # 1070--echo # Bug #45386: Wrong query result with MIN function in field list, 1071--echo # WHERE and GROUP BY clause 1072--echo # 1073 1074CREATE TABLE t (a INT, b INT, INDEX (a,b)); 1075INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); 1076INSERT INTO t SELECT * FROM t; 1077INSERT INTO t SELECT * FROM t; 1078ANALYZE TABLE t; 1079 1080--echo # test MIN 1081--echo #should use range with index for group by 1082EXPLAIN 1083SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; 1084--echo #should return 1 row 1085SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; 1086 1087--echo # test MAX 1088--echo #should use range with index for group by 1089EXPLAIN 1090SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; 1091--echo #should return 1 row 1092SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; 1093 1094--echo # test 3 ranges and use the middle one 1095INSERT INTO t SELECT a, 2 FROM t; 1096 1097--echo #should use range with index for group by 1098EXPLAIN 1099SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; 1100--echo #should return 1 row 1101SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; 1102 1103DROP TABLE t; 1104 1105--echo # 1106--echo # Bug #48472: Loose index scan inappropriately chosen for some WHERE 1107--echo # conditions 1108--echo # 1109 1110CREATE TABLE t (a INT, b INT, INDEX (a,b)); 1111INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); 1112INSERT INTO t SELECT * FROM t; 1113 1114SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a; 1115 1116DROP TABLE t; 1117 1118--echo End of 5.0 tests 1119 1120--echo # 1121--echo # Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in 1122--echo # server crash 1123--echo # 1124 1125CREATE TABLE t (a INT, b INT, INDEX (a,b)); 1126INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); 1127INSERT INTO t SELECT * FROM t; 1128 1129SELECT a, MAX(b) FROM t WHERE b GROUP BY a; 1130 1131DROP TABLE t; 1132 1133# 1134# BUG#49902 - SELECT returns incorrect results 1135# 1136CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b)); 1137INSERT INTO t1 VALUES(1,1),(2,1); 1138ANALYZE TABLE t1; 1139SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b; 1140SELECT a FROM t1 WHERE b=1; 1141DROP TABLE t1; 1142 1143--echo # 1144--echo # Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column 1145--echo # for NULL 1146--echo # 1147 1148--echo ## Test for NULLs allowed 1149CREATE TABLE t1 ( a INT, KEY (a) ); 1150INSERT INTO t1 VALUES (1), (2), (3); 1151--source include/min_null_cond.inc 1152INSERT INTO t1 VALUES (NULL), (NULL); 1153--source include/min_null_cond.inc 1154DROP TABLE t1; 1155 1156--echo ## Test for NOT NULLs 1157CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY); 1158INSERT INTO t1 VALUES (1), (2), (3); 1159--echo # 1160--echo # NULL-safe operator test disabled for non-NULL indexed columns. 1161--echo # 1162--echo # See bugs 1163--echo # 1164--echo # - Bug#52173: Reading NULL value from non-NULL index gives 1165--echo # wrong result in embedded server 1166--echo # 1167--echo # - Bug#52174: Sometimes wrong plan when reading a MAX value from 1168--echo # non-NULL index 1169--echo # 1170--let $skip_null_safe_test= 1 1171--source include/min_null_cond.inc 1172DROP TABLE t1; 1173 1174--echo # 1175--echo # Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at 1176--echo # opt_sum.cc:305 1177--echo # 1178CREATE TABLE t1 ( a INT, KEY (a) ); 1179INSERT INTO t1 VALUES (1), (2), (3); 1180 1181SELECT MIN( a ) AS min_a 1182FROM t1 1183WHERE a > 1 AND a IS NULL 1184ORDER BY min_a; 1185 1186DROP TABLE t1; 1187 1188# 1189# MDEV-729 lp:998028 - Server crashes on normal shutdown in closefrm after executing a query from MyISAM table 1190# 1191create table t1 (a int, b varchar(1), key(b,a)) engine=myisam; 1192insert t1 values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g'),(8,'h'),(null,'i'); 1193select min(a), b from t1 where a=7 or b='z' group by b; 1194flush tables; 1195drop table t1; 1196 1197--echo # 1198--echo # LP BUG#888456 Wrong result with DISTINCT , ANY , subquery_cache=off , NOT NULL 1199--echo # 1200 1201CREATE TABLE t1 ( a int NOT NULL) ; 1202INSERT INTO t1 VALUES (28),(29),(9); 1203 1204CREATE TABLE t2 ( a int, KEY (a)) ; 1205INSERT INTO t2 VALUES (1),(1),(1),(4),(4),(5),(5),(8),(8),(9); 1206 1207explain select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1; 1208select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1; 1209 1210drop table t1, t2; 1211 1212--echo # 1213--echo # LP BUG#900375 Wrong result with derived_merge=ON, DISTINCT or GROUP BY, EXISTS 1214--echo # 1215 1216CREATE TABLE t1 ( a INT, b INT, KEY (b) ); 1217INSERT INTO t1 VALUES 1218(100,10),(101,11),(102,12),(103,13),(104,14), 1219(105,15),(106,16),(107,17),(108,18),(109,19); 1220 1221EXPLAIN 1222SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1 1223WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; 1224SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1 1225WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; 1226 1227EXPLAIN 1228SELECT alias1.* FROM t1, t1 AS alias1 1229WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; 1230SELECT alias1.* FROM t1, t1 AS alias1 1231WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; 1232 1233drop table t1; 1234 1235--echo End of 5.1 tests 1236 1237--echo # 1238--echo # MDEV-765 lp:825075 - Wrong result with GROUP BY + multipart key + MIN/MAX loose scan 1239--echo # 1240 1241CREATE TABLE t1 (a varchar(1), b varchar(1), KEY (b,a)); 1242INSERT INTO t1 VALUES 1243('0',NULL),('9',NULL),('8','c'),('4','d'),('7','d'),(NULL,'f'), 1244('7','f'),('8','g'),(NULL,'j'); 1245 1246explain 1247SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b; 1248SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b; 1249 1250explain 1251SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b; 1252SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b; 1253 1254explain 1255SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b; 1256SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b; 1257 1258explain 1259SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b; 1260SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b; 1261 1262explain 1263SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b; 1264SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b; 1265 1266explain 1267SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b; 1268SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b; 1269 1270drop table t1; 1271 1272--echo # 1273--echo # MDEV-4140 Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery 1274--echo # 1275 1276CREATE TABLE t1 (a int, b int, KEY (b, a)) ; 1277INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0); 1278INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0); 1279CREATE TABLE t2 (c int) ; 1280INSERT INTO t2 VALUES (0),(1); 1281ANALYZE TABLE t1,t2; 1282 1283EXPLAIN 1284SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b; 1285SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b; 1286EXPLAIN 1287SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b; 1288SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b; 1289# this test is for 5.5 to ensure that the subquery is expensive 1290EXPLAIN 1291SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b; 1292SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b; 1293 1294drop table t1, t2; 1295 1296--echo End of 5.3 tests 1297 1298--echo # 1299--echo # WL#3220 (Loose index scan for COUNT DISTINCT) 1300--echo # 1301 1302CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b)); 1303INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1); 1304INSERT INTO t1 SELECT * FROM t1; 1305INSERT INTO t1 SELECT * FROM t1; 1306INSERT INTO t1 SELECT * FROM t1; 1307INSERT INTO t1 SELECT a, b + 4, 1 FROM t1; 1308INSERT INTO t1 SELECT a, b + 8, 1 FROM t1; 1309INSERT INTO t1 SELECT a + 1, b, 1 FROM t1; 1310CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c)); 1311INSERT INTO t2 VALUES 1312 (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1), (1,4,1,1,1,1); 1313INSERT INTO t2 SELECT * FROM t2; 1314INSERT INTO t2 SELECT * FROM t2; 1315INSERT INTO t2 SELECT * FROM t2; 1316INSERT INTO t2 SELECT * FROM t2; 1317INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2; 1318INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2; 1319ANALYZE TABLE t1,t2; 1320 1321EXPLAIN SELECT COUNT(DISTINCT a) FROM t1; 1322SELECT COUNT(DISTINCT a) FROM t1; 1323 1324EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1; 1325SELECT COUNT(DISTINCT a,b) FROM t1; 1326 1327EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1; 1328SELECT COUNT(DISTINCT b,a) FROM t1; 1329 1330EXPLAIN SELECT COUNT(DISTINCT b) FROM t1; 1331SELECT COUNT(DISTINCT b) FROM t1; 1332 1333EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; 1334SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; 1335 1336EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; 1337SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; 1338 1339EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; 1340SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; 1341 1342EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1; 1343SELECT DISTINCT COUNT(DISTINCT a) FROM t1; 1344 1345EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1; 1346SELECT COUNT(DISTINCT a, b + 0) FROM t1; 1347 1348EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; 1349SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; 1350 1351EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; 1352SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; 1353 1354EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 1355SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 1356 1357EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; 1358SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; 1359 1360EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; 1361SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; 1362 1363EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1; 1364SELECT COUNT(DISTINCT a), 12 FROM t1; 1365 1366EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2; 1367SELECT COUNT(DISTINCT a, b, c) FROM t2; 1368 1369EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; 1370SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; 1371 1372EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; 1373SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; 1374 1375EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; 1376SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; 1377 1378EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; 1379SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; 1380 1381EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; 1382SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; 1383 1384EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; 1385SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; 1386 1387EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2 1388 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; 1389SELECT COUNT(DISTINCT c, a, b) FROM t2 1390 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; 1391 1392EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 1393 GROUP BY b; 1394SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 1395 GROUP BY b; 1396 1397EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 1398SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 1399 1400EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 1401SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 1402 1403EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; 1404SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; 1405 1406EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 1407 WHERE b = 13 AND c = 42 GROUP BY a; 1408SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 1409 WHERE b = 13 AND c = 42 GROUP BY a; 1410 1411# This query could have been resolved using loose index scan since the second 1412# part of count(..) is defined by a constant predicate 1413EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; 1414SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; 1415 1416EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; 1417SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; 1418 1419EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 1420SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 1421 1422EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; 1423SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; 1424 1425DROP TABLE t1,t2; 1426 1427--echo # end of WL#3220 tests 1428 1429--echo # 1430--echo # Bug#50539: Wrong result when loose index scan is used for an aggregate 1431--echo # function with distinct 1432--echo # 1433CREATE TABLE t1 ( 1434 f1 int(11) NOT NULL DEFAULT '0', 1435 f2 char(1) NOT NULL DEFAULT '', 1436 KEY (f1,f2) 1437) ; 1438insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), 1439(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D'); 1440 1441SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; 1442explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; 1443 1444drop table t1; 1445--echo # End of test#50539. 1446 1447--echo # 1448--echo # Bug#17217128 - BAD INTERACTION BETWEEN MIN/MAX AND 1449--echo # "HAVING SUM(DISTINCT)": WRONG RESULTS. 1450--echo # 1451 1452CREATE TABLE t (a INT, b INT, KEY(a,b)); 1453INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5); 1454ANALYZE TABLE t; 1455 1456SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; 1457EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; 1458 1459SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; 1460EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; 1461 1462SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); 1463EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); 1464 1465SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; 1466EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; 1467 1468SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; 1469EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; 1470DROP TABLE t; 1471 1472--echo # 1473--echo # MDEV-4219 A simple select query returns random data (upstream bug#68473) 1474--echo # 1475 1476--disable_warnings 1477drop table if exists faulty; 1478--enable_warnings 1479 1480# MySQL's test case 1481 1482CREATE TABLE faulty ( 1483a int(11) unsigned NOT NULL AUTO_INCREMENT, 1484b int(11) unsigned NOT NULL, 1485c datetime NOT NULL, 1486PRIMARY KEY (a), 1487UNIQUE KEY b_and_c (b,c) 1488); 1489 1490INSERT INTO faulty (b, c) VALUES 1491(1801, '2013-02-15 09:00:00'), 1492(1802, '2013-02-28 09:00:00'), 1493(1802, '2013-03-01 09:00:00'), 1494(5, '1990-02-15 09:00:00'), 1495(5, '2013-02-15 09:00:00'), 1496(5, '2009-02-15 17:00:00'); 1497 1498EXPLAIN 1499SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c; 1500SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c; 1501 1502drop table faulty; 1503 1504# MariaDB test case 1505 1506CREATE TABLE t1 (a INT, b INT); 1507INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3); 1508INSERT INTO t1 SELECT * FROM t1; 1509INSERT INTO t1 SELECT a + 1, b FROM t1; 1510INSERT INTO t1 SELECT a + 2, b FROM t1; 1511ANALYZE TABLE t1; 1512 1513CREATE INDEX break_it ON t1 (a, b); 1514 1515EXPLAIN 1516SELECT distinct a, b FROM t1 where a = '3' ORDER BY b; 1517SELECT distinct a, b FROM t1 where a = '3' ORDER BY b; 1518 1519drop table t1; 1520 1521--echo # 1522--echo # Start of 10.0 tests 1523--echo # 1524 1525--echo # 1526--echo # MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases 1527--echo # 1528CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM; 1529INSERT INTO t1 VALUES (1,'2001-01-01'); 1530INSERT INTO t1 VALUES (1,'2001-01-02'); 1531INSERT INTO t1 VALUES (1,'2001-01-03'); 1532INSERT INTO t1 VALUES (1,' 2001-01-04'); 1533INSERT INTO t1 VALUES (2,'2001-01-01'); 1534INSERT INTO t1 VALUES (2,'2001-01-02'); 1535INSERT INTO t1 VALUES (2,'2001-01-03'); 1536INSERT INTO t1 VALUES (2,' 2001-01-04'); 1537INSERT INTO t1 VALUES (3,'2001-01-01'); 1538INSERT INTO t1 VALUES (3,'2001-01-02'); 1539INSERT INTO t1 VALUES (3,'2001-01-03'); 1540INSERT INTO t1 VALUES (3,' 2001-01-04'); 1541INSERT INTO t1 VALUES (4,'2001-01-01'); 1542INSERT INTO t1 VALUES (4,'2001-01-02'); 1543INSERT INTO t1 VALUES (4,'2001-01-03'); 1544INSERT INTO t1 VALUES (4,' 2001-01-04'); 1545SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id; 1546ALTER TABLE t1 ADD KEY(id,a); 1547SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id; 1548DROP TABLE t1; 1549 1550--echo # 1551--echo # MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 1552--echo # and use_stat_tables= PREFERABLY 1553--echo # 1554 1555CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); 1556INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), 1557(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); 1558INSERT INTO t1 SELECT * FROM t1; 1559ANALYZE TABLE t1; 1560set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; 1561set @save_use_stat_tables= @@use_stat_tables; 1562set @@optimizer_use_condition_selectivity=4; 1563set @@use_stat_tables=PREFERABLY; 1564explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); 1565set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1566set @@use_stat_tables=@save_use_stat_tables; 1567explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); 1568drop table t1; 1569 1570--echo # 1571--echo # End of 10.0 tests 1572--echo # 1573 1574 1575--echo # 1576--echo # Start of 10.1 tests 1577--echo # 1578 1579--echo # 1580--echo # MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could 1581--echo # 1582CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) ENGINE=MyISAM; 1583INSERT INTO t1 VALUES (1,'2001-01-01'); 1584INSERT INTO t1 VALUES (1,'2001-01-02'); 1585INSERT INTO t1 VALUES (1,'2001-01-03'); 1586INSERT INTO t1 VALUES (1,'2001-01-04'); 1587INSERT INTO t1 VALUES (2,'2001-01-01'); 1588INSERT INTO t1 VALUES (2,'2001-01-02'); 1589INSERT INTO t1 VALUES (2,'2001-01-03'); 1590INSERT INTO t1 VALUES (2,'2001-01-04'); 1591INSERT INTO t1 VALUES (3,'2001-01-01'); 1592INSERT INTO t1 VALUES (3,'2001-01-02'); 1593INSERT INTO t1 VALUES (3,'2001-01-03'); 1594INSERT INTO t1 VALUES (3,'2001-01-04'); 1595INSERT INTO t1 VALUES (4,'2001-01-01'); 1596INSERT INTO t1 VALUES (4,'2001-01-02'); 1597INSERT INTO t1 VALUES (4,'2001-01-03'); 1598INSERT INTO t1 VALUES (4,'2001-01-04'); 1599ANALYZE TABLE t1; 1600EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id; 1601EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id; 1602EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id; 1603SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id; 1604SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id; 1605SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id; 1606DROP TABLE t1; 1607 1608--echo # 1609--echo # MDEV-8229 GROUP_MIN_MAX is erroneously applied for BETWEEN in some cases 1610--echo # 1611SET NAMES latin1; 1612CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM; 1613INSERT INTO t1 VALUES (1,'2001-01-01'); 1614INSERT INTO t1 VALUES (1,'2001-01-02'); 1615INSERT INTO t1 VALUES (1,'2001-01-03'); 1616INSERT INTO t1 VALUES (1,' 2001-01-04'); 1617INSERT INTO t1 VALUES (2,'2001-01-01'); 1618INSERT INTO t1 VALUES (2,'2001-01-02'); 1619INSERT INTO t1 VALUES (2,'2001-01-03'); 1620INSERT INTO t1 VALUES (2,' 2001-01-04'); 1621INSERT INTO t1 VALUES (3,'2001-01-01'); 1622INSERT INTO t1 VALUES (3,'2001-01-02'); 1623INSERT INTO t1 VALUES (3,'2001-01-03'); 1624INSERT INTO t1 VALUES (3,' 2001-01-04'); 1625INSERT INTO t1 VALUES (4,'2001-01-01'); 1626INSERT INTO t1 VALUES (4,'2001-01-02'); 1627INSERT INTO t1 VALUES (4,'2001-01-03'); 1628INSERT INTO t1 VALUES (4,' 2001-01-04'); 1629INSERT INTO t1 SELECT * FROM t1; 1630INSERT INTO t1 SELECT * FROM t1; 1631ANALYZE TABLE t1; 1632SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN ' 2001-01-04' AND '2001-01-05' GROUP BY id; 1633SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND '2001-01-05' GROUP BY id; 1634SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id; 1635SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND '2001-01-05' GROUP BY id; 1636SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND DATE'2001-01-05' GROUP BY id; 1637ALTER TABLE t1 ADD KEY(id,a); 1638SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN ' 2001-01-04' AND '2001-01-05' GROUP BY id; 1639SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND '2001-01-05' GROUP BY id; 1640SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id; 1641SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND '2001-01-05' GROUP BY id; 1642SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND DATE'2001-01-05' GROUP BY id; 1643EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN ' 2001-01-04' AND '2001-01-05' GROUP BY id; 1644EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND '2001-01-05' GROUP BY id; 1645EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id; 1646EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND '2001-01-05' GROUP BY id; 1647EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND DATE'2001-01-05' GROUP BY id; 1648DROP TABLE t1; 1649 1650--echo # 1651--echo # MIN() optimization didn't work correctly with BETWEEN when using too 1652--echo # long strings. 1653--echo # 1654 1655create table t1 (a varchar(10), key (a)) engine=myisam; 1656insert into t1 values("bar"),("Cafe"); 1657explain select min(a) from t1 where a between "a" and "Cafe2"; 1658explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeeeeeee"; 1659explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2"; 1660drop table t1; 1661 1662--echo # 1663--echo # MDEV-15433: Optimizer does not use group by optimization with distinct 1664--echo # 1665 1666CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); 1667--disable_query_log 1668INSERT INTO t1(a) VALUES (1), (2), (3), (4); 1669INSERT INTO t1(a) SELECT a FROM t1; 1670INSERT INTO t1(a) SELECT a FROM t1; 1671INSERT INTO t1(a) SELECT a FROM t1; 1672INSERT INTO t1(a) SELECT a FROM t1; 1673INSERT INTO t1(a) SELECT a FROM t1; 1674INSERT INTO t1(a) SELECT a FROM t1; 1675INSERT INTO t1(a) SELECT a FROM t1; 1676INSERT INTO t1(a) SELECT a FROM t1; 1677INSERT INTO t1(a) SELECT a FROM t1; 1678INSERT INTO t1(a) SELECT a FROM t1; 1679INSERT INTO t1(a) SELECT a FROM t1; 1680INSERT INTO t1(a) SELECT a FROM t1; 1681INSERT INTO t1(a) SELECT a FROM t1; 1682INSERT INTO t1(a) SELECT a FROM t1; 1683--enable_query_log 1684OPTIMIZE TABLE t1; 1685EXPLAIN SELECT DISTINCT a FROM t1; 1686SELECT DISTINCT a FROM t1; 1687drop table t1; 1688 1689--echo # 1690--echo # End of 10.1 tests 1691--echo # 1692 1693--echo # 1694--echo # MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery 1695--echo # 1696 1697CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM; 1698INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102); 1699--echo # Must not use Using index for group-by 1700explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); 1701SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); 1702DROP TABLE t1; 1703 1704--echo # 1705--echo # MDEV-26585 Wrong query results when `using index for group-by` 1706--echo # 1707 1708CREATE TABLE `t1` ( 1709 `id` int(11) NOT NULL AUTO_INCREMENT, 1710 `owner_id` int(11) DEFAULT NULL, 1711 `foo` tinyint(1) DEFAULT 0, 1712 `whatever` varchar(255) DEFAULT NULL, 1713 PRIMARY KEY (`id`), 1714 KEY `index_t1_on_owner_id_and_foo` (`owner_id`,`foo`) 1715) engine=InnoDB DEFAULT CHARSET=utf8; 1716 1717INSERT INTO t1 (owner_id, foo, whatever) 1718VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"), 1719 (2, TRUE, "yello"), (2, FALSE, "yello"); 1720EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); 1721SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); 1722DROP TABLE t1; 1723