Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6892

WHERE does not apply

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Critical
        • Resolution: Fixed
        • Affects Version/s: 5.3.12, 5.5.40, 10.0.12, 10.0.14
        • Fix Version/s: 10.0.18, 5.5.43
        • Component/s: Optimizer
        • Labels:
          None
        • Environment:
          Ubunut/LXC

          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 |
+------+---------+

            Gliffy Diagrams

              Attachments

                Issue Links

                is duplicated by

                Bug - A problem which impairs or prevents the functions of the product. MDEV-6972 Left joined subquery gives wrong result

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-6919 wrong result from subquery - too much optimization?

                • Major - Major loss of function.
                • Closed

                  Activity

                  Ascending order - Click to sort in descending order
                  9 older comments
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  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.

                  —

                  Show
                  sanja Oleksandr Byelkin added a comment - 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. —
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

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

                  Show
                  psergey Sergei Petrunia added a comment - The fix for this bug may be useful for MDEV-7893
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  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.

                  Show
                  psergey Sergei Petrunia added a comment - 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.
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  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.

                  —

                  Show
                  sanja Oleksandr Byelkin added a comment - 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. —
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  Ok to push.

                  Show
                  psergey Sergei Petrunia added a comment - Ok to push.

                    People

                    • Assignee:
                      sanja Oleksandr Byelkin
                      Reporter:
                      erkules erkan yanar
                    • Votes:
                      3 Vote for this issue
                      Watchers:
                      9 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: