php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #78419 Incorrect fetch return value
Submitted: 2019-08-16 00:57 UTC Modified: 2019-08-20 17:26 UTC
From: ludwigdiehl at gmail dot com Assigned:
Status: Not a bug Package: PDO related
PHP Version: 7.3.8 OS: CentOS Linux release 7.6.1810
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: ludwigdiehl at gmail dot com
New email:
PHP Version: OS:

 

 [2019-08-16 00:57 UTC] ludwigdiehl at gmail dot com
Description:
------------
If you create a prepared statement from a non-results query, you get the following return values after calling fetchAll and fetch respectively:

fetchAll: EMPTY ARRAY. Which is the desired return value.
fetch: FALSE. Should it not be NULL?

According to the documentation, "In all cases, FALSE is returned on failure but this is not an error isn't it?.

Test script:
---------------
$pdo = new PDO('mysql:host=x.x.x.x;dbname=xxx','user','password');
$stmt = $pdo->prepare('SELECT * FROM mytable WHERE 1=12');
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();
$result = $stmt->fetch();
var_dump($result);

$pdo = new PDO('mysql:host=x.x.x.x;dbname=xxx','user','password');
$stmt = $pdo->prepare('SELECT * FROM mytable WHERE 1=12');
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();
$result = $stmt->fetchAll();
var_dump($result);

Expected result:
----------------
I think NULL should be return value of the fetch method instead of FALSE for an empty result.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2019-08-16 07:11 UTC] requinix@php.net
-Status: Open +Status: Not a bug -Type: Feature/Change Request +Type: Bug
 [2019-08-16 07:11 UTC] requinix@php.net
fetch() returns false because there are no rows to retrieve. No rows is not an error, but attempting to fetch rows when there are not any (more) could be considered one. It is not a significant error, and using the false value to identify the end of the results is convenient, so PHP will not produce a warning. Many other functions also use false to indicate an end of data, and changing this behavior will break BC for code that tests the return value ===false while offering no real gain.

fetchAll() returns an empty array because there are no rows to retrieve. The resultset is empty so the array is empty. It is very common for queries to not return rows and having fetchAll() return false (or null) would be inconvenient in the many cases where the developer wants to foreach or count() the rows.
 [2019-08-20 17:26 UTC] ludwigdiehl at gmail dot com
Thanks for your reply. I have an example which produces same results even if there is an error.

/*DATABASE*/
/*Table structure for table `tbl_test` */

DROP TABLE IF EXISTS `tbl_test`;

CREATE TABLE `tbl_test` (
  `id_test` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `test` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_test`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `tbl_test` */
insert  into `tbl_test`(`id_test`,`test`,`created`) values (1,'3','2019-08-20 11:38:19'),(2,'1','2019-08-15 15:08:17'),(3,'1','2019-08-15 15:08:18'),(4,'1','2019-08-15 15:08:18');


/*SAMPLE CODE*/
try
{
    
    $host       = 'x.x.x.x';    
    $dbname     = 'dbname';
    $user       = 'user';
    $pwd        = 'pwd';
    $charset    = 'utf8'; 
    $pdo = new PDO(
        sprintf('mysql:host=%s;dbname=%s;charset=%s',
        $host,$dbname,$charset),
        $user,$pwd,[
        //PDO::ATTR_ERRMODE           => PDO::ERRMODE_EXCEPTION,
    		
    ]);
    $result = null;
    $queries = [
        'SELECT * FROM tbl_test WHERE 1=0', //EMPTY RESULTSET
        'SELECT id_test,test FROM tbl_test WHERE 1=1', //GOOD QUERY
        'SELECT * FROM tbl_test WHERE 1=1', //RESULTSET MUST HAVE 2 COLUMNS
        'SELECT * FROM tbl_test WHERE' //WRONG QUERY
    ];
    foreach($queries as $query)
    {
        $stmt = $pdo->prepare($query);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_KEY_PAIR);
        echo 'Result for query '.$query.' : '.json_encode($result)."\n";    
    }
}
catch(PDOException $e)
{
    echo $e->getMessage(); 
}

It will produce these results (without PDO::ERRMODE_EXCEPTION):

Result for query SELECT * FROM tbl_test WHERE 1=0 : false
Result for query SELECT id_test,test FROM tbl_test WHERE 1=1 : {"1":"3"}
<br />
<b>Warning</b>:  PDOStatement::fetch(): SQLSTATE[HY000]: General error: PDO::FETCH_KEY_PAIR fetch mode requires the result set to contain extactly 2 columns. in <b>/var/www/pruebas/ldiehl/2019/Database/index.php</b> on line <b>34</b><br />
Result for query SELECT * FROM tbl_test WHERE 1=1 : false
Result for query SELECT * FROM tbl_test WHERE : false

And these results (with PDO::ERRMODE_EXCEPTION):
Result for query SELECT * FROM tbl_test WHERE 1=0 : false
Result for query SELECT id_test,test FROM tbl_test WHERE 1=1 : {"1":"3"}
SQLSTATE[HY000]: General error: PDO::FETCH_KEY_PAIR fetch mode requires the result set to contain extactly 2 columns.


If somebody is not working with exceptions both the empty and the error will produce the same result is it not ambiguous?
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Tue Apr 13 03:01:23 2021 UTC