SQLite Forum

Positive return of sqlite3_column_count() for ALTER TABLE
Login

Positive return of sqlite3_column_count() for ALTER TABLE

(1.1) By Ralf on 2021-02-15 12:13:05 edited from 1.0 [link] [source]

According to the documentation, sqlite3_column_count() shall return 0 for prepared statements that return no data.

This works as expected for CREATE TABLE, DROP TABLE, CREATE INDEX, DROP INDEX, INSERT, UPDATE, plus other SQL commands I did not tests.

But for ALTER TABLE sqlite3_column_count() returns 1, even though ALTER TABLE cannot return any data.

With reference to SELECT the documentation mentions that "just because this routine returns a positive number does not mean that one or more rows of data will be returned."

While this makes perfect sense for SELECT I do not understand the positive return value for ALTER TABLE.

Should this be considered a bug?

Ralf


Test code:

static void exec_and_write_sqlite3_column_count(const sqlite3 *db, const char *zSQL) {
  sqlite3_stmt *pStmt;
  int i, rc;

  printf("%s\n", zSQL);

  rc = sqlite3_prepare_v3(db, zSQL, -1, 0, &pStmt, 0);
  assert( rc == SQLITE_OK );

  i = sqlite3_column_count(pStmt);
  printf("%d\n", i);

  do {
    rc = sqlite3_step (pStmt);
  } while (rc == SQLITE_ROW);

  assert(rc == SQLITE_DONE);

  sqlite3_finalize(pStmt);
}

void do_sqlite3_column_count(void)
{
  sqlite3 *db;
  int i;
  int rc;

  remove("test.db");
  rc = sqlite3_open("test.db", &db);
  assert( rc==SQLITE_OK );

  exec_and_write_sqlite3_column_count(db, "CREATE TABLE t(a);");

  exec_and_write_sqlite3_column_count(db, "CREATE INDEX idx_t_a ON t(a);");
  exec_and_write_sqlite3_column_count(db, "DROP INDEX idx_t_a;");

  exec_and_write_sqlite3_column_count(db, "INSERT INTO t VALUES (1);");
  exec_and_write_sqlite3_column_count(db, "UPDATE t SET a = 2;");

  exec_and_write_sqlite3_column_count(db, "ALTER TABLE t RENAME TO t_new;");

  exec_and_write_sqlite3_column_count(db, "DROP TABLE t_new");

  sqlite3_close(db);
  return 0;
}

Output as of SQLite3 3.34.1:

CREATE TABLE t(a);
0
CREATE INDEX idx_t_a ON t(a);
0
DROP INDEX idx_t_a;
0
INSERT INTO t VALUES (1);
0
UPDATE t SET a = 2;
0
ALTER TABLE t RENAME TO t_new;
1
DROP TABLE t_new
0

(2) By Larry Brasfield (larrybr) on 2021-02-15 16:05:14 in reply to 1.1 [link] [source]

According to the documentation, sqlite3_column_count() shall return 0 for prepared statements that return no data.

Not so. The doc you reference says, "If this routine returns 0, that means the prepared statement returns no data (for example an UPDATE)." That assertion is not falsified by the behavior you report. That doc sentence does not address the meaning to be attached to returns greater than zero. In fact, the doc goes on to say, "However, just because this routine returns a positive number does not mean that one or more rows of data will be returned."

For those reasons, neither a code bug nor a doc bug is indicated.

Still, it is interesting to ask: What uses should be supported by sqlite3_column_count() beyond letting clients know how large the index can be for the value fetching functions? (ie. SELECT query results)

Apparently, you have such a use. What is it?

(3) By Ralf on 2021-02-15 16:13:58 in reply to 1.1 [link] [source]

(4) By Ralf on 2021-02-15 16:16:37 in reply to 2 [link] [source]

sqlite3_column_count() is a method to simply detect if a statement returns some data. Applications can avoid further steps if not.

(5) By anonymous on 2021-02-15 16:37:33 in reply to 2 [link] [source]

I have used this api for years. I always get zero for statements that do not return data. This is changed but I don't find time to check which version changed that.

(6) By Larry Brasfield (larrybr) on 2021-02-15 16:52:10 in reply to 5 [link] [source]

Unless the doc for the API has changed, you've been relying on undocumented behavior. The fact that undocumented behavior changes does not imply either a bug in the library or a deficiency in its docs.

BTW, I agree that it would be nice if the function could be relied upon to indicate that sqlite3_step() might return SQLITE_ROW, or to distinguish DML from SELECT queries.

I wonder how much larger the docs would be if there was no undocumented behavior?

(7) By anonymous on 2021-02-15 17:43:23 in reply to 6 [source]

Thank god it is fixed already.