summaryrefslogtreecommitdiff
path: root/mysql-test/t/group_min_max.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/group_min_max.test')
-rw-r--r--mysql-test/t/group_min_max.test1613
1 files changed, 0 insertions, 1613 deletions
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
deleted file mode 100644
index b0bc42d7f8c..00000000000
--- a/mysql-test/t/group_min_max.test
+++ /dev/null
@@ -1,1613 +0,0 @@
-#
-# 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
-
-create table t1 (
- a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' '
-);
-
-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
-
-create table t2 (
- a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' '
-);
-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
-
-create table t3 (
- a1 char(1), a2 char(1), b char(1), c char(4) not null, d char(3), dummy char(1) default ' '
-);
-
-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,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
-explain select a1,a2,b, 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') group by a1,a2;
-explain select a1,a2, max(c) from t1 where (b = 'b') 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,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
-explain select a1,a2,b, 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') group by a1,a2;
-explain select a1,a2, max(c) from t2 where (b = 'b') 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,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') 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,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1;
-select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2;
-select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2;
-select a1,a2, max(c) from t1 where (b = 'b') 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,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1;
-select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2;
-select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2;
-select a1,a2, max(c) from t2 where (b = 'b') 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,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') 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,max(c) from t2 where (a2 = 'a') 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;
-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,max(c) from t2 where (a2 = 'a') 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;
-
-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;
-
-select a1,a2,b,min(c),max(c) from t1
-where exists ( select * from t2 where t2.c > 'b1' )
-group by a1,a2,b;
-
-# correlated subselect that doesn't reference the min/max argument
-explain select a1,a2,b,c,min(c), max(c) from t1
-where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
-group by a1,a2,b;
-
-select a1,a2,b,c,min(c), max(c) from t1
-where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
-group by a1,a2,b;
-
-SET @save_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='semijoin_with_cache=off';
-explain select a1,a2,b,c,min(c), max(c) from t1
-where exists ( select * from t2
- where t2.c in (select c from t3 where t3.c > t1.b) and
- t2.c > 'b1' )
-group by a1,a2,b;
-
-select a1,a2,b,c,min(c), max(c) from t1
-where exists ( select * from t2
- where t2.c in (select c from t3 where t3.c > t1.b) and
- t2.c > 'b1' )
-group by a1,a2,b;
-SET optimizer_switch=@save_optimizer_switch;
-
-# correlated subselect that references the min/max argument
-explain select a1,a2,b,c,min(c), max(c) from t1
-where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
-group by a1,a2,b;
-
-select a1,a2,b,c,min(c), max(c) from t1
-where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
-group by a1,a2,b;
-
-SET @save_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='semijoin_with_cache=off';
-explain select a1,a2,b,c,min(c), max(c) from t1
-where exists ( select * from t2
- where t2.c in (select c from t3 where t3.c > t1.c) and
- t2.c > 'b1' )
-group by a1,a2,b;
-
-select a1,a2,b,c,min(c), max(c) from t1
-where exists ( select * from t2
- where t2.c in (select c from t3 where t3.c > t1.c) and
- t2.c > 'b1' )
-group by a1,a2,b;
-SET optimizer_switch=@save_optimizer_switch;
-
-
-# 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 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 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 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 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');
-
---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');
-
-# 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,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');
-
-# 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;
-
---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;
-
-# 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,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;
-
-
-#
-# 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 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;
-
-#
-# MDEV-4120 UNIQUE indexes should not be considered for loose index scan
-#
-
-create table t4 as select distinct a1, a2, b, c from t1;
-alter table t4 add unique index idxt4 (a1, a2, b, c);
-
---echo # This is "superceded" by MDEV-7118, and Loose Index Scan is again an option:
-explain
-select a1, a2, b, min(c) from t4 group by a1, a2, b;
-select a1, a2, b, min(c) from t4 group by a1, a2, b;
-
-drop table t4;
-
-#
-# 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
-#
-create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
-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
-#
-
-CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
-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
-#
-
-CREATE TABLE t1 (a varchar(64) NOT NULL default '', KEY(a));
-
-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
-#
-
-CREATE TABLE t1 (id1 INT, id2 INT);
-CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
-CREATE TABLE t3 (id3 INT, id4 INT);
-CREATE TABLE t4 (id4 INT);
-CREATE TABLE t5 (id5 INT, id6 INT);
-CREATE TABLE t6 (id6 INT);
-
-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
-#
-CREATE TABLE t1 (a int, b int, KEY (a,b), KEY b (b));
-INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
-
-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;
-CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
-INSERT INTO t2 SELECT a,b,b FROM t1;
-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
-#
-
-CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
-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);
-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;
-CREATE TABLE t2 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
-#
-
-CREATE TABLE t1 (a int, INDEX idx(a));
-INSERT INTO t1 VALUES
- (4), (2), (1), (2), (4), (2), (1), (4),
- (4), (2), (1), (2), (2), (4), (1), (4);
-
-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
-#
-
-CREATE TABLE t1 (a INT, b INT);
-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;
-
-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, 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;
-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
-#
-
-CREATE TABLE t1 (a int, b int, c int, d int,
- KEY foo (c,d,a,b), KEY bar (c,a,b,d));
-
-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;
-
-#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 #
-
-CREATE TABLE t (a INT, b INT, INDEX (a,b));
-INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
-INSERT INTO t SELECT * FROM t;
-INSERT INTO t SELECT * FROM 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 #
-
-CREATE TABLE t (a INT, b INT, INDEX (a,b));
-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 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 #
-
-CREATE TABLE t (a INT, b INT, INDEX (a,b));
-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
-#
-CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b));
-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
-CREATE TABLE t1 ( a INT, KEY (a) );
-INSERT INTO t1 VALUES (1), (2), (3);
---source include/min_null_cond.inc
-INSERT INTO t1 VALUES (NULL), (NULL);
---source include/min_null_cond.inc
-DROP TABLE t1;
-
---echo ## Test for NOT NULLs
-CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY);
-INSERT INTO t1 VALUES (1), (2), (3);
---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 #
-CREATE TABLE t1 ( a INT, KEY (a) );
-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;
-
-#
-# MDEV-729 lp:998028 - Server crashes on normal shutdown in closefrm after executing a query from MyISAM table
-#
-create table t1 (a int, b varchar(1), key(b,a)) engine=myisam;
-insert t1 values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g'),(8,'h'),(null,'i');
-select min(a), b from t1 where a=7 or b='z' group by b;
-flush tables;
-drop table t1;
-
---echo #
---echo # LP BUG#888456 Wrong result with DISTINCT , ANY , subquery_cache=off , NOT NULL
---echo #
-
-CREATE TABLE t1 ( a int NOT NULL) ;
-INSERT INTO t1 VALUES (28),(29),(9);
-
-CREATE TABLE t2 ( a int, KEY (a)) ;
-INSERT INTO t2 VALUES (1),(1),(1),(4),(4),(5),(5),(8),(8),(9);
-
-explain select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1;
-select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1;
-
-drop table t1, t2;
-
---echo #
---echo # LP BUG#900375 Wrong result with derived_merge=ON, DISTINCT or GROUP BY, EXISTS
---echo #
-
-CREATE TABLE t1 ( a INT, b INT, KEY (b) );
-INSERT INTO t1 VALUES
-(100,10),(101,11),(102,12),(103,13),(104,14),
-(105,15),(106,16),(107,17),(108,18),(109,19);
-
-EXPLAIN
-SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
-WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
-SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1
-WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
-
-EXPLAIN
-SELECT alias1.* FROM t1, t1 AS alias1
-WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
-SELECT alias1.* FROM t1, t1 AS alias1
-WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
-
-drop table t1;
-
---echo End of 5.1 tests
-
---echo #
---echo # MDEV-765 lp:825075 - Wrong result with GROUP BY + multipart key + MIN/MAX loose scan
---echo #
-
-CREATE TABLE t1 (a varchar(1), b varchar(1), KEY (b,a));
-INSERT INTO t1 VALUES
-('0',NULL),('9',NULL),('8','c'),('4','d'),('7','d'),(NULL,'f'),
-('7','f'),('8','g'),(NULL,'j');
-
-explain
-SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b;
-SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b;
-
-explain
-SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b;
-SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b;
-
-explain
-SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b;
-SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b;
-
-explain
-SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b;
-SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b;
-
-explain
-SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b;
-SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b;
-
-explain
-SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b;
-SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b;
-
-drop table t1;
-
---echo #
---echo # MDEV-4140 Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery
---echo #
-
-CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
-INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
-INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
-CREATE TABLE t2 (c int) ;
-INSERT INTO t2 VALUES (0),(1);
-
-EXPLAIN
-SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
-SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
-EXPLAIN
-SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
-SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
-# this test is for 5.5 to ensure that the subquery is expensive
-EXPLAIN
-SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
-SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
-
-drop table t1, t2;
-
---echo End of 5.3 tests
-
---echo #
---echo # WL#3220 (Loose index scan for COUNT DISTINCT)
---echo #
-
-CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b));
-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;
-CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c));
-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;
-
-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;
-
-# This query could have been resolved using loose index scan since 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 #
-CREATE TABLE t1 (
- f1 int(11) NOT NULL DEFAULT '0',
- f2 char(1) NOT NULL DEFAULT '',
- KEY (f1,f2)
-) ;
-insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'),
-(3, 'A'), (3, 'B'), (3, 'C'), (3, 'D');
-
-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 #
-
-CREATE TABLE t (a INT, b INT, KEY(a,b));
-INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5);
-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 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 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 SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
-EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t;
-
-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;
-DROP TABLE t;
-
---echo #
---echo # MDEV-4219 A simple select query returns random data (upstream bug#68473)
---echo #
-
---disable_warnings
-drop table if exists faulty;
---enable_warnings
-
-# MySQL's test case
-
-CREATE TABLE faulty (
-a int(11) unsigned NOT NULL AUTO_INCREMENT,
-b int(11) unsigned NOT NULL,
-c datetime NOT NULL,
-PRIMARY KEY (a),
-UNIQUE KEY b_and_c (b,c)
-);
-
-INSERT INTO faulty (b, c) VALUES
-(1801, '2013-02-15 09:00:00'),
-(1802, '2013-02-28 09:00:00'),
-(1802, '2013-03-01 09:00:00'),
-(5, '1990-02-15 09:00:00'),
-(5, '2013-02-15 09:00:00'),
-(5, '2009-02-15 17:00:00');
-
-EXPLAIN
-SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
-SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
-
-drop table faulty;
-
-# MariaDB test case
-
-CREATE TABLE t1 (a INT, b INT);
-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;
-
-CREATE INDEX break_it ON t1 (a, b);
-
-EXPLAIN
-SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
-SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
-
-drop table t1;
-
---echo #
---echo # Start of 10.0 tests
---echo #
-
---echo #
---echo # MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
---echo #
-CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
-INSERT INTO t1 VALUES (1,'2001-01-01');
-INSERT INTO t1 VALUES (1,'2001-01-02');
-INSERT INTO t1 VALUES (1,'2001-01-03');
-INSERT INTO t1 VALUES (1,' 2001-01-04');
-INSERT INTO t1 VALUES (2,'2001-01-01');
-INSERT INTO t1 VALUES (2,'2001-01-02');
-INSERT INTO t1 VALUES (2,'2001-01-03');
-INSERT INTO t1 VALUES (2,' 2001-01-04');
-INSERT INTO t1 VALUES (3,'2001-01-01');
-INSERT INTO t1 VALUES (3,'2001-01-02');
-INSERT INTO t1 VALUES (3,'2001-01-03');
-INSERT INTO t1 VALUES (3,' 2001-01-04');
-INSERT INTO t1 VALUES (4,'2001-01-01');
-INSERT INTO t1 VALUES (4,'2001-01-02');
-INSERT INTO t1 VALUES (4,'2001-01-03');
-INSERT INTO t1 VALUES (4,' 2001-01-04');
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
-ALTER TABLE t1 ADD KEY(id,a);
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
-DROP TABLE t1;
-
-
---echo #
---echo # End of 10.0 tests
---echo #
-
-
---echo #
---echo # Start of 10.1 tests
---echo #
-
---echo #
---echo # MDEV-6990 GROUP_MIN_MAX optimization is not applied in some cases when it could
---echo #
-CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) ENGINE=MyISAM;
-INSERT INTO t1 VALUES (1,'2001-01-01');
-INSERT INTO t1 VALUES (1,'2001-01-02');
-INSERT INTO t1 VALUES (1,'2001-01-03');
-INSERT INTO t1 VALUES (1,'2001-01-04');
-INSERT INTO t1 VALUES (2,'2001-01-01');
-INSERT INTO t1 VALUES (2,'2001-01-02');
-INSERT INTO t1 VALUES (2,'2001-01-03');
-INSERT INTO t1 VALUES (2,'2001-01-04');
-INSERT INTO t1 VALUES (3,'2001-01-01');
-INSERT INTO t1 VALUES (3,'2001-01-02');
-INSERT INTO t1 VALUES (3,'2001-01-03');
-INSERT INTO t1 VALUES (3,'2001-01-04');
-INSERT INTO t1 VALUES (4,'2001-01-01');
-INSERT INTO t1 VALUES (4,'2001-01-02');
-INSERT INTO t1 VALUES (4,'2001-01-03');
-INSERT INTO t1 VALUES (4,'2001-01-04');
-EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;
-EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id;
-EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104.0 GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
-DROP TABLE t1;
-
---echo #
---echo # MDEV-8229 GROUP_MIN_MAX is erroneously applied for BETWEEN in some cases
---echo #
-SET NAMES latin1;
-CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
-INSERT INTO t1 VALUES (1,'2001-01-01');
-INSERT INTO t1 VALUES (1,'2001-01-02');
-INSERT INTO t1 VALUES (1,'2001-01-03');
-INSERT INTO t1 VALUES (1,' 2001-01-04');
-INSERT INTO t1 VALUES (2,'2001-01-01');
-INSERT INTO t1 VALUES (2,'2001-01-02');
-INSERT INTO t1 VALUES (2,'2001-01-03');
-INSERT INTO t1 VALUES (2,' 2001-01-04');
-INSERT INTO t1 VALUES (3,'2001-01-01');
-INSERT INTO t1 VALUES (3,'2001-01-02');
-INSERT INTO t1 VALUES (3,'2001-01-03');
-INSERT INTO t1 VALUES (3,' 2001-01-04');
-INSERT INTO t1 VALUES (4,'2001-01-01');
-INSERT INTO t1 VALUES (4,'2001-01-02');
-INSERT INTO t1 VALUES (4,'2001-01-03');
-INSERT INTO t1 VALUES (4,' 2001-01-04');
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN ' 2001-01-04' AND '2001-01-05' GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND '2001-01-05' GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND '2001-01-05' GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND DATE'2001-01-05' GROUP BY id;
-ALTER TABLE t1 ADD KEY(id,a);
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN ' 2001-01-04' AND '2001-01-05' GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND '2001-01-05' GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND '2001-01-05' GROUP BY id;
-SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND DATE'2001-01-05' GROUP BY id;
-EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN ' 2001-01-04' AND '2001-01-05' GROUP BY id;
-EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND '2001-01-05' GROUP BY id;
-EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
-EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND '2001-01-05' GROUP BY id;
-EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN '2001-01-04' AND DATE'2001-01-05' GROUP BY id;
-DROP TABLE t1;
-
---echo #
---echo # MIN() optimization didn't work correctly with BETWEEN when using too
---echo # long strings.
---echo #
-
-create table t1 (a varchar(10), key (a)) engine=myisam;
-insert into t1 values("bar"),("Cafe");
-explain select min(a) from t1 where a between "a" and "Cafe2";
-explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeeeeeee";
-explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2";
-drop table t1;
-
---echo #
---echo # End of 10.1 tests
---echo #
View Lorry Controller Status