Bug #78813 [Opn->Ver]: SQLite ALTER TABLE query is returning columns

Edit report at https://bugs.php.net/bug.php?id=78813&edit=1 ID: 78813 Updated by: cmb@php.net Reported by: deus dot kane at claromentis dot com Summary: SQLite ALTER TABLE query is returning columns -Status: Open +Status: Verified Type: Bug Package: SQLite related Operating System: Windows PHP Version: 7.2.24 -Assigned To: +Assigned To: cmb Block user comment: N Private report: N New Comment: > (the docs are not particularly clear on this) While the sqlite3_column_count() docs[1] are indeed not as clear as would be desireable, the closely related sqlite3_column_*() docs[2] are: | These routines may only be called when the most recent call to | sqlite3_step() has returned SQLITE_ROW and neither | sqlite3_reset() nor sqlite3_finalize() have been called | subsequently. However, that is exactly what is happening in the sqlite3 extension, so this is not an upstream bug. Sorry for not having realized this earlier, but now it's too late to fix it for PHP 7.2, because that branch receives security fixes only[3]. [1] <https://www.sqlite.org/c3ref/column_count.html> [2] <https://www.sqlite.org/c3ref/column_blob.html> [3] <https://www.php.net/supported-versions.php> Previous Comments: ------------------------------------------------------------------------ [2019-11-14 13:04:47] cmb@php.net This behavioral change is caused by upgrading our bundled libsqlite to 3.28.0, and is an upstream issue. Not sure when exactly the behavior changed (must have been after 3.25.1), and whether this would be regarded as bug (the docs are not particularly clear on this). Anyway, pure C reproducer: #include <string.h> #include <stdio.h> #include <assert.h> #include <sqlite3.h> #define SQL_CREATE "CREATE TABLE users(id INTEGER PRIMARY KEY)" #define SQL_ALTER "ALTER TABLE users RENAME TO user_temp" int main() { sqlite3 *db; sqlite3_stmt *stmt; int rc; rc = sqlite3_open_v2(":memory:", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL); assert(rc == SQLITE_OK); rc = sqlite3_prepare_v2(db, SQL_CREATE, strlen(SQL_CREATE), &stmt, NULL); assert(rc == SQLITE_OK); rc = sqlite3_step(stmt); assert(rc == SQLITE_DONE); printf("%d\n", sqlite3_column_count(stmt)); rc = sqlite3_finalize(stmt); assert(rc == SQLITE_OK); rc = sqlite3_prepare_v2(db, SQL_ALTER, strlen(SQL_ALTER), &stmt, NULL); assert(rc == SQLITE_OK); rc = sqlite3_step(stmt); assert(rc == SQLITE_DONE); printf("%d\n", sqlite3_column_count(stmt)); rc = sqlite3_finalize(stmt); assert(rc == SQLITE_OK); rc = sqlite3_close_v2(db); assert(rc == SQLITE_OK); return 0; } ------------------------------------------------------------------------ [2019-11-14 11:34:50] deus dot kane at claromentis dot com Description: ------------ Calling SQLite3::query with a resultless query returns a mostly useless result object. Prior to PHP 7.2.24 calling SQLite3Result::numColumns on this result object would return zero, indicating that this was a query with no data to step through. In PHP 7.2.24, calling SQLite3Result::numColumns on this result object returns 1, making it indistingishable from a result object that contains data. In addition, when calling SQLite3Result::columnName with the value 0 returns "1" (as a string). The previous behaviour was relied on by my company when implementing an SQLite driver for our database layer. As we are issuing arbitrary queries, we always called ::query, and then used the fact that a query with no result had zero columns to avoid re-executing the query by stepping through it. Test script: --------------- <?php $db = new SQLite3(":memory:"); $result = $db->query("CREATE TABLE users(id INTEGER PRIMARY KEY)"); $result = $db->query("ALTER TABLE users RENAME TO user_temp"); print($result->numColumns()); Expected result: ---------------- 0 is output to the screen Actual result: -------------- 1 is output to the screen ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=78813&edit=1