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

Equality propagation not working properly for non-merged derived tables

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.2
    • N/A
    • Optimizer
    • None

    Description

      Equality propagation is not working to a full extent when one of the members of the multi-equality is a field of derived table

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table t21 (a int, b int, c int);
      insert into t21 select a,a,a from ten;
       
      create table t22 (a int, b int, c int);
      insert into t22 select a,a,a from ten;
      

      explain format=json
      select * 
      from 
        (select a,b, COUNT(*) as CNT from t22 group by a,b) TBL, 
        t21 
      where 
        TBL.a=CNT AND CNT=t21.c AND CNT<3;
      

      I would expect that in the above query:

      • the optimizer constructs multiple-equal(TBL.a, CNT, t21.c)
      • Then, CNT<3 is used to create t21.c < 3

      However, this EXPLAIN plan shows that the latter didn't occur:

      *************************** 1. row ***************************
      EXPLAIN: {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t21",
            "access_type": "ALL",
            "rows": 10,
            "filtered": 100,
            "attached_condition": "t21.c is not null"
          },
          "table": {
            "table_name": "<derived2>",
            "access_type": "ref",
            "possible_keys": ["key0"],
            "key": "key0",
            "key_length": "8",
            "used_key_parts": ["CNT"],
            "ref": ["test2.t21.c"],
            "rows": 2,
            "filtered": 100,
            "attached_condition": "TBL.a = TBL.CNT and TBL.CNT = t21.c and TBL.CNT < 3",
            "materialized": {
              "query_block": {
                "select_id": 2,
                "having_condition": "t22.a = CNT and CNT < 3",
                "filesort": {
                  "sort_key": "t22.a, t22.b",
                  "temporary_table": {
                    "table": {
                      "table_name": "t22",
                      "access_type": "ALL",
                      "rows": 10,
                      "filtered": 100
                    }
                  }
                }
              }
            }
          }
        }
      }
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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