[MDEV-6892] WHERE does not apply Created: 2014-10-18  Updated: 2019-06-22  Resolved: 2015-04-23

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.3.12, 5.5.40, 10.0.12, 10.0.14
Fix Version/s: 5.5.43, 10.0.18

Type: Bug Priority: Critical
Reporter: erkan yanar Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 3
Labels: None
Environment:

Ubunut/LXC

Issue Links:
Duplicate
is duplicated by MDEV-6919 wrong result from subquery - too much... Closed
is duplicated by MDEV-6972 Left joined subquery gives wrong result Closed
Relates
relates to MDEV-7893 table_elimination works wrong with on... Closed
relates to MDEV-19778 Wrong Result on Left Outer Join with ... Closed

 Description   

Thx to Andreas Kretschmer for pointing out.

create table t1 (id int);
 
create table t2 (id int);
 
insert into t1 values(1),(2),(3);
 
insert into t2 values(4),(5),(6)
 
select x.id, message from (select id from t1) x left join
 
(select id, 1 as message from t2) y on x.id=y.id
 
where coalesce(message,0) <> 0;
 
# 5.6.19-0ubuntu0.14.04.1
 
Empty set (0,01 sec)
 
# 10.0.12-MariaDB-1~trusty-wsrep-log
 
+------+---------+
 
| id   | message |
 
+------+---------+
 
|    1 |    NULL |
 
|    2 |    NULL |
 
|    3 |    NULL |
 
+------+---------+



 Comments   
Comment by Elena Stepanova [ 2014-10-18 ]

Thanks for the report.

As a workaround, please try to set optimizer_switch='derived_merge=off'.

Comment by Elena Stepanova [ 2014-10-18 ]

EXPLAIN

 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 
Warnings:
 
Note	1003	select `test`.`t1`.`id` AS `id`,1 AS `message` from `test`.`t1` left join (`test`.`t2`) on((`test`.`t2`.`id` = `test`.`t1`.`id`)) where 1
 
drop table t1, t2;

Also reproducible with merge views instead of subqueries.

Comment by Oleksandr Byelkin [ 2014-12-12 ]

I think here situation is even more interesting:

create table t1 (id int);
 
create table t2 (id int);
 
insert into t1 values(1),(2),(3);
 
insert into t2 values(4),(5),(6);
 
select x.id, message from (select id from t1) x left join
 
(select id, 1 as message from t2) y on x.id=y.id
 
where message is not NULL;
 
drop table t1,t2;

Comment by Oleksandr Byelkin [ 2014-12-12 ]

T@5 : | | | | | | | >optimize_cond

WHERE:(original) 0x7f6ea801e1b8 (`message` is not null)

WHERE:(after equal_items) 0x7f6ea801e1b8 (`message` is not null)

WHERE:(after const change) 0x7f6ea801e1b8 (`message` is not null)

WHERE:(after remove) (nil)
T@5 : | | | | | | | <optimize_cond

Comment by Oleksandr Byelkin [ 2014-12-12 ]

in the very beginning of optimize:
T@5 : | | | | | | >JOIN::optimize
T@5 : | | | | | | | >my_malloc
T@5 : | | | | | | | | my: size: 24 my_flags: 16
T@5 : | | | | | | | | mutex: sf_mutex (0x1ceea80) locking
T@5 : | | | | | | | | mutex: sf_mutex (0x1ceea80) locked
T@5 : | | | | | | | | mutex: sf_mutex (0x1ceea80) unlocking
T@5 : | | | | | | | | exit: ptr: 0x7f0690083f80
T@5 : | | | | | | | <my_malloc

WHERE:(VERY VERY original) 0x7f069001e0a8 (1 is not null)

it looks like derived table substituted but ability to be NULL is lost somewhere...

Comment by Oleksandr Byelkin [ 2014-12-12 ]

It merged view, and substituted 1 (constant). Wrapper for a view field should take into account that view row is NULL but it does not somehow.

Comment by Oleksandr Byelkin [ 2014-12-14 ]

This patch fixes the problem but it is too strict (should be limited by left joins only)

=== modified file 'sql/item.cc'
 
--- sql/item.cc	2014-10-06 17:53:55 +0000
 
+++ sql/item.cc	2014-12-14 16:38:11 +0000
 
@@ -9792,10 +9792,19 @@ void Item_ref::update_used_tables()
 
 
 table_map Item_direct_view_ref::used_tables() const
 
 {
 
+  TABLE *null_ref= null_ref_table;
 
+
 
+  if (null_ref == NULL)
 
+    null_ref= view->get_real_join_table();
 
+  else if (null_ref == NO_NULL_TABLE)
 
+    null_ref= NULL;
 
+
 
   return get_depended_from() ?
 
          OUTER_REF_TABLE_BIT :
 
          ((view->is_merged_derived() || view->merged || !view->table) ?
 
-          (*ref)->used_tables() :
 
+          ((*ref)->used_tables() ?
 
+           (*ref)->used_tables() :
 
+           (null_ref ?  null_ref->map : (table_map)0 )) :
 
           view->table->map);
 
 }
 
 
 
 
