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

Pushdown into materialized views/derived tables doesn't work in the IN subqueries

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2
    • Fix Version/s: 10.2.15
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Consider the query where the derived table is used in the IN subquery.

      SELECT * FROM t1
      WHERE t1.a>1 AND
        (t1.a,t1.b,t1.c) IN
        (
          SELECT d_tab.e,d_tab.f,d_tab.max_g 
          FROM (
            SELECT t2.e, t2.f, MAX(t2.g) AS max_g
            FROM t2
            GROUP BY t2.e
            HAVING max_g>25
          ) as d_tab
          WHERE d_tab.e<5
        )
      ;
      

      There the condition (d_tab.e<5) that is used in the WHERE clause of the IN subquery
      should be pushed down into the WHERE clause of the derived table but the pushdown
      isn't done. Explain in json format for this query:

      | {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "<subquery2>",
            "access_type": "ALL",
            "possible_keys": ["distinct_key"],
            "rows": 12,
            "filtered": 100,
            "materialized": {
              "unique": 1,
              "query_block": {
                "select_id": 2,
                "table": {
                  "table_name": "<derived3>",
                  "access_type": "ALL",
                  "rows": 12,
                  "filtered": 100,
                  "attached_condition": "d_tab.e > 1 and d_tab.e < 5",
                  "materialized": {
                    "query_block": {
                      "select_id": 3,
                      "having_condition": "max_g > 25",
                      "filesort": {
                        "sort_key": "t2.e",
                        "temporary_table": {
                          "table": {
                            "table_name": "t2",
                            "access_type": "ALL",
                            "rows": 12,
                            "filtered": 100
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          },
          "block-nl-join": {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows": 15,
              "filtered": 100
            },
            "buffer_type": "flat",
            "buffer_size": "256Kb",
            "join_type": "BNL",
            "attached_condition": "t1.a = d_tab.e and t1.b = d_tab.f and t1.c = d_tab.max_g"
          }
        }
      } |
      

      Pushdown also isn't down when the derived table is used in the materialized IN subquery that is converted to semijoin:

      SELECT * FROM t1
      WHERE t1.a>1 AND
        (t1.a,t1.b,t1.c) IN
        (
          SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g 
          FROM (
            SELECT t2.e, t2.f, MAX(t2.g) AS max_g
            FROM t2
            GROUP BY t2.e
            HAVING max_g>25
          ) as d_tab
          WHERE d_tab.e<5
          GROUP BY d_tab.e
        )
      ;
      

      Explain in json format for this query:

      | {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows": 15,
            "filtered": 100,
            "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null"
          },
          "table": {
            "table_name": "<subquery2>",
            "access_type": "eq_ref",
            "possible_keys": ["distinct_key"],
            "key": "distinct_key",
            "key_length": "12",
            "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"],
            "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
            "rows": 1,
            "filtered": 100,
            "materialized": {
              "unique": 1,
              "query_block": {
                "select_id": 2,
                "temporary_table": {
                  "table": {
                    "table_name": "<derived3>",
                    "access_type": "ALL",
                    "rows": 12,
                    "filtered": 100,
                    "attached_condition": "d_tab.e < 5",
                    "materialized": {
                      "query_block": {
                        "select_id": 3,
                        "having_condition": "max_g > 25",
                        "filesort": {
                          "sort_key": "t2.e",
                          "temporary_table": {
                            "table": {
                              "table_name": "t2",
                              "access_type": "ALL",
                              "rows": 12,
                              "filtered": 100
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      } |
      

        Attachments

          Activity

            People

            Assignee:
            shagalla Galina Shalygina (Inactive)
            Reporter:
            shagalla Galina Shalygina (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: