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

erroneous implicit "fiels is not null" in left join

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 5.5.28, 5.3.11
    • 5.5.28a
    • None
    • 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.

      Attachments

        Activity

          People

            serg Sergei Golubchik
            serg Sergei Golubchik
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.