maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hello Igor,

While reviewing MDEV-12845, I've found this piece in sql_select.cc:

    while ((tbl= li++))
    {
      /* 
        Do not push conditions from where into materialized inner tables
        of outer joins: this is not valid.
      */
      if (tbl->is_materialized_derived())
      {
        /* 
          Do not push conditions from where into materialized inner tables
          of outer joins: this is not valid.
        */
        if (!tbl->is_inner_table_of_outer_join())

Q1: Is int meaningful to have two identical comments?

Q2: It is not valid push parts of WHERE, but it is valid to push the parts of
ON expression.

An example:

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 confirms it:

  "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
              }
            }
          }
        }
      }
    }

Just in case, the commands to create the dataset were:

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;


BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog