Hi,

I am new here,

Not sure if this is the appropriate place. If not, any pointers are much appreciated.

Not sure if this is a bug that I should report it. Any input on that matter will be much appreciated also.

Platform: Debian unstable/SID, MariaDB: 10.5.12-MariaDB-1 SSD disks joined in a LVM setup.

I was investigating a bug where GROUP BY is not working in a huge table of mine when I noticed the following discrepancy.

I managed to create a trivial reproducer with a 10 rows table.

If I don't specify LIMIT the plan goes to filesort. If I specify LIMIT <= 9 the plan goes to utilize the index If I specify LIMIT >= 10 (table rows) the plan foes to filesort.

Is this behavior expected? Do you think I should report it?

Thanks in advance.

Vassilis Virvilis

# make sure you have no table named t that holds your precious data #DROP TABLE IF EXISTS t;

CREATE TABLE t (id INT, val INT); INSERT INTO t SELECT seq, FLOOR( 1 + RAND() *60 ) FROM seq_1_to_10; ALTER TABLE t ADD INDEX(id); EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id; SELECT COUNT(*) FROM t; EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id LIMIT 9; EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id LIMIT 10; EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id LIMIT 11;

Output:

MariaDB [MEDLINE]> EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id; 1 +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 2 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 3 +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 4 | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 10 | Using filesort | 5 +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.034 sec)

MariaDB [MEDLINE]> SELECT COUNT(*) FROM t; 1 +----------+ 2 | COUNT(*) | 3 +----------+ 4 | 10 | 5 +----------+ 1 row in set (0.001 sec)

MariaDB [MEDLINE]> EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id LIMIT 9; 1 +------+-------------+-------+-------+---------------+------+---------+------+------+-------+ 2 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 3 +------+-------------+-------+-------+---------------+------+---------+------+------+-------+ 4 | 1 | SIMPLE | t | index | NULL | id | 5 | NULL | 9 | | 5 +------+-------------+-------+-------+---------------+------+---------+------+------+-------+ 1 row in set (0.001 sec)

MariaDB [MEDLINE]> EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id LIMIT 10; 1 +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 2 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 3 +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 4 | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 10 | Using filesort | 5 +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.000 sec)

MariaDB [MEDLINE]> EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id LIMIT 11; 1 +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 2 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 3 +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 4 | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 10 | Using filesort | 5 +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.000 sec)