[MDEV-3434] LP:419423 - Equality propagation not performed for ON clauses when there is no WHERE clause Created: 2009-08-26  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Trivial
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug419423.xml    

 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)

 Comments   
Comment by Hakan Küçükyılmaz (Inactive) [ 2010-05-26 ]

Re: Equality propagation not performed for ON clauses when there is no WHERE clause
Sergey,

do you have any update about this bug?

Best,

Hakan

Comment by Sergei Petrunia [ 2010-09-13 ]

Re: Equality propagation not performed for ON clauses when there is no WHERE clause
Fixed by fix for bugs.mysql.com's BUG#49600

Comment by Rasmus Johansson (Inactive) [ 2010-09-13 ]

Launchpad bug id: 419423

Generated at Thu Feb 08 06:48:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.