From af7213d5d483afce7256b646cb943a9973be2a53 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
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/r/func_group.result | 47 ++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 47 insertions(+)

(limited to 'mysql-test/r/func_group.result')

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 3608f587572..c07d16cb3f4 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1828,4 +1828,51 @@ NULL
 NULL
 DROP TABLE t1,t2,t3;
 #
+# Bug #884175: MIN/MAX for short varchar = long const  
+#
+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';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT MAX(f1) FROM t1 WHERE f1 = 'abc';
+MAX(f1)
+NULL
+EXPLAIN
+SELECT MAX(f2) FROM t1 WHERE f2 = 'abc';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	f2	f2	4	const	1	Using where; Using index
+SELECT MAX(f2) FROM t1 WHERE f2 = 'abc';
+MAX(f2)
+NULL
+EXPLAIN
+SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc';
+MIN(f1)
+b
+EXPLAIN
+SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	f2	f2	4	NULL	2	Using where; Using index
+SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc';
+MIN(f2)
+b
+EXPLAIN
+SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ;
+MIN(f1)
+b
+EXPLAIN
+SELECT MIN(f2) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	f2	f2	4	NULL	2	Using where; Using index
+SELECT MIN(f2) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ;
+MIN(f2)
+b
+DROP TABLE t1;
 End of 5.2 tests
-- 
cgit v1.2.1