[MDEV-19778] Wrong Result on Left Outer Join with Subquery right on true and WHERE filter afterwards Created: 2019-06-17  Updated: 2019-07-29  Resolved: 2019-06-26

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.4.5, 10.2, 10.3, 10.4
Fix Version/s: 10.2.26, 5.5.65, 10.1.41, 10.3.17, 10.4.7

Type: Bug Priority: Major
Reporter: - Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: optimizer, wrong_result
Environment:

MariaDB Docker (Bionic) on Ubuntu Linux 18.04

Issue Links:
Relates
relates to MDEV-6892 WHERE does not apply Closed

 Description   

Summary: The following query returns an entry ('1'), even if there is no matching columns.

CREATE SCHEMA A ;
 
CREATE TABLE A.B  ( C SMALLINT) ;
 
INSERT INTO A.B VALUES (2);
 
CREATE TABLE A.D  ( E SMALLINT) ;
 
SELECT 1 FROM (A.B LEFT OUTER JOIN (SELECT 2 AS G FROM A.D) I ON TRUE ) WHERE B.C = I.G ;

I expect that an empty result set is returned because no query matches the given Condition. Postgres 11 doesn't return anything, which is correct.

MySQL 8.0.16 is also affected, I'm not sure about other mariadb or mysql versions.

 Comments   
Comment by Alice Sherepa [ 2019-06-17 ]

Thanks! I repeated on 5.5-10.4. Also the same on Mysql 5.7.26, but Mysql 5.6.40 retured the correct result 

MariaDB [test]> create table t1  (c smallint) ;
 
Query OK, 0 rows affected (0.040 sec)
 
 
 
MariaDB [test]> insert into t1 values (2);
 
Query OK, 1 row affected (0.010 sec)
 
 
 
MariaDB [test]> create table t2  (e smallint) ;
 
Query OK, 0 rows affected (0.031 sec)
 
 
 
MariaDB [test]> select 1 from (t1 left join (select 2 as g from t2) dt on true) where t1.c = dt.g ;
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
+---+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> explain extended select 1 from (t1 left join (select 2 as g from t2) dt on true) where t1.c = dt.g ;
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
2 rows in set, 1 warning (0.000 sec)
 
 
 
Note (Code 1003): select 1 AS `1` from `test`.`t1` left join (`test`.`t2`) on(1) where `test`.`t1`.`c` = 2

Comment by Igor Babaev [ 2019-06-20 ]

This is a more generalized test for this bug:

create table t1 (pk int, a int);
 
insert into t1 values (1,7), (2,3), (3,2), (4,3);
 
create table t2 (b int);
 
insert into t2 values (5), (1), (NULL), (3);
 
create table t3 (c int);
 
insert into t3 values (1), (8);
 
 
 
create view v1 as
 
select 3 as d, t2.b from t2;
 
 
 
select * from t1 left join v1 on t1.pk <= 2 where t1.a=v1.d;


MariaDB [test]> select * from t1 left join v1 on t1.pk <= 2 where t1.a=v1.d;
 
 
 
+------+------+---+------+
 
| pk   | a    | d | b    |
 
+------+------+---+------+
 
|    2 |    3 | 3 |    5 |
 
|    2 |    3 | 3 |    1 |
 
|    2 |    3 | 3 | NULL |
 
|    2 |    3 | 3 |    3 |
 
|    4 |    3 | NULL | NULL |
 
+------+------+---+------+

The last row in the result set does not satisfy the WHERE condition t1.a=v1.d (3 != NULL).

Comment by Igor Babaev [ 2019-06-20 ]

This bug is a result of incompleteness of the fix for bug MDEV-6892: the fix did not work for equality conditions involving constant columns from views / derived tables used as inner
tables of outer joins.
Let's consider the query 

select * from t1 left join v1 on t1.pk <= 2 where t1.a=v1.d;

from the above test case.
After the merge of v1 into the embedding select v1.d replaced by a special Item_direct_view_ref object with the field 'null_ref_table' pointing to t2 and referring to constant 3. The function optimize_cond transforms the equality into a multiple equality. Yet the current code removes the Item_direct_view_ref wrapper from the constant object when doing this. After this the result of substitution for v1.d is handled as a regular constant and this is of course incorrect.

Comment by Oleksandr Byelkin [ 2019-06-21 ]

Ok to push

Comment by Igor Babaev [ 2019-06-26 ]

A fix for this bug was pushed into 5.5.
It has to be merged upstream.

Generated at Thu Feb 08 08:54:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.