MySQL Bugs: #12734: prepared statement may return incorrect result set for a select SQL request

Bug #12734	prepared statement may return incorrect result set for a select SQL request
Submitted:	22 Aug 2005 20:11	Modified:	24 Jan 2006 23:52
Reporter:	c dubet	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	4.1.15, 5.0.12, 5.0.19-BK	OS:	Linux (Linux (fedora core 3))
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
prepared statement is like
SELECT .... FROM tableXXX WHERE unuse_until <CURRENT_DATE AND length(formatted)<= ? AND formatted LIKE ?

the LIKE value in prepared statement is something like "%ER"

If we call a lot of times (>20 000 times) this prepared statement with different values, we get after a while a false result. Instead of sending all the data matching the "%ER" (for example) criteria, we have also strings that does not match it (but would match one with one letter less, like "%R")

OS : Linux fedora core 3 =Linux 2.6.10-1.770_FC3 #1 Thu Feb 24 14:00:06 EST 2005 i686 athlon i386 GNU/Linux
JAVA VM = sun 1.5.0_01
My sql version = 4.1.13-standard
connector/J = 3.1.10

How to repeat:
use my test class DBconnectionTest based on com.mysql.jdbc.util.BaseBugReport

java test files to demonstrate the bug

Attachment: bug_prepared_statement_MySQL.zip (application/zip, text), 75.62 KiB.

This is a server issue. I've attached a testcase for the developer to use while debugging this issue that is plain SQL.

The issue is present in 4.1.x (latest) and 5.0.x (latest).

A shorter test case (without extra columns and indices)

Attachment: 12734.sql.gz (application/x-gzip, text), 108.59 KiB.

The fix for the bug is:
kostja@dragonfly:~/work/mysql-4.1-12734/sql> bk diffs -u
===== item_cmpfunc.cc 1.204 vs edited =====
--- 1.204/sql/item_cmpfunc.cc	2006-01-08 20:07:47 +03:00
+++ edited/item_cmpfunc.cc	2006-01-14 04:17:38 +03:00
@@ -2524,6 +2524,12 @@
   return 0;
 }
 
+void Item_func_like::cleanup()
+{
+  Item_bool_func2::cleanup();
+  canDoTurboBM= FALSE;
+}
+
 #ifdef USE_REGEX
 
 bool
===== item_cmpfunc.h 1.112 vs edited =====
--- 1.112/sql/item_cmpfunc.h	2005-09-29 04:07:36 +04:00
+++ edited/item_cmpfunc.h	2006-01-14 04:18:05 +03:00
@@ -911,6 +911,7 @@
   cond_result eq_cmp_result() const { return COND_TRUE; }
   const char *func_name() const { return "like"; }
   bool fix_fields(THD *thd, struct st_table_list *tlist, Item **ref);
+  void cleanup();
 };
 
 #ifdef USE_REGEX

Support team: could you please verify that this patch fixes customer's issue?

I working on a reproducible test case to supply a complete changeset for the bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1071

Pushed into 4.1 tree, currently tagged 4.1.17; merged into 5.0 tree (5.0.19).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1087

OK, now I need to try it without debug to see that maybe it's a compilation flags -related issue.

Can't repeat with using a release build either.

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:

Dopcumented bugfix in 4.1.18 and 5.0.19 changelogs. Closed.