From: Ole John Aske
Date: February 2 2011 7:47am
Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4163) Bug#49322
 Bug#58490
List-Archive: http://lists.mysql.com/commits/130176
X-Bug: 49322,58490
Message-Id: <20110202074758.8D3AA223@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============2260792616406590455=="

--===============2260792616406590455==
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-telco-7.0/ based on revid:martin.zaun@stripped

 4163 Ole John Aske	2011-02-02
      Fix for bug#58490: 'Incorrect result in multi level OUTER JOIN', cherry picked into telco branches.
      Also fix the duplicate bug#49322.
      
      Se original commit http://lists.mysql.com/commits/130129 for comments

    modified:
      mysql-test/r/join_outer.result
      mysql-test/t/join_outer.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2011-01-14 14:26:14 +0000
+++ b/mysql-test/r/join_outer.result	2011-02-02 07:47:52 +0000
@@ -1507,4 +1507,146 @@ WHERE t3.pk2 = t2.i
 IS UNKNOWN;
 I	I
 DROP TABLE t1,t2,t3;
+#
+# Bug#58490: Incorrect result in multi level OUTER JOIN
+# in combination with IS NULL
+#
+CREATE TABLE t1 (i INT NOT NULL);
+INSERT INTO t1 VALUES (0),    (2),(3),(4);
+CREATE TABLE t2 (i INT NOT NULL);
+INSERT INTO t2 VALUES (0),(1),    (3),(4);
+CREATE TABLE t3 (i INT NOT NULL);
+INSERT INTO t3 VALUES (0),(1),(2),    (4);
+CREATE TABLE t4 (i INT NOT NULL);
+INSERT INTO t4 VALUES (0),(1),(2),(3)   ;
+SELECT * FROM
+t1 LEFT JOIN
+( t2 LEFT JOIN
+( t3 LEFT JOIN
+t4
+ON t4.i = t3.i
+)
+ON t3.i = t2.i
+)
+ON t2.i = t1.i
+;
+i	i	i	i
+0	0	0	0
+2	NULL	NULL	NULL
+3	3	NULL	NULL
+4	4	4	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( t2 LEFT JOIN
+( t3 LEFT JOIN
+t4
+ON t4.i = t3.i
+)
+ON t3.i = t2.i
+)
+ON t2.i = t1.i
+WHERE t4.i IS NULL;
+i	i	i	i
+2	NULL	NULL	NULL
+3	3	NULL	NULL
+4	4	4	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i
+2	NULL	NULL
+3	3	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+JOIN t4
+ON t4.i=t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i	i
+2	NULL	NULL	NULL
+3	3	NULL	3
+4	NULL	NULL	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)
+ON t4a.i=t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i	i	i
+2	NULL	NULL	NULL	NULL
+3	3	NULL	3	3
+4	NULL	NULL	NULL	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+JOIN (t4 AS t4a, t4 AS t4b)
+ON t4a.i=t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i	i	i
+2	NULL	NULL	NULL	NULL
+3	3	NULL	3	0
+3	3	NULL	3	1
+3	3	NULL	3	2
+3	3	NULL	3	3
+4	NULL	NULL	NULL	NULL
+DROP TABLE t1,t2,t3,t4;
+#
+# Bug#49322(Duplicate): Server is adding extra NULL row
+# on processing a WHERE clause
+#
+CREATE TABLE h (pk INT NOT NULL, col_int_key INT);
+INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);
+CREATE TABLE m (pk INT NOT NULL, col_int_key INT);
+INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9);
+CREATE TABLE k (pk INT NOT NULL, col_int_key INT);
+INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;
+pk
+2
+4
+2
+4
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key
+WHERE TABLE1.pk IS NULL;
+pk
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+DROP TABLE h,m,k;
 End of 5.1 tests

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2011-01-14 14:26:14 +0000
+++ b/mysql-test/t/join_outer.test	2011-02-02 07:47:52 +0000
@@ -1107,4 +1107,128 @@ SELECT * FROM
 
 DROP TABLE t1,t2,t3;
 
