From 42010cde4ad923b03788e430646c0ff395069f01 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 3 Jan 2007 12:16:03 -0800 Subject: Fixed bug #24345. This bug appeared after the patch for bug 21390 that had added some code to handle outer joins with no matches after substitution of a const table in an efficient way. That code as it is cannot be applied to the case of nested outer join operations. Being applied to the queries with nested outer joins the code can cause crashes or wrong result sets. The fix blocks row substitution for const inner tables of an outer join if the inner operand is not a single table. mysql-test/r/join_nested.result: Added a test case for bug #24345. mysql-test/t/join_nested.test: Added a test case for bug #24345. --- mysql-test/r/join_nested.result | 43 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 43 insertions(+) (limited to 'mysql-test/r/join_nested.result') diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 0747418111b..f5c98f383b7 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1562,3 +1562,46 @@ id ngroupbynsa 2 1 2 1 DROP TABLE t1,t2,t3,t4,t5; +CREATE TABLE t1 ( +id int NOT NULL PRIMARY KEY, +ct int DEFAULT NULL, +pc int DEFAULT NULL, +INDEX idx_ct (ct), +INDEX idx_pc (pc) +); +INSERT INTO t1 VALUES +(1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL); +CREATE TABLE t2 ( +id int NOT NULL PRIMARY KEY, +sr int NOT NULL, +nm varchar(255) NOT NULL, +INDEX idx_sr (sr) +); +INSERT INTO t2 VALUES +(2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand'); +CREATE TABLE t3 ( +id int NOT NULL PRIMARY KEY, +ct int NOT NULL, +ln int NOT NULL, +INDEX idx_ct (ct), +INDEX idx_ln (ln) +); +CREATE TABLE t4 ( +id int NOT NULL PRIMARY KEY, +nm varchar(255) NOT NULL +); +INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique'); +SELECT t1.* +FROM t1 LEFT JOIN +(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id +WHERE t1.id='5'; +id ct pc +5 NULL NULL +SELECT t1.*, t4.nm +FROM t1 LEFT JOIN +(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id +LEFT JOIN t4 ON t2.sr=t4.id +WHERE t1.id='5'; +id ct pc nm +5 NULL NULL NULL +DROP TABLE t1,t2,t3,t4; -- cgit v1.2.1