[MDEV-6892] WHERE does not apply - Jira

XML

Word

Printable

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 |

+------+---------+

is duplicated by

MDEV-6919 wrong result from subquery - too much optimization?

MDEV-6972 Left joined subquery gives wrong result

relates to

MDEV-7893 table_elimination works wrong with on computed expression and compound unique key

MDEV-19778 Wrong Result on Left Outer Join with Subquery right on true and WHERE filter afterwards

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.