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

Condition pushdown into derived tables on the inner sides of outer joins

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    Description

      Consider an example:

      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
        t21 LEFT JOIN
        (select a,b, COUNT(*) as CNT from t22 group by a,b) TBL
        ON (t21.a=TBL.a AND TBL.a<5);
      

      here, "TBL.a<5" can be pushed down into the subquery, but it is not. EXPLAIN EXTENDED shows:

        "query_block": {
          "select_id": 1,
          "const_condition": "1",
          "table": {
            "table_name": "t21",
            "access_type": "ALL",
            "rows": 10,
            "filtered": 100
          },
          "table": {
            "table_name": "<derived2>",
            "access_type": "ref",
            "possible_keys": ["key0"],
            "key": "key0",
            "key_length": "5",
            "used_key_parts": ["a"],
            "ref": ["test.t21.a"],
            "rows": 2,
            "filtered": 100,
            "attached_condition": "trigcond(trigcond(t21.a < 5 and t21.a is not null))",
            "materialized": {
              "query_block": {
                "select_id": 2,
                "filesort": {
                  "sort_key": "t22.a, t22.b",
                  "temporary_table": {
                    "table": {
                      "table_name": "t22",
                      "access_type": "ALL",
                      "rows": 10,
                      "filtered": 100
                    }
                  }
                }
              }
            }
          }
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.