diff options
| author | unknown <gkodinov/kgeorge@macbook.gmz> | 2007-01-31 10:18:26 +0200 |
|---|---|---|
| committer | unknown <gkodinov/kgeorge@macbook.gmz> | 2007-01-31 10:18:26 +0200 |
| commit | d01ec5e72704513ce0e13fe5dd924c088757507e (patch) | |
| tree | d89cb0e43cd03774723d33becca1cac5b580ab63 /mysql-test/r/distinct.result | |
| parent | c6d4b94dd452d31f49430321a16690f7bec06d5c (diff) | |
| download | mariadb-git-d01ec5e72704513ce0e13fe5dd924c088757507e.tar.gz | |
Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
The optimizer takes away columns from GROUP BY/DISTINCT if they constitute
all the parts of an unique index.
However if some of the columns can contain NULLs this cannot be done
(because an UNIQUE index can have multiple rows with NULL values).
Fixed by not using UNIQUE indexes with nullable columns to remove
grouping columns from GROUP BY/DISTINCT.
mysql-test/r/distinct.result:
Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
- test case
mysql-test/t/distinct.test:
Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
- test case
sql/sql_select.cc:
Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
- UNIQUE NULL indices don't guarantee GROUP BY/DISTINCT.
Diffstat (limited to 'mysql-test/r/distinct.result')
| -rw-r--r-- | mysql-test/r/distinct.result | 26 |
1 files changed, 25 insertions, 1 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 32151305698..3508a83a810 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -530,7 +530,8 @@ id select_type table type possible_keys key key_len ref rows Extra 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 -CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b)); +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); EXPLAIN SELECT DISTINCT a FROM t2; id select_type table type possible_keys key key_len ref rows Extra @@ -644,3 +645,26 @@ SELECT COUNT(*) FROM COUNT(*) 2 DROP TABLE t1, t2; +CREATE TABLE t1 (a INT, UNIQUE (a)); +INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3); +EXPLAIN SELECT DISTINCT a FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 6 Using index +SELECT DISTINCT a FROM t1; +a +NULL +1 +2 +3 +4 +EXPLAIN SELECT a FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 6 Using index +SELECT a FROM t1 GROUP BY a; +a +NULL +1 +2 +3 +4 +DROP TABLE t1; |