summaryrefslogtreecommitdiff
path: root/mysql-test/t/select.test
diff options
context:
space:
mode:
authorgkodinov/kgeorge@macbook.gmz <>2006-10-16 18:09:58 +0300
committergkodinov/kgeorge@macbook.gmz <>2006-10-16 18:09:58 +0300
commita1310d84beaf367f6e085e7a1d9ad3aee4046836 (patch)
treeddf7474cc07baef2f6c1c4ff063b7613f711b03b /mysql-test/t/select.test
parentdf1376a542c7a4f3c2ba2e2908a9ac383b7d5ae4 (diff)
downloadmariadb-git-a1310d84beaf367f6e085e7a1d9ad3aee4046836.tar.gz
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.
Diffstat (limited to 'mysql-test/t/select.test')
-rw-r--r--mysql-test/t/select.test14
1 files changed, 14 insertions, 0 deletions
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 36b3749b4d7..1a5a3d2b254 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2998,3 +2998,17 @@ SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
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;