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

Using optimization Splitting with Group BY we see an unnecessary attached condition t1.pk IS NOT NULL where pk is a PRIMARY KEY

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3(EOL)
    • 10.3.7
    • Optimizer
    • None

    Description

      Here is the test (picked from derived_cond_pushdown.test in the main suite)

      CREATE TABLE t1 (pk1 INT PRIMARY KEY, f INT) ENGINE=Aria;
      INSERT INTO t1 VALUES (1,0),(2,0);
      CREATE TABLE t2 (pk2 INT PRIMARY KEY) ENGINE=Aria;
      INSERT INTO t2 VALUES (1),(2),(3);
      CREATE VIEW v2 AS SELECT pk2, COUNT(*) AS cnt FROM t2 GROUP BY pk2;
      

      Now when i run this analyze command

      MariaDB [test]> ANALYZE FORMAT=JSON SELECT * FROM t1 INNER JOIN v2 ON pk1 = pk2 WHERE f <> 5;
      

      the output is

      | {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 1.1568,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "possible_keys": ["PRIMARY"],
            "r_loops": 1,
            "rows": 2,
            "r_rows": 2,
            "r_total_time_ms": 0.0677,
            "filtered": 100,
            "r_filtered": 100,
            "attached_condition": "t1.f <> 5"
          },
          "table": {
            "table_name": "<derived2>",
            "access_type": "ref",
            "possible_keys": ["key0"],
            "key": "key0",
            "key_length": "4",
            "used_key_parts": ["pk2"],
            "ref": ["test.t1.pk1"],
            "r_loops": 2,
            "rows": 2,
            "r_rows": 1,
            "r_total_time_ms": 0.7043,
            "filtered": 100,
            "r_filtered": 100,
            "materialized": {
              "query_block": {
                "select_id": 2,
                "r_loops": 2,
                "r_total_time_ms": 0.8569,
                "outer_ref_condition": "t1.pk1 is not null",
                "table": {
                  "table_name": "t2",
                  "access_type": "eq_ref",
                  "possible_keys": ["PRIMARY"],
                  "key": "PRIMARY",
                  "key_length": "4",
                  "used_key_parts": ["pk2"],
                  "ref": ["test.t1.pk1"],
                  "r_loops": 2,
                  "rows": 1,
                  "r_rows": 1,
                  "r_total_time_ms": 0.0723,
                  "filtered": 100,
                  "r_filtered": 100,
                  "using_index": true
                }
              }
            }
          }
        }
      } |
      

      So if you look closely at the "outer_ref_condition": "t1.pk1 is not null". But as we see in the create table statement t1.pk1 is a primary key and so it is guaranteed to be NOT NULL so this is a useless condition to have.

      Attachments

        Activity

          People

            varun Varun Gupta (Inactive)
            varun Varun Gupta (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.