From: Ole John Aske
Date: December 7 2010 11:50am
Subject: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58422
List-Archive: http://lists.mysql.com/commits/126195
X-Bug: 58422
Message-Id: <20101207115006.4ADD0222@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============3329883255225472464=="

--===============3329883255225472464==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:georgi.kodinov@stripped

 3477 Ole John Aske	2010-12-07
      Fix for bug#58422: Incorrect result when OUTER JOIN'ing with an empty table
      
      Fixed incorrect checks in join_read_const_table() for when to 
      accept a non-existing, or empty const-row as a part of the const'ified 
      set of tables.
      
      Intention of this test is to only accept NULL-rows if this table is outer joined
      into the resultset. (In case of an inner-join we can conclude at this point that 
      resultset will be empty, end we want to return 'error' to signal this.)
      
      Initially 'maybe_null' is set to the same value as 'outer_join' in 
      setup_table_map(), mysql_priv.h ~line 2424. Later simplify_joins() will
      attemp to replace outer joins by inner join whenever possible. This
      will cause 'outer_join' to be updated. However, 'maybe_null' is *not* updated
      to reflect this rewrite as this field is used to currectly set the 'nullability'
      property for the columns in the resultset.
      
      We should therefore change join_read_const_table() to check the 'outer_join'
      property instead of 'maybe_null', as this correctly reflect the nullability of
      the *execution plan* (not *resultset*).

    modified:
      mysql-test/r/select.result
      mysql-test/t/select.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2010-06-24 08:00:48 +0000
+++ b/mysql-test/r/select.result	2010-12-07 11:50:00 +0000
@@ -4797,4 +4797,68 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
 1
 1
 DROP TABLE t1;
+#
+# Bug #58422: Incorrect result when OUTER JOIN'ing 
+# with an empty table
+#
+CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+EXPLAIN
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON TRUE)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON TRUE)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk	i	pk	i	pk	i
+EXPLAIN
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 CROSS JOIN t_empty)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 CROSS JOIN t_empty)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk	i	pk	i	pk	i
+EXPLAIN
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk	i	pk	i	pk	i
+DROP TABLE t1,t2,t_empty;
 End of 5.1 tests

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2010-06-24 17:13:08 +0000
+++ b/mysql-test/t/select.test	2010-12-07 11:50:00 +0000
@@ -4088,4 +4088,74 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
 DROP TABLE t1;
 
 
+
+--echo #
+--echo # Bug #58422: Incorrect result when OUTER JOIN'ing 
+--echo # with an empty table
+--echo #
+
+CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+
+EXPLAIN
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON TRUE)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON TRUE)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 CROSS JOIN t_empty)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 CROSS JOIN t_empty)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+
+
+DROP TABLE t1,t2,t_empty;
+
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-10-29 08:23:06 +0000
+++ b/sql/sql_select.cc	2010-12-07 11:50:00 +0000
@@ -11787,7 +11787,7 @@ join_read_const_table(JOIN_TAB *tab, POS
       /* Mark for EXPLAIN that the row was not found */
       pos->records_read=0.0;
       pos->ref_depend_map= 0;
-      if (!table->maybe_null || error > 0)
+      if (!table->pos_in_table_list->outer_join || error > 0)
 	DBUG_RETURN(error);
     }
   }
@@ -11808,7 +11808,7 @@ join_read_const_table(JOIN_TAB *tab, POS
       /* Mark for EXPLAIN that the row was not found */
       pos->records_read=0.0;
       pos->ref_depend_map= 0;
-      if (!table->maybe_null || error > 0)
+      if (!table->pos_in_table_list->outer_join || error > 0)
 	DBUG_RETURN(error);
     }
   }


