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