From: Ole John Aske Date: December 3 2010 4:55pm Subject: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#57034 List-Archive: http://lists.mysql.com/commits/125966 X-Bug: 57034 Message-Id: <20101203165534.8305C222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2123306110153005963==" --===============2123306110153005963== 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-03 Fix for bug#57034: incorrect OUTER JOIN result when joined on unique key. The value of 'null_value' is not valid until *after* ::store_value() or ::cmp() has been called for the Item object. Fix is to check swap order of ::store_value()/::cmp() and checking of Item::null_value. This pattern is widely used other places inside item_cmpfunc.cc modified: mysql-test/r/join_outer.result mysql-test/t/join_outer.test sql/item_cmpfunc.cc === modified file 'mysql-test/r/join_outer.result' --- a/mysql-test/r/join_outer.result 2010-10-29 08:23:06 +0000 +++ b/mysql-test/r/join_outer.result 2010-12-03 16:55:30 +0000 @@ -1427,4 +1427,24 @@ WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AN GROUP BY t2.f1, t2.f2; f1 f1 f2 DROP TABLE t1,t2; +# +# Bug#57034 incorrect OUTER JOIN result when joined on unique key +# +CREATE TABLE t1 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY); +INSERT INTO t1 VALUES (1,NULL,2), (2,0,0); +CREATE TABLE t2 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY); +INSERT INTO t2 VALUES (1,0,1), (2,0,2); +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 +ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int +WHERE t1.pk=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const col_int_unique col_int_unique 5 const 1 +SELECT * FROM t1 LEFT JOIN t2 +ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int +WHERE t1.pk=1; +pk col_int col_int_unique pk col_int col_int_unique +1 NULL 2 NULL NULL NULL +DROP TABLE t1,t2; End of 5.1 tests === modified file 'mysql-test/t/join_outer.test' --- a/mysql-test/t/join_outer.test 2010-10-29 08:23:06 +0000 +++ b/mysql-test/t/join_outer.test 2010-12-03 16:55:30 +0000 @@ -1010,4 +1010,24 @@ GROUP BY t2.f1, t2.f2; DROP TABLE t1,t2; +--echo # +--echo # Bug#57034 incorrect OUTER JOIN result when joined on unique key +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY); +INSERT INTO t1 VALUES (1,NULL,2), (2,0,0); +CREATE TABLE t2 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY); +INSERT INTO t2 VALUES (1,0,1), (2,0,2); + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 + ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int + WHERE t1.pk=1; + +SELECT * FROM t1 LEFT JOIN t2 + ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int + WHERE t1.pk=1; + +DROP TABLE t1,t2; + --echo End of 5.1 tests === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-09-09 12:48:06 +0000 +++ b/sql/item_cmpfunc.cc 2010-12-03 16:55:30 +0000 @@ -5604,15 +5604,15 @@ longlong Item_equal::val_int() return 0; List_iterator_fast it(fields); Item *item= const_item ? const_item : it++; + eval_item->store_value(item); if ((null_value= item->null_value)) return 0; - eval_item->store_value(item); while ((item_field= it++)) { /* Skip fields of non-const tables. They haven't been read yet */ if (item_field->field->table->const_table) { - if ((null_value= item_field->null_value) || eval_item->cmp(item_field)) + if (eval_item->cmp(item_field) || (null_value= item_field->null_value)) return 0; } } --===============2123306110153005963== 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\ # 1a268pcs6iqca858 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1/ # testament_sha1: a8fea4afd8a945f07cb82d8dfdca40c2e3aef9bb # timestamp: 2010-12-03 17:55:34 +0100 # base_revision_id: georgi.kodinov@stripped\ # l819wohslm0k87fn # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQj52yAAA+ffgFAwWPf//39/ /+C////0YAiO+poB6KA6d2Fs0AAAA8JSAmpqekPUD0m0mgNAZGRkA0eieoD1GgFU0xGjTTBBkMmT AgYjTIaGhpiNDQyCSlG1PJPKPVA0zRBpiAA00GQAAAANMqp+jVG0aJ+pNqaDRkG1NGTBAAGgNAZB wDCMJpiGAQDIAYRpkyYRgIaCSQIAAg0BAAIJkmGSNMI0aG0ylgdKVHH1Y6nTOHPaUkTEszuhW/Ij 2y5bw0uTKLalRyK4a5iGyhl0rYqfs8+pRGuFbmgUpMlmNmqRwfAKwUSBRC+0odrtTgIsgbWr2ayH ONMZRfqn4hrcRFsW/8A7wKX+IlHcB8Tt6trMSQSSw3coZ80I6BDkqpEVl41YwiPc42MPZzfx6sem wGAt5kQ8AgEQzB035p4P7w8gyDiTqQRPyGHBrJEjtvv5eXICVq+cMfGll0w3L8K8fStFpuo/6ZLV IlfMtsEgvlRgS2CkPMWUA8HAwMPLwJhT0XqrVxwZRv7O4jD65HmfHz8fNP62xQ9kl0e1mG4Kx3Ac OEM5yMJvvWcqjtIO52jvANKkVA8O8JEigYcf0GHhesx4jUFYB7T2OwZx8FFxvOT/WLkpz/3Q7ibJ oaXTtSkXhmGQDA+QBMMkck6IUi26KaMy3qgxkgMman0AwEQaQe4vCU0E8ZCekDgahGDkoCGkOaHq RMOnHPqCYeUPiRFFZkxHWTzyeXm1fab1twSuapnjDCxOYOEu2wCIwj13JwPnORjv/vJZQi8AlMnK ebJZtMtp7Wog9MVfBGQ4IQEVvnmq3b5UaaKcbAXqQi1aqnCxNP5LuGnRa4Nj7nmDnDuAcAeBIvBE hHRN2SwiFRtk4bO6CjwYUBGNJIN2BOTXaOFacis6Bj5e8rVXZ7ziYc5RzGEM4wGe4Rxa86jOGvHl RSY+qnEKL8w2MiMxAr2p3Rmp3N19g3y7AyyhOqW5iDJshwQrN7+JPnlOTyyZ4cRsiuj+bp6SzF1R XSs+Ok1AhtvV3Z4CN1yVoVfFTIiTJFBMZn0x6JxxSPu2Ged45zMmKxoobYnFbBI8LsUiT9PHsyNJ HCxwi0y1j3atSMJBIw5CPQ9DjnsnKFg+H366ic2l3KcoluWZWpsUONsdesgQ1le5UHcUkxnYODNk 6pFNZQpYmAZbRFFdiYptHUU3K2cRcXUNYRxYjIiRsfcPg9WNPAnHEikrLqiljEtvaWZUXjQElMJn OoGVslbeIjcIi+YU6skGT0gHRJ7mBegpBWKRPOz5vYznAOPYFd7AcnKCZb1olqDnmuepj6InSG94 vefILQ+IvvB0xgmGYb9hQgPBwdwLIH2EYLCgFCgrLA8galQLB4RGYDDEIhpN/ArCnJHak0HgGUDB BIXgG0KIQC4VAUB/CEwMDgeDgRcH9jIPkleFBcOHLGo+ZsKSIVBal8gwF/If6IBiJgtDUFJOFwTh AFUKcMk8Jz5hrB4pBgmFErKRMBzF2dy5L6gAZL7YCh937OAUpJl+LoAN5iMbuEyDTGIxcfRwYJSk eTACtDEmHvTmFhJ2d8qQFhSPn+JDPNXQQJvVELKMxGPIMOfXtFNAzJOUGyNIrdphidv4n5v5y3SB c+hjmFN4chsRrzIRTuD9T9Bqr8TW4txoIKDPZwBifx9A+ssAnBwOAcDICLwnWYXBESCIXjNrvaKl eNlWSiiA52eToqIunjbISSsnOs0IMPHZYlJkHFXSdvUDOL/1YF2wDY8HxcKAkr8DqrPI/bsKiyds Y6yVBYWUrUr0j0pY8mdgFYtapxwMtxsNPFQJ1/uo37VxspO819E2k7PjVrjfu1YtZ3OOZfq0SMo6 laHomRopoTJO4O10p3LZZxghqhfv3uFfFLYFx6OJ8eupjeBYdWXbNyJSidR1hSGfifNd61q0tWh1 QDx7WpC4wO4mnWufewqE5HkdOXh22HSBfz/KqJ7FCcmJcgUlcVyILYBP0pjWc8a21TdVPBKV78yr 59AOeg8xx3mzETLMTla6dQsEl3dOk14vMRkkFZ4FLmNBU1HszIwbw+JWtuJ7dfkdh1pFlgzFa5eo 6BdRkdJ3p8AwBZe2lNr23jhT6HtxCn01mIPFwNOiGBrTl0iS8SQR95E4fZYsAChIcKEZ8aV1Zg5r 0wsCUAPE7u+l7FgAxJWDx7MLivQoXfI5Uz+mSmmBwUTkbCOwrj4OmZUg9IwdsOscEwulRC16ZhXD JouKopBA1E0VnzLX0AwlaDAFDk5kfL3YeUV425LPZfFWuX6aJHrTINwkv3PMGV3rgASGJ1cLxeFR XG8+gBeJ7LUoKZd0E2ktOrgdgtpJT289AmQU6mTIb3H2CIh1kxbbAJHd6uYegQKso9c9rtRUV5Zb xGrMOyz0IpBmUG5zObRkFMSAkeVZgPDcsqUGa1rVteFinTW5D1XDwvljZMsCZwdG/TCMgetBFWfZ pZPNZmjwYvU5PjYKC/G5I9dy9bGtYm48F4MzM1+tZ7bwcLIDycLufw1BIWLAWqj1BFhjVGO2cSGY A0/yfVVMqPuE1DwCh8P14JmF51C3HYry4TNh3XO33Zj9pNegin44PnZF9PUdRnsgZWnPwSDID6/d I+B5OIlwbtSTBYAawfb2kSGy8XnIiXkBdJMUpVKv2xF2Ht6C+gPgrSvQ3nIsq9CwD8GXOLOBto64 1DDua4AIKIKEASCpaGu0G/xdyRThQkAj52yA --===============2123306110153005963==--