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)