MySQL Bugs: #49406: Binding params doesn't work when selecting a date inside a CASE-WHEN

Bug #49406	Binding params doesn't work when selecting a date inside a CASE-WHEN
Submitted:	3 Dec 2009 15:56	Modified:	30 Aug 2010 19:39
Reporter:	Mark Plomer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connectors: mysqlnd ( PHP )	Severity:	S3 (Non-critical)
Version:	5.1.41	OS:	Any
Assigned to:	Andrey Hristov	CPU Architecture:	Any
Tags:	bind_result, case, date, execute, fetch, prepare, then, WHEN

Description:
When querying a date field (must be type "date"!) inside a CASE-WHEN block, the result-binding fails (does return only empty or incorrect values for all selected fields), when using the prepare()/execute() API-functions. When using the query() API-function instead, it works as expected.

Sample query:

SELECT CASE
  WHEN 0 THEN CAST('2009-12-03' AS DATE)
  ELSE CAST('2009-12-03' AS DATE)
END

When skipping the CASE-WHEN and selecting the ELSE-value directly, it works always as expected (with both API-functions):

SELECT CAST('2009-12-03' AS DATE)

In summary, this three conditions must be true, to reproduce the problem:
- Result-parameter binding using bind_result() must be used
- one selected column using CASE-WHEN
- the fields inside this CASE-WHEN must be from type date

How to repeat:
I reproduced this with PHP mysqli client, but I guess the root of the problem is independent from the client:

<?php

$query = 'SELECT
CASE
  WHEN 0 THEN CAST(\'2009-12-03\' AS DATE)
  ELSE CAST(\'2009-12-03\' AS DATE)
END';

$dbMysqli = new mysqli('host', 'username', 'password');

// via mysqli::query:
$result = $dbMysqli->query($query);
$rowViaMysqli = $result->fetch_row();
$valueViaMysqli = $rowViaMysqli[0];

echo "Fetched via mysqli::query():\n";
var_dump($valueViaMysqli);

// Always correct output:
// output: string(10) "2009-12-03"

// via mysqli::prepare/execute:
$stmt = $dbMysqli->prepare($query);
$stmt->execute();
$stmt->store_result();
$valueViaMysqli = null;
$stmt->bind_result($valueViaMysqli);
$stmt->fetch();

echo "\nFetched via mysqli::prepare/execute/store_result/bind_result/fetch:\n";
var_dump($valueViaMysqli);

// Different output on different clients/servers, but always wrong:
// output on PHP 5.2/MySQL 5.1.37: null
// output on PHP 5.3/MySQL 5.1.38: string(11) "12338-48-57"

Suggested fix:
No idea.

Thank you for the problem report. Please, check with a newer version of serve, 5.1.41, and inform about the results.

Oh sorry, now we upgraded to 5.1.41 and get the same results as with 5.1.38:

// output on PHP 5.3.1/MySQL 5.1.41: string(11) "12338-48-57"

Please, send my.cnf file content from your server. I can not repeat this neither with recent 5.1.43 nor with older 5.1.37, for example:

77-52-7-73:5.1 openxs$ php ../6.0-codebase/bug49406.php 
Fetched via mysqli::query():
string(10) "2009-12-03"

Fetched via mysqli::prepare/execute/store_result/bind_result/fetch:
string(10) "2009-12-03"
77-52-7-73:5.1 openxs$ php --version
PHP 5.2.6 (cli) (built: Oct  9 2008 22:44:00) 
Copyright (c) 1997-2008 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2008 Zend Technologies

It's the default my.cnf that I'm using. I checked this now with the windows version (server and client) and I could reproduce it there too, with a clean installation.

Now I tested with:
5.1.41-win32.msi (Windows XP, 32 bit)
5.1.41-linux-x86_64-glibc23.tar.gz (Debian lenny, amd64)
5.0.67-community-nt (Windows XP, 32 bit)

The only small difference makes the client version:
With PHP 5.2.6 I get: NULL
With PHP 5.3.1 I get: string(11) "12338-48-57"

Seems that the new mysqlnd in PHP 5.3 handles the problem in a different way, but returns garbage, too.

In short, I still didn't find any combination or configuration, where this could not be reproduced. If you still could not reproduce it, I'll do some more tests again.

I couldn't repeat too:

C:\tmp>php test.php
Fetched via mysqli::query():
string(10) "2009-12-03"

Fetched via mysqli::prepare/execute/store_result/bind_result/fetch:
NULL

C:\tmp>php --version
PHP 5.2.9-2 (cli) (built: Apr  9 2009 08:23:19)
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2009 Zend Technologies

C:\tmp>c:\dbs\5.1\bin\mysqld --version
c:\dbs\5.1\bin\mysqld  Ver 5.1.42-Win X64 for Win64 on unknown (Source distribution)

Now when you mentioned mysqlnd, what version of it do you use? Looks like it is more a driver problem than MySQL server's/C API/MySQL client library one...

@Miguel Solorzano: You said, you couldn't repeat it, but you DID it:

The first query returns string(10) "2009-12-03" (which is correct)
The second query returns NULL (which is wrong)

@Valeriy Kravchuk: I use "mysqlnd 5.0.5-dev" which is shipped with PHP 5.3.1

Sorry changing it to Verified.

Thanks :-)
But I'm not sure, if this is really a client bug. When you change the SELECT-CASE-WHEN-ELSE query to a simple "SELECT CAST('2009-12-03' AS DATE)", it works always as expected. I think from the client's point of view, the result-sets of this two querys should be identical.
Furthermore, as mentioned, it does not work correctly in mysqlnd AND in libmysqlclient (PHP 5.2.x)

I still think, that this is a server issue, so I changed category back to server. I apologize if this is really wrong.

Mark,

thank you for the feedback. But we could not repeat the problem using mysql command line client and/or C API test case. Please either provide such test case or we need to change this to PHP/mysqlnd again.

Test case can be output like below:

$mysql51
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1030
Server version: 5.1.43-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set @query="SELECT
    "> CASE
    ">   WHEN 0 THEN CAST(\'2009-12-03\' AS DATE)
    ">   ELSE CAST(\'2009-12-03\' AS DATE)
    "> END";
Query OK, 0 rows affected (0.01 sec)

mysql> prepare psmt from @query;
Query OK, 0 rows affected (0.33 sec)
Statement prepared

mysql> execute psmt;
+-------------------------------------------------------------------------------------+
| CASE
  WHEN 0 THEN CAST('2009-12-03' AS DATE)
  ELSE CAST('2009-12-03' AS DATE)
END |
+-------------------------------------------------------------------------------------+
| 2009-12-03                                                                          |
+-------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

As you see no problem with server in my case.

Okay, I could not reproduce it with the commandline, too. So, you change it back to mysqlnd :-)

Andrey, please have a look. Smells like a mysqlnd bug.

Fixed. Will appear in PHP 5.3.4