create table t1 (s1 binary(3));
insert into t1 values (0x61), (0x6120), (0x612020);
select hex(s1) from t1;
hex(s1)
610000
612000
612020
drop table t1;
create table t1 (s1 binary(2), s2 varbinary(2));
insert into t1 values (0x4100,0x4100);
select length(concat('*',s1,'*',s2,'*')) from t1;
length(concat('*',s1,'*',s2,'*'))
7
delete from t1;
insert into t1 values (0x4120,0x4120);
select length(concat('*',s1,'*',s2,'*')) from t1;
length(concat('*',s1,'*',s2,'*'))
7
drop table t1;
create table t1 (s1 varbinary(20), s2 varbinary(20));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `s1` varbinary(20) DEFAULT NULL,
  `s2` varbinary(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
insert into t1 values (0x41,0x4100),(0x41,0x4120),(0x4100,0x4120);
select hex(s1), hex(s2) from t1;
hex(s1)	hex(s2)
41	4100
41	4120
4100	4120
select count(*) from t1 where s1 < s2;
count(*)
3
drop table t1;
create table t1 (s1 varbinary(2), s2 varchar(1));
insert into t1 values (0x41,'a'), (0x4100,'b'), (0x41,'c'), (0x4100,'d');
select hex(s1),s2 from t1 order by s1,s2;
hex(s1)	s2
41	a
41	c
4100	b
4100	d
drop table t1;
create table t1 (s1 binary(2) primary key);
insert into t1 values (0x01);
insert into t1 values (0x0120);
insert into t1 values (0x0100);
ERROR 23000: Duplicate entry '\x01' for key 'PRIMARY'
select hex(s1) from t1 order by s1;
hex(s1)
0100
0120
select hex(s1) from t1 where s1=0x01;
hex(s1)
select hex(s1) from t1 where s1=0x0120;
hex(s1)
0120
select hex(s1) from t1 where s1=0x0100;
hex(s1)
0100
select count(distinct s1) from t1;
count(distinct s1)
2
alter table t1 drop primary key;
select hex(s1) from t1 where s1=0x01;
hex(s1)
select hex(s1) from t1 where s1=0x0120;
hex(s1)
0120
select hex(s1) from t1 where s1=0x0100;
hex(s1)
0100
select count(distinct s1) from t1;
count(distinct s1)
2
drop table t1;
create table t1 (s1 varbinary(2) primary key);
insert into t1 values (0x01);
insert into t1 values (0x0120);
insert into t1 values (0x0100);
select hex(s1) from t1 order by s1;
hex(s1)
01
0100
0120
select hex(s1) from t1 where s1=0x01;
hex(s1)
01
select hex(s1) from t1 where s1=0x0120;
hex(s1)
0120
select hex(s1) from t1 where s1=0x0100;
hex(s1)
0100
select count(distinct s1) from t1;
count(distinct s1)
3
alter table t1 drop primary key;
select hex(s1) from t1 where s1=0x01;
hex(s1)
01
select hex(s1) from t1 where s1=0x0120;
hex(s1)
0120
select hex(s1) from t1 where s1=0x0100;
hex(s1)
0100
select count(distinct s1) from t1;
count(distinct s1)
3
drop table t1;
select hex(cast(0x10 as binary(2)));
hex(cast(0x10 as binary(2)))
1000
create table t1 (b binary(2), vb varbinary(2));
insert into t1 values(0x4120, 0x4120);
insert ignore into t1 values(0x412020, 0x412020);
Warnings:
Warning	1265	Data truncated for column 'b' at row 1
Warning	1265	Data truncated for column 'vb' at row 1
drop table t1;
create table t1 (c char(2), vc varchar(2));
insert into t1 values(0x4120, 0x4120);
insert into t1 values(0x412020, 0x412020);
Warnings:
Note	1265	Data truncated for column 'vc' at row 1
drop table t1;
set @old_sql_mode= @@sql_mode, sql_mode= 'traditional';
create table t1 (b binary(2), vb varbinary(2));
insert into t1 values(0x4120, 0x4120);
insert into t1 values(0x412020, NULL);
ERROR 22001: Data too long for column 'b' at row 1
insert into t1 values(NULL, 0x412020);
ERROR 22001: Data too long for column 'vb' at row 1
drop table t1;
set @@sql_mode= @old_sql_mode;
create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null);
insert ignore into t1 set f1=1;
Warnings:
Warning	1364	Field 'f2' doesn't have a default value
Warning	1364	Field 'f3' doesn't have a default value
select hex(f2), hex(f3) from t1;
hex(f2)	hex(f3)
0000	
drop table t1;
End of 5.0 tests
#
# Start of 10.0 tests
#
#
# MDEV-8472 BINARY, VARBINARY and BLOB return different warnings on CAST to DECIMAL
#
SET NAMES utf8;
CREATE TABLE t1 (a BINARY(30));
INSERT INTO t1 VALUES ('1äÖüß@µ*$');
SELECT CAST(a AS DECIMAL) FROM t1;
CAST(a AS DECIMAL)
1
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00'
DROP TABLE t1;
CREATE TABLE t1 (a VARBINARY(30));
INSERT INTO t1 VALUES ('1äÖüß@µ*$');
SELECT CAST(a AS DECIMAL) FROM t1;
CAST(a AS DECIMAL)
1
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$'
DROP TABLE t1;
CREATE TABLE t1 (a BLOB);
INSERT INTO t1 VALUES ('1äÖüß@µ*$');
SELECT CAST(a AS DECIMAL) FROM t1;
CAST(a AS DECIMAL)
1
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$'
DROP TABLE t1;
#
# End of 10.0 tests
#
#
# Start of 10.5 tests
#
#
# MDEV-20818 ER_CRASHED_ON_USAGE or Assertion `length <= column->length' failed in write_block_record on temporary table
#
CREATE TABLE t1 (a VARCHAR(39));
SELECT
CAST(a AS BINARY(0)),
CAST(a AS BINARY(1)),
CAST(a AS BINARY(16)),
CAST(a AS BINARY(255)),
CAST(a AS BINARY(256)),
CAST(a AS BINARY(512)),
CAST(a AS BINARY(513)),
CAST(a AS BINARY(65532)),
CAST(a AS BINARY(65533)),
CAST(a AS BINARY(65534)),
CAST(a AS BINARY(65535)),
CAST(a AS BINARY(65536)),
CAST(a AS BINARY(16777215)),
CAST(a AS BINARY(16777216))
FROM t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					CAST(a AS BINARY(0))	253	0	0	Y	128	0	63
def					CAST(a AS BINARY(1))	253	1	0	Y	128	0	63
def					CAST(a AS BINARY(16))	253	16	0	Y	128	0	63
def					CAST(a AS BINARY(255))	253	255	0	Y	128	0	63
def					CAST(a AS BINARY(256))	253	256	0	Y	128	0	63
def					CAST(a AS BINARY(512))	253	512	0	Y	128	0	63
def					CAST(a AS BINARY(513))	253	513	0	Y	128	0	63
def					CAST(a AS BINARY(65532))	252	65532	0	Y	128	0	63
def					CAST(a AS BINARY(65533))	252	65533	0	Y	128	0	63
def					CAST(a AS BINARY(65534))	252	65534	0	Y	128	0	63
def					CAST(a AS BINARY(65535))	252	65535	0	Y	128	0	63
def					CAST(a AS BINARY(65536))	250	65536	0	Y	128	0	63
def					CAST(a AS BINARY(16777215))	250	16777215	0	Y	128	0	63
def					CAST(a AS BINARY(16777216))	251	16777216	0	Y	128	0	63
CAST(a AS BINARY(0))	CAST(a AS BINARY(1))	CAST(a AS BINARY(16))	CAST(a AS BINARY(255))	CAST(a AS BINARY(256))	CAST(a AS BINARY(512))	CAST(a AS BINARY(513))	CAST(a AS BINARY(65532))	CAST(a AS BINARY(65533))	CAST(a AS BINARY(65534))	CAST(a AS BINARY(65535))	CAST(a AS BINARY(65536))	CAST(a AS BINARY(16777215))	CAST(a AS BINARY(16777216))
DROP TABLE t1;
#
# MDEV-32203 Raise notes when an index cannot be used on data type mismatch
#
SET note_verbosity=unusable_keys;
CREATE TABLE t1 (indexed_col VARBINARY(32), KEY(indexed_col));
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));
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
DROP TABLE t2;
DROP TABLE t1;
SET note_verbosity=DEFAULT;