Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17956

Full table is read, but most of the results are discarded right after that.

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3
    • Fix Version/s: 10.3, 10.4
    • Component/s: None
    • Labels:

      Description

      based on https://bugs.mysql.com/bug.php?id=79040

      create table t1 (i int);
      insert into t1 select seq from seq_1_to_1000;
      select * from ( select * from t1)dt limit 1;  # select `test`.`t1`.`i` AS `i` from `test`.`t1` limit 1
      select * from ( select * from t1 group by i)dt limit 1; #/* select#1 */ select `dt`.`i` AS `i` from (/* select#2 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by `test`.`t1`.`i`) `dt` limit 1
      

      MariaDB [test]> explain extended select * from (select * from t1 group by i)dt;
      +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
      | id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
      +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
      |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |                                 |
      |    2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using temporary; Using filesort |
      +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
      2 rows in set, 1 warning (0.001 sec)
       
      MariaDB [test]> explain extended select * from (select * from t1)dt;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      1 row in set, 1 warning (0.001 sec)
       
      MariaDB [test]> select * from (select * from t1)dt limit 1;
      +------+
      | i    |
      +------+
      |    1 |
      +------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> show status like '%Handler_read_rnd%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_rnd         | 0     |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 1     |
      +--------------------------+-------+
      3 rows in set (0.002 sec)
       
      MariaDB [test]>  select * from ( select * from t1 group by i)dt limit 1;
      +------+
      | i    |
      +------+
      |    1 |
      +------+
      1 row in set (0.067 sec)
       
      MariaDB [test]> show status like '%Handler_read_rnd%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_rnd         | 1000  |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 2004  |
      +--------------------------+-------+
      

      based on https://bugs.mysql.com/bug.php?id=92994

      create table t1 (i int);
      insert into t1 select seq from seq_1_to_1000;
       
      (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
      explain extended (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
      

      MariaDB [test]> explain extended (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      |    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |       |
      |    2 | UNION       | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      2 rows in set, 1 warning (0.003 sec)
       
      MariaDB [test]> flush status;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [test]> (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
      +------+
      | i    |
      +------+
      |    1 |
      +------+
      1 row in set (0.008 sec)
       
      MariaDB [test]> show status like '%Handler_read_rnd%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_rnd         | 0     |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 200   |
      +--------------------------+-------+
      3 rows in set (0.006 sec)
      
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                alice Alice Sherepa
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: