Bug #13920 Escape does not work as expected
Submitted: 11 Oct 2005 9:22 Modified: 11 Oct 2005 9:27
Reporter: Hakan Küçükyılmaz Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.13-rc-nt OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any

[11 Oct 2005 9:22] Hakan Küçükyılmaz
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)
[11 Oct 2005 9:27] Hartmut Holzgraefe
Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.