From: Ole John Aske Date: December 3 2010 4:59pm Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3387) Bug#57034 List-Archive: http://lists.mysql.com/commits/125972 X-Bug: 57034 Message-Id: <20101203165948.3843C222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1556583235289140821==" --===============1556583235289140821== 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-spj-scan-scan/ based on revid:ole.john.aske@stripped 3387 Ole John Aske 2010-12-03 SPJ-scan-scan: Cherry picked fix for bug#57034 into SPJ branch. 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-12-01 10:21:04 +0000 +++ b/mysql-test/r/join_outer.result 2010-12-03 16:59:43 +0000 @@ -1481,4 +1481,24 @@ WHERE t3.pk2 = t2.i IS UNKNOWN; I I DROP TABLE t1,t2,t3; +# +# 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-12-01 10:21:04 +0000 +++ b/mysql-test/t/join_outer.test 2010-12-03 16:59:43 +0000 @@ -1066,4 +1066,24 @@ SELECT * FROM DROP TABLE t1,t2,t3; +--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-11-23 07:28:24 +0000 +++ b/sql/item_cmpfunc.cc 2010-12-03 16:59:43 +0000 @@ -5598,15 +5598,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; } } --===============1556583235289140821== 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\ # i2v00tjho8yxg7da # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: 1e3657430e4df353a6915c27662d5378aeb1cc7d # timestamp: 2010-12-03 17:59:48 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: ole.john.aske@stripped\ # vlprbatxthsq9vnh # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQYLSkwAA6zfgBAwWHf//39/ /+C////0YAfvg0AAKAooAUAoVDVNGRNGhoNHqaNBoABoDQBoA0yAJSRoKeJtT01TZTaTQaNMmjCA DRkAAAcZMmhiMTRgEYCYQBgJpo0yNAMcZMmhiMTRgEYCYQBgJpo0yNAMcZMmhiMTRgEYCYQBgJpo 0yNAMJIhAAmgAENNAmgJlNpkGmQTTeoyiUgPi5o2vmmmBkyjmFEkJ5zRFfig7vPXwUV2CIKxiWmC FB3P5ou/z5HWY1mofqzYM71UbZkrpsJ8B9jOiwf3FNpACLINSzVRshLr7K+ymUMte4GIi0r4B1hv u+zGB+Ida+izJmYZmZHV/As2fJNUxF3iwzyKoEUzHizQdzhQDAU7jz+AdoSCYMA/MYIdod4YBsJy YNcx+QxAMhOTnw8toELF4/XDwhyXt0k5wUit1P3Dg00BpHjOEAPAoFD5KBCJKVGPgGMzZoTevqv8 /UtBG9EGSWvzMw38phFvk6MnhVLpOkbyuH7ApCkHDrCRInGHPuGIBcshmCsA8jue9vepbD2npNYj hL1ipP7o8fKc78s+Ws5Lw9zzRoQGdagoAYN6AvZAQCoW9T2yXUqDPZOJMwU+gD4wkTBgThhE+oho EgCkYaYZJQBuoNbn+FNBAoQqFmgDNe5Ii5qFJUk5nsdiMcpAR8DoW7FKxqGgMTOLMfkDmdgXjIQf fQOXFK5G/zZpj5tRKaCEQpfRIz6dlRYtMNyLQksJjwvoltUrgxIpGTNXx3mSY6yc8QyLmWTZE0TV AXDZ0aXDK7lgxvDeDgSLwRJJVm+44EQ4Dayy3foMxPsCmI5TqYWBMcC+47Cw5+4tPcV7CDCMaYuz mGIjaSfGgmJ0y6NTiJE5iWRTlmg6qo2xmlqm1a/RhoPUcMwWprDGtbmMKs4jJEM0XTzvoOrdA1aK 4kTaFjUFP+9uW+ykxZaankqTUfVsaFgX8bEqggdosbCk+KmIUUlAwjouO2siRniQ2lMSEHQ9pdUR DJqw1kCoRqJY00HLJmNxKvLaihJjAcxGGeVeBQeY2GGSebWxmLFF8CzMZGDdaOoc9Q5eKwuOJCci LwgsvkAudTqghY/Ousr2FHXt0Eozme6u2dyKnM4h52aBG4jG0kIuzTExeUFzGgptHHOhbLYUhWxR fkytNe4TslPBDTtIKBT0M8vYzuA54hqyMBwcizDK5aEtQcv1XDtYnQHsF7DyC0PgL3g8xgmTM37i jEgDg/wCBQeQVA4fmDDBS4H6hE/oHCYJz+wPXWFgf4FgqC4+QqQnALw+Y6SvE4dwqwiFwqQpDyBM DA4QBwRcHxMQ+QsApLxxzLKNPwqJSEJgYJ9BQJ9h6CwcIoGBSEhJfUGAVBMCkKkMxAP9DSDikGUY VhaVCYDgjlzXBesAGS/CIo/n+7gKUx3H6MlID4kj76G/H3n2c8+62w/ZkBTSVeoujIX8H0ZYqqlS GxMliDuQC0jU0d5AZKCMzijT1GgkNKqRqRnZCV1vj4uIzFRZqo6voNyBT7tDm1WQu/TIYl/yOLny KNFJaGc3rXlUDQZDRJQcZAeX2XR6yoDWDg4DgyA2QCo0GUsVegtgD57BlsxjCg2aimViNBmqRpIz JTiNpcVF48B+IWz/SJrUjGaHzYFAGIMOhBw7uPaTw7T9tDd5gWFlK7lUgJsW72HvCoW/aX7i75qR PXq2HM0U9T1tKmh+OzB2bm5l3nS4GFhWG1MiIs2p9dE++rZMhqUfzzcUUsqJXz6BSO0jtNLGcORw NwwdJWGTtPquxZQmNJwzRXcUqwmUvERp6Et3DJyXZ5itdAGBe39nvoJHgIJ1/QiyR0LSAyzkBHHL U1sp5tiTYlP17Agg6CJ2G7ETG0TldYko9nHVLETRAkLhaEjkMrh+bBRKmFpTd1RDEgE0wpRKV4yM W/INuIBdgyAi/ppGy+jO4tJ3U5S8HF3mjjG84OmElzJHrIrp9vmWdAUIBxRlhT0+Cs1p0ZC6ZcOj TXcAMXDp0bVOuUjdvpo781FAOqaSVhLiWS63KlUQQDzl9pKITi6FiokBDRfKq5CDA3GXgY0hSJYA yAZMyPTzgudWVZdGmAiv9e9Ap5hJahf9O0GT98QDxGNheLugFhV9kBgk6idME2jp2HIVPp0EUF2R kyPaRDuKqoKJNP2eO4POExywlyq7VfDMXHK5Z/WdMA52+c3iDuNLbe1BXIkkjzFCsDhd0tSoFwtp TcfCcY4kSbE2BliEjaKecKsqL2xWFebs1W7wsuzwYMFOT4Cgvx1ID02o9PEzHeu9mZmu2Js6x07A cWUXi6HyhEWDAWnJRYYyx0bxIZgD9z0UyU/3CaqABRCOGsDeLV1K8uE3EoXXdn2ZjmIhqOZlxSZZ q3pZGPUWLqNGvRQXHHsEGdeqY6ChhwuQUkwBbH7O6QE4mBZOYRC8SI0mRuSATbR7l7ORiWl95rNu 8tr6jiIuSsXvdbw3DxBuK4HvEGvE4NUTxU/ur/i7kinChIAwWlJg --===============1556583235289140821==--