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

Derived table elimination does not work for multiple occurencies of a field

    XMLWordPrintable

Details

    • Bug
    • Status: In Review (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.10(EOL)
    • 10.11
    • Optimizer
    • None

    Description

      This issue has been found after implementing derived table elimination (MDEV-26278). If a single field appears multiple times on the derived table's SELECT list and the derived table is joined using a field alias then the algorithm fails to eliminate the derived table.

      create table t1 (a int, b int);
      create table t2 (a int, b int, c int);
       
      -- successful elimination of D
      explain select t1.* from t1 left join 
        (select a, b from t2 group by a, b) D 
        on D.a=t1.a and D.b=t1.b;
       
      --  Field 'a' appears as 'a' and 'a2' and D is joined using 'a2'. It is not eliminated though (a2,b) is also unique
      explain select t1.* from t1 left join 
        (select a, b, a as a2 from t2 group by a, b) D 
        on D.a2=t1.a and D.b=t1.b;
       
      
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              oleg.smirnov Oleg Smirnov
              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.