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

CREATE DATABASE upgrade1 CHARACTER SET latin1;
USE upgrade1;

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/upgrade1/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;

DROP DATABASE upgrade1;
USE test;

#
# 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)) charset=latin1;
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)) charset=latin1;
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)) charset=latin1;
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)) charset=latin1;
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') CHARSET=latin1;
INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
DROP TABLE IF EXISTS t1;

CREATE TABLE t1(f1 CHAR(100) DEFAULT 'test') CHARSET=latin1;
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 #

CREATE DATABASE test1 CHARACTER SET latin1;
USE test1;

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));

DROP DATABASE test1;
USE test;

--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)) CHARSET=latin1;
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()
#

CREATE DATABASE upgrade1 CHARACTER SET latin1;
USE upgrade1;
copy_file $MYSQL_TEST_DIR/std_data/vchar.frm $MYSQLD_DATADIR/upgrade1/vchar.frm;
TRUNCATE TABLE vchar;
SHOW CREATE TABLE vchar;
ALTER TABLE vchar ADD FULLTEXT INDEX(v);
SHOW CREATE TABLE vchar;
DROP TABLE vchar;
DROP DATABASE upgrade1;
USE test;

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


--echo #
--echo # MDEV-32203 Raise notes when an index cannot be used on data type mismatch
--echo #

SET note_verbosity=unusable_keys;
CREATE TABLE t1 (indexed_col VARCHAR(32), KEY(indexed_col)) CHARSET=latin1;
DELIMITER $$;
FOR i IN 1..31
DO
  INSERT INTO t1 VALUES (20230100+i);
END FOR;
$$
DELIMITER ;$$
--source unusable_keys_literals.inc
--source unusable_keys_joins.inc
DROP TABLE t1;
SET note_verbosity=DEFAULT;


--echo #
--echo # MDEV-32957 Unusable key notes report wrong predicates for > and >=
--echo #

SET note_verbosity=unusable_keys;
CREATE TABLE t1 (a INT, i CHAR(32), KEY(i)) CHARSET=latin1;
DELIMITER $$;
FOR i IN 1..31
DO
  INSERT INTO t1 VALUES (i, 10+i);
END FOR;
$$
DELIMITER ;$$
EXPLAIN SELECT * FROM t1 WHERE i>30 ORDER BY i LIMIT 5;
EXPLAIN SELECT * FROM t1 WHERE i>=30 ORDER BY i LIMIT 5;
DROP TABLE t1;
SET note_verbosity=DEFAULT;


--echo #
--echo # MDEV-32958 Unusable key notes do not get reported for some operations
--echo #

SET note_verbosity=unusable_keys;
CREATE TABLE t1 (c1 varchar(10), KEY(c1)) CHARACTER SET latin1;
INSERT INTO t1 VALUES ('a');
INSERT INTO t1 VALUES ('b');
INSERT INTO t1 VALUES ('c');
INSERT INTO t1 VALUES ('d');
INSERT INTO t1 VALUES ('e');
INSERT INTO t1 VALUES ('f');
INSERT INTO t1 VALUES ('g');
INSERT INTO t1 VALUES ('h');
INSERT INTO t1 VALUES ('i');
INSERT INTO t1 VALUES ('j');

EXPLAIN SELECT * FROM t1 WHERE c1=10;
SELECT * FROM t1 WHERE c1=10;

EXPLAIN SELECT * FROM t1 WHERE c1<10;
SELECT * FROM t1 WHERE c1<10;

EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11;
SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11;

EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND '11';
SELECT * FROM t1 WHERE c1 BETWEEN 10 AND '11';

EXPLAIN SELECT * FROM t1 WHERE c1 IN (10,20);
SELECT * FROM t1 WHERE c1 IN (10,20);

EXPLAIN SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b');
SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b');

EXPLAIN SELECT * FROM t1 WHERE c1 IN ('a',_latin1'b' COLLATE latin1_german2_ci);
SELECT * FROM t1 WHERE c1 IN ('a',_latin1'b' COLLATE latin1_german2_ci);

DROP TABLE t1;


CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1)));
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
                          (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
                          (30),(31),(32),(33),(34),(35);

EXPLAIN SELECT * FROM t1 WHERE i >= 10 ORDER BY i LIMIT 5;
SELECT * FROM t1 WHERE i >= 10 ORDER BY i LIMIT 5;

EXPLAIN UPDATE t1 SET a = 1 WHERE i = 10 ORDER BY a, i LIMIT 5;
EXPLAIN UPDATE t1 SET a = 1 WHERE i < 10 ORDER BY a, i LIMIT 5;
EXPLAIN DELETE FROM t1 WHERE i = 10 ORDER BY a, i LIMIT 5;
EXPLAIN DELETE FROM t1 WHERE i < 10 ORDER BY a, i LIMIT 5;

EXPLAIN UPDATE t1 SET a = 1 WHERE i = 10;
EXPLAIN UPDATE t1 SET a = 1 WHERE i < 10;
EXPLAIN DELETE FROM t1 WHERE i = 10;
EXPLAIN DELETE FROM t1 WHERE i < 10;

DROP TABLE t1;

SET note_verbosity=DEFAULT;


--echo #
--echo # MDEV-34123 CONCAT Function Returns Unexpected Empty Set in Query
--echo #

CREATE TABLE IF NOT EXISTS t0
(
  c0_1 VARCHAR(200),
  c0_2 TEXT
);
INSERT INTO t0 VALUES ('0.67882431850', '97966');
SELECT (CONCAT(t0.c0_1, t0.c0_2)) AS ref0 FROM t0 WHERE (CONCAT(t0.c0_1, t0.c0_2));
DROP TABLE t0;

CREATE TABLE t0 (c VARCHAR(200));
INSERT INTO t0 VALUES ('0.0'),('0.4'),('0.6');
--source include/search_condition.inc
DROP TABLE t0;


--echo #
--echo # End of 10.6 tests
--echo #