+
+--echo #
+--echo # Bug#58490: Incorrect result in multi level OUTER JOIN
+--echo # in combination with IS NULL
+--echo #
+
+CREATE TABLE t1 (i INT NOT NULL);
+INSERT INTO t1 VALUES (0),    (2),(3),(4);
+CREATE TABLE t2 (i INT NOT NULL);
+INSERT INTO t2 VALUES (0),(1),    (3),(4);
+CREATE TABLE t3 (i INT NOT NULL);
+INSERT INTO t3 VALUES (0),(1),(2),    (4);
+CREATE TABLE t4 (i INT NOT NULL);
+INSERT INTO t4 VALUES (0),(1),(2),(3)   ;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( t2 LEFT JOIN
+   ( t3 LEFT JOIN
+     t4
+     ON t4.i = t3.i
+   )
+   ON t3.i = t2.i
+ )
+ ON t2.i = t1.i
+ ;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( t2 LEFT JOIN
+   ( t3 LEFT JOIN
+     t4
+     ON t4.i = t3.i
+   )
+   ON t3.i = t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t4.i IS NULL;
+
+
+# Most simplified testcase to reproduce the bug.
+# (Has to be at least a two level nested outer join)
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+
+# Extended testing:
+# We then add some equi-join inside the query above:
+# (There Used to be some problems here with first
+#  proposed patch for this bug)
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+   JOIN t4
+   ON t4.i=t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+   JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)
+   ON t4a.i=t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+   JOIN (t4 AS t4a, t4 AS t4b)
+   ON t4a.i=t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+
+DROP TABLE t1,t2,t3,t4;
+
+## Bug#49322 & bug#58490 are duplicates. However, we include testcases
+## for both.
+--echo #
+--echo # Bug#49322(Duplicate): Server is adding extra NULL row
+--echo # on processing a WHERE clause
+--echo #
+
+CREATE TABLE h (pk INT NOT NULL, col_int_key INT);
+INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);
+
+CREATE TABLE m (pk INT NOT NULL, col_int_key INT);
+INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9);
+CREATE TABLE k (pk INT NOT NULL, col_int_key INT);
+INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);
+
+# Baseline query wo/ 'WHERE ... IS NULL' - was correct
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;
+
+# Adding 'WHERE ... IS NULL' -> incorrect result
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key
+WHERE TABLE1.pk IS NULL;
+
+DROP TABLE h,m,k;
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-01-14 15:08:09 +0000
+++ b/sql/sql_select.cc	2011-02-02 07:47:52 +0000
@@ -11598,17 +11598,44 @@ evaluate_join_record(JOIN *join, JOIN_TA
       first_unmatched->found= 1;
       for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
       {
-        if (tab->table->reginfo.not_exists_optimize)
-          return NESTED_LOOP_NO_MORE_ROWS;
         /* Check all predicates that has just been activated. */
         /*
           Actually all predicates non-guarded by first_unmatched->found
           will be re-evaluated again. It could be fixed, but, probably,
           it's not worth doing now.
         */
+#ifndef MCP_BUG58490
+        /*
+          not_exists_optimize has been created from a
+          select_cond containing 'is_null'. This 'is_null'
+          predicate is still present on any 'tab' with
+          'not_exists_optimize'. Furthermore, the usual rules
+          for condition guards also applies for
+          'not_exists_optimize' -> When 'is_null==false' we
+          know all cond. guards are open and we can apply
+          the 'not_exists_optimize'.
+        */
+        DBUG_ASSERT(!(tab->table->reginfo.not_exists_optimize &&
+                     !tab->select_cond));
+#endif
+
         if (tab->select_cond && !tab->select_cond->val_int())
         {
           /* The condition attached to table tab is false */
+
+#ifndef MCP_BUG58490
+          if (tab->table->reginfo.not_exists_optimize)
+          {
+            /*
+              When not_exists_optimize is set: No need to further
+              explore more rows of 'tab' for this partial result.
+              Any found 'tab' matches are known to evaluate to 'false'.
+              Returning .._NO_MORE_ROWS will skip rem. 'tab' rows.
+            */
+            return NESTED_LOOP_NO_MORE_ROWS;
+          }
+#endif
+
           if (tab == join_tab)
             found= 0;
           else


--===============2260792616406590455==
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\
#   4c6jzaiwxvf7rngl
# target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\
#   /mysql-5.1-telco-7.0/
# testament_sha1: 7cc2e6b089cd2080fcdf28e9cbb8e1bff4b14ce0
# timestamp: 2011-02-02 08:47:58 +0100
# base_revision_id: martin.zaun@stripped\
#   075u8ua13s1ckk47
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWT1ws68AB+j/gGVwIAB59///
f/ffgL////pgDVy2+b7db2+++6fe6+3tzgAD699JFs3u+jL6dfJ9Pq7KPu3r0+usJRCibI1NPFPU
0xMTGSYhoAaAGjIAA0CURJ6U9NoEj1NGnqPSAyAA0AAABoACUTIJpU8NET0Rk2jE01APUGjNTQBo
AAAiSFEwqe0j0amp4p+qafpTah5I9T1AA0zU0PU00GjQCKRQp7Q0nqn6Ke1NRtTJ6jIaNqejSYQ0
GgABp6gJJBNACaYhoE0p7TVNppT9U2nqQybKMJo9QAaG8oHE8b1pFJyBQ8hRYplQasUDaFV51DcH
aQ1Eua6ktA5c246MMxD0dx+/9fhWrxQN8+oTRjm9/F53hely/l+SlngYUIL9MsCPIQqNEaxUFEVD
qEjqnr+ZNzMLoox9TciP+qgcBMBOqSyyOkIXwoXFw4ua/Zrsu1oy3DT4qODgyatXfKkqhrRJQuGh
39+hPFYDD0xSHAK6CAxIKGzZnPmLkSei9kJ1KmFS71j1sb6TVCcw0FmKtAkveKYQjWMA8QKdICzk
1XfaxmM+HamGuqUMAKKEx4iJOVFBcKhu+Zz9GUKr40j9AfG9Jk538u6xERARDl3gqr79ybLFl2f2
k6EnpxD7YxmiNBztWpuVAJbQN1rJfoBbg2mQCh2jxi8xgw4iznMC+mASD7BAG+gfbmGIxkCYTjiX
6qRLuKRhwkwKucNYYxwpxGpuG2fUgYIHEGcMATLAmHdDZyZCo1CEnlEeIyJCTin3S3rVdg3lYYFu
NI3DOFjeFrmmKqKKcjxG85QkqLRyxfHD/vsj57dZXRXx1HIP6ji67ZReOT7tY4xxaGsKj01JfkXg
kXTtTupPI2G3CTMGCxJNiidEXmH094OshvjpYHuY+xBQXRKBh2Aim4CNGg8INy4XLhcG4EIQ2lyg
sQ8YQYF9VTXidyahsxrZsmf4N9+MJ63yJtuH3duv029vTeep7e+fkrezh2peK7jwkIvPKkjUqSHS
BA6rGT3c84GpupvhfLJAN4IMIMPjoDaMGB5CCQgSCBTSTROmKIS/jktHLClq1VuWHYsNRSSEgHgG
OAUDgJhkOcHHFFIFBwuGw89SFRMxSAm+wxBfv+6kXzyF7SzziCF8TErNZcGInmI44xAiYhSZI5jm
HJFhi3gHTScgUAM1BBd+gRGukJugaw/CWmW8IWzT3GNI3pCtHY26TJaTqjmYx1jjNb+PWuOIlYnh
sDAHBuAN7OWsYUBMgooaBoHASQZOQ8NoyGQXvcxvDsHS+QdLL0UJE1bz1mBI0u5C0KVXwBVGzR8A
GyTpiuFoeBlJjgGeCMG/CojkmQSSCZSAw0hUE40i6xVVnI5dw5CeFMoliX8tg5gjm2sQnkB841ys
uNxEzBMUqicHgmFhUULBkJwxtOvsvxmALj8h07LGCU9EAebL5QrZ0t3A2ELTnIqWhOST7xyJ0++w
ZP+y+MWKRRS1J+fZ64nwmK1A8FWtjC6ChZzec38+RqYFyBh5zOgwMAwnkhEKZgICS1AidC4MhbeA
ON5iFB0GHVyHQ32FLE95D2EiIq2HG9tGFzCjqVDiB/uc+Y4rka963oGSJ9oOTBcxfAcLVoCcQGAU
FtIjlmGy5jpPk85ZFBg26iok4cU6QNbjZZHf7p1LQ7jabRkxGzNQGVye4RyBijmbgU5EIgOOTLMx
XV6WTKpVCIv4rJ5yFy/UExQqgsEGwQ9/5ln59J7umQGQquRKS1SnEY18jn4picIvXfkxR/YzDg8+
NBkehSQ6G7Ury3DMOBWM3ax3sZw0iVIPkMtbh09/GjiTy3YUogDy8jecXltJAR5nCJaR5CxDlAma
tdsjuLG1m834ZNQIviIcqjA95lInzRYUVjx0w1Q1wxXNOSIiLyealbsTEy1jBNc4nFaOtLKbXugS
wLCPRXnRvBkwNGyCTioZTrdgZKGO0eLPAs1ECTuvtSrdPN5Xdk+xq2H5tENzgiwRehpmiEhSk3qu
3OuTXeEx9+MAN5g6o5ZQXRdPB32iHY8S1szAfd1geLhd47kJdbzCnKCPULsAa+IWVZFwkwEP2BHB
/GMkT8TILYA+ygrqO40iwPZAIBCEECC6aALQF5gCn1AFK+sXz5SlAvAvRK2dQEawDzgYbd0D5qpU
LMEfbCrKIhCICpVThBGkAmC31VMlguYAuWJviBYCAQBgQGUrYoAfMi2IBnkBiSg+cYo8Qv+KpqAL
C4H1BRQ0QqQhDVcNHIbBcNyjZgPWStQkLIoA1gGYKy4ragWsAxAZS0AVCWkJoWlYK0SrBLeBQJWi
UQi8UwIOoQCBnF0cbgXlycIvI3VU0N1eU1FDAksUVzfybMHmtwnjQ54+49IpQpQ9olxtXlwzWnSe
8kyA1UHqboE+Q5PWYP9gWggv4D0QgXJpHzDOJKYhh8eBbLRf9FJ+mwiGM99RkwCHKm199VeFd4ll
KhqGiUsRZ9xrX43E5U7Qa8HDJjE/L4m4+7+BlsZR8cZTIVEetkhoGaczGwTCCVZVK4+TJBCPVi4Q
Noj8RrPA8Ud6x772xKzEn76KXv1gRicFIkQQE3MsFATAkwfhr+weEwYjt5gWiKvwJD0ZMDubXpuH
CG4pGx3m+dE6ImY+qSU5b9TGkIEyguk2NAXJCWcy8RuFvAbpyZDTq6eKyuaNFfjklIrKKcsp3PZi
412WX6Tja6qpEe8CBHe1apmQc21X4y4Tp1dgy2Edfgeli2reJczoxm7JXi1lxths7j5wHIeii+t2
m3RktMV+9XPQKV2SSciIaLYWpb6nypiloSJ1JpOY7zIYQpWjheMoEmYWrg6BzocV9SBbGO9pOrs4
EEOYxaAEm+F9IdoC9yXGdKY0Eiqw9jbDv7hb+A/cd3OXhcLIhPl6eW/fwkAWiILvmMzpXSshQHFx
Gh8qFFRWpIJdRHF2Z6dGFS8gIz1e2o3S6PGGE1jhnVjugC901ddSd16yOcymLnAc6uQDoNWbY+gh
EkH6AWDBoCAvaBBM4Q8FGywFaendGtvLX5JYu0UqKDgwP9CrtiToJ8uw5BX7jYtzJoJhe+YgchW7
ol+RUOKelGQkNp83dVJUCsvMGoGGPFxj191eDIFci2Vie6/aWlo5+bcF29jfkRBn4DQJ4EwwJhdj
UbF8i5JQv5CR60DhRiu1P18CMT3HgalptczhngxtTlAJC1kd8Lhk/xEidaFZImPReTSRQCgiMJdJ
C/LtOF4SOy4oSNCknTYwGE1OULfHvnutAv4DAF7XDE7dl5kmeFa5RkOTJdSIa6UpQJspWVSaS0Zy
U9+xi22IzQCEDSoyyFpoNbKXQwykPzcYvC472oha2WpSmcTQ53Ms6TeSf1M/cT0eBYhwkWM5Mwuq
sHq/l81eGQG2djizm2/g4hMQtOGpGfy+IkTiRxXgeYMnek8UBoI6iBn4oQCg6prMuIeBe8v/IGse
jWZcOZHjMm+XJLy2+yeiwUbSX3s4wt1oLaM2TIY9SIWlsPM6h6iY7ic8gyp9XFXUjHyrDtc6MktD
L367sQPK6kruKnMDJzh2jIKDYQSRoWGBOSYizOa7ch/SSQoCtULaLhpuNCPUFtSAya3cEFwSBTXE
0jCqFerYXMqPFkhjhAjViaX4LRlbb0OjzaJM1o5kJKAE/lvuonKvAt+WC7uE0+jsnH0MSVppG4wF
FelAm3pxmzDkLxiLhodERERV5SUiRW7VNk0LsYxrA2vAfjZdQ+UE+6IRgzDGMHwVIHcJgA9RkfBg
xiPpC4UBcgDYliPUEFuQXB03WUGpKtArfaJvqD/NhzJoqhKTOs2wzS3zeSyJIG2Bd0gjQYivrnmi
WbzwSen0zE13EqFqkJldngPHPqiUWoNxapeH6ZOJLqdiUdw1JhXYN0zJvIooyKJMVQGpaLSz5DRh
uJfDAcQM6EtdZabIMD1cO3CMv2MxsMxm5mu+xhCXGSSU5VEzaBwUeAwK/KDjEi4WDCwHMF48R3l5
G44BzNmr6RHZYJRGC2t+oXjYP8XckU4UJA9cLOvA


--===============2260792616406590455==--