Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.5, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
MariaDB Docker (Bionic) on Ubuntu Linux 18.04
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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).
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.
A fix for this bug was pushed into 5.5.
It has to be merged upstream.
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