--===============3329883255225472464==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/ole.john.aske@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: ole.john.aske@stripped\
#   rdf0awjzi9u6pluu
# target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\
#   /mysql-5.1/
# testament_sha1: 60ec0fbcd50f4b8faaec04ca5d3a85c2c62c2977
# timestamp: 2010-12-07 12:50:06 +0100
# base_revision_id: georgi.kodinov@stripped\
#   l819wohslm0k87fn
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWXoBJQ8ABSPfgFAwePf//3//
3+C////1YAr+++dbu3W23d0vQUZwAztj1Wxuuq69u3ZtNjV4SSSeUwIU3pJ6abRqein6I9JMjRk0
0GmgaBo0ASlFN5U8Ygj1R6npNAaPUD1AAAaZAABoONGTIwjEAwmgwCaDQMmTRkyGEBhIiRkg0Jk1
R+BSD1PTU2JqeoDQaADQAARSmgmTQZVPaYlPGlP1J+lP1TbVDQMnoE2nlJtQaP0UBJIBGiYhpoTK
abSp+TFTbUDQRmUejSHqMQZJxnIA/l3InW8jwQnfB4D3Pe73eyG0dgc/I97Q06oJu3Dp4SGkx8de
H6nwr4d/2i4i1HQTC1CqKWWuthTrpT71gHmZk7NgWtvhOXiKZBlRVFGZts4tt6BTn/OEB2ro4cNM
XUjOuHG19F1mXIwOdcZEuoXaV/mHA8UASnEggF8ft1TaCdnHuB4FmarsBVtq7l6luTbTba8po9kX
jdV5mDGsnXYFIudzp0k9z+wFTxF+pd3naHeLmLc8CwVRZh9K/EEcwQ4+8bBs9A5+kJxgLAcrA3nA
gOwK7tBFClFNnntX2Wm/36nHj155b2N7nzuva06i/COCfJ/SES/kddbpil8mtw0LWhY2qN0ouKeC
+fio20dYWY2XRrVVnGAW2MFYiwXzdEUv9RmDgCggSgBjAqq4LbGQTzGyL7qmFCBEzllrnP/lIt8m
uLFJxVz7H6d5A4/cezirxw55Tlhtdx9C578twXmANfMzDMy3K8BUN4XPxcCcg3G/lKSwOw0HoMRj
GM82w29Vhx8UzI47xcllCoO6cg1At20ct3YB23eYn8bwvCoOHG0OsuDvAKhxmOn2B0FOWDmp1LhN
mcQ/quDelvxDiRjVVoin0XUYcUY1Km8pVxeOcJgkprZAOkGoelwrVdi7bcvXsLe1DIiBxAZWaBDa
ROkRULFAzoSipiZCehDgaLAcmPIrGeaqOnSJYInClbWIJKQJrcIuNBcYXauS5rTqNgmJiZe3AeId
QQJeGQSPRo2KUtH3cTnx3l60iNxXUitpzEZK91OPB1/AvvOB9KNmBAxTevLERgEKp6a1ysgHimX6
zKRZMjy5nvcUJMQz00NFiSaLm3REguwl4OQOElv2AiYjYcaYuybWsZOit/Eauf15XqFSJOkc5rfO
AqAmNRgf94D25E8NZlv0DQJZIx1OJk2IM4gMcYmBzuTh2hCF8d/jUvkXF6No4zZWkCDYD05plSXi
bXBiwVIl1VtMZg/uocQuKkiwhadx6DH/1CZeGdwjmPWyzGIPIadkJ2bAb2LAvM4uNtctukaKFKbs
kWOIojlGOYQpbFjlEksS1DhVbDu4FpQbZRFMDfcOTGgjn4nInGuRhrEM0Hah/2SwvMDrKKw8blOS
ID+t+5OPs1nKEsq42oZxwuTD3nW8mp7+pPLKF6s3UHYtdbW0lew4tsGxlI2DtRr3ayli/E4YaS7M
adT0YlIOoRryrOEYEOX0Mxz4KKxAXO6UNl7i4KirVx7TPO1H0kphIt4LvCF6tLiftc0iBi4MwOze
1nAHXtDmPvCxTzgfiPEPcOYe0banMwwfECeckMBMD6pVvrHCPcyGAYOITDZIBtBsGkGoCH5KzhgC
seI97gGQ4gd01g+CtGMbHgDcMK/4NAGAIAPeNr7JM5AgXinxQ8wGUGcaBvFLQlaDMPFCQwISAIBg
IQuAqGYFtRKMweCjeDITCMBMMK8lh4jzM44RQ4jcPiD4kgnDEFwzBoC4cgN4XuQKwbgtBoBxgYR+
ZWQSSgMwSFMCs6Ni4hpGBuHadgJ5YdT3G9PNJCGB66KSn3QUEED4uVFqJAfUe37y2H4wqWCCmt9h
WqStvS9tE+AtXsPP3kYKJfSgXQx+P0Z+/ERQLPikUnBpnNz+LDHgEFJuTlVogxE/hhvExgF+R2ii
CMxPL6NM0gE6mEgIX8qNRGhfXDnngu2Y4PdXRPBym/lLyfE6nroVxNBta/tYPLq90rlUP4E3QZyS
jG9AYuF+xAHbyYIm91EwHogzpZJ2mm66Js1ldOZVG8qje+BJLA9PcYnPouqXeI1EwevUq8i58RaC
9RvbH4QC/pRXhjLL4eKKbK5dcMkkYgKSTDVb8yJqd+Gwyx8aoWRI4lNHDsBWiZZKO42svT5A8eEh
Jj++yfPgEOMNxNTZplaKZMUuWEyEMpLo3czZP04x7gKkp4QNgMJmFot4PbWEfNOR1JuJsiRYGd09
EBU2aip+fYtQZdVYxkEwARi1zL8uepeh5IGvO7C0hiNQ5hNuguusJFcaScv2GlzrgnGkIgogUYQ3
2q+i2JyxEsBuRVPT2KdJtTuKqjutJS0YGbPOOlsYUycHLsm3qOPUoZ9wwDtYFboCKHGtEV463Jbp
aCgEcl4jMTyS2ntQcwOIq4h6k4lkIgG6GCTJ2+xZ/xSbQX+Dqwssa8qI0DeKUYLkDLQJSGoRlqdx
KkEeSZCiOaP5d/N8y4XSjDMMww2sVx8umS0ziSI+4tUKH4bugoOvoU6MvMkBFxVcJ5UMECusms2s
pwZAtnaK1dIlHi9ehirbfAzXiyzDIO0kKHZDcmVCoKDIUlGSsfVDgYG1XWpIZDsKgb2QWf9IkYsq
rsZPyW14sjJwYS8MbxHTKJTdAnMAgtY7FYta4pQtxlJmwjzO6anbghKEvYsD0Cg//HirHrmko3dB
W1RC8aDBNlCBJIb7WCAaVUoQsVjksRnTcUoBfnrrK3GHd8cKVm0x0vmeQwSkz4hnctNozKbNvj1y
Y9GQGEYbyMUgu3QkXJgVncdIMuDy2pDKaoGbptmXEN8oJtat4yzsNKY52CRC2G9rjtynym5Tcs/7
MN3J8EHXk2tNDgoHUK/FY5jsXmLzAXeJgUDS6vgIdnmQT7Fg/tyvIWkUCz8tP0HnMucTsPrvozha
6ISZgugDCyRLj7NwkqTM848rMV2ITBpc+6TbKZKSxizblnc+ntvqpcrdcvBFsq1OWNZl5FK67m8Y
Lu2ZFQZF0e4WNHPlx2cjZbje21eWwZjat5vqYA6MAmo3pMaxPWRL4XpHnyh4voW0NK7223hvCIIN
mZJ3ptmJEDG41CkQ/Lje9pu2V4ypWSk3uZ6k3cWmCRnTdFC9B7B0DoENoMBxPAAmB7ccwhOYXD+g
w4XpUg1H7jJwR3mlH1OVfSfMoQeizwW4cJrU5bWEvOm+LLhyNWq6O0tRVlFpCcoYp1l4ORddxOpB
Ie5MyFuWawQTiO9wjpUf5+89b8CO3FHDYIhwzH4T0xSyU3oPB2wNV4VlbS/4VAsTurstwGS0e8M6
mWIiSskEtflhq68qQgO3DJfBeA5Q0AZjrk4eNImkBHhBhRaVsJoKi8BSX35hd54CMt2gw37yoxlS
f4u5IpwoSD0AkoeA


--===============3329883255225472464==--