MySQL Bugs: #22122: Like query wildcards do not work with prepared statements.

Bug #22122	Like query wildcards do not work with prepared statements.
Submitted:	8 Sep 2006 15:45	Modified:	11 Sep 2006 13:36
Reporter:	emerson clarke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
Assum you have a database with a table called "hashes" which contains md4 hash values of url's.  When you perform a like query to select all the url's matching a particular site, you get results.  But when you perform the same query using a prepared statement, you get zero results, as though the wildcard % in the like query is ignored.

select * from hashes where url like 'http://cnn.com%';

How to repeat:
Using the mysql console, issue the following commands on the same table as described in the description.

prepare stmt FROM "select * from hashes where url like ?";
set @param = 'http://cnn.com%';;
execute stmt using @test;

And you will get zero results, where as the normal, non prepared statement returns results.

@test should read @param

Thank you for the report.

I can not repeat nor on Windows, nor on Linux using current BK sources:

mysql> create table hashes(url varchar(200));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into hashes values('http://cnn.com/index.html';), ('http://ntv.ru/index.php';);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> prepare stmt FROM "select * from hashes where url like ?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @param = 'http://cnn.com%';;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @param;
+---------------------------+
| url                       |
+---------------------------+
| http://cnn.com/index.html |
+---------------------------+
1 row in set (0.00 sec)

mysql>

I can't repeat it either this week.