path: root/mysql-test/main/type_set.result

drop table if exists t1;
create table t1 (a set (' ','a','b') not null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` set('','a','b') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1;
create table t1 (a set (' ','a','b ') not null default 'b ');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` set('','a','b') NOT NULL DEFAULT 'b'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1;
CREATE TABLE t1 (   user varchar(64) NOT NULL default '',   path varchar(255) NOT NULL default '',   privilege   set('select','RESERVED30','RESERVED29','RESERVED28','RESERVED27','RESERVED26',   'RESERVED25','RESERVED24','data.delete','RESERVED22','RESERVED21',   'RESERVED20','data.insert.none','data.insert.approve',   'data.insert.delete','data.insert.move','data.insert.propose',   'data.insert.reject','RESERVED13','RESERVED12','RESERVED11','RESERVED10',   'RESERVED09','data.update','RESERVED07','RESERVED06','RESERVED05',   'RESERVED04','metadata.delete','metadata.put','RESERVED01','RESERVED00')   NOT NULL default '',   KEY user (user)   ) ENGINE=MyISAM CHARSET=utf8;
DROP TABLE t1;
set names latin1;
create table t1 (s set ('a','A') character set latin1 collate latin1_bin);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `s` set('a','A') CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
insert into t1 values ('a'),('a,A'),('A,a'),('A');
select s from t1 order by s;
s
a
A
a,A
a,A
select s from t1 order by concat(s);
s
A
a
a,A
a,A
drop table t1;
CREATE TABLE t1 (c set('ae','oe','ue','ss') collate latin1_german2_ci);
INSERT INTO t1 VALUES ('�'),('�'),('�'),('�');
INSERT INTO t1 VALUES ('ae'),('oe'),('ue'),('ss');
INSERT INTO t1 VALUES ('�,�,�,�');
INSERT INTO t1 VALUES ('ae,oe,ue,ss');
SELECT c FROM t1 ORDER BY c;
c
ae
ae
oe
oe
ue
ue
ss
ss
ae,oe,ue,ss
ae,oe,ue,ss
SELECT c FROM t1 ORDER BY concat(c);
c
ae
ae
ae,oe,ue,ss
ae,oe,ue,ss
oe
oe
ss
ss
ue
ue
DROP TABLE t1;
create table t1(f1
set('1','2','3','4','5','6','7','8','9','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','36','37','38','39','40','41','42','43','44','45','46','47','48','49',
'50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','128'));
ERROR HY000: Too many strings for column f1 and SET
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
create table t1(f1
set('1','2','3','4','5','6','7','8','9','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','36','37','38','39','40','41','42','43','44','45','46','47','48','49',
'50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','1'));
Warnings:
Note	1291	Column 'f1' has duplicated value '1' in SET
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` set('1','2','3','4','5','6','7','8','9','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','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','1') DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1;
CREATE TABLE t1(c set('1','2','3','4','5','6','7','8','9','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','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64'));
INSERT INTO t1 VALUES(7);
INSERT INTO t1 VALUES(9223372036854775808);
SELECT * FROM t1;
c
1,2,3
64
DROP TABLE t1;
CREATE TABLE t1 (
set_unique_utf8 set ('a','b','c','d','e','f','g','h','i','j','k','l',
'm','n','o','p','q','r','s','t','u','v','w','x',
'y','z') CHARACTER SET utf8,
unique (set_unique_utf8)
);
INSERT INTO t1 ( set_unique_utf8 ) VALUES ( '' );
INSERT INTO t1 ( set_unique_utf8 ) VALUES ( '' );
ERROR 23000: Duplicate entry '' for key 'set_unique_utf8'
DROP TABLE t1;
End of 5.0 tests
#
# Start of 10.0 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 SET('2001-01-01','2001/01/01'));
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	1	NULL	2	Using where; Using index
Warnings:
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of type `set` = "`t1`.`c1`" of type `date`
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of type `set` = "'2001-01-01'" 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	1	NULL	2	Using where; Using index
Warnings:
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of type `set` = "`t1`.`c1`" of type `date`
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of type `set` = "'2001-01-01'" of type `date`
DROP TABLE t1, t2;
#
# MDEV-6978 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column
#
CREATE TABLE t1 (c1 ENUM('a') CHARACTER SET latin1 PRIMARY KEY);
INSERT INTO t1 VALUES ('a');
CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
INSERT INTO t2 VALUES ('a'),('A');
SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
ALTER TABLE t2 ADD PRIMARY KEY(c1);
SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
# t2 should NOT be eliminated
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=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	1	NULL	2	Using where; Using index
Warnings:
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of collation `latin1_bin` = "`t1`.`c1` collate latin1_swedish_ci" of collation `latin1_swedish_ci`
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of collation `latin1_bin` = "<cache>('a' collate latin1_swedish_ci)" of collation `latin1_swedish_ci`
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (c1 SET('a') CHARACTER SET latin1 PRIMARY KEY);
INSERT INTO t1 VALUES ('a');
CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
INSERT INTO t2 VALUES ('a'),('A');
SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
ALTER TABLE t2 ADD PRIMARY KEY(c1);
SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
# t2 should NOT be eliminated
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=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	1	NULL	2	Using where; Using index
Warnings:
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of collation `latin1_bin` = "`t1`.`c1` collate latin1_swedish_ci" of collation `latin1_swedish_ci`
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of collation `latin1_bin` = "<cache>('a' collate latin1_swedish_ci)" of collation `latin1_swedish_ci`
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY);
INSERT INTO t1 VALUES ('a');
CREATE TABLE t2 (c1 SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
INSERT INTO t2 VALUES ('a'),('A');
SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
ALTER TABLE t2 ADD PRIMARY KEY(c1);
SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1;
c1
a
a
# t2 should NOT be eliminated
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=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	1	NULL	2	Using where; Using index
Warnings:
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of collation `latin1_bin` = "`t1`.`c1` collate latin1_swedish_ci" of collation `latin1_swedish_ci`
Note	1105	Cannot use key `PRIMARY` part[0] for lookup: `test`.`t2`.`c1` of collation `latin1_bin` = "<cache>('a' collate latin1_swedish_ci)" of collation `latin1_swedish_ci`
DROP TABLE IF EXISTS t1,t2;
#
# MDEV-6993 Bad results with join comparing DECIMAL and ENUM/SET columns
#
CREATE TABLE t1 (c1 DECIMAL(10,1) PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (c1 SET('a','b'));
INSERT INTO t2 VALUES ('a'),('b');
SELECT t1.* FROM t1 NATURAL JOIN t2;
c1
1.0
2.0
ALTER TABLE t2 ADD PRIMARY KEY(c1);
SELECT t1.* FROM t1 NATURAL JOIN t2;
c1
1.0
2.0
SELECT t1.* FROM t1 LEFT OUTER JOIN t2 USING (c1);
c1
1.0
2.0
DROP TABLE t1,t2;
CREATE TABLE t1 (a DECIMAL(10,1), b SET('1','2'));
INSERT INTO t1 (a) VALUES (1),(2);
UPDATE t1 SET b=a;
SELECT * FROM t1;
a	b
1.0	1
2.0	2
ALTER TABLE t1 MODIFY a SET('1','2');
SELECT * FROM t1;
a	b
1	1
2	2
DROP TABLE t1;
#
# End of 10.0 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-8729 Wrong result for SELECT..WHERE HEX(enum_column)='61' AND enum_column='a '
#
CREATE TABLE t1 (a SET('a','A') CHARACTER SET latin1 COLLATE latin1_bin);
INSERT INTO t1 VALUES ('a'),('A');
SELECT * FROM t1 WHERE a='a ';
a
a
SELECT * FROM t1 WHERE HEX(a)='61';
a
a
SELECT * FROM t1 WHERE HEX(a)='61' AND a='a ';
a
a
# Can't propagate the equality into HEX(a), because binary collations still ignore trailing spaces
EXPLAIN EXTENDED SELECT * FROM t1 WHERE HEX(a)='61' AND a='a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a ' and hex(`test`.`t1`.`a`) = '61'
DROP TABLE t1;
CREATE TABLE t1 (a SET('a','a ') CHARACTER SET BINARY);
INSERT INTO t1 VALUES ('a'),('a ');
SELECT * FROM t1 WHERE a='a ';
a
a 
SELECT * FROM t1 WHERE HEX(a)='61';
a
a
SELECT * FROM t1 WHERE HEX(a)='61' AND a='a';
a
a
SELECT * FROM t1 WHERE HEX(a)='61' AND a='a ';
a
# Ok to propagate the equality into HEX(a), because "CHARACTER SET BINARY" does not ignore trailing spaces
EXPLAIN EXTENDED SELECT * FROM t1 WHERE HEX(a)='61' AND a='a';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a'
EXPLAIN EXTENDED SELECT * FROM t1 WHERE HEX(a)='61' AND a='a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
DROP TABLE t1;
#
# End of 10.1 tests
#
#
# Start of 10.3 tests
#
#
# MDEV-12432 Range optimizer for ENUM and SET does not return "Impossible WHERE" in some case
#
CREATE TABLE t1 (a SET('a','b','c','1'),KEY(a));
INSERT INTO t1 VALUES ('a'),('b'),('c'),('1');
EXPLAIN SELECT * FROM t1 WHERE a='xx';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT * FROM t1 WHERE a='99999999';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT * FROM t1 WHERE a=100.1e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT * FROM t1 WHERE a=100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT * FROM t1 WHERE a=100.1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT * FROM t1 WHERE a='100';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT * FROM t1 WHERE a='1x';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT * FROM t1 WHERE a='1.0';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT * FROM t1 WHERE a='1.1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
DROP TABLE t1;
#
# MDEV-11155 Bad error message when creating a SET column with comma and non-ASCII characters
#
SET NAMES utf8;
CREATE TABLE t1 (a SET('a,bü'));
ERROR 22007: Illegal set 'a,bü' value found during parsing
#
# MDEV-23323 Rounding functions return a wrong data type for a BIT, ENUM, SET argument
#
CREATE TABLE t1 (a SET('999999999999999999999999999999999999999999999999999999999999'));
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 AS
SELECT a, FLOOR(a), CEILING(a), TRUNCATE(a,0), ROUND(a) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` set('999999999999999999999999999999999999999999999999999999999999') DEFAULT NULL,
  `FLOOR(a)` int(5) unsigned DEFAULT NULL,
  `CEILING(a)` int(5) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` int(5) unsigned DEFAULT NULL,
  `ROUND(a)` int(5) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * FROM t2;
a	FLOOR(a)	CEILING(a)	TRUNCATE(a,0)	ROUND(a)
999999999999999999999999999999999999999999999999999999999999	1	1	1	1
DROP TABLE t2;
DROP TABLE t1;
#
# MDEV-32226 UBSAN shift exponent X is too large for 64-bit type 'long long int' in sql/field.cc
#
SET sql_mode='';
CREATE TABLE t (f SET('1','2','3','4','5','6','7','8','9','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1'));
Warnings:
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
Note	1291	Column 'f' has duplicated value '1' in SET
INSERT INTO t VALUES ('0');
DROP TABLE t;
#
# MDEV-32203 Raise notes when an index cannot be used on data type mismatch
#
SET note_verbosity=unusable_keys;
CREATE TABLE t1 (
indexed_col SET('2001','2002','2003','2004','2005','2006','2007','2008','2009','2010'),
KEY(indexed_col)
);
FOR i IN 1..10
DO
INSERT INTO t1 VALUES (CONCAT(2000+i));
END FOR;
$$
SELECT * FROM t1 WHERE indexed_col=20230101;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1e0;
indexed_col
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 `set` = "DATE'2001-01-01'" of type `date`
Warning	1292	Truncated incorrect datetime value: '2001'
Warning	1292	Truncated incorrect datetime value: '2002'
Warning	1292	Truncated incorrect datetime value: '2003'
Warning	1292	Truncated incorrect datetime value: '2004'
Warning	1292	Truncated incorrect datetime value: '2005'
Warning	1292	Truncated incorrect datetime value: '2006'
Warning	1292	Truncated incorrect datetime value: '2007'
Warning	1292	Truncated incorrect datetime value: '2008'
Warning	1292	Truncated incorrect datetime value: '2009'
Warning	1292	Truncated incorrect datetime value: '2010'
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 `set` = "TIME'10:20:30'" of type `time`
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 `set` = "TIMESTAMP'2001-01-01 10:20:30'" of type `datetime`
Warning	1292	Truncated incorrect datetime value: '2001'
Warning	1292	Truncated incorrect datetime value: '2002'
Warning	1292	Truncated incorrect datetime value: '2003'
Warning	1292	Truncated incorrect datetime value: '2004'
Warning	1292	Truncated incorrect datetime value: '2005'
Warning	1292	Truncated incorrect datetime value: '2006'
Warning	1292	Truncated incorrect datetime value: '2007'
Warning	1292	Truncated incorrect datetime value: '2008'
Warning	1292	Truncated incorrect datetime value: '2009'
Warning	1292	Truncated incorrect datetime value: '2010'
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
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
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
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
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
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
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
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
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `set` = "`t2`.`not_indexed_col`" of type `date`
Warning	1292	Truncated incorrect datetime value: '2001'
Warning	1292	Truncated incorrect datetime value: '2002'
Warning	1292	Truncated incorrect datetime value: '2003'
Warning	1292	Truncated incorrect datetime value: '2004'
Warning	1292	Truncated incorrect datetime value: '2005'
Warning	1292	Truncated incorrect datetime value: '2006'
Warning	1292	Truncated incorrect datetime value: '2007'
Warning	1292	Truncated incorrect datetime value: '2008'
Warning	1292	Truncated incorrect datetime value: '2009'
Warning	1292	Truncated incorrect datetime value: '2010'
Warning	1292	Truncated incorrect datetime value: '2001'
Warning	1292	Truncated incorrect datetime value: '2002'
Warning	1292	Truncated incorrect datetime value: '2003'
Warning	1292	Truncated incorrect datetime value: '2004'
Warning	1292	Truncated incorrect datetime value: '2005'
Warning	1292	Truncated incorrect datetime value: '2006'
Warning	1292	Truncated incorrect datetime value: '2007'
Warning	1292	Truncated incorrect datetime value: '2008'
Warning	1292	Truncated incorrect datetime value: '2009'
Warning	1292	Truncated incorrect datetime value: '2010'
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
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `set` = "`t2`.`not_indexed_col`" of type `datetime`
Warning	1292	Truncated incorrect datetime value: '2001'
Warning	1292	Truncated incorrect datetime value: '2002'
Warning	1292	Truncated incorrect datetime value: '2003'
Warning	1292	Truncated incorrect datetime value: '2004'
Warning	1292	Truncated incorrect datetime value: '2005'
Warning	1292	Truncated incorrect datetime value: '2006'
Warning	1292	Truncated incorrect datetime value: '2007'
Warning	1292	Truncated incorrect datetime value: '2008'
Warning	1292	Truncated incorrect datetime value: '2009'
Warning	1292	Truncated incorrect datetime value: '2010'
Warning	1292	Truncated incorrect datetime value: '2001'
Warning	1292	Truncated incorrect datetime value: '2002'
Warning	1292	Truncated incorrect datetime value: '2003'
Warning	1292	Truncated incorrect datetime value: '2004'
Warning	1292	Truncated incorrect datetime value: '2005'
Warning	1292	Truncated incorrect datetime value: '2006'
Warning	1292	Truncated incorrect datetime value: '2007'
Warning	1292	Truncated incorrect datetime value: '2008'
Warning	1292	Truncated incorrect datetime value: '2009'
Warning	1292	Truncated incorrect datetime value: '2010'
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
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `set` = "`t2`.`not_indexed_col`" of type `timestamp`
Warning	1292	Truncated incorrect datetime value: '2001'
Warning	1292	Truncated incorrect datetime value: '2002'
Warning	1292	Truncated incorrect datetime value: '2003'
Warning	1292	Truncated incorrect datetime value: '2004'
Warning	1292	Truncated incorrect datetime value: '2005'
Warning	1292	Truncated incorrect datetime value: '2006'
Warning	1292	Truncated incorrect datetime value: '2007'
Warning	1292	Truncated incorrect datetime value: '2008'
Warning	1292	Truncated incorrect datetime value: '2009'
Warning	1292	Truncated incorrect datetime value: '2010'
Warning	1292	Truncated incorrect datetime value: '2001'
Warning	1292	Truncated incorrect datetime value: '2002'
Warning	1292	Truncated incorrect datetime value: '2003'
Warning	1292	Truncated incorrect datetime value: '2004'
Warning	1292	Truncated incorrect datetime value: '2005'
Warning	1292	Truncated incorrect datetime value: '2006'
Warning	1292	Truncated incorrect datetime value: '2007'
Warning	1292	Truncated incorrect datetime value: '2008'
Warning	1292	Truncated incorrect datetime value: '2009'
Warning	1292	Truncated incorrect datetime value: '2010'
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
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of collation `latin1_swedish_ci` = "`t2`.`not_indexed_col`" of collation `binary`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32));
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-29062 Wrong result set metadata for a mix of INT+ENUM
#
CREATE TABLE t1
(
c_int INT,
c_set SET('1')
);
CREATE TABLE t2 AS SELECT c_int FROM t1 UNION SELECT c_set FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c_int` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE t2;
CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(c_int, c_set)  FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `COALESCE(c_int, c_set)` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE t2;
SELECT c_int FROM t1 UNION SELECT c_set FROM t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def				c_int	c_int	253	33	0	Y	0	0	33
c_int
SELECT COALESCE(c_int, c_set)  FROM t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					COALESCE(c_int, c_set)	253	33	0	Y	0	39	33
COALESCE(c_int, c_set)
DROP TABLE t1;