Details
Description
With lower_case_table_names=0, a JOIN_PREFIX hint targeting alias A binds the distinct alias a instead. The resulting plan places a first, while an otherwise equivalent control using aliases a and b correctly places the requested alias b first.
Save and run the following SQL on MariaDB 12.3.3:
CREATE DATABASE hint_order; |
USE hint_order; |
CREATE TABLE t1(k INT); |
INSERT INTO t1 VALUES (1),(2); |
SELECT @@lower_case_table_names; |
SELECT 'BEGIN_control'; |
EXPLAIN SELECT /*+ JOIN_PREFIX(b) */ a.k,b.k |
FROM t1 a, t1 b; |
SHOW WARNINGS;
|
SELECT 'END_control'; |
SELECT 'BEGIN_case'; |
EXPLAIN SELECT /*+ JOIN_PREFIX(A) */ a.k,A.k |
FROM t1 a, t1 A; |
SHOW WARNINGS;
|
SELECT 'END_case'; |
DROP DATABASE hint_order; |
SELECT COUNT(*) |
FROM information_schema.schemata |
WHERE schema_name='hint_order'; |
The relevant recorded output was:
@@lower_case_table_names
|
0
|
BEGIN_control
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE b ALL NULL NULL NULL NULL 2
|
1 SIMPLE a ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
|
END_control
|
BEGIN_case
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE a ALL NULL NULL NULL NULL 2
|
1 SIMPLE A ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
|
END_case
|
COUNT(*)
|
0
|
The two table references contain identical data and have no dependency, constant access, or join constraint. In the control, JOIN_PREFIX(b) places b first as requested. Because table aliases are distinct by case when lower_case_table_names=0, JOIN_PREFIX(A) should likewise place A first. Instead, the plan starts with a, showing that the hint was attached to the wrong alias without a warning.
Source inspection indicates that table aliases are compared using the containing query block's case-insensitive collation while binding the hint, which merges a and A for this lookup.
This occurs when JOIN_PREFIX targets table aliases that differ only by letter case while lower_case_table_names=0.
Attachments
Issue Links
- relates to
-
MDEV-33103 LOCK TABLE t1 AS t2 -- alias is not case sensitive with lower-case-table-names=0
-
- Closed
-
-
MDEV-38105 Join order optimizer hints inconsistently resolve table names
-
- Open
-