MySQL Bugs: #38125: Prepared statements silently return truncated strings

Bug #38125	Prepared statements silently return truncated strings
Submitted:	15 Jul 2008 8:14	Modified:	23 Jul 2008 8:49
Reporter:	Tanguy Ortolo	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.0.41, 5.0.51a, 5.0.51b	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	binding, Parameter, result, string, truncated, truncation

Description:
I discovered that some prepared statement, when executed, return truncated strings, without any warning or error.

I tested it under Microsoft Windows and Debian GNU/Linux, with C API calls as well as with the command line client: this problem only occurs when the server runs under Windows, no matter its version or what client is used.

I found that issue using a more complicated query, that I tried to reduce to its minimum, still leading to the same erroneous result.

How to repeat:
Connected to a server running under Microsoft Windows, execute the following statements:

CREATE TABLE debug(i INTEGER);
INSERT INTO  debug(i) VALUES(12), (42);

PREPARE stmt FROM "SELECT IF(SYSDATE() < '2008-12-12', ?, '')
                   FROM debug T0,debug T1
                   ORDER BY T0.i";
SET @param = "foobarbaz";
EXECUTE stmt USING @param;

I get this result:
+-------------------------------------+
| IF(SYSDATE() < '2008-12-12', ?, '') |
+-------------------------------------+
| foo                                 |
| foo                                 |
| foo                                 |
| foo                                 |
+-------------------------------------+
where I espected:
+-------------------------------------+
| IF(SYSDATE() < '2008-12-12', ?, '') |
+-------------------------------------+
| foobarbaz                           |
| foobarbaz                           |
| foobarbaz                           |
| foobarbaz                           |
+-------------------------------------+

Thank you for the bug report. I can't repeat with latest Windows server:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.66-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > use test
Database changed
mysql 5.0 > CREATE TABLE debug(i INTEGER);
Query OK, 0 rows affected (0.19 sec)

mysql 5.0 > INSERT INTO  debug(i) VALUES(12), (42);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > PREPARE stmt FROM "SELECT IF(SYSDATE() < '2008-12-12', ?, '')
    ">                    FROM debug T0,debug T1
    ">                    ORDER BY T0.i";
Query OK, 0 rows affected (0.05 sec)
Statement prepared

mysql 5.0 > SET @param = "foobarbaz";
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > EXECUTE stmt USING @param;
+-------------------------------------+
| IF(SYSDATE() < '2008-12-12', ?, '') |
+-------------------------------------+
| foobarbaz                           |
| foobarbaz                           |
| foobarbaz                           |
| foobarbaz                           |
+-------------------------------------+
4 rows in set (0.05 sec)

mysql 5.0 >

5.0.66: I did not test with that release, but would be glad to do so: is it available somewhere?

Actually, I did not mention that I run my server on a *32 bits* Windows XP.

Unfortunately it is not available at the moment. The version is our internal source code version.
But when we can't repeat it with that version, then it means that the bug already is fixed and you won't have this problem with our next upcoming release.

The new version 5.0.67 will be available soon.

I have the same problem with MySQL server 5.1 for Windows, 32 bits.
I am using InnoDB tables.