|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2016-11-15 18:53 UTC] cmb@php.net
Description: ------------ Due to the implementation, interference of multiple result set objects retrieved by executing the same statement object are to be expected[1]. However, unsetting a result set while iterating over another shouldn't reset the other result set. [1] <http://svn.php.net/viewvc?view=revision&revision=341033> Test script: --------------- <?php $db = new SQLite3(':memory:'); $db->exec("CREATE TABLE foo (num int)"); $db->exec("INSERT INTO foo VALUES (0)"); $db->exec("INSERT INTO foo VALUES (1)"); $stmt = $db->prepare("SELECT * FROM foo WHERE NUM = ?"); $stmt->bindValue(1, 0, SQLITE3_INTEGER); $res1 = $stmt->execute(); while ($row = $res1->fetchArray(SQLITE3_ASSOC)) { var_dump($row); } $stmt->clear(); $stmt->reset(); $res1->finalize(); echo "finished first pass\n"; $stmt->bindValue(1, 1, SQLITE3_INTEGER); $res2 = $stmt->execute(); while ($row = $res2->fetchArray(SQLITE3_ASSOC)) { var_dump($row); unset($res1); } Expected result: ---------------- array(1) { ["num"]=> int(0) } finished first pass array(1) { ["num"]=> int(1) } Actual result: -------------- array(1) { ["num"]=> int(0) } finished first pass array(1) { ["num"]=> int(1) } array(1) { ["num"]=> int(1) } PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Thu Nov 21 11:01:29 2024 UTC |
The change referenced in this bug introduced a regression compared to earlier releases. Working: php-7.0.13 Fails: php-7.0.14 Description ---------------- Using php-7.0.14 with phpoffice/phpexcel with sqlite3 caching causes documents to fail to load. This is ultimately caused by phpexcel not using SQLite3Result::finalize (or reset) internally, within limited-lifetime functions. This is a significant change in behavior that may break deployed applications during system maintenance. sqlite3_step has two effects: - On a reset (or new) statement, the prepared statement will be executed, and the first row will be available. - On a statement VM that previously had sqlite3_step called, the next result row will be available, until the end of data is reached. sqlite3_reset resets the statement for execution, not any bound parameters. This also explains the original test script behavior: A step is peformed after a reset, which was implicitly called through unset. The statement was thus re-run, returning the first result, again. When fetchArray hits SQLITE_DONE, the statement isn't reset, and it shouldn't be. Otherwise, any while(haveData) loops would run forever. It appears, via static analysis, the correct solution is to sqlite3_reset and sqlite3_clear prior to re-binding parameters in sqlite3stmt::execute (in addition to this change). This will guarantee that each call to execute provides consistent results. Unfortunately, I do not have sufficient resources to build and test php at this time. Test Script ---------------- <?php $db = new SQLite3(':memory:'); $db->exec("CREATE TABLE foo (num int)"); $db->exec("INSERT INTO foo VALUES (0)"); $db->exec("INSERT INTO foo VALUES (1)"); $stmt = $db->prepare("SELECT * FROM foo WHERE NUM = ?"); function pass1($db, $stmt) { $stmt->bindValue(1, 0, SQLITE3_INTEGER); $res1 = $stmt->execute(); while ($row = $res1->fetchArray(SQLITE3_ASSOC)) { var_dump($row); } echo "finished first pass\n"; } function pass2($db, $stmt) { $stmt->bindValue(1, 1, SQLITE3_INTEGER); $res2 = $stmt->execute(); /* Oops, we forgot to reset in fetchArray, no effect */ while ($row = $res2->fetchArray(SQLITE3_ASSOC)) { var_dump($row); } } pass1($db, $stmt); pass2($db, $stmt); ?> Expected Result (>>php-7.0.13): ---------------- sqlitetest.php:12: array(1) { 'num' => int(0) } finished first pass sqlitetest.php:21: array(1) { 'num' => int(1) } Observed Result: ---------------- sqlitetest.php:12: array(1) { 'num' => int(0) } finished first pass sqlitetest.php:21: array(1) { 'num' => int(0) }An actually related issue: <?php $db = new SQLite3(':memory:'); // let's consider this to be already in the db $db->query("CREATE TABLE dog ( id INTEGER PRIMARY KEY, name TEXT, annoying INTEGER )"); $result = $db->exec("INSERT INTO dog VALUES (1, 'Annoying Dog', 1)"); // process some query $result = $db->query("SELECT 1"); $result->fetchArray(); unset($result); // now trigger an error $result = $db->exec("INSERT INTO dog VALUES (1, 'Annoying Dog', 1)"); var_dump($db->lastErrorCode()); ?> Output: Warning: SQLite3::exec(): UNIQUE constraint failed: dog.id in %s on line %d int(19) That is to be expected; however, if we did not unset($result) explicitly, the output would be: Warning: SQLite3::exec(): UNIQUE constraint failed: dog.id in %s on line %d int(0) In this case the error code is changed, because the SQLite3Result of the "SELECT 1" query is destroyed after the "INSERT INTO" query has been executed, and this triggers the sqlite3_reset() which causes the DB error to be set to zero.