[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 Created: 2018-04-30  Updated: 2018-05-06  Resolved: 2018-05-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: 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.



 Comments   
Comment by Varun Gupta (Inactive) [ 2018-04-30 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-April/012473.html

Comment by Sergei Petrunia [ 2018-05-04 ]

Ok to push

Generated at Thu Feb 08 08:26:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.