php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #52745 Binding params doesn't work when selecting a date inside a CASE-WHEN
Submitted: 2010-08-30 20:12 UTC Modified: 2010-08-30 20:14 UTC
From: andrey@php.net Assigned: mysql
Status: Closed Package: MySQLi related
PHP Version: 5.3.3 OS: all
Private report: No CVE-ID:
 [2010-08-30 20:12 UTC] andrey@php.net
Description:
------------
This is MySQL Bug #49406 Binding params doesn't work when selecting a date inside a CASE-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"



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2010-08-30 20:14 UTC] andrey@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: mysql
 [2010-08-30 20:14 UTC] andrey@php.net
The fix will appear in 5.3.4
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Wed Apr 23 07:02:14 2014 UTC