bk commit into 5.0 tree (igor:1.1951) BUG#12154

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1951 05/08/05 16:43:35 igor@stripped +3 -0
  mysql_priv.h:
    Fixed bug #12154: a query returned: Column <name> cannot be null.
    The problem was due to a bug in the function setup_table_map:
    the flag maybe_null was set up incorrectly for inner tables of
    nested outer joins.
  join_nested.result, join_nested.test:
    Added a test case for bug #12154.

  sql/mysql_priv.h
    1.337 05/08/05 16:34:48 igor@stripped +6 -0
    Fixed bug #12154: a query returned: Column <name> cannot be null.
    The problem was due to a bug in the function setup_table_map:
    the flag maybe_null was set up incorrectly for inner tables of
    nested outer joins.

  mysql-test/r/join_nested.result
    1.12 05/08/05 16:33:58 igor@stripped +55 -0
    Added a test case for bug #12154.

  mysql-test/t/join_nested.test
    1.8 05/08/05 16:33:16 igor@stripped +48 -0
    Added a test case for bug #12154.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-5.0-0

--- 1.336/sql/mysql_priv.h	Thu Aug  4 04:27:48 2005
+++ 1.337/sql/mysql_priv.h	Fri Aug  5 16:34:48 2005
@@ -1450,6 +1450,12 @@
   table->status= STATUS_NO_RECORD;
   table->keys_in_use_for_query= table->s->keys_in_use;
   table->maybe_null= table_list->outer_join;
+  TABLE_LIST *embedding= table_list->embedding;
+  while (!table->maybe_null && embedding)
+  {
+    table->maybe_null= embedding->outer_join;
+    embedding= embedding->embedding;
+  }
   table->tablenr= tablenr;
   table->map= (table_map) 1 << tablenr;
   table->force_index= table_list->force_index;

--- 1.11/mysql-test/r/join_nested.result	Wed Jun 15 05:54:52 2005
+++ 1.12/mysql-test/r/join_nested.result	Fri Aug  5 16:33:58 2005
@@ -1343,3 +1343,58 @@
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
+INSERT INTO t1 VALUES (23, 2340), (26, 9900);
+CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));
+INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');
+create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);
+INSERT INTO t3 VALUES (3,23), (6,26);
+CREATE TABLE t4 (groupid int(12));
+INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);
+SELECT * FROM
+(SELECT DISTINCT gl.groupid, gp.price
+FROM t4 gl 
+LEFT JOIN
+(t3 g INNER JOIN t2 p ON g.goodsid = p.goods 
+INNER JOIN t1 gp ON p.goods = gp.goods)
+ON gl.groupid = g.groupid and p.shop = 'fr') t;
+groupid	price
+1	NULL
+2	NULL
+3	2340
+4	NULL
+5	NULL
+6	9900
+CREATE VIEW v1 AS
+SELECT g.groupid groupid, p.goods goods,  
+p.name name, p.shop shop, 
+gp.price price
+FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods
+INNER JOIN t1 gp on p.goods = gp.goods;
+CREATE VIEW v2 AS
+SELECT DISTINCT g.groupid, fr.price
+FROM t4 g
+LEFT JOIN
+v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';
+SELECT * FROM v2;
+groupid	price
+1	NULL
+2	NULL
+3	2340
+4	NULL
+5	NULL
+6	9900
+SELECT * FROM 
+(SELECT DISTINCT g.groupid, fr.price
+FROM t4 g
+LEFT JOIN
+v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;
+groupid	price
+1	NULL
+2	NULL
+3	2340
+4	NULL
+5	NULL
+6	9900
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3,t4;

--- 1.7/mysql-test/t/join_nested.test	Wed Jun 15 05:53:39 2005
+++ 1.8/mysql-test/t/join_nested.test	Fri Aug  5 16:33:16 2005
@@ -770,3 +770,51 @@
 EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
 
 DROP TABLE t1,t2,t3;
+
+#
+# Bug #12154: creation of temp table for a query with nested outer join
+# 
+
+CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
+INSERT INTO t1 VALUES (23, 2340), (26, 9900);
+
+CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));
+INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');
+
+create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);
+INSERT INTO t3 VALUES (3,23), (6,26);
+
+CREATE TABLE t4 (groupid int(12));
+INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);
+
+SELECT * FROM
+(SELECT DISTINCT gl.groupid, gp.price
+  FROM t4 gl 
+       LEFT JOIN
+       (t3 g INNER JOIN t2 p ON g.goodsid = p.goods 
+             INNER JOIN t1 gp ON p.goods = gp.goods)
+       ON gl.groupid = g.groupid and p.shop = 'fr') t;
+
+CREATE VIEW v1 AS
+SELECT g.groupid groupid, p.goods goods,  
+       p.name name, p.shop shop, 
+       gp.price price
+  FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods
+            INNER JOIN t1 gp on p.goods = gp.goods;
+
+CREATE VIEW v2 AS
+SELECT DISTINCT g.groupid, fr.price
+  FROM t4 g
+       LEFT JOIN
+       v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';
+
+SELECT * FROM v2;
+
+SELECT * FROM 
+(SELECT DISTINCT g.groupid, fr.price
+  FROM t4 g
+       LEFT JOIN
+       v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3,t4;