From: Ole John Aske Date: January 18 2011 7:49am Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4131) Bug#59517 List-Archive: http://lists.mysql.com/commits/129034 X-Bug: 59517 Message-Id: <20110118074920.9C39F223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8454943345632689134==" --===============8454943345632689134== 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 4131 Ole John Aske 2011-01-18 Fix for bug#59517: 'Incorrect detection of single row access in ha_ndbcluster::records_in_range()' The logic for detecting a range being an excact match on a single row has been corrected. Added a new MTR test 'ndb_statistics' intended for testing statistics returned from ha_ndbcluster @ mysql-test/suite/ndb/r/ndb_index_unique.result The condition 'a < 9' was previously incorrectly detected as an excact match on pk(a) return 1 row. @ mysql-test/suite/ndb/r/ndb_read_multi_range.result The condition 't2.pk != 6' was previously detected as two exact ranges which produced the estimate '2 rows' @ mysql-test/suite/ndb/t/ndb_statistics.test Added a new MTR test which is intended for testing of statistics returned from ha_ndbcluster. added: mysql-test/suite/ndb/r/ndb_statistics.result mysql-test/suite/ndb/t/ndb_statistics.test modified: mysql-test/suite/ndb/r/ndb_index_unique.result mysql-test/suite/ndb/r/ndb_read_multi_range.result sql/ha_ndbcluster.cc === modified file 'mysql-test/suite/ndb/r/ndb_index_unique.result' --- a/mysql-test/suite/ndb/r/ndb_index_unique.result 2011-01-17 12:31:29 +0000 +++ b/mysql-test/suite/ndb/r/ndb_index_unique.result 2011-01-18 07:49:14 +0000 @@ -185,7 +185,7 @@ set @old_ecpd = @@session.engine_conditi set engine_condition_pushdown = true; explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY,b PRIMARY 4 NULL 1 Using where with pushed condition +1 SIMPLE t2 range PRIMARY,b b 9 NULL 2 Using where with pushed condition; Using filesort select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; a b c 3 3 NULL === modified file 'mysql-test/suite/ndb/r/ndb_read_multi_range.result' --- a/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2010-11-24 13:16:09 +0000 +++ b/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2011-01-18 07:49:14 +0000 @@ -605,7 +605,7 @@ SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3000 Using temporary -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Distinct +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 20 Using where; Distinct SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6; drop table t1, t2; === added file 'mysql-test/suite/ndb/r/ndb_statistics.result' --- a/mysql-test/suite/ndb/r/ndb_statistics.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/r/ndb_statistics.result 2011-01-18 07:49:14 +0000 @@ -0,0 +1,55 @@ +drop table if exists t1, t2, t3, t4; +CREATE TABLE t10( +K INT NOT NULL AUTO_INCREMENT, +I INT, J INT, +PRIMARY KEY(K), +KEY(I,J), +UNIQUE KEY(J,K) +) ENGINE=ndbcluster; +INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); +CREATE TABLE t100 LIKE t10; +INSERT INTO t100(I,J) +SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y; +CREATE TABLE t10000 LIKE t10; +INSERT INTO t10000(I,J) +SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y +WHERE X.J<50; +INSERT INTO t10000(I,J) +SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y +WHERE X.J>=50; +ANALYZE TABLE t10,t100,t10000; +Table Op Msg_type Msg_text +test.t10 analyze status OK +test.t100 analyze status OK +test.t10000 analyze status OK +SELECT COUNT(*) FROM t10; +COUNT(*) +10 +SELECT COUNT(*) FROM t100; +COUNT(*) +100 +SELECT COUNT(*) FROM t10000; +COUNT(*) +10000 +EXPLAIN +SELECT * FROM t10000 WHERE k = 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 const PRIMARY PRIMARY 4 const 1 +EXPLAIN +SELECT * FROM t10000 WHERE k >= 42 and k < 10000; +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 WHERE k BETWEEN 42 AND 10000; +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 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 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 +DROP TABLE t10,t100,t10000; +End of 5.1 tests === added file 'mysql-test/suite/ndb/t/ndb_statistics.test' --- a/mysql-test/suite/ndb/t/ndb_statistics.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_statistics.test 2011-01-18 07:49:14 +0000 @@ -0,0 +1,59 @@ +-- source include/have_ndb.inc + +--disable_warnings +drop table if exists t1, t2, t3, t4; +--enable_warnings + +CREATE TABLE t10( + K INT NOT NULL AUTO_INCREMENT, + I INT, J INT, + PRIMARY KEY(K), + KEY(I,J), + UNIQUE KEY(J,K) +) ENGINE=ndbcluster; + +INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); + +CREATE TABLE t100 LIKE t10; +INSERT INTO t100(I,J) + SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y; + +CREATE TABLE t10000 LIKE t10; + +# Insert into t10000 in two chunks to not +# exhaust MaxNoOfConcurrentOperations +INSERT INTO t10000(I,J) + SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y + WHERE X.J<50; +INSERT INTO t10000(I,J) + SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y + WHERE X.J>=50; + +ANALYZE TABLE t10,t100,t10000; + +SELECT COUNT(*) FROM t10; +SELECT COUNT(*) FROM t100; +SELECT COUNT(*) FROM t10000; + +# +# Bug #59517: Incorrect detection of single row access in +# ha_ndbcluster::records_in_range() + +# Expect a single row (or const) when PK is excact specified +EXPLAIN +SELECT * FROM t10000 WHERE k = 42; + +# All queries below should *not* return a single row +EXPLAIN +SELECT * FROM t10000 WHERE k >= 42 and k < 10000; +EXPLAIN +SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; +EXPLAIN +SELECT * FROM t10000 WHERE k < 42; +EXPLAIN +SELECT * FROM t10000 WHERE k > 42; + + +DROP TABLE t10,t100,t10000; + +--echo End of 5.1 tests === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2011-01-17 13:29:52 +0000 +++ b/sql/ha_ndbcluster.cc 2011-01-18 07:49:14 +0000 @@ -10578,8 +10578,10 @@ ha_ndbcluster::records_in_range(uint inx // Read from hash index with full key // This is a "const" table which returns only one record! if ((idx_type != ORDERED_INDEX) && - ((min_key && min_key->length == key_length) || - (max_key && max_key->length == key_length))) + ((min_key && min_key->length == key_length) && + (max_key && max_key->length == key_length) && + (min_key->key==max_key->key || + memcmp(min_key->key, max_key->key, key_length)==0))) DBUG_RETURN(1); if ((idx_type == PRIMARY_KEY_ORDERED_INDEX || --===============8454943345632689134== 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\ # s3dcmrs3ev63b1p0 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0/ # testament_sha1: 88c74fc4cf6b5cb026c2d6783a9e0945ebb32142 # timestamp: 2011-01-18 08:49:20 +0100 # base_revision_id: jonas@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfH8/doABwZfgFwwef///3// //C////0YA2+XvttoltuKgPQdNB7ng6W1tEqCqqKkBVTbJISKSTT0yj0NRN6Q9TU/UwmTU2pkGnq HojNIepkyMgANImp6mj1D1A1D01NkDQJgTJiYgAYAENGOGhk00NMjQ0yMgyMjQyAxNGTQBkyMQw0 0hqT1I0yA2ppp6gAGQ2kMjINAAAABFJGiaNGhPUGmQ00TE9FT9J6nlT9NRGynqep6J6T1M9U9Qb1 IJIgmgCABNNT00CjxJk09SMIGg00aDQD1LxA+OAN769w+4f6/VK/PkDnilpqGEwRYuIOSMcGKyjH hT9ksSkMB5p80+aTe9YE63kj5weq1kaWVY9fJxyUQBvw+9w8I9we28sMR/uLIyZOo2abcMjK5egK vkgqCCVlCQgRVxZZH4zIpJJNJTNnud+bLnHHkVJitUT7hEOA37KsFB57RwRXkshu/QbETuYsR8Ko fRTdsmtEjaxQEaoUsQ/x37JkOHNuTPrKrvYItcqMq20ySUBIoe4Aovi/BfJeNoiswBKKSlAzaQ4x umJV+nEoSpYxW9W8PLa5/gYMSgL6Tr0SH2i7us+eLiziGK/hNJpLAF+PiJ4zMZTk2B8BflDZjCM7 G2h9OccrEeIsBViqCAvE+W25ryHnwJj52/3viERoU8x7CU5FWXcSRb/7zuOV7/8tbKsuk2qFUPsy 9nShrEeJusqH+o9yPA267DQXG6pEMiIvrru6/CZQWKPd17azeH1S25DtKDuGEAZAZAR4oggETadA ug+xQWicRcluOkositbSN6/7efz5ym70TkoYv7NmgcYq1f1zQ8EEmSR9qVebxBg1bM/1RgwVn7dp 70HMkfeJwA22CQRvhLTdWDWVzql5LYnTRclsShQjwhFrQjGnRYugpMBhKjpN43yclJ9gRatIKwop hbtttttkBeBkZGRMQUIkSJkZGRkZGY+0Reb/9CvA+jcHlkiDpOd5nyeF7CSBBd4xe6Bvcj7T7MOK O4M7Mdeo1aTULjwTzPe3NPlLM452aR0gGdApcOchhX8YQMsPAwF7JNiUBWWk7M80sJs2UhAzhYz+ Iz87RKDBAcBhhoiYcY93mOQDvJBIMDXWlAgUmqASQgQIEwIdFNCCgIXI6iYGwU1JRLyjICKjA2lc ISX76iiK2VKgYaDDszv1FaJlZKV37+1L2GQjClKYyjJ8Sovuzmo0FpVgEcGD8zBMZEGBODCHZCLR hjY5lWAv1yUtXExoL6aaywBVMyzVQATU7z8Z9Kw3qWNc+GNKD7yUXGLBlpeAl7UpEiK3nWVccsSw rLkqnKsjq08KkBFSvNq5se+ZnRbgbDaZ0QJmjdQkBpWgS59fUavjdaYh9ceZUcza9lFvQBrzB0dC ww0sGZN3k8UtJIcMS3gcmaZGdoR1Jil1HOJAeOwAZKRyVhzNExyvvXmtyzd59WJhldxKHE9qvJTx yOoZ3Y2sQKy4gjgcDWTO2suhVcvAe9GgY0rRUZQKywsVBLv+KqLiwnE6wSzFXOS7BXY22GN+02mc SnkiuAdbKwiuVd8C4maagcisx7YihN/mJnsGr7KzAtLbqKh4hJzc2yAlQhNvl8vk6xczAqPYG17O fDkWBXjc1Y6icqKi6rperlOU+JurWKk5ndw0Xa2tr3KilWVQ2hXj0zfuJGwxHJjncvDjIxC3dduN xNQWm0xM5A9BKZ00bb16PkToXGy1K/JWWmbv8dZnKbDFehUT/775RWlKadcDgWUEnm0mQDAm66ib DMQIQQASIgG5h9ROhuIlUFajfraUUvAfGwqMvPJOtS9yMTAaVmo1GjcRBEyBdpcH85VjjjRNSbkc iivKuXKyqJM1Gnqntnuz0M2demarLPCEJcdZrMJWjJ1iry8SY4F5kUUFF8ChroSc0sVGOe/M9ZeW lRsKi8mTGviHaPlAkytgQV97TggcUWnSuChJKafYVvAIwkAEYlbxZTc3qT4wRVhBzEMyIrgSac8q DgE4oaRnbyGOMNhsGqWORDQakZGWvVW6jB3IrhZMWMXKEyA5hHWI/Z9DaJDSkmCmEyLfgWIhwQER BEjzEnKlgYGsoFPVIoKcK/+LT0ID8xaRPkGAoG2kU9D/IpSYzEZT9hZgYz2FwmMa5R3pApuXGMWi f2iXBcrat4n7noVgYBcJH4w+I+8YCAbSAo82K/rUVH0Cc4C4CpAvE4BSkN4DKGIUsVmQs3dJCUEh xhhyZIgIdBB2ZIkKJ9nACC1ALOf9QpPhoq7chh+gnGswaL5ZDCXC2gbivoKf9KQL1YIBsAMMQQZg MS2AGcCguKBkqmIqE4GkxLCzON8TYVAVxtQWELxMZhpMzACQRMCgDICfgKIkPYSGBHouIguE0Ip1 IIYpR7gPBJJ8Vqaj80kmD9MIlCEBHmSSVFg9yMdsXBYBdJoObvWgkM97p66x8IiVKKCjhvzpbNeY /DSgLjU1aNzXDp3Pvy0loVgOvyXDADHE7muRCuPKjpsJ3yJWB4IHvStizELgOukO98Mnr0Fr0JLq P4m0/SfgaHJh0JcSJVutSRz40IIKHQrLN6Y3WAcUMWfip4/MCnX/JjsHzkC4wIKBkYkzLovkPA7E w5oO1QKys6Vm3+5eZ+0bU2nW2EGQGZnlQ2Wi/TcROH5Nq7CMZhJRjUihxhAMv1ysmaUmKEaAi9I4 C/VHrsNkE2s48RBhWRumK1rkpRJJGCIBYMAD3lYl6m7WTJ50OhzbzHepWlvLjSXdJa7Hkvt3dYyT sYShNf6yn1sl0SdGCIodIC5OzNroooiZ9EFDqMxA7f7TnUXj5jxORjoR3I0IC4yZUZsEKwDskEDm d5hfejXKRx2yGYoisdk3DTtIliHIXEVj4GFcI9N9GymjT1VmFlKuKrjc1P6Pf7EU3aEaJ6qiKMyW xMl7VwkGx3RtsE9/saPDXYmu3mQVgDN0YJpXoYO4SYRoMrSlS1A8pqFayzdTY5CDiA1nQbDYajrO ewaRVmPhM7CZrLlqoKwbEVIclwj+SPLxQy3iz7e7UUjOKthqadlSnTLq5zNQzF32+EJccJQELwcp 2AwDMmrBZengecF5fj85W2itHJL1GMhBfGD7VMDfzyT11p16SjdmpgT5XwjG/trq3S0cmlrBJKQe QWHvgR5gRRffHPncZIyKL17dtdQFQzCjpOwsSw3KQJqf04zmXSlS3/C1Ps6tre9TjwuhAvDJ1PXu A8cozvO5RDP0pxH2sDEksaE2QBde0HDkdeF2OZ9miMx2IaAhzjOdL5xVv9o93Ekjah96A8kA6HVw 4Ekq0bEbxlHT69+HCjo0BuSTGJtOW4xOUUzO2T6mOIyGaW39KTNhT00BgK2fXl8Fd8ORputFjGIl vRRc1MPiGkM6M14mt98Xi95KCZpIBkhxRisyIhOUW+8YjRRPuIgYeqm+MqGhoo90SYi6U10EZDAm JdpkIlcUo5FYWpMdZyDV8NhydrV1tu7sZNq42GIXrZPF4WOvT2nQcu6c1Xx5mw6TgTq8/BA2Gw5h hJeVhcCb7BvDigiCTC2B+VC+6mTWGbsh5Hagb4FRSwHH40sD/E18U7S1N+FqNJ4YCX4CxgRwEN+/ CQJOY5lJ8tlnR5Ycvknq22zckJiMvPKfcdDlQqYDpMd6Ydc3O9kHa0hzkNDAakkg+3dGytYqvqJD Smk4BCpFsGBbBFYYlXrAXoV2NTYXLTgdrEjyis+TmMXWUZB3Ei0MWkWVxGoh0pN1pjO26MGYjVqG gdxUcoQtqTga1gJdQlsKg1FltwWVmlHDL7iWOE4EvTfwp+SyS0CaeWVAucsBYe1zWexOZmbB3fko 5cAWhUF1Ice1h2Ep88yDWozQaEh2YuAYcwUwglGzUbt8Mo6mipMD/ImJssrHUrVovs0DAgwgDBhC 2iIQRDYwIDM2tkeJB/DLaOYTEGVMKVlCtTe1QF6Vucq4MI4cGRYZg2qaqCEQ0sIh0LkMsFH5X2TR akbW3DZMWCcaoFrhZmmAy0IZwrdKmgK1BVGN6Qnreo1azNzvWPxrrcq6GDeoknrS1OKifEG/DtSg vuQ1bdL72HQL318L0NImvjI7b3ITISxvOsis6zQA6ZOitPI4qwWe7nzqNK0empGSD2qLrNepm5/r 1rI61QFFHR0FZuZ0DnY6DOWqaitSRNN4G5sczSYHdaNLvJxxVwUuc4H9+H/4u5IpwoSHj+fu0A== --===============8454943345632689134==--