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

erroneous implicit "fiels is not null" in left join

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Not a Bug
        • Affects Version/s: 5.5.28, 5.3.11
        • Fix Version/s: 5.5.28a
        • Component/s: None
        • Labels:
          None

          Description

          a query like

          select t1.id
  from t1
  left join t2 on t1.id = t2.t1_id
  left join t3 on t2.id = t3.t2_id;

          is rewritten by the optimizer into

          select t1.id
  from t1
  left join t2 on (t2.t1_id = t1.id and t1.id is not null)

          the table t3 is correctly eliminated, but the extended condition does not allow the table t2 to be eliminated too.

          insert t1 values (1),(2);
create table t2 (t1_id int primary key, id int, key (id));
insert t2 values (1,1),(2,2);
create table t3 (t2_id int primary key);
insert t3 values (1),(2);
set sql_quote_show_create=0;
explain extended select t1.id from t1 left join t2 on t1.id = t2.t1_id left join t3 on t2.id = t3.t2_id;

          on 5.1 and 5.2 the table t2 is not eliminated either, but the condition, as shown by EXPLAIN EXTENDED does not have the new clause.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    serg Sergei Golubchik
                    Reporter:
                    serg Sergei Golubchik
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    1 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: