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;