diff options
| author | unknown <mhansson/martin@linux-st28.site> | 2007-08-27 17:33:41 +0200 |
|---|---|---|
| committer | unknown <mhansson/martin@linux-st28.site> | 2007-08-27 17:33:41 +0200 |
| commit | 236866468123d5f61713c627ba77aae73c916d28 (patch) | |
| tree | 471fede21d51804a73054250f11c2a6995683515 /mysql-test/r/distinct.result | |
| parent | de619bd17c69453dc115fad746719b0751de87f5 (diff) | |
| download | mariadb-git-236866468123d5f61713c627ba77aae73c916d28.tar.gz | |
Bug #30596 GROUP BY optimization gives wrong result order
The optimization that uses a unique index to remove GROUP BY, did not
ensure that the index was actually used, thus violating the ORDER BY
that is impled by GROUP BY.
Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
a unique index. In case GROUP BY ... ORDER BY null is used, GROUP BY is
simply removed.
BitKeeper/etc/ignore:
Added support-files/mysqld_multi.server tests/bug25714 cscope.in.out cscope.out cscope.po.out to the ignore list
mysql-test/r/distinct.result:
Bug#30596: Changed test case.
Prior to Bug#16458, These queries use temp table and filesort. The
bug was that they used a temp table. However, that patch removed
filesort also, in which case we can no longer gurantee correct ordering.
mysql-test/r/group_by.result:
Bug#30596: Correct result
mysql-test/r/innodb_mysql.result:
Bug#30596: Test case for innodb. Here, as opposed to for MyISAM, row
lookup is done using index whenever the index covers the group list.
mysql-test/t/group_by.test:
Bug#30596: Test case
mysql-test/t/innodb_mysql.test:
Bug#30596: Test case
sql/sql_select.cc:
Bug#30596: The fix, replacing GROUP BY with ORDER BY unless
ORDER BY [NULL|<constant>]
Diffstat (limited to 'mysql-test/r/distinct.result')
| -rw-r--r-- | mysql-test/r/distinct.result | 6 |
1 files changed, 3 insertions, 3 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 8525e0f19e4..8cfe5aa78b5 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -526,10 +526,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 Using index EXPLAIN SELECT a,b FROM t1 GROUP BY a,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, PRIMARY KEY (a,b)); INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); @@ -554,7 +554,7 @@ id select_type table type possible_keys key key_len ref rows Extra CREATE UNIQUE INDEX c_b_unq ON t2 (c,b); 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 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using filesort DROP TABLE t1,t2; create table t1 (id int, dsc varchar(50)); insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three"); |