SQLite Forum

Timeline
Login

11 forum posts by user Ralf

2021-12-03
18:14 Post: sqlite3_compileoption_used() now meaningless for some macros (artifact: 6dcf1740e4 user: Ralf)

As of https://www.sqlite.org/src/info/e306952690bfb140, sqlite3_compileoption_used() no longer has distinctive meaning for the following macros. Even if not defined at compile time, they are automatically #defined with default values in code so sqlite3_compileoption_used() always returns true:

  • SQLITE_DEFAULT_AUTOVACUUM
  • SQLITE_DEFAULT_CACHE_SIZE
  • SQLITE_DEFAULT_FILE_FORMAT
  • SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT
  • SQLITE_DEFAULT_MMAP_SIZE
  • SQLITE_MAX_MMAP_SIZE
  • SQLITE_TEMP_STORE
2021-02-15
16:16 Reply: Positive return of sqlite3_column_count() for ALTER TABLE (artifact: 11883bdb3e user: Ralf)

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

16:13 Reply: Positive return of sqlite3_column_count() for ALTER TABLE (artifact: bbe8a33e9c user: Ralf)
12:13 Edit: Positive return of sqlite3_column_count() for ALTER TABLE (artifact: 7499088143 user: Ralf)

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
10:33 Post: Positive return of sqlite3_column_count() for ALTER TABLE (artifact: ead9e1042e user: Ralf)

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 0, 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
2021-01-30
13:13 Reply: Does table have rowid? (artifact: 71cd83b7bc user: Ralf)

The trick here is that a non-rowid table (that is to say a WITHOUT ROWID table) is really implemented as a stand-alone index

This is interesting. Especially the misleading error message generated if an index with the same name already exists. Example:

drop table if exists t1;
create table t1 (a);
create index i1 on t1(a);

drop table if exists i1;
create table i1 (a primary key) without rowid;

Even though there is no table i1 this fails with the following error message:

there is already a table named i1
                   ^^^^^

I would have looked forever for this table did I not know that i1 in fact conflicts with an index.

2020-11-27
18:34 Reply: Access Violation running FTS5 rank due to compiler over-optimization (artifact: 40b52fea94 user: Ralf)

No, the cast patch alone does not alter the way Embarcadero's C++ Builder compiles the for loop.

However, the other two patches do fix the problem. The cast is not needed, but it does not hurt, either. To C++ Builder it makes no difference.

There is just an (unrelated) "Warning W8004 fts5_aux.c 643: 'rc' is assigned a value that is never used in function fts5Bm25Function".

At last, I noticed your fix takes into account a test for a rc return which I overlooked. Thanks for that, and for the fast answer!

11:58 Post: Access Violation running FTS5 rank due to compiler over-optimization (artifact: d2b48412e7 user: Ralf)

Embarcadero's C++ Builder may optimize out rc==SQLITE_OK in this for loop: https://www.sqlite.org/src/info?name=afe8c2394cf6de2a&ln=676

This leads to an AV in the same line if pData is NULL. This test then crashes: https://www.sqlite.org/src/info?name=4a15fb03b6c7eac6&ln=90-92

The cause may be an over-optimization or compiler bug. It seems related to the fact that the rc variable is not used within the for loop. This similar loop a few lines above uses rc, and rc==SQLITE_OK is compiled in: https://www.sqlite.org/src/info?name=afe8c2394cf6de2a&ln=659

It helps to move the for loop into the if( rc==SQLITE_OK ) block above. This also removes one rc==SQLITE_OK test. Another such test may be avoided if the test below is also moved there.

The resulting code looks like this and tests fine:

  /* Figure out the total size of the current row in tokens. */
  if( rc==SQLITE_OK ){
    int nTok;
    rc = pApi->xColumnSize(pFts, -1, &nTok);
    D = (double)nTok;

    /* Determine the BM25 score for the current row. */
    for(i=0; i<pData->nPhrase; i++){
      score += pData->aIDF[i] * (
        ( aFreq[i] * (k1 + 1.0) ) /
        ( aFreq[i] + k1 * (1 - b + b * D / pData->avgdl) )
      );
    }

    /* If no error has occurred, return the calculated score. Otherwise,
    ** throw an SQL exception.  */
    sqlite3_result_double(pCtx, -1.0 * score);
  }else{
    sqlite3_result_error_code(pCtx, rc);
  }
2020-11-26
14:50 Post: fts5_unicode2.c changed, but not mkunicode.tcl (artifact: 7f2f236176 user: Ralf)
2020-05-26
20:01 Post: ext/misc/cksumvfs.c VFS name mismatch (artifact: 824dda7e13 user: Ralf)

Name "cksmvfs":

https://www.sqlite.org/src/info/b0d07f2e1bb08f8b?ln=249

Find "cksum":

https://www.sqlite.org/src/info/b0d07f2e1bb08f8b?ln=746

Find will not succeed, possibly registering the VFS more often than necessary.

2020-05-25
11:26 Post: wal.c: variable declarations NOT at start of scope block (artifact: 1bf7773cf1 user: Ralf)