diff options
| author | unknown <igor@olga.mysql.com> | 2007-02-14 22:06:41 -0800 |
|---|---|---|
| committer | unknown <igor@olga.mysql.com> | 2007-02-14 22:06:41 -0800 |
| commit | 6ae94723ca07c0938d25105d6180c96bc6abeaae (patch) | |
| tree | d44a33ab601b96a951e6b65aec5bb1a6981f3a6a /mysql-test/t/select.test | |
| parent | c039eed82cb21d75965fd9b2e936396e0282e0cb (diff) | |
| download | mariadb-git-6ae94723ca07c0938d25105d6180c96bc6abeaae.tar.gz | |
Fixed bug #25971: indexes on text columns were ignored when ref accesses
were evaluated.
According to the new rules for string comparison partial indexes on text
columns can be used in the same cases when partial indexes on varchar
columns can be used.
mysql-test/r/endspace.result:
Adjusted results after the fix for bug #25971.
mysql-test/r/innodb.result:
Adjusted results after the fix for bug #25971.
mysql-test/r/myisam.result:
Adjusted results after the fix for bug #25971.
mysql-test/r/select.result:
Added a test case for bug #25971.
mysql-test/r/type_blob.result:
Adjusted results after the fix for bug #25971.
mysql-test/t/select.test:
Added a test case for bug #25971.
Diffstat (limited to 'mysql-test/t/select.test')
| -rw-r--r-- | mysql-test/t/select.test | 46 |
1 files changed, 46 insertions, 0 deletions
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index c4737814137..ea5fadb2e1b 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3253,4 +3253,50 @@ select case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co; +# +# Bug #22971: indexes on text columns are ignored for ref accesses +# + +CREATE TABLE t1 (name varchar(255)); +CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +SELECT * FROM t2 ORDER BY name; +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; + +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; + +DROP TABLE t1,t2; + +CREATE TABLE t1 (name text); +CREATE TABLE t2 (name text, n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +SELECT * FROM t2 ORDER BY name; +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; + +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; + +DROP TABLE t1,t2; + --echo End of 5.0 tests |