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: christian dubettier 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

[22 Aug 2005 20:11] christian dubettier
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
[22 Aug 2005 20:22] christian dubettier
java test files to demonstrate the bug

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

[30 Aug 2005 19:26] Mark Matthews
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).
[14 Jan 2006 1:27] Konstantin Osipov
A shorter test case (without extra columns and indices)

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

[14 Jan 2006 1:29] Konstantin Osipov
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.
[14 Jan 2006 1:55] Bugs System
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
[14 Jan 2006 16:03] Konstantin Osipov
Pushed into 4.1 tree, currently tagged 4.1.17; merged into 5.0 tree (5.0.19).
[14 Jan 2006 18:20] Bugs System
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
[18 Jan 2006 17:33] Konstantin Osipov
OK, now I need to try it without debug to see that maybe it's a compilation flags -related issue.
[19 Jan 2006 17:47] Konstantin Osipov
Can't repeat with using a release build either.
[24 Jan 2006 23:52] 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:

Dopcumented bugfix in 4.1.18 and 5.0.19 changelogs. Closed.