Bug #69856 [Opn->Csd]: bindColumn() casts NULL to zero if PDO::PARAM_INT is used

Edit report at https://bugs.php.net/bug.php?id=69856&edit=1 ID: 69856 Updated by: adambaratz@php.net Reported by: abcaeffchen at gmail dot com Summary: bindColumn() casts NULL to zero if PDO::PARAM_INT is used -Status: Open +Status: Closed Type: Bug Package: PDO related Operating System: Windows PHP Version: 5.6.10 -Assigned To: +Assigned To: adambaratz Block user comment: N Private report: N New Comment: I had a duplicate ticket in for this issue (#73234). The fix I committed references the newer ticket. Previous Comments: ------------------------------------------------------------------------ [2016-09-08 23:10:28] cmb@php.net Sorry, I've overlooked your latest comment until now. Considering > Also notice, that with PDO::PARAM_STR can NULL be fetched from > the database. I'm switching back to "bug". At least both types should be handled consistently. ------------------------------------------------------------------------ [2016-07-10 14:51:58] abcaeffchen at gmail dot com If you turn off emulated prepared statements, the behavior is as expected. So I still think that this is a bug in emulating the prepared statement. At least it is inconsistent. Also notice, that with PDO::PARAM_STR can NULL be fetched from the database. ------------------------------------------------------------------------ [2016-06-30 11:11:44] cmb@php.net In my opinion, this behavior is not a bug. You ask for an int, and you get one; same as (int) null === 0. Of course, the desired behavior (getting null or an int) would be useful, but other constants should be introduced for this (maybe PARAM_NULLABLE_INT or PARAM_NULL_OR_INT), if only for backward compatibility. Anyhow, changing to feature request. ------------------------------------------------------------------------ [2015-06-26 00:12:03] abcaeffchen at gmail dot com Corrected the summary ------------------------------------------------------------------------ [2015-06-17 03:07:13] abcaeffchen at gmail dot com Description: ------------ PDO fetches fields to strings by default. If the field contains NULL in database the returned value will also be null. But if using PDOStatement::bindColumn with PDO::PARAM_INT, all null values are casted to zero (as integer), so from the PHP side one cannot tell if the database field contains zero or null. The same happens with PDO::PARAM_BOOL. Here null is casted to false. There is a workaround to this: After turning off emulated prepared statements the returned values have the expected types. But this results in bug #69257. Test script: --------------- $db = new PDO('mysql:host=127.0.0.1;dbname=my_db;charset=UTF8',$user,$pass); // Let field contain NULL in all (matching) rows $stmt = $db->prepare('SELECT field FROM table WHERE other_field = ?'); $stmt->execute([1]); $stmt->bindColumn(1,$field,PDO::PARAM_INT); $stmt->fetch(PDO::FETCH_BOUND); var_dump($field); // 0 (integer) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=69856&edit=1