diff options
| author | Sergei Petrunia <psergey@askmonty.org> | 2019-06-16 12:44:04 +0300 |
|---|---|---|
| committer | Sergei Petrunia <psergey@askmonty.org> | 2019-06-16 12:44:04 +0300 |
| commit | 9c75b3d2831e6822d3d122b8e4554ff77400bc62 (patch) | |
| tree | 70f98cb6e34ef942364a442f499323623c5d332f | |
| parent | e9e5e7fc925bc751b9656de26f7f232a56225d9b (diff) | |
| download | mariadb-git-9c75b3d2831e6822d3d122b8e4554ff77400bc62.tar.gz | |
Post-merge fixes
19 files changed, 1670 insertions, 31 deletions
diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 3f1db15a426..b7d0e0186a0 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -8774,7 +8774,6 @@ int ha_rocksdb::check(THD *const thd, HA_CHECK_OPT *const check_opt) { } // NO_LINT_DEBUG sql_print_verbose_info("CHECKTABLE %s: ... %lld index entries checked " - "CHECKTABLE %s: ... %lld index entries checked " "(%lld had checksums)", table_name, rows, checksums); diff --git a/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc b/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc new file mode 100644 index 00000000000..79ac367a73b --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc @@ -0,0 +1,1425 @@ +# +# Test file for WL#1724 (Min/Max Optimization for Queries with Group By Clause). +# The queries in this file test query execution via QUICK_GROUP_MIN_MAX_SELECT. +# + +# +# TODO: +# Add queries with: +# - C != const +# - C IS NOT NULL +# - HAVING clause + +--disable_warnings +drop table if exists t1; +--enable_warnings + +eval create table t1 ( + a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' ' +) engine=$engine; + +insert into t1 (a1, a2, b, c, d) values +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), +('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), +('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), +('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), +('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), +('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), +('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), +('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), +('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); + +create index idx_t1_0 on t1 (a1); +create index idx_t1_1 on t1 (a1,a2,b,c); +create index idx_t1_2 on t1 (a1,a2,b); +analyze table t1; + +# t2 is the same as t1, but with some NULLs in the MIN/MAX column, and +# one more nullable attribute + +--disable_warnings +drop table if exists t2; +--enable_warnings + +eval create table t2 ( + a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' ' +) engine=$engine; +insert into t2 select * from t1; +# add few rows with NULL's in the MIN/MAX column +insert into t2 (a1, a2, b, c, d) values +('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), +('a','a','a',NULL,'xyz'), +('a','a','b',NULL,'xyz'), +('a','b','a',NULL,'xyz'), +('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), +('d','b','b',NULL,'xyz'), +('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), +('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'), +('a','a',NULL,'a777','xyz'),('a','a',NULL,'a888','xyz'),('a','a',NULL,'a999','xyz'), +('a','a','a',NULL,'xyz'), +('a','a','b',NULL,'xyz'), +('a','b','a',NULL,'xyz'), +('c','a',NULL,'c777','xyz'),('c','a',NULL,'c888','xyz'),('c','a',NULL,'c999','xyz'), +('d','b','b',NULL,'xyz'), +('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'),('e','a','a',NULL,'xyz'), +('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'),('e','a','b',NULL,'xyz'); + +create index idx_t2_0 on t2 (a1); +create index idx_t2_1 on t2 (a1,a2,b,c); +create index idx_t2_2 on t2 (a1,a2,b); +analyze table t2; + +# Table t3 is the same as t1, but with smaller column lenghts. +# This allows to test different branches of the cost computation procedure +# when the number of keys per block are less than the number of keys in the +# sub-groups formed by predicates over non-group attributes. + +--disable_warnings +drop table if exists t3; +--enable_warnings + +eval create table t3 ( + a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' ' +) engine=$engine; + +insert into t3 (a1, a2, b, c, d) values +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); +insert into t3 (a1, a2, b, c, d) values +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); +insert into t3 (a1, a2, b, c, d) values +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); +insert into t3 (a1, a2, b, c, d) values +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'); + +create index idx_t3_0 on t3 (a1); +create index idx_t3_1 on t3 (a1,a2,b,c); +create index idx_t3_2 on t3 (a1,a2,b); +analyze table t3; + + +# +# Queries without a WHERE clause. These queries do not use ranges. +# + +# plans +explain select a1, min(a2) from t1 group by a1; +explain select a1, max(a2) from t1 group by a1; +explain select a1, min(a2), max(a2) from t1 group by a1; +explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; +explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; +--replace_column 7 # 9 # +explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; +# Select fields in different order +explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; +explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; +explain select min(a2) from t1 group by a1; +explain select a2, min(c), max(c) from t1 group by a1,a2,b; + +# queries +select a1, min(a2) from t1 group by a1; +select a1, max(a2) from t1 group by a1; +select a1, min(a2), max(a2) from t1 group by a1; +select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; +select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; +select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; +# Select fields in different order +select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; +select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; +select min(a2) from t1 group by a1; +select a2, min(c), max(c) from t1 group by a1,a2,b; + +# +# Queries with a where clause +# + +# A) Preds only over the group 'A' attributes +# plans +explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; +explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; +explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; +explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; +explain 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; +explain 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; +explain select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; +explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; + +--replace_column 9 # +explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; +--replace_column 9 # +explain select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; +--replace_column 9 # +explain 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; +--replace_column 9 # +explain 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; +--replace_column 9 # +explain select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; +--replace_column 9 # +explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; + +# queries +select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; +select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; +select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; +select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; +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; +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; +select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; +select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; + +select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; +select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; +select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; +select a1,a2,b, max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; +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; +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; +select a1,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b; +select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; + +# B) Equalities only over the non-group 'B' attributes +# plans +explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +explain 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; + +explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + +explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; +explain select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + +explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; +explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + +explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; +explain select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + +explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + +explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; +explain select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + +explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; +explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + +explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; +explain select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + +# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() +explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + +explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + +# queries +select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +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; + +select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; +select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + +select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; +select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + +select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; +select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + +select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; +select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + +select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + +select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; +select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + +select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; +select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + +select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; +select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + +select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; +select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + +# these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() +select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + +select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; +select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + + +# IS NULL (makes sense for t2 only) +# plans +explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; +explain select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; + +explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; +explain select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; + +explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; +explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; +explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; + +# queries +select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; +select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; +select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; +select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; +select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; +select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; +select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; +select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; + +# C) Range predicates for the MIN/MAX attribute +# plans +--replace_column 9 # +explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; +explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; +explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; +explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; +explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; +explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; +explain 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; +explain 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; + +--replace_column 9 # +explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; +--replace_column 9 # +explain 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; + +# queries +select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; +select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; +select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; +select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; +select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; +select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; +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; +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; + +select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; +select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; +select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; +select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; +select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; +select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; +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; + +# analyze the sub-select +explain select a1,a2,b,min(c),max(c) from t1 +where exists ( select * from t2 where t2.c = t1.c ) +group by a1,a2,b; + +# the sub-select is unrelated to MIN/MAX +explain select a1,a2,b,min(c),max(c) from t1 +where exists ( select * from t2 where t2.c > 'b1' ) +group by a1,a2,b; + + +# A,B,C) Predicates referencing mixed classes of attributes +# plans +explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; +explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; +explain 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; +explain 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; +explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; +explain 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; + +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; +--replace_column 9 # +explain 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; +--replace_column 9 # +explain 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; +--replace_column 9 # +explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; + +# queries +select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; +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; +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; +select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; +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; + +select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; +select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; +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; +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; +select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; + + +# +# GROUP BY queries without MIN/MAX +# + +# plans +explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; +explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; +explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; + +--replace_column 9 # +explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; +--replace_column 9 # +explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; + +# queries +select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; +select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; +select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; + +select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; +select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; +select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; + +# +# DISTINCT queries +# + +# plans +explain select distinct a1,a2,b from t1; +explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); +explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); +explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); +explain select distinct b from t1 where (a2 >= 'b') and (b = 'a'); +explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b'; +explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e'; + +--replace_column 9 # +explain select distinct a1,a2,b from t2; +--replace_column 9 # +explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); +explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); +--replace_column 9 # +explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); +explain select distinct b from t2 where (a2 >= 'b') and (b = 'a'); +explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b'; +explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e'; + +# queries +select distinct a1,a2,b from t1; +select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); +select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); +select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); +select distinct b from t1 where (a2 >= 'b') and (b = 'a'); +select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b'; +select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e'; + +select distinct a1,a2,b from t2; +select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); +select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); +select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); +select distinct b from t2 where (a2 >= 'b') and (b = 'a'); +select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b'; +select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e'; + +# BUG #6303 +select distinct t_00.a1 +from t1 t_00 +where exists ( select * from t2 where a1 = t_00.a1 ); + +# BUG #8532 - SELECT DISTINCT a, a causes server to crash +select distinct a1,a1 from t1; +select distinct a2,a1,a2,a1 from t1; +select distinct t1.a1,t2.a1 from t1,t2; + + +# +# DISTINCT queries with GROUP-BY +# + +# plans +explain select distinct a1,a2,b from t1; +explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; +explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1; +explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1; + +--replace_column 9 # +explain select distinct a1,a2,b from t2; +--replace_column 9 # +explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +--replace_column 9 # +explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +--replace_column 9 # +explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; +--replace_column 9 # +explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +--replace_column 9 # +explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1; +--replace_column 9 # +explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1; + +# queries +select distinct a1,a2,b from t1; +select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; +select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1; +select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1; + +select distinct a1,a2,b from t2; +select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; +select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; +select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1; +select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1; + + +# +# COUNT (DISTINCT cols) queries +# + +explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); +explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); +explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); +explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); +explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); + +select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); +select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); +select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); +select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); +select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); + +# +# Queries with expressions in the select clause +# + +explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; +explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; +explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; +explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; +explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; + +select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; +select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; +select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; +select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; +select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; + + +# +# Negative examples: queries that should NOT be treated as optimizable by +# QUICK_GROUP_MIN_MAX_SELECT +# + +# select a non-indexed attribute +explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b; + +explain select a1,a2,b,d from t1 group by a1,a2,b; + +# predicate that references an attribute that is after the MIN/MAX argument +# in the index +explain extended select a1,a2,min(b),max(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; + +# predicate that references a non-indexed attribute +explain extended 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') and (d > 'xy2') group by a1,a2,b; + +explain extended select a1,a2,b,c from t1 +where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c; + +# non-equality predicate for a non-group select attribute +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b < 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b' and b >= 'a') group by a1; +explain 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; + +# non-group field with an equality predicate that references a keypart after the +# MIN/MAX argument +explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; +select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; + +# disjunction for a non-group select attribute +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1; + +# non-range predicate for the MIN/MAX attribute +explain select a1,a2,b,min(c),max(c) from t2 +where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b; + +# not all attributes are indexed by one index +explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c; + +# other aggregate functions than MIN/MAX +explain select a1,a2,count(a2) from t1 group by a1,a2,b; +explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; +explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; + +# test multi_range_groupby flag +set optimizer_switch = 'multi_range_groupby=off'; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; +set optimizer_switch = 'default'; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; + + +# +# Bug #16710: select distinct doesn't return all it should +# + +explain select distinct(a1) from t1 where ord(a2) = 98; +select distinct(a1) from t1 where ord(a2) = 98; + +# +# BUG#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX. +# + +explain select a1 from t1 where a2 = 'b' group by a1; +select a1 from t1 where a2 = 'b' group by a1; + +explain select distinct a1 from t1 where a2 = 'b'; +select distinct a1 from t1 where a2 = 'b'; + +# +# Bug #12672: primary key implcitly included in every innodb index +# +# Test case moved to group_min_max_innodb + + +# +# Bug #6142: a problem with the empty innodb table +# +# Test case moved to group_min_max_innodb + + +# +# Bug #9798: group by with rollup +# +# Test case moved to group_min_max_innodb + + +# +# Bug #13293 Wrongly used index results in endless loop. +# +# Test case moved to group_min_max_innodb + + +drop table t1,t2,t3; + +# +# Bug #14920 Ordering aggregated result sets with composite primary keys +# corrupts resultset +# +eval create table t1 (c1 int not null,c2 int not null, primary key(c1,c2)) engine=$engine; +insert into t1 (c1,c2) values +(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9); +select distinct c1, c2 from t1 order by c2; +select c1,min(c2) as c2 from t1 group by c1 order by c2; +select c1,c2 from t1 group by c1,c2 order by c2; +drop table t1; + +# +# Bug #16203: Analysis for possible min/max optimization erroneously +# returns impossible range +# + +eval CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b)) engine=$engine; +INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4); +OPTIMIZE TABLE t1; + +SELECT a FROM t1 WHERE a='AA' GROUP BY a; +SELECT a FROM t1 WHERE a='BB' GROUP BY a; + +EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a; +EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a; + +SELECT DISTINCT a FROM t1 WHERE a='BB'; +SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%'; +SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a; + +DROP TABLE t1; + + +# +# Bug #15102: select distinct returns empty result, select count +# distinct > 0 (correct) +# + +CREATE TABLE t1 ( + a int(11) NOT NULL DEFAULT '0', + b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '', + PRIMARY KEY (a,b) + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +delimiter |; + +CREATE PROCEDURE a(x INT) +BEGIN + DECLARE rnd INT; + DECLARE cnt INT; + + WHILE x > 0 DO + SET rnd= x % 100; + SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd); + INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR)); + SET x= x - 1; + END WHILE; +END| + +DELIMITER ;| + +CALL a(1000); + +SELECT a FROM t1 WHERE a=0; +SELECT DISTINCT a FROM t1 WHERE a=0; +SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0; + +DROP TABLE t1; +DROP PROCEDURE a; + +# +# Bug #18068: SELECT DISTINCT +# + +eval CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)) engine=$engine; + +INSERT INTO t1 (a) VALUES + (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'), + ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'), + ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); + +EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; +SELECT DISTINCT a,a FROM t1 ORDER BY a; + +DROP TABLE t1; + +# +# Bug #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server +# + +eval CREATE TABLE t1 (id1 INT, id2 INT) engine=$engine; +eval CREATE TABLE t2 (id2 INT, id3 INT, id5 INT) engine=$engine; +eval CREATE TABLE t3 (id3 INT, id4 INT) engine=$engine; +eval CREATE TABLE t4 (id4 INT) engine=$engine; +eval CREATE TABLE t5 (id5 INT, id6 INT) engine=$engine; +eval CREATE TABLE t6 (id6 INT) engine=$engine; + +INSERT INTO t1 VALUES(1,1); +INSERT INTO t2 VALUES(1,1,1); +INSERT INTO t3 VALUES(1,1); +INSERT INTO t4 VALUES(1); +INSERT INTO t5 VALUES(1,1); +INSERT INTO t6 VALUES(1); + +# original bug query +SELECT * FROM +t1 + NATURAL JOIN +(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) + ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); + +# inner join swapped +SELECT * FROM +t1 + NATURAL JOIN +(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2 + ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)); + +# one join less, no ON cond +SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2); + +# wrong error message: 'id2' - ambiguous column +SELECT * FROM +(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6) + ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5)) + NATURAL JOIN +t1; +SELECT * FROM +(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6))) + NATURAL JOIN +t1; + +DROP TABLE t1,t2,t3,t4,t5,t6; + +# +# Bug#22342: No results returned for query using max and group by +# +eval CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) engine=$engine; +INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); +ANALYZE TABLE t1; + +explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; +SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; +SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a; +eval CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)) engine=$engine; +INSERT INTO t2 SELECT a,b,b FROM t1; +ANALYZE TABLE t2; +explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; +SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; + +DROP TABLE t1,t2; + +# +# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements +# + +eval CREATE TABLE t1 (a INT, b INT, INDEX (a,b)) engine=$engine; +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), + (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +ANALYZE TABLE t1; +EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; +FLUSH STATUS; +SELECT max(b), a FROM t1 GROUP BY a; +SHOW STATUS LIKE 'handler_read__e%'; +EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; +FLUSH STATUS; +eval CREATE TABLE t2 engine=$engine SELECT max(b), a FROM t1 GROUP BY a; +SHOW STATUS LIKE 'handler_read__e%'; +FLUSH STATUS; +SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; +SHOW STATUS LIKE 'handler_read__e%'; +FLUSH STATUS; +(SELECT max(b), a FROM t1 GROUP BY a) UNION + (SELECT max(b), a FROM t1 GROUP BY a); +SHOW STATUS LIKE 'handler_read__e%'; +EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION + (SELECT max(b), a FROM t1 GROUP BY a); + +EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x + FROM t1 AS t1_outer; +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS + (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE + (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE + a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING + a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 + ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) + AND t1_outer1.b = t1_outer2.b; +EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x + FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; + +CREATE TABLE t3 LIKE t1; +FLUSH STATUS; +INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; +SHOW STATUS LIKE 'handler_read__e%'; +DELETE FROM t3; +FLUSH STATUS; +INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) + FROM t1 LIMIT 1; +SHOW STATUS LIKE 'handler_read__e%'; +FLUSH STATUS; +DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; +SHOW STATUS LIKE 'handler_read__e%'; +FLUSH STATUS; +--error ER_SUBQUERY_NO_1_ROW +DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x + FROM t1) > 10000; +SHOW STATUS LIKE 'handler_read__e%'; + +DROP TABLE t1,t2,t3; + +# +# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint +# for which loose scan optimization is applied +# + +eval CREATE TABLE t1 (a int, INDEX idx(a)) engine=$engine; +INSERT INTO t1 VALUES + (4), (2), (1), (2), (4), (2), (1), (4), + (4), (2), (1), (2), (2), (4), (1), (4); +ANALYZE TABLE t1; + +EXPLAIN SELECT DISTINCT(a) FROM t1; +SELECT DISTINCT(a) FROM t1; +EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; +SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; + +DROP TABLE t1; + +# +# Bug #32268: Indexed queries give bogus MIN and MAX results +# + +eval CREATE TABLE t1 (a INT, b INT) engine=$engine; +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3); +INSERT INTO t1 SELECT a + 1, b FROM t1; +INSERT INTO t1 SELECT a + 2, b FROM t1; +ANALYZE TABLE t1; + +EXPLAIN +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; + +CREATE INDEX break_it ON t1 (a, b); + +EXPLAIN +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; + +EXPLAIN +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; + +EXPLAIN +SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; +SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; + +DROP TABLE t1; + +# +# Bug#38195: Incorrect handling of aggregate functions when loose index scan is +# used causes server crash. +# +create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM; +insert into t1 (a,b) values +(0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6), + (0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13), +(1,0),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6), + (1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13), +(2,0),(2,1),(2,2),(2,3),(2,4),(2,5),(2,6), + (2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13), +(3,0),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6), + (3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13); +insert into t1 (a,b) select a, max(b)+1 from t1 where a = 0 group by a; +ANALYZE TABLE t1; +select * from t1; +explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a; +drop table t1; + + +# +# Bug #41610: key_infix_len can be overwritten causing some group by queries +# to return no rows +# + +eval CREATE TABLE t1 (a int, b int, c int, d int, + KEY foo (c,d,a,b), KEY bar (c,a,b,d)) engine=$engine; + +INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT a,b,c+1,d FROM t1; +ANALYZE TABLE t1; + +#Should be non-empty +EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4; +SELECT DISTINCT c FROM t1 WHERE d=4; + +DROP TABLE t1; + +--echo # +--echo # Bug #45386: Wrong query result with MIN function in field list, +--echo # WHERE and GROUP BY clause +--echo # + +eval CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=$engine; +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; +INSERT INTO t SELECT * FROM t; +ANALYZE TABLE t; + +--echo # test MIN +--echo #should use range with index for group by +EXPLAIN +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; +--echo #should return 1 row +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; + +--echo # test MAX +--echo #should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; +--echo #should return 1 row +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; + +--echo # test 3 ranges and use the middle one +INSERT INTO t SELECT a, 2 FROM t; + +--echo #should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; +--echo #should return 1 row +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; + +DROP TABLE t; + +--echo # +--echo # Bug #48472: Loose index scan inappropriately chosen for some WHERE +--echo # conditions +--echo # + +eval CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=$engine; +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; +ANALYZE TABLE t; + +SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a; + +DROP TABLE t; + +--echo End of 5.0 tests + +--echo # +--echo # Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in +--echo # server crash +--echo # + +eval CREATE TABLE t (a INT, b INT, INDEX (a,b)) engine=$engine; +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; + +SELECT a, MAX(b) FROM t WHERE b GROUP BY a; + +DROP TABLE t; + +# +# BUG#49902 - SELECT returns incorrect results +# +eval CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b)) engine=$engine; +INSERT INTO t1 VALUES(1,1),(2,1); +ANALYZE TABLE t1; +SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b; +SELECT a FROM t1 WHERE b=1; +DROP TABLE t1; + +--echo # +--echo # Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column +--echo # for NULL +--echo # + +--echo ## Test for NULLs allowed +eval CREATE TABLE t1 ( a INT, KEY (a) ) engine=$engine; +INSERT INTO t1 VALUES (1), (2), (3); +ANALYZE TABLE t1; +--source include/min_null_cond.inc +INSERT INTO t1 VALUES (NULL), (NULL); +ANALYZE TABLE t1; +--source include/min_null_cond.inc +DROP TABLE t1; + +--echo ## Test for NOT NULLs +eval CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY) engine=$engine; +INSERT INTO t1 VALUES (1), (2), (3); +ANALYZE TABLE t1; +--echo # +--echo # NULL-safe operator test disabled for non-NULL indexed columns. +--echo # +--echo # See bugs +--echo # +--echo # - Bug#52173: Reading NULL value from non-NULL index gives +--echo # wrong result in embedded server +--echo # +--echo # - Bug#52174: Sometimes wrong plan when reading a MAX value from +--echo # non-NULL index +--echo # +--let $skip_null_safe_test= 1 +--source include/min_null_cond.inc +DROP TABLE t1; + +--echo # +--echo # Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at +--echo # opt_sum.cc:305 +--echo # +eval CREATE TABLE t1 ( a INT, KEY (a) ) engine=$engine; +INSERT INTO t1 VALUES (1), (2), (3); + +SELECT MIN( a ) AS min_a +FROM t1 +WHERE a > 1 AND a IS NULL +ORDER BY min_a; + +DROP TABLE t1; + + +--echo End of 5.1 tests + + +--echo # +--echo # WL#3220 (Loose index scan for COUNT DISTINCT) +--echo # + +eval CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b)) engine=$engine; +INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1); +INSERT INTO t1 SELECT a, b + 4, 1 FROM t1; +INSERT INTO t1 SELECT a + 1, b, 1 FROM t1; +ANALYZE TABLE t1; +eval CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c)) engine=$engine; +INSERT INTO t2 VALUES (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1), + (1,4,1,1,1,1); +INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2; +INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2; +ANALYZE TABLE t2; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1; +SELECT COUNT(DISTINCT a) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1; +SELECT COUNT(DISTINCT a,b) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1; +SELECT COUNT(DISTINCT b,a) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT b) FROM t1; +SELECT COUNT(DISTINCT b) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; +SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; + +EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; +SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; +SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; + +EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1; +SELECT DISTINCT COUNT(DISTINCT a) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1; +SELECT COUNT(DISTINCT a, b + 0) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; +SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; +SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; + +EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; +SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; + +EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; +SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; + +EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; +SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; + +EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1; +SELECT COUNT(DISTINCT a), 12 FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2; +SELECT COUNT(DISTINCT a, b, c) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; +SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; +SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; +SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; +SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; +SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; + +EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; +SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; + +EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2 + WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; +SELECT COUNT(DISTINCT c, a, b) FROM t2 + WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; + +EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 + GROUP BY b; +SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 + GROUP BY b; + +EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; +SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; + +EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; +SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; + +EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; +SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; + +EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 + WHERE b = 13 AND c = 42 GROUP BY a; +SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 + WHERE b = 13 AND c = 42 GROUP BY a; + +--echo # This query could have been resolved using loose index scan since +--echo # the second part of count(..) is defined by a constant predicate +EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; +SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; + +EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; +SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; + +EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; +SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; + +EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; +SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; + +DROP TABLE t1,t2; + +--echo # end of WL#3220 tests + +--echo # +--echo # Bug#50539: Wrong result when loose index scan is used for an aggregate +--echo # function with distinct +--echo # +eval CREATE TABLE t1 ( + f1 int(11) NOT NULL DEFAULT '0', + f2 char(1) NOT NULL DEFAULT '', + PRIMARY KEY (f1,f2) +) engine=$engine; +insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), +(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D'); +ANALYZE TABLE t1; + +SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; +explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; + +drop table t1; +--echo # End of test#50539. + +--echo # +--echo # Bug#17217128 - BAD INTERACTION BETWEEN MIN/MAX AND +--echo # "HAVING SUM(DISTINCT)": WRONG RESULTS. +--echo # + +eval CREATE TABLE t (a INT, b INT, KEY(a,b)) engine=$engine; +INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5); +ANALYZE TABLE t; +let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB +eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE; +set @@session.optimizer_trace='enabled=on'; +set end_markers_in_json=on; + +ANALYZE TABLE t; + +SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; +EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; +SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK + FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; +EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; +SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK + FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); +EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); +SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK + FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; +EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; +SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK + FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; +EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; +SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK + FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +SET optimizer_trace_max_mem_size=DEFAULT; +SET optimizer_trace=DEFAULT; +SET end_markers_in_json=DEFAULT; + +DROP TABLE t; + +--echo # +--echo # Bug#18109609: LOOSE INDEX SCAN IS NOT USED WHEN IT SHOULD +--echo # + +eval CREATE TABLE t1 ( +id INT AUTO_INCREMENT PRIMARY KEY, +c1 INT, +c2 INT, +KEY(c1,c2)) engine=$engine; + +INSERT INTO t1(c1,c2) VALUES +(1, 1), (1,2), (2,1), (2,2), (3,1), (3,2), (3,3), (4,1), (4,2), (4,3), +(4,4), (4,5), (4,6), (4,7), (4,8), (4,9), (4,10), (4,11), (4,12), (4,13), +(4,14), (4,15), (4,16), (4,17), (4,18), (4,19), (4,20),(5,5); +ANALYZE TABLE t1; + +EXPLAIN SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; +FLUSH STATUS; +SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; +SHOW SESSION STATUS LIKE 'Handler_read%'; + +DROP TABLE t1; + +--echo # End of test for Bug#18109609 diff --git a/storage/rocksdb/mysql-test/rocksdb/include/have_direct_io.inc b/storage/rocksdb/mysql-test/rocksdb/include/have_direct_io.inc new file mode 100644 index 00000000000..49509af5bd2 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/include/have_direct_io.inc @@ -0,0 +1,22 @@ +# Common test pattern for options that control direct i/o +# +# Required input: +# $io_option - name and assignment to enable on server command line + +--perl +use Cwd 'abs_path'; + +open(FILE, ">", "$ENV{MYSQL_TMP_DIR}/data_in_shm.inc") or die; +my $real_path= abs_path($ENV{'MYSQLTEST_VARDIR'}); +my $in_shm= index($real_path, "/dev/shm") != -1; +print FILE "let \$DATA_IN_SHM= $in_shm;\n"; +close FILE; +EOF + +--source $MYSQL_TMP_DIR/data_in_shm.inc +--remove_file $MYSQL_TMP_DIR/data_in_shm.inc + +if ($DATA_IN_SHM) +{ + --skip DATADIR is in /dev/shm, possibly due to --mem +} diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter5.result b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter5.result index 279d3040755..daf4f5e30ba 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter5.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter5.result @@ -78,7 +78,7 @@ set global rocksdb_force_flush_memtable_now=1; explain select * from t5 order by id1 desc,id2 desc, id3 desc, id4 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t5 index NULL PRIMARY 122 NULL 1 NULL +1 SIMPLE t5 index NULL PRIMARY 122 NULL 1 select * from t5 order by id1 desc,id2 desc, id3 desc, id4 desc limit 1; id1 id2 id3 id4 id5 value value2 1000 2000 2000 10000 10000 1000 aaabbbccc diff --git a/storage/rocksdb/mysql-test/rocksdb/r/check_flags.result b/storage/rocksdb/mysql-test/rocksdb/r/check_flags.result index 32369c12136..12c5bc4f85c 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/check_flags.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/check_flags.result @@ -1,45 +1,64 @@ set debug_sync='RESET'; set global rocksdb_debug_ttl_read_filter_ts = -10; +connect conn1, localhost, root,,; +connection default; CREATE TABLE t1 (id INT, value INT, KEY (id), KEY (value)) ENGINE=ROCKSDB; CREATE TABLE t2 (id INT, value INT) ENGINE=ROCKSDB; CREATE TABLE t3 (id INT, kp1 INT, PRIMARY KEY (id), KEY(kp1)) ENGINE=ROCKSDB COMMENT='ttl_duration=1'; INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); INSERT INTO t2 SELECT * FROM t1; INSERT INTO t3 SELECT * FROM t1; +connection conn1; set debug_sync='rocksdb.check_flags_rmi SIGNAL parked WAIT_FOR go'; SELECT value FROM t1 WHERE value = 3; +connection default; set debug_sync='now WAIT_FOR parked'; KILL QUERY $conn1_id; set debug_sync='now SIGNAL go'; +connection conn1; ERROR 70100: Query execution was interrupted set debug_sync='RESET'; +connection conn1; set debug_sync='rocksdb.check_flags_rmi_scan SIGNAL parked WAIT_FOR go'; SELECT DISTINCT(id) FROM t1 WHERE value = 5 AND id IN (1, 3, 5); +connection default; set debug_sync='now WAIT_FOR parked'; KILL QUERY $conn1_id; set debug_sync='now SIGNAL go'; +connection conn1; ERROR 70100: Query execution was interrupted set debug_sync='RESET'; +connection conn1; set debug_sync='rocksdb.check_flags_inwd SIGNAL parked WAIT_FOR go'; SELECT value FROM t1 WHERE value > 3; +connection default; set debug_sync='now WAIT_FOR parked'; KILL QUERY $conn1_id; set debug_sync='now SIGNAL go'; +connection conn1; ERROR 70100: Query execution was interrupted set debug_sync='RESET'; +connection conn1; set debug_sync='rocksdb.check_flags_rnwd SIGNAL parked WAIT_FOR go'; SELECT id FROM t2; +connection default; set debug_sync='now WAIT_FOR parked'; KILL QUERY $conn1_id; set debug_sync='now SIGNAL go'; +connection conn1; ERROR 70100: Query execution was interrupted set debug_sync='RESET'; +connection conn1; set debug_sync='rocksdb.check_flags_ser SIGNAL parked WAIT_FOR go'; SELECT kp1 FROM t3 ORDER BY kp1; +connection default; set debug_sync='now WAIT_FOR parked'; KILL QUERY $conn1_id; set debug_sync='now SIGNAL go'; +connection conn1; ERROR 70100: Query execution was interrupted +connection default; +disconnect conn1; set debug_sync='RESET'; set global rocksdb_debug_ttl_read_filter_ts = DEFAULT; DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/cons_snapshot_read_committed.result b/storage/rocksdb/mysql-test/rocksdb/r/cons_snapshot_read_committed.result index 2b6448d1bf0..637354e013e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/cons_snapshot_read_committed.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/cons_snapshot_read_committed.result @@ -5,7 +5,7 @@ connection con1; CREATE TABLE t1 (a INT, pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=ROCKSDB; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION WITH CONSISTENT SNAPSHOT; -ERROR: 50048 +ERROR: 4062 connection con2; select * from information_schema.rocksdb_dbstats where stat_type='DB_NUM_SNAPSHOTS'; STAT_TYPE VALUE @@ -18,7 +18,7 @@ STAT_TYPE VALUE DB_NUM_SNAPSHOTS 0 connection con1; START TRANSACTION WITH CONSISTENT SNAPSHOT; -ERROR: 50048 +ERROR: 4062 connection con2; INSERT INTO t1 (a) VALUES (1); connection con1; @@ -69,7 +69,7 @@ id value value2 5 5 5 6 6 6 START TRANSACTION WITH CONSISTENT SNAPSHOT; -ERROR: 50048 +ERROR: 4062 connection con2; INSERT INTO r1 values (7,7,7); connection con1; @@ -107,12 +107,12 @@ id value value2 7 7 7 8 8 8 START TRANSACTION WITH CONSISTENT SNAPSHOT; -ERROR: 50048 +ERROR: 4062 connection con2; INSERT INTO r1 values (9,9,9); connection con1; START TRANSACTION WITH CONSISTENT SNAPSHOT; -ERROR: 50048 +ERROR: 4062 connection con2; INSERT INTO r1 values (10,10,10); connection con1; @@ -129,7 +129,7 @@ id value value2 9 9 9 10 10 10 START TRANSACTION WITH CONSISTENT SNAPSHOT; -ERROR: 50048 +ERROR: 4062 INSERT INTO r1 values (11,11,11); ERROR: 0 SELECT * FROM r1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/deadlock_stats.result b/storage/rocksdb/mysql-test/rocksdb/r/deadlock_stats.result index 92dc6b00482..79cb6bb0f61 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/deadlock_stats.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/deadlock_stats.result @@ -1,5 +1,4 @@ -set @prior_lock_wait_timeout = @@rocksdb_lock_wait_timeout; set @prior_deadlock_detect = @@rocksdb_deadlock_detect;; -set global rocksdb_deadlock_detect = on; set global rocksdb_lock_wait_timeout = 100000;; +set @prior_lock_wait_timeout = @@rocksdb_lock_wait_timeout; set @prior_deadlock_detect = @@rocksdb_deadlock_detect; set global rocksdb_deadlock_detect = on; set global rocksdb_lock_wait_timeout = 100000;; connect con1,localhost,root,,; connect con2,localhost,root,,; connection default; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ha_extra_keyread.result b/storage/rocksdb/mysql-test/rocksdb/r/ha_extra_keyread.result index c481dd6a953..93c8a464577 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/ha_extra_keyread.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/ha_extra_keyread.result @@ -2,9 +2,9 @@ CREATE TABLE t1 (a INT, b CHAR(8), KEY ab(a, b)) ENGINE=rocksdb DEFAULT CHARSET INSERT INTO t1 (a,b) VALUES (76,'bar'); INSERT INTO t1 (a,b) VALUES (35,'foo'); INSERT INTO t1 (a,b) VALUES (77,'baz'); -SET debug="+d,dbug.rocksdb.HA_EXTRA_KEYREAD"; +SET debug_dbug="+d,dbug.rocksdb.HA_EXTRA_KEYREAD"; SELECT b FROM t1 FORCE INDEX(ab) WHERE a=35; b foo -SET debug="-d,dbug.rocksdb.HA_EXTRA_KEYREAD"; +SET debug_dbug="-d,dbug.rocksdb.HA_EXTRA_KEYREAD"; DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/insert_with_keys.result b/storage/rocksdb/mysql-test/rocksdb/r/insert_with_keys.result index 26f481e73c6..6d4139caefa 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/insert_with_keys.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/insert_with_keys.result @@ -43,6 +43,8 @@ a b 5 e 6 f INSERT IGNORE INTO t1 (a,b) VALUES (1,'a'),(12345,'z'); +Warnings: +Warning 1062 Duplicate entry '1' for key 'a' INSERT INTO t1 (a,b) VALUES (3,'a'),(4,'d') ON DUPLICATE KEY UPDATE a = a+10; SELECT a,b FROM t1; a b @@ -79,6 +81,8 @@ a b 5 e 6 f INSERT IGNORE INTO t1 (a,b) VALUES (1,'a'),(12345,'z'); +Warnings: +Warning 1062 Duplicate entry '1-a' for key 'a' INSERT INTO t1 (a,b) VALUES (1,'a'),(12345,'z') ON DUPLICATE KEY UPDATE a = a+VALUES(a); SELECT a,b FROM t1; a b diff --git a/storage/rocksdb/mysql-test/rocksdb/r/issue896.result b/storage/rocksdb/mysql-test/rocksdb/r/issue896.result index 6d09361522c..917c95733f7 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/issue896.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/issue896.result @@ -7,11 +7,11 @@ PRIMARY KEY (`a`,`b`), KEY `d` (`d`) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ttl_duration=1000;ttl_col=u'; INSERT INTO t1 VALUES (100, 'aaabbb', UNIX_TIMESTAMP(), 200); -EXPLAIN SELECT COUNT(*) FROM t1; +EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(d); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL d 9 NULL # Using index +1 SIMPLE t1 index NULL d 11 NULL # Using index # segfault here without the fix -SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t1 FORCE INDEX(d); COUNT(*) 1 DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_concurrent_delete.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_concurrent_delete.result index 188c1bdcad0..9106e79f80c 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_concurrent_delete.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_concurrent_delete.result @@ -1,10 +1,11 @@ -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; connect con, localhost, root,,; connection default; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY COMMENT "", a INT); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --PK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; @@ -21,30 +22,41 @@ pk a --PK middle row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 3; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) --PK end row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, index a(a)); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --SK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='rocksdb_concurrent_delete_sk SIGNAL parked WAIT_FOR go'; SELECT a FROM t1 FORCE INDEX(a) FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 1; SET debug_sync='now SIGNAL go'; +connection con; a 2 3 @@ -53,30 +65,41 @@ a --SK middle row delete SET debug_sync='rocksdb_concurrent_delete_sk SIGNAL parked WAIT_FOR go'; SELECT a FROM t1 FORCE INDEX(a) FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 3; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) --SK end row delete SET debug_sync='rocksdb_concurrent_delete_sk SIGNAL parked WAIT_FOR go'; SELECT a FROM t1 FORCE INDEX(a) FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY COMMENT "", a INT); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --PK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; pk a 4 4 3 3 @@ -89,27 +112,37 @@ connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 3; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) --PK end row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 1; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY COMMENT "rev:cf2", a INT); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --PK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 1; SET debug_sync='now SIGNAL go'; +connection con; pk a 2 2 3 3 @@ -118,30 +151,41 @@ pk a --PK middle row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 3; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) --PK end row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY COMMENT "rev:cf2", a INT); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --PK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; pk a 4 4 3 3 @@ -150,30 +194,41 @@ pk a --PK middle row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 3; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) --PK end row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 1; SET debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; set debug_sync='RESET'; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; create table t1 (id1 int, id2 int, value int, primary key (id1, id2), index sk (id1, value)) engine=rocksdb; insert into t1 values (1, 1, 1),(1, 2, 1),(1, 3, 1),(1, 4, 1),(1, 5, 1),(2, 2, 2); --First row delete with PRIMARY +connection con; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (PRIMARY) set value=100 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=1; set debug_sync='now SIGNAL go'; +connection con; select * from t1 where id1=1; id1 id2 value 1 2 100 @@ -184,9 +239,11 @@ id1 id2 value SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (PRIMARY) set value=200 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=3; set debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) select * from t1 where id1=1; id1 id2 value @@ -197,27 +254,36 @@ id1 id2 value SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (PRIMARY) set value=300 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=5; set debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) select * from t1 where id1=1; id1 id2 value 1 2 100 1 4 100 +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; set debug_sync='RESET'; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; create table t1 (id1 int, id2 int, value int, primary key (id1, id2), index sk (id1, value)) engine=rocksdb; insert into t1 values (1, 1, 1),(1, 2, 1),(1, 3, 1),(1, 4, 1),(1, 5, 1),(2, 2, 2); --First row delete with sk +connection con; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (sk) set value=100 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=1; set debug_sync='now SIGNAL go'; +connection con; select * from t1 where id1=1; id1 id2 value 1 2 100 @@ -228,9 +294,11 @@ id1 id2 value SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (sk) set value=200 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=3; set debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) select * from t1 where id1=1; id1 id2 value @@ -241,27 +309,36 @@ id1 id2 value SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (sk) set value=300 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=5; set debug_sync='now SIGNAL go'; +connection con; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) select * from t1 where id1=1; id1 id2 value 1 2 100 1 4 100 +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY COMMENT "", a INT); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --PK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 1; SET debug_sync='now SIGNAL go'; +connection con; pk a 2 2 3 3 @@ -270,9 +347,11 @@ pk a --PK middle row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 3; SET debug_sync='now SIGNAL go'; +connection con; pk a 2 2 4 4 @@ -280,25 +359,34 @@ pk a --PK end row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; pk a 2 2 4 4 +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, index a(a)); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --SK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='rocksdb_concurrent_delete_sk SIGNAL parked WAIT_FOR go'; SELECT a FROM t1 FORCE INDEX(a) FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 1; SET debug_sync='now SIGNAL go'; +connection con; a 2 3 @@ -307,9 +395,11 @@ a --SK middle row delete SET debug_sync='rocksdb_concurrent_delete_sk SIGNAL parked WAIT_FOR go'; SELECT a FROM t1 FORCE INDEX(a) FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 3; SET debug_sync='now SIGNAL go'; +connection con; a 2 4 @@ -317,25 +407,34 @@ a --SK end row delete SET debug_sync='rocksdb_concurrent_delete_sk SIGNAL parked WAIT_FOR go'; SELECT a FROM t1 FORCE INDEX(a) FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; a 2 4 +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY COMMENT "", a INT); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --PK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; pk a 4 4 3 3 @@ -344,9 +443,11 @@ pk a --PK middle row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 3; SET debug_sync='now SIGNAL go'; +connection con; pk a 4 4 2 2 @@ -354,19 +455,26 @@ pk a --PK end row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 1; SET debug_sync='now SIGNAL go'; +connection con; pk a 4 4 2 2 +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY COMMENT "rev:cf2", a INT); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --PK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; @@ -383,9 +491,11 @@ pk a --PK middle row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 3; SET debug_sync='now SIGNAL go'; +connection con; pk a 2 2 4 4 @@ -393,26 +503,34 @@ pk a --PK end row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk ASC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; pk a 2 2 4 4 +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +connect con, localhost, root,,; connection default; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='RESET'; CREATE TABLE t1 (pk INT PRIMARY KEY COMMENT "rev:cf2", a INT); INSERT INTO t1 VALUES(1,1), (2,2), (3,3), (4,4), (5,5); --PK first row delete +connection con; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 5; SET debug_sync='now SIGNAL go'; +connection con; pk a 4 4 3 3 @@ -433,25 +551,34 @@ pk a --PK end row delete SET debug_sync='rocksdb_concurrent_delete SIGNAL parked WAIT_FOR go'; SELECT * FROM t1 order by t1.pk DESC FOR UPDATE; +connection default; SET debug_sync='now WAIT_FOR parked'; DELETE FROM t1 WHERE pk = 1; SET debug_sync='now SIGNAL go'; +connection con; pk a 4 4 2 2 +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; set debug_sync='RESET'; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; create table t1 (id1 int, id2 int, value int, primary key (id1, id2), index sk (id1, value)) engine=rocksdb; insert into t1 values (1, 1, 1),(1, 2, 1),(1, 3, 1),(1, 4, 1),(1, 5, 1),(2, 2, 2); --First row delete with PRIMARY +connection con; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (PRIMARY) set value=100 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=1; set debug_sync='now SIGNAL go'; +connection con; select * from t1 where id1=1; id1 id2 value 1 2 100 @@ -462,9 +589,11 @@ id1 id2 value SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (PRIMARY) set value=200 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=3; set debug_sync='now SIGNAL go'; +connection con; select * from t1 where id1=1; id1 id2 value 1 2 200 @@ -474,26 +603,35 @@ id1 id2 value SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (PRIMARY) set value=300 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=5; set debug_sync='now SIGNAL go'; +connection con; select * from t1 where id1=1; id1 id2 value 1 2 300 1 4 300 +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; +connect con, localhost, root,,; +connection default; set debug_sync='RESET'; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; create table t1 (id1 int, id2 int, value int, primary key (id1, id2), index sk (id1, value)) engine=rocksdb; insert into t1 values (1, 1, 1),(1, 2, 1),(1, 3, 1),(1, 4, 1),(1, 5, 1),(2, 2, 2); --First row delete with sk +connection con; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (sk) set value=100 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=1; set debug_sync='now SIGNAL go'; +connection con; select * from t1 where id1=1; id1 id2 value 1 2 100 @@ -504,9 +642,11 @@ id1 id2 value SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (sk) set value=200 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=3; set debug_sync='now SIGNAL go'; +connection con; select * from t1 where id1=1; id1 id2 value 1 2 200 @@ -516,12 +656,16 @@ id1 id2 value SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; set debug_sync='rocksdb.get_row_by_rowid SIGNAL parked WAIT_FOR go'; update t1 force index (sk) set value=300 where id1=1; +connection default; set debug_sync='now WAIT_FOR parked'; delete from t1 where id1=1 and id2=5; set debug_sync='now SIGNAL go'; +connection con; select * from t1 where id1=1; id1 id2 value 1 2 300 1 4 300 +connection default; +disconnect con; set debug_sync='RESET'; drop table t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_timeout_rollback.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_timeout_rollback.result index 7b32d354691..adf05d06aac 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_timeout_rollback.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_timeout_rollback.result @@ -6,25 +6,31 @@ rocksdb_rollback_on_timeout ON create table t1 (a int unsigned not null primary key) engine = rocksdb; insert into t1 values (1); commit; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con2; begin work; insert into t1 values (5); insert into t1 values (6); update t1 set a = a + 1 where a = 1; +connection con1; begin work; insert into t1 values (7); insert into t1 values (8); update t1 set a = a + 1 where a = 1; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; a 1 commit; +connection con2; select * from t1; a 2 5 6 commit; +connection default; select * from t1; a 2 @@ -34,15 +40,17 @@ SET @@global.rocksdb_rollback_on_timeout = 0; show variables like 'rocksdb_rollback_on_timeout'; Variable_name Value rocksdb_rollback_on_timeout OFF +connection con2; begin work; insert into t1 values (9); insert into t1 values (10); update t1 set a = a + 1 where a = 2; +connection con1; begin work; insert into t1 values (11); insert into t1 values (12); update t1 set a = a + 1 where a = 2; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t1.PRIMARY +ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; a 2 @@ -51,6 +59,7 @@ a 11 12 commit; +connection con2; select * from t1; a 3 @@ -59,6 +68,7 @@ a 9 10 commit; +connection default; select * from t1; a 3 @@ -70,3 +80,5 @@ a 12 SET @@global.rocksdb_rollback_on_timeout = DEFAULT; drop table t1; +disconnect con1; +disconnect con2; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/ttl_rows_examined.result b/storage/rocksdb/mysql-test/rocksdb/r/ttl_rows_examined.result index cb93ecfe88b..b0304af8bef 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/ttl_rows_examined.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/ttl_rows_examined.result @@ -25,10 +25,10 @@ connection conn2; set debug_sync='now WAIT_FOR parked'; affected rows: 0 SHOW PROCESSLIST; -Id User Host db Command Time State Info Rows examined Rows sent Tid Srv_Id -### ### ### ### Query ### debug sync point: rocksdb.ttl_rows_examined SELECT * FROM t_re 1 ### ### ### -### ### ### ### Query ### init SHOW PROCESSLIST 0 ### ### ### -### ### ### ### Sleep ### NULL 0 ### ### ### +Id User Host db Command Time State Info Progress +### ### ### ### Query ### debug sync point: rocksdb.ttl_rows_examined SELECT * FROM t_re 0.000 +### ### ### ### Query ### init SHOW PROCESSLIST 0.000 +### ### ### ### Sleep ### NULL 0.000 affected rows: 3 set debug_sync='now SIGNAL go'; affected rows: 0 diff --git a/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter3.test b/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter3.test index d2eb5bf72a9..4469d549fe7 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter3.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter3.test @@ -35,11 +35,12 @@ select case when variable_value-@c > 0 then 'true' else 'false' end from informa # BF len 20 select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked'; -set @tmp_force_index_for_range=@@optimizer_force_index_for_range; -set optimizer_force_index_for_range=on; +# MariaDB: no support for optimizer_force_index_for_range: +#set @tmp_force_index_for_range=@@optimizer_force_index_for_range; +#set optimizer_force_index_for_range=on; select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type2`) where id1 = 100 and link_type = 1 and time >= 0 and time <= 9223372036854775807 order by time desc; select case when variable_value-@c > 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked'; -set global optimizer_force_index_for_range=@tmp_force_index_for_range; +#set global optimizer_force_index_for_range=@tmp_force_index_for_range; # BF len 13 select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked'; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/bulk_load_errors.test b/storage/rocksdb/mysql-test/rocksdb/t/bulk_load_errors.test index c2ae7630384..0409784811f 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/bulk_load_errors.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/bulk_load_errors.test @@ -59,6 +59,8 @@ let $ID = `SELECT connection_id()`; SELECT * FROM t1; +--disable_parsing +# MariaDB: no support for $RPC_PROTOCOL if (`SELECT $RPC_PROTOCOL > 0`) { # for --rpc_protocol mode wait for the background detached session to # go away @@ -74,6 +76,9 @@ if (`SELECT $RPC_PROTOCOL = 0`) { # returns to earlier levels --source include/wait_until_count_sessions.inc } +--enable_parsing +# MariaDB: +--source include/wait_until_count_sessions.inc # Note: in MariaDB, session count will be decremented *before* # myrocks::rocksdb_close_connection is called. This causes a race condition: diff --git a/storage/rocksdb/mysql-test/rocksdb/t/disabled.def b/storage/rocksdb/mysql-test/rocksdb/t/disabled.def index 72cf650ca3e..8f6eef0f703 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/disabled.def +++ b/storage/rocksdb/mysql-test/rocksdb/t/disabled.def @@ -25,6 +25,15 @@ create_no_primary_key_table: MariaDB doesn't have --block_create_no_primary_key explicit_snapshot: MariaDB doesn't support Shared/Explicit snapshots percona_nonflushing_analyze_debug : Requires Percona Server's Non-flushing ANALYZE feature com_rpc_tx : Requires connection attributes and detached sessions +mysqlbinlog_blind_replace: requires @@enable_blind_replace support +optimize_myrocks_replace_into_base: requires @@enable_blind_replace support +optimize_myrocks_replace_into_lock: requires @@enable_blind_replace support +rocksdb.skip_core_dump_on_error: requires @@binlog_error_action support +bypass_select_basic_bloom : Query bypass is not supported +bypass_select_basic : Query bypass is not supported + +rocksdb_read_free_rpl : Read-Free replication is not supported +rocksdb_read_free_rpl_stress : Read-Free replication is not supported ## ## Tests that do not fit MariaDB's test environment. Upstream seems to test @@ -60,7 +69,6 @@ ddl_high_priority: Needs fractional @@lock_wait_timeout deadlock_tracking : Needs SHOW ENGINE ROCKSDB TRANSACTION STATUS bytes_written: Needs I_S.TABLE_STATISTICS.IO_WRITE_BYTES trx_info_rpl : MariaRocks: @@rpl_skip_tx_api doesn't work, yet. -rpl_read_free: MDEV-10976 lock_wait_timeout_stats: MDEV-13404 rpl_row_triggers : Requires read-free slave. diff --git a/storage/rocksdb/mysql-test/rocksdb/t/ha_extra_keyread.test b/storage/rocksdb/mysql-test/rocksdb/t/ha_extra_keyread.test index 3b7ae699d4c..0d0fad2e5fa 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/ha_extra_keyread.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/ha_extra_keyread.test @@ -6,10 +6,10 @@ INSERT INTO t1 (a,b) VALUES (76,'bar'); INSERT INTO t1 (a,b) VALUES (35,'foo'); INSERT INTO t1 (a,b) VALUES (77,'baz'); -SET debug="+d,dbug.rocksdb.HA_EXTRA_KEYREAD"; +SET debug_dbug="+d,dbug.rocksdb.HA_EXTRA_KEYREAD"; SELECT b FROM t1 FORCE INDEX(ab) WHERE a=35; -SET debug="-d,dbug.rocksdb.HA_EXTRA_KEYREAD"; +SET debug_dbug="-d,dbug.rocksdb.HA_EXTRA_KEYREAD"; DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/issue896.test b/storage/rocksdb/mysql-test/rocksdb/t/issue896.test index d5d0a70cdf4..ba57fb99832 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/issue896.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/issue896.test @@ -11,7 +11,7 @@ KEY `d` (`d`) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ttl_duration=1000;ttl_col=u'; INSERT INTO t1 VALUES (100, 'aaabbb', UNIX_TIMESTAMP(), 200); --replace_column 9 # -EXPLAIN SELECT COUNT(*) FROM t1; +EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(d); --echo # segfault here without the fix -SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t1 FORCE INDEX(d); DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/truncate_partition.test b/storage/rocksdb/mysql-test/rocksdb/t/truncate_partition.test index 3b587cce9b8..f9a89517e2a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/truncate_partition.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/truncate_partition.test @@ -1,4 +1,5 @@ --source include/have_rocksdb.inc +--source include/have_partition.inc # # TRUNCATE PARTITION |