diff options
| author | unknown <timour/timka@lamia.home> | 2006-08-23 16:46:57 +0300 |
|---|---|---|
| committer | unknown <timour/timka@lamia.home> | 2006-08-23 16:46:57 +0300 |
| commit | 2baf2fdf13ac46f6679de735f05aa6a290e95cc7 (patch) | |
| tree | 3a5b0e759c19fd8a93216b95b35115b1efa789c6 /mysql-test | |
| parent | bfdbb780c26aae2705cf0d7e3048b5c0e40f59e1 (diff) | |
| download | mariadb-git-2baf2fdf13ac46f6679de735f05aa6a290e95cc7.tar.gz | |
Bug #21456: SELECT DISTINCT(x) produces incorrect results when using order by
GROUP BY/DISTINCT pruning optimization must be done before ORDER BY
optimization because ORDER BY may be removed when GROUP BY/DISTINCT
sorts as a side effect, e.g. in
SELECT DISTINCT <non-key-col>,<pk> FROM t1
ORDER BY <non-key-col> DISTINCT
must be removed before ORDER BY as if done the other way around
it will remove both.
mysql-test/r/distinct.result:
Test for BUG#21456.
mysql-test/t/distinct.test:
Test for BUG#21456.
sql/sql_select.cc:
Bug #21456: SELECT DISTINCT(x) produces incorrect results when using order by
GROUP BY/DISTINCT pruning optimization must be done before ORDER BY
optimization because ORDER BY may be removed when GROUP BY/DISTINCT
sorts as a side effect.
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/distinct.result | 11 | ||||
| -rw-r--r-- | mysql-test/t/distinct.test | 11 |
2 files changed, 22 insertions, 0 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index c6c614a5646..6cdf4063291 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -555,3 +555,14 @@ EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 DROP TABLE t1,t2; +CREATE TABLE t1 (a int primary key, b int); +INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2); +explain SELECT DISTINCT a, b FROM t1 ORDER BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort +SELECT DISTINCT a, b FROM t1 ORDER BY b; +a b +1 1 +3 2 +2 3 +DROP TABLE t1; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 8ca6f350b8d..2a87427a2b6 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -378,4 +378,15 @@ EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d; DROP TABLE t1,t2; +# +# Bug 21456: SELECT DISTINCT(x) produces incorrect results when using order by +# +CREATE TABLE t1 (a int primary key, b int); + +INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2); + +explain SELECT DISTINCT a, b FROM t1 ORDER BY b; +SELECT DISTINCT a, b FROM t1 ORDER BY b; +DROP TABLE t1; + # End of 4.1 tests |