path: root/mysql-test/main/type_varchar.test

--disable_warnings
drop table if exists t1, t2;
--enable_warnings

create table t1 (v varchar(30), c char(3), e enum('abc','def','ghi'), t text);
let $MYSQLD_DATADIR= `select @@datadir`;
copy_file $MYSQL_TEST_DIR/std_data/vchar.frm $MYSQLD_DATADIR/test/vchar.frm;
truncate table vchar;
show create table t1;
show create table vchar;
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;
select length(v),length(c),length(e),length(t) from vchar;
alter table vchar add i int;
show create table vchar;
select length(v),length(c),length(e),length(t) from vchar;
drop table t1, vchar;
create table t1 (v varchar(20));
insert into t1 values('a ');
select v='a' from t1;
select binary v='a' from t1;
select binary v='a ' from t1;
insert into t1 values('a');
--error ER_DUP_ENTRY
alter table t1 add primary key (v);
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;

#
# Test with varchar of lengths 254,255,256,258 & 258 to ensure we don't
# have any problems with varchar with one or two byte length_bytes
#

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");
let $i= 255;
let $j= 5;
while ($j)
{
  select * from t1 where v like 'This is a test' order by v;
  select * from t1 where v='This is a test' order by v;
  select * from t1 where v like 'S%' order by v;
  explain select * from t1 where v like 'This is a test' order by v;
  explain select * from t1 where v='This is a test' order by v;
  explain select * from t1 where v like 'S%' order by v;
  eval alter table t1 change v v varchar($i);
  inc $i;
  dec $j;
}
let $i= 258;
let $j= 6;
while ($j)
{
  select * from t1 where v like 'This is a test' order by v;
  select * from t1 where v='This is a test' order by v;
  select * from t1 where v like 'S%' order by v;
  explain select * from t1 where v like 'This is a test' order by v;
  explain select * from t1 where v='This is a test' order by v;
  explain select * from t1 where v like 'S%' order by v;
  eval alter table t1 change v v varchar($i);
  dec $i;
  dec $j;
}
alter table t1 change v v varchar(254), drop key v;

# Test with length(varchar) > 256 and key < 256 (to ensure things works with
# different kind of packing

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;
select * from t1 where v='This is a test' order by v;
select * from t1 where v like 'S%' order by v;
explain select * from t1 where v like 'This is a test' order by v;
explain select * from t1 where v='This is a test' order by v;
explain select * from t1 where v like 'S%' order by v;
drop table t1;

#
# bug#9339 - meaningless Field_varstring::get_key_image
#
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;
drop table t1;

#
# Bug #9489: problems with key handling
#

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;
drop table t1, t2;

#
# Bug #10543: convert varchar with index to text
#
create table t1 (f1 varchar(65500));
create index index1 on t1(f1(10));
show create table t1;
alter table t1 modify f1 varchar(255);
show create table t1;
alter table t1 modify f1 tinytext;
show create table t1;
drop table t1;

#
# BUG#15588: String overrun
#

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

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;

#
# Bug#14897 "ResultSet.getString("table.column") sometimes doesn't find the
# column"
# Test that after upgrading an old 4.1 VARCHAR column to 5.0 VARCHAR we preserve
# the original column metadata.
#
--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings

create table t3 (
  id int(11),
  en varchar(255) character set utf8,
  cz varchar(255) character set utf8
);
remove_file $MYSQLD_DATADIR/test/t3.frm;
copy_file $MYSQL_TEST_DIR/std_data/14897.frm $MYSQLD_DATADIR/test/t3.frm;
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);

# max_length is different for varchar fields in ps-protocol and we can't 
# replace a single metadata column, disable PS protocol
--disable_ps_protocol
--enable_metadata
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;
--disable_metadata
--enable_ps_protocol
drop table t1, t2, t3;

#
# Bug #11927: Warnings shown for CAST( chr as signed) but not (chr + 0)
#
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;
SELECT a,(a DIV 2) FROM t1 ORDER BY a;
SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a;
DROP TABLE t1;

#
# Bug #28299: To-number conversion warnings work differenly with CHAR
# and VARCHAR sp variables
#
# * Verify that 'Truncated incorrect DOUBLE value' is shown for 's' 
#   when using both CHAR and VARCHAR.
#

