1set @debug_tmp= @@debug_dbug; 2set global debug_dbug="+d,force_group_by"; 3drop table if exists t1; 4create table t1 ( 5a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' ' 6) engine=RocksDB; 7insert into t1 (a1, a2, b, c, d) values 8('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 9('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 10('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 11('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 12('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 13('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 14('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 15('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 16('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 17('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 18('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 19('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 20('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 21('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 22('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 23('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), 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'); 40create index idx_t1_0 on t1 (a1); 41create index idx_t1_1 on t1 (a1,a2,b,c); 42create index idx_t1_2 on t1 (a1,a2,b); 43analyze table t1; 44Table Op Msg_type Msg_text 45test.t1 analyze status OK 46drop table if exists t2; 47create table t2 ( 48a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' ' 49) engine=RocksDB; 50insert into t2 select * from t1; 51insert into t2 (a1, a2, b, c, d) values 52('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 53('a','a','a',NULL,'xyz'), 54('a','a','b',NULL,'xyz'), 55('a','b','a',NULL,'xyz'), 56('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 57('d','b','b',NULL,'xyz'), 58('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 59('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'), 60('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), 61('a','a','a',NULL,'xyz'), 62('a','a','b',NULL,'xyz'), 63('a','b','a',NULL,'xyz'), 64('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), 65('d','b','b',NULL,'xyz'), 66('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), 67('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'); 68create index idx_t2_0 on t2 (a1); 69create index idx_t2_1 on t2 (a1,a2,b,c); 70create index idx_t2_2 on t2 (a1,a2,b); 71analyze table t2; 72Table Op Msg_type Msg_text 73test.t2 analyze status OK 74drop table if exists t3; 75create table t3 ( 76a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' ' 77) engine=RocksDB; 78insert into t3 (a1, a2, b, c, d) values 79('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 80('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 81('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 82('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 83('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 84('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 85('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 86('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 87('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 88('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 89('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 90('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 91insert into t3 (a1, a2, b, c, d) values 92('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 93('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 94('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 95('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 96('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 97('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 98('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 99('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 100('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 101('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 102('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 103('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 104insert into t3 (a1, a2, b, c, d) values 105('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 106('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 107('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 108('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 109('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 110('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 111('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 112('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 113('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 114('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 115('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 116('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 117insert into t3 (a1, a2, b, c, d) values 118('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 119('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 120('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 121('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 122('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 123('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 124('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 125('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 126('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 127('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 128('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 129('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); 130create index idx_t3_0 on t3 (a1); 131create index idx_t3_1 on t3 (a1,a2,b,c); 132create index idx_t3_2 on t3 (a1,a2,b); 133analyze table t3; 134Table Op Msg_type Msg_text 135test.t3 analyze status OK 136explain select a1, min(a2) from t1 group by a1; 137id select_type table type possible_keys key key_len ref rows Extra 1381 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using index for group-by 139explain select a1, max(a2) from t1 group by a1; 140id select_type table type possible_keys key key_len ref rows Extra 1411 SIMPLE t1 range NULL idx_t1_1 65 NULL 63 Using index for group-by 142explain select a1, min(a2), max(a2) from t1 group by a1; 143id select_type table type possible_keys key key_len ref rows Extra 1441 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using index for group-by 145explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; 146id select_type table type possible_keys key key_len ref rows Extra 1471 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by 148explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; 149id select_type table type possible_keys key key_len ref rows Extra 1501 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by 151explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; 152id select_type table type possible_keys key key_len ref rows Extra 1531 SIMPLE t2 range NULL idx_t2_1 # NULL # Using index for group-by 154explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; 155id select_type table type possible_keys key key_len ref rows Extra 1561 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using index for group-by 157explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; 158id select_type table type possible_keys key key_len ref rows Extra 1591 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by 160explain select min(a2) from t1 group by a1; 161id select_type table type possible_keys key key_len ref rows Extra 1621 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using index for group-by 163explain select a2, min(c), max(c) from t1 group by a1,a2,b; 164id select_type table type possible_keys key key_len ref rows Extra 1651 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by 166select a1, min(a2) from t1 group by a1; 167a1 min(a2) 168a a 169b a 170c a 171d a 172select a1, max(a2) from t1 group by a1; 173a1 max(a2) 174a b 175b b 176c b 177d b 178select a1, min(a2), max(a2) from t1 group by a1; 179a1 min(a2) max(a2) 180a a b 181b a b 182c a b 183d a b 184select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; 185a1 a2 b min(c) max(c) 186a a a a111 d111 187a a b e112 h112 188a b a i121 l121 189a b b m122 p122 190b a a a211 d211 191b a b e212 h212 192b b a i221 l221 193b b b m222 p222 194c a a a311 d311 195c a b e312 h312 196c b a i321 l321 197c b b m322 p322 198d a a a411 d411 199d a b e412 h412 200d b a i421 l421 201d b b m422 p422 202select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; 203a1 a2 b max(c) min(c) 204a a a d111 a111 205a a b h112 e112 206a b a l121 i121 207a b b p122 m122 208b a a d211 a211 209b a b h212 e212 210b b a l221 i221 211b b b p222 m222 212c a a d311 a311 213c a b h312 e312 214c b a l321 i321 215c b b p322 m322 216d a a d411 a411 217d a b h412 e412 218d b a l421 i421 219d b b p422 m422 220select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; 221a1 a2 b max(c) min(c) 222a a NULL a999 a777 223a a a d111 a111 224a a b h112 e112 225a b a l121 i121 226a b b p122 m122 227b a a d211 a211 228b a b h212 e212 229b b a l221 i221 230b b b p222 m222 231c a NULL c999 c777 232c a a d311 a311 233c a b h312 e312 234c b a l321 i321 235c b b p322 m322 236d a a d411 a411 237d a b h412 e412 238d b a l421 i421 239d b b p422 m422 240e a a NULL NULL 241e a b NULL NULL 242select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; 243min(a2) a1 max(a2) min(a2) a1 244a a b a a 245a b b a b 246a c b a c 247a d b a d 248select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; 249a1 b min(c) a1 max(c) b a2 max(c) max(c) 250a a a111 a d111 a a d111 d111 251a b e112 a h112 b a h112 h112 252a a i121 a l121 a b l121 l121 253a b m122 a p122 b b p122 p122 254b a a211 b d211 a a d211 d211 255b b e212 b h212 b a h212 h212 256b a i221 b l221 a b l221 l221 257b b m222 b p222 b b p222 p222 258c a a311 c d311 a a d311 d311 259c b e312 c h312 b a h312 h312 260c a i321 c l321 a b l321 l321 261c b m322 c p322 b b p322 p322 262d a a411 d d411 a a d411 d411 263d b e412 d h412 b a h412 h412 264d a i421 d l421 a b l421 l421 265d b m422 d p422 b b p422 p422 266select min(a2) from t1 group by a1; 267min(a2) 268a 269a 270a 271a 272select a2, min(c), max(c) from t1 group by a1,a2,b; 273a2 min(c) max(c) 274a a111 d111 275a e112 h112 276b i121 l121 277b m122 p122 278a a211 d211 279a e212 h212 280b i221 l221 281b m222 p222 282a a311 d311 283a e312 h312 284b i321 l321 285b m322 p322 286a a411 d411 287a e412 h412 288b i421 l421 289b m422 p422 290explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 291id select_type table type possible_keys key key_len ref rows Extra 2921 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 293explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 294id select_type table type possible_keys key key_len ref rows Extra 2951 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 296explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 297id select_type table type possible_keys key key_len ref rows Extra 2981 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 299explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 300id select_type table type possible_keys key key_len ref rows Extra 3011 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 302explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 303id select_type table type possible_keys key key_len ref rows Extra 3041 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 305explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 306id select_type table type possible_keys key key_len ref rows Extra 3071 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 308explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 309id select_type table type possible_keys key key_len ref rows Extra 3101 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 311explain 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; 312id select_type table type possible_keys key key_len ref rows Extra 3131 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 314explain 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; 315id select_type table type possible_keys key key_len ref rows Extra 3161 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 317explain select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 318id select_type table type possible_keys key key_len ref rows Extra 3191 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 320explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; 321id select_type table type possible_keys key key_len ref rows Extra 3221 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 323explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; 324id select_type table type possible_keys key key_len ref rows Extra 3251 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 326explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b; 327id select_type table type possible_keys key key_len ref rows Extra 3281 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 329explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 330id select_type table type possible_keys key key_len ref rows Extra 3311 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 332explain select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 333id select_type table type possible_keys key key_len ref rows Extra 3341 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 335explain select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 336id select_type table type possible_keys key key_len ref rows Extra 3371 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 338explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 339id select_type table type possible_keys key key_len ref rows Extra 3401 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 341explain select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 342id select_type table type possible_keys key key_len ref rows Extra 3431 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 344explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 345id select_type table type possible_keys key key_len ref rows Extra 3461 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 347explain 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; 348id select_type table type possible_keys key key_len ref rows Extra 3491 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 350explain 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; 351id select_type table type possible_keys key key_len ref rows Extra 3521 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 353explain select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 354id select_type table type possible_keys key key_len ref rows Extra 3551 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 356explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; 357id select_type table type possible_keys key key_len ref rows Extra 3581 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 359select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 360a1 a2 b min(c) max(c) 361a a a a111 d111 362a a b e112 h112 363a b a i121 l121 364a b b m122 p122 365b a a a211 d211 366b a b e212 h212 367b b a i221 l221 368b b b m222 p222 369c a a a311 d311 370c a b e312 h312 371c b a i321 l321 372c b b m322 p322 373select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 374a1 a2 b min(c) max(c) 375b a a a211 d211 376b a b e212 h212 377b b a i221 l221 378b b b m222 p222 379c a a a311 d311 380c a b e312 h312 381c b a i321 l321 382c b b m322 p322 383d a a a411 d411 384d a b e412 h412 385d b a i421 l421 386d b b m422 p422 387select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 388a1 a2 b max(c) 389a a a d111 390a a b h112 391a b a l121 392a b b p122 393c a a d311 394c a b h312 395c b a l321 396c b b p322 397d a a d411 398d a b h412 399d b a l421 400d b b p422 401select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 402a1 max(c) 403a d111 404a h112 405a l121 406a p122 407c d311 408c h312 409c l321 410c p322 411d d411 412d h412 413d l421 414d p422 415select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 416a1 a2 b min(c) max(c) 417a a a a111 d111 418a a b e112 h112 419b a a a211 d211 420b a b e212 h212 421c a a a311 d311 422c a b e312 h312 423c b a i321 l321 424c b b m322 p322 425d a a a411 d411 426d a b e412 h412 427d b a i421 l421 428d b b m422 p422 429select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 430a1 a2 b max(c) 431b a a d211 432b a b h212 433b b a l221 434b b b p222 435d a a d411 436d a b h412 437d b a l421 438d b b p422 439select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 440a1 a2 b min(c) max(c) 441b a a a211 d211 442b a b e212 h212 443b b a i221 l221 444b b b m222 p222 445d a a a411 d411 446d a b e412 h412 447d b a i421 l421 448d b b m422 p422 449select 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; 450a1 a2 b max(c) 451a b a l121 452a b b p122 453b b a l221 454b b b p222 455c b a l321 456c b b p322 457d b a l421 458d b b p422 459select 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; 460a1 a2 b min(c) max(c) 461a b a i121 l121 462a b b m122 p122 463b b a i221 l221 464b b b m222 p222 465c b a i321 l321 466c b b m322 p322 467d b a i421 l421 468d b b m422 p422 469select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; 470a1 min(c) max(c) 471b a211 d211 472b e212 h212 473b i221 l221 474b m222 p222 475c a311 d311 476c e312 h312 477c i321 l321 478c m322 p322 479d a411 d411 480d e412 h412 481d i421 l421 482d m422 p422 483select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; 484a1 max(c) 485a d111 486a h112 487a l121 488a p122 489b d211 490b h212 491b l221 492b p222 493d d411 494d h412 495d l421 496d p422 497select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; 498a1 a2 b max(c) 499a a NULL a999 500a a a d111 501a a b h112 502a b a l121 503a b b p122 504b a a d211 505b a b h212 506b b a l221 507b b b p222 508c a NULL c999 509c a a d311 510c a b h312 511c b a l321 512c b b p322 513select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b; 514a1 a2 b min(c) max(c) 515a a NULL a777 a999 516a a a a111 d111 517a a b e112 h112 518a b a i121 l121 519a b b m122 p122 520b a a a211 d211 521b a b e212 h212 522b b a i221 l221 523b b b m222 p222 524c a NULL c777 c999 525c a a a311 d311 526c a b e312 h312 527c b a i321 l321 528c b b m322 p322 529select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 530a1 a2 b min(c) max(c) 531b a a a211 d211 532b a b e212 h212 533b b a i221 l221 534b b b m222 p222 535c a NULL c777 c999 536c a a a311 d311 537c a b e312 h312 538c b a i321 l321 539c b b m322 p322 540d a a a411 d411 541d a b e412 h412 542d b a i421 l421 543d b b m422 p422 544e a a NULL NULL 545e a b NULL NULL 546select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 547a1 a2 b max(c) 548a a NULL a999 549a a a d111 550a a b h112 551a b a l121 552a b b p122 553c a NULL c999 554c a a d311 555c a b h312 556c b a l321 557c b b p322 558d a a d411 559d a b h412 560d b a l421 561d b b p422 562e a a NULL 563e a b NULL 564select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; 565a1 max(c) 566a a999 567a d111 568a h112 569a l121 570a p122 571c c999 572c d311 573c h312 574c l321 575c p322 576d d411 577d h412 578d l421 579d p422 580e NULL 581e NULL 582select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; 583a1 a2 b min(c) max(c) 584a a NULL a777 a999 585a a a a111 d111 586a a b e112 h112 587b a a a211 d211 588b a b e212 h212 589c a NULL c777 c999 590c a a a311 d311 591c a b e312 h312 592c b a i321 l321 593c b b m322 p322 594d a a a411 d411 595d a b e412 h412 596d b a i421 l421 597d b b m422 p422 598e a a NULL NULL 599e a b NULL NULL 600select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 601a1 a2 b max(c) 602b a a d211 603b a b h212 604b b a l221 605b b b p222 606d a a d411 607d a b h412 608d b a l421 609d b b p422 610select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; 611a1 a2 b min(c) max(c) 612b a a a211 d211 613b a b e212 h212 614b b a i221 l221 615b b b m222 p222 616d a a a411 d411 617d a b e412 h412 618d b a i421 l421 619d b b m422 p422 620select 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; 621a1 a2 b max(c) 622a b a l121 623a b b p122 624b b a l221 625b b b p222 626c b a l321 627c b b p322 628d b a l421 629d b b p422 630select 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; 631a1 a2 b min(c) max(c) 632a b a i121 l121 633a b b m122 p122 634b b a i221 l221 635b b b m222 p222 636c b a i321 l321 637c b b m322 p322 638d b a i421 l421 639d b b m422 p422 640select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; 641a1 min(c) max(c) 642b a211 d211 643b e212 h212 644b i221 l221 645b m222 p222 646c c777 c999 647c a311 d311 648c e312 h312 649c i321 l321 650c m322 p322 651d a411 d411 652d e412 h412 653d i421 l421 654d m422 p422 655e NULL NULL 656e NULL NULL 657select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; 658a1 max(c) 659a a999 660a d111 661a h112 662a l121 663a p122 664b d211 665b h212 666b l221 667b p222 668d d411 669d h412 670d l421 671d p422 672# 673# MariaDB: we dont have the following patch: 674# 675# commit 60a92a79a3b7fde3c6efe91799e344b977c8e5c3 676# Author: Manuel Ung <mung@fb.com> 677# Date: Thu Apr 19 23:06:27 2018 -0700 678# 679# Enhance group-by loose index scan 680# 681# So the following results are not very meaningful, but are still kept here 682explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 683id select_type table type possible_keys key key_len ref rows Extra 6841 SIMPLE t1 range NULL idx_t1_1 147 NULL 63 Using where; Using index for group-by 685explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 686id select_type table type possible_keys key key_len ref rows Extra 6871 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index 688explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 689id select_type table type possible_keys key key_len ref rows Extra 6901 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index 691explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; 692id select_type table type possible_keys key key_len ref rows Extra 6931 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index 694explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 695id select_type table type possible_keys key key_len ref rows Extra 6961 SIMPLE t1 range NULL idx_t1_1 147 NULL 63 Using where; Using index for group-by 697explain select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 698id select_type table type possible_keys key key_len ref rows Extra 6991 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index 700explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 701id select_type table type possible_keys key key_len ref rows Extra 7021 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index 703explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; 704id select_type table type possible_keys key key_len ref rows Extra 7051 SIMPLE t1 range NULL idx_t1_1 147 NULL 126 Using where; Using index for group-by 706explain select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 707id select_type table type possible_keys key key_len ref rows Extra 7081 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index 709explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; 710id select_type table type possible_keys key key_len ref rows Extra 7111 SIMPLE t1 range NULL idx_t1_1 147 NULL 126 Using where; Using index for group-by 712explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 713id select_type table type possible_keys key key_len ref rows Extra 7141 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index 715explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; 716id select_type table type possible_keys key key_len ref rows Extra 7171 SIMPLE t1 range NULL idx_t1_1 147 NULL 126 Using where; Using index for group-by 718explain select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 719id select_type table type possible_keys key key_len ref rows Extra 7201 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index 721explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 722id select_type table type possible_keys key key_len ref rows Extra 7231 SIMPLE t2 range NULL idx_t2_1 163 NULL 63 Using where; Using index for group-by 724explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 725id select_type table type possible_keys key key_len ref rows Extra 7261 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 727explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 728id select_type table type possible_keys key key_len ref rows Extra 7291 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 730explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 731id select_type table type possible_keys key key_len ref rows Extra 7321 SIMPLE t2 range NULL idx_t2_1 163 NULL 63 Using where; Using index for group-by 733explain select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 734id select_type table type possible_keys key key_len ref rows Extra 7351 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 736explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 737id select_type table type possible_keys key key_len ref rows Extra 7381 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 739explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; 740id select_type table type possible_keys key key_len ref rows Extra 7411 SIMPLE t2 range NULL idx_t2_1 146 NULL 126 Using where; Using index for group-by 742explain select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 743id select_type table type possible_keys key key_len ref rows Extra 7441 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 745explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; 746id select_type table type possible_keys key key_len ref rows Extra 7471 SIMPLE t2 range NULL idx_t2_1 163 NULL 126 Using where; Using index for group-by 748explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 749id select_type table type possible_keys key key_len ref rows Extra 7501 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 751explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; 752id select_type table type possible_keys key key_len ref rows Extra 7531 SIMPLE t2 range NULL idx_t2_1 146 NULL 126 Using where; Using index for group-by 754explain select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 755id select_type table type possible_keys key key_len ref rows Extra 7561 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 757explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 758id select_type table type possible_keys key key_len ref rows Extra 7591 SIMPLE t3 range NULL idx_t3_1 6 NULL 63 Using where; Using index for group-by 760explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 761id select_type table type possible_keys key key_len ref rows Extra 7621 SIMPLE t3 index NULL idx_t3_1 10 NULL 1000 Using where; Using index 763explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 764id select_type table type possible_keys key key_len ref rows Extra 7651 SIMPLE t3 index NULL idx_t3_1 10 NULL 1000 Using where; Using index 766explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 767id select_type table type possible_keys key key_len ref rows Extra 7681 SIMPLE t3 range NULL idx_t3_1 6 NULL 63 Using where; Using index for group-by 769explain select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 770id select_type table type possible_keys key key_len ref rows Extra 7711 SIMPLE t3 index NULL idx_t3_1 10 NULL 1000 Using where; Using index 772explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 773id select_type table type possible_keys key key_len ref rows Extra 7741 SIMPLE t3 index NULL idx_t3_1 10 NULL 1000 Using where; Using index 775select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 776a1 a2 b max(c) min(c) 777a a b h112 e112 778b a b h212 e212 779c a b h312 e312 780d a b h412 e412 781select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 782a1 a2 b max(c) min(c) 783a a b p122 e112 784b a b p222 e212 785c a b p322 e312 786d a b p422 e412 787select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 788a1 a2 b max(c) min(c) 789a a a h112 a111 790b a a h212 a211 791c a a h312 a311 792d a a h412 a411 793select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; 794a1 a2 b max(c) min(c) 795a a a p122 a111 796b a a p222 a211 797c a a p322 a311 798d a a p422 a411 799select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; 800a1 max(c) min(c) 801a h112 e112 802b h212 e212 803c h312 e312 804d h412 e412 805select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 806a1 max(c) min(c) 807a p122 e112 808b p222 e212 809c p322 e312 810d p422 e412 811select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 812a1 max(c) min(c) 813a h112 a111 814b h212 a211 815c h312 a311 816d h412 a411 817select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; 818a1 a2 b max(c) 819a a b h112 820a b b p122 821b a b h212 822b b b p222 823c a b h312 824c b b p322 825d a b h412 826d b b p422 827select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 828a1 a2 b max(c) 829a a a h112 830a b a p122 831b a a h212 832b b a p222 833c a a h312 834c b a p322 835d a a h412 836d b a p422 837select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; 838a1 a2 b min(c) max(c) 839a a b e112 h112 840a b b m122 p122 841b a b e212 h212 842b b b m222 p222 843c a b e312 h312 844c b b m322 p322 845d a b e412 h412 846d b b m422 p422 847select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 848a1 a2 b min(c) max(c) 849a a a a111 h112 850a b a i121 p122 851b a a a211 h212 852b b a i221 p222 853c a a a311 h312 854c b a i321 p322 855d a a a411 h412 856d b a i421 p422 857select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; 858a1 a2 max(c) 859a a h112 860a b p122 861b a h212 862b b p222 863c a h312 864c b p322 865d a h412 866d b p422 867select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; 868a1 a2 max(c) 869a a h112 870a b p122 871b a h212 872b b p222 873c a h312 874c b p322 875d a h412 876d b p422 877select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 878a1 a2 b max(c) min(c) 879a a b h112 e112 880b a b h212 e212 881c a b h312 e312 882d a b h412 e412 883e a b NULL NULL 884select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 885a1 a2 b max(c) min(c) 886a a b p122 e112 887b a b p222 e212 888c a b p322 e312 889d a b p422 e412 890e a b NULL NULL 891select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 892a1 a2 b max(c) min(c) 893a a a h112 a111 894b a a h212 a211 895c a a h312 a311 896d a a h412 a411 897e a a NULL NULL 898select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 899a1 max(c) min(c) 900a h112 e112 901b h212 e212 902c h312 e312 903d h412 e412 904e NULL NULL 905select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 906a1 max(c) min(c) 907a p122 e112 908b p222 e212 909c p322 e312 910d p422 e412 911e NULL NULL 912select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 913a1 max(c) min(c) 914a h112 a111 915b h212 a211 916c h312 a311 917d h412 a411 918e NULL NULL 919select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; 920a1 a2 b max(c) 921a a b h112 922a b b p122 923b a b h212 924b b b p222 925c a b h312 926c b b p322 927d a b h412 928d b b p422 929e a b NULL 930select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 931a1 a2 b max(c) 932a a a h112 933a b a p122 934b a a h212 935b b a p222 936c a a h312 937c b a p322 938d a a h412 939d b a p422 940e a a NULL 941select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; 942a1 a2 b min(c) max(c) 943a a b e112 h112 944a b b m122 p122 945b a b e212 h212 946b b b m222 p222 947c a b e312 h312 948c b b m322 p322 949d a b e412 h412 950d b b m422 p422 951e a b NULL NULL 952select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 953a1 a2 b min(c) max(c) 954a a a a111 h112 955a b a i121 p122 956b a a a211 h212 957b b a i221 p222 958c a a a311 h312 959c b a i321 p322 960d a a a411 h412 961d b a i421 p422 962e a a NULL NULL 963select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; 964a1 a2 max(c) 965a a h112 966a b p122 967b a h212 968b b p222 969c a h312 970c b p322 971d a h412 972d b p422 973e a NULL 974select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; 975a1 a2 max(c) 976a a h112 977a b p122 978b a h212 979b b p222 980c a h312 981c b p322 982d a h412 983d b p422 984e a NULL 985select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 986a1 a2 b max(c) min(c) 987a a b h112 e112 988b a b h212 e212 989c a b h312 e312 990select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 991a1 a2 b max(c) min(c) 992a a b p122 e112 993b a b p222 e212 994c a b p322 e312 995select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 996a1 a2 b max(c) min(c) 997a a a h112 a111 998b a a h212 a211 999c a a h312 a311 1000select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; 1001a1 max(c) min(c) 1002a h112 e112 1003b h212 e212 1004c h312 e312 1005select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; 1006a1 max(c) min(c) 1007a p122 e112 1008b p222 e212 1009c p322 e312 1010select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; 1011a1 max(c) min(c) 1012a h112 a111 1013b h212 a211 1014c h312 a311 1015explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; 1016id select_type table type possible_keys key key_len ref rows Extra 10171 SIMPLE t2 range NULL idx_t2_1 163 NULL 63 Using where; Using index for group-by 1018explain select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; 1019id select_type table type possible_keys key key_len ref rows Extra 10201 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 1021explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; 1022id select_type table type possible_keys key key_len ref rows Extra 10231 SIMPLE t2 range NULL idx_t2_1 146 NULL 63 Using where; Using index for group-by 1024explain select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; 1025id select_type table type possible_keys key key_len ref rows Extra 10261 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 1027explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; 1028id select_type table type possible_keys key key_len ref rows Extra 10291 SIMPLE t2 range NULL idx_t2_1 163 NULL 126 Using where; Using index for group-by 1030explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; 1031id select_type table type possible_keys key key_len ref rows Extra 10321 SIMPLE t2 range NULL idx_t2_1 146 NULL 126 Using where; Using index for group-by 1033explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 1034id select_type table type possible_keys key key_len ref rows Extra 10351 SIMPLE t2 range NULL idx_t2_1 163 NULL 126 Using where; Using index for group-by 1036select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; 1037a1 a2 b min(c) 1038a a NULL a777 1039c a NULL c777 1040select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; 1041a1 a2 b min(c) 1042a a NULL a777 1043c a NULL c777 1044select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; 1045a1 a2 b max(c) 1046a a NULL a999 1047c a NULL c999 1048select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; 1049a1 a2 b max(c) 1050a a NULL a999 1051c a NULL c999 1052select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; 1053a1 a2 b min(c) 1054a a NULL a777 1055c a NULL c777 1056select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; 1057a1 a2 b max(c) 1058a a NULL a999 1059c a NULL c999 1060select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 1061a1 a2 b min(c) max(c) 1062a a NULL a777 a999 1063c a NULL c777 c999 1064select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; 1065a1 a2 b min(c) max(c) 1066a a NULL a777 a999 1067c a NULL c777 c999 1068explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; 1069id select_type table type possible_keys key key_len ref rows Extra 10701 SIMPLE t1 range NULL idx_t1_1 147 NULL # Using where; Using index for group-by 1071explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; 1072id select_type table type possible_keys key key_len ref rows Extra 10731 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1074explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; 1075id select_type table type possible_keys key key_len ref rows Extra 10761 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by 1077explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; 1078id select_type table type possible_keys key key_len ref rows Extra 10791 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1080explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; 1081id select_type table type possible_keys key key_len ref rows Extra 10821 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1083explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; 1084id select_type table type possible_keys key key_len ref rows Extra 10851 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1086explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; 1087id select_type table type possible_keys key key_len ref rows Extra 10881 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1089explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; 1090id select_type table type possible_keys key key_len ref rows Extra 10911 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1092explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1093id select_type table type possible_keys key key_len ref rows Extra 10941 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1095explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1096id select_type table type possible_keys key key_len ref rows Extra 10971 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1098explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1099id select_type table type possible_keys key key_len ref rows Extra 11001 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by 1101explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1102id select_type table type possible_keys key key_len ref rows Extra 11031 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by 1104explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 1105id select_type table type possible_keys key key_len ref rows Extra 11061 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1107explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 1108id select_type table type possible_keys key key_len ref rows Extra 11091 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1110explain 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; 1111id select_type table type possible_keys key key_len ref rows Extra 11121 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1113explain 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; 1114id select_type table type possible_keys key key_len ref rows Extra 11151 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1116explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; 1117id select_type table type possible_keys key key_len ref rows Extra 11181 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by 1119explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; 1120id select_type table type possible_keys key key_len ref rows Extra 11211 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1122explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; 1123id select_type table type possible_keys key key_len ref rows Extra 11241 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by 1125explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; 1126id select_type table type possible_keys key key_len ref rows Extra 11271 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1128explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; 1129id select_type table type possible_keys key key_len ref rows Extra 11301 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1131explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; 1132id select_type table type possible_keys key key_len ref rows Extra 11331 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1134explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; 1135id select_type table type possible_keys key key_len ref rows Extra 11361 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1137explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; 1138id select_type table type possible_keys key key_len ref rows Extra 11391 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1140explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1141id select_type table type possible_keys key key_len ref rows Extra 11421 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1143explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1144id select_type table type possible_keys key key_len ref rows Extra 11451 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1146explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1147id select_type table type possible_keys key key_len ref rows Extra 11481 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by 1149explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1150id select_type table type possible_keys key key_len ref rows Extra 11511 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1152explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 1153id select_type table type possible_keys key key_len ref rows Extra 11541 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1155explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 1156id select_type table type possible_keys key key_len ref rows Extra 11571 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1158explain 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; 1159id select_type table type possible_keys key key_len ref rows Extra 11601 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1161select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; 1162a1 a2 b max(c) 1163a a a d111 1164a a b h112 1165a b a l121 1166a b b p122 1167b a a d211 1168b a b h212 1169b b a l221 1170b b b p222 1171c a a d311 1172c a b h312 1173c b a l321 1174c b b p322 1175d a a d411 1176d a b h412 1177d b a l421 1178d b b p422 1179select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; 1180a1 a2 b min(c) max(c) 1181a a a b111 d111 1182a a b e112 h112 1183a b a i121 l121 1184a b b m122 p122 1185b a a b211 d211 1186b a b e212 h212 1187b b a i221 l221 1188b b b m222 p222 1189c a a b311 d311 1190c a b e312 h312 1191c b a i321 l321 1192c b b m322 p322 1193d a a b411 d411 1194d a b e412 h412 1195d b a i421 l421 1196d b b m422 p422 1197select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; 1198a1 a2 b max(c) 1199a a b h112 1200a b a l121 1201a b b p122 1202b a b h212 1203b b a l221 1204b b b p222 1205c a b h312 1206c b a l321 1207c b b p322 1208d a b h412 1209d b a l421 1210d b b p422 1211select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; 1212a1 a2 b min(c) max(c) 1213a a b g112 h112 1214a b a i121 l121 1215a b b m122 p122 1216b a b f212 h212 1217b b a i221 l221 1218b b b m222 p222 1219c a b f312 h312 1220c b a i321 l321 1221c b b m322 p322 1222d a b f412 h412 1223d b a i421 l421 1224d b b m422 p422 1225select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; 1226a1 a2 b max(c) 1227select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; 1228a1 a2 b min(c) max(c) 1229select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; 1230a1 a2 b max(c) 1231a a a d111 1232a a b h112 1233a b a k121 1234b a a d211 1235b a b h212 1236b b a k221 1237c a a d311 1238c a b h312 1239c b a j321 1240d a a d411 1241d a b h412 1242d b a j421 1243select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; 1244a1 a2 b min(c) max(c) 1245a a a a111 d111 1246a a b e112 h112 1247a b a i121 k121 1248b a a a211 d211 1249b a b e212 h212 1250b b a i221 k221 1251c a a a311 d311 1252c a b e312 h312 1253c b a i321 j321 1254d a a a411 d411 1255d a b e412 h412 1256d b a i421 j421 1257select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1258a1 a2 b max(c) 1259a a a d111 1260a a b h112 1261a b a l121 1262a b b p122 1263b a a d211 1264b a b h212 1265b b a l221 1266b b b p222 1267c a a d311 1268c a b h312 1269c b a l321 1270c b b p322 1271d a a d411 1272d a b h412 1273d b a l421 1274d b b p422 1275select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1276a1 a2 b min(c) max(c) 1277a a a b111 d111 1278a a b e112 h112 1279a b a i121 l121 1280a b b m122 p122 1281b a a b211 d211 1282b a b e212 h212 1283b b a i221 l221 1284b b b m222 p222 1285c a a b311 d311 1286c a b e312 h312 1287c b a i321 l321 1288c b b m322 p322 1289d a a b411 d411 1290d a b e412 h412 1291d b a i421 l421 1292d b b m422 p422 1293select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1294a1 a2 b max(c) 1295a a a d111 1296a a b h112 1297a b a l121 1298a b b p122 1299b a a d211 1300b a b h212 1301b b a l221 1302b b b p222 1303c a a d311 1304c a b h312 1305c b a l321 1306c b b p322 1307d a a d411 1308d a b h412 1309d b a l421 1310d b b p422 1311select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1312a1 a2 b min(c) max(c) 1313a a a a111 d111 1314a a b e112 h112 1315a b a i121 l121 1316a b b m122 p122 1317b a a a211 d211 1318b a b e212 h212 1319b b a i221 l221 1320b b b m222 p222 1321c a a a311 d311 1322c a b e312 h312 1323c b a i321 l321 1324c b b m322 p322 1325d a a a411 d411 1326d a b e412 h412 1327d b a i421 l421 1328d b b m422 p422 1329select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 1330a1 a2 b min(c) max(c) 1331a a a c111 d111 1332a a b e112 g112 1333b a a b211 d211 1334b a b e212 f212 1335c a a b311 d311 1336c a b e312 f312 1337d a a b411 d411 1338d a b e412 f412 1339select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 1340a1 a2 b min(c) max(c) 1341a a a a111 c111 1342b a a a211 c211 1343c a a a311 c311 1344d a a a411 c411 1345d a b g412 g412 1346d b a k421 k421 1347select 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; 1348a1 a2 b min(c) max(c) 1349a a a c111 d111 1350a a b e112 h112 1351b a a b211 d211 1352b a b e212 h212 1353c a a b311 d311 1354c a b e312 h312 1355d a a b411 d411 1356d a b e412 h412 1357select 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; 1358a1 a2 b min(c) max(c) 1359a a a b111 d111 1360a a b e112 h112 1361b a a b211 d211 1362b a b e212 h212 1363c a a b311 d311 1364c a b e312 h312 1365d a a b411 d411 1366d a b e412 h412 1367select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; 1368a1 a2 b max(c) 1369a a a d111 1370a a b h112 1371a b a l121 1372a b b p122 1373b a a d211 1374b a b h212 1375b b a l221 1376b b b p222 1377c a NULL c999 1378c a a d311 1379c a b h312 1380c b a l321 1381c b b p322 1382d a a d411 1383d a b h412 1384d b a l421 1385d b b p422 1386select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; 1387a1 a2 b min(c) max(c) 1388a a a b111 d111 1389a a b e112 h112 1390a b a i121 l121 1391a b b m122 p122 1392b a a b211 d211 1393b a b e212 h212 1394b b a i221 l221 1395b b b m222 p222 1396c a NULL c777 c999 1397c a a b311 d311 1398c a b e312 h312 1399c b a i321 l321 1400c b b m322 p322 1401d a a b411 d411 1402d a b e412 h412 1403d b a i421 l421 1404d b b m422 p422 1405select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; 1406a1 a2 b max(c) 1407a a b h112 1408a b a l121 1409a b b p122 1410b a b h212 1411b b a l221 1412b b b p222 1413c a b h312 1414c b a l321 1415c b b p322 1416d a b h412 1417d b a l421 1418d b b p422 1419select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; 1420a1 a2 b min(c) max(c) 1421a a b g112 h112 1422a b a i121 l121 1423a b b m122 p122 1424b a b f212 h212 1425b b a i221 l221 1426b b b m222 p222 1427c a b f312 h312 1428c b a i321 l321 1429c b b m322 p322 1430d a b f412 h412 1431d b a i421 l421 1432d b b m422 p422 1433select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; 1434a1 a2 b max(c) 1435select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; 1436a1 a2 b min(c) max(c) 1437select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; 1438a1 a2 b max(c) 1439a a NULL a999 1440a a a d111 1441a a b h112 1442a b a k121 1443b a a d211 1444b a b h212 1445b b a k221 1446c a NULL c999 1447c a a d311 1448c a b h312 1449c b a j321 1450d a a d411 1451d a b h412 1452d b a j421 1453select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; 1454a1 a2 b min(c) max(c) 1455a a NULL a777 a999 1456a a a a111 d111 1457a a b e112 h112 1458a b a i121 k121 1459b a a a211 d211 1460b a b e212 h212 1461b b a i221 k221 1462c a NULL c777 c999 1463c a a a311 d311 1464c a b e312 h312 1465c b a i321 j321 1466d a a a411 d411 1467d a b e412 h412 1468d b a i421 j421 1469select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1470a1 a2 b max(c) 1471a a a d111 1472a a b h112 1473a b a l121 1474a b b p122 1475b a a d211 1476b a b h212 1477b b a l221 1478b b b p222 1479c a NULL c999 1480c a a d311 1481c a b h312 1482c b a l321 1483c b b p322 1484d a a d411 1485d a b h412 1486d b a l421 1487d b b p422 1488select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; 1489a1 a2 b min(c) max(c) 1490a a a b111 d111 1491a a b e112 h112 1492a b a i121 l121 1493a b b m122 p122 1494b a a b211 d211 1495b a b e212 h212 1496b b a i221 l221 1497b b b m222 p222 1498c a NULL c777 c999 1499c a a b311 d311 1500c a b e312 h312 1501c b a i321 l321 1502c b b m322 p322 1503d a a b411 d411 1504d a b e412 h412 1505d b a i421 l421 1506d b b m422 p422 1507select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1508a1 a2 b max(c) 1509a a NULL a999 1510a a a d111 1511a a b h112 1512a b a l121 1513a b b p122 1514b a a d211 1515b a b h212 1516b b a l221 1517b b b p222 1518c a NULL c999 1519c a a d311 1520c a b h312 1521c b a l321 1522c b b p322 1523d a a d411 1524d a b h412 1525d b a l421 1526d b b p422 1527select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; 1528a1 a2 b min(c) max(c) 1529a a NULL a777 a999 1530a a a a111 d111 1531a a b e112 h112 1532a b a i121 l121 1533a b b m122 p122 1534b a a a211 d211 1535b a b e212 h212 1536b b a i221 l221 1537b b b m222 p222 1538c a NULL c777 c999 1539c a a a311 d311 1540c a b e312 h312 1541c b a i321 l321 1542c b b m322 p322 1543d a a a411 d411 1544d a b e412 h412 1545d b a i421 l421 1546d b b m422 p422 1547select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; 1548a1 a2 b min(c) max(c) 1549a a a c111 d111 1550a a b e112 g112 1551b a a b211 d211 1552b a b e212 f212 1553c a NULL c777 c999 1554c a a b311 d311 1555c a b e312 f312 1556d a a b411 d411 1557d a b e412 f412 1558select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; 1559a1 a2 b min(c) max(c) 1560a a NULL a777 a999 1561a a a a111 c111 1562b a a a211 c211 1563c a a a311 c311 1564d a a a411 c411 1565d a b g412 g412 1566d b a k421 k421 1567select 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; 1568a1 a2 b min(c) max(c) 1569a a a c111 d111 1570a a b e112 h112 1571b a a b211 d211 1572b a b e212 h212 1573c a NULL c777 c999 1574c a a b311 d311 1575c a b e312 h312 1576d a a b411 d411 1577d a b e412 h412 1578explain select a1,a2,b,min(c),max(c) from t1 1579where exists ( select * from t2 where t2.c = t1.c ) 1580group by a1,a2,b; 1581id select_type table type possible_keys key key_len ref rows Extra 15821 PRIMARY t1 index NULL idx_t1_1 163 NULL 1000 Using index 15831 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func 1 15842 MATERIALIZED t2 index NULL idx_t2_1 163 NULL 1000 Using index 1585explain select a1,a2,b,min(c),max(c) from t1 1586where exists ( select * from t2 where t2.c > 'b1' ) 1587group by a1,a2,b; 1588id select_type table type possible_keys key key_len ref rows Extra 15891 PRIMARY t1 index NULL idx_t1_1 163 NULL 1000 Using index 15902 SUBQUERY t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 1591explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1592id select_type table type possible_keys key key_len ref rows Extra 15931 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 1594explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 1595id select_type table type possible_keys key key_len ref rows Extra 15961 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 251 Using where; Using index for group-by 1597explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 1598id select_type table type possible_keys key key_len ref rows Extra 15991 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1600explain 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; 1601id select_type table type possible_keys key key_len ref rows Extra 16021 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 251 Using where; Using index for group-by 1603explain 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; 1604id select_type table type possible_keys key key_len ref rows Extra 16051 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 251 Using where; Using index for group-by 1606explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1607id select_type table type possible_keys key key_len ref rows Extra 16081 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 1609explain 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; 1610id select_type table type possible_keys key key_len ref rows Extra 16111 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by 1612explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1613id select_type table type possible_keys key key_len ref rows Extra 16141 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1615explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 1616id select_type table type possible_keys key key_len ref rows Extra 16171 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1618explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 1619id select_type table type possible_keys key key_len ref rows Extra 16201 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1621explain 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; 1622id select_type table type possible_keys key key_len ref rows Extra 16231 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1624explain 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; 1625id select_type table type possible_keys key key_len ref rows Extra 16261 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1627explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1628id select_type table type possible_keys key key_len ref rows Extra 16291 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by 1630select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1631a1 a2 b min(c) max(c) 1632a a b e112 h112 1633b a b e212 h212 1634c a b e312 h312 1635c b b m322 p322 1636d a b e412 h412 1637d b b m422 p422 1638select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 1639a1 a2 b min(c) max(c) 1640a a a c111 d111 1641a a b e112 h112 1642b a a b211 d211 1643b a b e212 h212 1644c a a b311 d311 1645c a b e312 h312 1646c b a i321 l321 1647c b b m322 p322 1648d a a b411 d411 1649d a b e412 h412 1650d b a i421 l421 1651d b b m422 p422 1652select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 1653a1 a2 b min(c) max(c) 1654a b a i121 l121 1655b b a i221 l221 1656c b a i321 l321 1657d b a i421 l421 1658select 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; 1659a1 a2 b min(c) 1660b b a k221 1661c b a k321 1662d b a k421 1663select 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; 1664a1 a2 b min(c) 1665b b a k221 1666c b a k321 1667d b a k421 1668select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1669a1 a2 b min(c) 1670select 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; 1671a1 a2 b min(c) 1672select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1673a1 a2 b min(c) max(c) 1674a a b e112 h112 1675b a b e212 h212 1676c a b e312 h312 1677c b b m322 p322 1678d a b e412 h412 1679d b b m422 p422 1680e a b NULL NULL 1681select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; 1682a1 a2 b min(c) max(c) 1683a a a c111 d111 1684a a b e112 h112 1685b a a b211 d211 1686b a b e212 h212 1687c a NULL c777 c999 1688c a a b311 d311 1689c a b e312 h312 1690c b a i321 l321 1691c b b m322 p322 1692d a a b411 d411 1693d a b e412 h412 1694d b a i421 l421 1695d b b m422 p422 1696select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; 1697a1 a2 b min(c) max(c) 1698a b a i121 l121 1699b b a i221 l221 1700c b a i321 l321 1701d b a i421 l421 1702select 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; 1703a1 a2 b min(c) 1704b b a k221 1705c b a k321 1706d b a k421 1707select 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; 1708a1 a2 b min(c) 1709b b a k221 1710c b a k321 1711d b a k421 1712select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1713a1 a2 b min(c) 1714explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1715id select_type table type possible_keys key key_len ref rows Extra 17161 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 1717explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1718id select_type table type possible_keys key key_len ref rows Extra 17191 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by 1720explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1721id select_type table type possible_keys key key_len ref rows Extra 17221 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1723explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; 1724id select_type table type possible_keys key key_len ref rows Extra 17251 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1726explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1727id select_type table type possible_keys key key_len ref rows Extra 17281 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 1729explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1730id select_type table type possible_keys key key_len ref rows Extra 17311 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 1732explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1733id select_type table type possible_keys key key_len ref rows Extra 17341 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by 1735explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1736id select_type table type possible_keys key key_len ref rows Extra 17371 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1738explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; 1739id select_type table type possible_keys key key_len ref rows Extra 17401 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1741explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1742id select_type table type possible_keys key key_len ref rows Extra 17431 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 1744select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1745a1 a2 b 1746a a b 1747b a b 1748c a b 1749c b b 1750d a b 1751d b b 1752select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1753a1 a2 b 1754a b a 1755b b a 1756c b a 1757d b a 1758select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1759a1 a2 b c 1760a b a i121 1761select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; 1762a1 a2 b c 1763a b a i121 1764select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1765a1 a2 b 1766select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; 1767a1 a2 b 1768a a b 1769b a b 1770c a b 1771c b b 1772d a b 1773d b b 1774e a b 1775select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1776a1 a2 b 1777a b a 1778b b a 1779c b a 1780d b a 1781select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1782a1 a2 b c 1783a b a i121 1784select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; 1785a1 a2 b c 1786a b a i121 1787select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1788a1 a2 b 1789explain select distinct a1,a2,b from t1; 1790id select_type table type possible_keys key key_len ref rows Extra 17911 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by 1792explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); 1793id select_type table type possible_keys key key_len ref rows Extra 17941 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by 1795explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 1796id select_type table type possible_keys key key_len ref rows filtered Extra 17971 SIMPLE t1 range NULL idx_t1_1 163 NULL 501 100.00 Using where; Using index for group-by 1798Warnings: 1799Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`b` = 'a' and `test`.`t1`.`c` = 'i121' and `test`.`t1`.`a2` >= 'b' 1800explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 1801id select_type table type possible_keys key key_len ref rows Extra 18021 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 1803explain select distinct b from t1 where (a2 >= 'b') and (b = 'a'); 1804id select_type table type possible_keys key key_len ref rows Extra 18051 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 Using where; Using index 1806explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b'; 1807id select_type table type possible_keys key key_len ref rows Extra 18081 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 63 Using where; Using index for group-by 1809explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e'; 1810id select_type table type possible_keys key key_len ref rows Extra 18111 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 63 Using where; Using index for group-by 1812explain select distinct a1,a2,b from t2; 1813id select_type table type possible_keys key key_len ref rows Extra 18141 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by 1815explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); 1816id select_type table type possible_keys key key_len ref rows Extra 18171 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by 1818explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 1819id select_type table type possible_keys key key_len ref rows filtered Extra 18201 SIMPLE t2 range NULL idx_t2_1 163 NULL 501 100.00 Using where; Using index for group-by 1821Warnings: 1822Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`b` = 'a' and `test`.`t2`.`c` = 'i121' and `test`.`t2`.`a2` >= 'b' 1823explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 1824id select_type table type possible_keys key key_len ref rows Extra 18251 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 1826explain select distinct b from t2 where (a2 >= 'b') and (b = 'a'); 1827id select_type table type possible_keys key key_len ref rows Extra 18281 SIMPLE t2 index NULL idx_t2_2 146 NULL 1000 Using where; Using index 1829explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b'; 1830id select_type table type possible_keys key key_len ref rows Extra 18311 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 129 NULL 63 Using where; Using index for group-by 1832explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e'; 1833id select_type table type possible_keys key key_len ref rows Extra 18341 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 129 NULL 63 Using where; Using index for group-by 1835select distinct a1,a2,b from t1; 1836a1 a2 b 1837a a a 1838a a b 1839a b a 1840a b b 1841b a a 1842b a b 1843b b a 1844b b b 1845c a a 1846c a b 1847c b a 1848c b b 1849d a a 1850d a b 1851d b a 1852d b b 1853select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); 1854a1 a2 b 1855a b a 1856b b a 1857c b a 1858d b a 1859select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 1860a1 a2 b c 1861a b a i121 1862select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 1863a1 a2 b 1864select distinct b from t1 where (a2 >= 'b') and (b = 'a'); 1865b 1866a 1867select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b'; 1868a1 1869a 1870d 1871select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e'; 1872a1 1873select distinct a1,a2,b from t2; 1874a1 a2 b 1875a a NULL 1876a a a 1877a a b 1878a b a 1879a b b 1880b a a 1881b a b 1882b b a 1883b b b 1884c a NULL 1885c a a 1886c a b 1887c b a 1888c b b 1889d a a 1890d a b 1891d b a 1892d b b 1893e a a 1894e a b 1895select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); 1896a1 a2 b 1897a b a 1898b b a 1899c b a 1900d b a 1901select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 1902a1 a2 b c 1903a b a i121 1904select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 1905a1 a2 b 1906select distinct b from t2 where (a2 >= 'b') and (b = 'a'); 1907b 1908a 1909select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b'; 1910a1 1911a 1912d 1913select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e'; 1914a1 1915select distinct t_00.a1 1916from t1 t_00 1917where exists ( select * from t2 where a1 = t_00.a1 ); 1918a1 1919a 1920b 1921c 1922d 1923select distinct a1,a1 from t1; 1924a1 a1 1925a a 1926b b 1927c c 1928d d 1929select distinct a2,a1,a2,a1 from t1; 1930a2 a1 a2 a1 1931a a a a 1932b a b a 1933a b a b 1934b b b b 1935a c a c 1936b c b c 1937a d a d 1938b d b d 1939select distinct t1.a1,t2.a1 from t1,t2; 1940a1 a1 1941a a 1942b a 1943c a 1944d a 1945a b 1946b b 1947c b 1948d b 1949a c 1950b c 1951c c 1952d c 1953a d 1954b d 1955c d 1956d d 1957a e 1958b e 1959c e 1960d e 1961explain select distinct a1,a2,b from t1; 1962id select_type table type possible_keys key key_len ref rows Extra 19631 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by 1964explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1965id select_type table type possible_keys key key_len ref rows Extra 19661 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by 1967explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1968id select_type table type possible_keys key key_len ref rows Extra 19691 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by 1970explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1971id select_type table type possible_keys key key_len ref rows Extra 19721 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 1973explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1974id select_type table type possible_keys key key_len ref rows Extra 19751 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by; Using temporary; Using filesort 1976explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1; 1977id select_type table type possible_keys key key_len ref rows Extra 19781 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 63 Using where; Using index for group-by 1979explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1; 1980id select_type table type possible_keys key key_len ref rows Extra 19811 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 63 Using where; Using index for group-by 1982explain select distinct a1,a2,b from t2; 1983id select_type table type possible_keys key key_len ref rows Extra 19841 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by 1985explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1986id select_type table type possible_keys key key_len ref rows Extra 19871 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by 1988explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 1989id select_type table type possible_keys key key_len ref rows Extra 19901 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by 1991explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 1992id select_type table type possible_keys key key_len ref rows Extra 19931 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by 1994explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 1995id select_type table type possible_keys key key_len ref rows Extra 19961 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by; Using temporary; Using filesort 1997explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1; 1998id select_type table type possible_keys key key_len ref rows Extra 19991 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 129 NULL # Using where; Using index for group-by 2000explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1; 2001id select_type table type possible_keys key key_len ref rows Extra 20021 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 129 NULL # Using where; Using index for group-by 2003select distinct a1,a2,b from t1; 2004a1 a2 b 2005a a a 2006a a b 2007a b a 2008a b b 2009b a a 2010b a b 2011b b a 2012b b b 2013c a a 2014c a b 2015c b a 2016c b b 2017d a a 2018d a b 2019d b a 2020d b b 2021select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 2022a1 a2 b 2023a b a 2024b b a 2025c b a 2026d b a 2027select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 2028a1 a2 b c 2029a b a i121 2030select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 2031a1 a2 b 2032select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 2033b 2034a 2035select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1; 2036a1 2037a 2038d 2039select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1; 2040a1 2041select distinct a1,a2,b from t2; 2042a1 a2 b 2043a a NULL 2044a a a 2045a a b 2046a b a 2047a b b 2048b a a 2049b a b 2050b b a 2051b b b 2052c a NULL 2053c a a 2054c a b 2055c b a 2056c b b 2057d a a 2058d a b 2059d b a 2060d b b 2061e a a 2062e a b 2063select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 2064a1 a2 b 2065a b a 2066b b a 2067c b a 2068d b a 2069select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; 2070a1 a2 b c 2071a b a i121 2072select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; 2073a1 a2 b 2074select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; 2075b 2076a 2077select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1; 2078a1 2079a 2080d 2081select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1; 2082a1 2083explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); 2084id select_type table type possible_keys key key_len ref rows Extra 20851 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by 2086explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 2087id select_type table type possible_keys key key_len ref rows Extra 20881 SIMPLE t1 range NULL idx_t1_1 163 NULL 501 Using where; Using index for group-by (scanning) 2089explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 2090id select_type table type possible_keys key key_len ref rows filtered Extra 20911 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 100.00 Using where; Using index for group-by 2092Warnings: 2093Note 1003 select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where `test`.`t1`.`b` = 'c' and `test`.`t1`.`a1` > 'a' and `test`.`t1`.`a2` > 'a' 2094explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); 2095id select_type table type possible_keys key key_len ref rows Extra 20961 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 Using where; Using index 2097explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); 2098id select_type table type possible_keys key key_len ref rows filtered Extra 20991 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 100.00 Using where; Using index for group-by 2100Warnings: 2101Note 1003 select 98 + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `98 + count(distinct a1,a2,b)` from `test`.`t1` where `test`.`t1`.`a1` > 'a' and `test`.`t1`.`a2` > 'a' 2102select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); 2103count(distinct a1,a2,b) 21044 2105select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); 2106count(distinct a1,a2,b,c) 21071 2108select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); 2109count(distinct a1,a2,b) 21100 2111select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); 2112count(distinct b) 21131 2114select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); 211598 + count(distinct a1,a2,b) 2116104 2117explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; 2118id select_type table type possible_keys key key_len ref rows Extra 21191 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 2120explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; 2121id select_type table type possible_keys key key_len ref rows Extra 21221 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 2123explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; 2124id select_type table type possible_keys key key_len ref rows Extra 21251 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 2126explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 2127id select_type table type possible_keys key key_len ref rows Extra 21281 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by 2129explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; 2130id select_type table type possible_keys key key_len ref rows Extra 21311 SIMPLE t1 range NULL idx_t1_1 147 NULL 126 Using index for group-by 2132select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; 2133a1 a2 b concat(min(c), max(c)) 2134a a a a111d111 2135a a b e112h112 2136a b a i121l121 2137a b b m122p122 2138b a a a211d211 2139b a b e212h212 2140b b a i221l221 2141b b b m222p222 2142c a a a311d311 2143c a b e312h312 2144c b a i321l321 2145c b b m322p322 2146select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; 2147concat(a1,min(c)) b 2148aa111 a 2149ae112 b 2150ai121 a 2151am122 b 2152ba211 a 2153be212 b 2154bi221 a 2155bm222 b 2156ca311 a 2157ce312 b 2158ci321 a 2159cm322 b 2160select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; 2161concat(a1,min(c)) b max(c) 2162aa111 a d111 2163ae112 b h112 2164ai121 a l121 2165am122 b p122 2166ba211 a d211 2167be212 b h212 2168bi221 a l221 2169bm222 b p222 2170ca311 a d311 2171ce312 b h312 2172ci321 a l321 2173cm322 b p322 2174select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; 2175concat(a1,a2) b min(c) max(c) 2176aa a a111 d111 2177aa b e112 h112 2178ab a i121 l121 2179ab b m122 p122 2180ba a a211 d211 2181ba b e212 h212 2182bb a i221 l221 2183bb b m222 p222 2184ca a a311 d311 2185ca b e312 h312 2186cb a i321 l321 2187cb b m322 p322 2188select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; 2189concat(ord(min(b)),ord(max(b))) min(b) max(b) 21909798 a b 21919798 a b 21929798 a b 21939798 a b 21949798 a b 21959798 a b 21969798 a b 21979798 a b 2198explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b; 2199id select_type table type possible_keys key key_len ref rows Extra 22001 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 2201explain select a1,a2,b,d from t1 group by a1,a2,b; 2202id select_type table type possible_keys key key_len ref rows Extra 22031 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 2204explain extended select a1,a2,min(b),max(b) from t1 2205where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2; 2206id select_type table type possible_keys key key_len ref rows filtered Extra 22071 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 1000 100.00 Using where; Using index 2208Warnings: 2209Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2` 2210explain extended select a1,a2,b,min(c),max(c) from t1 2211where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b; 2212id select_type table type possible_keys key key_len ref rows filtered Extra 22131 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 130 NULL 1000 100.00 Using where 2214Warnings: 2215Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` 2216explain extended select a1,a2,b,c from t1 2217where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c; 2218id select_type table type possible_keys key key_len ref rows filtered Extra 22191 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 1000 100.00 Using where 2220Warnings: 2221Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c` 2222explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; 2223id select_type table type possible_keys key key_len ref rows Extra 22241 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 2225explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b < 'b') group by a1; 2226id select_type table type possible_keys key key_len ref rows Extra 22271 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 2228explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b') group by a1; 2229id select_type table type possible_keys key key_len ref rows Extra 22301 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 2231explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b' and b >= 'a') group by a1; 2232id select_type table type possible_keys key key_len ref rows Extra 22331 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 2234explain 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; 2235id select_type table type possible_keys key key_len ref rows filtered Extra 22361 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 1000 100.00 Using where; Using index 2237Warnings: 2238Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` 2239explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; 2240id select_type table type possible_keys key key_len ref rows Extra 22411 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 2242select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; 2243a1 a2 min(b) c 2244a a a a111 2245explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1; 2246id select_type table type possible_keys key key_len ref rows Extra 22471 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 2248explain select a1,a2,b,min(c),max(c) from t2 2249where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b; 2250id select_type table type possible_keys key key_len ref rows Extra 22511 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 2252explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c; 2253id select_type table type possible_keys key key_len ref rows Extra 22541 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 2255explain select a1,a2,count(a2) from t1 group by a1,a2,b; 2256id select_type table type possible_keys key key_len ref rows Extra 22571 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 Using index 2258explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; 2259id select_type table type possible_keys key key_len ref rows filtered Extra 22601 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 1000 100.00 Using where; Using index 2261Warnings: 2262Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` 2263explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; 2264id select_type table type possible_keys key key_len ref rows filtered Extra 22651 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 1000 100.00 Using where; Using index 2266Warnings: 2267Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` 2268explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; 2269id select_type table type possible_keys key key_len ref rows Extra 22701 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 2271explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; 2272id select_type table type possible_keys key key_len ref rows Extra 22731 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index 2274explain select distinct(a1) from t1 where ord(a2) = 98; 2275id select_type table type possible_keys key key_len ref rows Extra 22761 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 Using where; Using index 2277select distinct(a1) from t1 where ord(a2) = 98; 2278a1 2279a 2280b 2281c 2282d 2283explain select a1 from t1 where a2 = 'b' group by a1; 2284id select_type table type possible_keys key key_len ref rows Extra 22851 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using where; Using index for group-by 2286select a1 from t1 where a2 = 'b' group by a1; 2287a1 2288a 2289b 2290c 2291d 2292explain select distinct a1 from t1 where a2 = 'b'; 2293id select_type table type possible_keys key key_len ref rows Extra 22941 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using where; Using index for group-by 2295select distinct a1 from t1 where a2 = 'b'; 2296a1 2297a 2298b 2299c 2300d 2301drop table t1,t2,t3; 2302create table t1 (c1 int not null,c2 int not null, primary key(c1,c2)) engine=RocksDB; 2303insert into t1 (c1,c2) values 2304(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9); 2305select distinct c1, c2 from t1 order by c2; 2306c1 c2 230710 1 230810 2 230910 3 231020 4 231120 5 231220 6 231330 7 231430 8 231530 9 2316select c1,min(c2) as c2 from t1 group by c1 order by c2; 2317c1 c2 231810 1 231920 4 232030 7 2321select c1,c2 from t1 group by c1,c2 order by c2; 2322c1 c2 232310 1 232410 2 232510 3 232620 4 232720 5 232820 6 232930 7 233030 8 233130 9 2332drop table t1; 2333CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b)) engine=RocksDB; 2334INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4); 2335OPTIMIZE TABLE t1; 2336Table Op Msg_type Msg_text 2337test.t1 optimize status OK 2338SELECT a FROM t1 WHERE a='AA' GROUP BY a; 2339a 2340AA 2341SELECT a FROM t1 WHERE a='BB' GROUP BY a; 2342a 2343BB 2344EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a; 2345id select_type table type possible_keys key key_len ref rows Extra 23461 SIMPLE t1 ref PRIMARY PRIMARY 7 const 1000 Using where; Using index 2347EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a; 2348id select_type table type possible_keys key key_len ref rows Extra 23491 SIMPLE t1 ref PRIMARY PRIMARY 7 const 1000 Using where; Using index 2350SELECT DISTINCT a FROM t1 WHERE a='BB'; 2351a 2352BB 2353SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%'; 2354a 2355BB 2356SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a; 2357a 2358BB 2359DROP TABLE t1; 2360CREATE TABLE t1 ( 2361a int(11) NOT NULL DEFAULT '0', 2362b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '', 2363PRIMARY KEY (a,b) 2364) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 2365CREATE PROCEDURE a(x INT) 2366BEGIN 2367DECLARE rnd INT; 2368DECLARE cnt INT; 2369WHILE x > 0 DO 2370SET rnd= x % 100; 2371SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd); 2372INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR)); 2373SET x= x - 1; 2374END WHILE; 2375END| 2376CALL a(1000); 2377SELECT a FROM t1 WHERE a=0; 2378a 23790 23800 23810 23820 23830 23840 23850 23860 23870 23880 2389SELECT DISTINCT a FROM t1 WHERE a=0; 2390a 23910 2392SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0; 2393COUNT(DISTINCT a) 23941 2395DROP TABLE t1; 2396DROP PROCEDURE a; 2397CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)) engine=RocksDB; 2398INSERT INTO t1 (a) VALUES 2399(''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'), 2400('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'), 2401('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); 2402EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; 2403id select_type table type possible_keys key key_len ref rows Extra 24041 SIMPLE t1 index NULL PRIMARY 66 NULL 1000 Using index 2405SELECT DISTINCT a,a FROM t1 ORDER BY a; 2406a a 2407 2408CENTRAL CENTRAL 2409EASTERN EASTERN 2410GREATER LONDON GREATER LONDON 2411NORTH CENTRAL NORTH CENTRAL 2412NORTH EAST NORTH EAST 2413NORTH WEST NORTH WEST 2414SCOTLAND SCOTLAND 2415SOUTH EAST SOUTH EAST 2416SOUTH WEST SOUTH WEST 2417WESTERN WESTERN 2418DROP TABLE t1; 2419CREATE TABLE t1 (id1 INT, id2 INT) engine=RocksDB; 2420CREATE TABLE t2 (id2 INT, id3 INT, id5 INT) engine=RocksDB; 2421CREATE TABLE t3 (id3 INT, id4 INT) engine=RocksDB; 2422CREATE TABLE t4 (id4 INT) engine=RocksDB; 2423CREATE TABLE t5 (id5 INT, id6 INT) engine=RocksDB; 2424CREATE TABLE t6 (id6 INT) engine=RocksDB; 2425INSERT INTO t1 VALUES(1,1); 2426INSERT INTO t2 VALUES(1,1,1); 2427INSERT INTO t3 VALUES(1,1); 2428INSERT INTO t4 VALUES(1); 2429INSERT INTO t5 VALUES(1,1); 2430INSERT INTO t6 VALUES(1); 2431SELECT * FROM 2432t1 2433NATURAL JOIN 2434(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) 2435ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); 2436id2 id1 id3 id5 id4 id3 id6 id5 24371 1 1 1 1 1 1 1 2438SELECT * FROM 2439t1 2440NATURAL JOIN 2441(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2 2442ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); 2443id2 id1 id4 id3 id6 id5 id3 id5 24441 1 1 1 1 1 1 1 2445SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2); 2446id2 id1 id3 id4 id6 id5 id3 id5 24471 1 1 1 1 1 1 1 2448SELECT * FROM 2449(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) 2450ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)) 2451NATURAL JOIN 2452t1; 2453id2 id3 id5 id4 id3 id6 id5 id1 24541 1 1 1 1 1 1 1 2455SELECT * FROM 2456(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6))) 2457NATURAL JOIN 2458t1; 2459id2 id3 id5 id4 id3 id6 id5 id1 24601 1 1 1 1 1 1 1 2461DROP TABLE t1,t2,t3,t4,t5,t6; 2462CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) engine=RocksDB; 2463INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); 2464ANALYZE TABLE t1; 2465Table Op Msg_type Msg_text 2466test.t1 analyze status OK 2467explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; 2468id select_type table type possible_keys key key_len ref rows Extra 24691 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 501 Using where; Using index for group-by 2470SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; 2471MAX(b) a 24721 1 2473SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a; 2474MIN(b) a 24752 1 2476CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)) engine=RocksDB; 2477INSERT INTO t2 SELECT a,b,b FROM t1; 2478ANALYZE TABLE t2; 2479Table Op Msg_type Msg_text 2480test.t2 analyze status OK 2481explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; 2482id select_type table type possible_keys key key_len ref rows Extra 24831 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 251 Using where; Using index for group-by 2484SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; 2485MIN(c) 24862 2487DROP TABLE t1,t2; 2488CREATE TABLE t1 (a INT, b INT, INDEX (a,b)) engine=RocksDB; 2489INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), 2490(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); 2491ANALYZE TABLE t1; 2492Table Op Msg_type Msg_text 2493test.t1 analyze status OK 2494EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; 2495id select_type table type possible_keys key key_len ref rows Extra 24961 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by 2497FLUSH STATUS; 2498SELECT max(b), a FROM t1 GROUP BY a; 2499max(b) a 25005 1 25013 2 25021 3 25036 4 2504SHOW STATUS LIKE 'handler_read__e%'; 2505Variable_name Value 2506Handler_read_key 8 2507Handler_read_next 0 2508Handler_read_retry 0 2509EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; 2510id select_type table type possible_keys key key_len ref rows Extra 25111 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by 2512FLUSH STATUS; 2513CREATE TABLE t2 engine=RocksDB SELECT max(b), a FROM t1 GROUP BY a; 2514SHOW STATUS LIKE 'handler_read__e%'; 2515Variable_name Value 2516Handler_read_key 8 2517Handler_read_next 0 2518Handler_read_retry 0 2519FLUSH STATUS; 2520SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; 2521max(b) a 25225 1 25233 2 25241 3 25256 4 2526SHOW STATUS LIKE 'handler_read__e%'; 2527Variable_name Value 2528Handler_read_key 8 2529Handler_read_next 0 2530Handler_read_retry 0 2531FLUSH STATUS; 2532(SELECT max(b), a FROM t1 GROUP BY a) UNION 2533(SELECT max(b), a FROM t1 GROUP BY a); 2534max(b) a 25355 1 25363 2 25371 3 25386 4 2539SHOW STATUS LIKE 'handler_read__e%'; 2540Variable_name Value 2541Handler_read_key 16 2542Handler_read_next 0 2543Handler_read_retry 0 2544EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION 2545(SELECT max(b), a FROM t1 GROUP BY a); 2546id select_type table type possible_keys key key_len ref rows Extra 25471 PRIMARY t1 range NULL a 5 NULL 251 Using index for group-by 25482 UNION t1 range NULL a 5 NULL 251 Using index for group-by 2549NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 2550EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x 2551FROM t1 AS t1_outer; 2552id select_type table type possible_keys key key_len ref rows Extra 25531 PRIMARY t1_outer index NULL a 10 NULL 1000 Using index 25542 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by 2555EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS 2556(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 2557id select_type table type possible_keys key key_len ref rows Extra 25581 PRIMARY t1_outer index NULL a 10 NULL 1000 Using index 25592 SUBQUERY t1 index NULL a 10 NULL 1000 Using index 2560EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 2561(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; 2562id select_type table type possible_keys key key_len ref rows Extra 25631 PRIMARY t1_outer index NULL a 10 NULL 1000 Using index 25642 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by 2565EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 2566a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 2567id select_type table type possible_keys key key_len ref rows Extra 25681 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 251 25691 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 4 Using index 25702 MATERIALIZED t1 range NULL a 5 NULL 251 Using index for group-by 2571EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 2572a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 2573id select_type table type possible_keys key key_len ref rows Extra 25741 PRIMARY t1_outer range NULL a 5 NULL 251 Using index for group-by 25752 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by 2576EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 2577ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 2578AND t1_outer1.b = t1_outer2.b; 2579id select_type table type possible_keys key key_len ref rows Extra 25801 PRIMARY t1_outer1 ref a a 5 const 4 Using where; Using index 25811 PRIMARY t1_outer2 index NULL a 10 NULL 1000 Using where; Using index; Using join buffer (flat, BNL join) 25822 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by 2583EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x 2584FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; 2585id select_type table type possible_keys key key_len ref rows Extra 25861 PRIMARY t1_outer2 index NULL a 10 NULL 1000 Using index 25872 SUBQUERY t1_outer index NULL a 10 NULL 1000 Using index 25883 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by 2589CREATE TABLE t3 LIKE t1; 2590FLUSH STATUS; 2591INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; 2592SHOW STATUS LIKE 'handler_read__e%'; 2593Variable_name Value 2594Handler_read_key 8 2595Handler_read_next 0 2596Handler_read_retry 0 2597DELETE FROM t3; 2598FLUSH STATUS; 2599INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) 2600FROM t1 LIMIT 1; 2601SHOW STATUS LIKE 'handler_read__e%'; 2602Variable_name Value 2603Handler_read_key 8 2604Handler_read_next 0 2605Handler_read_retry 0 2606FLUSH STATUS; 2607DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; 2608SHOW STATUS LIKE 'handler_read__e%'; 2609Variable_name Value 2610Handler_read_key 8 2611Handler_read_next 0 2612Handler_read_retry 0 2613FLUSH STATUS; 2614DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 2615FROM t1) > 10000; 2616ERROR 21000: Subquery returns more than 1 row 2617SHOW STATUS LIKE 'handler_read__e%'; 2618Variable_name Value 2619Handler_read_key 8 2620Handler_read_next 1 2621Handler_read_retry 0 2622DROP TABLE t1,t2,t3; 2623CREATE TABLE t1 (a int, INDEX idx(a)) engine=RocksDB; 2624INSERT INTO t1 VALUES 2625(4), (2), (1), (2), (4), (2), (1), (4), 2626(4), (2), (1), (2), (2), (4), (1), (4); 2627ANALYZE TABLE t1; 2628Table Op Msg_type Msg_text 2629test.t1 analyze status OK 2630EXPLAIN SELECT DISTINCT(a) FROM t1; 2631id select_type table type possible_keys key key_len ref rows Extra 26321 SIMPLE t1 range NULL idx 5 NULL 501 Using index for group-by 2633SELECT DISTINCT(a) FROM t1; 2634a 26351 26362 26374 2638EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; 2639id select_type table type possible_keys key key_len ref rows Extra 26401 SIMPLE t1 range NULL idx 5 NULL 501 Using index for group-by 2641SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; 2642a 26431 26442 26454 2646DROP TABLE t1; 2647CREATE TABLE t1 (a INT, b INT) engine=RocksDB; 2648INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3); 2649INSERT INTO t1 SELECT a + 1, b FROM t1; 2650INSERT INTO t1 SELECT a + 2, b FROM t1; 2651ANALYZE TABLE t1; 2652Table Op Msg_type Msg_text 2653test.t1 analyze status OK 2654EXPLAIN 2655SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 2656id select_type table type possible_keys key key_len ref rows Extra 26571 SIMPLE t1 ALL NULL NULL NULL NULL 1000 Using temporary; Using filesort 2658SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 2659a MIN(b) MAX(b) 26604 1 3 26613 1 3 26622 1 3 26631 1 3 2664CREATE INDEX break_it ON t1 (a, b); 2665EXPLAIN 2666SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; 2667id select_type table type possible_keys key key_len ref rows Extra 26681 SIMPLE t1 range NULL break_it 10 NULL 251 Using index for group-by 2669SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; 2670a MIN(b) MAX(b) 26711 1 3 26722 1 3 26733 1 3 26744 1 3 2675EXPLAIN 2676SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 2677id select_type table type possible_keys key key_len ref rows Extra 26781 SIMPLE t1 range NULL break_it 10 NULL 251 Using index for group-by; Using temporary; Using filesort 2679SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; 2680a MIN(b) MAX(b) 26814 1 3 26823 1 3 26832 1 3 26841 1 3 2685EXPLAIN 2686SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; 2687id select_type table type possible_keys key key_len ref rows Extra 26881 SIMPLE t1 index NULL break_it 10 NULL 1000 Using index 2689SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; 2690a MIN(b) MAX(b) AVG(b) 26914 1 3 2.0000 26923 1 3 2.0000 26932 1 3 2.0000 26941 1 3 2.0000 2695DROP TABLE t1; 2696create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM; 2697insert into t1 (a,b) values 2698(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6), 2699(0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13), 2700(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6), 2701(1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13), 2702(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6), 2703(2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13), 2704(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6), 2705(3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13); 2706insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a; 2707ANALYZE TABLE t1; 2708Table Op Msg_type Msg_text 2709test.t1 analyze status OK 2710select * from t1; 2711a b 27120 0 27130 1 27140 2 27150 3 27160 4 27170 5 27180 6 27190 7 27200 8 27210 9 27220 10 27230 11 27240 12 27250 13 27260 14 27271 0 27281 1 27291 2 27301 3 27311 4 27321 5 27331 6 27341 7 27351 8 27361 9 27371 10 27381 11 27391 12 27401 13 27412 0 27422 1 27432 2 27442 3 27452 4 27462 5 27472 6 27482 7 27492 8 27502 9 27512 10 27522 11 27532 12 27542 13 27553 0 27563 1 27573 2 27583 3 27593 4 27603 5 27613 6 27623 7 27633 8 27643 9 27653 10 27663 11 27673 12 27683 13 2769explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a; 2770id select_type table type possible_keys key key_len ref rows filtered Extra 27711 SIMPLE t1 ref PRIMARY,index PRIMARY 4 const 15 100.00 Using index; Using temporary 2772Warnings: 2773Note 1003 select sql_buffer_result `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) + 1 AS `max(b)+1` from `test`.`t1` where `test`.`t1`.`a` = 0 group by `test`.`t1`.`a` 2774drop table t1; 2775CREATE TABLE t1 (a int, b int, c int, d int, 2776KEY foo (c,d,a,b), KEY bar (c,a,b,d)) engine=RocksDB; 2777INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4); 2778INSERT INTO t1 SELECT * FROM t1; 2779INSERT INTO t1 SELECT * FROM t1; 2780INSERT INTO t1 SELECT a,b,c+1,d FROM t1; 2781ANALYZE TABLE t1; 2782Table Op Msg_type Msg_text 2783test.t1 analyze status OK 2784EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4; 2785id select_type table type possible_keys key key_len ref rows Extra 27861 SIMPLE t1 range NULL foo 10 NULL 63 Using where; Using index for group-by 2787SELECT DISTINCT c FROM t1 WHERE d=4; 2788c 27891 27902 2791DROP TABLE t1; 2792# 2793# Bug #45386: Wrong query result with MIN function in field list, 2794# WHERE and GROUP BY clause 2795# 2796CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB; 2797INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); 2798INSERT INTO t SELECT * FROM t; 2799INSERT INTO t SELECT * FROM t; 2800ANALYZE TABLE t; 2801Table Op Msg_type Msg_text 2802test.t analyze status OK 2803# test MIN 2804#should use range with index for group by 2805EXPLAIN 2806SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; 2807id select_type table type possible_keys key key_len ref rows Extra 28081 SIMPLE t range NULL a 10 NULL 251 Using where; Using index for group-by 2809#should return 1 row 2810SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; 2811a MIN(b) 28122 1 2813# test MAX 2814#should use range with index for group by 2815EXPLAIN 2816SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; 2817id select_type table type possible_keys key key_len ref rows Extra 28181 SIMPLE t range NULL a 10 NULL 251 Using where; Using index for group-by 2819#should return 1 row 2820SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; 2821a MAX(b) 28222 0 2823# test 3 ranges and use the middle one 2824INSERT INTO t SELECT a, 2 FROM t; 2825#should use range with index for group by 2826EXPLAIN 2827SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; 2828id select_type table type possible_keys key key_len ref rows Extra 28291 SIMPLE t range NULL a 10 NULL 251 Using where; Using index for group-by 2830#should return 1 row 2831SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; 2832a MAX(b) 28332 1 2834DROP TABLE t; 2835# 2836# Bug #48472: Loose index scan inappropriately chosen for some WHERE 2837# conditions 2838# 2839CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB; 2840INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); 2841INSERT INTO t SELECT * FROM t; 2842ANALYZE TABLE t; 2843Table Op Msg_type Msg_text 2844test.t analyze status OK 2845SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a; 2846a MAX(b) 28472 0 2848DROP TABLE t; 2849End of 5.0 tests 2850# 2851# Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in 2852# server crash 2853# 2854CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=RocksDB; 2855INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); 2856INSERT INTO t SELECT * FROM t; 2857SELECT a, MAX(b) FROM t WHERE b GROUP BY a; 2858a MAX(b) 28592 1 2860DROP TABLE t; 2861CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b)) engine=RocksDB; 2862INSERT INTO t1 VALUES(1,1),(2,1); 2863ANALYZE TABLE t1; 2864Table Op Msg_type Msg_text 2865test.t1 analyze status OK 2866SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b; 2867c b 28681 1 2869SELECT a FROM t1 WHERE b=1; 2870a 28711 28722 2873DROP TABLE t1; 2874# 2875# Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column 2876# for NULL 2877# 2878## Test for NULLs allowed 2879CREATE TABLE t1 ( a INT, KEY (a) ) engine=RocksDB; 2880INSERT INTO t1 VALUES (1), (2), (3); 2881ANALYZE TABLE t1; 2882Table Op Msg_type Msg_text 2883test.t1 analyze status OK 2884EXPLAIN 2885SELECT MIN( a ) FROM t1 WHERE a = NULL; 2886id select_type table type possible_keys key key_len ref rows Extra 2887x x x x x x x x x Impossible WHERE noticed after reading const tables 2888SELECT MIN( a ) FROM t1 WHERE a = NULL; 2889MIN( a ) 2890NULL 2891EXPLAIN 2892SELECT MIN( a ) FROM t1 WHERE a <> NULL; 2893id select_type table type possible_keys key key_len ref rows Extra 2894x x x x x x x x x Impossible WHERE noticed after reading const tables 2895SELECT MIN( a ) FROM t1 WHERE a <> NULL; 2896MIN( a ) 2897NULL 2898EXPLAIN 2899SELECT MIN( a ) FROM t1 WHERE a > NULL; 2900id select_type table type possible_keys key key_len ref rows Extra 2901x x x x x x x x x Impossible WHERE noticed after reading const tables 2902SELECT MIN( a ) FROM t1 WHERE a > NULL; 2903MIN( a ) 2904NULL 2905EXPLAIN 2906SELECT MIN( a ) FROM t1 WHERE a < NULL; 2907id select_type table type possible_keys key key_len ref rows Extra 2908x x x x x x x x x Impossible WHERE noticed after reading const tables 2909SELECT MIN( a ) FROM t1 WHERE a < NULL; 2910MIN( a ) 2911NULL 2912EXPLAIN 2913SELECT MIN( a ) FROM t1 WHERE a <=> NULL; 2914id select_type table type possible_keys key key_len ref rows Extra 2915x x x x x x x x x No matching min/max row 2916SELECT MIN( a ) FROM t1 WHERE a <=> NULL; 2917MIN( a ) 2918NULL 2919EXPLAIN 2920SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 2921id select_type table type possible_keys key key_len ref rows Extra 2922x x x x x x x x x Impossible WHERE noticed after reading const tables 2923SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 2924MIN( a ) 2925NULL 2926EXPLAIN 2927SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 2928id select_type table type possible_keys key key_len ref rows Extra 2929x x x x x x x x x Impossible WHERE noticed after reading const tables 2930SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 2931MIN( a ) 2932NULL 2933EXPLAIN 2934SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 2935id select_type table type possible_keys key key_len ref rows Extra 2936x x x x x x x x x Impossible WHERE noticed after reading const tables 2937SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 2938MIN( a ) 2939NULL 2940EXPLAIN 2941SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 2942id select_type table type possible_keys key key_len ref rows Extra 2943x x x x x x x x x Using where; Using index 2944x x x x x x x x x Using where; Using index 2945SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 2946MIN( a ) 2947NULL 2948EXPLAIN 2949SELECT MIN( a ) FROM t1 WHERE a IS NULL; 2950id select_type table type possible_keys key key_len ref rows Extra 2951x x x x x x x x x No matching min/max row 2952SELECT MIN( a ) FROM t1 WHERE a IS NULL; 2953MIN( a ) 2954NULL 2955INSERT INTO t1 VALUES (NULL), (NULL); 2956ANALYZE TABLE t1; 2957Table Op Msg_type Msg_text 2958test.t1 analyze status OK 2959EXPLAIN 2960SELECT MIN( a ) FROM t1 WHERE a = NULL; 2961id select_type table type possible_keys key key_len ref rows Extra 2962x x x x x x x x x Impossible WHERE noticed after reading const tables 2963SELECT MIN( a ) FROM t1 WHERE a = NULL; 2964MIN( a ) 2965NULL 2966EXPLAIN 2967SELECT MIN( a ) FROM t1 WHERE a <> NULL; 2968id select_type table type possible_keys key key_len ref rows Extra 2969x x x x x x x x x Impossible WHERE noticed after reading const tables 2970SELECT MIN( a ) FROM t1 WHERE a <> NULL; 2971MIN( a ) 2972NULL 2973EXPLAIN 2974SELECT MIN( a ) FROM t1 WHERE a > NULL; 2975id select_type table type possible_keys key key_len ref rows Extra 2976x x x x x x x x x Impossible WHERE noticed after reading const tables 2977SELECT MIN( a ) FROM t1 WHERE a > NULL; 2978MIN( a ) 2979NULL 2980EXPLAIN 2981SELECT MIN( a ) FROM t1 WHERE a < NULL; 2982id select_type table type possible_keys key key_len ref rows Extra 2983x x x x x x x x x Impossible WHERE noticed after reading const tables 2984SELECT MIN( a ) FROM t1 WHERE a < NULL; 2985MIN( a ) 2986NULL 2987EXPLAIN 2988SELECT MIN( a ) FROM t1 WHERE a <=> NULL; 2989id select_type table type possible_keys key key_len ref rows Extra 2990x x x x x x x x x Select tables optimized away 2991SELECT MIN( a ) FROM t1 WHERE a <=> NULL; 2992MIN( a ) 2993NULL 2994EXPLAIN 2995SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 2996id select_type table type possible_keys key key_len ref rows Extra 2997x x x x x x x x x Impossible WHERE noticed after reading const tables 2998SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 2999MIN( a ) 3000NULL 3001EXPLAIN 3002SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 3003id select_type table type possible_keys key key_len ref rows Extra 3004x x x x x x x x x Impossible WHERE noticed after reading const tables 3005SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 3006MIN( a ) 3007NULL 3008EXPLAIN 3009SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 3010id select_type table type possible_keys key key_len ref rows Extra 3011x x x x x x x x x Impossible WHERE noticed after reading const tables 3012SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 3013MIN( a ) 3014NULL 3015EXPLAIN 3016SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 3017id select_type table type possible_keys key key_len ref rows Extra 3018x x x x x x x x x Using where; Using index 3019x x x x x x x x x Using where; Using index 3020SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 3021MIN( a ) 3022NULL 3023EXPLAIN 3024SELECT MIN( a ) FROM t1 WHERE a IS NULL; 3025id select_type table type possible_keys key key_len ref rows Extra 3026x x x x x x x x x Select tables optimized away 3027SELECT MIN( a ) FROM t1 WHERE a IS NULL; 3028MIN( a ) 3029NULL 3030DROP TABLE t1; 3031## Test for NOT NULLs 3032CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY) engine=RocksDB; 3033INSERT INTO t1 VALUES (1), (2), (3); 3034ANALYZE TABLE t1; 3035Table Op Msg_type Msg_text 3036test.t1 analyze status OK 3037# 3038# NULL-safe operator test disabled for non-NULL indexed columns. 3039# 3040# See bugs 3041# 3042# - Bug#52173: Reading NULL value from non-NULL index gives 3043# wrong result in embedded server 3044# 3045# - Bug#52174: Sometimes wrong plan when reading a MAX value from 3046# non-NULL index 3047# 3048EXPLAIN 3049SELECT MIN( a ) FROM t1 WHERE a = NULL; 3050id select_type table type possible_keys key key_len ref rows Extra 3051x x x x x x x x x Impossible WHERE noticed after reading const tables 3052SELECT MIN( a ) FROM t1 WHERE a = NULL; 3053MIN( a ) 3054NULL 3055EXPLAIN 3056SELECT MIN( a ) FROM t1 WHERE a <> NULL; 3057id select_type table type possible_keys key key_len ref rows Extra 3058x x x x x x x x x Impossible WHERE noticed after reading const tables 3059SELECT MIN( a ) FROM t1 WHERE a <> NULL; 3060MIN( a ) 3061NULL 3062EXPLAIN 3063SELECT MIN( a ) FROM t1 WHERE a > NULL; 3064id select_type table type possible_keys key key_len ref rows Extra 3065x x x x x x x x x Impossible WHERE noticed after reading const tables 3066SELECT MIN( a ) FROM t1 WHERE a > NULL; 3067MIN( a ) 3068NULL 3069EXPLAIN 3070SELECT MIN( a ) FROM t1 WHERE a < NULL; 3071id select_type table type possible_keys key key_len ref rows Extra 3072x x x x x x x x x Impossible WHERE noticed after reading const tables 3073SELECT MIN( a ) FROM t1 WHERE a < NULL; 3074MIN( a ) 3075NULL 3076EXPLAIN 3077SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 3078id select_type table type possible_keys key key_len ref rows Extra 3079x x x x x x x x x Impossible WHERE noticed after reading const tables 3080SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND 10; 3081MIN( a ) 3082NULL 3083EXPLAIN 3084SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 3085id select_type table type possible_keys key key_len ref rows Extra 3086x x x x x x x x x Impossible WHERE noticed after reading const tables 3087SELECT MIN( a ) FROM t1 WHERE a BETWEEN NULL AND NULL; 3088MIN( a ) 3089NULL 3090EXPLAIN 3091SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 3092id select_type table type possible_keys key key_len ref rows Extra 3093x x x x x x x x x Impossible WHERE noticed after reading const tables 3094SELECT MIN( a ) FROM t1 WHERE a BETWEEN 10 AND NULL; 3095MIN( a ) 3096NULL 3097EXPLAIN 3098SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 3099id select_type table type possible_keys key key_len ref rows Extra 3100x x x x x x x x x Using where; Using index 3101x x x x x x x x x Using where; Using index 3102SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 3103MIN( a ) 3104NULL 3105EXPLAIN 3106SELECT MIN( a ) FROM t1 WHERE a IS NULL; 3107id select_type table type possible_keys key key_len ref rows Extra 3108x x x x x x x x x Impossible WHERE 3109SELECT MIN( a ) FROM t1 WHERE a IS NULL; 3110MIN( a ) 3111NULL 3112DROP TABLE t1; 3113# 3114# Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at 3115# opt_sum.cc:305 3116# 3117CREATE TABLE t1 ( a INT, KEY (a) ) engine=RocksDB; 3118INSERT INTO t1 VALUES (1), (2), (3); 3119SELECT MIN( a ) AS min_a 3120FROM t1 3121WHERE a > 1 AND a IS NULL 3122ORDER BY min_a; 3123min_a 3124NULL 3125DROP TABLE t1; 3126End of 5.1 tests 3127# 3128# WL#3220 (Loose index scan for COUNT DISTINCT) 3129# 3130CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b)) engine=RocksDB; 3131INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1); 3132INSERT INTO t1 SELECT a, b + 4, 1 FROM t1; 3133INSERT INTO t1 SELECT a + 1, b, 1 FROM t1; 3134ANALYZE TABLE t1; 3135Table Op Msg_type Msg_text 3136test.t1 analyze status OK 3137CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c)) engine=RocksDB; 3138INSERT INTO t2 VALUES (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1), 3139(1,4,1,1,1,1); 3140INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2; 3141INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2; 3142ANALYZE TABLE t2; 3143Table Op Msg_type Msg_text 3144test.t2 analyze status OK 3145EXPLAIN SELECT COUNT(DISTINCT a) FROM t1; 3146id select_type table type possible_keys key key_len ref rows Extra 31471 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by 3148SELECT COUNT(DISTINCT a) FROM t1; 3149COUNT(DISTINCT a) 31502 3151EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1; 3152id select_type table type possible_keys key key_len ref rows Extra 31531 SIMPLE t1 range NULL a 10 NULL 501 Using index for group-by 3154SELECT COUNT(DISTINCT a,b) FROM t1; 3155COUNT(DISTINCT a,b) 315616 3157EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1; 3158id select_type table type possible_keys key key_len ref rows Extra 31591 SIMPLE t1 range NULL a 10 NULL 501 Using index for group-by 3160SELECT COUNT(DISTINCT b,a) FROM t1; 3161COUNT(DISTINCT b,a) 316216 3163EXPLAIN SELECT COUNT(DISTINCT b) FROM t1; 3164id select_type table type possible_keys key key_len ref rows Extra 31651 SIMPLE t1 index NULL a 10 NULL 1000 Using index 3166SELECT COUNT(DISTINCT b) FROM t1; 3167COUNT(DISTINCT b) 31688 3169EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; 3170id select_type table type possible_keys key key_len ref rows Extra 31711 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by 3172SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; 3173COUNT(DISTINCT a) 31741 31751 3176EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; 3177id select_type table type possible_keys key key_len ref rows Extra 31781 SIMPLE t1 range NULL a 10 NULL 501 Using index for group-by 3179SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; 3180COUNT(DISTINCT b) 31818 31828 3183EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; 3184id select_type table type possible_keys key key_len ref rows Extra 31851 SIMPLE t1 index NULL a 10 NULL 1000 Using index; Using filesort 3186SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; 3187COUNT(DISTINCT a) 31882 31892 31902 31912 31922 31932 31942 31952 3196EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1; 3197id select_type table type possible_keys key key_len ref rows Extra 31981 SIMPLE t1 index NULL a 10 NULL 1000 Using index 3199SELECT DISTINCT COUNT(DISTINCT a) FROM t1; 3200COUNT(DISTINCT a) 32012 3202EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1; 3203id select_type table type possible_keys key key_len ref rows Extra 32041 SIMPLE t1 index NULL a 10 NULL 1000 Using index 3205SELECT COUNT(DISTINCT a, b + 0) FROM t1; 3206COUNT(DISTINCT a, b + 0) 320716 3208EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; 3209id select_type table type possible_keys key key_len ref rows Extra 32101 SIMPLE t1 index NULL a 10 NULL 1000 Using index 3211SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; 3212COUNT(DISTINCT a) 32132 3214EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; 3215id select_type table type possible_keys key key_len ref rows Extra 32161 SIMPLE t1 ALL NULL NULL NULL NULL 1000 3217SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; 3218COUNT(DISTINCT a) 32192 3220EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 3221id select_type table type possible_keys key key_len ref rows Extra 32221 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by 3223SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 32241 32251 3226EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; 3227id select_type table type possible_keys key key_len ref rows Extra 32281 SIMPLE t1 range NULL a 10 NULL 501 Using index for group-by 3229SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; 32301 32311 32321 3233EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; 3234id select_type table type possible_keys key key_len ref rows Extra 32351 SIMPLE t1_1 index NULL a 10 NULL 1000 Using index; Using temporary; Using filesort 32361 SIMPLE t1_2 index NULL a 10 NULL 1000 Using index; Using join buffer (flat, BNL join) 3237SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; 3238COUNT(DISTINCT t1_1.a) 32391 32401 3241EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1; 3242id select_type table type possible_keys key key_len ref rows Extra 32431 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by 3244SELECT COUNT(DISTINCT a), 12 FROM t1; 3245COUNT(DISTINCT a) 12 32462 12 3247EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2; 3248id select_type table type possible_keys key key_len ref rows Extra 32491 SIMPLE t2 range NULL a 15 NULL 501 Using index for group-by 3250SELECT COUNT(DISTINCT a, b, c) FROM t2; 3251COUNT(DISTINCT a, b, c) 325216 3253EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; 3254id select_type table type possible_keys key key_len ref rows Extra 32551 SIMPLE t2 range NULL a 5 NULL 126 Using index for group-by 3256SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; 3257COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT a) 32582 3 1.5000 3259EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; 3260id select_type table type possible_keys key key_len ref rows Extra 32611 SIMPLE t2 ALL NULL NULL NULL NULL 1000 3262SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; 3263COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT f) 32642 3 1.0000 3265EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; 3266id select_type table type possible_keys key key_len ref rows Extra 32671 SIMPLE t2 range NULL a 10 NULL 251 Using index for group-by 3268SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; 3269COUNT(DISTINCT a, b) COUNT(DISTINCT b, a) 327016 16 3271EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; 3272id select_type table type possible_keys key key_len ref rows Extra 32731 SIMPLE t2 ALL NULL NULL NULL NULL 1000 3274SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; 3275COUNT(DISTINCT a, b) COUNT(DISTINCT b, f) 327616 8 3277EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; 3278id select_type table type possible_keys key key_len ref rows Extra 32791 SIMPLE t2 ALL NULL NULL NULL NULL 1000 3280SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; 3281COUNT(DISTINCT a, b) COUNT(DISTINCT b, d) 328216 8 3283EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; 3284id select_type table type possible_keys key key_len ref rows Extra 32851 SIMPLE t2 range NULL a 15 NULL 501 Using index for group-by 3286SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; 3287a c COUNT(DISTINCT c, a, b) 32881 1 1 32891 1 1 32901 1 1 32911 1 1 32921 1 1 32931 1 1 32941 1 1 32951 1 1 32962 1 1 32972 1 1 32982 1 1 32992 1 1 33002 1 1 33012 1 1 33022 1 1 33032 1 1 3304EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2 3305WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; 3306id select_type table type possible_keys key key_len ref rows Extra 33071 SIMPLE t2 range a a 15 NULL 501 Using where; Using index for group-by 3308SELECT COUNT(DISTINCT c, a, b) FROM t2 3309WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; 3310COUNT(DISTINCT c, a, b) 3311EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 3312GROUP BY b; 3313id select_type table type possible_keys key key_len ref rows Extra 33141 SIMPLE t2 ref a a 5 const 1000 Using where; Using index 3315SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 3316GROUP BY b; 3317COUNT(DISTINCT b) SUM(DISTINCT b) 3318EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 3319id select_type table type possible_keys key key_len ref rows Extra 33201 SIMPLE t2 range NULL a 10 NULL 251 Using index for group-by 3321SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 3322a COUNT(DISTINCT b) SUM(DISTINCT b) 33231 8 36 33242 8 36 3325EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 3326id select_type table type possible_keys key key_len ref rows Extra 33271 SIMPLE t2 range NULL a 10 NULL 251 Using index for group-by 3328SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; 3329COUNT(DISTINCT b) SUM(DISTINCT b) 33308 36 33318 36 3332EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; 3333id select_type table type possible_keys key key_len ref rows Extra 33341 SIMPLE t2 ALL NULL NULL NULL NULL 1000 Using where 3335SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; 3336COUNT(DISTINCT a, b) 33370 3338EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 3339WHERE b = 13 AND c = 42 GROUP BY a; 3340id select_type table type possible_keys key key_len ref rows Extra 33411 SIMPLE t2 range NULL a 15 NULL 126 Using where; Using index for group-by 3342SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 3343WHERE b = 13 AND c = 42 GROUP BY a; 3344a COUNT(DISTINCT a) SUM(DISTINCT a) 3345# This query could have been resolved using loose index scan since 3346# the second part of count(..) is defined by a constant predicate 3347EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; 3348id select_type table type possible_keys key key_len ref rows Extra 33491 SIMPLE t2 index NULL a 15 NULL 1000 Using where; Using index 3350SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; 3351COUNT(DISTINCT a, b) SUM(DISTINCT a) 33520 NULL 3353EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; 3354id select_type table type possible_keys key key_len ref rows Extra 33551 SIMPLE t2 index NULL a 15 NULL 1000 Using index 3356SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; 3357SUM(DISTINCT a) MAX(b) 33581 8 33592 8 3360EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 3361id select_type table type possible_keys key key_len ref rows Extra 33621 SIMPLE t2 range NULL a 15 NULL 501 Using index for group-by 3363SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 336442 * (a + c + COUNT(DISTINCT c, a, b)) 3365126 3366126 3367126 3368126 3369126 3370126 3371126 3372126 3373168 3374168 3375168 3376168 3377168 3378168 3379168 3380168 3381EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; 3382id select_type table type possible_keys key key_len ref rows Extra 33831 SIMPLE t2 index NULL a 15 NULL 1000 Using index 3384SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; 3385(SUM(DISTINCT a) + MAX(b)) 33869 338710 3388DROP TABLE t1,t2; 3389# end of WL#3220 tests 3390# 3391# Bug#50539: Wrong result when loose index scan is used for an aggregate 3392# function with distinct 3393# 3394CREATE TABLE t1 ( 3395f1 int(11) NOT NULL DEFAULT '0', 3396f2 char(1) NOT NULL DEFAULT '', 3397PRIMARY KEY (f1,f2) 3398) engine=RocksDB; 3399insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), 3400(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D'); 3401ANALYZE TABLE t1; 3402Table Op Msg_type Msg_text 3403test.t1 analyze status OK 3404SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; 3405f1 COUNT(DISTINCT f2) 34061 3 34072 1 34083 4 3409explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; 3410id select_type table type possible_keys key key_len ref rows Extra 34111 SIMPLE t1 index NULL PRIMARY 5 NULL 1000 Using index 3412drop table t1; 3413# End of test#50539. 3414# 3415# Bug#17217128 - BAD INTERACTION BETWEEN MIN/MAX AND 3416# "HAVING SUM(DISTINCT)": WRONG RESULTS. 3417# 3418CREATE TABLE t (a INT, b INT, KEY(a,b)) engine=RocksDB; 3419INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5); 3420ANALYZE TABLE t; 3421Table Op Msg_type Msg_text 3422test.t analyze status OK 3423ANALYZE TABLE t; 3424Table Op Msg_type Msg_text 3425test.t analyze status OK 3426SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; 3427a SUM(DISTINCT a) MIN(b) 34281 1 0 34292 2 2 34303 3 2 34314 4 4 3432EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; 3433id select_type table type possible_keys key key_len ref rows Extra 34341 SIMPLE t index NULL a 10 NULL 1000 Using index 3435SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; 3436a SUM(DISTINCT a) MAX(b) 34371 1 1 34382 2 2 34393 3 3 34404 4 5 3441EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; 3442id select_type table type possible_keys key key_len ref rows Extra 34431 SIMPLE t index NULL a 10 NULL 1000 Using index 3444SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); 3445a MAX(b) 34461 1 34472 2 34483 3 34494 5 3450EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); 3451id select_type table type possible_keys key key_len ref rows Extra 34521 SIMPLE t index NULL a 10 NULL 1000 Using index 3453SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; 3454SUM(DISTINCT a) MIN(b) MAX(b) 345510 0 5 3456EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; 3457id select_type table type possible_keys key key_len ref rows Extra 34581 SIMPLE t index NULL a 10 NULL 1000 Using index 3459SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; 3460a SUM(DISTINCT a) MIN(b) MAX(b) 34611 1 0 1 34622 2 2 2 34633 3 2 3 34644 4 4 5 3465EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; 3466id select_type table type possible_keys key key_len ref rows Extra 34671 SIMPLE t index NULL a 10 NULL 1000 Using index 3468DROP TABLE t; 3469# 3470# Bug#18109609: LOOSE INDEX SCAN IS NOT USED WHEN IT SHOULD 3471# 3472CREATE TABLE t1 ( 3473id INT AUTO_INCREMENT PRIMARY KEY, 3474c1 INT, 3475c2 INT, 3476KEY(c1,c2)) engine=RocksDB; 3477INSERT INTO t1(c1,c2) VALUES 3478(1, 1), (1,2), (2,1), (2,2), (3,1), (3,2), (3,3), (4,1), (4,2), (4,3), 3479(4,4), (4,5), (4,6), (4,7), (4,8), (4,9), (4,10), (4,11), (4,12), (4,13), 3480(4,14), (4,15), (4,16), (4,17), (4,18), (4,19), (4,20),(5,5); 3481ANALYZE TABLE t1; 3482Table Op Msg_type Msg_text 3483test.t1 analyze status OK 3484EXPLAIN SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; 3485id select_type table type possible_keys key key_len ref rows Extra 34861 SIMPLE t1 ref c1 c1 5 const 1000 Using index 3487FLUSH STATUS; 3488SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; 3489MAX(c2) c1 349020 4 3491SHOW SESSION STATUS LIKE 'Handler_read%'; 3492Variable_name Value 3493Handler_read_first 0 3494Handler_read_key 1 3495Handler_read_last 0 3496Handler_read_next 20 3497Handler_read_prev 0 3498Handler_read_retry 0 3499Handler_read_rnd 0 3500Handler_read_rnd_deleted 0 3501Handler_read_rnd_next 0 3502DROP TABLE t1; 3503# End of test for Bug#18109609 3504set global debug_dbug=@debug_tmp; 3505