Prepare statement return incorrect result

(1) By ChiZhang on 2025-07-01 08:26:05 [source]

Hi,

The following test case includes only two statements and the second one is a prepare statement with a bind value, as there is not any row in rt0, so I expect the second statement has an empty result. however, the second statement returns 0:

#include <stdio.h>
#include <assert.h>
#include "sqlite3.h"

int main(int argc, char const * const * argv){
  int rc = 0;
  sqlite3 * db = 0;
  sqlite3_stmt *q = 0;

  rc = sqlite3_open(":memory:", &db);
  assert( 0==rc );
  rc = sqlite3_exec(db, "CREATE TABLE rt0(c3, c4);", 0, 0, 0);
  assert( 0==rc );
  rc = sqlite3_prepare(db, "SELECT MAX(rt0.c3)AND(?) FROM rt0;", -1, &q, 0);
  assert( 0==rc );
  rc = sqlite3_bind_int(q, 1, 0);
  assert( 0==rc );
  while( SQLITE_ROW== (rc = sqlite3_step(q)) ){
    printf("row: %s\n", sqlite3_column_text(q, 0));
  }
  sqlite3_finalize(q);
  assert( SQLITE_DONE==rc );
  rc = 0;
  sqlite3_close(db);
  return rc;
}

Makefile:

test: test.c
        cc -o test -I. test.c sqlite3.c -O0

output:

./test 
row: 0

If I directly run SELECT MAX(rt0.c3)AND(0) FROM rt0; I can get the empty result.

I can reproduce this with sqlite 3.50.2

(2) By Richard Hipp (drh) on 2025-07-01 09:43:31 in reply to 1 [link] [source]

Simplified test case:

CREATE TABLE empty(x);
.parameter set $zero 0
SELECT max(x) AND 0 FROM empty;
SELECT max(x) AND $zero FROM empty;

I believe the second of the two SELECT statements is correct.

(3) By Gunter Hick (gunter_hick) on 2025-07-01 10:43:56 in reply to 2 [link] [source]

The first one reduces "... AND 0" to FALSE during query preparation and generates an empty result set.
The second one retrieves the NULL from the max() function in order to perform the AND, yielding a single result row. Similar to a LEFT JOIN.

WITH lit(a) AS (VALUE (?))
SELECT a FROM lit LEFT JOIN SELECT max(x) from empty;