drop table if exists t1;
create table t1 (x blob);
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('a','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','a'));
insert into t1 values (des_encrypt('ab','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','ab'));
insert into t1 values (des_encrypt('abc','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abc'));
insert into t1 values (des_encrypt('abcd','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcd'));
insert into t1 values (des_encrypt('abcde','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcde'));
insert into t1 values (des_encrypt('abcdef','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdef'));
insert into t1 values (des_encrypt('abcdefg','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefg'));
insert into t1 values (des_encrypt('abcdefgh','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefgh'));
insert into t1 values (des_encrypt('abcdefghi','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefghi'));
insert into t1 values (des_encrypt('abcdefghij','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefghij'));
insert into t1 values (des_encrypt('abcdefghijk','The quick red fox jumped over the lazy brown dog'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefghijk'));
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','sabakala'));
insert into t1 values (des_encrypt('quick red fox jumped over the lazy brown dog','sabakala'));
insert into t1 values (des_encrypt('red fox jumped over the lazy brown dog','sabakala'));
insert into t1 values (des_encrypt('fox jumped over the lazy brown dog','sabakala'));
insert into t1 values (des_encrypt('jumped over the lazy brown dog','sabakala'));
insert into t1 values (des_encrypt('over the lazy brown dog','sabakala'));
insert into t1 values (des_encrypt('the lazy brown dog','sabakala'));
insert into t1 values (des_encrypt('lazy brown dog','sabakala'));
insert into t1 values (des_encrypt('brown dog','sabakala'));
insert into t1 values (des_encrypt('dog','sabakala'));
insert into t1 values (des_encrypt('dog!','sabakala'));
insert into t1 values (des_encrypt('dog!!','sabakala'));
insert into t1 values (des_encrypt('dog!!!','sabakala'));
insert into t1 values (des_encrypt('dog!!!!','sabakala'));
insert into t1 values (des_encrypt('dog!!!!!','sabakala'));
insert into t1 values (des_encrypt('jumped over the lazy brown dog','sabakala'));
insert into t1 values (des_encrypt('jumped over the lazy brown dog','sabakala'));
select hex(x), hex(des_decrypt(x,'sabakala')) from t1;
hex(x)	hex(des_decrypt(x,'sabakala'))
FFA185A4656D113445E31D7A5B31BB57671A4CA9E21E63FE5D9C801E0CC7AA6190C13E269C2AE8D8060D3FB3FEA94FEC7FB006B9DCAC3E3E41	NULL
FFC620C3B84E926A54	NULL
FFA4F77D4220A16C980AF7CB576F8DC0A864F357825C16F329F24F66EBA775765B7C1538B90970740F853B556AEBD35AC31B962EA9B12B5AD4	NULL
FFACFF5921BB876A90	NULL
FFA6F18760E7CC5A4C325244B8486F692AAA11D229AF9ED4E4C9D56D7C0278C0DDA58F73E15E2B5F6C1DDD19B22B8071C454C930585449AAEB	NULL
FF3736DFEDC4B765F4	NULL
FF598681EA5F03CD6D6AEA2B118DF4885DD78BECDFB012BBB05386E436DC403D3CB9DE3BE8D5D3BB7FD90A1F9F9A3E055BB3B4FD3F6A869473	NULL
FF15B8B5952D630CAE	NULL
FF11EE3A400685226B76D5EC567681FA90247CE3C9DBE43341311C22F74562B1199957D80E300737791F6345BBC61AE03F28F52E5A6DCC78B2	NULL
FF0A832AE10DC85483	NULL
FF6F5C0BF4C5F899B4E7C091D9B1F1E92A7623B651B150CA3E7F420B4DD316D2C1BF76FCF9F9A046C000A9E21C106591E8C1930201B1750269	NULL
FFA08D5FB849A9FC90	NULL
FFF7331312FE153A39B1EC0D65BC1D3A0B6FCD49DA8C95D6161F53B11D297BAE142BCA6B9492DAE9A02AF455F16CA2C1CF4E1AD17297E947E2	NULL
FFDEE60A938478E059	NULL
FF95A729E73D5D87416A53055029E8CAA95B4F7B49F3D2D821A95D1FCE70F4B7A3226077176723F3DCC0A44D3B2EE9EFBC4D31AA87C948916C	NULL
FF1C78557F542A1FDC91943761B2EED14F	NULL
FF1E35B0775EEE512544A75BDAF58EA1655F5C899D3C5191A47263E2D11C3E688F662AB79F66D3B1DF9C75BD869EB8E04FDAE85719CB573A43	NULL
FF1C78557F542A1FDCDC4182B5314185E5	NULL
FF783123DCB36F98A51C39A560C92E129F1DDEEAB170825406A61260FBFBBFB0F2E48DB3282588A975C9C71E0EACA71A2B642A8C9C2E921A9F	NULL
FF1C78557F542A1FDCAC4B1B6B47206306	NULL
FF6D9B450837017D06CA1F1C9A0E700D03DEF06A4F954527A961CA805F70320E9F3F0007636B80768A253A5F7ADABC18B78F1A2FA560CC0B21	NULL
FF1C78557F542A1FDCE9038BD99DD43F2E	NULL
FF23FD03BA7548DD0957EBA7A8FBF7A18589762F3913E9A935BDA72F6F28202DC64572E0D633A54EA55BFD2C749E408C8632CCE36A7AE00619	NULL
FFD8DD3C4ABCB02FCDFE1383ECC0F61E7D02CD3BA72BBAEA26384D14835796501B3DC9A2F7EC2FC1633BDA6D56464536FE12010049C53A1991	54686520717569636B2072656420666F78206A756D706564206F76657220746865206C617A792062726F776E20646F67
FFACC5C5479575CBCA518B05778139B1BFC10F07299C98D04F580BC2F816828722D65A89C1831BD29DA626D319813BD374	717569636B2072656420666F78206A756D706564206F76657220746865206C617A792062726F776E20646F67
FF128D5517241DEEC631ABD2A47FA66E57930001417F18204328B0B2CB13F7AD2F50B8336EFAE7DE21	72656420666F78206A756D706564206F76657220746865206C617A792062726F776E20646F67
FF7CF971283B4DC2D050B3DB22684737B74B5B1CF12CF2FAC5A5995A298505F56D82BBFB9FC3E70059	666F78206A756D706564206F76657220746865206C617A792062726F776E20646F67
FF8333F3DD21E4488F967E03DD12394813A49F72848BB49473D3CB1C8A1AACF220	6A756D706564206F76657220746865206C617A792062726F776E20646F67
FFE8CB7FD80E6262C5FEB042A2DCC73B699CEEDCA6DC4458A0	6F76657220746865206C617A792062726F776E20646F67
FFA29334D7CDB1B403DF3EB992067DD524C7D568E8D98EBFE5	746865206C617A792062726F776E20646F67
FF4F0C5858FE2358D400E38831D5577C85	6C617A792062726F776E20646F67
FFB370CD6BAFD1CB95974D21DCCA2DD9D7	62726F776E20646F67
FF8F7777B28C7A459A	646F67
FF75213A4D7D01D715	646F6721
FF2DCAF574B173FB4D	646F672121
FFFA775787BE776B15	646F67212121
FF3FC2E42D7C840905	646F6721212121
FF9723312D26D9E6DA01D01A784A64DB9D	646F672121212121
FF8333F3DD21E4488F967E03DD12394813A49F72848BB49473D3CB1C8A1AACF220	6A756D706564206F76657220746865206C617A792062726F776E20646F67
FF8333F3DD21E4488F967E03DD12394813A49F72848BB49473D3CB1C8A1AACF220	6A756D706564206F76657220746865206C617A792062726F776E20646F67
select des_decrypt(x,'sabakala') as s from t1 having s like '%dog%';
s
The quick red fox jumped over the lazy brown dog
quick red fox jumped over the lazy brown dog
red fox jumped over the lazy brown dog
fox jumped over the lazy brown dog
jumped over the lazy brown dog
over the lazy brown dog
the lazy brown dog
lazy brown dog
brown dog
dog
dog!
dog!!
dog!!!
dog!!!!
dog!!!!!
jumped over the lazy brown dog
jumped over the lazy brown dog
drop table t1;
select hex(des_encrypt("hello")),des_decrypt(des_encrypt("hello"));
hex(des_encrypt("hello"))	des_decrypt(des_encrypt("hello"))
85D6DC8859F9759BBB	hello
select des_decrypt(des_encrypt("hello",4));
des_decrypt(des_encrypt("hello",4))
hello
select des_decrypt(des_encrypt("hello",'test'),'test');
des_decrypt(des_encrypt("hello",'test'),'test')
hello
select hex(des_encrypt("hello")),hex(des_encrypt("hello",5)),hex(des_encrypt("hello",'default_password'));
hex(des_encrypt("hello"))	hex(des_encrypt("hello",5))	hex(des_encrypt("hello",'default_password'))
85D6DC8859F9759BBB	85D6DC8859F9759BBB	FFD6DC8859F9759BBB
select des_decrypt(des_encrypt("hello"),'default_password');
des_decrypt(des_encrypt("hello"),'default_password')
hello
select des_decrypt(des_encrypt("hello",4),'password4');
des_decrypt(des_encrypt("hello",4),'password4')
hello
select des_encrypt("hello",10);
des_encrypt("hello",10)
NULL
Warnings:
Warning	1108	Incorrect parameters to procedure 'des_encrypt'
select des_encrypt(NULL);
des_encrypt(NULL)
NULL
select des_encrypt(NULL, 10);
des_encrypt(NULL, 10)
NULL
select des_encrypt(NULL, NULL);
des_encrypt(NULL, NULL)
NULL
select des_encrypt(10, NULL);
des_encrypt(10, NULL)
NULL
Warnings:
Warning	1108	Incorrect parameters to procedure 'des_encrypt'
select des_encrypt("hello", NULL);
des_encrypt("hello", NULL)
NULL
Warnings:
Warning	1108	Incorrect parameters to procedure 'des_encrypt'
select des_decrypt("hello",10);
des_decrypt("hello",10)
hello
select des_decrypt(NULL);
des_decrypt(NULL)
NULL
select des_decrypt(NULL, 10);
des_decrypt(NULL, 10)
NULL
select des_decrypt(NULL, NULL);
des_decrypt(NULL, NULL)
NULL
select des_decrypt(10, NULL);
des_decrypt(10, NULL)
10
select des_decrypt("hello", NULL);
des_decrypt("hello", NULL)
hello
SET @a=des_decrypt(des_encrypt("hello"));
flush des_key_file;
select @a = des_decrypt(des_encrypt("hello"));
@a = des_decrypt(des_encrypt("hello"))
1
select hex("hello");
hex("hello")
68656C6C6F
select hex(des_decrypt(des_encrypt("hello",4),'password2'));
hex(des_decrypt(des_encrypt("hello",4),'password2'))
NULL
select hex(des_decrypt(des_encrypt("hello","hidden")));
hex(des_decrypt(des_encrypt("hello","hidden")))
NULL
Warnings:
Warning	1108	Incorrect parameters to procedure 'des_decrypt'
explain extended select des_decrypt(des_encrypt("hello",4),'password2'), des_decrypt(des_encrypt("hello","hidden"));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select des_decrypt(des_encrypt('hello',4),'password2') AS `des_decrypt(des_encrypt("hello",4),'password2')`,des_decrypt(des_encrypt('hello','hidden')) AS `des_decrypt(des_encrypt("hello","hidden"))`
#
# Start of 10.1 tests
#
#
# MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
#
CREATE TABLE t1 (a INT(6) ZEROFILL);
INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=1 AND DES_ENCRYPT('test',a)=_latin1 'abc' COLLATE latin1_bin;
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` = 1 and des_encrypt('test',`test`.`t1`.`a`) = 'abc'
DROP TABLE t1;
#
# End of 10.1 tests
#
#
# Start of 10.2 tests
#
#
# MDEV-10134 Add full support for DEFAULT
#
CREATE TABLE t1 (
a VARCHAR(30),
b BLOB DEFAULT DES_ENCRYPT(a, 'passwd'),
c TEXT DEFAULT DES_DECRYPT(b, 'passwd')
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(30) DEFAULT NULL,
  `b` blob DEFAULT des_encrypt(`a`,'passwd'),
  `c` text DEFAULT des_decrypt(`b`,'passwd')
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
INSERT INTO t1 (a) VALUES ('test');
SELECT c FROM t1;
c
test
DROP TABLE t1;
#
# End of 10.2 tests
#