From: Ole John Aske
Date: January 13 2011 10:42am
Subject: bzr commit into mysql-5.5 branch (ole.john.aske:3238)
List-Archive: http://lists.mysql.com/commits/128628
Message-Id: <20110113104253.1D0F5223@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============7316999927494725668=="

--===============7316999927494725668==
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.5/ based on revid:nirbhay.choubey@stripped

 3238 Ole John Aske	2011-01-13
      Fix for #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-10-19 06:45:18 +0000
+++ b/mysql-test/r/select.result	2011-01-13 10:42:48 +0000
@@ -4867,6 +4867,70 @@ 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
 #
 # Bug#54515: Crash in opt_range.cc::get_best_group_min_max on 

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2010-10-19 06:45:18 +0000
+++ b/mysql-test/t/select.test	2011-01-13 10:42:48 +0000
@@ -4123,6 +4123,76 @@ 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
 
 --echo #

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-01-13 09:20:45 +0000
+++ b/sql/sql_select.cc	2011-01-13 10:42:48 +0000
@@ -12040,7 +12040,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);
     }
   }
@@ -12061,7 +12061,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);
     }
   }


--===============7316999927494725668==
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\
#   sidh9k083k39k799
# target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\
#   /mysql-5.5/
# testament_sha1: 36b226f3d04830673f4a66674ce5d3f56e11e457
# timestamp: 2011-01-13 11:42:52 +0100
# base_revision_id: nirbhay.choubey@stripped\
#   cv5ikkfbtuaa3ezt
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZpXOuwABUjfgFAwePf//3//
3+C////1YArevfK7t1KkgXsG8FNO9uNAHroLY9qdxkMoJhTSeo08kep6GozSb1QAAA0aPUDQAAkp
NCZR6eintFPSeoAA0ANAAAAAAcaMmRhGIBhNBgE0GgZMmjJkMIDCREgE0JglH6n6Rqj1PSe1T00y
hmoaAAANGgIpTKJmQo/SnpNimm1HpppBoGg2po9RptJkGj01AJJAQAjTTQTTBAKn5KPSPUbSMm0g
aG1NGJMZkKfl4kTO4HoQm7HoH6P2dTqZDcHgDdwKUi4k1U1a10IiQMuIVzWlQ5QaAbEgnGAFAKgX
gwtzW3CnZSn3q4Owz4Z9YXXb4Tl6BTIcS8VoqZ22zgzM2gUr/vkAaVt31m+On0vWPDKUF4F0mA4z
0vQHxF/c4/OXGIpCAsQIkEz5y9e+zmWs4+0Igdc+P2grLdXcv0WSbabbXYaOsVdWDPI+MxJOFoKa
HAYd2yphEc9/UFR6CzLfE6h4i7RZvApFYLWHG84J3ghvdQ0jT7hw5AmMBSD22BvmogOIB9q4EZhs
Ax02cRympv1hSNuD1SrSuNK4FNlrKWGJ/6GPX5UceLpil7aXDQqaFLXRslFxR5r37lGqh1JThTZG
utV2VUC+0uVpFoVzVEUr/MzB5AUECUAMYFhNqVrfUJ5hTF9lZfQQImUtGmc/xoi3tpixROKsfS/w
Paob/UePd07K/G88Dts83eHXhyCWAeX4Wxth04mQ90Uz8VCD2zfwlJXGc2nymIxjGcxy2dVo48Zm
Ru9Rb1WFYO57QuBa9Q5a+QHSz2E/naFoVg4cbA6DMHrAMj45Mi5fQHE11N6vKdc+rsDbwEVyQE2L
OlBL4QhzwnaCoVamnpkio2flmEgiCcAtgKgXYqZSWK8aJIChk4HmsPi8wFIBwkySgGjQMUsQ0xlp
yXwCKWp7BNbjIu5yvCuDE4LQaxMGLRSVJLPAOoIEvPeBeJfJBgmEwjEy+7mYecxK5hKMIzL0ZFc6
7JaLXJ4lV9AFgXt7XZFFRYEr1Ctj15kezncUl2PeaAsJJzb9KxiZvFhpq2CTmNwbgcJLK4EVKhjT
aVJxaTezCHRKzQnbDM3LKAqQXfl/2+o21x3lt+DFzSdDlrDWGCsBmMaB6FdUWusNUTZheYtyEVwk
blQHLYI2+WBrvcmqZrd1iL1qrUXCHwcSNw9PoLh9xriS4RaFyqiLCs2i1QRcewu9jL1p1s1XWM0D
BuOq3TxQ3mGXFkGBOIypYfA8uFUo2u8Nz6jUUuqTTOkaqGlQgY5Axc1w/vocd2gfXQTDBxmPzyRM
H4F5OLx6vKTL1IkdO/GgrT3kKbz4U37NPbPAccSogY9BFM0saDUONE6aQTDGLy6IZiHqBSRJEafQ
fTeUHJ9rXMdxK7NZUjkYDKZIcs1ZKLtGEsIaKJvJFe+JQSJkmVjBWOtcPv1NMvqHFWQi0RALr8Sy
yAnM6s0BVioK6IdjrC0VtrsJ2M8zUfSSwEhxg8QQu9W3XzOKRAxiDug5udtmoHw7wUlsGt9gHqHW
HzHAPINy0YIghg6QJzJDAUA++Vt5RqHmZDAMGsKBpkA3AaRsg7QEPWrMLQWx1j1NoZDdB+NFI9it
i8NLzg1jCv7GwBaCADmG46ZMyBAvinSh4AaQVBVC1CRmCcwKBetBImIJAGDAQhWBtDQC3ESxth2K
N8GQlQwFAwrgpNg42Y1ChrGsdgOwkEwuhWNAYQ0CvBaQ0q8KQWgLAUQVwFYsUk8HoiG2EhS0rNGl
bo2Rgax8R5ATLDkdJqTNJCGB0WLJZ0TZoTOqhtN4+egtJpIeObVoOXU85+0DhBIXbIaKBKanmGC5
eiVRRir5Dt7CKFT9cgOfo+/RLnghoDqwC1i0UjCnQoc0rPBIJ6x4oGTiov+QaKy+Z3fcThMZCZGB
gIW5wZnIU4l+UOKcH1OHOW/A7ziGcSBeeVy6p5gNpWV54PmqgoXgUPizhKUpIDJwvzIA7aTBE3u+
5UpwHSLOnoTvE27ctgy1dHWaitGdRUi4hJJx5HIuNkMKpiMygIrlgRW+IGYiAtozfBYEl+W9gcNR
oFSo50nIzXe4kAOjlui9s6scncSjb5j+BV4EfVYLaC4HK5w7SK0TLUo7TNlyPM7ggQCYkx/jlR07
QhxhwM+uyDba1l5uWBvL+Jq79l7N+LjVpBYmj0A8wYTMLNbAkQdoCXVOFtNECDAzvDlAU/Gqo/Dz
VoYdaRNeIoAa3ycl8980uC6phU9VsXmWjJm7he7pHkUFA+EW8Tcw3LvLV3lncTC0mXhtbf969k5Y
FMht0FBjDkeidVzJnMZGmkdPgwszpTjwhwXn3W4FxvEQ6ANPkmQYvOiH8j7HJfM6oUkqXkzGBaZn
1oOAG8VeIMR9h4boWpMnb6Fs80pCNY/cUf+LYh6elHirTUVIN5rVhOsNiDQRkLNTmzmhO6wqG6vx
6+iazMLauq5jY2MeoVp66tKnyiSI/YVKFB+zgQOwF2XbEMDXkvJB3MK5Sj0iZxIElMGpG/vjS7zm
skh4XfwlMiljJeTLI0B1JCh1gEQeWECGM0fxZVJkqxLqCeJ4u8mCwTwq/R5IvZVroyfxcLAwcF0b
ul7yWMDykAaBrrF4qkO/A1lpvanDhZTSFPyVkUClHlzIL3UTbpBU1IhYNBgmyhAkCsTgkNENNanC
FKk4B+oe8CNlqnxKJFPn9DlZhHFtxzKUnl14WxKGITC1YcJYcuLHhCBO1M5CvdqHp84Dlx9Z7QhR
39SQcxxYo6cISxDWyYI1H/CTFIVIxlMeMlSb2XDWFR/A2qz8GDM29rb0HdTrZMhnEAkK5W2nivUX
uAXgJgUDpXU2rEen0K1/S+whSRQLLPifEu1Aeh46MgrWbIcmMHDgK04EevJOialaKtQEkqCALFXf
arGwIjJdkwUTuVmNwvluKlbD0y5FuK1uWNYrpUrbU3ngq7ZkVBkVR7xaaOjoy3cB+FRdZUr7xjCp
UltsxQEDVGyRROShEQDxQqk7nAvPgZNc4aV2ttvNvIgg2YJOtNsxIgY3GkUiOSjGt7jfrsylSr6T
W5nuTdRbMEjNlUUK0GkdwdwQ4QYDsNgBQDovUCEypq5Bhqe1SVx+g5yYI7DP3lMLB/ZZzPeoweir
0WY4TVpyyYS+FG2LLd2F91kcipFbKLTJieo6E6m0HIss9F2HBBMg5MyRkMbFmoGnJYrpce3yPt0k
tKN2QihlfzkbZ3RS1Ke+40yqs1FuMMlAtRywtyGQ0eAzg1ZQMb99JkjIaX4L3yFTYA2dFcEjSJ2q
Ahgis2UFgWHqSFFe4Jk7RSPkV3lAa9Zkfmal/8XckU4UJCaVzrsA


--===============7316999927494725668==--