Bug #78419 [Nab]: Incorrect fetch return value

Edit report at https://bugs.php.net/bug.php?id=78419&edit=1 ID: 78419 User updated by: ludwigdiehl at gmail dot com Reported by: ludwigdiehl at gmail dot com Summary: Incorrect fetch return value Status: Not a bug Type: Bug Package: PDO related Operating System: CentOS Linux release 7.6.1810 PHP Version: 7.3.8 Block user comment: N Private report: N New Comment: 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? Previous Comments: ------------------------------------------------------------------------ [2019-08-16 07:11:28] 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-16 00:57:08] 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. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=78419&edit=1