diff options
Diffstat (limited to 'mysql-test/main/type_varchar.result')
| -rw-r--r-- | mysql-test/main/type_varchar.result | 688 |
1 files changed, 688 insertions, 0 deletions
diff --git a/mysql-test/main/type_varchar.result b/mysql-test/main/type_varchar.result new file mode 100644 index 00000000000..0b2a5b54d08 --- /dev/null +++ b/mysql-test/main/type_varchar.result @@ -0,0 +1,688 @@ +drop table if exists t1, t2; +create table t1 (v varchar(30), c char(3), e enum('abc','def','ghi'), t text); +truncate table vchar; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(30) DEFAULT NULL, + `c` char(3) DEFAULT NULL, + `e` enum('abc','def','ghi') DEFAULT NULL, + `t` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table vchar; +Table Create Table +vchar CREATE TABLE `vchar` ( + `v` varchar(30) DEFAULT NULL, + `c` char(3) DEFAULT NULL, + `e` enum('abc','def','ghi') DEFAULT NULL, + `t` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values ('abc', 'de', 'ghi', 'jkl'); +insert into t1 values ('abc ', 'de ', 'ghi', 'jkl '); +insert into t1 values ('abc ', 'd ', 'ghi', 'jkl '); +insert into vchar values ('abc', 'de', 'ghi', 'jkl'); +insert into vchar values ('abc ', 'de ', 'ghi', 'jkl '); +insert into vchar values ('abc ', 'd ', 'ghi', 'jkl '); +select length(v),length(c),length(e),length(t) from t1; +length(v) length(c) length(e) length(t) +3 2 3 3 +4 2 3 4 +7 1 3 7 +select length(v),length(c),length(e),length(t) from vchar; +length(v) length(c) length(e) length(t) +3 2 3 3 +3 2 3 4 +3 1 3 7 +alter table vchar add i int; +show create table vchar; +Table Create Table +vchar CREATE TABLE `vchar` ( + `v` varchar(30) DEFAULT NULL, + `c` char(3) DEFAULT NULL, + `e` enum('abc','def','ghi') DEFAULT NULL, + `t` text DEFAULT NULL, + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select length(v),length(c),length(e),length(t) from vchar; +length(v) length(c) length(e) length(t) +3 2 3 3 +3 2 3 4 +3 1 3 7 +drop table t1, vchar; +create table t1 (v varchar(20)); +insert into t1 values('a '); +select v='a' from t1; +v='a' +1 +select binary v='a' from t1; +binary v='a' +0 +select binary v='a ' from t1; +binary v='a ' +1 +insert into t1 values('a'); +alter table t1 add primary key (v); +ERROR 23000: Duplicate entry 'a' for key 'PRIMARY' +drop table t1; +create table t1 (v varbinary(20)); +insert into t1 values('a'); +insert into t1 values('a '); +alter table t1 add primary key (v); +drop table t1; +create table t1 (v varchar(254), index (v)); +insert into t1 values ("This is a test "); +insert into t1 values ("Some sample data"); +insert into t1 values (" garbage "); +insert into t1 values (" This is a test "); +insert into t1 values ("This is a test"); +insert into t1 values ("Hello world"); +insert into t1 values ("Foo bar"); +insert into t1 values ("This is a test"); +insert into t1 values ("MySQL varchar test"); +insert into t1 values ("test MySQL varchar"); +insert into t1 values ("This is a long string to have some random length data included"); +insert into t1 values ("Short string"); +insert into t1 values ("VSS"); +insert into t1 values ("Some samples"); +insert into t1 values ("Bar foo"); +insert into t1 values ("Bye"); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 257 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 257 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 257 NULL 2 Using where; Using index +alter table t1 change v v varchar(255); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 258 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 258 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 258 NULL 2 Using where; Using index +alter table t1 change v v varchar(256); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 259 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 259 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 259 NULL 2 Using where; Using index +alter table t1 change v v varchar(257); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 260 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 260 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 260 NULL 2 Using where; Using index +alter table t1 change v v varchar(258); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 261 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 261 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 261 NULL 2 Using where; Using index +alter table t1 change v v varchar(259); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 262 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 262 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 262 NULL 2 Using where; Using index +alter table t1 change v v varchar(258); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 261 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 261 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 261 NULL 2 Using where; Using index +alter table t1 change v v varchar(257); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 260 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 260 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 260 NULL 2 Using where; Using index +alter table t1 change v v varchar(256); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 259 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 259 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 259 NULL 2 Using where; Using index +alter table t1 change v v varchar(255); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 258 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 258 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 258 NULL 2 Using where; Using index +alter table t1 change v v varchar(254); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 257 NULL 3 Using where; Using index +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 257 const 3 Using where; Using index +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 257 NULL 2 Using where; Using index +alter table t1 change v v varchar(253); +alter table t1 change v v varchar(254), drop key v; +alter table t1 change v v varchar(300), add key (v(10)); +select * from t1 where v like 'This is a test' order by v; +v +This is a test +This is a test +select * from t1 where v='This is a test' order by v; +v +This is a test +This is a test +This is a test +select * from t1 where v like 'S%' order by v; +v +Short string +Some sample data +Some samples +explain select * from t1 where v like 'This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL 4 Using where; Using filesort +explain select * from t1 where v='This is a test' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 13 const 4 Using where +explain select * from t1 where v like 'S%' order by v; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL 2 Using where; Using filesort +drop table t1; +create table t1 (pkcol varchar(16), othercol varchar(16), primary key (pkcol)); +insert into t1 values ('test', 'something'); +update t1 set othercol='somethingelse' where pkcol='test'; +select * from t1; +pkcol othercol +test somethingelse +drop table t1; +create table t1 (a int, b varchar(12)); +insert into t1 values (1, 'A'), (22, NULL); +create table t2 (a int); +insert into t2 values (22), (22); +select t1.a, t1.b, min(t1.b) from t1 inner join t2 ON t2.a = t1.a +group by t1.b, t1.a; +a b min(t1.b) +22 NULL NULL +drop table t1, t2; +create table t1 (f1 varchar(65500)); +create index index1 on t1(f1(10)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` varchar(65500) DEFAULT NULL, + KEY `index1` (`f1`(10)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 modify f1 varchar(255); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` varchar(255) DEFAULT NULL, + KEY `index1` (`f1`(10)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 modify f1 tinytext; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` tinytext DEFAULT NULL, + KEY `index1` (`f1`(10)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(f1 VARCHAR(100) DEFAULT 'test'); +INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3)); +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(f1 CHAR(100) DEFAULT 'test'); +INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3)); +DROP TABLE IF EXISTS t1; +drop table if exists t1, t2, t3; +create table t3 ( +id int(11), +en varchar(255) character set utf8, +cz varchar(255) character set utf8 +); +truncate table t3; +insert into t3 (id, en, cz) values +(1,'en string 1','cz string 1'), +(2,'en string 2','cz string 2'), +(3,'en string 3','cz string 3'); +create table t1 ( +id int(11), +name_id int(11) +); +insert into t1 (id, name_id) values (1,1), (2,3), (3,3); +create table t2 (id int(11)); +insert into t2 (id) values (1), (2), (3); +select t1.*, t2.id, t3.en, t3.cz from t1 left join t2 on t1.id=t2.id +left join t3 on t1.id=t3.id order by t3.id; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 t1 id id 3 11 1 Y 32768 0 63 +def test t1 t1 name_id name_id 3 11 1 Y 32768 0 63 +def test t2 t2 id id 3 11 1 Y 32768 0 63 +def test t3 t3 en en 253 255 11 Y 0 0 8 +def test t3 t3 cz cz 253 255 11 Y 0 0 8 +id name_id id en cz +1 1 1 en string 1 cz string 1 +2 3 2 en string 2 cz string 2 +3 3 3 en string 3 cz string 3 +drop table t1, t2, t3; +CREATE TABLE t1 (a CHAR(2)); +INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t'); +SELECT a,(a + 0) FROM t1 ORDER BY a; +a (a + 0) +10 10 +1a 1 +30 30 +50 50 +60 60 +t 0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1a' +Warning 1292 Truncated incorrect DOUBLE value: 't ' +SELECT a,(a DIV 2) FROM t1 ORDER BY a; +a (a DIV 2) +10 5 +1a 0 +30 15 +50 25 +60 30 +t 0 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '1a' +Warning 1918 Encountered illegal value '' when converting to DECIMAL +Warning 1292 Truncated incorrect DECIMAL value: 't ' +SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a; +a CAST(a AS SIGNED) +10 10 +1a 1 +30 30 +50 50 +60 60 +t 0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '1a' +Warning 1292 Truncated incorrect INTEGER value: 't' +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(16)); +INSERT INTO t1 VALUES ('5'), ('s'), (''); +SELECT 5 = a FROM t1; +5 = a +1 +0 +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 's' +Warning 1292 Truncated incorrect DOUBLE value: '' +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(16)); +INSERT INTO t1 VALUES ('5'), ('s'), (''); +SELECT 5 = a FROM t1; +5 = a +1 +0 +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 's ' +Warning 1292 Truncated incorrect DOUBLE value: ' ' +DROP TABLE t1; +# +# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 +# +set sql_mode=''; +CREATE TABLE t1 (c1 VARBINARY(65532)); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varbinary(65532) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65533)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 blob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65534)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 blob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65535)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 blob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARBINARY(65536)); +Warnings: +Note 1246 Converting column 'c1' from VARBINARY to BLOB +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumblob YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65532)); +DESCRIBE t1; +Field Type Null Key Default Extra +c1 varchar(65532) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65533)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65534)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65535)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 text YES NULL +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(65536)); +Warnings: +Note 1246 Converting column 'c1' from VARCHAR to TEXT +DESCRIBE t1; +Field Type Null Key Default Extra +c1 mediumtext YES NULL +DROP TABLE t1; +set sql_mode=default; +CREATE TABLE t1 (c1 VARCHAR(65536)); +ERROR 42000: Column length too big for column 'c1' (max = 65532); use BLOB or TEXT instead +# +# End of 5.5 tests +# +# +# MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns +# +CREATE TABLE t1 (c1 DATE PRIMARY KEY); +INSERT INTO t1 VALUES ('2001-01-01'); +CREATE TABLE t2 (c1 VARCHAR(20)); +INSERT INTO t2 VALUES ('2001-01-01'); +INSERT INTO t2 VALUES ('2001/01/01'); +SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1; +c1 +2001-01-01 +2001-01-01 +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1; +c1 +2001-01-01 +2001-01-01 +ALTER TABLE t2 ADD PRIMARY KEY(c1); +SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1; +c1 +2001-01-01 +2001-01-01 +EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 index PRIMARY PRIMARY 22 NULL 2 Using where; Using index +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1; +c1 +2001-01-01 +2001-01-01 +# t2 should NOT be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 index PRIMARY PRIMARY 22 NULL 2 Using where; Using index +DROP TABLE IF EXISTS t1,t2; +# +# MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases +# +CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET latin1, PRIMARY KEY(c1)); +INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'); +SELECT * FROM t1 WHERE c1=BINARY 'a'; +c1 +a +EXPLAIN SELECT * FROM t1 WHERE c1=BINARY 'a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 22 const 1 Using index +SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin; +c1 +a +EXPLAIN SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 22 const 1 Using index +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1)); +INSERT INTO t2 VALUES ('a'),('b'); +SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; +c1 c1 +a a +EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 Using index +ALTER TABLE t1 MODIFY c1 VARBINARY(10); +SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; +c1 c1 +a a +EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 Using index +DROP TABLE t1, t2; +CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t1 VALUES ('a'),('c'); +CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1)); +INSERT INTO t2 VALUES ('a'),('b'); +SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); +c1 +a +c +# t2 should be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +ALTER TABLE t1 MODIFY c1 VARBINARY(10); +SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); +c1 +a +c +# t2 should be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +DROP TABLE t1,t2; +# +# End of 10.0 tests +# |