maria-discuss team mailing list archive

[Vassilis Virvilis <vasvir@xxxxxxxxxxxxxxxxx>]

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)