Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-10-16 18:09:58+03:00, gkodinov@stripped +6 -0
Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on
strings
MySQL is setting the flag HA_END_SPACE_KEYS for all the keys that reference
text or varchar columns with collation different than binary.
This was done to handle correctly the situation where a lookup on such a key
may return more than 1 row because of the presence of many rows that differ
only by the amount of trailing space in the table's string column.
Inserting such values however appears to violate the unique checks on
INSERT/UPDATE. Thus that flag must not be set as it will prevent the optimizer
from choosing a faster access method.
This fix removes the setting of the HA_END_SPACE_KEYS flag.
include/my_base.h@stripped, 2006-10-16 18:09:49+03:00, gkodinov@stripped +6 -1
Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on
strings
- disabled HA_END_SPACE_KEY as it's no longer needed
mysql-test/r/func_str.result@stripped, 2006-10-16 18:09:50+03:00, gkodinov@stripped +2 -2
Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on
strings
- fixed explain in an existing case
mysql-test/r/merge.result@stripped, 2006-10-16 18:09:50+03:00, gkodinov@stripped +1 -1
Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on
strings
- fixed explain in an existing case
mysql-test/r/select.result@stripped, 2006-10-16 18:09:51+03:00, gkodinov@stripped +10 -0
Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on
strings
- test case
mysql-test/r/subselect.result@stripped, 2006-10-16 18:09:52+03:00, gkodinov@stripped +5 -5
Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on
strings
- fixed explain in an existing case
mysql-test/t/select.test@stripped, 2006-10-16 18:09:52+03:00, gkodinov@stripped +14 -0
Bug #22367: Optimizer uses ref join type instead of eq_ref for simple join on
strings
- test case
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: gkodinov
# Host: macbook.gmz
# Root: /Users/kgeorge/mysql/work/B22367-5.0-opt
--- 1.79/include/my_base.h 2006-10-16 18:10:14 +03:00
+++ 1.80/include/my_base.h 2006-10-16 18:10:14 +03:00
@@ -224,12 +224,17 @@ enum ha_base_keytype {
/* poor old NISAM has 8-bit flags :-( */
#define HA_SORT_ALLOWS_SAME 128 /* Intern bit when sorting records */
#endif
+#if MYSQL_VERSION_ID < 0x50200
/*
Key has a part that can have end space. If this is an unique key
we have to handle it differently from other unique keys as we can find
many matching rows for one key (because end space are not compared)
*/
-#define HA_END_SPACE_KEY 4096
+#define HA_END_SPACE_KEY 0 /* was: 4096 */
+#else
+#error HA_END_SPACE_KEY is obsolete, please remove it
+#endif
+
/* These flags can be added to key-seg-flag */
--- 1.161/mysql-test/r/subselect.result 2006-10-16 18:10:14 +03:00
+++ 1.162/mysql-test/r/subselect.result 2006-10-16 18:10:14 +03:00
@@ -363,12 +363,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t8 ref PRIMARY PRIMARY 37 const 1 Using where; Using index
-4 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 Using where; Using index
-2 SUBQUERY t8 ref PRIMARY PRIMARY 37 const 1 Using where
-3 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 Using where; Using index
+1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 Using index
+4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 Using index
+2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1
+3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 Using index
Warnings:
-Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))
+Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
ERROR 21000: Operand should contain 1 column(s)
--- 1.50/mysql-test/r/merge.result 2006-10-16 18:10:14 +03:00
+++ 1.51/mysql-test/r/merge.result 2006-10-16 18:10:14 +03:00
@@ -626,7 +626,7 @@ id select_type table type possible_keys
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
AND file_code = '0000000115' LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref PRIMARY,files PRIMARY 35 const,const 1 Using where
+1 SIMPLE t2 const PRIMARY,files PRIMARY 35 const,const 1
DROP TABLE t2, t1;
create table t1 (x int, y int, index xy(x, y));
create table t2 (x int, y int, index xy(x, y));
--- 1.138/mysql-test/r/select.result 2006-10-16 18:10:14 +03:00
+++ 1.139/mysql-test/r/select.result 2006-10-16 18:10:14 +03:00
@@ -3517,3 +3517,13 @@ id a b c d e
2 NULL NULL NULL 2 40
2 NULL NULL NULL 2 50
DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
+CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
+PRIMARY KEY (a), UNIQUE KEY (b));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
+EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 const b b 22 const 1 Using index
+DROP TABLE t1,t2;
--- 1.112/mysql-test/t/select.test 2006-10-16 18:10:14 +03:00
+++ 1.113/mysql-test/t/select.test 2006-10-16 18:10:14 +03:00
@@ -2998,3 +2998,17 @@ SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1
DROP TABLE t1,t2,t3;
+
+#
+# Bug #22367: Optimizer uses ref join type instead of eq_ref for simple
+# join on strings
+#
+CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
+CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
+ PRIMARY KEY (a), UNIQUE KEY (b));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
+
+EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
+
+DROP TABLE t1,t2;
--- 1.120/mysql-test/r/func_str.result 2006-10-16 18:10:14 +03:00
+++ 1.121/mysql-test/r/func_str.result 2006-10-16 18:10:14 +03:00
@@ -1143,9 +1143,9 @@ EXPLAIN EXTENDED
SELECT * FROM t1 INNER JOIN t2 ON code=id
WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 Using index
1 SIMPLE t1 ref code code 13 const 3 Using where; Using index
-1 SIMPLE t2 ref PRIMARY PRIMARY 12 const 1 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (`test`.`t2`.`id` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
+Note 1003 select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
DROP TABLE t1,t2;
End of 5.0 tests
| Thread |
|---|
| • bk commit into 5.0 tree (gkodinov:1.2280) BUG#22367 | kgeorge | 16 Oct |
