[#MDEV-13897] SELECT @a := MAX(col) FROM t requires full index scan

MariaDB 10.1.x and 10.2.x do not apply " Select tables optimized away" optimization when MAX(col) value is assigned to a user variable. Consider the following primitive test case:

openxs@ao756:~/dbs/maria10.2$ bin/mysql -uroot test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.2.9-MariaDB Source distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> create table test(a int, key(a));

Query OK, 0 rows affected (0.32 sec)

MariaDB [test]> insert into test values (1), (2), (3);

Query OK, 3 rows affected (0.14 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> explain select max(a) from test;

+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |

+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |

+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> explain select @a := max(a) from test;

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | test  | index | NULL          | a    | 5       | NULL |    3 | Using index |

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

MariaDB [test]> explain select max(a) into @a from test;

+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |

+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |

+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

1 row in set (0.00 sec)

With any large table this leads to huge difference in query execution time, as full index scan is slow.

mysql> create table test(a int, key(a));

Query OK, 0 rows affected (0.70 sec)

mysql> insert into test values (1), (2), (3);

Query OK, 3 rows affected (0.26 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select max(a) from test;

+----+-------------+-------+------+---------------+------+---------+------+-----

-+------------------------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows

 | Extra                        |

+----+-------------+-------+------+---------------+------+---------+------+-----

-+------------------------------+

|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL

 | Select tables optimized away |

+----+-------------+-------+------+---------------+------+---------+------+-----

-+------------------------------+

1 row in set (0.12 sec)

mysql> explain select @id := max(a) from test;

+----+-------------+-------+------+---------------+------+---------+------+-----

-+------------------------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows

 | Extra                        |

+----+-------------+-------+------+---------------+------+---------+------+-----

-+------------------------------+

|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL

 | Select tables optimized away |

+----+-------------+-------+------+---------------+------+---------+------+-----

-+------------------------------+

1 row in set (0.02 sec)

mysql> show create table test\G

*************************** 1. row ***************************

       Table: test

Create Table: CREATE TABLE `test` (

  `a` int(11) DEFAULT NULL,

  KEY `a` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.03 sec)

mysql> select version();

+------------+

| version()  |

+------------+

| 5.6.29-log |

+------------+

1 row in set (0.04 sec)

[MDEV-13897] SELECT @a := MAX(col) FROM t requires full index scan Created: 2017-09-23 Updated: 2020-08-25 Resolved: 2017-09-27
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.0, 10.1, 10.1.24, 10.2.9, 10.2
Fix Version/s:	10.1.28, 10.2.10

[MDEV-13897] SELECT @a := MAX(col) FROM t requires full index scan Created: 2017-09-23 Updated: 2020-08-25 Resolved: 2017-09-27