From af7213d5d483afce7256b646cb943a9973be2a53 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 8 Mar 2012 22:33:01 -0800 Subject: Fixed LP bug #884175. If in the where clause of the a query some comparison conditions on the field under a MIN/MAX aggregate function contained constants whose sizes exceeded the size of the field then the query could return a wrong result when the optimizer had chosen to apply the MIN/MAX optimization. With such conditions the MIN/MAX optimization still could be applied, yet it would require a more thorough analysis of the keys built to find the value of MIN/MAX aggregate functions with index look-ups. The current patch just prohibits using the MIN/MAX optimization in this situation. --- mysql-test/t/func_group.test | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) (limited to 'mysql-test/t/func_group.test') diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 3939d53cb31..927c4b084da 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1194,4 +1194,37 @@ SELECT (SELECT MIN(t1.a) FROM t1,t2 WHERE t2.a = t3.b) FROM t3; DROP TABLE t1,t2,t3; --echo # +--echo # Bug #884175: MIN/MAX for short varchar = long const +--echo # + +CREATE TABLE t1 (f1 varchar(1), f2 varchar(1), KEY (f2)); +INSERT INTO t1 VALUES ('b', 'b'), ('a','a'); + +EXPLAIN +SELECT MAX(f1) FROM t1 WHERE f1 = 'abc'; +SELECT MAX(f1) FROM t1 WHERE f1 = 'abc'; + +EXPLAIN +SELECT MAX(f2) FROM t1 WHERE f2 = 'abc'; +SELECT MAX(f2) FROM t1 WHERE f2 = 'abc'; + +EXPLAIN +SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc'; +SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc'; + +EXPLAIN +SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc'; +SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc'; + +EXPLAIN +SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ; +SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ; + +EXPLAIN +SELECT MIN(f2) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ; +SELECT MIN(f2) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ; + +DROP TABLE t1; + + --echo End of 5.2 tests -- cgit v1.2.1