From: Ole John Aske Date: January 18 2011 8:14am Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4132) List-Archive: http://lists.mysql.com/commits/129037 Message-Id: <20110118081413.3D516223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2239423125603918421==" --===============2239423125603918421== 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 4132 Ole John Aske 2011-01-18 Fix for bug##59519 ::set_rec_per_key() assumes ORDER_INDEX to be unique An ORDERED_INDEX - which is non-unique - should not set rec_per_key[]= 1 which is intended for signaling that there will be ~1 row matching each key value. Leaving it at '0' will force the optimizer to use its own heuristic to estimate how mane 'records pr. key' there will be. NOTE: For some unknown reason the optimizer will not use handler::::records_in_range() to estimate this, but will instead really upon its own estimates. @ mysql-test/suite/ndb/r/ndb_alter_table3.result As rec_pr_key[] will no longer indicate an ordered index to be be unique, the ordered indexes indexes 'b' and 'c' in this test will now have an unknown 'Cardinality'. modified: mysql-test/suite/ndb/r/ndb_alter_table3.result mysql-test/suite/ndb/r/ndb_statistics.result mysql-test/suite/ndb/t/ndb_statistics.test sql/ha_ndbcluster.cc === modified file 'mysql-test/suite/ndb/r/ndb_alter_table3.result' --- a/mysql-test/suite/ndb/r/ndb_alter_table3.result 2008-08-12 15:35:47 +0000 +++ b/mysql-test/suite/ndb/r/ndb_alter_table3.result 2011-01-18 08:14:06 +0000 @@ -6,8 +6,8 @@ create index c on t1(c); show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 a A 3 NULL NULL BTREE -t1 1 b 1 b A 3 NULL NULL YES BTREE -t1 1 c 1 c A 3 NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 1 c 1 c A NULL NULL NULL YES BTREE select * from t1 where c = 'two'; a b c 2 two two @@ -15,7 +15,7 @@ alter table t1 drop index c; show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 a A 3 NULL NULL BTREE -t1 1 b 1 b A 3 NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE select * from t1 where c = 'two'; a b c 2 two two === modified file 'mysql-test/suite/ndb/r/ndb_statistics.result' --- a/mysql-test/suite/ndb/r/ndb_statistics.result 2011-01-18 07:49:14 +0000 +++ b/mysql-test/suite/ndb/r/ndb_statistics.result 2011-01-18 08:14:06 +0000 @@ -51,5 +51,11 @@ EXPLAIN SELECT * FROM t10000 WHERE k > 42; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 AS X JOIN t10000 AS Y +ON Y.I=X.I AND Y.J = X.I; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE X ALL I NULL NULL NULL 10000 +1 SIMPLE Y ref J,I I 10 test.X.I,test.X.I 11 Using where DROP TABLE t10,t100,t10000; End of 5.1 tests === modified file 'mysql-test/suite/ndb/t/ndb_statistics.test' --- a/mysql-test/suite/ndb/t/ndb_statistics.test 2011-01-18 07:49:14 +0000 +++ b/mysql-test/suite/ndb/t/ndb_statistics.test 2011-01-18 08:14:06 +0000 @@ -53,6 +53,15 @@ SELECT * FROM t10000 WHERE k < 42; EXPLAIN SELECT * FROM t10000 WHERE k > 42; +# +# Bug #59519 ::set_rec_per_key() assumes ORDER_INDEX to be unique +# + +# 'REF' join of 'Y' should match >1 rows +EXPLAIN +SELECT * FROM t10000 AS X JOIN t10000 AS Y + ON Y.I=X.I AND Y.J = X.I; + DROP TABLE t10,t100,t10000; === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2011-01-18 07:49:14 +0000 +++ b/sql/ha_ndbcluster.cc 2011-01-18 08:14:06 +0000 @@ -961,7 +961,11 @@ void ha_ndbcluster::set_rec_per_key() DBUG_ENTER("ha_ndbcluster::set_rec_per_key"); for (uint i=0 ; i < table_share->keys ; i++) { - table->key_info[i].rec_per_key[table->key_info[i].key_parts-1]= 1; + if (get_index_type(i) != ORDERED_INDEX) + { + KEY* key_info= table->key_info + i; + key_info->rec_per_key[key_info->key_parts-1]= 1; + } } DBUG_VOID_RETURN; } --===============2239423125603918421== 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\ # 5uuk2kns0f01v37d # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0/ # testament_sha1: c9421c54ac52af116b8dfa49c5e880fd7855e149 # timestamp: 2011-01-18 09:14:13 +0100 # base_revision_id: ole.john.aske@stripped\ # s3dcmrs3ev63b1p0 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfqGaewABEDfgFAweP///38/ 3mq////7YAkH3myQAADFawAaFEigVCSKmGpPSeg0geinpHqeahtU8KPKZMnpDTQDQ0ZBxoyZGEYg GE0GATQaBkyaMmQwgMcaMmRhGIBhNBgE0GgZMmjJkMIDCSKaJinhRDDRqbaRMI2gANTARtGp6DRM HGjJkYRiAYTQYBNBoGTJoyZDCAwkkE0NAENE0wTSIzEmTU2k9TAmNGo0Gh6l1Ee3C4VEbbx1De3N nZ4jn2L8Rye5h604rK3B04pP5b6vrufCKhq6XxmaPK+jAMpML+P3tKF4mmmryW/FqLem5e7aJ0Qn f6rrnbfokXfYOEiK9fmxdiW0ey2JDOyv4U0445XZMj/m+4iUUO/Jet3renoRV/0sh+d4L4agMJmZ hhqMQqoT4HLrM9kkcGUei73HeNgseyEFJBySUr6y7tm8GXb5D1b5Z45L+E9I4xyHuJTnxmDEou47 6ssZPIHhVD9bI5Nsk7ZtbU/fbTVTD27r/GxvQFlkHE5jNAtoB1Ec3wR5h933UnwNxvqqAztsnNz9 OB4WwUW206nV440Fkv3rdDl7PJNsrJFb8K2nrlDrbZnpQWGQGsJc1yFr6dHDutIlfcd2wb3ZQxEQ aTj1aojy7GaPXcOZd+E5RxAxhBSLJOx0FUINxW0ivHuKzoLfKFveWWGT/BYe6/57uQu0MyxIK1ba JBabWZl2A+OJLKAdIFd6M/mzmIeyBmTZQHxEQQ8WXuHCQ5PEj6m0YhuBaBqKihUDAnLxp41d4pFN VMEKN5ymlSo2jzM/hGBW6W4FqXoH+8XqNgFMKnve/5fhGgiYUwNHTCIkadg4yOQ/0yiaGAkMaxy0 YSJU79tKj8bXP3V1XcabDQWdgL7xVEShHA7cccKtmcnU3cSRiXVFxmDGBteILVPu5H1xJVh25bCO BrB5vyejcJwKnd8rCDsdRQCVekolRaGeskbTIiah/YSfg0yiY/Y9LYUGwnXAF6R0TQtzfM8DeQJ4 mDzwvmVczgWj4kyk7Zm9V+0E4xuU2HYis23MGs+RtPOCia+ECdVQVjtSVxQ4QdnaUlFBSUA7aYDw zNnDbodNCflbXjrLRPNzHdYh1Zoda6vlz6nabayR2u2K3WXHbrjURlwbeU8aDEjuE7UTkaDiUDae 04kX5jDjMFyOdEyCr1YYgolTOQ9Yu478jXwFN0+/bMqqylPN9tZMcP0uFhJLAyHEjQc43NbnR8ut 0aCc1sc8ewUrrhe8yFEwHLZ01EiDGphXFiRoM4Y866DFNeF+ULxilyVrAnuQnrQj3lI8hJbSp2Ve D8XUk7JPoKyswLBWlBdaVjzMYvtVu9jkUmNWDu3tkVl5mVJ9pCj9ZkYOCByvMZxuzH2RsdW6A9EW Ak8UXSasejXYlXU3BLY99FXMf5y7avXAdMZzDb4MlOaGMJrNVVzJBuLENILMz3jiZkyGIL7wY4Og 9gYIkTHfM5GLCYQwJgoMlM9JU0W3hd0pzxPU+AgR2l6ZaF/woPedki+Z2gwxAYH1gv0NRZBg0y/M /sTC03Ls/YeisMoqPcl41SCgWqhlnYxoiIg5RE4RLwXwbN3NG6UY1zZgc7Hsh7ERAOy60+1ECEpD 5QVgCMTJpkPheMUGDuME5X0cw1Y5aUDASyFCUxPT2UpA4sIV3kUsD2S8kflALNxJsf5Pt7rwXFIL KVKll+qbRvempawVFPXkFvT37h/3CkWn4GqRGA287CAk78Icj8SK5mgrQ4MvW8tyPASovzZUlhad B7hxqNSPyKnmRwTqjlqPhqJFO9rs3Pe5kguWwX53AjTEkJqh1XYPgEiuMQzbsBgtNO+OorGwNZ+e 0wkYCc4wvsFGm8kgfeMgKRM7DQeHzN0Q90DIioFPd+hqqMOyTY2O74w8+x4XHetlEV5pjv3tsrGg 4SCTBvocYm3KIR3+bneegMA1JBbs6oa8U0oJy87tes+zoOSo77nmRPp3OnBRL5HDYZKdWjWfF1nC 91qOKYWvQgpudRxByNiiVQgud4zubxwhtysRKxivqKCUwpQfE0SUCvE526kbgXHryXOnvO45GRAo RKQmRgOLxCrGDYAnxidFikbuXJGGR3nQN+bhbg+WaODchHx/YwjkyqwcQ+keVQOAIrKdqocIjexu 7p5c66I6EIwFVh9Ihn/xSirWJXJRSazBqpdUnvZTcptdx+a2Rlx0ril9BmD5VlaWrIUOAMea77bA eP6bivoV7T1pBPy4lwlUrQRb9Mjaj1fX+kIwiBbiaGYFvmLScCV00BeC8lE6yNGYte2sHbhSCpIH B4yGCWfn8VFLrN+5ycjBXusWafakHsPnsH10DFic+oM1YZFBxY9EvLQqkivAVO43ET5YrThL5/ve Xwte973EUgYT9jawvlKsaKX9/W0F2jnpmcFsUSEziHeaHAK0i6YCOfSRAqSa0hmTMJlXIUmZHnam wrZ5H9al4y725wRiFSUoYH1boIobW9F5hXd95cYHr9p1SDYlCJaOVsE1hx7hzTB1IzjYnEFoe4TZ IOxZlWtgYXc1CMk8LefTD1OlVKqkv5lyVxO4HlHPq4cVlDlwZQGT2S2PEL00OUmIq4iG0N06Iy+Q SNaKNEEcpLNwZzIra4XzuUS35nVx6RWYz9I9OXjxZ4byw6eTx8PMLAFWLvFkVS5FOo2Ge7UxNkaw eUt8MEoBjrSC0V2a+gqj4h3MzW3aC2Go20nVPSMBR1rJKCrSsoT0aiDKoohW9qY+AyrqcuSwD2Ho rkE1zApXHfVNEEqQL5Mn26pvW6CJBV3L42VUKIDUOWLB9T9U0ujLYdSIvRSFTUJYLJsiscJxfmeJ u5+AZ/b42hsJAnGsqcjAiwOwOWVDX0AsrGkE8oq4cW8so8aWvFSqUPVuF8V1WleyXviPiGDIwyqs 7b6TiOz1pxvPIsHoPxpW9616nMrFgZBOYiJWL4pxyovFNCWJR3Fvsain/i7kinChIfUM09g= --===============2239423125603918421==--