diff options
| author | Igor Babaev <igor@askmonty.org> | 2010-09-09 21:16:01 -0700 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2010-09-09 21:16:01 -0700 |
| commit | b3cd40e0d2aeafa64a1fa8b81a1d3905c67f4726 (patch) | |
| tree | 3638ee5db75f763c6177e9d2f4199e95ebf92d7a /mysql-test/r/join_nested.result | |
| parent | 6f59c41d2dfad95ecdb161d6b08ec4cce736f7a8 (diff) | |
| download | mariadb-git-b3cd40e0d2aeafa64a1fa8b81a1d3905c67f4726.tar.gz | |
Fixed bug #49322.
When not-exists optimization was applied to a table that
happened to be an inner table of two outer joins, one
embedded into another, then setting the match flag for
the embedding outer join on could be skipped. This caused
generation of extra null complemented rows.
Made sure that the match flags are set correctly in all cases
when not-exists optimization is used.
Diffstat (limited to 'mysql-test/r/join_nested.result')
| -rw-r--r-- | mysql-test/r/join_nested.result | 58 |
1 files changed, 58 insertions, 0 deletions
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 2bdac08e7fd..9033e2b2252 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1743,4 +1743,62 @@ ON t4.carrier = t1.carrier; COUNT(*) 6 DROP TABLE t1,t2,t3,t4,t5; +CREATE TABLE t1 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int DEFAULT NULL, +KEY idx(a) +); +CREATE TABLE t2 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int DEFAULT NULL, +KEY idx(a) +); +CREATE TABLE t3 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int DEFAULT NULL, +KEY idx(a) +); +INSERT INTO t1 VALUES +(1,2), (2,7), (3,5), (4,7), (5,5), (6,NULL), (7,NULL), (8,9); +INSERT INTO t2 VALUES +(1,NULL), (4,2), (5,2), (3,4), (2,8); +INSERT INTO t3 VALUES +(1,9), (2,2), (3,5), (4,2), (5,7), (6,0), (7,5); +SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a +FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a; +pk a pk a pk a +1 2 4 2 2 2 +1 2 4 2 4 2 +1 2 5 2 2 2 +1 2 5 2 4 2 +2 7 NULL NULL NULL NULL +3 5 NULL NULL NULL NULL +4 7 NULL NULL NULL NULL +5 5 NULL NULL NULL NULL +6 NULL NULL NULL NULL NULL +7 NULL NULL NULL NULL NULL +8 9 NULL NULL NULL NULL +SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a +FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a +WHERE t2.pk IS NULL; +pk a pk a pk a +2 7 NULL NULL NULL NULL +3 5 NULL NULL NULL NULL +4 7 NULL NULL NULL NULL +5 5 NULL NULL NULL NULL +6 NULL NULL NULL NULL NULL +7 NULL NULL NULL NULL NULL +8 9 NULL NULL NULL NULL +SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a +FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a +WHERE t3.pk IS NULL; +pk a pk a pk a +2 7 NULL NULL NULL NULL +3 5 NULL NULL NULL NULL +4 7 NULL NULL NULL NULL +5 5 NULL NULL NULL NULL +6 NULL NULL NULL NULL NULL +7 NULL NULL NULL NULL NULL +8 9 NULL NULL NULL NULL +DROP TABLE t1, t2, t3; End of 5.0 tests |