Bug #72267 [Opn->Nab]: SQLite PDOStatement iterator problems

Edit report at https://bugs.php.net/bug.php?id=72267&edit=1 ID: 72267 Updated by: requinix@php.net Reported by: baptiste dot gaillard at gomoob dot com Summary: SQLite PDOStatement iterator problems -Status: Open +Status: Not a bug Type: Bug Package: PDO SQLite PHP Version: Irrelevant Block user comment: N Private report: N New Comment: PHP and PDO are just interfaces to the database engine. They simply just pass along whatever information is returned to them through the engine's API. So it's a SQLite problem. And they say this is undefined behavior: https://www.sqlite.org/isolation.html > No Isolation Between Operations On The Same Database Connection > ...The application can also INSERT new rows into the table while the SELECT > statement is running, but whether or not the new rows appear in subsequent > sqlite3_step()s of the query is undefined. And it's not just SELECT+INSERT too - concurrently DELETEing or UPDATEing can cause undefined behavior as well. Previous Comments: ------------------------------------------------------------------------ [2016-05-26 13:50:37] baptiste dot gaillard at gomoob dot com Description: ------------ This has been reported on Stackoverflow here http://stackoverflow.com/questions/37431600/pdo-sqlite-extension-pdostatement-bug but nobody provided a satisfying response. When we use an in memory SQLite and a PDOStatement inside a foreach loop the iterator state associated to the PDOStatement can be updated by other PDOStatement in the source code. Please test the provided test script provided with this case, this script shows that the PDOStatement used in a foreach loop can be modified by other PDOStatement objects. The output displays "1" and "3" but should display only "1" IMO. This behavior does not appear with MySQL so I think this behavior is clearly not expected and is very dangerous. The official PHP documentation indicates a PDOStatement "Represents a prepared statement and, after the statement is executed, an associated result set.". So we expect the PDOStatement to be something like an "in memory" object which embeds our results and which cannot be touched ("magically") by other peaces of code elsewhere. Test script: --------------- // Insert 2 rows inside our table $pdoStatementInsert = $pdo->prepare( 'insert into scheduled_task(id, task_class, date_and_time, task_parameters) values(?,?,?,?)' ); $pdoStatementInsert->execute([1, 'tk1', '2015-01-01', '{}']); $pdoStatementInsert->execute([2, 'tk2', '2015-01-02', '{}']); // Ensure the 2 rows were inserted $pdoStatementCount->execute(); var_dump('Then table has size \'' . intval($pdoStatementCount->fetchColumn()) . '\'.'); // Now create a statement to select only the first row $pdoStatementSelect1 = $pdo->query('select * from scheduled_task where date_and_time < \'2015-01-02\''); // Here it seems their is a bug with the Iterator associated to the PDO Statement foreach($pdoStatementSelect1 as $row) { var_dump($row['id']); // Inserts a new row inside our table $pdoStatementInsert = $pdo->prepare( 'insert into scheduled_task(id, task_class, date_and_time, task_parameters) values(?,?,?,?)' ); if($pdoStatementInsert->execute([3, 'tk3', '2015-01-01', '{}']) === false) { var_dump('Fail inserting row in loop !'); var_dump($pdoStatementInsert->errorCode()); var_dump($pdoStatementInsert->errorInfo()); } // Deletes the first row $pdo->query('delete from scheduled_task where id = ' . $row['id']); } // This fails with SQLite, we should have 2 rows inside our table here $pdoStatementCount->execute(); var_dump('At the end table has size \'' . intval($pdoStatementCount->fetchColumn()) . '\'.'); Expected result: ---------------- string(32) "At beginning table has size '0'." string(24) "Then table has size '2'." string(1) "1" string(30) "At the end table has size '2'." Actual result: -------------- string(32) "At beginning table has size '0'." string(24) "Then table has size '2'." string(1) "1" string(1) "3" string(30) "At the end table has size '1'." ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=72267&edit=1