[MDEV-13156] Equality propagation not working properly for non-merged derived tables Created: 2017-06-22  Updated: 2017-06-23  Resolved: 2017-06-23

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: 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
 
              }
 
            }
 
          }
 
        }
 
      }
 
    }
 
  }
 
}



 Comments   
Comment by Sergei Petrunia [ 2017-06-22 ]

(TODO: check if this is just caused by CNT and other columns having different data types)

Comment by Igor Babaev [ 2017-06-23 ]

Most probably multiple-equal(TBL.a, CNT, t21.c) is not built here because the type of CNT differs from the type of the columns. It should be checked in debugger.
Sergey, try a query with MAX/MIN instead of COUNT.

Comment by Sergei Petrunia [ 2017-06-23 ]

Indeed if I change it to MAX:

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

Then equality propagation will work:

{
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "table_name": "t21",
 
      "access_type": "ALL",
 
      "rows": 10,
 
      "filtered": 100,
 
      "attached_condition": "t21.c < 3 and t21.c is not null and t21.c is not null"
 
    },
 
    "table": {
 
      "table_name": "<derived2>",
 
      "access_type": "ref",
 
      "possible_keys": ["key0"],
 
      "key": "key0",
 
      "key_length": "10",
 
      "used_key_parts": ["a", "CNT"],
 
      "ref": ["j33.t21.c", "j33.t21.c"],
 
      "rows": 2,
 
      "filtered": 100,
 
      "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,
 
                "attached_condition": "t22.a < 3 and t22.a = t22.a"
 
              }
 
            }
 
          }
 
        }
 
      }
 
    }
 
  }
 
} |

So this limitation is not really a bug (in a way that everything works like expected)

Comment by Sergei Petrunia [ 2017-06-23 ]

Closing as this limitation is not considered a bug ( that is, a limitation but it's not worth working on)

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