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