[MDEV-662] LP:884636 - No conversion of outer join into inner join when a view is used Created: 2011-11-01  Updated: 2021-11-16

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.2.14, 5.3.12, 5.5, 10.0, 10.1
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Igor Babaev Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Launchpad, verified

Attachments: XML File LPexportBug884636.xml    

 Description   

For the test case from bug #884184
we have in mariadb-5.2 (and mariadb-5.3):

MariaDB [test]> set optimizer_switch='table_elimination=off';
 
Query OK, 0 rows affected (0.00 sec)
 

MariaDB [test]> EXPLAIN EXTENDED SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
 
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
 
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra                    |
 
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
 
|  1 | SIMPLE      | t3    | system | NULL          | NULL    | NULL    | NULL      |    1 |   100.00 |                          |
 
|  1 | SIMPLE      | t1    | ALL    | NULL          | NULL    | NULL    | NULL      |    1 |   100.00 | Using where              |
 
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.a |    1 |   100.00 | Using where; Using index |
 
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
 
3 rows in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                                                          |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` is not null)))) on(((`test`.`t2`.`a` <> 0) and (`test`.`t1`.`b` = 'g'))) where 1 |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

We can see that the left join of inherited from the view is not converted into an inner join.

With a manual substitution of the view the conversion is performed:

MariaDB [test]> EXPLAIN EXTENDED SELECT t1.a, t1.b FROM t3 LEFT JOIN (t1 LEFT JOIN t2 ON t1.a = t2.a) ON t2.a <> 0 AND t3.a = t1.b;
 
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------+--------------------------+
 
| id | select_type | table | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
 
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------+--------------------------+
 
|  1 | SIMPLE      | t3    | system | NULL          | NULL    | NULL    | NULL |    1 |   100.00 |                          |
 
|  1 | SIMPLE      | t2    | index  | PRIMARY       | PRIMARY | 4       | NULL |    0 |     0.00 | Using where; Using index |
 
|  1 | SIMPLE      | t1    | ALL    | NULL          | NULL    | NULL    | NULL |    1 |   100.00 | Using where              |
 
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------+--------------------------+
 
3 rows in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                 |
 
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t2`.`a` <> 0) and (`test`.`t1`.`b` = 'g') and (`test`.`t1`.`a` = `test`.`t2`.`a`))) where 1 |
 
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-11-01 ]

Launchpad bug id: 884636

Comment by Elena Stepanova [ 2015-12-02 ]

The above-mentioned test case from bug#884184:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 ( a int(11), b varchar(1)) ;
 
INSERT IGNORE INTO t1 VALUES (0,'g');
 
DROP TABLE IF EXISTS t3;
 
CREATE TABLE t3 ( a varchar(1)) ;
 
INSERT IGNORE INTO t3 VALUES ('g');
 
DROP TABLE IF EXISTS t2;
 
CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ;
 
create or replace view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0;

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