From: Ole John Aske Date: January 17 2011 2:47pm Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4125) Bug#59517 List-Archive: http://lists.mysql.com/commits/128985 X-Bug: 59517 Message-Id: <20110117144716.BC9B9223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3546014839190949473==" --===============3546014839190949473== 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 4125 Ole John Aske 2011-01-17 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-17 14:47:10 +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-17 14:47:10 +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-17 14:47:10 +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-17 14:47:10 +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-17 14:47:10 +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 || --===============3546014839190949473== 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\ # fal3io1x9rql51h5 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0/ # testament_sha1: 31f124f36c824801c70481b6fbf35d755eed8c70 # timestamp: 2011-01-17 15:47:16 +0100 # base_revision_id: ole.john.aske@stripped\ # q6poszeqy1pbflwf # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWe5jHykABwpfgFwwef///3// //C////0YA2++322+ag7ewyVS+3k3bb57zqvkXZtjNvpl0ql9tdAPodVwkkRqMmhppMNGgjTTZKn qbJinpNo1Ho1PUNHpPU9RhNBlTEJg1TzVP0U9qj1GjR6gAAAaAAAAGgkkmCJhqk2IZTCbU00MQaA NAD1NBoGgaBiIUyRiMg0GgANA0DQNABoAAAIqJomp6aaVP02hU/0pm1U/VPahPTxQ9Sfqh6IyZPU wCNqaA0EUiAExBMQ0Mp6FNJ+UNJ6ZNIyaZNGnqAyDTJaIHXSGf5cB8w/p8kwdl4bISZUDBKYci4Q vhigX2z0XJ/5LUoCw/BPenvSl9awJ5PdHvg8MGR1sa67u/mlyOBm8/887dtsw7Kw3Ifb7rGLqbUU Zp3WMQOFwwfIioImuwMYobFXDoQqyLJJJUmVjJd/yy7qtQY2E9FB9ok/Ej7K8Wcqt5IEt0uZHjqN yVYQXJ67J1V5rxcejFyOEWgzsIb/f7wlhX6qbzfA1aP4ET9SOVUpkkiEhN7gCjCeHPcSgLKFyQCR ZIzLJzRxi0p0ztzZJkJ2MVulmDg13HOMGBMF4Ta1YDtcW9tHG9wuQQwtG8cpylYGifCdKZ4jiO5q jdY0YgxzCZ8mZkO4KBxIIrFeFIK0DxVm+5M10fy5yA6hvnmiCIaqfCOsIRgSpvEEYujhtNu538xN Sstg2SqVXJTc0TMoispltDvWc6NxlxymEmKbSHzkQuySbTugWxX0c+nLIaBu+5glPEyHxGBwMOGH CMTwcD2ZNkF3HkZCcoEZbmVqjJfFPjRo2fj29HYVae6guBu/696nHN2ojteu+6tBGySO1KWiwGDH lw9kT2C8d9J0IOIjP2E4AhzDMMdYuiHh7q3PfP7V1HStj11GUcKITHRxSSX5YRG47DwPE5HE7Dka i81axM80KBniPXpiIiIgkLfKFChEQTHjx5QoUKFDUPMIuM36FcB37g6rIQO6bHoeodLuIiwMf3wt bhxNgv4Hhqww4BS0jx4HAOsvroyfW8Gj3SzcCnqHqANIJNt0gn8oBModzAa2SehJhWYFNumlLSlp ZaBA0hfEiO68WcBMkBBBDBB6vMkTDwJBIIFrau2gQKDrgEkIECBSBOT0b50INCCZtozkgNCIqCeX k3p9VRHUK4PgtMysJsE5Aw1TDmZzm6ZWESsfCOnQluGoIunOd8HwdeSLsNZtNheV4hRjAfPAVEBs FiiAZhAkoRMCCDfI03AP76Wno7TOwfbZuLgGuIdVcwGLOkq5qVhsKGEbMz5SMMxF4OGLGWN3ElAe VJhJsxslXqzwLi0wAYKszh1yZ4G9y5SPl1XlKadGJtNxqSohI2Jk4gPwEuoHXEtvZNn/XXTbXDyw 3SZukgddY9aAc9+s4mRqyLJfQ9LWz4Q48pMur4G3u78r7NDvCUjM4ymBC0nFuO0486yZ5vU6959G Jn09hWdh8bZjI3zvoUGcA1haVBEzmqROrMdGxb5nqtReMa0IspFAxJRKcBgnWIwoRsYJ88E5UhvA 4VC5TIxwcJydD2QFpNqwkWlJSEE3M+SgemPEqd5Atu6XK0oMxcuiUDHE5WQHXGfvrnFqJi0Xgyx+ zZYtxgoJLdRgiFUVGZZjTGi0NyngTUq6VLOJRPBm4+PRNd1yyIrytI1NJKvP7ik8DE1TKyZzfHtp Mgw36N5vKmbsvMzx1ltJWbr3yxrgqLzbQuFw7Np4eW00tJg+TyCWC3LMjY4002uDhCbtrQTXC8Ag PEgV6FYmCFmOATAudkIyM48lUrDNlaD0rysiSwt4LlUsa7aMJDQtxmw4dJrpayg7t+UwzqvO4kUF rZLuJFji1d3ddMpOBStT9aHKhDGHTnhXQlKX8eoVBb8lLyCkzGYirGCxLQEqvArRIymsZ3ZWmWZM MbrC8tJGoSLycjKJTHYROYGrOIo0HZOadNScFgYKM1FwXiTItXE7ODIUABPOm4lJlvblKE8SzRi8 kJSJZwkr7bxiLYtAtdGPHmK7YYWYGWlhpUMgjtlsn64u82/FEAvldg+vEE+kT3ifkf1NpcferAOI wT5SLZxDAREREj8BKZTYGBrJinjImUXL+RgfmQH9BaBGkJlQrXiR0H0Eh5cYih/RKAFxxmAi4Vco 9qQKa8jQLOn2CZBkrguwT9D8ysCwWwb3MvILSJgYFaMFX3TS54kTmCEGDACKA2CbxSgNoGYXilqt JC0vQSEmUkiCBEoJyYSKE0YQUnGR1doCZzAPKf6hI9F1HfsKvgJqWcGa2NhUYxawMqv/Cn9kgLVY IBtALogg0gXraAagJmRMZK1CVjIDYZFxdqI/Q3FYFtF6FxPQMGkimIgALhMQZBBIY/EZlx9Bcd6e vekn4T7k4UwQxQjwA4pFOtaGg9qRSn3VCTIQCHxIphTkPMivALkkS7mR7j3nku89+GY9uxxEeEqX 6PUnDhSy5KD4YgT3m6NeQ4GpgmWhWBJ/F+kXhoAxxPFskQrfkjwuKdkiVvsBPrSti28Ww86A+Z9m Pt7x7NvI9heaD2mWLcHQevUSJVqkujFgJIVE5SXNJ9IcwGhDEvioXucFCTaxxDsA8mLxieYqDAWy zjTG49DBI2HezLCw57z/JoNfeRtjo3RonAJm05Iej+2pE67l5EXlhKL1Im3hAMvxxZNKUF0IzCNS RtOXZHptL4KSsuOwlcszUX4RgtNBSpckwqIABWcTPoJFGRIpEsz6x9U6zuUxmkx90xPQ6ra814xT cwSZL/jGfaxW6MUsSSRQStjCEMltRIiOTM9P0GA1xEPKDs+6knI+AsOEoxo2oxgiY1w2RGSNwHOk JnM8S9OmRy66CIKkrJQx28uAUIiEBLQorqDYkqssw3qICG3AuKWC6a1QPN80LvEXHdCdFW6soTNd 7C/G9YUh0yknVcMtHKKOG25jDgZhaARHhAVLoSBd5kzSlaQeJ1CtZZsTiV0YpA0FhUVGc3muEuu0 bcJ06xKQfsOM2lKmE0xgwidsWp/mjdWhlpFjCrg1EQo2htNSi2wSK3VeJpm0i7XohCXGEmELu5ne EARBvRiJXA5N+o3TO34eIqhbyJev3EDIiYpTRuUpc31xTvYE3cCe2lSxP47IRjf5V1dBgOPBrBKW kPMLj5ZlHoAoTRoo1apEKZlj7PDfb3C2AWEQMek8y5LjZICdTz3mxeZKFzeitOHTtcz0uKp0gmaz pd2UHulGp8HNE1elO09bAxJLWac4Ay2MzqxeXN0vfuhkN43BSLyzCwSiid7IYNHgHhzpE2pHSCbg SSScCQFK2ptTpIaM/Z8GPVZJNYa5JoE8k5ZF7mKaXyk+DHAxNMvL/FBpuT8+kLCtq553+faa8NQ2 SuTi62cPnaHKmTEMKuEoyjWTzMITgkAGBhA35EJRgroQhMYDoEABT3TE22cuXfkTFKo1K9JGJYl6 6CMSaOhWFpTCo7Rq+zkdtPwt+vixcK1sGEF6WLp48jqu3mwvpFtNeDr1tR2zQnc7OIJxTichhJe6 0zBNzBpG+CIQt++hfrok1hg3svwVgIyAQIkwaeuJMg6jXojmJkZGSgZzpsJFaNZDr2wkCfUFI6VK eVtvj7rs/cngH6sKXGEvM+8JU+o8XMdgsB8Bj0JfxCl1vnB8TQHeQzYDgkgX27RtrWKuiokNCdBk E60vnAO4S0Miv5pj8RbdFm0wd+J3wUnlQ6seZf5E8R1pH1i6AwjIjsIetKHkmg+TKLNJHDgRM8Ss 7pzvrZAbXaU7Cy24LKzWnVj9pINcG5Nabbk+9ZJgBSnuzBMkzgtPndNvqTnERjKUu1oz6kNbYPWk iV8EoFq46UNrRUhrUlEGAEEjFqCa0XB0G7mqo/Iz1Bgd2UxFlSxyxMqGhj2DAOYHAwQEtKTEJkQQ BIg+fiyPpIPjzwHSJeGaXJWTVqdjVAbErdRVvuJB1wlxpDe1NYToDZAk+ZgkOLR5S3VJepvjpIzg uGRFYO2d2moCHWkSC2S2awtZtZlsSE+F9Jw4mnvfMf4V1ua3MDPNFN6VpzzT84aIO1Ji3Ghzd7zv M8aLnuPaE49pHybHEpIS12HaUOp0hMDnnJLWVJ2Nw6sNevAWuXfwFzpkQ8LPF5bHAaY/joche0gy TZFC06okhI5hJDWXs3apQm5DJsdLQVu1nwdyNQNCmJUFK+2f/xdyRThQkO5jHyk= --===============3546014839190949473==--