From: Ole John Aske Date: January 18 2011 11:49am Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4134) List-Archive: http://lists.mysql.com/commits/129071 Message-Id: <20110118114910.03801223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0795678356086805406==" --===============0795678356086805406== 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:jonas@stripped 4134 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 11:49:03 +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 11:49:03 +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 11:49:03 +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 11:49:03 +0000 @@ -959,9 +959,35 @@ Ndb *ha_ndbcluster::get_ndb(THD *thd) void ha_ndbcluster::set_rec_per_key() { DBUG_ENTER("ha_ndbcluster::set_rec_per_key"); + /* + Set up the 'rec_per_key[]' for keys which we have good knowledge + about the distribution. 'rec_per_key[]' is init'ed to '0' by + open_binary_frm(), which is interpreted as 'unknown' by optimizer. + -> Not setting 'rec_per_key[]' will force the optimizer to use + its own heuristic to estimate 'records pr. 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; + switch (get_index_type(i)) + { + case UNIQUE_ORDERED_INDEX: + case PRIMARY_KEY_ORDERED_INDEX: + case UNIQUE_INDEX: + case PRIMARY_KEY_INDEX: + { + // Index is unique when all 'key_parts' are specified, + // else distribution is unknown and not specified here. + KEY* key_info= table->key_info + i; + key_info->rec_per_key[key_info->key_parts-1]= 1; + break; + } + case ORDERED_INDEX: + // 'Records pr. key' are unknown for non-unique indexes. + // (May change when we get better index statistics.) + break; + default: + DBUG_ASSERT(false); + } } DBUG_VOID_RETURN; } --===============0795678356086805406== 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\ # t8faxnys8vam34y3 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0/ # testament_sha1: 0e5562d0d48aed4850755eb995ca463b2bf9a13f # timestamp: 2011-01-18 12:49:09 +0100 # base_revision_id: jonas@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSVFzh8ABLNfgFAwWP///3+/ /mq////7YAqCur7uM5Dqii3LiLQejroKNMt6wbsCSSYkypsaqftE2mSSemzU0wECn6TETNQ9RpoD TaQSk1KeMk8SnmQ1JMmJpkHqNAAGgAANADVJ5NR6amQADQAAA0NBoAAA0AEkIRPSKfpT01MTTI08 kGjT0j1NAND0jRpoAaaHGRpkxNBkyYTTIGQ0BoDTJoYATQGEkgQAAgm1A0EYqPZTaUbQn6oHqep4 ieoNHqUIgI97WMJWkGwniN7tW1nPO78F/EI/Bh2pOFMpxycKP0v+Pyn8n0tqyO9hphn0bcoyl5Hc /p872knxF67tdouz8y9ML803RGA00uqSSEB26ye7iyfpVgQ/JXi3X2+/fKXvEckiC3KYLgQsxjGy +ycV+Dm2NxhixATpfvXUICOBE+PBz8fQ4rcuf+bOX1OIS8eLzKL8u2TrDpDQDCZmYYYwfMWF/q73 BuPnehN2U87vvO6N6maUoYo7qKWmPUZfIcQY+zoNldRPvyXJMowdhUMbD8SMDucxn1Hr9ijaSf7p uGuJow4FhkHRZGLCfmtgSmcb2YnytSIwMT3C7B1GtnPwrsVuv0ObwC22UR5XWr3OkpTA5TRzfBHW PLvScfRj8TkNatQGXO5xFYLVe/gOUvKx7LVUdjkrFrpWRnu5XVm2av6uuf29Jvdh4iKwfXe1G/qD 4Td1zkQQZAcAubtk3HZxWY+Jv18IyRcTAUslVIfST2s9t2vL+araO+UvMVvpn4cInRlZo9+MoWDC bhwgYgtPdfceBluPbAqyC12mc8F65aQUbvxXeWlpo9neEIKHms/2YzV+IucNCoAqVtiZMhKwnZmX u6Q5vgDBKeYo0gHdArvRp45tUIfscgdgbKSIoIkiCgyq3XtHEh1NJG7tObNATLEVJjI8vsrvqWAt eKk3WfAlYrsKgyDcSaRV6ay8h38Be40RUynjQ973w+3pKYkW0QM3VhESM+sqOkd4zSNC8SMRloCY SJp3yXyqlVsndt5Jt7cCfcs/5ivIkgtBcMK6cNmWrqL5iYxJyoLC43PEFa6O7M9Ik2Ya7DJ5oFRc RHcII3qff9qlm6d+ZIEtz4FwaGlKENsskSMhF3DU55bltFKkTQvoXxOUfoYnEX7MR6PE3gqrCASM rXHswrKk7pNDAE+BcNg8gXG9IwQvTNg1s0ukUP8NNiZxVM96shdcmoeERiORtTNTkSAQhvHrl6YH jXGC5C1PBbF+Tjq4IkUmEfty9RuJAuWRc9rd0ORWSmQy2nFP5JuVpSGwH6J2pA2Gl/AokYH4mpa/ iTmRr2xUjnONF5uVvLxgsEPobRNmmZvo6y3jysaikpSbDKGoeKXa3iZWhgSMaiccMYw+3ZWRKKFo 6YgDHXdZeojaWjiJvYkPahNgwsiYbItPUuoYp2Y2XQZhgtZJNOthK8vHqSkUb4KwpvmwdecsH3b9 oLiCxxv0upMVZMOBcU8ckywwIilbC5JvB4thVaTlbFd3G4uJDkeUTNMLdKNf6X3UcKG7kNa+eRsp GOeN6yUMQibIpApvY05IjHxJTzNwt9rT5n70UeIxjji5AcmMjDdYMlMWdoWz53K9zKcA6V8hpT8h g+1NkksgzDAw0V580GPRynq6BOBJLEclXJgcxUGY9yYDEftf4vuvMbQh9/zc5KsrPqfqaAZFMxO5 ZxMUL/oXFtTJGFZzB82HDERgIkawVfzMB0GsGV+XmcCBUWCuDkXDE+hDEYgycFptxH/T9TKv8F/4 bMFgUFbhjh9RoISk5fDhCeo3MLpRwdOehRzeZh2+CNGDvJ+EHN95B7QJAOy9yc1AWlthbz2qAJiZ NgQ/1eYipzlTk4xpHKQajiVq+JsGo5MNxel3KmT5dUAcXqbMglA0L+Z+X3I+SFILZE7Gf5M+zuiX IU7mVSM4Beep52XkmIt8U0zXUqJSZwLs1aAo6PjUSarmvPh8TZOSeRHiXE4jltS1qDlYPf6ESBW4 wPckoFd2Li7QhssOzI9RkfFiZ5tJwKDtRbAnOlbjqKj6NaSW7gU84xi29t8MCMGAMVqvlgCKpgmS akdQ54uqYfTCO11gWnD1R0InrmNo81LpiawT3ltlYo8mVJOghqMgKFq7XLUelSYd7+1EeA+Y2EkY BaNyCECFMdNoLEqPCFrHcpBOxOKcwDZkyHIYB3KCiiRfCax5B1tEaxoyeAE7Bd00bTZEzM5BKNx+ 0LwzAK660NWKhKCctzsrg+HM4DQ83Kebo4YRO5+ZNd2V9T6qZlSZRric240WxcZmbj9XWc+/Lebo 4I6UwncNQdazvbYk6LQmViTLNjGdtqCBDYgwow28buR23VmSglYlSg+pvEiBXkceVG3ccI+ocQf4 fFzwTxw9RSurSXzGjKVuPQNmYGyhnzCcBJ1WDta1xR2uteeCBqNuRdS6HPoBo3QI/T8yUI5vcKjB OHr7x4Sgc6ApJ+RSckRuY4ZVY8y6amJiW4HyglThvENoQnRgUwErgIiGswaifr6koEGCtyrai7n9 ds4Z8+6qiTML2mYp4sVMNUloTpYEZsuYHnl24f1uB4P6tS3qLazzAK8158xeUrsGVyAu91AcAPL0 4Ph2wi9kqMJJUistXN1laCk6UqQpSe5cjQKrfLaDm5AD1gDgeMhgmyz46hIDrrfsnc8GRgr3WLRP a1IPaRBd1hCqQxYDnyDNVmRI52Dvm9nWt5TOirBUeRwIn2DFbN83f8IQReRse974QgEgBkQ09htc GhX2W1GJpffS0luDwmZwu5qJWFXSHDq6N4osixLEXKSu9pqT2IYZMwmXs3gpGvBmF2MavV5zA73r KfoMvp5IDoWpqhgd52gjRs+EjqRkplftyUaWoCkOzgc4BkSeQKAcqHpr/mxVzmuR10HKdmGHFacP Vhm706pByrIT2MDC0DSRopi6KTddWPtdPSpsKqAv7SsRcV3CeS7Bj1MOVw1EyzMpDKGEbMJI2KOF jBNxlIvJmlPpYpv2GQ1EPCg+XbchsG3kKGw5E4ZJrq7SjWxeqYK/bTZC7gITqNG60oZbYYVnXDCs k7TO6lVZlkLAVVTP0FGZtkjvcNlwzYZlaxoDylvzxSgGVQBarmWa+4oj4h1szWQLc0HA0N9B2J6R NeKWxYoIhUk4skoIzI8XKomiVqiD3vjtGkxVS5dKsD3HfiCosoCxcQKVz61WIgBbQgaZk+emqrfF NBD5wp61+llEyiA3cpnixYPe/SxB1MG47Av3EyVViqEewciYJNRZpBFEnUxmORyEeHYJn6uxAMBh oOUmywEBha2JKBynVyZdXP4DSrZc9wK6ca0DopdvLYrEO9dReKswTJXMB+GUiEkHtZGOdNnNepzo HZ1py1PI0BWEEH9qFzvVstjtIB1MQO8E4TmSwOkinl4LYgYcdxoV6IOlRxW1jwDh/8XckU4UJAlR c4fA --===============0795678356086805406==--