Bug #13919 Escape does not work as expected
Submitted: 11 Oct 2005 9:22 Modified: 19 Oct 2005 14:18
Reporter: Hakan Küçükyılmaz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.13-rc-nt,5.0.15 on Oct 3 OS:Microsoft Windows (Windows, Linux/x86)
Assigned to: Sergey Petrunya 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:28] Hakan Küçükyılmaz
Duplicate of #13920
[11 Oct 2005 10:16] Valeriy Kravchuk
Verified just as described on 5.0.13-rc-nt. 

Works OK on 5.0.15-BK (ChangeSet@1.2028.1.1, 2005-10-10 21:42:14+02:00, lars@mysql.com) on Linux:

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (
    ->   a varchar(4) NOT NULL default ' ',
    ->   PRIMARY KEY (a)
    -> ) ENGINE=InnoDB;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO t1 (a) VALUES ('AB%D');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

mysql> INSERT INTO t1 (a) VALUES ('AB#D');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D';

+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

mysql> ALTER TABLE t1 ENGINE MyISAM;
Query OK, 2 rows affected (0.02 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)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-rc |
+-----------+
1 row in set (0.00 sec)
[11 Oct 2005 18:24] Heikki Tuuri
InnoDB string comparison should be identical on Linux and Windows. I wonder whether this is some problem in the MySQL parser on Windows.

Is EXPLAIN SELECT the same for an InnoDB table as for a MyISAM table?

Regards,

Heikki
[12 Oct 2005 6:22] Hakan Küçükyılmaz
Hello Heikki,

I did following:
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';
EXPLAIN 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';
EXPLAIN SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';

ALTER TABLE t1 ENGINE MyISAM;
SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
EXPLAIN SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';

The output on MySQL 5.0.13-rc-nt running on Windows is:
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.02 sec)

mysql> SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY       | PRIMARY | 6       | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 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(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 6       | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
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)

mysql> EXPLAIN SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY       | PRIMARY | 6       | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

The output on 5.0.15 on Linux is:
[08:19] root@test>DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

5.0.15-rc-log
[08:19] root@test>CREATE TABLE t1 (
    ->   a varchar(4) NOT NULL DEFAULT ' ',
    ->   PRIMARY KEY (a)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

5.0.15-rc-log
[08:19] root@test>INSERT INTO t1 (a) VALUES ('AB%D');
Query OK, 1 row affected (0.09 sec)

5.0.15-rc-log
[08:19] root@test>SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

5.0.15-rc-log
[08:19] root@test>EXPLAIN SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY       | PRIMARY | 6       | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

5.0.15-rc-log
[08:19] root@test>INSERT INTO t1 (a) VALUES ('AB#D');
Query OK, 1 row affected (0.06 sec)

5.0.15-rc-log
[08:19] root@test>SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.04 sec)

5.0.15-rc-log
[08:20] root@test>EXPLAIN SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 6       | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.02 sec)

5.0.15-rc-log
[08:20] root@test>ALTER TABLE t1 ENGINE MyISAM;
Query OK, 2 rows affected (0.22 sec)
Records: 2  Duplicates: 0  Warnings: 0

5.0.15-rc-log
[08:20] root@test>SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.03 sec)

5.0.15-rc-log
[08:20] root@test>EXPLAIN SELECT count(*) FROM t1 WHERE a LIKE 'AB\%D';
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY       | PRIMARY | 6       | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
[13 Oct 2005 18:21] Heikki Tuuri
Hi!

I compiled 5.0.13 on Windows with Visual Studio 7.

mysql> SELECT count(*) from t1 WHERE a LIKE 'AB\%D';

MySQL passes a wrong key value to ha_innobase::index_read()

It is a VARCHAR field whose length is 3 bytes:

0x02C80000  03 00 41 42 25 20 94 a5 03 00 41 42 25 20 08 a5 00 00

key_len is 6, which is sensible: 2 bytes for the length field and 4 bytes for the actual characters.

find_flag is HA_READ_KEY_EXACT, which is sensible.

This is probably a MySQL bug, not an InnoDB bug.

I ran the MySQL client in an MS-DOS prompt. I wonder whether \% is parsed by the MS-DOS prompt interpreter in the same way as it is parsed by bash in Linux.

Regards,

Heikki
[13 Oct 2005 18:37] Heikki Tuuri
Hi!

The bug is present in a 10-day old 5.0.15 on Linux.

The below gdb traces explain why the bug appears with 2 rows in the table.

If the bug really is NOT present in the latest 5.0.15, what changeset has fixed it?

Regards,

Heikki

With 2 rows in the table:

[New Thread 163851 (LWP 30664)]
[Switching to Thread 163851 (LWP 30664)]

Breakpoint 2, ha_innobase::index_read(char*, char const*, unsigned, ha_rkey_func
tion) (this=0x8c60820, buf=0x8c60948 '¥' <repeats 16 times>,
    key_ptr=0x8c830e8 "\003", key_len=6, find_flag=HA_READ_KEY_EXACT)
    at ha_innodb.cc:3869
3869            row_prebuilt_t* prebuilt        = (row_prebuilt_t*) innobase_pre
built;
(gdb) next
3872            ulint           match_mode      = 0;
(gdb) x/10b 0x8c830e8
0x8c830e8:      0x03    0x00    0x41    0x42    0x25    0x20    0x00    0xa5
0x8c830f0:      0x03    0x00
(gdb) c
Continuing.

With 1 row in the table:

Breakpoint 2, ha_innobase::index_read(char*, char const*, unsigned, ha_rkey_func
tion) (this=0x8c60820, buf=0x8c60948 '¥' <repeats 16 times>, key_ptr=0x0,
    key_len=0, find_flag=HA_READ_AFTER_KEY) at ha_innodb.cc:3869
3869            row_prebuilt_t* prebuilt        = (row_prebuilt_t*) innobase_pre
built;
(gdb) c
Continuing.

Breakpoint 2, ha_innobase::index_read(char*, char const*, unsigned, ha_rkey_func
tion) (this=0x8c60820, buf=0x8c60948 "\004AB%D", '¥' <repeats 11 times>,
    key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at ha_innodb.cc:3869
3869            row_prebuilt_t* prebuilt        = (row_prebuilt_t*) innobase_pre
built;
(gdb)
[18 Oct 2005 9:10] Sergey Petrunya
I've verified that this bug was fixed by fix for BUG#13046.
[19 Oct 2005 14:18] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Same as Bug#13046. Documented fix in 4.1.15 and 5.0.15 changelogs.