diff options
| author | Sergey Glukhov <Sergey.Glukhov@sun.com> | 2010-03-19 10:21:37 +0400 |
|---|---|---|
| committer | Sergey Glukhov <Sergey.Glukhov@sun.com> | 2010-03-19 10:21:37 +0400 |
| commit | d1c2e8508fcc795eb5aeafe1af492929ec13ee97 (patch) | |
| tree | bac600074d72d2769dd262757434eebbacb45fcf /mysql-test/r/join_outer.result | |
| parent | caa1ccb0c324bca664112a5562f174f603e0a0b7 (diff) | |
| download | mariadb-git-d1c2e8508fcc795eb5aeafe1af492929ec13ee97.tar.gz | |
Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison
Optimizer erroneously translated LEFT JOIN into INNER JOIN.
It leads to cutting rows with NULL right side. It happens
because Item_row uses not_null_tables() method form the
base(Item) class and does not calculate 'null tables'
properly. The fix is adding calculation of 'not null tables'
to Item_row.
mysql-test/r/join_outer.result:
test result
mysql-test/t/join_outer.test:
test case
sql/item_row.cc:
adding calculation of 'not null tables' to Item_row.
sql/item_row.h:
adding calculation of 'not null tables' to Item_row.
Diffstat (limited to 'mysql-test/r/join_outer.result')
| -rw-r--r-- | mysql-test/r/join_outer.result | 20 |
1 files changed, 20 insertions, 0 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 083be3737f7..4543446e807 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1289,3 +1289,23 @@ a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b ) 1 3 6 3 NULL 3 6 3 DROP TABLE t1, t2; +# +# Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison +# +CREATE TABLE t1(f1 INT, f2 INT, f3 INT); +INSERT INTO t1 VALUES (1, NULL, 3); +CREATE TABLE t2(f1 INT, f2 INT); +INSERT INTO t2 VALUES (2, 1); +EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 +WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select '1' AS `f1`,NULL AS `f2`,'3' AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t1` left join `test`.`t2` on(multiple equal(NULL)) where ((coalesce('1',NULL),'3') in ((1,3),(2,2))) +SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 +WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); +f1 f2 f3 f1 f2 +1 NULL 3 NULL NULL +DROP TABLE t1, t2; +End of 5.1 tests |