drop table if exists t1, t2;
CREATE DATABASE upgrade1 CHARACTER SET latin1;
USE upgrade1;
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 COLLATE=latin1_swedish_ci
show create table vchar;
Table	Create Table
vchar	CREATE TABLE `vchar` (
  `v` varchar(30)/*old*/ DEFAULT NULL,
  `c` char(3) DEFAULT NULL,
  `e` enum('abc','def','ghi') DEFAULT NULL,
  `t` text DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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;
DROP DATABASE upgrade1;
USE test;
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");
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	3	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	3	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	3	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	3	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	3	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	3	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	3	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	3	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	3	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	3	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	3	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	3	Using where; Using filesort
drop table t1;
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;
pkcol	othercol
test	somethingelse
drop table t1;
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;
a	b	min(t1.b)
22	NULL	NULL
drop table t1, t2;
create table t1 (f1 varchar(65500)) charset=latin1;
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
drop table t1;
DROP TABLE IF EXISTS t1;
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;
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	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 DECIMAL value: 's'
Warning	1292	Truncated incorrect DECIMAL 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 DECIMAL value: 's'
Warning	1292	Truncated incorrect DECIMAL value: ''
DROP TABLE t1;
#
# MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535
#
CREATE DATABASE test1 CHARACTER SET latin1;
USE test1;
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
DROP DATABASE test1;
USE test;
#
# 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)) 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;
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
Warnings:
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of type `varchar` = "`t1`.`c1`" of type `date`
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
Warnings:
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of type `varchar` = "`t1`.`c1`" of type `date`
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
#
#
# MDEV-17551
# Assertion `(&(&share->intern_lock)->m_mutex)->count > 0 &&
# pthread_equal(pthread_self(), (&(&share->intern_lock)->m_mutex)->
# thread)' failed in _ma_state_info_write or ER_CRASHED_ON_USAGE
# upon SELECT with UNION
#
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;
f1	f2
foo	bar
foobar	foobar
DROP TABLE t1;
#
# End of 10.3 tests
#
SET sql_mode='';
CREATE TABLE t1 (c VARCHAR(1) DEFAULT 'foo');
ERROR 42000: Invalid default value for 'c'
SHOW WARNINGS;
Level	Code	Message
Warning	1265	Data truncated for column 'c' at row 0
Error	1067	Invalid default value for 'c'
SET sql_mode='STRICT_ALL_TABLES';
CREATE TABLE t1 (c VARCHAR(1) DEFAULT 'foo');
ERROR 42000: Invalid default value for 'c'
SHOW WARNINGS;
Level	Code	Message
Warning	1265	Data truncated for column 'c' at row 0
Error	1067	Invalid default value for 'c'
CREATE TABLE t1 (c VARCHAR(1));
SET sql_mode='';
ALTER TABLE t1 ALTER column c SET DEFAULT 'foo';
ERROR 42000: Invalid default value for 'c'
SHOW WARNINGS;
Level	Code	Message
Warning	1265	Data truncated for column 'c' at row 0
Error	1067	Invalid default value for 'c'
SET sql_mode='STRICT_ALL_TABLES';
ALTER TABLE t1 ALTER column c SET DEFAULT 'foo';
ERROR 42000: Invalid default value for 'c'
SHOW WARNINGS;
Level	Code	Message
Warning	1265	Data truncated for column 'c' at row 0
Error	1067	Invalid default value for 'c'
DROP TABLE t1;
SET sql_mode=DEFAULT;
#
# End of 10.4 tests
#
#
# Start of 10.5 tests
#
#
# MDEV-15592 Column COMPRESSED should select a 'high order' datatype
#
CREATE DATABASE upgrade1 CHARACTER SET latin1;
USE upgrade1;
TRUNCATE TABLE vchar;
SHOW CREATE TABLE vchar;
Table	Create Table
vchar	CREATE TABLE `vchar` (
  `v` varchar(30)/*old*/ DEFAULT NULL,
  `c` char(3) DEFAULT NULL,
  `e` enum('abc','def','ghi') DEFAULT NULL,
  `t` text DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE vchar ADD FULLTEXT INDEX(v);
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,
  FULLTEXT KEY `v` (`v`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE vchar;
DROP DATABASE upgrade1;
USE test;
#
# End of 10.5 tests
#
#
# MDEV-32203 Raise notes when an index cannot be used on data type mismatch
#
SET note_verbosity=unusable_keys;
CREATE TABLE t1 (indexed_col VARCHAR(32), KEY(indexed_col)) CHARSET=latin1;
FOR i IN 1..31
DO
INSERT INTO t1 VALUES (20230100+i);
END FOR;
$$
SELECT * FROM t1 WHERE indexed_col=20230101;
indexed_col
20230101
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "20230101" of type `int`
SELECT * FROM t1 WHERE indexed_col=20230101102030;
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "20230101102030" of type `bigint`
SELECT * FROM t1 WHERE indexed_col=20230101102030.1;
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "20230101102030.1" of type `decimal`
SELECT * FROM t1 WHERE indexed_col=20230101102030.1e0;
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "20230101102030.1e0" of type `double`
SELECT * FROM t1 WHERE indexed_col='10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col='2001-01-01';
indexed_col
SELECT * FROM t1 WHERE indexed_col='2001-01-01 10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col=DATE'2001-01-01';
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "DATE'2001-01-01'" of type `date`
SELECT * FROM t1 WHERE indexed_col=TIME'10:20:30';
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "TIME'10:20:30'" of type `time`
Warning	1292	Truncated incorrect time value: '20230101'
Warning	1292	Truncated incorrect time value: '20230102'
Warning	1292	Truncated incorrect time value: '20230103'
Warning	1292	Truncated incorrect time value: '20230104'
Warning	1292	Truncated incorrect time value: '20230105'
Warning	1292	Truncated incorrect time value: '20230106'
Warning	1292	Truncated incorrect time value: '20230107'
Warning	1292	Truncated incorrect time value: '20230108'
Warning	1292	Truncated incorrect time value: '20230109'
Warning	1292	Truncated incorrect time value: '20230110'
Warning	1292	Truncated incorrect time value: '20230111'
Warning	1292	Truncated incorrect time value: '20230112'
Warning	1292	Truncated incorrect time value: '20230113'
Warning	1292	Truncated incorrect time value: '20230114'
Warning	1292	Truncated incorrect time value: '20230115'
Warning	1292	Truncated incorrect time value: '20230116'
Warning	1292	Truncated incorrect time value: '20230117'
Warning	1292	Truncated incorrect time value: '20230118'
Warning	1292	Truncated incorrect time value: '20230119'
Warning	1292	Truncated incorrect time value: '20230120'
Warning	1292	Truncated incorrect time value: '20230121'
Warning	1292	Truncated incorrect time value: '20230122'
Warning	1292	Truncated incorrect time value: '20230123'
Warning	1292	Truncated incorrect time value: '20230124'
Warning	1292	Truncated incorrect time value: '20230125'
Warning	1292	Truncated incorrect time value: '20230126'
Warning	1292	Truncated incorrect time value: '20230127'
Warning	1292	Truncated incorrect time value: '20230128'
Warning	1292	Truncated incorrect time value: '20230129'
Warning	1292	Truncated incorrect time value: '20230130'
Warning	1292	Truncated incorrect time value: '20230131'
SELECT * FROM t1 WHERE indexed_col=TIMESTAMP'2001-01-01 10:20:30';
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "TIMESTAMP'2001-01-01 10:20:30'" of type `datetime`
SELECT * FROM t1 WHERE indexed_col=0x00;
indexed_col
SELECT * FROM t1 WHERE indexed_col=_utf8mb3'0' COLLATE utf8mb3_bin;
indexed_col
Warnings:
Note	1105	Cannot use key parts with `test`.`t1`.`indexed_col` in the rewritten condition: `convert(``t1``.``indexed_col`` using utf8mb3) = _utf8mb3'0' collate utf8mb3_bin`
CREATE TABLE t2 (not_indexed_col INT);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	20230101
20230102	20230102
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `int`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col INT UNSIGNED);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	20230101
20230102	20230102
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `int unsigned`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `bigint`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT UNSIGNED);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `bigint unsigned`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DECIMAL(30,6));
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `decimal`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col FLOAT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `float`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DOUBLE);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `double`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATE);
INSERT INTO t2 VALUES ('2023-01-01'),('2023-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	2023-01-01
20230102	2023-01-02
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `date`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATETIME);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	2023-01-01 00:00:00
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `datetime`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col TIMESTAMP);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	2023-01-01 00:00:00
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `varchar` = "`t2`.`not_indexed_col`" of type `timestamp`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARBINARY(32));
INSERT INTO t2 VALUES (0x30),(0x31);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32)) CHARACTER SET latin1;
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32) CHARACTER SET utf8mb3);
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Note	1105	Cannot use key parts with `test`.`t1`.`indexed_col` in the rewritten condition: `convert(``t1``.``indexed_col`` using utf8mb3) = ``t2``.``not_indexed_col```
DROP TABLE t2;
DROP TABLE t1;
SET note_verbosity=DEFAULT;
#
# MDEV-32957 Unusable key notes report wrong predicates for > and >=
#
SET note_verbosity=unusable_keys;
CREATE TABLE t1 (a INT, i CHAR(32), KEY(i)) CHARSET=latin1;
FOR i IN 1..31
DO
INSERT INTO t1 VALUES (i, 10+i);
END FOR;
$$
EXPLAIN SELECT * FROM t1 WHERE i>30 ORDER BY i LIMIT 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	i	i	33	NULL	5	Using where
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "30" of type `int`
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "30" of type `int`
EXPLAIN SELECT * FROM t1 WHERE i>=30 ORDER BY i LIMIT 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	i	i	33	NULL	5	Using where
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "30" of type `int`
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "30" of type `int`
DROP TABLE t1;
SET note_verbosity=DEFAULT;
#
# MDEV-32958 Unusable key notes do not get reported for some operations
#
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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	c1	c1	13	NULL	10	Using where; Using index
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int`
SELECT * FROM t1 WHERE c1=10;
c1
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int`
Warning	1292	Truncated incorrect DECIMAL value: 'a'
Warning	1292	Truncated incorrect DECIMAL value: 'b'
Warning	1292	Truncated incorrect DECIMAL value: 'c'
Warning	1292	Truncated incorrect DECIMAL value: 'd'
Warning	1292	Truncated incorrect DECIMAL value: 'e'
Warning	1292	Truncated incorrect DECIMAL value: 'f'
Warning	1292	Truncated incorrect DECIMAL value: 'g'
Warning	1292	Truncated incorrect DECIMAL value: 'h'
Warning	1292	Truncated incorrect DECIMAL value: 'i'
Warning	1292	Truncated incorrect DECIMAL value: 'j'
EXPLAIN SELECT * FROM t1 WHERE c1<10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	c1	c1	13	NULL	10	Using where; Using index
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` < "10" of type `int`
SELECT * FROM t1 WHERE c1<10;
c1
a
b
c
d
e
f
g
h
i
j
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` < "10" of type `int`
Warning	1292	Truncated incorrect DECIMAL value: 'a'
Warning	1292	Truncated incorrect DECIMAL value: 'b'
Warning	1292	Truncated incorrect DECIMAL value: 'c'
Warning	1292	Truncated incorrect DECIMAL value: 'd'
Warning	1292	Truncated incorrect DECIMAL value: 'e'
Warning	1292	Truncated incorrect DECIMAL value: 'f'
Warning	1292	Truncated incorrect DECIMAL value: 'g'
Warning	1292	Truncated incorrect DECIMAL value: 'h'
Warning	1292	Truncated incorrect DECIMAL value: 'i'
Warning	1292	Truncated incorrect DECIMAL value: 'j'
EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	c1	c1	13	NULL	10	Using where; Using index
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` >= "10" of type `int`
SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11;
c1
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` >= "10" of type `int`
Warning	1292	Truncated incorrect DECIMAL value: 'a'
Warning	1292	Truncated incorrect DECIMAL value: 'b'
Warning	1292	Truncated incorrect DECIMAL value: 'c'
Warning	1292	Truncated incorrect DECIMAL value: 'd'
Warning	1292	Truncated incorrect DECIMAL value: 'e'
Warning	1292	Truncated incorrect DECIMAL value: 'f'
Warning	1292	Truncated incorrect DECIMAL value: 'g'
Warning	1292	Truncated incorrect DECIMAL value: 'h'
Warning	1292	Truncated incorrect DECIMAL value: 'i'
Warning	1292	Truncated incorrect DECIMAL value: 'j'
EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND '11';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	c1	c1	13	NULL	10	Using where; Using index
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` >= "10" of type `int`
SELECT * FROM t1 WHERE c1 BETWEEN 10 AND '11';
c1
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` >= "10" of type `int`
Warning	1292	Truncated incorrect DOUBLE value: 'a'
Warning	1292	Truncated incorrect DOUBLE value: 'b'
Warning	1292	Truncated incorrect DOUBLE value: 'c'
Warning	1292	Truncated incorrect DOUBLE value: 'd'
Warning	1292	Truncated incorrect DOUBLE value: 'e'
Warning	1292	Truncated incorrect DOUBLE value: 'f'
Warning	1292	Truncated incorrect DOUBLE value: 'g'
Warning	1292	Truncated incorrect DOUBLE value: 'h'
Warning	1292	Truncated incorrect DOUBLE value: 'i'
Warning	1292	Truncated incorrect DOUBLE value: 'j'
EXPLAIN SELECT * FROM t1 WHERE c1 IN (10,20);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	c1	c1	13	NULL	10	Using where; Using index
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int`
SELECT * FROM t1 WHERE c1 IN (10,20);
c1
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int`
Warning	1292	Truncated incorrect DECIMAL value: 'a'
Warning	1292	Truncated incorrect DECIMAL value: 'b'
Warning	1292	Truncated incorrect DECIMAL value: 'c'
Warning	1292	Truncated incorrect DECIMAL value: 'd'
Warning	1292	Truncated incorrect DECIMAL value: 'e'
Warning	1292	Truncated incorrect DECIMAL value: 'f'
Warning	1292	Truncated incorrect DECIMAL value: 'g'
Warning	1292	Truncated incorrect DECIMAL value: 'h'
Warning	1292	Truncated incorrect DECIMAL value: 'i'
Warning	1292	Truncated incorrect DECIMAL value: 'j'
EXPLAIN SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	c1	c1	13	NULL	10	Using where; Using index
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of collation `latin1_swedish_ci` = "_latin1'a' collate latin1_german2_ci" of collation `latin1_german2_ci`
SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b');
c1
a
b
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of collation `latin1_swedish_ci` = "_latin1'a' collate latin1_german2_ci" of collation `latin1_german2_ci`
EXPLAIN SELECT * FROM t1 WHERE c1 IN ('a',_latin1'b' COLLATE latin1_german2_ci);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	c1	c1	13	NULL	10	Using where; Using index
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of collation `latin1_swedish_ci` = "'a'" of collation `latin1_german2_ci`
SELECT * FROM t1 WHERE c1 IN ('a',_latin1'b' COLLATE latin1_german2_ci);
c1
a
b
Warnings:
Note	1105	Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of collation `latin1_swedish_ci` = "'a'" of collation `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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	i	NULL	NULL	NULL	26	Using where; Using filesort
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int`
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int`
SELECT * FROM t1 WHERE i >= 10 ORDER BY i LIMIT 5;
a	i
NULL	10
NULL	11
NULL	12
NULL	13
NULL	14
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int`
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int`
EXPLAIN UPDATE t1 SET a = 1 WHERE i = 10 ORDER BY a, i LIMIT 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	Using where; Using filesort
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` = "10" of type `int`
EXPLAIN UPDATE t1 SET a = 1 WHERE i < 10 ORDER BY a, i LIMIT 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	Using where; Using filesort
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "10" of type `int`
EXPLAIN DELETE FROM t1 WHERE i = 10 ORDER BY a, i LIMIT 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	Using where; Using filesort
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` = "10" of type `int`
EXPLAIN DELETE FROM t1 WHERE i < 10 ORDER BY a, i LIMIT 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	Using where; Using filesort
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "10" of type `int`
EXPLAIN UPDATE t1 SET a = 1 WHERE i = 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	Using where
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` = "10" of type `int`
EXPLAIN UPDATE t1 SET a = 1 WHERE i < 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	Using where
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "10" of type `int`
EXPLAIN DELETE FROM t1 WHERE i = 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	Using where
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` = "10" of type `int`
EXPLAIN DELETE FROM t1 WHERE i < 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	Using where
Warnings:
Note	1105	Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "10" of type `int`
DROP TABLE t1;
SET note_verbosity=DEFAULT;
#
# MDEV-34123 CONCAT Function Returns Unexpected Empty Set in Query
#
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));
ref0
0.6788243185097966
DROP TABLE t0;
CREATE TABLE t0 (c VARCHAR(200));
INSERT INTO t0 VALUES ('0.0'),('0.4'),('0.6');
#
# WHERE <search condition>
#
CREATE TABLE t1 AS SELECT * FROM t0;
SELECT * FROM t1 WHERE c;
c
0.4
0.6
SELECT * FROM t1 WHERE c IS FALSE;
c
0.0
SELECT * FROM t1 WHERE c IS TRUE;
c
0.4
0.6
SELECT * FROM t1 WHERE COALESCE(c);
c
0.4
0.6
SELECT * FROM t1 WHERE CONCAT(c);
c
0.4
0.6
SELECT * FROM t1 WHERE LEFT(c,100);
c
0.4
0.6
DROP TABLE t1;
#
# HAVING <search condition>
#
CREATE TABLE t1 AS SELECT * FROM t0;
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING c2;
c2
0.4
0.6
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING c2 IS FALSE;
c2
0.0
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING c2 IS TRUE;
c2
0.4
0.6
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING COALESCE(c2);
c2
0.4
0.6
SELECT CONCAT(c,'0') AS c2 FROM t1 GROUP BY c2 HAVING LEFT(c2,100);
c2
0.40
0.60
DROP TABLE t1;
#
# <join condition> :=  ON <search condition>
#
CREATE TABLE t1 AS SELECT * FROM t0;
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (t1.c);
c
0.4
0.6
0.4
0.6
0.4
0.6
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (t1.c IS FALSE);
c
0.0
0.0
0.0
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (t1.c IS TRUE);
c
0.4
0.6
0.4
0.6
0.4
0.6
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (COALESCE(t1.c));
c
0.4
0.6
0.4
0.6
0.4
0.6
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (CONCAT(t1.c));
c
0.4
0.6
0.4
0.6
0.4
0.6
DROP TABLE t1;
#
# <delete statement: searched>
#   DELETE FROM <target table> [ WHERE <search condition> ]
#
CREATE TABLE t1 AS SELECT * FROM t0;
DELETE FROM t1 WHERE c;
SELECT * FROM t1;
c
0.0
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
DELETE FROM t1 WHERE c IS FALSE;
SELECT * FROM t1;
c
0.4
0.6
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
DELETE FROM t1 WHERE c IS TRUE;
SELECT * FROM t1;
c
0.0
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
DELETE FROM t1 WHERE COALESCE(c);
SELECT * FROM t1;
c
0.0
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
DELETE FROM t1 WHERE CONCAT(c);
SELECT * FROM t1;
c
0.0
DROP TABLE t1;
#
# <update statement: searched>
#   UPDATE <target table> SET <set clause list> [ WHERE <search condition> ]
CREATE TABLE t1 AS SELECT * FROM t0;
UPDATE t1 SET c=c+11 WHERE c;
SELECT * FROM t1;
c
0.0
11.4
11.6
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
UPDATE t1 SET c=c+11 WHERE c IS FALSE;
SELECT * FROM t1;
c
11
0.4
0.6
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
UPDATE t1 SET c=c+11 WHERE c IS TRUE;
SELECT * FROM t1;
c
0.0
11.4
11.6
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
UPDATE t1 SET c=c+11 WHERE COALESCE(c);
SELECT * FROM t1;
c
0.0
11.4
11.6
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
UPDATE t1 SET c=c+11 WHERE COALESCE(c);
SELECT * FROM t1;
c
0.0
11.4
11.6
DROP TABLE t1;
#
# <check constraint definition>
#   CHECK <left paren> <search condition> <right paren>
CREATE TABLE t1 LIKE t0;
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(c);
INSERT INTO t1 SELECT * FROM t0 WHERE NOT c;
ERROR 23000: CONSTRAINT `check0` failed for `test`.`t1`
INSERT INTO t1 SELECT * FROM t0 WHERE c;
SELECT * FROM t1;
c
0.4
0.6
DROP TABLE t1;
CREATE TABLE t1 LIKE t0;
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(c IS FALSE);
INSERT INTO t1 SELECT * FROM t0 WHERE c IS FALSE;
INSERT INTO t1 SELECT * FROM t0 WHERE c IS TRUE;
ERROR 23000: CONSTRAINT `check0` failed for `test`.`t1`
SELECT * FROM t1;
c
0.0
DROP TABLE t1;
CREATE TABLE t1 LIKE t0;
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(c IS TRUE);
INSERT INTO t1 SELECT * FROM t0 WHERE c IS FALSE;
ERROR 23000: CONSTRAINT `check0` failed for `test`.`t1`
INSERT INTO t1 SELECT * FROM t0 WHERE c IS TRUE;
SELECT * FROM t1;
c
0.4
0.6
DROP TABLE t1;
CREATE TABLE t1 LIKE t0;
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(COALESCE(c));
INSERT INTO t1 SELECT * FROM t0 WHERE c IS FALSE;
ERROR 23000: CONSTRAINT `check0` failed for `test`.`t1`
INSERT INTO t1 SELECT * FROM t0 WHERE c IS TRUE;
SELECT * FROM t1;
c
0.4
0.6
DROP TABLE t1;
#
# <case expression>
#   WHEN <search condition> THEN <result>
CREATE TABLE t1 AS SELECT * FROM t0;
SELECT c, CASE WHEN c THEN 'true' ELSE 'false' END AS c2 FROM t1;
c	c2
0.0	false
0.4	true
0.6	true
SELECT c, CASE WHEN COALESCE(c) THEN 'true' ELSE 'false' END AS c2 FROM t1;
c	c2
0.0	false
0.4	true
0.6	true
DROP TABLE t1;
DROP TABLE t0;
#
# End of 10.6 tests
#