Description:
Escape does not work as expected on Windows with InnoDB.
How to repeat:
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
a varchar(4) NOT NULL default ' ',
PRIMARY KEY (a)
) ENGINE=InnoDB;
INSERT INTO t1 (a) VALUES ('AB%D');
SELECT count(*) from t1 WHERE a LIKE 'AB\%D';
INSERT INTO t1 (a) VALUES ('AB#D');
SELECT count(*) from t1 WHERE a LIKE 'AB\%D';
ALTER TABLE t1 ENGINE MyISAM;
SELECT count(*) from t1 WHERE a LIKE 'AB\%D';
Should return: 1, 1, 1 but it returns 1, 0, 1.
If no PRIMARY KEY is set it works.
Sample output on Windows:
mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE t1 (
-> a varchar(4) NOT NULL default ' ',
-> PRIMARY KEY (a)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> INSERT INTO t1 (a) VALUES ('AB%D');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> INSERT INTO t1 (a) VALUES ('AB#D');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE t1 (
-> a varchar(4) NOT NULL default ' ',
-> PRIMARY KEY (a)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> INSERT INTO t1 (a) VALUES ('AB%D');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> INSERT INTO t1 (a) VALUES ('AB#D');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> ALTER TABLE t1 ENGINE MyISAM;
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
Description: Escape does not work as expected on Windows with InnoDB. How to repeat: USE test; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a varchar(4) NOT NULL default ' ', PRIMARY KEY (a) ) ENGINE=InnoDB; INSERT INTO t1 (a) VALUES ('AB%D'); SELECT count(*) from t1 WHERE a LIKE 'AB\%D'; INSERT INTO t1 (a) VALUES ('AB#D'); SELECT count(*) from t1 WHERE a LIKE 'AB\%D'; ALTER TABLE t1 ENGINE MyISAM; SELECT count(*) from t1 WHERE a LIKE 'AB\%D'; Should return: 1, 1, 1 but it returns 1, 0, 1. If no PRIMARY KEY is set it works. Sample output on Windows: mysql> USE test; Database changed mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE t1 ( -> a varchar(4) NOT NULL default ' ', -> PRIMARY KEY (a) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.08 sec) mysql> mysql> INSERT INTO t1 (a) VALUES ('AB%D'); Query OK, 1 row affected (0.03 sec) mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> mysql> INSERT INTO t1 (a) VALUES ('AB#D'); Query OK, 1 row affected (0.03 sec) mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> USE test; Database changed mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE t1 ( -> a varchar(4) NOT NULL default ' ', -> PRIMARY KEY (a) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.06 sec) mysql> mysql> INSERT INTO t1 (a) VALUES ('AB%D'); Query OK, 1 row affected (0.01 sec) mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> mysql> INSERT INTO t1 (a) VALUES ('AB#D'); Query OK, 1 row affected (0.03 sec) mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> mysql> ALTER TABLE t1 ENGINE MyISAM; Query OK, 2 rows affected (0.11 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)