#
# Start of 5.5 tests
#
SET NAMES utf8mb4 COLLATE utf8mb4_bin;
SELECT COLLATION('a');
COLLATION('a')
utf8mb4_bin
SELECT 'a'   = 'a ' AS c;
c
1
SELECT 'a\0' = 'a'  AS c;
c
0
SELECT 'a\0' = 'a ' AS c;
c
0
SELECT 'a\t' = 'a'  AS c;
c
0
SELECT 'a\t' = 'a ' AS c;
c
0
SELECT 'a'   < 'a ' AS c;
c
0
SELECT 'a\0' < 'a'  AS c;
c
1
SELECT 'a\0' < 'a ' AS c;
c
1
SELECT 'a\t' < 'a'  AS c;
c
1
SELECT 'a\t' < 'a ' AS c;
c
1
SELECT 'a'   > 'a ' AS c;
c
0
SELECT 'a\0' > 'a'  AS c;
c
0
SELECT 'a\0' > 'a ' AS c;
c
0
SELECT 'a\t' > 'a'  AS c;
c
0
SELECT 'a\t' > 'a ' AS c;
c
0
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS a LIMIT 0;
EXECUTE IMMEDIATE
REPLACE(REPLACE(
"ALTER TABLE t1 ADD a_readable "
  "TEXT CHARACTER SET latin1 COLLATE DEFAULT "
  "GENERATED ALWAYS AS "
  "(RPAD(QUOTE(REGEXP_REPLACE(t1.a, '(\\\\x{00}|\\\\x{09})', '.')), 10))",
'DEFAULT', collation('a')),
'latin1', charset('a'));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `a_readable` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin GENERATED ALWAYS AS (rpad(quote(regexp_replace(`a`,'(\\x{00}|\\x{09})','.')),10)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 (a) VALUES ('a'),('a\0'),('a\t'),('a ');
SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1;
hex(a)	STRCMP(a,'a')	STRCMP(a,'a ')
61	0	0
6100	-1	-1
6109	-1	-1
6120	0	0
SELECT HEX(a), a_readable FROM t1 ORDER BY a, BINARY a;
HEX(a)	a_readable
6100	'a.'      
6109	'a.'      
61	'a'       
6120	'a '      
SELECT
HEX(t1.a) AS t1a,
CASE STRCMP(t1.a, t2.a) WHEN -1 THEN '<' WHEN 1 THEN '>' ELSE '=' END AS cmp,
HEX(t2.a) AS t2a,
t1.a_readable,
t2.a_readable
FROM t1 t1, t1 t2
ORDER BY BINARY t1.a, BINARY t2.a;
t1a	cmp	t2a	a_readable	a_readable
61	=	61	'a'       	'a'       
61	>	6100	'a'       	'a.'      
61	>	6109	'a'       	'a.'      
61	=	6120	'a'       	'a '      
6100	<	61	'a.'      	'a'       
6100	=	6100	'a.'      	'a.'      
6100	<	6109	'a.'      	'a.'      
6100	<	6120	'a.'      	'a '      
6109	<	61	'a.'      	'a'       
6109	>	6100	'a.'      	'a.'      
6109	=	6109	'a.'      	'a.'      
6109	<	6120	'a.'      	'a '      
6120	=	61	'a '      	'a'       
6120	>	6100	'a '      	'a.'      
6120	>	6109	'a '      	'a.'      
6120	=	6120	'a '      	'a '      
DROP TABLE t1;
#
# End of 5.5 tests
#
#
# Start of 5.6 tests
#
#
# WL#3664 WEIGHT_STRING
#
SET NAMES utf8mb4 COLLATE utf8mb4_bin;
select @@collation_connection;
@@collation_connection
utf8mb4_bin
CREATE TABLE t1 AS SELECT 'a' AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
000061
SELECT HEX(ws) FROM t2;
HEX(ws)
000061
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
000061000061000061000061000061
SELECT HEX(ws) FROM t2;
HEX(ws)
000061000061000061000061000061
DROP TABLE t2;
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(3)))
000061000061000061
SELECT HEX(ws) FROM t2;
HEX(ws)
000061000061000061
DROP TABLE t2;
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
000061000061000061000061000061000020000020000020000020000020
SELECT HEX(ws) FROM t2;
HEX(ws)
000061000061000061000061000061000020000020000020000020000020
DROP TABLE t2;
DROP TABLE t1;
select hex(weight_string('a'));
hex(weight_string('a'))
000061
select hex(weight_string('A'));
hex(weight_string('A'))
000041
select hex(weight_string('abc'));
hex(weight_string('abc'))
000061000062000063
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
000061000062
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
000061000062000063
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
000061000062000063000020000020
select hex(weight_string('abc', 1, 2, 0xC0));
hex(weight_string('abc', 1, 2, 0xC0))
00
select hex(weight_string('abc', 2, 2, 0xC0));
hex(weight_string('abc', 2, 2, 0xC0))
0000
select hex(weight_string('abc', 3, 2, 0xC0));
hex(weight_string('abc', 3, 2, 0xC0))
000061
select hex(weight_string('abc', 4, 2, 0xC0));
hex(weight_string('abc', 4, 2, 0xC0))
00006100
select hex(weight_string('abc', 5, 2, 0xC0));
hex(weight_string('abc', 5, 2, 0xC0))
0000610000
select hex(weight_string('abc',25, 2, 0xC0));
hex(weight_string('abc',25, 2, 0xC0))
00006100006200002000002000002000002000002000002000
select hex(weight_string('abc', 1, 3, 0xC0));
hex(weight_string('abc', 1, 3, 0xC0))
00
select hex(weight_string('abc', 2, 3, 0xC0));
hex(weight_string('abc', 2, 3, 0xC0))
0000
select hex(weight_string('abc', 3, 3, 0xC0));
hex(weight_string('abc', 3, 3, 0xC0))
000061
select hex(weight_string('abc', 4, 3, 0xC0));
hex(weight_string('abc', 4, 3, 0xC0))
00006100
select hex(weight_string('abc', 5, 3, 0xC0));
hex(weight_string('abc', 5, 3, 0xC0))
0000610000
select hex(weight_string('abc',25, 3, 0xC0));
hex(weight_string('abc',25, 3, 0xC0))
00006100006200006300002000002000002000002000002000
select hex(weight_string('abc', 1, 4, 0xC0));
hex(weight_string('abc', 1, 4, 0xC0))
00
select hex(weight_string('abc', 2, 4, 0xC0));
hex(weight_string('abc', 2, 4, 0xC0))
0000
select hex(weight_string('abc', 3, 4, 0xC0));
hex(weight_string('abc', 3, 4, 0xC0))
000061
select hex(weight_string('abc', 4, 4, 0xC0));
hex(weight_string('abc', 4, 4, 0xC0))
00006100
select hex(weight_string('abc', 5, 4, 0xC0));
hex(weight_string('abc', 5, 4, 0xC0))
0000610000
select hex(weight_string('abc',25, 4, 0xC0));
hex(weight_string('abc',25, 4, 0xC0))
00006100006200006300002000002000002000002000002000
select @@collation_connection;
@@collation_connection
utf8mb4_bin
select hex(weight_string(cast(_latin1 0x80 as char)));
hex(weight_string(cast(_latin1 0x80 as char)))
0020AC
select hex(weight_string(cast(_latin1 0x808080 as char)));
hex(weight_string(cast(_latin1 0x808080 as char)))
0020AC0020AC0020AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(2)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(2)))
0020AC0020AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(3)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(3)))
0020AC0020AC0020AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(5)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(5)))
0020AC0020AC0020AC000020000020
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0))
00
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0))
0020
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0))
0020AC
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0))
0020AC00
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0))
0020AC0020
select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0))
0020AC0020AC00002000002000002000002000002000002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0))
00
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0))
0020
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0))
0020AC
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0))
0020AC00
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0))
0020AC0020
select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0))
0020AC0020AC0020AC00002000002000002000002000002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0))
00
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0))
0020
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0))
0020AC
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0))
0020AC00
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0))
0020AC0020
select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0))
0020AC0020AC0020AC00002000002000002000002000002000
select @@collation_connection;
@@collation_connection
utf8mb4_bin
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
000061
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
000041
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
000061000062000063
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
000061000062
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
000061000062000063
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
000061000062000063000020000020
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
200000200000630000620000610000
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
FFFF9EFFFF9DFFFF9CFFFFDFFFFFDF
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
DFFFFFDFFFFF9CFFFF9DFFFF9EFFFF
#
# End of 5.6 tests
#
#
# Start of 10.0 tests
#
SET NAMES utf8mb4 COLLATE utf8mb4_bin;
#
# Bug#31081 server crash in regexp function
#
drop table if exists t1;
create table t1 as
select repeat(' ', 64) as s1, repeat(' ',64) as s2
union
select null, null;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `s1` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `s2` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
delete from t1;
insert into t1 values('aaa','aaa');
insert into t1 values('aaa|qqq','qqq');
insert into t1 values('gheis','^[^a-dXYZ]+$');
insert into t1 values('aab','^aa?b');
insert into t1 values('Baaan','^Ba*n');
insert into t1 values('aaa','qqq|aaa');
insert into t1 values('qqq','qqq|aaa');
insert into t1 values('bbb','qqq|aaa');
insert into t1 values('bbb','qqq');
insert into t1 values('aaa','aba');
insert into t1 values(null,'abc');
insert into t1 values('def',null);
insert into t1 values(null,null);
insert into t1 values('ghi','ghi[');
select HIGH_PRIORITY s1 regexp s2 from t1;
s1 regexp s2
0
0
0
1
1
1
1
1
1
1
NULL
NULL
NULL
NULL
drop table t1;
#
# Bug #3928 regexp [[:>:]] and UTF-8
#
SELECT @@character_set_client, @@collation_connection;
@@character_set_client	@@collation_connection
utf8mb4	utf8mb4_bin
# This should return TRUE
select  'вася'  rlike '\\bвася\\b';
'вася'  rlike '\\bвася\\b'
1
select  'вася ' rlike '\\bвася\\b';
'вася ' rlike '\\bвася\\b'
1
select ' вася'  rlike '\\bвася\\b';
' вася'  rlike '\\bвася\\b'
1
select ' вася ' rlike '\\bвася\\b';
' вася ' rlike '\\bвася\\b'
1
select  'вася'  rlike '[[:<:]]вася[[:>:]]';
'вася'  rlike '[[:<:]]вася[[:>:]]'
1
select  'вася ' rlike '[[:<:]]вася[[:>:]]';
'вася ' rlike '[[:<:]]вася[[:>:]]'
1
select ' вася'  rlike '[[:<:]]вася[[:>:]]';
' вася'  rlike '[[:<:]]вася[[:>:]]'
1
select ' вася ' rlike '[[:<:]]вася[[:>:]]';
' вася ' rlike '[[:<:]]вася[[:>:]]'
1
# This should return FALSE
select  'васяz' rlike '\\bвася\\b';
'васяz' rlike '\\bвася\\b'
0
select 'zвася'  rlike '\\bвася\\b';
'zвася'  rlike '\\bвася\\b'
0
select 'zвасяz' rlike '\\bвася\\b';
'zвасяz' rlike '\\bвася\\b'
0
select  'васяz' rlike '[[:<:]]вася[[:>:]]';
'васяz' rlike '[[:<:]]вася[[:>:]]'
0
select 'zвася'  rlike '[[:<:]]вася[[:>:]]';
'zвася'  rlike '[[:<:]]вася[[:>:]]'
0
select 'zвасяz' rlike '[[:<:]]вася[[:>:]]';
'zвасяz' rlike '[[:<:]]вася[[:>:]]'
0
#
# MDEV-7149 Constant condition propagation erroneously applied for LIKE
#
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('a'),('a ');
SELECT * FROM t1 WHERE CONCAT(c1)='a';
c1
a
a 
SELECT * FROM t1 WHERE CONCAT(c1) LIKE 'a ';
c1
a 
SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a ';
c1
a 
EXPLAIN EXTENDED SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE '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`.`c1` AS `c1` from `test`.`t1` where concat(`test`.`t1`.`c1`) = 'a' and concat(`test`.`t1`.`c1`) like 'a '
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('a'),('a ');
SELECT * FROM t1 WHERE 'a'=CONCAT(c1);
c1
a
a 
SELECT * FROM t1 WHERE 'a ' LIKE CONCAT(c1);
c1
a 
SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1);
c1
a 
EXPLAIN EXTENDED SELECT * FROM t1 WHERE 'a'=CONCAT(c1) AND 'a ' LIKE CONCAT(c1);
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`.`c1` AS `c1` from `test`.`t1` where 'a' = concat(`test`.`t1`.`c1`) and 'a ' like concat(`test`.`t1`.`c1`)
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('%'),('% ');
SELECT * FROM t1 WHERE '% '=CONCAT(c1);
c1
%
% 
SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1);
c1
%
SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1);
c1
%
EXPLAIN EXTENDED SELECT * FROM t1 WHERE '% '=CONCAT(c1) AND 'a' LIKE CONCAT(c1);
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`.`c1` AS `c1` from `test`.`t1` where '% ' = concat(`test`.`t1`.`c1`) and 'a' like concat(`test`.`t1`.`c1`)
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS c1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('%'),('% ');
SELECT * FROM t1 WHERE '%'=CONCAT(c1);
c1
%
% 
SELECT * FROM t1 WHERE 'a' LIKE CONCAT(c1);
c1
%
SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1);
c1
%
EXPLAIN EXTENDED SELECT * FROM t1 WHERE '%'=CONCAT(c1) AND 'a' LIKE CONCAT(c1);
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`.`c1` AS `c1` from `test`.`t1` where '%' = concat(`test`.`t1`.`c1`) and 'a' like concat(`test`.`t1`.`c1`)
DROP TABLE t1;
#
# MDEV-8694 Wrong result for SELECT..WHERE a NOT LIKE 'a ' AND a='a'
#
CREATE TABLE t1 AS SELECT SPACE(10) AS a LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('a'),('a ');
SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ';
a	LENGTH(a)
a	1
SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a';
a	LENGTH(a)
a	1
EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' 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`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a`  not like 'a '
DROP TABLE t1;
#
# End of MDEV-8694
#
#
# MDEV-7149 Constant condition propagation erroneously applied for LIKE
#
CREATE TABLE t1 AS SELECT REPEAT('a',10) AS c1 LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('ae'),('ä');
SELECT * FROM t1 WHERE c1='ä';
c1
ä
SELECT * FROM t1 WHERE c1 LIKE 'ae';
c1
ae
SELECT * FROM t1 WHERE c1='ä' AND c1 LIKE 'ae';
c1
EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1='ä' AND c1 LIKE 'ae';
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`.`c1` AS `c1` from `test`.`t1` where `test`.`t1`.`c1` = 'ä' and `test`.`t1`.`c1` like 'ae'
SELECT * FROM t1 WHERE CONCAT(c1)='ä';
c1
ä
SELECT * FROM t1 WHERE CONCAT(c1) LIKE 'ae';
c1
ae
SELECT * FROM t1 WHERE CONCAT(c1)='ä' AND CONCAT(c1) LIKE 'ae';
c1
EXPLAIN EXTENDED SELECT * FROM t1 WHERE CONCAT(c1)='ä' AND CONCAT(c1) LIKE 'ae';
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`.`c1` AS `c1` from `test`.`t1` where concat(`test`.`t1`.`c1`) = 'ä' and concat(`test`.`t1`.`c1`) like 'ae'
DROP TABLE IF EXISTS t1;
#
# End of 10.0 tests
#