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

LP:419423 - Equality propagation not performed for ON clauses when there is no WHERE clause

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Trivial
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      create table t11 (a int not null);
      insert into t11 values (0),(1),(2),(3);

      create table t12 (pk int not null primary key, b int not null);
      insert into t12 select a,a from t11;

      explain select * from t11 left join t12 on t12.pk=t12.b and t12.b=3;
      -------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      -------------------------------------------------------------+

      1 SIMPLE t11 ALL NULL NULL NULL NULL 4  
      1 SIMPLE t12 ALL NULL NULL NULL NULL 4  

      -------------------------------------------------------------+
      2 rows in set (0.00 sec)

      mysql> show warnings\G

                                                          • 1. row ***************************
                                                            Level: Note
                                                            Code: 1003
                                                            Message: select `j33`.`t11`.`a` AS `a`,`j33`.`t12`.`pk` AS `pk`,`j33`.`t12`.`b` AS `b` from `j33`.`t11` left join `j33`.`t12` on(((`j33`.`t12`.`pk` = `j33`.`t12`.`b`) and (`j33`.`t12`.`b` = 3))) where 1

      mysql> explain extended select * from t11 left join t12 on t12.pk=t12.b and t12.b=3 where t11.a=333;
      --------------------------------------------------------------------------------

      id select_type table type possible_keys key key_len ref rows filtered Extra

      --------------------------------------------------------------------------------

      1 SIMPLE t11 ALL NULL NULL NULL NULL 4 100.00 Using where
      1 SIMPLE t12 const PRIMARY PRIMARY 4 const 1 100.00  

      --------------------------------------------------------------------------------
      2 rows in set, 1 warning (0.00 sec)

      mysql> show warnings\G

                                                          • 1. row ***************************
                                                            Level: Note
                                                            Code: 1003
                                                            Message: select `j33`.`t11`.`a` AS `a`,`j33`.`t12`.`pk` AS `pk`,`j33`.`t12`.`b` AS `b` from `j33`.`t11` left join `j33`.`t12` on(((`j33`.`t12`.`pk` = 3) and (`j33`.`t12`.`b` = 3))) where (`j33`.`t11`.`a` = 333)
                                                            1 row in set (0.00 sec)

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.