From: Ole John Aske Date: January 14 2011 11:14am Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4108) Bug#58422 List-Archive: http://lists.mysql.com/commits/128721 X-Bug: 58422 Message-Id: <20110114111432.18A67223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1676365581077977756==" --===============1676365581077977756== 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:ole.john.aske@stripped 4108 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: 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 thatresultset will be empty, and 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 partial *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 2011-01-14 11:14:26 +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 2011-01-14 11:14:26 +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 2011-01-14 10:42:53 +0000 +++ b/sql/sql_select.cc 2011-01-14 11:14:26 +0000 @@ -11788,7 +11788,11 @@ 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; +#ifndef MCP_BUG58422 + if (!table->pos_in_table_list->outer_join || error > 0) +#else if (!table->maybe_null || error > 0) +#endif DBUG_RETURN(error); } } @@ -11809,7 +11813,11 @@ 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; +#ifndef MCP_BUG58422 + if (!table->pos_in_table_list->outer_join || error > 0) +#else if (!table->maybe_null || error > 0) +#endif DBUG_RETURN(error); } } --===============1676365581077977756== 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\ # esdkcnm8d0ovau6b # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0/ # testament_sha1: 86757df87668b11c80febcbf812a6359ac0de660 # timestamp: 2011-01-14 12:14:31 +0100 # base_revision_id: ole.john.aske@stripped\ # ojmo06ia9rjdvqkp # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQIqQtoABVbfgFAwePf//3// 3+C////1YAs+++btnz3vZe2gA+muAHe56tunQp6fR0UkD4ZKaekypjU9T1NHpNpA2poeUaNA0DTQ AyAAEpKbKjep6TJPaaUZNqGgaBoAMhoADQxAGSNNTCJAAaGgAAA0AAAAAEiSaaINKn5PU09FHqek yn6YUJoYCAyDIwQxBFSaNCZCeU0NQ02poxNNA0GjIZGnpNMgNpqBJETQARoyNCZCU9iNMlPTSeUP UGaaT0gDEnGcgD+XzIm85HoQndD0DzvU53OxG0d0NnJCDMuHK7r76a2Z52Py1XfNV9x8BZir5zF5 0FgKIqxbqc1sys3E8YnfSn7qgcGW5bwss1QnJ5hcumoGWitFS5ts2NtxoFXm7KgWdpcS/GuRV3DB 5lvx7bY2T1SdvL+qF2oCHIPSbevKcdyQIWCJIKSrWs2I66QDGAoAom1OsA5X++8chnKIhCIiGoSX gaVTWdLKwbHwcT6r6sQK0rR1UiTsiuUvrBWd4W0u4lYb45hyxArG0dgPI/UE2Qg4vuOAcG0e4cvg CgYBYDcwHVM5APOF+wQxNY4M1m89bjp7N2Y3Dx5M1wIOhW5dI2VNGBb9EHH8quXHWAtfIj5I7ZtO WdYslqHdxi0UkKTlKtqUNH1QhWEMRMsHRAZ9KPkreIsA0BOOJOAwwE6nqWT1ihhF7WeystoJiY2y vlVsXyxUvytQNhNd72Nm2Qot9A4KtWy3DWGDcig3VO+mCkiGkuz7mxtrRAsnaNsHRKvN0qEUa1mG ySSxHA0nxKBhhhjxz9E4z5G2MYjP4i4qsKwfr4BcCy2DqnLoB2ewT6WhaFYOObA6iwP+AHFekgcT 5B4jziL9dcPm4G/OFX4cgwtpIgvLYLtPXiLPRLkcTrvvVqdcsV7e8F4RVmAbgZDmapSeBw5a4Uis YHZDMQYqZOExmF+yRRCRgKInEmSThppMqFjJwDoM1hXfMZBHZIULZmJ2jLJ0uWYjqT7RawGExOxV Q7kQ9oegHKhgXlQBQJfBxYBeffxMPVvnEpxGjiVQjQp1oKaqbKKbN80LDZWW+5DALCB7Lb1C0GpU 5Pc8N46LdjhLsuP2HtmMFqxNDFXGrO8iKGZlWxuEqWNA0BxJbqtQIwLjmwc+I09mMwfawjCvIJjV RWSWcRRBMaGJI/63UVTl8si7fmGYYhMHVe5bQXjU1yNJ1ORI3VRG6G1Q3+qwnpcyMSvJhBURIteQ Q9wTCIUj+AxffYNeWnS0zM7aQeNDeis0C4nMNUbjJfmTfnYW3Zj2qD6yygJqcHDmu18Q6asD8j9y cuPjNbvI7Y1nAnr8Td+lRWIajdaZldETYqyHXTNSem8gbt+VSKkR3oL2wmsOEjSHHNzYFpL2kiG9 uRlAhtCAw83op4ywT3B7QYqZw5rUIhpCKrRObk5S11eRVC0GcgzDQxMIl6IMRcmubDbSZCJVsUk0 zNwHoMSu8i7FpOPwnIlFxKRdJqjEYwCueoHehqGuZX5HXXATaJyQ2VxMKCpRx3WeFqP1JJhIWoF5 AyXPCxhjzWMBhN4BwBen8FSfQFy7A0KUl7wPaPYHOOQfgNlLkYMDqA0nHKRSSGYIg8sbHmHCOdiM AYGgJhwxAcQOAZA0gQf4rOFQVjoH8NQxHCDAoKx7VaMI1vSDqjBXkGgCsIAGcbXmizkBAuFOpDhA 1AZxoHUFLQjaDMOgYkGAwEIgEAYBBC4CkZgW1EoyB2qN4MRLBgEwwV7mA0j3WcdBaKEBuHSDpIhO GELhmDKFw4wbwvcYVg3BaDQDiAsHVViEQrkGsGsUCmBWSMlxDSMBvG83zygnBBzPOdCcMUIMB3qJ EuadnQnOuZqcZIDiOIr48NHrDxvyEYgVh0F35bNhESWZEwDUiWJI9Ri8Bi0YS4aoEcObn+mHHKSH AP8ySrEtC7A+HOBHa5TozyIiHwQCc0JyPzPp933nI21QJfUkUGo6dqopOIyXRMDAyXvn6DdSD5Mt cWNinLT6GPpPnZQcGibSguTnRbTG0k9C9kYi7dPGt4bikVRDSU/5EbSlyA0QHQ/UUB+BUCKoPWmS qi7tLWn6CKtIlHp6FCKOJQjnGYolkOfedtYUJ5CCYSVwi3UYuPtCguZUGcG24zLcIFiiO5U+iI3m VrMAHY2l7gSA6iSa0wMRJ1V9zbF1x4q1ZJLgV5OPeK0TKxTbTFl+AOOEwkx9c8wqWqWkNqi5q2VE aUjCGgtJi0SaIyGe5T8lQmHwBMRQR7wO4GEzC3rQJDYhLzTitIa2IsmfPnEU/bdaf54K4NW8R11C bARSA13a6X+p50uSCjNVHcxBAQ9otm7ieGJcuotUjERLgbw6zYbZkwtMwam4f3XcnVhRIaRpMpmM Ohin3T9Cg6DAzb6h6OLC66NdXfyXllgWj9YHB5CNbnWiERhfc3sOwAkFECcwibD+iDgI3iqwBiZw 6whm1iTJ91C296TZkuh/4tcO/wPC3E3FSDxMlYS3WGA4GBDgKyM1xmTtoTG3692yysyCjrGyjGM0 CyHfwzrDnmVk/SVqNB5zByJjWHJJccNyNQNqKO9B3MJLrHauALQQ4Bj1ipXokOL15mIVFPcd+aZl Mi8OskM+OcKSQTlpQUOpUvLBqYJYrsKxGI+IpBYKAVfrAkXsq1myhrpICwMHC6V3O+A8sX90gCoi Ie7ivQqQ7NRmbriY0erV4B6756wr+aumQKXOX8HZggdeUUzVM6ELaCBUNSFVDElCHgYczBViYOLA pHJsiEAJrJ8SfeIkVeH2OrcMpHE5FSUC68LmgyZkcFXw4Ey9e3lmDJGwwRMaqhfXkkLs3hyD/Z7w ZP5WJBgdTCfqsdK0WuDD9ElrFAJkXEkyQ0rTW12+fEeUwXkEZfgw1dx9pBut0tNDHIh/AXIMFfed 69gvIBdwmBRO2ytqstpFRpV0O3CYjSTIFn6tO0/iX+4M0jxPVrlYbgY0YHTGtgLFBCNZ5PMYriLa tIU1IHDeq/GCsndE5V9l/yC5Ya/Rlsos67+hO1FmRaJOIY1lXdUllnxcQT6YFEZRmfuKC/pz5beZ jUkdDVc21eOwZjatK1eDORYkoWpNpiKK1k/42pL21o9+YXrzZmZqOCHcc2VpOtNsykQMbjOKQh8J 5dJrvpskpLFVKtzqqwTdZZKCQy+cVFEH6C0i6BBxBMP9P+AIBcsziCJWq/iJlWuSkFx+oycJtxnR Csica4cz9qi6KfBZDiatOIPhRu3zOn4l1tc2JUtzCmaQnUdSrhVaDohZocEEiDpmQsltUC+cR0qP L7z2XGN6N2sRRu2ELIbHjQlDzpMJphEFtKvtTi1msPXP5U8DR4sj2jCpgENfhfdz20RiPmMl+K7x 1E3AMxzk5BkiDA3m5TJaLYRFBeaUxKVgvIqjcUaaGZ+hMf8XckU4UJACKkLa --===============1676365581077977756==--