From 0ee34b1ca25aa757f373857513d51d58fd7aea80 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 3 Apr 2007 14:32:16 -0700 Subject: Fixed bug #27532: wrong results with ORDER/GROUP BY queries containing IN/BETWEEN predicates in sorting expressions. Wrong results may occur when the select list contains an expression with IN/BETWEEN predicate that differs from a sorting expression by an additional NOT only. Added the method Item_func_opt_neg::eq to compare correctly expressions containing [NOT] IN/BETWEEN. The eq method inherited from the Item_func returns TRUE when comparing 'a IN (1,2)' with 'a NOT IN (1,2)' that is not, of course, correct. mysql-test/r/order_by.result: Added a test case for bug #27532. mysql-test/t/order_by.test: Added a test case for bug #27532. sql/item_cmpfunc.cc: Fixed bug #27532. Added the method Item_func_opt_neg::eq to compare correctly expressions containing [NOT] IN/BETWEEN. The eq method inherited from the Item_func returns TRUE when comparing 'a IN (1,2)' with 'a NOT IN (1,2)' that is not, of course, correct. sql/item_cmpfunc.h: Added the method Item_func_opt_neg::eq to compare correctly expressions containing [NOT] IN/BETWEEN. The eq method inherited from the Item_func returns TRUE when comparing 'a IN (1,2)' with 'a NOT IN (1,2)' that is not, of course, correct. --- mysql-test/t/order_by.test | 29 +++++++++++++++++++++++++++++ 1 file changed, 29 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index d781bd6c6ba..af5811fb4a2 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -617,6 +617,35 @@ SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; --error 1052 SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; +DROP TABLE t1; + +# +# Bug #27532: ORDER/GROUP BY expressions with IN/BETWEEN and NOT IN/BETWEEN +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (3), (2), (4), (1); + +SELECT a, IF(a IN (2,3), a, a+10) FROM t1 + ORDER BY IF(a IN (2,3), a, a+10); +SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1 + ORDER BY IF(a NOT IN (2,3), a, a+10); +SELECT a, IF(a IN (2,3), a, a+10) FROM t1 + ORDER BY IF(a NOT IN (2,3), a, a+10); + +SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 + ORDER BY IF(a BETWEEN 2 AND 3, a, a+10); +SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1 + ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); +SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 + ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); + +SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 + FROM t1 GROUP BY x1, x2; +SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 + FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, ''); + + DROP TABLE t1; # End of 4.1 tests -- cgit v1.2.1