From: Ole John Aske
Date: January 18 2011 8:55am
Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4132)
List-Archive: http://lists.mysql.com/commits/129045
Message-Id: <20110118085521.D3064223@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============4613775356751722484=="

--===============4613775356751722484==
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:55:15 +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:55:15 +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:55:15 +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:55:15 +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;
 }


--===============4613775356751722484==
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\
#   nxo4eq22g6lk42mk
# target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\
#   /mysql-5.1-telco-7.0/
# testament_sha1: 8c551d868ddee452de7a1c35027b1f90a58d6ee5
# timestamp: 2011-01-18 09:55:21 +0100
# base_revision_id: ole.john.aske@stripped\
#   s3dcmrs3ev63b1p0
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWRzuvxgABLxfgFAwWP///3+/
/mq////7YApuvo9rMAABoDIBqmgAAAAJQggpqemptqn6p4Jom2lPU8AibUyaek9EMhtQA0OMjTJi
aDJkwmmQMhoDQGmTQwAmgMNRpPQCaImE0ekeoGgGg0AaAAAACk9UaD1M1Bpo0GRoNMjQNNGgNADQ
DQ0HGRpkxNBkyYTTIGQ0BoDTJoYATQGEkgQAEyE9IaaNCT01NpoJqbCmTJ6nk0mjTGpYQgIda3VE
TCQWg9wuzJnVnnRrTdCO1RsiMJMk4wwEOJ7k6HibYaBlugComDbFq7TEYOKmC0HUrxERAU8oLHkV
HBZfyTYowFmkxudCA2i5Pppt91uhB9ynTTvtxapCvsGREIJ47cHYFksbLZXSytr+VNM1GNuLIF1f
xyPECoBL58P37N3H03sP2vce8xCSnzd5Y35NDvWHKGIFEVVUUU4NwlD97qYMDVNEgypDiZuNmQqj
U97lIsyOjKWO0o5jWFi/oM1y0T6rcrrwoc5aFMxtIwOllKshf9CRwjv1jlMsTFQ5EZgPxip4jfew
CYxjJk5WkrUpyQd8QdJLTPXRlvhRhc3lC22UR5XWr3OkpTA5TRyfBHEeXfCcfRj8TQ5aoAC38ucJ
FOSqI1jCKAYilhEHWbZJI8kxK1VbeYpEPv5Gfz6jS1FYinB/dK2NPIG2bol4UCCoB7Alz5Zqbta7
56y9/VF0WJgLKolpx+3dKvXPlvfFLix0SFdS4+zPr1xPV7VaX37C9Y5HgOEDEV3ey5x7Tlng2K0I
rv8gLtOw9ot88Lu4sLDJ7O8sIKHpWqy9pvAXOGopSClV1iQVnIzMvV0BzfmDBKf7SnOAfZAsxRn4
ZtUIf7nIHMDZkB8hEEPJDBpMOEhygkjRjazLMnIDI9Xun7hTk8noXEjPxZKZWFY8xPWi4qdNivEd
7xesyRUynjQ973w+vwlMSLqIGbqwiJGfWV1nSP888xmXiRkMgUptkl8q5Va53beabkLxavxFcQIo
0vsv3AqderldRbcUGBSZBcYG14grXC43zxJsg3YlzzIKi0aJfpFDtoqNv1rWTqX3EgS1vgXheabN
hgf0Cv3aGsp2p8CjY+3RprjRyWh3my8kCwBeBbyGJbtxPObB5gx4UYUDp+BmOqGkUlBvwMUWLFiu
8VOJGvafUed4ujBwtm+JZVUFZqSuLXI0awnIjw0BbRwb7+mk89srpGYtvvib9yJFJKwFw6Oo0Oao
uLnAtGKVZEFIrWsdpNytGJOCvIZidykjM5CmiJce04cpKDym8tN3GRMc5wpvNau6OEFijYPMG14m
8VEuMiYxxxlVlXaVDjbaLKSV5iOJG0Y01e4FL69dhfOV1rY6cxskD1068DIUSRM8yNzEh7GbCtJj
aPuO1dIxRfXjbBmGSsYE062bSWBQPLqaFQJOgWlVsr5yVm+eyrCVBPOXDjK4U95OXGocTMYWAqkI
sEzQdiFBVVPFEQu0HiSDZiQMrxnesgFiukWJGW6h9sbXUugPRFgJnii6ZqR6NdaVNDez7Pvanqh4
Iv94xwlnuByY2sN6QZKg09YcaeLlbQkHnXzGkfkMH1x0fM9mTAw0l8eEWPZ/k9v8icBBKw/KlxED
5FIMx+CYCw/df0XVXF7Qh7vm5yVZWfsfwZgaimYncsomKF/IXFtTJGFZzB+jDhiIwESNYKv9DAdB
rBlfq9BuIFRYK4NFuxPoQxGIMnBabMR/0/g1V+xf0NkCwKCtwxu/YZ49KDi57genUjfqLxQy+OCJ
FipVGX3oZ5sSNoBj5s8OsB4DKnYjHYBKSyEvhKjgICKizINuaAhOeBOduwV4xtMky/I1n4FNBCxL
qoL/jckDi0uxIJQNR93M/4+dHzkFsidjL2s+z7IlyFO65HFILXqmll+KYk34JqGsSvmKPT3doX+T
8Nw/8xcLz4mcxF5xiTREvjOdA9bEuWQczBx+BEgWuMD1JKBZ8dTjaPqH3W6jE2Yj2O2BrIgVGfWi
6I86FTWdJWfTyExaU7xjFtrbYYEYMkGK1C+WAI3TBMk1I6hzxdUw+mEdTrAtNO2OZEktDYPNC50x
YKaDy2ysVE05MgdvGQGugTm0w0IJbi3uf1ojuHzGZJRCzZ8ymky5pv0pfr7LXP8k8kQ7s5TTA+Dm
JpGBCoOchWsydQmKTjiEmtQQgBqQK23tpAyMpBLZK43/aGAZpBZsmQ1gqUoJy0dngH55hAC6MRIa
zBfsmNRWfcC20zaJPWhwyGwFyu8LqFv2k4PYrOHJjyGyOCOpMKoE/dzA5ZOdROk5FARKnwZccBnN
yPHiGtCxFc/vc4f1OrOoUErEqUH7GsSIFagqvoYB1s4R/c/GKPIjKhUVylC8W2P8GaoFuhv6ROAf
46DvKvLJHW7UCugeVA1OeovqdDn1gc7bxH6/QlCOb3CoxTh69g8JQOcEUk+5SckRuY5dVWPRSxIj
tB8YJUX8ghsiE6LymAlaBEQ1d7UT9XSlAgwVuVbUXb+22cNW/ZVRJmF6DIU8WKmGqSzJ0sCM2rnB
55+vD8rgeD+rcW9RbWelIK/TSc7IGuKwXErVoIkeqgNwHh8PuhH1RlBkr8h+xHIcPEwQYngGIYpQ
ctzROjj/jvBzciQdqQOB4yGCbHVw3LlBNMB1WP10OeDIxWDrVmntckHieS0fVEYsBz6AzVZkSN7B
5kjuafzaFM6KsBUTm4ifXBZ8k32fnCCLiNj3vfCEAkkDIe0dbZg1009IxBL/fi0FzDnpmcFXyklM
VuSHDq6NoosixLEXKSu8TcT2IYZMwmX21ApG7vZhdbG56icoLnf7Fqo6DV+ngCOgXKcqGB3ouBGb
Zet7shkruvkv7mvDUHo9Z2JBolAiXA5XQTW/Fjb2He91gOVLMMONE4gtx1ewTtiDoW0p0YGF2jSR
mpi6KTcasfQ6elTYVUBxBYdhYleWXieTTDHaw5VDSTLgygMnsI2vELZJwTMEXDKBcRPQnehnG+Rm
TpHrRXq89CPKT3Fm07jsXDUuR/g4nntrcsHV8omxKu8enJ/m6Gg9lyhWupeD1NBzROxKkXSLEol0
E2RssR3OG1bsmGaxgzB5S30wSgGqpILRXZLxKI+YOLM1kC3YLcZnJQdaekTXilrWKCIVJOLJKCMi
PS5VE0StUQe98dg0mKqXLgrA9R3T1zhWukChb+WmtEALKEDTMnz051aRTQQ+cKeK/WyiZRAaThYM
HqflWg6WDYcQu2EyXdSKpidLBZOHZKtOFk9mxzOws4eUWf499gbRjYUOMmWJEYXlYfeOVKnoXj2f
eNMLvnrM12DoperfotEO7aS4VRemStYD3YyISQeLIw1U2c16nN47KtOW48CAKsgg/zOud6s2a3Zw
DpYgMJmSvN5BPMxWDjtMirJBvUcFrY7g0/+LuSKcKEgOd1+MAA==


--===============4613775356751722484==--