Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.5
Description
Take the DBT-3 dataset, make sure you have these indexes:
CREATE INDEX i_o_orderdate ON orders (o_orderdate); |
CREATE INDEX i_o_custkey ON orders (o_custkey); |
Run these two queries. The queries are the same, except for the ORDER BY list.
However this difference causes different join orders to be picked:
MariaDB [dbt3]> explain select * from orders,customer where o_custkey=c_custkey order by o_orderdate limit 10;
|
+------+-------------+----------+--------+---------------+---------------+---------+-----------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+--------+---------------+---------------+---------+-----------------------+------+-------------+
|
| 1 | SIMPLE | orders | index | i_o_custkey | i_o_orderdate | 4 | NULL | 10 | Using where |
|
| 1 | SIMPLE | customer | eq_ref | PRIMARY | PRIMARY | 4 | dbt3.orders.o_custkey | 1 | |
|
+------+-------------+----------+--------+---------------+---------------+---------+-----------------------+------+-------------+
|
MariaDB [dbt3]> explain select * from orders,customer where o_custkey=c_custkey order by c_acctbal limit 10;
|
+------+-------------+----------+-------+---------------+-------------+---------+-------------------------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+-------------+---------+-------------------------+------+-------+
|
| 1 | SIMPLE | customer | index | PRIMARY | c_acctbal | 9 | NULL | 1 | |
|
| 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | dbt3.customer.c_custkey | 15 | |
|
+------+-------------+----------+-------+---------------+-------------+---------+-------------------------+------+-------+
|
However, looking at the optimizer trace, one can see that the join optimization parts are identical:
MariaDB [dbt3]> select * from information_schema.optimizer_trace\G
|
*************************** 1. row ***************************
|
- QUERY: explain select * from orders,customer where o_custkey=c_custkey order by o_orderdate limit 10
|
+ QUERY: explain select * from orders,customer where o_custkey=c_custkey order by c_acctbal limit 10
|
TRACE: {
|
"steps": [
|
{
|
@@ -18,7 +17,7 @@
|
"select_id": 1,
|
"steps": [
|
{
|
- "expanded_query": ...
|
+ "expanded_query": ...
|
}
|
]
|
}
|
@@ -177,15 +176,15 @@
|
},
|
{
|
"attaching_conditions_to_tables": {
|
- "original_condition": "customer.c_custkey = orders.o_custkey",
|
+ "original_condition": "orders.o_custkey = customer.c_custkey",
|
"attached_conditions_computation": [],
|
"attached_conditions_summary": [
|
{
|
- "table": "orders",
|
- "attached": "orders.o_custkey is not null"
|
+ "table": "customer",
|
+ "attached": null
|
}, |
The first difference is in "attaching_conditions_to_tables" ?