CREATE TABLE t1 (a VARCHAR(16));
INSERT INTO t1 VALUES ('5'), ('s'), ('');
SELECT 5 = a FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a CHAR(16));
INSERT INTO t1 VALUES ('5'), ('s'), ('');
SELECT 5 = a FROM t1;
DROP TABLE t1;

--echo #
--echo # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535
--echo #

set sql_mode='';
CREATE TABLE t1 (c1 VARBINARY(65532));
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARBINARY(65533));
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARBINARY(65534));
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARBINARY(65535));
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARBINARY(65536));
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARCHAR(65532));
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARCHAR(65533));
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARCHAR(65534));
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARCHAR(65535));
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARCHAR(65536));
DESCRIBE t1;
DROP TABLE t1;
set sql_mode=default;

--error ER_TOO_BIG_FIELDLENGTH
CREATE TABLE t1 (c1 VARCHAR(65536));

--echo #
--echo # End of 5.5 tests
--echo #

--echo #
--echo # MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns
--echo #
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;
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
ALTER TABLE t2 ADD PRIMARY KEY(c1);
SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
EXPLAIN SELECT t1.* FROM t1,t2 WHERE t1.c1=t2.c1;
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
--echo # t2 should NOT be eliminated
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
DROP TABLE IF EXISTS t1,t2;


--echo #
--echo # MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases
--echo #
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';
EXPLAIN SELECT * FROM t1 WHERE c1=BINARY 'a';
SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin;
EXPLAIN SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin;
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;
EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
ALTER TABLE t1 MODIFY c1 VARBINARY(10);
SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
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);
--echo # t2 should be eliminated
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
ALTER TABLE t1 MODIFY c1 VARBINARY(10);
SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
--echo # t2 should be eliminated
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
DROP TABLE t1,t2;

--echo #
--echo # End of 10.0 tests
--echo #

--echo #
--echo # MDEV-17551
--echo # Assertion `(&(&share->intern_lock)->m_mutex)->count > 0 &&
--echo # pthread_equal(pthread_self(), (&(&share->intern_lock)->m_mutex)->
--echo # thread)' failed in _ma_state_info_write or ER_CRASHED_ON_USAGE
--echo # upon SELECT with UNION
--echo #

CREATE TABLE t1 (b BLOB, vb BLOB AS (b) VIRTUAL);
INSERT INTO t1 (b) VALUES ('foobar');
SELECT 'foo' AS f1, CONVERT( 'bar' USING latin1 ) AS f2 FROM t1
  UNION
SELECT b AS f1, CONVERT( vb USING latin1 ) AS f2 FROM t1;
DROP TABLE t1;

--echo #
--echo # End of 10.3 tests
--echo #

SET sql_mode='';
--error ER_INVALID_DEFAULT
CREATE TABLE t1 (c VARCHAR(1) DEFAULT 'foo');
SHOW WARNINGS;

SET sql_mode='STRICT_ALL_TABLES';
--error ER_INVALID_DEFAULT
CREATE TABLE t1 (c VARCHAR(1) DEFAULT 'foo');
SHOW WARNINGS;

CREATE TABLE t1 (c VARCHAR(1));
SET sql_mode='';
--error ER_INVALID_DEFAULT
ALTER TABLE t1 ALTER column c SET DEFAULT 'foo';
SHOW WARNINGS;
SET sql_mode='STRICT_ALL_TABLES';
--error ER_INVALID_DEFAULT
ALTER TABLE t1 ALTER column c SET DEFAULT 'foo';
SHOW WARNINGS;
DROP TABLE t1;
SET sql_mode=DEFAULT;

--echo #
--echo # End of 10.4 tests
--echo #

--echo #
--echo # Start of 10.5 tests
--echo #

--echo #
--echo # MDEV-15592 Column COMPRESSED should select a 'high order' datatype
--echo #

#
# Old VARCHAR is automatically upgraded to new VARCHAR.
# So we don't have to override Type_handler_var_string::Key_part_spec_init_ft()
#
copy_file $MYSQL_TEST_DIR/std_data/vchar.frm $MYSQLD_DATADIR/test/vchar.frm;
TRUNCATE TABLE vchar;
SHOW CREATE TABLE vchar;
ALTER TABLE vchar ADD FULLTEXT INDEX(v);
SHOW CREATE TABLE vchar;
DROP TABLE vchar;

--echo #
--echo # End of 10.5 tests
--echo #