=== modified file 'sql/item.h'
 
--- sql/item.h	2014-11-18 14:42:40 +0000
 
+++ sql/item.h	2014-12-14 16:32:40 +0000
 
@@ -3440,6 +3440,7 @@ class Item_direct_view_ref :public Item_
 
     else
 
       Item_direct_ref::save_in_result_field(no_conversions);
 
   }
 
+  bool const_item() const { return FALSE; };
 
 
   void cleanup()
 
   {
 
 

Comment by Nathan Parrish [ 2015-02-02 ]

Here's another test case that demonstrates the problem. This is similar to the type of query generated by Microsoft's EntityFramework. The workaround mentioned above does work.

DROP DATABASE IF EXISTS testdb;
 
CREATE DATABASE testdb;
 
 
 
USE testdb;
 
 
 
CREATE TABLE foo (
 
  id   INTEGER PRIMARY KEY AUTO_INCREMENT,
 
  data VARCHAR(64)
 
)
 
  ENGINE = TokuDB;
 
 
 
CREATE TABLE bar (
 
  id     INTEGER PRIMARY KEY AUTO_INCREMENT,
 
  foo_id INTEGER REFERENCES foo (id),
 
  data   VARCHAR(64)
 
)
 
  ENGINE = TokuDB;
 
 
 
INSERT INTO foo (data) VALUES ('foo1'), ('foo2'), ('foo3');
 
INSERT INTO bar (foo_id, data) VALUES (1, 'bar1'), (2, 'bar2'), (3, 'bar3'), (NULL, 'bar4'), (NULL, 'bar5');
 
 
 
SELECT *
 
FROM bar
 
  LEFT OUTER JOIN (SELECT
 
                     id,
 
                     data,
 
                     1 AS indicator
 
                   FROM foo) AS foo_projection
 
    ON foo_projection.id = bar.foo_id
 
WHERE indicator = 1 AND indicator IS NOT NULL;

Comment by Sergei Petrunia [ 2015-03-04 ]

was changing the wrong bug

Comment by Sergei Petrunia [ 2015-03-04 ]

.. Review feedback sent. Cannot push yet.

Comment by Oleksandr Byelkin [ 2015-04-15 ]

revision-id: b9426b304b2900b0dd95a41c52d5d1ac90357cfe
parent(s): cc84ac3be41d9d6ac480d55449d5bf4e324cca10
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-04-14 23:18:54 +0200
message:

MDEV-6892: WHERE does not apply

Taking into account implicit dependence of constant view field from nullable table of left join added.

Fixed finding real table to check if it turned to NULL (materialized view & derived taken into account)

Removed incorrect uninitialization.

—

Comment by Sergei Petrunia [ 2015-04-21 ]

The fix for this bug may be useful for MDEV-7893

Comment by Sergei Petrunia [ 2015-04-22 ]

The first bit of review feedback is the same as during the previous iteration:
why does Item_direct_view_ref::update_used_tables() call check_null_ref()?

check_null_ref analyzes null_ref_table->null_row which is generally not set
to a meaningful value during query optimization.

Comment by Oleksandr Byelkin [ 2015-04-22 ]

Sorry, I just forget to remove the line

revision-id: 21f2a0e32bca94bdacc0c56a9b3531e7f264886c
parent(s): cc84ac3be41d9d6ac480d55449d5bf4e324cca10
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-04-22 10:39:13 +0200
message:

MDEV-6892: WHERE does not apply

Taking into account implicit dependence of constant view field from nullable table of left join added.

Fixed finding real table to check if it turned to NULL (materialized view & derived taken into account)

Removed incorrect uninitialization.

—

Comment by Sergei Petrunia [ 2015-04-22 ]

Ok to push.

Comment by Igor Babaev [ 2019-06-20 ]

It looks like still have some problems with a variant of the reported query:

MariaDB [test]> explain extended select x.id, message from (select id from t1) x left join (select id, 1 as message from t2) y on x.id=y.id where message <> 0;
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |                                                 |
 
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
 
 
MariaDB [test]> show warnings;
 
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                      |
 
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `test`.`t1`.`id` AS `id`,1 AS `message` from `test`.`t1` left join (`test`.`t2`) on(`test`.`t2`.`id` = `test`.`t1`.`id`) where 1 <> 0 |
 
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+

I would expect conversion of left join into inner join here as the condition message <> 0 rejects null complements.

I will fix the problem in the patch for MDEV-19778.

Generated at Thu Feb 08 07:15:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.