1 files changed, 209 insertions, 0 deletions

diff --git a/mysql-test/main/func_like.test b/mysql-test/main/func_like.test
new file mode 100644
index 00000000000..5026bb76aa3
--- /dev/null
+++ b/mysql-test/main/func_like.test
@@ -0,0 +1,209 @@
+#
+# Test of like
+#
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+create table t1 (a varchar(10), key(a));
+insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
+explain extended select * from t1 where a like 'abc%';
+explain extended select * from t1 where a like concat('abc','%');
+select * from t1 where a like "abc%";
+select * from t1 where a like concat("abc","%");
+select * from t1 where a like "ABC%";
+select * from t1 where a like "test%";
+select * from t1 where a like "te_t";
+
+#
+# The following will test the Turbo Boyer-Moore code
+#
+select * from t1 where a like "%a%";
+select * from t1 where a like "%abcd%";
+select * from t1 where a like "%abc\d%";
+
+drop table t1;
+
+create table t1 (a varchar(10), key(a));
+
+#
+# Bug #2231
+#
+insert into t1 values ('a'), ('a\\b');
+select * from t1 where a like 'a\\%' escape '#';
+select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b';
+
+#
+# Bug #4200: Prepared statement parameter as argument to ESCAPE
+#
+prepare stmt1 from 'select * from t1 where a like \'a\\%\' escape ?';
+set @esc='#';
+execute stmt1 using @esc;  
+deallocate prepare stmt1;
+
+drop table t1;
+
+#
+# Bug #2885: like and datetime
+#
+
+create table t1 (a datetime);
+insert into t1 values ('2004-03-11 12:00:21');
+select * from t1 where a like '2004-03-11 12:00:21';
+drop table t1;
+
+#
+# Test like with non-default character set
+#
+
+SET NAMES koi8r;
+
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET koi8r);
+
+INSERT INTO t1 VALUES ('ÆÙ×Á'),('æÙ×Á'),('Æù×Á'),('ÆÙ÷Á'),('ÆÙ×á'),('æù÷á');
+INSERT INTO t1 VALUES ('ÆÙ×ÁÐÒÏÌÄÖ'),('æÙ×ÁÐÒÏÌÄÖ'),('Æù×ÁÐÒÏÌÄÖ'),('ÆÙ÷ÁÐÒÏÌÄÖ');
+INSERT INTO t1 VALUES ('ÆÙ×áÐÒÏÌÄÖ'),('ÆÙ×ÁðÒÏÌÄÖ'),('ÆÙ×ÁÐòÏÌÄÖ'),('ÆÙ×ÁÐÒïÌÄÖ');
+INSERT INTO t1 VALUES ('ÆÙ×ÁÐÒÏìÄÖ'),('ÆÙ×ÁÐÒÏÌäÖ'),('ÆÙ×ÁÐÒÏÌÄö'),('æù÷áðòïìäö');
+
+SELECT * FROM t1 WHERE a LIKE '%Æù×Á%';
+SELECT * FROM t1 WHERE a LIKE '%Æù×%';
+SELECT * FROM t1 WHERE a LIKE 'Æù×Á%';
+
+DROP TABLE t1;
+
+# Bug #2547 Strange "like" behaviour in tables with default charset=cp1250
+# Test like with non-default character set using TurboBM
+#
+SET NAMES cp1250;
+CREATE TABLE t1 (a varchar(250) NOT NULL) DEFAULT CHARACTER SET=cp1250;
+INSERT INTO t1 VALUES
+('Techni Tapes Sp. z o.o.'),
+('Pojazdy Szynowe PESA Bydgoszcz SA Holding'),
+('AKAPESTER 1 P.P.H.U.'),
+('Pojazdy Szynowe PESA Bydgoszcz S A Holding'),
+('PPUH PESKA-I Maria Struniarska');
+
+select * from t1 where a like '%PESA%';
+select * from t1 where a like '%PESA %';
+select * from t1 where a like '%PES%';
+select * from t1 where a like '%PESKA%';
+select * from t1 where a like '%ESKA%';
+DROP TABLE t1;
+
+#
+# LIKE crashed for binary collations in some cases
+#
+select _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin;
+
+#
+# Check 8bit escape character
+#
+set names koi8r;
+select 'andre%' like 'andreÊ%' escape 'Ê';
+
+# Check 8bit escape character with charset conversion:
+# For "a LIKE b ESCAPE c" expressions,
+# escape character is converted into the operation character set,
+# which is result of aggregation  of character sets of "a" and "b".
+# "c" itself doesn't take part in aggregation, because its collation
+# doesn't matter, escape character is always compared binary.
+# In the example below, escape character is converted from koi8r into cp1251:
+#
+select _cp1251'andre%' like convert('andreÊ%' using cp1251)  escape 'Ê';
+
+
+--echo End of 4.1 tests
+
+
+--echo #
+--echo # Bug #54575: crash when joining tables with unique set column
+--echo #
+CREATE TABLE t1(a SET('a') NOT NULL, UNIQUE KEY(a));
+CREATE TABLE t2(b INT PRIMARY KEY);
+INSERT IGNORE INTO t1 VALUES ();
+INSERT INTO t2 VALUES (1), (2), (3);
+SELECT 1 FROM t2 JOIN t1 ON 1 LIKE a GROUP BY a;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#59149 valgrind warnings with "like .. escape .." function
+--echo #
+--error ER_WRONG_ARGUMENTS
+SELECT '' LIKE '1' ESCAPE COUNT(1);
+
+--echo End of 5.1 tests
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-5445 Server crashes in Item_func_like::fix_fields on LIKE ExtractValue(..)
+--echo #
+SELECT 'a' LIKE REPEAT('',0);
+SELECT 'a' LIKE EXTRACTVALUE('bar','qux');
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8257 Erroneous "Impossible where" when mixing decimal comparison and LIKE
+--echo #
+CREATE TABLE t1 (a DECIMAL(8,2));
+INSERT INTO t1 VALUES (10),(20);
+SELECT * FROM t1 WHERE a=10.0;
+SELECT * FROM t1 WHERE a LIKE 10.00;
+SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-8599 "WHERE varchar_field LIKE temporal_const" does not use range optimizer
+--echo #
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('00:00:00');
+INSERT INTO t1 VALUES ('00:00:01');
+INSERT INTO t1 VALUES ('00:00:02');
+INSERT INTO t1 VALUES ('00:00:03');
+INSERT INTO t1 VALUES ('00:00:04');
+INSERT INTO t1 VALUES ('00:00:05');
+INSERT INTO t1 VALUES ('00:00:06');
+INSERT INTO t1 VALUES ('00:00:07');
+EXPLAIN SELECT * FROM t1 WHERE a LIKE '00:00:00';
+EXPLAIN SELECT * FROM t1 WHERE a LIKE TIME'00:00:00';
+SELECT * FROM t1 WHERE a LIKE '00:00:00';
+SELECT * FROM t1 WHERE a LIKE TIME'00:00:00';
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
+
+#
+# Item_func_line::print()
+#
+create view v1 as select 'foo!' like 'foo!!', 'foo!' like 'foo!!' escape '!';
+show create view v1;
+select * from v1;
+drop view v1;
+
+create table t1 (a varchar(100),
+  b int default (a like '%f\\_'),
+  c int default (a like '%f\\_' escape ''),
+  d int default (a like '%f\\_' escape '\\'));
+show create table t1;
+insert t1 (a) values ('1 f_'), ('1 f\\_');
+set sql_mode=no_backslash_escapes;
+insert t1 (a) values ('2 f_'), ('2 f\_');
+flush tables;
+insert t1 (a) values ('3 f_'), ('3 f\_');
+set sql_mode=default;
+select * from t1;
+drop table t1;