diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/order_by.result | 30 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 18 |
2 files changed, 48 insertions, 0 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 89c8b6c8c81..50c601b14a8 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1643,4 +1643,34 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 8 NULL 10 Using index; Using temporary; Using filesort 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where DROP TABLE t1, t2; +# +# Bug #707848: WHERE condition with OR + ORDER BY + field substitution +# +CREATE TABLE t1 (a int PRIMARY KEY); +INSERT INTO t1 VALUES +(9), (7), (11), (15), (2), (4), (1), (5), (14), (54), (3), (8); +EXPLAIN EXTENDED +SELECT * FROM t1 r JOIN t1 s ON r.a = s.a +WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0 +ORDER BY 1 LIMIT 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE r index PRIMARY PRIMARY 4 NULL 10 120.00 Using where; Using index +1 SIMPLE s eq_ref PRIMARY PRIMARY 4 test.r.a 1 100.00 Using index +Warnings: +Note 1003 select `test`.`r`.`a` AS `a`,`test`.`s`.`a` AS `a` from `test`.`t1` `r` join `test`.`t1` `s` where ((`test`.`s`.`a` = `test`.`r`.`a`) and ((`test`.`r`.`a` in (2,9)) or ((`test`.`r`.`a` < 100) and (`test`.`r`.`a` <> 0)))) order by 1 limit 10 +SELECT * FROM t1 r JOIN t1 s ON r.a = s.a +WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0 +ORDER BY 1 LIMIT 10; +a a +1 1 +2 2 +3 3 +4 4 +5 5 +7 7 +8 8 +9 9 +11 11 +14 14 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 83aff014c16..e725e107c48 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1497,5 +1497,23 @@ LIMIT 2; DROP TABLE t1, t2; +--echo # +--echo # Bug #707848: WHERE condition with OR + ORDER BY + field substitution +--echo # + +CREATE TABLE t1 (a int PRIMARY KEY); +INSERT INTO t1 VALUES + (9), (7), (11), (15), (2), (4), (1), (5), (14), (54), (3), (8); + +EXPLAIN EXTENDED +SELECT * FROM t1 r JOIN t1 s ON r.a = s.a + WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0 + ORDER BY 1 LIMIT 10; + +SELECT * FROM t1 r JOIN t1 s ON r.a = s.a + WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0 + ORDER BY 1 LIMIT 10; + +DROP TABLE t1; --echo End of 5.1 tests |