Details
Major Description
CREATE TABLE t (a int, b char(3), KEY (a)); |
INSERT INTO t VALUES (2,'foo'),(2,'bar'),(3,'baz'),(3,'qux'); |
|
|
SELECT DISTINCT a, b FROM t ORDER BY a FETCH FIRST 1 ROWS WITH TIES; |
|
|
# Cleanup
|
DROP TABLE t; |
|
10.6 0a67daad |
SELECT DISTINCT a, b FROM t ORDER BY a FETCH FIRST 1 ROWS WITH TIES; |
a b
|
2 foo
|
2 bar
|
3 baz
|
3 qux
|
Reproducible with at least InnoDB, MyISAM, Aria.
The plan for the query is
EXPLAIN EXTENDED SELECT DISTINCT a, b FROM t ORDER BY a FETCH FIRST 1 ROWS WITH TIES; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t index NULL a 5 NULL 1 100.00 Using temporary |
Warnings:
|
Note 1003 select distinct `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` order by `test`.`t`.`a` fetch first 1 rows with ties |
The expected result is
a b
|
2 bar
|
2 foo
|
It can be achieved by disabling the index:
SELECT DISTINCT a, b FROM t IGNORE INDEX (a) ORDER BY a FETCH FIRST 1 ROWS WITH TIES; |
a b
|
2 bar
|
2 foo
|
|
|
EXPLAIN EXTENDED SELECT DISTINCT a, b FROM t IGNORE INDEX (a) ORDER BY a FETCH FIRST 1 ROWS WITH TIES; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort |
Warnings:
|
Note 1003 select distinct `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` IGNORE INDEX (`a`) order by `test`.`t`.`a` fetch first 1 rows with ties |