Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-42103

JOIN_PREFIX binds the wrong table when aliases differ only by case

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
    • 10.11, 11.4, 11.8, 12.3, 13.1
    • Optimizer
    • None
    • Linux x86_64 with lower_case_table_names=0

    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

          Activity

            People

              psergei Sergei Petrunia
              Wren Qin
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.