From: Ole John Aske Date: November 15 2010 2:27pm Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3366) List-Archive: http://lists.mysql.com/commits/123915 Message-Id: <20101115142724.7F12C222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============5384479051237437291==" --===============5384479051237437291== 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 3366 Ole John Aske 2010-11-15 spj-svs: Disable 'REF_OR_NULL' as pushable join. - See MTR testcase. modified: mysql-test/suite/ndb/r/ndb_join_pushdown.result mysql-test/suite/ndb/t/ndb_join_pushdown.test sql/abstract_query_plan.cc === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-11-11 13:07:41 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-11-15 14:27:20 +0000 @@ -4497,6 +4497,28 @@ pk1 pk2 pk1 pk2 3 6 6 9 1 3 3 6 drop table t; +create table t (k int, uq int, unique key ix1 (uq)) engine = ndb; +insert into t values (1,3), (3,NULL), (6,9), (9,1); +explain extended +select straight_join * from t as a join t as b +on a.uq=b.uq or b.uq is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE a ALL ix1 NULL NULL NULL 4 50.00 +1 SIMPLE b ref_or_null ix1 ix1 5 test.a.uq 2 100.00 Using where +Warnings: +Note 1644 Table 'b' is not pushable: This table access method can not be pushed. +Note 1003 select straight_join `test`.`a`.`k` AS `k`,`test`.`a`.`uq` AS `uq`,`test`.`b`.`k` AS `k`,`test`.`b`.`uq` AS `uq` from `test`.`t` `a` join `test`.`t` `b` where ((`test`.`b`.`uq` = `test`.`a`.`uq`) or isnull(`test`.`b`.`uq`)) +select straight_join * from t as a join t as b +on a.uq=b.uq or b.uq is null; +k uq k uq +1 3 1 3 +1 3 3 NULL +3 NULL 3 NULL +6 9 3 NULL +6 9 6 9 +9 1 3 NULL +9 1 9 1 +drop table t; create table tc( a varchar(10) not null, b varchar(10), @@ -4589,7 +4611,7 @@ LOCAL+REMOTE READS_SENT drop table spj_counts_at_startup; drop table spj_counts_at_end; scan_count -2021 +2029 pruned_scan_count 8 sorted_scan_count === modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test' --- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2010-11-11 13:07:41 +0000 +++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2010-11-15 14:27:20 +0000 @@ -2897,6 +2897,25 @@ select * from t as t1 join t as t2 drop table t; ####### +# Testcase using 'REF_OR_NULL' +# 'ref_or_null' contains elements of left outer join wo/ being identical. +# +create table t (k int, uq int, unique key ix1 (uq)) engine = ndb; +insert into t values (1,3), (3,NULL), (6,9), (9,1); + +# Currently we do not handle 'ref_or_null' correctly. +# It is therefore disabled as pushable +explain extended +select straight_join * from t as a join t as b + on a.uq=b.uq or b.uq is null; + +--sorted_result +select straight_join * from t as a join t as b + on a.uq=b.uq or b.uq is null; + +drop table t; + +####### # Test of varchar query parameteres. ####### === modified file 'sql/abstract_query_plan.cc' --- a/sql/abstract_query_plan.cc 2010-11-11 13:19:27 +0000 +++ b/sql/abstract_query_plan.cc 2010-11-15 14:27:20 +0000 @@ -327,7 +327,6 @@ namespace AQP break; case JT_REF: - case JT_REF_OR_NULL: { DBUG_ASSERT(join_tab->ref.key >= 0); DBUG_ASSERT(join_tab->ref.key < MAX_KEY); --===============5384479051237437291== 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\ # wteg640w78jcdab2 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: 505947e8241c176b9596ae4423bd87fd0470c983 # timestamp: 2010-11-15 15:27:24 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: ole.john.aske@stripped\ # 15aenuh06d0vqv2r # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWf4CH6EAA6RfgCgwWPf//383 3oD////wYAga+82qjT0FGKsgDoAAAJSI0FPJ6obUep6jaj1PE01NpqBoAaADTQCSpT9NFD0wm0Ke p6TyQAD1AAeoNAAA1TIPVAGjRkDCaABkAAaaNGmgCU0piSepkwjTRkaMg00AZGgGjQNGgcZMmmmE yMgYEYmjBGEGjTAAIJJCaGiNMKeIExGQTJ6gmkep6mamahobKKoErWWMFtwiIJ4lEQczCJ66GzeR BM4BgU3CPKSkM5L7Qi8PHA1lSvYtRzJkuxKGTL+NpN4ZMugtqpXrLJpN+mOdH1LC97ub5eeVaJ2R psclTNbapuMzRoI8mJ2hSZbP+Nq3nexOZqxBF8wjV6BF8LOBszWWSSTJND3YnhPrb0buw8JE1x2n k8U0Vbk8q3shBTsbSgczzRMpLq+XmLQOEMgE9jA0g7nYweHqCAcTNxtzhFwxXD46RuAttJT7AfUu tnJYGr1gLyNAX6aqVEfU8HIcYhIJDg8o0nrAI7aDITDLJFfmVQj5xW6oa7EvbbiEdUIll6DDdT4l HAS/N7kdeKyOR1KTSYlATixCBAyJyFH2z/sotfIkocxjAJDHwYBprlGEuPvU7Xz0vMgiMrBGmiel RmLmMwDRMIL0gGF6cQGC/Yu9TaJ1I3NERiyV2dIiyPxjMTRAgODN44QMARE0nvWkj4sa1WUKcnCY T1oPQqJoeqcZKcRBcTrNxJxXW1WIxomiIqSbkMBQWeWi0VRDocAx6jcS32vVk6cp1+mIOQmfrIuQ GJMUycZsVtHhLceE3pHZ2otEWhsMYn8lk522zCDcmM2Lq9d9fhnZpbwNElU8ysMVeSoyDnDI5xiU giagSNVpTCNgenHSufGtOKd4jXNfKLGyodlAXx8DUuZhR5++hjDIxYNLbjmcyBL6GdTr05rszp24 EiBFS1E206T8IcXVnHfgdnC5gZ5B5TYTLexERYOEOYUBmYYY2jVmJweSGLQqLqnze7fI4ls8rahl dNAIjITStxKToxYZkG6C2uvpVS2UmA87jXu18S+6nRwMS68225uHFxeIndRDGywtK60QJUtIlcRj m32bJoNkpxlz4wRd65lg+l5lSylsfquPuImh2diNhQ4oCRyeaqCqRPYrZylVLTIUK6tE3jzkeoxc ZmoRyOS01uZ04zF3idxpwFKafhowKrziMX8rJnbTTODVs2jWZFJmUKR6FFeiwEVSVWoo1HC2maMz CnUQlSGVJdF6qk6vithaCNWmkrCd3S52cMIWsjXpMBdoYc02TJLxmMRDsJRO4kSYbOfgeqA+CNID 9MBUHOPOaMpoKwpJRc4/kE6Vx/4wWzcB2iel8mPmvVPURRUeHpigBBXsghxdMHIJQZoQcQ4rHmyQ yEK4ljZnD0zDYanA9g+BWj2jL8aiv3kUdTE/YPn8KaRDoqtdjHgeZw3mPNQCJJ3utZ76RykTB3fm Mqa+QKGVkWJB/SCHyxAc5MPVGFsIYX5weoMyyCMCjBtojiiwgStGiUpGtvr8XJxz6pFE9h6n0tNA /9O7v03mvyHERrr+Z3kjtx/URcZ/tjtr6UO7DwHoCFSAw1GwrRFMqWXsbYm7FHDbPYiCMCJOQ9pF J+BeImETGsk8n0xPt4hbGZ3kKwou2FBj5sDe6ofc0P2cG4vcFJlucTkEBBgRhNDBjsIcDs/faUFZ L3L1V6A53taw6ISFAka/C08B04nnQdhHgU/4lzqk212pgqoEQv8jXda0v4cUbRd1Q8cVhg3is3I2 qM2x2QzSXHgju21ToTfB1Y9MLmPSsNaJrueWs8DuO+Ow+R5GY86dxFTnnAJYhv1/9WK7F7+7nogV shznr18yAitEqGoPGwKupqTbF1og1QjVZirrLKhAcgykY/IjJKKw7fDRB10Zfqla6NAZI3mIiGQ1 wUMRGBeItRXZqayeBQL+nbnCcU5FG1vvdcJ/vN57OSgSXr3nfeeYjPqgN6KUmFfVyyLHVGZ8Q1CC 2ioapLlSbmGLw9Cq8KhQcDBgix/qGf1dyTIrQHJAPT2TRE1rtWiQYslPYaZjMqQjl36n81FaO6pW Nnzo6UThT1dWVSRzYpBihkOc5h6AbaKCIC0KUzxPPn8hmMiRGI1DgeNVTiSV7/lFb2CgKWHDJmQV KFAQXhRhjkbr892NFX46pAaxazWDJ3pOisQUHAuF+UJgmR1xX3QF4uwJ8ucFe7qvqbq9gtLyc1yU cGTIeGoj0idS5afPuplUoiaCS1MkWkgp/MyKFxU1iOXSZyeFLIBkwmRyeP6nithnM/A/sPqCZkVU YcOwJ5q12cKdD9XaifeZWyzrHN1HA5eHa40gaM9iLIDjtKfLdGYnqPMqkZBqM9IwRwVwiTfasUF+ 9SA0ox9Yy801bYrTadjAEwuS3osIEKOh7YmpgKTkfbg/miYnzsRpdpLjROaJ09FK8RUDvgOQbaET vWgpOhTUW9oi+Bv230aXFiCxJzXf102W2ZHxfgxgJlMWvAePZGFmOBhv70XlCkRqeBrgYDY1jxwH 5DBkl+PERNj4bvS1THEsXLvOAuH0V7vL2wESVZYJzcTWI1UfI2VHBFtBYkfxfwfyY4OZsDmQerDP oQyWi6iwJqP/i7kinChIfwEP0IA= --===============5384479051237437291==--