1# check 0x00 padding 2create table t1 (s1 binary(3)); 3insert into t1 values (0x61), (0x6120), (0x612020); 4select hex(s1) from t1; 5drop table t1; 6 7# check that 0x00 is not stripped in val_str 8create table t1 (s1 binary(2), s2 varbinary(2)); 9insert into t1 values (0x4100,0x4100); 10select length(concat('*',s1,'*',s2,'*')) from t1; 11delete from t1; 12insert into t1 values (0x4120,0x4120); 13select length(concat('*',s1,'*',s2,'*')) from t1; 14drop table t1; 15 16# check that trailing 0x00 and 0x20 do matter on comparison 17create table t1 (s1 varbinary(20), s2 varbinary(20)); 18show create table t1; 19insert into t1 values (0x41,0x4100),(0x41,0x4120),(0x4100,0x4120); 20select hex(s1), hex(s2) from t1; 21select count(*) from t1 where s1 < s2; 22drop table t1; 23 24# check that trailing 0x00 do matter on filesort 25create table t1 (s1 varbinary(2), s2 varchar(1)); 26insert into t1 values (0x41,'a'), (0x4100,'b'), (0x41,'c'), (0x4100,'d'); 27select hex(s1),s2 from t1 order by s1,s2; 28drop table t1; 29 30# check that 0x01 is padded to 0x0100 and thus we get a duplicate value 31create table t1 (s1 binary(2) primary key); 32insert into t1 values (0x01); 33insert into t1 values (0x0120); 34--error ER_DUP_ENTRY 35insert into t1 values (0x0100); 36select hex(s1) from t1 order by s1; 37# check index search 38select hex(s1) from t1 where s1=0x01; 39select hex(s1) from t1 where s1=0x0120; 40select hex(s1) from t1 where s1=0x0100; 41select count(distinct s1) from t1; 42alter table t1 drop primary key; 43# check non-indexed search 44select hex(s1) from t1 where s1=0x01; 45select hex(s1) from t1 where s1=0x0120; 46select hex(s1) from t1 where s1=0x0100; 47select count(distinct s1) from t1; 48drop table t1; 49 50# check that 0x01 is not padded, and all three values are unique 51create table t1 (s1 varbinary(2) primary key); 52insert into t1 values (0x01); 53insert into t1 values (0x0120); 54insert into t1 values (0x0100); 55select hex(s1) from t1 order by s1; 56# check index search 57select hex(s1) from t1 where s1=0x01; 58select hex(s1) from t1 where s1=0x0120; 59select hex(s1) from t1 where s1=0x0100; 60select count(distinct s1) from t1; 61alter table t1 drop primary key; 62# check non-indexed search 63select hex(s1) from t1 where s1=0x01; 64select hex(s1) from t1 where s1=0x0120; 65select hex(s1) from t1 where s1=0x0100; 66select count(distinct s1) from t1; 67drop table t1; 68 69# check that cast appends trailing zeros 70select hex(cast(0x10 as binary(2))); 71 72# 73# Bug #14299: BINARY space truncation should cause warning or error 74# 75create table t1 (b binary(2), vb varbinary(2)); 76insert into t1 values(0x4120, 0x4120); 77insert ignore into t1 values(0x412020, 0x412020); 78drop table t1; 79create table t1 (c char(2), vc varchar(2)); 80insert into t1 values(0x4120, 0x4120); 81insert into t1 values(0x412020, 0x412020); 82drop table t1; 83 84set @old_sql_mode= @@sql_mode, sql_mode= 'traditional'; 85create table t1 (b binary(2), vb varbinary(2)); 86insert into t1 values(0x4120, 0x4120); 87--error ER_DATA_TOO_LONG 88insert into t1 values(0x412020, NULL); 89--error ER_DATA_TOO_LONG 90insert into t1 values(NULL, 0x412020); 91drop table t1; 92set @@sql_mode= @old_sql_mode; 93 94# 95# Bug#14171: Wrong default value for a BINARY field 96# 97create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null); 98insert ignore into t1 set f1=1; 99select hex(f2), hex(f3) from t1; 100drop table t1; 101 102--echo End of 5.0 tests 103 104--echo # 105--echo # Start of 10.0 tests 106--echo # 107 108--echo # 109--echo # MDEV-8472 BINARY, VARBINARY and BLOB return different warnings on CAST to DECIMAL 110--echo # 111SET NAMES utf8; 112CREATE TABLE t1 (a BINARY(30)); 113INSERT INTO t1 VALUES ('1äÖüß@µ*$'); 114SELECT CAST(a AS DECIMAL) FROM t1; 115DROP TABLE t1; 116CREATE TABLE t1 (a VARBINARY(30)); 117INSERT INTO t1 VALUES ('1äÖüß@µ*$'); 118SELECT CAST(a AS DECIMAL) FROM t1; 119DROP TABLE t1; 120CREATE TABLE t1 (a BLOB); 121INSERT INTO t1 VALUES ('1äÖüß@µ*$'); 122SELECT CAST(a AS DECIMAL) FROM t1; 123DROP TABLE t1; 124 125--echo # 126--echo # End of 10.0 tests 127--echo # 128 129--echo # 130--echo # Start of 10.5 tests 131--echo # 132 133--echo # 134--echo # MDEV-20818 ER_CRASHED_ON_USAGE or Assertion `length <= column->length' failed in write_block_record on temporary table 135--echo # 136 137CREATE TABLE t1 (a VARCHAR(39)); 138--enable_metadata 139SELECT 140 CAST(a AS BINARY(0)), 141 CAST(a AS BINARY(1)), 142 CAST(a AS BINARY(16)), 143 CAST(a AS BINARY(255)), 144 CAST(a AS BINARY(256)), 145 CAST(a AS BINARY(512)), 146 CAST(a AS BINARY(513)), 147 CAST(a AS BINARY(65532)), 148 CAST(a AS BINARY(65533)), 149 CAST(a AS BINARY(65534)), 150 CAST(a AS BINARY(65535)), 151 CAST(a AS BINARY(65536)), 152 CAST(a AS BINARY(16777215)), 153 CAST(a AS BINARY(16777216)) 154FROM t1; 155--disable_metadata 156DROP TABLE t1; 157