Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
None
-
None
-
None
Description
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.