diff options
| -rw-r--r-- | mysql-test/r/order_by.result | 59 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 30 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 20 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 1 |
4 files changed, 109 insertions, 1 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 67cf3b8dd36..918a6b2e3d1 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -906,6 +906,65 @@ 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; create table t1 (a int not null, b int not null, c int not null); insert t1 values (1,1,1),(1,1,2),(1,2,1); select a, b from t1 group by a, b order by sum(c); diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 4597276e3bc..6ad62ee8127 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -617,7 +617,6 @@ UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol; UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol; DROP TABLE bug25126; - # # Bug #25427: crash when order by expression contains a name # that cannot be resolved unambiguously @@ -633,6 +632,35 @@ 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 create table t1 (a int not null, b int not null, c int not null); insert t1 values (1,1,1),(1,1,2),(1,2,1); select a, b from t1 group by a, b order by sum(c); diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 8943b9d3586..ac1adc235c3 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1079,6 +1079,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() { use_decimal_comparison= (row->element_index(0)->result_type() == DECIMAL_RESULT) || diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index f6c6f612c5b..0c4a62aaa24 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -561,6 +561,7 @@ public: negated= !negated; return this; } + bool eq(const Item *item, bool binary_cmp) const; bool subst_argument_checker(byte **arg) { return TRUE; } }; |