group_min_max.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

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;

-- 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 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 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 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