/* Remove the next line and the failure disappears! */
    fclose(stdin);

Presumably, by "confused" you're referring to:

$ ./tfd 
sqlite3_exec(indexify): disk I/O error

If so, i can reproduce both it and your workaround on Linux Mint 21.

Yes, that's it. Thank you for confirming my sanity.

Thanks for the bug report and the example problem. I now see what is happening.

The error arises from SQLite's MINIMUM_FILE_DESCRIPTOR defenses against database corrupt. See https://www.sqlite.org/howtocorrupt.html#_continuing_to_use_a_file_descriptor_after_it_has_been_closed for background information.

In your program, SQLite is trying to create a new, unique temporary file (named, for example, "/var/tmp/etilqs_1062fa79f017d944", though the name will always be different). It tries once and gets back file descriptor

1. It then closes that file, reopens /dev/null to consume file descriptor 0 and then tried again.

But on the second attempt it fails because now the file already exists and for the case of temporary files, the O_EXCL and O_CREAT flags are both passed, causing the open to fail.

Your work-around is simple: Don't close stdin while SQLite is running. :-)

I'll try to come up with a real solution later this morning.

Since this is Unix-specific code, the dup system call might help.

Fixed by check-in c0cfe0582add8798. For the record, here is a slight simplification to the original test case:

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

static char const preamble[] =
    "CREATE TEMP TABLE t(c);"
    "WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)"
    "  INSERT INTO t(c) SELECT randomblob(10000) FROM c;";

static void errorLogCallback(void *pArg, int iErrCode, const char *zMsg){
  fprintf(stderr, "(%d) %s\n", iErrCode, zMsg);
}

static int resultCallback(void *NotUsed, int argc, char **argv, char **colv){
  int i;
  for(i=0; i<argc; i++){
    const char *z = (const char*)argv[i];
    if( z==0 ) z = "NULL";
    if( i>0 ) printf(",");
    printf("%s", z);
  }
  printf("\n");
  return 0;
}

int main(void){
  int status;
  sqlite3 *db=NULL;
  char *errmsg=NULL;
  sqlite3_stmt *stmt=NULL;
  int i;

  sqlite3_config(SQLITE_CONFIG_LOG, errorLogCallback, 0);
  status = sqlite3_open("b1.db", &db);
  if (status!=SQLITE_OK) {
    fprintf(stderr,"sqlite3_open: %s\n",sqlite3_errmsg(db));
    return 1;
  }
  status=sqlite3_exec(db,preamble,0,NULL,&errmsg);
  if (status!=SQLITE_OK) {
    fprintf(stderr,"sqlite3_exec(preamble): %s\n",errmsg);
    return 1;
  }

  /* Remove the next line and the failure disappears! */
  fclose(stdin);

  status=sqlite3_exec(db,"CREATE INDEX i ON t(c)",0,NULL,&errmsg);
  if (status!=SQLITE_OK) {
    fprintf(stderr,"sqlite3_exec(indexify): %s\n",errmsg);
    return 1;
  }
  sqlite3_exec(db, "SELECT count(*) FROM t;", resultCallback, 0, 0);
  sqlite3_exec(db, "PRAGMA integrity_check;", resultCallback, 0, 0);
  sqlite3_close_v2(db);

  printf(
    "^^^^^^^^----- should be (approximately):\n"
    "  (28) attempt to open \"/var/tmp/etilqs_...\" as file descriptor 0\n"
    "  100\n"
    "  ok\n"
  );
  return 0;
}

Thank you!