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.

4 files changed, 109 insertions, 0 deletions

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index f5601ba0e43..2d6a4a922bc 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -879,3 +879,62 @@ ERROR 23000: Column 'val' in order clause is ambiguous
 SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1;
 ERROR 23000: Column 'val' in order clause is ambiguous
 DROP TABLE t1;
+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);
+a	IF(a IN (2,3), a, a+10)
+2	2
+3	3
+1	11
+4	14
+SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1 
+ORDER BY IF(a NOT IN (2,3), a, a+10);
+a	IF(a NOT IN (2,3), a, a+10)
+1	1
+4	4
+2	12
+3	13
+SELECT a, IF(a IN (2,3), a, a+10) FROM t1 
+ORDER BY IF(a NOT IN (2,3), a, a+10);
+a	IF(a IN (2,3), a, a+10)
+1	11
+4	14
+2	2
+3	3
+SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
+ORDER BY IF(a BETWEEN 2 AND 3, a, a+10);
+a	IF(a BETWEEN 2 AND 3, a, a+10)
+2	2
+3	3
+1	11
+4	14
+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);
+a	IF(a NOT BETWEEN 2 AND 3, a, a+10)
+1	1
+4	4
+2	12
+3	13
+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);
+a	IF(a BETWEEN 2 AND 3, a, a+10)
+1	11
+4	14
+2	2
+3	3
+SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
+FROM t1 GROUP BY x1, x2;
+x1	x2
+	3
+	4
+1	
+2	
+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, '');
+x1	x2
+	3
+	4
+1	
+2	
+DROP TABLE t1;
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
@@ -619,4 +619,33 @@ 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
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index ffb60754381..4d54dfc2b39 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -838,6 +838,26 @@ longlong Item_func_strcmp::val_int()
 }
 
 
+bool Item_func_opt_neg::eq(const Item *item, bool binary_cmp) const
+{
+  /* Assume we don't have rtti */
+  if (this == item)
+    return 1;
+  if (item->type() != FUNC_ITEM)
+    return 0;
+  Item_func *item_func=(Item_func*) item;
+  if (arg_count != item_func->arg_count ||
+      functype() != item_func->functype())
+    return 0;
+  if (negated != ((Item_func_opt_neg *) item_func)->negated)
+    return 0;
+  for (uint i=0; i < arg_count ; i++)
+    if (!args[i]->eq(item_func->arguments()[i], binary_cmp))
+      return 0;
+  return 1;
+}
+
+
 void Item_func_interval::fix_length_and_dec()
 {
   if (row->cols() > 8)
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index a13be83e093..132e019b4a3 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -405,6 +405,7 @@ public:
     negated= !negated;
     return this;
   }
+  bool eq(const Item *item, bool binary_cmp) const;
 };