|
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
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
|
|