MySQL Bugs: #13919: Escape does not work as expected

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:	Windows (Windows, Linux/x86)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

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)

Duplicate of #13920

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)

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

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)

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

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)

I've verified that this bug was fixed by fix for BUG#13046.

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.