Unpredictable behaviour of setting optimizer_switch='mrr_sort_keys=off'

[Igor Babaev]

I observe the following strange behavior of the mariadb server built from the current LP 5.3 tree when
running against an instance of DBT3 (factor 10) database for innodb.

MariaDB [test]> use dbt3x10_innodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [dbt3x10_innodb]>
MariaDB [dbt3x10_innodb]> set join_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.00 sec)

MariaDB [dbt3x10_innodb]> set join_buffer_space_limit=1024*1024*32;
Query OK, 0 rows affected (0.00 sec)

MariaDB [dbt3x10_innodb]> set join_cache_level=6;
Query OK, 0 rows affected (0.00 sec)

MariaDB [dbt3x10_innodb]> set optimizer_switch='mrr_sort_keys=off';
Query OK, 0 rows affected (0.00 sec)

MariaDB [dbt3x10_innodb]>
MariaDB [dbt3x10_innodb]> explain
    -> select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+------------------------------------+
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index |
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | i_l_orderkey | 4 | dbt3x10_innodb.orders.o_orderkey | 1 | Using join buffer (flat, BKA join) |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+------------------------------------+
2 rows in set (0.02 sec)

MariaDB [dbt3x10_innodb]> explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+------------------------------------+
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index |
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | i_l_orderkey | 4 | dbt3x10_innodb.orders.o_orderkey | 1 | Using join buffer (flat, BKA join) |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+------------------------------------+
2 rows in set (0.00 sec)

MariaDB [dbt3x10_innodb]> analyze table lineitem;
+-------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| dbt3x10_innodb.lineitem | analyze | status | OK |
+-------------------------+---------+----------+----------+
1 row in set (1.12 sec)

MariaDB [dbt3x10_innodb]> explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+--------------------------+
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index |
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3x10_innodb.orders.o_orderkey | 2 | |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+--------------------------+
2 rows in set (0.01 sec)

I observe this behavior for both debug and non-debug version of the server on SuSE 10.3 32-bit.

Sometimes the last command returns the following result:
MariaDB [dbt3x10_innodb]> explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+--------------------------+
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index |
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3x10_innodb.orders.o_orderkey | 1 | |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------------+--------+------------------------

I did not try to execute this sequence of commands with dbt3 of a smaller factor. Maybe the problem can be
reproduced with them as well.

[Sergey Petrunia (sergefp)]

For better readability of the above: all EXPLAINS are for the same query:

explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';

Attempt#1: 2nd table is lineitem, type=ref, key=i_l_orderkey, Extra="Using join buffer (flat, BKA join)"
Attempt#2: 2nd table is lineitem, type=ref, key=i_l_orderkey, Extra="Using join buffer (flat, BKA join)" (same as #1)

<ANALYZE is run>

Attempt#3: 2nd table is lineitem, type=ref, key=PRIMARY, Extra="", rows=2
Attempt#4: 2nd table is lineitem, type=ref, key=PRIMARY, Extra="", rows=1

[Sergey Petrunia (sergefp)]

I don't see anything wrong here.

Attempt#1 and #2 are OK.

ANALYZE command updates index cardinalities. After that, the optimizer starts to prefer a prefix of PRIMARY key over i_l_orderkey index. This is ok, too (even if it wasn't: this would have no relation to mrr_sort_keys switch/code).

optimizer_switch='mrr_sort_keys=off' setting disables key sorting functionality. DS-MRR's operation over a clustered primary key is defined as
 - sort the keys
 - use sorted keys array to make lookups in key order (note that lookups produce full result records here, there is no separate rnd_pos() step).

that is, if sorting is disabled, then DS-MRR has nothing to do. handler->multi_range_read_info() function will indicate that to BKA code by setting HA_MRR_USE_DEFAULT_IMPL. when BKA code sees this flag, it disables use of join buffering.

I think everything is operating as-designed here.

[Sergey Petrunia (sergefp)]

Had a discussion with Igor, we came to agreement that the observed behaviour is not a bug.