SQLite

3.46.0
3.46.1

# This initializes the environment with PACKAGE_NAME and PACKAGE_VERSION
# set as provided.  These will also be added as -D defs in your Makefile
# so you can encode the package version directly into the source files.
# This will also define a special symbol for Windows (BUILD_<PACKAGE_NAME>
# so that we create the export library with the dll.
#-----------------------------------------------------------------------

AC_INIT([sqlite],[3.46.0])
AC_INIT([sqlite],[3.46.1])

#--------------------------------------------------------------------
# Call TEA_INIT as the first TEA_ macro to set up initial vars.
# This will define a ${TEA_PLATFORM} variable == "unix" or "windows"
# as well as PKG_LIB_FILE and PKG_STUB_LIB_FILE.
#--------------------------------------------------------------------

#! /bin/sh
# Guess values for system-dependent variables and create Makefiles.
# Generated by GNU Autoconf 2.69 for sqlite 3.46.0.
# Generated by GNU Autoconf 2.69 for sqlite 3.46.1.
#
#
# Copyright (C) 1992-1996, 1998-2012 Free Software Foundation, Inc.
#
#
# This configure script is free software; the Free Software Foundation
# gives unlimited permission to copy, distribute and modify it.

subdirs=
MFLAGS=
MAKEFLAGS=

# Identity of this package.
PACKAGE_NAME='sqlite'
PACKAGE_TARNAME='sqlite'
PACKAGE_VERSION='3.46.0'
PACKAGE_STRING='sqlite 3.46.0'
PACKAGE_VERSION='3.46.1'
PACKAGE_STRING='sqlite 3.46.1'
PACKAGE_BUGREPORT=''
PACKAGE_URL=''

# Factoring default headers for most tests.
ac_includes_default="\
#include <stdio.h>
#ifdef HAVE_SYS_TYPES_H

#
# Report the --help message.
#
if test "$ac_init_help" = "long"; then
  # Omit some internal or obsolete options to make the list less imposing.
  # This message is too long to be a string in the A/UX 3.1 sh.
  cat <<_ACEOF
\`configure' configures sqlite 3.46.0 to adapt to many kinds of systems.
\`configure' configures sqlite 3.46.1 to adapt to many kinds of systems.

Usage: $0 [OPTION]... [VAR=VALUE]...

To assign environment variables (e.g., CC, CFLAGS...), specify them as
VAR=VALUE.  See below for descriptions of some of the useful variables.

Defaults for the options are specified in brackets.

  --build=BUILD     configure for building on BUILD [guessed]
  --host=HOST       cross-compile to build programs to run on HOST [BUILD]
_ACEOF
fi

if test -n "$ac_init_help"; then
  case $ac_init_help in
     short | recursive ) echo "Configuration of sqlite 3.46.0:";;
     short | recursive ) echo "Configuration of sqlite 3.46.1:";;
   esac
  cat <<\_ACEOF

Optional Features:
  --disable-option-checking  ignore unrecognized --enable/--with options
  --disable-FEATURE       do not include FEATURE (same as --enable-FEATURE=no)
  --enable-FEATURE[=ARG]  include FEATURE [ARG=yes]

    cd "$ac_pwd" || { ac_status=$?; break; }
  done
fi

test -n "$ac_init_help" && exit $ac_status
if $ac_init_version; then
  cat <<\_ACEOF
sqlite configure 3.46.0
sqlite configure 3.46.1
generated by GNU Autoconf 2.69

Copyright (C) 2012 Free Software Foundation, Inc.
This configure script is free software; the Free Software Foundation
gives unlimited permission to copy, distribute and modify it.
_ACEOF
  exit

  eval $as_lineno_stack; ${as_lineno_stack:+:} unset as_lineno

} # ac_fn_c_check_header_mongrel
cat >config.log <<_ACEOF
This file contains any messages produced by compilers while
running configure, to aid debugging if configure makes a mistake.

It was created by sqlite $as_me 3.46.0, which was
It was created by sqlite $as_me 3.46.1, which was
generated by GNU Autoconf 2.69.  Invocation command line was

  $ $0 $@

_ACEOF
exec 5>>config.log
{

test $as_write_fail = 0 && chmod +x $CONFIG_STATUS || ac_write_fail=1

cat >>$CONFIG_STATUS <<\_ACEOF || ac_write_fail=1
# Save the log message, to keep $0 and so on meaningful, and to
# report actual input values of CONFIG_FILES etc. instead of their
# values after options handling.
ac_log="
This file was extended by sqlite $as_me 3.46.0, which was
This file was extended by sqlite $as_me 3.46.1, which was
generated by GNU Autoconf 2.69.  Invocation command line was

  CONFIG_FILES    = $CONFIG_FILES
  CONFIG_HEADERS  = $CONFIG_HEADERS
  CONFIG_LINKS    = $CONFIG_LINKS
  CONFIG_COMMANDS = $CONFIG_COMMANDS
  $ $0 $@

Report bugs to the package provider."

_ACEOF
cat >>$CONFIG_STATUS <<_ACEOF || ac_write_fail=1
ac_cs_config="`$as_echo "$ac_configure_args" | sed 's/^ //; s/[\\""\`\$]/\\\\&/g'`"
ac_cs_version="\\
sqlite config.status 3.46.0
sqlite config.status 3.46.1
configured by $0, generated by GNU Autoconf 2.69,
  with options \\"\$ac_cs_config\\"

Copyright (C) 2012 Free Software Foundation, Inc.
This config.status script is free software; the Free Software Foundation
gives unlimited permission to copy, distribute and modify it."

#  endif
#  define CIO_WIN_WC_XLATE 0 /* Use plain C library stream I/O at console */
# endif
#else
# define CIO_WIN_WC_XLATE 0 /* Not exposing translation routines at all */
#endif

#if CIO_WIN_WC_XLATE
/* Character used to represent a known-incomplete UTF-8 char group (�) */
static WCHAR cBadGroup = 0xfffd;
#endif

#if CIO_WIN_WC_XLATE
static HANDLE handleOfFile(FILE *pf){
  int fileDesc = _fileno(pf);
  union { intptr_t osfh; HANDLE fh; } fid = {
    (fileDesc>=0)? _get_osfhandle(fileDesc) : (intptr_t)INVALID_HANDLE_VALUE
  };
  return fid.fh;

  t1 t1_idx_00000062 {100 20}
  t1 t1_idx_000123a7 {100 50 17}
  t2 t2_idx_00000063 {100 20} 
  t2 t2_idx_00000064 {100 5} 
  t2 t2_idx_0001295b {100 20 5}
}

do_catchsql_test 5.4 {
  SELECT sqlite_expert_rem(123, 123);
} {1 {no such function: sqlite_expert_rem}}
do_catchsql_test 5.5 {
  SELECT sqlite_expert_sample();
} {1 {no such function: sqlite_expert_sample}}

if 0 {
do_test expert1-6.0 {
  catchcmd :memory: {
.expert
select base64('');
.expert
select name from pragma_collation_list order by name collate uint;

  (void)idxStr;
  (void)argc;
  (void)argv;
  rc = sqlite3_finalize(pCsr->pData);
  pCsr->pData = 0;
  if( rc==SQLITE_OK ){
    rc = idxPrintfPrepareStmt(pExpert->db, &pCsr->pData, &pVtab->base.zErrMsg,
        "SELECT * FROM main.%Q WHERE sample()", pVtab->pTab->zName
        "SELECT * FROM main.%Q WHERE sqlite_expert_sample()", pVtab->pTab->zName
    );
  }

  if( rc==SQLITE_OK ){
    rc = expertNext(cur);
  }
  return rc;

    int nByte;                    /* Bytes of space allocated at z */
    int n;                        /* Size of buffer z */
    char *z;                      /* SQLITE_TEXT/BLOB value */
  } aSlot[1];
};

/*
** Implementation of scalar function rem().
** Implementation of scalar function sqlite_expert_rem().
*/
static void idxRemFunc(
  sqlite3_context *pCtx,
  int argc,
  sqlite3_value **argv
){
  struct IdxRemCtx *p = (struct IdxRemCtx*)sqlite3_user_data(pCtx);
  struct IdxRemSlot *pSlot;
  int iSlot;
  assert( argc==2 );

  iSlot = sqlite3_value_int(argv[0]);
  assert( iSlot<=p->nSlot );
  assert( iSlot<p->nSlot );
  pSlot = &p->aSlot[iSlot];

  switch( pSlot->eType ){
    case SQLITE_NULL:
      /* no-op */
      break;

  /* Formulate the query text */
  sqlite3_bind_text(pIndexXInfo, 1, zIdx, -1, SQLITE_STATIC);
  while( SQLITE_OK==rc && SQLITE_ROW==sqlite3_step(pIndexXInfo) ){
    const char *zComma = zCols==0 ? "" : ", ";
    const char *zName = (const char*)sqlite3_column_text(pIndexXInfo, 0);
    const char *zColl = (const char*)sqlite3_column_text(pIndexXInfo, 1);
    zCols = idxAppendText(&rc, zCols, 
        "%sx.%Q IS sqlite_expert_rem(%d, x.%Q) COLLATE %s", 
        "%sx.%Q IS rem(%d, x.%Q) COLLATE %s", zComma, zName, nCol, zName, zColl
        zComma, zName, nCol, zName, zColl
    );
    zOrder = idxAppendText(&rc, zOrder, "%s%d", zComma, ++nCol);
  }
  sqlite3_reset(pIndexXInfo);
  if( rc==SQLITE_OK ){
    if( p->iSample==100 ){
      zQuery = sqlite3_mprintf(

  if( rc==SQLITE_OK ){
    int nByte = sizeof(struct IdxRemCtx) + (sizeof(struct IdxRemSlot) * nMax);
    pCtx = (struct IdxRemCtx*)idxMalloc(&rc, nByte);
  }

  if( rc==SQLITE_OK ){
    sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
    rc = sqlite3_create_function(
        dbrem, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
    rc = sqlite3_create_function(dbrem, "sqlite_expert_rem", 
        2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
    );
  }
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_function(
        p->db, "sample", 0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0
    rc = sqlite3_create_function(p->db, "sqlite_expert_sample", 
        0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0
    );
  }

  if( rc==SQLITE_OK ){
    pCtx->nSlot = nMax+1;
    rc = idxPrepareStmt(p->dbm, &pAllIndex, pzErr, zAllIndex);
  }

    sqlite3_free(pCtx);
  }

  if( rc==SQLITE_OK ){
    rc = sqlite3_exec(p->dbm, "ANALYZE sqlite_schema", 0, 0, 0);
  }

  sqlite3_create_function(p->db, "sqlite_expert_rem", 2, SQLITE_UTF8, 0,0,0,0);
  sqlite3_create_function(p->db, "sqlite_expert_sample", 0,SQLITE_UTF8,0,0,0,0);

  sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
  return rc;
}

/*
** Define and possibly pretend to use a useless collation sequence.
** This pretense allows expert to accept SQL using custom collations.

      sParse.apPhrase = 0;
    }
  }else{
    sqlite3Fts5ParseNodeFree(sParse.pExpr);
  }

  sqlite3_free(sParse.apPhrase);
  if( 0==*pzErr ){
  *pzErr = sParse.zErr;
    *pzErr = sParse.zErr;
  }else{
    sqlite3_free(sParse.zErr);
  }
  return sParse.rc;
}

/*
** Assuming that buffer z is at least nByte bytes in size and contains a
** valid utf-8 string, return the number of characters in the string.
*/

        || pLeft->eType==FTS5_TERM
        || pLeft->eType==FTS5_EOF
        || pLeft->eType==FTS5_AND
    );
    assert( pRight->eType==FTS5_STRING 
        || pRight->eType==FTS5_TERM 
        || pRight->eType==FTS5_EOF 
        || (pRight->eType==FTS5_AND && pParse->bPhraseToAnd) 
    );

    if( pLeft->eType==FTS5_AND ){
      pPrev = pLeft->apChild[pLeft->nChild-1];
    }else{
      pPrev = pLeft;
    }

      if( pConfig->bContentlessDelete ){
        fts5SetVtabError(pTab, 
            "'delete' may not be used with a contentless_delete=1 table"
        );
        rc = SQLITE_ERROR;
      }else{
        rc = fts5SpecialDelete(pTab, apVal);
        bUpdateOrDelete = 1;
      }
    }else{
      rc = fts5SpecialInsert(pTab, z, apVal[2 + pConfig->nCol + 1]);
    }
  }else{
    /* A regular INSERT, UPDATE or DELETE statement. The trick here is that
    ** any conflict on the rowid value must be detected before any 

  Fts5TokenizerModule *pMod;
  int rc = SQLITE_OK;

  pMod = fts5LocateTokenizer(pGlobal, nArg==0 ? 0 : azArg[0]);
  if( pMod==0 ){
    assert( nArg>0 );
    rc = SQLITE_ERROR;
    *pzErr = sqlite3_mprintf("no such tokenizer: %s", azArg[0]);
    if( pzErr ) *pzErr = sqlite3_mprintf("no such tokenizer: %s", azArg[0]);
  }else{
    rc = pMod->x.xCreate(
        pMod->pUserData, (azArg?&azArg[1]:0), (nArg?nArg-1:0), &pConfig->pTok
    );
    pConfig->pTokApi = &pMod->x;
    if( rc!=SQLITE_OK ){
      if( pzErr && rc!=SQLITE_NOMEM ){
      if( pzErr ) *pzErr = sqlite3_mprintf("error in tokenizer constructor");
        *pzErr = sqlite3_mprintf("error in tokenizer constructor");
      }
    }else{
      pConfig->ePattern = sqlite3Fts5TokenizerPattern(
          pMod->x.xCreate, pConfig->pTok
      );
    }
  }

  char **pzErr            /* Write error message here */
){
  Fts5FullTable *pTab = (Fts5FullTable*)pVtab;
  int rc;

  assert( pzErr!=0 && *pzErr==0 );
  UNUSED_PARAM(isQuick);
  assert( pTab->p.pConfig->pzErrmsg==0 );
  pTab->p.pConfig->pzErrmsg = pzErr;
  rc = sqlite3Fts5StorageIntegrity(pTab->pStorage, 0);
  if( *pzErr==0 && rc!=SQLITE_OK ){
  if( (rc&0xff)==SQLITE_CORRUPT ){
    *pzErr = sqlite3_mprintf("malformed inverted index for FTS5 table %s.%s",
                zSchema, zTabname);
     rc = (*pzErr) ? SQLITE_OK : SQLITE_NOMEM;
  }else if( rc!=SQLITE_OK ){
    *pzErr = sqlite3_mprintf("unable to validate the inverted index for"
                             " FTS5 table %s.%s: %s",
                zSchema, zTabname, sqlite3_errstr(rc));
  }
    if( (rc&0xff)==SQLITE_CORRUPT ){
      *pzErr = sqlite3_mprintf("malformed inverted index for FTS5 table %s.%s",
          zSchema, zTabname);
      rc = (*pzErr) ? SQLITE_OK : SQLITE_NOMEM;
    }else{
      *pzErr = sqlite3_mprintf("unable to validate the inverted index for"
          " FTS5 table %s.%s: %s",
          zSchema, zTabname, sqlite3_errstr(rc));
    }
  }

  sqlite3Fts5IndexCloseReader(pTab->p.pIndex);
  pTab->p.pConfig->pzErrmsg = 0;

  return rc;
}

static int fts5Init(sqlite3 *db){
  static const sqlite3_module fts5Mod = {
    /* iVersion      */ 4,

    p = sqlite3_malloc(sizeof(AsciiTokenizer));
    if( p==0 ){
      rc = SQLITE_NOMEM;
    }else{
      int i;
      memset(p, 0, sizeof(AsciiTokenizer));
      memcpy(p->aTokenChar, aAsciiTokenChar, sizeof(aAsciiTokenChar));
      for(i=0; rc==SQLITE_OK && i<nArg; i+=2){
      for(i=0; rc==SQLITE_OK && i<nArg-1; i+=2){
        const char *zArg = azArg[i+1];
        if( 0==sqlite3_stricmp(azArg[i], "tokenchars") ){
          fts5AsciiAddExceptions(p, zArg, 1);
        }else
        if( 0==sqlite3_stricmp(azArg[i], "separators") ){
          fts5AsciiAddExceptions(p, zArg, 0);
        }else{
          rc = SQLITE_ERROR;
        }
      }
      if( rc==SQLITE_OK && i<nArg ) rc = SQLITE_ERROR;
      if( rc!=SQLITE_OK ){
        fts5AsciiDelete((Fts5Tokenizer*)p);
        p = 0;
      }
    }
  }

      p->nFold = 64;
      p->aFold = sqlite3_malloc64(p->nFold * sizeof(char));
      if( p->aFold==0 ){
        rc = SQLITE_NOMEM;
      }

      /* Search for a "categories" argument */
      for(i=0; rc==SQLITE_OK && i<nArg; i+=2){
      for(i=0; rc==SQLITE_OK && i<nArg-1; i+=2){
        if( 0==sqlite3_stricmp(azArg[i], "categories") ){
          zCat = azArg[i+1];
        }
      }

      if( rc==SQLITE_OK ){
        rc = unicodeSetCategories(p, zCat);
      }

      for(i=0; rc==SQLITE_OK && i<nArg; i+=2){
      for(i=0; rc==SQLITE_OK && i<nArg-1; i+=2){
        const char *zArg = azArg[i+1];
        if( 0==sqlite3_stricmp(azArg[i], "remove_diacritics") ){
          if( (zArg[0]!='0' && zArg[0]!='1' && zArg[0]!='2') || zArg[1] ){
            rc = SQLITE_ERROR;
          }else{
            p->eRemoveDiacritic = (zArg[0] - '0');
            assert( p->eRemoveDiacritic==FTS5_REMOVE_DIACRITICS_NONE

        }else
        if( 0==sqlite3_stricmp(azArg[i], "categories") ){
          /* no-op */
        }else{
          rc = SQLITE_ERROR;
        }
      }
      if( i<nArg && rc==SQLITE_OK ) rc = SQLITE_ERROR;

    }else{
      rc = SQLITE_NOMEM;
    }
    if( rc!=SQLITE_OK ){
      fts5UnicodeDelete((Fts5Tokenizer*)p);
      p = 0;

  UNUSED_PARAM(pUnused);
  if( pNew==0 ){
    rc = SQLITE_NOMEM;
  }else{
    int i;
    pNew->bFold = 1;
    pNew->iFoldParam = 0;
    for(i=0; rc==SQLITE_OK && i<nArg; i+=2){
    for(i=0; rc==SQLITE_OK && i<nArg-1; i+=2){
      const char *zArg = azArg[i+1];
      if( 0==sqlite3_stricmp(azArg[i], "case_sensitive") ){
        if( (zArg[0]!='0' && zArg[0]!='1') || zArg[1] ){
          rc = SQLITE_ERROR;
        }else{
          pNew->bFold = (zArg[0]=='0');
        }
      }else if( 0==sqlite3_stricmp(azArg[i], "remove_diacritics") ){
        if( (zArg[0]!='0' && zArg[0]!='1' && zArg[0]!='2') || zArg[1] ){
          rc = SQLITE_ERROR;
        }else{
          pNew->iFoldParam = (zArg[0]!='0') ? 2 : 0;
        }
      }else{
        rc = SQLITE_ERROR;
      }
    }
    if( i<nArg && rc==SQLITE_OK ) rc = SQLITE_ERROR;

    if( pNew->iFoldParam!=0 && pNew->bFold==0 ){
      rc = SQLITE_ERROR;
    }

    if( rc!=SQLITE_OK ){
      fts5TriDelete((Fts5Tokenizer*)pNew);

} {1 SQLITE_RANGE}
do_catchsql_test 12.3.2 {
  SELECT fts5_collist(t1, 2) FROM t1('one AND two');
} {1 SQLITE_RANGE}
do_catchsql_test 12.3.3 {
  SELECT fts5_collist(t1, 1) FROM t1('one AND two');
} {0 1}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 13.1 {
  CREATE VIRTUAL TABLE t1 USING fts5(a, tokenize=ascii);
  INSERT INTO t1 VALUES('a b c'), ('d e f');
  PRAGMA integrity_check;
} {ok}

do_catchsql_test 13.2 {
  SELECT highlight(t1, 0, '[', ']') FROM t1
} {0 {{a b c} {d e f}}}

do_execsql_test 13.3 {
  PRAGMA writable_schema = 1;
  UPDATE sqlite_schema SET sql = 'CREATE VIRTUAL TABLE t1 USING fts5(a, tokenize=blah)'
  WHERE name = 't1';
}

db close
sqlite3 db test.db
do_catchsql_test 13.4 {
  SELECT highlight(t1, 0, '[', ']') FROM t1
} {1 {no such tokenizer: blah}}

finish_test

explain_i {
  PRAGMA integrity_check
  }
do_execsql_test 12.3 {
  PRAGMA integrity_check
} {ok}


#-------------------------------------------------------------------
reset_db
do_execsql_test 13.1 {
  CREATE VIRTUAL TABLE t1 USING fts5(a, tokenize=ascii);
  INSERT INTO t1 VALUES('a b c'), ('d e f');
  PRAGMA integrity_check;
} {ok}

db close
sqlite3 db test.db
do_catchsql_test 13.2 {
  PRAGMA integrity_check;
} {0 ok}

do_execsql_test 13.3 {
  PRAGMA writable_schema = 1;
  UPDATE sqlite_schema SET sql = 'CREATE VIRTUAL TABLE t1 USING fts5(a, tokenize=blah)'
  WHERE name = 't1';
}

db close
sqlite3 db test.db
breakpoint
do_catchsql_test 13.4 {
  PRAGMA integrity_check;
} {1 {no such tokenizer: blah}}


finish_test

  INSERT INTO ft(ft, rank) VALUES('secure-delete', 1);
  DELETE FROM ft WHERE rowid=100;
}

do_execsql_test 1.2 {
  PRAGMA integrity_check;
} {ok}

do_execsql_test 2.0 {
  CREATE VIRTUAL TABLE xyz USING fts5 (
  	name,
        content=''
  );

  INSERT INTO xyz(xyz, rank) VALUES('secure-delete', 1);
  INSERT INTO xyz (rowid, name) VALUES(1, 'A');
  INSERT INTO xyz (rowid, name) VALUES(2, 'A');
  INSERT INTO xyz(xyz, rowid, name) VALUES('delete', 2, 'A');
}

do_execsql_test 2.1 {
  pragma quick_check;
} {ok}





finish_test

do_execsql_test 1.6 {
  SELECT highlight(t1, 0, '>', '<') FROM t1('mess');
} {AAdontBB>mess<}

do_execsql_test 1.7 {
  SELECT highlight(t1, 0, '>', '<') FROM t1('BB mess');
} {AAdont>BBmess<}

# 2024-08-06 https://sqlite.org/forum/forumpost/171bcc2bcd
# Error handling of tokenize= arguments.
#
foreach {n tkz} {
  1  {ascii none}
  2  {unicode61 none}
  3  {porter none}
  4  {trigram none}
  5  {ascii none 0}
  6  {unicode61 none 0}
  7  {porter none 0}
  8  {trigram none 0}
} {
  db eval {DROP TABLE IF EXISTS t2;}
  do_catchsql_test 2.$n "
     DROP TABLE IF EXISTS t2;
     CREATE VIRTUAL TABLE t2 USING fts5(a,b,c,tokenize='$tkz');
  " {1 {error in tokenizer constructor}}
}


finish_test

#-------------------------------------------------------------------------
reset_db
do_execsql_test 2.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(y, tokenize="trigram case_sensitive 1");
  INSERT INTO t1 VALUES('abcdefghijklm');
  INSERT INTO t1 VALUES('กรุงเทพมหานคร');
}
do_catchsql_test 2.0.1 {
  CREATE VIRTUAL TABLE t2 USING fts5(z, tokenize='trigram case_sensitive');
} {1 {error in tokenizer constructor}}

foreach {tn s res} {
  1 abc           "(abc)defghijklm"
  2 defgh         "abc(defgh)ijklm"
  3 abcdefghijklm "(abcdefghijklm)"
  4 กรุ            "(กรุ)งเทพมหานคร"
  5 งเทพมห        "กรุ(งเทพมห)านคร"

do_execsql_test 7.0 {
  CREATE VIRTUAL TABLE f USING FTS5(filename, tokenize="trigram");
  INSERT INTO f (rowid, filename) VALUES 
      (10, "giraffe.png"), 
      (20, "жираф.png"), 
      (30, "cat.png"), 
      (40, "кот.png"), 
      (50, "misic-🎵-.mp3");
      (50, "misic-🎵-.mp3");
}
do_execsql_test 7.1 {
  SELECT rowid FROM f WHERE +filename GLOB '*ир*';
} {20}
do_execsql_test 7.2 {
  SELECT rowid FROM f WHERE filename GLOB '*ир*';
} {20}

set ::testprefix fts5trigram2

do_execsql_test 1.0 "
  CREATE VIRTUAL TABLE t1 USING fts5(y, tokenize='trigram remove_diacritics 1');
  INSERT INTO t1 VALUES('abc\u0303defghijklm');
  INSERT INTO t1 VALUES('a\u0303b\u0303c\u0303defghijklm');
"
do_catchsql_test 1.0.1 {
  CREATE VIRTUAL TABLE t2 USING fts5(z, tokenize='trigram remove_diacritics');
} {1 {error in tokenizer constructor}}

do_execsql_test 1.1 {
  SELECT highlight(t1, 0, '(', ')') FROM t1('abc');
} [list \
  "(abc\u0303)defghijklm"                          \
  "(a\u0303b\u0303c\u0303)defghijklm"              \
]

  const char *zFmt, ...
){
  char *z = 0;
  va_list ap;
  va_start(ap, zFmt);
  if( zFmt ){
    z = sqlite3_vmprintf(zFmt, ap);
    va_end(ap);
  }
  va_end(ap);
  sqlite3_free(p->zErrMsg);
  p->zErrMsg = z;
  p->errCode = errCode;
  return errCode;
}

# 2024 November 04
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
} 
source [file join [file dirname [info script]] session_common.tcl]
source $testdir/tester.tcl
ifcapable !session {finish_test; return}

if {$::tcl_platform(pointerSize)<8} {
  finish_test
  return
}

set testprefix sessionblob

forcedelete test.db2
sqlite3 db2 test.db2

set NBLOB 2000000

do_execsql_test 1.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
  INSERT INTO t1 VALUES(123, zeroblob($NBLOB));
}

do_test 1.1 {
  sqlite3session S db main
  S attach t1
} {}

set b2 [string repeat x 1000]
do_test 1.2 {
  set ::blob [db incrblob t1 b 123]
  for {set ii 0} {$ii < $NBLOB} {incr ii [string length $b2]} {
    seek $::blob $ii
    puts -nonewline $::blob $b2
  }
  close $::blob
} {}

S delete

finish_test

  };
  const logImpl = (level,...args)=>{
    if(state.verbose>level) loggers[level]("OPFS asyncer:",...args);
  };
  const log =    (...args)=>logImpl(2, ...args);
  const warn =   (...args)=>logImpl(1, ...args);
  const error =  (...args)=>logImpl(0, ...args);
  const metrics = Object.create(null);
  metrics.reset = ()=>{
    let k;
    const r = (m)=>(m.count = m.time = m.wait = 0);
    for(k in state.opIds){
      r(metrics[k] = Object.create(null));
    }
    let s = metrics.s11n = Object.create(null);
    s = s.serialize = Object.create(null);
    s.count = s.time = 0;
    s = metrics.s11n.deserialize = Object.create(null);
    s.count = s.time = 0;
  };
  metrics.dump = ()=>{
    let k, n = 0, t = 0, w = 0;
    for(k in state.opIds){
      const m = metrics[k];
      n += m.count;
      t += m.time;
      w += m.wait;
      m.avgTime = (m.count && m.time) ? (m.time / m.count) : 0;
    }
    console.log(globalThis?.location?.href,
                "metrics for",globalThis?.location?.href,":\n",
                metrics,
                "\nTotal of",n,"op(s) for",t,"ms",
                "approx",w,"ms spent waiting on OPFS APIs.");
    console.log("Serialization metrics:",metrics.s11n);
  };

  /**
     __openFiles is a map of sqlite3_file pointers (integers) to
     metadata related to a given OPFS file handles. The pointers are, in
     this side of the interface, opaque file handle IDs provided by the
     synchronous part of this constellation. Each value is an object
     with a structure demonstrated in the xOpen() impl.

        errorObject.message
      ].join(' '), {
        cause: errorObject
      });
      this.name = 'GetSyncHandleError';
    }
  };

  /**
     Attempts to find a suitable SQLITE_xyz result code for Error
     object e. Returns either such a translation or rc if if it does
     not know how to translate the exception.
  */
  GetSyncHandleError.convertRc = (e,rc)=>{
    if(1){
      return (
        e instanceof GetSyncHandleError
          && ((e.cause.name==='NoModificationAllowedError')
              /* Inconsistent exception.name from Chrome/ium with the
                 same exception.message text: */
              || (e.cause.name==='DOMException'
                  && 0===e.cause.message.indexOf('Access Handles cannot')))
    if( e instanceof GetSyncHandleError ){
      if( e.cause.name==='NoModificationAllowedError'
        /* Inconsistent exception.name from Chrome/ium with the
           same exception.message text: */
          || (e.cause.name==='DOMException'
              && 0===e.cause.message.indexOf('Access Handles cannot')) ){
      ) ? (
        /*console.warn("SQLITE_BUSY",e),*/
        state.sq3Codes.SQLITE_BUSY
        return state.sq3Codes.SQLITE_BUSY;
      }else if( 'NotFoundError'===e.cause.name ){
        /**
           Maintenance reminder: SQLITE_NOTFOUND, though it looks like
           a good match, has different semantics than NotFoundError
           and is not suitable here.
        */
        return state.sq3Codes.SQLITE_CANTOPEN;
      ) : rc;
    }else{
      return rc;
    }
  }
      }
    }else if( 'NotFoundError'===e?.name ){
      return state.sq3Codes.SQLITE_CANTOPEN;
    }
    return rc;
  };

  /**
     Returns the sync access handle associated with the given file
     handle object (which must be a valid handle object, as created by
     xOpen()), lazily opening it if needed.

     In order to help alleviate cross-tab contention for a dabase, if
     an exception is thrown while acquiring the handle, this routine

  /**
     Throws if fh is a file-holding object which is flagged as read-only.
  */
  const affirmNotRO = function(opName,fh){
    if(fh.readOnly) toss(opName+"(): File is read-only: "+fh.filenameAbs);
  };

  /**
     We track 2 different timers: the "metrics" timer records how much
     time we spend performing work. The "wait" timer records how much
     time we spend waiting on the underlying OPFS timer. See the calls
     to mTimeStart(), mTimeEnd(), wTimeStart(), and wTimeEnd()
     throughout this file to see how they're used.
  */
  const __mTimer = Object.create(null);
  __mTimer.op = undefined;
  __mTimer.start = undefined;
  const mTimeStart = (op)=>{
    __mTimer.start = performance.now();
    __mTimer.op = op;
    //metrics[op] || toss("Maintenance required: missing metrics for",op);
    ++metrics[op].count;
  };
  const mTimeEnd = ()=>(
    metrics[__mTimer.op].time += performance.now() - __mTimer.start
  );
  const __wTimer = Object.create(null);
  __wTimer.op = undefined;
  __wTimer.start = undefined;
  const wTimeStart = (op)=>{
    __wTimer.start = performance.now();
    __wTimer.op = op;
    //metrics[op] || toss("Maintenance required: missing metrics for",op);
  };
  const wTimeEnd = ()=>(
    metrics[__wTimer.op].wait += performance.now() - __wTimer.start
  );

  /**
     Gets set to true by the 'opfs-async-shutdown' command to quit the
     wait loop. This is only intended for debugging purposes: we cannot
     inspect this file's state while the tight waitLoop() is running and
     need a way to stop that loop for introspection purposes.
  */
  let flagAsyncShutdown = false;

  /**
     Asynchronous wrappers for sqlite3_vfs and sqlite3_io_methods
     methods, as well as helpers like mkdir(). Maintenance reminder:
     methods, as well as helpers like mkdir().
     members are in alphabetical order to simplify finding them.
  */
  const vfsAsyncImpls = {
    'opfs-async-metrics': async ()=>{
      mTimeStart('opfs-async-metrics');
      metrics.dump();
      storeAndNotify('opfs-async-metrics', 0);
      mTimeEnd();
    },
    'opfs-async-shutdown': async ()=>{
      flagAsyncShutdown = true;
      storeAndNotify('opfs-async-shutdown', 0);
    },
    mkdir: async (dirname)=>{
      mTimeStart('mkdir');
      let rc = 0;
      wTimeStart('mkdir');
      try {
        await getDirForFilename(dirname+"/filepart", true);
      }catch(e){
        state.s11n.storeException(2,e);
        rc = state.sq3Codes.SQLITE_IOERR;
      }finally{
        wTimeEnd();
      }
      storeAndNotify('mkdir', rc);
      mTimeEnd();
    },
    xAccess: async (filename)=>{
      mTimeStart('xAccess');
      /* OPFS cannot support the full range of xAccess() queries
         sqlite3 calls for. We can essentially just tell if the file
         is accessible, but if it is then it's automatically writable
         (unless it's locked, which we cannot(?) know without trying
         to open it). OPFS does not have the notion of read-only.

         The return semantics of this function differ from sqlite3's
         xAccess semantics because we are limited in what we can
         communicate back to our synchronous communication partner: 0 =
         accessible, non-0 means not accessible.
      */
      let rc = 0;
      wTimeStart('xAccess');
      try{
        const [dh, fn] = await getDirForFilename(filename);
        await dh.getFileHandle(fn);
      }catch(e){
        state.s11n.storeException(2,e);
        rc = state.sq3Codes.SQLITE_IOERR;
      }finally{
        wTimeEnd();
      }
      storeAndNotify('xAccess', rc);
      mTimeEnd();
    },
    xClose: async function(fid/*sqlite3_file pointer*/){
      const opName = 'xClose';
      mTimeStart(opName);
      __implicitLocks.delete(fid);
      const fh = __openFiles[fid];
      let rc = 0;
      wTimeStart(opName);
      if(fh){
        delete __openFiles[fid];
        await closeSyncHandle(fh);
        if(fh.deleteOnClose){
          try{ await fh.dirHandle.removeEntry(fh.filenamePart) }
          catch(e){ warn("Ignoring dirHandle.removeEntry() failure of",fh,e) }
        }
      }else{
        state.s11n.serialize();
        rc = state.sq3Codes.SQLITE_NOTFOUND;
      }
      wTimeEnd();
      storeAndNotify(opName, rc);
      mTimeEnd();
    },
    xDelete: async function(...args){
      mTimeStart('xDelete');
      const rc = await vfsAsyncImpls.xDeleteNoWait(...args);
      storeAndNotify('xDelete', rc);
      mTimeEnd();
    },
    xDeleteNoWait: async function(filename, syncDir = 0, recursive = false){
      /* The syncDir flag is, for purposes of the VFS API's semantics,
         ignored here. However, if it has the value 0x1234 then: after
         deleting the given file, recursively try to delete any empty
         directories left behind in its wake (ignoring any errors and
         stopping at the first failure).

         That said: we don't know for sure that removeEntry() fails if
         the dir is not empty because the API is not documented. It has,
         however, a "recursive" flag which defaults to false, so
         presumably it will fail if the dir is not empty and that flag
         is false.
      */
      let rc = 0;
      wTimeStart('xDelete');
      try {
        while(filename){
          const [hDir, filenamePart] = await getDirForFilename(filename, false);
          if(!filenamePart) break;
          await hDir.removeEntry(filenamePart, {recursive});
          if(0x1234 !== syncDir) break;
          recursive = false;
          filename = getResolvedPath(filename, true);
          filename.pop();
          filename = filename.join('/');
        }
      }catch(e){
        state.s11n.storeException(2,e);
        rc = state.sq3Codes.SQLITE_IOERR_DELETE;
      }
      wTimeEnd();
      return rc;
    },
    xFileSize: async function(fid/*sqlite3_file pointer*/){
      mTimeStart('xFileSize');
      const fh = __openFiles[fid];
      let rc = 0;
      wTimeStart('xFileSize');
      try{
        const sz = await (await getSyncHandle(fh,'xFileSize')).getSize();
        state.s11n.serialize(Number(sz));
      }catch(e){
        state.s11n.storeException(1,e);
        rc = GetSyncHandleError.convertRc(e,state.sq3Codes.SQLITE_IOERR);
      }
      await releaseImplicitLock(fh);
      wTimeEnd();
      storeAndNotify('xFileSize', rc);
      mTimeEnd();
    },
    xLock: async function(fid/*sqlite3_file pointer*/,
                          lockType/*SQLITE_LOCK_...*/){
      mTimeStart('xLock');
      const fh = __openFiles[fid];
      let rc = 0;
      const oldLockType = fh.xLock;
      fh.xLock = lockType;
      if( !fh.syncHandle ){
        wTimeStart('xLock');
        try {
          await getSyncHandle(fh,'xLock');
          __implicitLocks.delete(fid);
        }catch(e){
          state.s11n.storeException(1,e);
          rc = GetSyncHandleError.convertRc(e,state.sq3Codes.SQLITE_IOERR_LOCK);
          fh.xLock = oldLockType;
        }
        wTimeEnd();
      }
      storeAndNotify('xLock',rc);
      mTimeEnd();
    },
    xOpen: async function(fid/*sqlite3_file pointer*/, filename,
                          flags/*SQLITE_OPEN_...*/,
                          opfsFlags/*OPFS_...*/){
      const opName = 'xOpen';
      mTimeStart(opName);
      const create = (state.sq3Codes.SQLITE_OPEN_CREATE & flags);
      wTimeStart('xOpen');
      try{
        let hDir, filenamePart;
        try {
          [hDir, filenamePart] = await getDirForFilename(filename, !!create);
        }catch(e){
          state.s11n.storeException(1,e);
          storeAndNotify(opName, state.sq3Codes.SQLITE_NOTFOUND);
          mTimeEnd();
          wTimeEnd();
          return;
        }
        if( state.opfsFlags.OPFS_UNLINK_BEFORE_OPEN & opfsFlags ){
          try{
            await hDir.removeEntry(filenamePart);
          }catch(e){
            /* ignoring */
            //warn("Ignoring failed Unlink of",filename,":",e);
          }
        }
        const hFile = await hDir.getFileHandle(filenamePart, {create});
        wTimeEnd();
        const fh = Object.assign(Object.create(null),{
          fid: fid,
          filenameAbs: filename,
          filenamePart: filenamePart,
          dirHandle: hDir,
          fileHandle: hFile,
          sabView: state.sabFileBufView,
          readOnly: create
            ? false : (state.sq3Codes.SQLITE_OPEN_READONLY & flags),
          readOnly: !create && !!(state.sq3Codes.SQLITE_OPEN_READONLY & flags),
          deleteOnClose: !!(state.sq3Codes.SQLITE_OPEN_DELETEONCLOSE & flags)
        });
        fh.releaseImplicitLocks =
          (opfsFlags & state.opfsFlags.OPFS_UNLOCK_ASAP)
          || state.opfsFlags.defaultUnlockAsap;
        if(0 /* this block is modelled after something wa-sqlite
                does but it leads to immediate contention on journal files.
                Update: this approach reportedly only works for DELETE journal
                mode. */
           && (0===(flags & state.sq3Codes.SQLITE_OPEN_MAIN_DB))){
          /* sqlite does not lock these files, so go ahead and grab an OPFS
             lock. */
          fh.xLock = "xOpen"/* Truthy value to keep entry from getting
                               flagged as auto-locked. String value so
                               that we can easily distinguish is later
                               if needed. */;
          await getSyncHandle(fh,'xOpen');
        }
        __openFiles[fid] = fh;
        storeAndNotify(opName, 0);
      }catch(e){
        wTimeEnd();
        error(opName,e);
        state.s11n.storeException(1,e);
        storeAndNotify(opName, state.sq3Codes.SQLITE_IOERR);
      }
      mTimeEnd();
    },
    xRead: async function(fid/*sqlite3_file pointer*/,n,offset64){
      mTimeStart('xRead');
      let rc = 0, nRead;
      const fh = __openFiles[fid];
      try{
        wTimeStart('xRead');
        nRead = (await getSyncHandle(fh,'xRead')).read(
          fh.sabView.subarray(0, n),
          {at: Number(offset64)}
        );
        wTimeEnd();
        if(nRead < n){/* Zero-fill remaining bytes */
          fh.sabView.fill(0, nRead, n);
          rc = state.sq3Codes.SQLITE_IOERR_SHORT_READ;
        }
      }catch(e){
        if(undefined===nRead) wTimeEnd();
        error("xRead() failed",e,fh);
        state.s11n.storeException(1,e);
        rc = GetSyncHandleError.convertRc(e,state.sq3Codes.SQLITE_IOERR_READ);
      }
      await releaseImplicitLock(fh);
      storeAndNotify('xRead',rc);
      mTimeEnd();
    },
    xSync: async function(fid/*sqlite3_file pointer*/,flags/*ignored*/){
      mTimeStart('xSync');
      const fh = __openFiles[fid];
      let rc = 0;
      if(!fh.readOnly && fh.syncHandle){
        try {
          wTimeStart('xSync');
          await fh.syncHandle.flush();
        }catch(e){
          state.s11n.storeException(2,e);
          rc = state.sq3Codes.SQLITE_IOERR_FSYNC;
        }
        wTimeEnd();
      }
      storeAndNotify('xSync',rc);
      mTimeEnd();
    },
    xTruncate: async function(fid/*sqlite3_file pointer*/,size){
      mTimeStart('xTruncate');
      let rc = 0;
      const fh = __openFiles[fid];
      wTimeStart('xTruncate');
      try{
        affirmNotRO('xTruncate', fh);
        await (await getSyncHandle(fh,'xTruncate')).truncate(size);
      }catch(e){
        error("xTruncate():",e,fh);
        state.s11n.storeException(2,e);
        rc = GetSyncHandleError.convertRc(e,state.sq3Codes.SQLITE_IOERR_TRUNCATE);
      }
      await releaseImplicitLock(fh);
      wTimeEnd();
      storeAndNotify('xTruncate',rc);
      mTimeEnd();
    },
    xUnlock: async function(fid/*sqlite3_file pointer*/,
                            lockType/*SQLITE_LOCK_...*/){
      mTimeStart('xUnlock');
      let rc = 0;
      const fh = __openFiles[fid];
      if( state.sq3Codes.SQLITE_LOCK_NONE===lockType
          && fh.syncHandle ){
        wTimeStart('xUnlock');
        try { await closeSyncHandle(fh) }
        catch(e){
          state.s11n.storeException(1,e);
          rc = state.sq3Codes.SQLITE_IOERR_UNLOCK;
        }
        wTimeEnd();
      }
      storeAndNotify('xUnlock',rc);
      mTimeEnd();
    },
    xWrite: async function(fid/*sqlite3_file pointer*/,n,offset64){
      mTimeStart('xWrite');
      let rc;
      const fh = __openFiles[fid];
      wTimeStart('xWrite');
      try{
        affirmNotRO('xWrite', fh);
        rc = (
          n === (await getSyncHandle(fh,'xWrite'))
            .write(fh.sabView.subarray(0, n),
                   {at: Number(offset64)})
        ) ? 0 : state.sq3Codes.SQLITE_IOERR_WRITE;
      }catch(e){
        error("xWrite():",e,fh);
        state.s11n.storeException(1,e);
        rc = GetSyncHandleError.convertRc(e,state.sq3Codes.SQLITE_IOERR_WRITE);
      }
      await releaseImplicitLock(fh);
      wTimeEnd();
      storeAndNotify('xWrite',rc);
      mTimeEnd();
    }
  }/*vfsAsyncImpls*/;

  const initS11n = ()=>{
    /**
       ACHTUNG: this code is 100% duplicated in the other half of this
       proxy! The documentation is maintained in the "synchronous half".

          case TypeIds.bigint.id: return TypeIds.bigint;
          case TypeIds.boolean.id: return TypeIds.boolean;
          case TypeIds.string.id: return TypeIds.string;
          default: toss("Invalid type ID:",tid);
      }
    };
    state.s11n.deserialize = function(clear=false){
      ++metrics.s11n.deserialize.count;
      const t = performance.now();
      const argc = viewU8[0];
      const rc = argc ? [] : null;
      if(argc){
        const typeIds = [];
        let offset = 1, i, n, v;
        for(i = 0; i < argc; ++i, ++offset){
          typeIds.push(getTypeIdById(viewU8[offset]));

            offset += n;
          }
          rc.push(v);
        }
      }
      if(clear) viewU8[0] = 0;
      //log("deserialize:",argc, rc);
      metrics.s11n.deserialize.time += performance.now() - t;
      return rc;
    };
    state.s11n.serialize = function(...args){
      const t = performance.now();
      ++metrics.s11n.serialize.count;
      if(args.length){
        //log("serialize():",args);
        const typeIds = [];
        let i = 0, offset = 1;
        viewU8[0] = args.length & 0xff /* header = # of args */;
        for(; i < args.length; ++i, ++offset){
          /* Write the TypeIds.id value into the next args.length

            offset += s.byteLength;
          }
        }
        //log("serialize() result:",viewU8.slice(0,offset));
      }else{
        viewU8[0] = 0;
      }
      metrics.s11n.serialize.time += performance.now() - t;
    };

    state.s11n.storeException = state.asyncS11nExceptions
      ? ((priority,e)=>{
        if(priority<=state.asyncS11nExceptions){
          state.s11n.serialize([e.name,': ',e.message].join(""));
        }

            state.sabS11nView = new Uint8Array(state.sabIO, state.sabS11nOffset, state.sabS11nSize);
            Object.keys(vfsAsyncImpls).forEach((k)=>{
              if(!Number.isFinite(state.opIds[k])){
                toss("Maintenance required: missing state.opIds[",k,"]");
              }
            });
            initS11n();
            metrics.reset();
            log("init state",state);
            wPost('opfs-async-inited');
            waitLoop();
            break;
          }
          case 'opfs-async-restart':
            if(flagAsyncShutdown){
              warn("Restarting after opfs-async-shutdown. Might or might not work.");
              flagAsyncShutdown = false;
              waitLoop();
            }
            break;
          case 'opfs-async-metrics':
            metrics.dump();
            break;
      }
    };
    wPost('opfs-async-loaded');
  }).catch((e)=>error("error initializing OPFS asyncer:",e));
}/*installAsyncProxy()*/;
if(!globalThis.SharedArrayBuffer){
  wPost('opfs-unavailable', "Missing SharedArrayBuffer API.",

  major browsers released since March 2023). If that API is not
  detected, the VFS is not registered.
*/
globalThis.sqlite3ApiBootstrap.initializers.push(function(sqlite3){
  'use strict';
  const toss = sqlite3.util.toss;
  const toss3 = sqlite3.util.toss3;
  const initPromises = Object.create(null);
  const initPromises = Object.create(null) /* cache of (name:result) of VFS init results */;
  const capi = sqlite3.capi;
  const util = sqlite3.util;
  const wasm = sqlite3.wasm;
  // Config opts for the VFS...
  const SECTOR_SIZE = 4096;
  const HEADER_MAX_PATH_SIZE = 512;
  const HEADER_FLAGS_SIZE = 4;

       Resolves to true if it did its job, false if the
       VFS has already been shut down.
    */
    async removeVfs(){
      if(!this.#cVfs.pointer || !this.#dhOpaque) return false;
      capi.sqlite3_vfs_unregister(this.#cVfs.pointer);
      this.#cVfs.dispose();
      delete initPromises[this.vfsName];
      try{
        this.releaseAccessHandles();
        await this.#dhVfsRoot.removeEntry(OPAQUE_DIR_NAME, {recursive: true});
        this.#dhOpaque = undefined;
        await this.#dhVfsParent.removeEntry(
          this.#dhVfsRoot.name, {recursive: true}
        );

       counterpart...
    */
    state.sq3Codes = Object.create(null);
    [
      'SQLITE_ACCESS_EXISTS',
      'SQLITE_ACCESS_READWRITE',
      'SQLITE_BUSY',
      'SQLITE_CANTOPEN',
      'SQLITE_ERROR',
      'SQLITE_IOERR',
      'SQLITE_IOERR_ACCESS',
      'SQLITE_IOERR_CLOSE',
      'SQLITE_IOERR_DELETE',
      'SQLITE_IOERR_FSYNC',
      'SQLITE_IOERR_LOCK',

         It goes without saying that deleting a file out from under another
         instance results in Undefined Behavior.
      */
      OPFS_UNLINK_BEFORE_OPEN: 0x02,
      /**
         If true, any async routine which implicitly acquires a sync
         access handle (i.e. an OPFS lock) will release that locks at
         access handle (i.e. an OPFS lock) will release that lock at
         the end of the call which acquires it. If false, such
         "autolocks" are not released until the VFS is idle for some
         brief amount of time.

         The benefit of enabling this is much higher concurrency. The
         down-side is much-reduced performance (as much as a 4x decrease
         in speedtest1).

      const opNdx = state.opIds[op] || toss("Invalid op ID:",op);
      state.s11n.serialize(...args);
      Atomics.store(state.sabOPView, state.opIds.rc, -1);
      Atomics.store(state.sabOPView, state.opIds.whichOp, opNdx);
      Atomics.notify(state.sabOPView, state.opIds.whichOp)
      /* async thread will take over here */;
      const t = performance.now();
      Atomics.wait(state.sabOPView, state.opIds.rc, -1)
      /* When this wait() call returns, the async half will have
         completed the operation and reported its results. */;
      while('not-equal'!==Atomics.wait(state.sabOPView, state.opIds.rc, -1)){
        /*
          The reason for this loop is buried in the details of a long
          discussion at:

          https://github.com/sqlite/sqlite-wasm/issues/12

          Summary: in at least one browser flavor, under high loads,
          the wait()/notify() pairings can get out of sync. Calling
          wait() here until it returns 'not-equal' gets them back in
          sync.
        */
      }
      /* When the above wait() call returns 'not-equal', the async
         half will have completed the operation and reported its results
         in the state.opIds.rc slot of the SAB. */
      const rc = Atomics.load(state.sabOPView, state.opIds.rc);
      metrics[op].wait += performance.now() - t;
      if(rc && state.asyncS11nExceptions){
        const err = state.s11n.deserialize();
        if(err) error(op+"() async error:",...err);
      }
      return rc;

           As of late 2022, only a single lock can be held on an OPFS
           file. We have no way of checking whether any _other_ db
           connection has a lock except by trying to obtain and (on
           success) release a sync-handle for it, but doing so would
           involve an inherent race condition. For the time being,
           pending a better solution, we simply report whether the
           given pFile is open.

           Update 2024-06-12: based on forum discussions, this
           function now always sets pOut to 0 (false):

           https://sqlite.org/forum/forumpost/a2f573b00cda1372
        */
        if(1){
          wasm.poke(pOut, 0, 'i32');
        }else{
        const f = __openFiles[pFile];
        wasm.poke(pOut, f.lockType ? 1 : 0, 'i32');
          const f = __openFiles[pFile];
          wasm.poke(pOut, f.lockType ? 1 : 0, 'i32');
        }
        return 0;
      },
      xClose: function(pFile){
        mTimeStart('xClose');
        let rc = 0;
        const f = __openFiles[pFile];
        if(f){
          delete __openFiles[pFile];
          rc = opRun('xClose', pFile);
          if(f.sq3File) f.sq3File.dispose();
        }
        mTimeEnd();
        return rc;
      },
      xDeviceCharacteristics: function(pFile){
        //debug("xDeviceCharacteristics(",pFile,")");
        return capi.SQLITE_IOCAP_UNDELETABLE_WHEN_OPEN;
      },
      xFileControl: function(pFile, opId, pArg){
        /*mTimeStart('xFileControl');
          mTimeEnd();*/
        return capi.SQLITE_NOTFOUND;
      },

          //warn("xOpen zName =",zName, "opfsFlags =",opfsFlags);
        }
        const fh = Object.create(null);
        fh.fid = pFile;
        fh.filename = zName;
        fh.sab = new SharedArrayBuffer(state.fileBufferSize);
        fh.flags = flags;
        fh.readOnly = !(sqlite3.SQLITE_OPEN_CREATE & flags)
          && !!(flags & capi.SQLITE_OPEN_READONLY);
        const rc = opRun('xOpen', pFile, zName, flags, opfsFlags);
        if(!rc){
          /* Recall that sqlite3_vfs::xClose() will be called, even on
             error, unless pFile->pMethods is NULL. */
          if(fh.readOnly){
            wasm.poke(pOutFlags, capi.SQLITE_OPEN_READONLY, 'i32');
          }

        //dump('After deleting one entry via subquery');
        rc = fetchEm();
        T.assert(2===rc.length)
          .assert('abcghi'===rc.join(''));
        //log('rc =',rc);
        db.close();
      }
    })
    .t({
      name: 'r/o connection recovery from write op error',
      predicate: ()=>hasOpfs() || "Requires OPFS to reproduce",
      //predicate: ()=>false,
      test: async function(sqlite3){
        /* https://sqlite.org/forum/forumpost/cf37d5ff1182c31081

           The "opfs" VFS (but not SAHPool) was formerly misbehaving
           after a write attempt was made on a db opened with
           mode=ro. This test ensures that that behavior is fixed and
           compares that behavior with other VFSes. */
        const tryOne = function(vfsName,descr){
          const uri = 'file:///foo.db';
          let db = new sqlite3.oo1.DB(uri + (vfsName ? '?vfs='+vfsName : ''));
          db.exec([
            "drop table if exists t;",
            "create table t(a);",
            "insert into t(a) values('abc'),('def'),('ghi');"
          ]);
          db.close();
          db = new sqlite3.oo1.DB(uri+'?mode=ro'+
                                  (vfsName ? '&vfs='+vfsName : ''));
          let err;
          try {
            db.exec('insert into t(a) values(1)');
          }catch(e){
            err = e;
          }
          T.assert(err && (err.message.indexOf('SQLITE_READONLY')===0));
          try{
            db.exec('select a from t');
          }finally{
            db.close();
          }
        };
        const poolConfig = JSON.parse(JSON.stringify(sahPoolConfig));
        poolConfig.name = 'opfs-sahpool-cf37d5ff11';
        let poolUtil;
        await sqlite3.installOpfsSAHPoolVfs(poolConfig).then(p=>poolUtil=p);
        T.assert(!!sqlite3.capi.sqlite3_vfs_find(poolConfig.name), "Expecting to find just-registered VFS");
        try{
          tryOne(false, "Emscripten filesystem");
          tryOne(poolConfig.name);
          tryOne('opfs');
        }finally{
          await poolUtil.removeVfs();
        }
      }
    })
  ;/*end of Bug Reports group*/;

  ////////////////////////////////////////////////////////////////////////
  log("Loading and initializing sqlite3 WASM module...");
  if(0){
    globalThis.sqlite3ApiConfig = {

  pParse->pTriggerTab = sqlite3FindTable(db, pNew->table,
      db->aDb[sqlite3SchemaToIndex(db, pNew->pTabSchema)].zDbSName
  );
  pParse->eTriggerOp = pNew->op;
  /* ALWAYS() because if the table of the trigger does not exist, the
  ** error would have been hit before this point */
  if( ALWAYS(pParse->pTriggerTab) ){
    rc = sqlite3ViewGetColumnNames(pParse, pParse->pTriggerTab);
    rc = sqlite3ViewGetColumnNames(pParse, pParse->pTriggerTab)!=0;
  }

  /* Resolve symbols in WHEN clause */
  if( rc==SQLITE_OK && pNew->pWhen ){
    rc = sqlite3ResolveExprNames(&sNC, pNew->pWhen);
  }

      *pRes = c;
      return SQLITE_OK;  /* Cursor already pointing at the correct spot */
    }
    if( pCur->iPage>0
     && indexCellCompare(pCur, 0, pIdxKey, xRecordCompare)<=0
     && pIdxKey->errCode==SQLITE_OK
    ){
      pCur->curFlags &= ~BTCF_ValidOvfl;
      pCur->curFlags &= ~(BTCF_ValidOvfl|BTCF_AtLast);
      if( !pCur->pPage->isInit ){
        return SQLITE_CORRUPT_BKPT;
      }
      goto bypass_moveto_root;  /* Start search on the current page */
    }
    pIdxKey->errCode = SQLITE_OK;
  }

  return;
}
#endif /* SQLITE_OMIT_VIEW */

#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
/*
** The Table structure pTable is really a VIEW.  Fill in the names of
** the columns of the view in the pTable structure.  Return the number
** of errors.  If an error is seen leave an error message in pParse->zErrMsg.
** the columns of the view in the pTable structure.  Return non-zero if
** there are errors.  If an error is seen an error message is left
** in pParse->zErrMsg.
*/
static SQLITE_NOINLINE int viewGetColumnNames(Parse *pParse, Table *pTable){
  Table *pSelTab;   /* A fake table from which we get the result set */
  Select *pSel;     /* Copy of the SELECT that implements the view */
  int nErr = 0;     /* Number of errors encountered */
  sqlite3 *db = pParse->db;  /* Database connection for malloc errors */
#ifndef SQLITE_OMIT_VIRTUALTABLE

    nErr++;
  }
  pTable->pSchema->schemaFlags |= DB_UnresetViews;
  if( db->mallocFailed ){
    sqlite3DeleteColumnNames(db, pTable);
  }
#endif /* SQLITE_OMIT_VIEW */
  return nErr; 
  return nErr + pParse->nErr; 
}
int sqlite3ViewGetColumnNames(Parse *pParse, Table *pTable){
  assert( pTable!=0 );
  if( !IsVirtual(pTable) && pTable->nCol>0 ) return 0;
  return viewGetColumnNames(pParse, pTable);
}
#endif /* !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE) */

    datetimeError(p);
    return;
  }
  if( M<=2 ){
    Y--;
    M += 12;
  }
  A = Y/100;
  B = 2 - A + (A/4);
  A = (Y+4800)/100;
  B = 38 - A + (A/4);
  X1 = 36525*(Y+4716)/100;
  X2 = 306001*(M+1)/10000;
  p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
  p->validJD = 1;
  if( p->validHMS ){
    p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5);
    if( p->tz ){

  return iJD>=0 && iJD<=INT_464269060799999;
}

/*
** Compute the Year, Month, and Day from the julian day number.
*/
static void computeYMD(DateTime *p){
  int Z, A, B, C, D, E, X1;
  int Z, alpha, A, B, C, D, E, X1;
  if( p->validYMD ) return;
  if( !p->validJD ){
    p->Y = 2000;
    p->M = 1;
    p->D = 1;
  }else if( !validJulianDay(p->iJD) ){
    datetimeError(p);
    return;
  }else{
    Z = (int)((p->iJD + 43200000)/86400000);
    A = (int)((Z - 1867216.25)/36524.25);
    A = Z + 1 + A - (A/4);
    alpha = (int)((Z + 32044.75)/36524.25) - 52;
    A = Z + 1 + alpha - ((alpha+100)/4) + 25;
    B = A + 1524;
    C = (int)((B - 122.1)/365.25);
    D = (36525*(C&32767))/100;
    E = (int)((B-D)/30.6001);
    X1 = (int)(30.6001*E);
    p->D = B - D - X1;
    p->M = E<14 ? E-1 : E-13;

    = (GroupConcatCtx*)sqlite3_aggregate_context(context, 0);
  if( pGCC ){
    StrAccum *pAccum = &pGCC->str;
    if( pAccum->accError==SQLITE_TOOBIG ){
      sqlite3_result_error_toobig(context);
    }else if( pAccum->accError==SQLITE_NOMEM ){
      sqlite3_result_error_nomem(context);
    }else if( pGCC->nAccum>0 && pAccum->nChar==0 ){
      sqlite3_result_text(context, "", 1, SQLITE_STATIC);
    }else{   
      const char *zText = sqlite3_str_value(pAccum);
      sqlite3_result_text(context, zText, pAccum->nChar, SQLITE_TRANSIENT);
    }
  }
}
#else

      }

      if( pRet ){
        SelectDest dest;
        pRet->pSrc->nSrc = 1;
        pRet->pPrior = pLeft->pPrior;
        pRet->op = pLeft->op;
        if( pRet->pPrior ) pRet->selFlags |= SF_Values;
        pLeft->pPrior = 0;
        pLeft->op = TK_SELECT;
        assert( pLeft->pNext==0 );
        assert( pRet->pNext==0 );
        p = &pRet->pSrc->a[0];
        p->pSelect = pLeft;
        p->fg.viaCoroutine = 1;

  }
  if( zPath[0]=='.' ){
    int rawKey = 1;
    x = pParse->aBlob[iRoot];
    zPath++;
    if( zPath[0]=='"' ){
      zKey = zPath + 1;
      for(i=1; zPath[i] && zPath[i]!='"'; i++){}
      for(i=1; zPath[i] && zPath[i]!='"'; i++){
        if( zPath[i]=='\\' && zPath[i+1]!=0 ) i++;
      }
      nKey = i-1;
      if( zPath[i] ){
        i++;
      }else{
        return JSON_LOOKUP_PATHERROR;
      }
      testcase( nKey==0 );

        if( pLoop->pOrderBy || pLoop->pLimit ){
          sqlite3ErrorMsg(pParse,"%s clause should come after %s not before",
             pLoop->pOrderBy!=0 ? "ORDER BY" : "LIMIT",
             sqlite3SelectOpName(pNext->op));
          break;
        }
      }
      if( (p->selFlags & SF_MultiValue)==0 && 
        (mxSelect = pParse->db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT])>0 &&
        cnt>mxSelect
      if( (p->selFlags & (SF_MultiValue|SF_Values))==0
       && (mxSelect = pParse->db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT])>0
       && cnt>mxSelect
      ){
        sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
      }
    }
  }

  /* Attach a With object describing the WITH clause to a Select

/*
** Return TRUE (non-zero) if zTab is a valid name for the schema table pTab.
*/
static SQLITE_NOINLINE int isValidSchemaTableName(
  const char *zTab,         /* Name as it appears in the SQL */
  Table *pTab,              /* The schema table we are trying to match */
  Schema *pSchema           /* non-NULL if a database qualifier is present */
  const char *zDb           /* non-NULL if a database qualifier is present */
){
  const char *zLegacy;
  assert( pTab!=0 );
  assert( pTab->tnum==1 );
  if( sqlite3StrNICmp(zTab, "sqlite_", 7)!=0 ) return 0;
  zLegacy = pTab->zName;
  if( strcmp(zLegacy+7, &LEGACY_TEMP_SCHEMA_TABLE[7])==0 ){
    if( sqlite3StrICmp(zTab+7, &PREFERRED_TEMP_SCHEMA_TABLE[7])==0 ){
      return 1;
    }
    if( pSchema==0 ) return 0;
    if( zDb==0 ) return 0;
    if( sqlite3StrICmp(zTab+7, &LEGACY_SCHEMA_TABLE[7])==0 ) return 1;
    if( sqlite3StrICmp(zTab+7, &PREFERRED_SCHEMA_TABLE[7])==0 ) return 1;
  }else{
    if( sqlite3StrICmp(zTab+7, &PREFERRED_SCHEMA_TABLE[7])==0 ) return 1;
  }
  return 0;
}

          }
          if( pItem->zAlias!=0 ){
            if( sqlite3StrICmp(zTab, pItem->zAlias)!=0 ){
              continue;
            }
          }else if( sqlite3StrICmp(zTab, pTab->zName)!=0 ){
            if( pTab->tnum!=1 ) continue;
            if( !isValidSchemaTableName(zTab, pTab, pSchema) ) continue;
            if( !isValidSchemaTableName(zTab, pTab, zDb) ) continue;
          }
          assert( ExprUseYTab(pExpr) );
          if( IN_RENAME_OBJECT && pItem->zAlias ){
            sqlite3RenameTokenRemap(pParse, 0, (void*)&pExpr->y.pTab);
          }
        }
        hCol = sqlite3StrIHash(zCol);

      if( is_agg ){
        if( pExpr->pLeft ){
          assert( pExpr->pLeft->op==TK_ORDER );
          assert( ExprUseXList(pExpr->pLeft) );
          sqlite3WalkExprList(pWalker, pExpr->pLeft->x.pList);
        }
#ifndef SQLITE_OMIT_WINDOWFUNC
        if( pWin ){
        if( pWin && pParse->nErr==0 ){
          Select *pSel = pNC->pWinSelect;
          assert( pWin==0 || (ExprUseYWin(pExpr) && pWin==pExpr->y.pWin) );
          if( IN_RENAME_OBJECT==0 ){
            sqlite3WindowUpdate(pParse, pSel ? pSel->pWinDefn : 0, pWin, pDef);
            if( pParse->db->mallocFailed ) break;
          }
          sqlite3WalkExprList(pWalker, pWin->pPartition);

  return pNC->nNcErr>0 || w.pParse->nErr>0;
}

/*
** Resolve all names for all expression in an expression list.  This is
** just like sqlite3ResolveExprNames() except that it works for an expression
** list rather than a single expression.
**
** The return value is SQLITE_OK (0) for success or SQLITE_ERROR (1) for a
** failure.
*/
int sqlite3ResolveExprListNames(
  NameContext *pNC,       /* Namespace to resolve expressions in. */
  ExprList *pList         /* The expression list to be analyzed. */
){
  int i;
  int savedHasAgg = 0;
  Walker w;
  if( pList==0 ) return WRC_Continue;
  if( pList==0 ) return SQLITE_OK;
  w.pParse = pNC->pParse;
  w.xExprCallback = resolveExprStep;
  w.xSelectCallback = resolveSelectStep;
  w.xSelectCallback2 = 0;
  w.u.pNC = pNC;
  savedHasAgg = pNC->ncFlags & (NC_HasAgg|NC_MinMaxAgg|NC_HasWin|NC_OrderAgg);
  pNC->ncFlags &= ~(NC_HasAgg|NC_MinMaxAgg|NC_HasWin|NC_OrderAgg);
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr = pList->a[i].pExpr;
    if( pExpr==0 ) continue;
#if SQLITE_MAX_EXPR_DEPTH>0
    w.pParse->nHeight += pExpr->nHeight;
    if( sqlite3ExprCheckHeight(w.pParse, w.pParse->nHeight) ){
      return WRC_Abort;
      return SQLITE_ERROR;
    }
#endif
    sqlite3WalkExprNN(&w, pExpr);
#if SQLITE_MAX_EXPR_DEPTH>0
    w.pParse->nHeight -= pExpr->nHeight;
#endif
    assert( EP_Agg==NC_HasAgg );
    assert( EP_Win==NC_HasWin );
    testcase( pNC->ncFlags & NC_HasAgg );
    testcase( pNC->ncFlags & NC_HasWin );
    if( pNC->ncFlags & (NC_HasAgg|NC_MinMaxAgg|NC_HasWin|NC_OrderAgg) ){
      ExprSetProperty(pExpr, pNC->ncFlags & (NC_HasAgg|NC_HasWin) );
      savedHasAgg |= pNC->ncFlags &
                          (NC_HasAgg|NC_MinMaxAgg|NC_HasWin|NC_OrderAgg);
      pNC->ncFlags &= ~(NC_HasAgg|NC_MinMaxAgg|NC_HasWin|NC_OrderAgg);
    }
    if( w.pParse->nErr>0 ) return WRC_Abort;
    if( w.pParse->nErr>0 ) return SQLITE_ERROR;
  }
  pNC->ncFlags |= savedHasAgg;
  return WRC_Continue;
  return SQLITE_OK;
}

/*
** Resolve all names in all expressions of a SELECT and in all
** descendants of the SELECT, including compounds off of p->pPrior,
** subqueries in expressions, and subqueries used as FROM clause
** terms.

static void finalizeAggFunctions(Parse *pParse, AggInfo *pAggInfo){
  Vdbe *v = pParse->pVdbe;
  int i;
  struct AggInfo_func *pF;
  for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
    ExprList *pList;
    assert( ExprUseXList(pF->pFExpr) );
    if( pParse->nErr ) return;
    pList = pF->pFExpr->x.pList;
    if( pF->iOBTab>=0 ){
      /* For an ORDER BY aggregate, calls to OP_AggStep were deferred.  Inputs
      ** were stored in emphermal table pF->iOBTab.  Here, we extract those
      ** inputs (in ORDER BY order) and make all calls to OP_AggStep
      ** before doing the OP_AggFinal call. */
      int iTop;        /* Start of loop for extracting columns */

      sqlite3VdbeAppendP4(v, pF->pFunc, P4_FUNCDEF);
      sqlite3VdbeChangeP5(v, (u8)nArg);
      sqlite3ReleaseTempRange(pParse, regAgg, nArg);
    }
    if( addrNext ){
      sqlite3VdbeResolveLabel(v, addrNext);
    }
    if( pParse->nErr ) return;
  }
  if( regHit==0 && pAggInfo->nAccumulator ){
    regHit = regAcc;
  }
  if( regHit ){
    addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); VdbeCoverage(v);
  }
  for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
    sqlite3ExprCode(pParse, pC->pCExpr, AggInfoColumnReg(pAggInfo,i));
    if( pParse->nErr ) return;
  }

  pAggInfo->directMode = 0;
  if( addrHitTest ){
    sqlite3VdbeJumpHereOrPopInst(v, addrHitTest);
  }
}

    }
    zSql = sqlite3_mprintf("SELECT count(*) FROM pragma_table_info(%Q,%Q);",
                           zTable, zSchema);
    if( zSql==0 ){
      import_cleanup(&sCtx);
      shell_out_of_memory();
    }
    nByte = strlen(zSql);    
    rc =  sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
    sqlite3_free(zSql);
    zSql = 0;
    if( rc ){
      if (pStmt) sqlite3_finalize(pStmt);
      eputf("Error: %s\n", sqlite3_errmsg(p->db));
      import_cleanup(&sCtx);
      rc = 1;
      goto meta_command_exit;
    }
    if( sqlite3_step(pStmt)==SQLITE_ROW ){
      nCol = sqlite3_column_int(pStmt, 0);
    }else{
      nCol = 0;
    }
    sqlite3_finalize(pStmt);
    pStmt = 0;
    if( nCol==0 ) return 0; /* no columns, no error */

    nByte = 64                 /* space for "INSERT INTO", "VALUES(", ")\0" */
          + (zSchema ? strlen(zSchema)*2 + 2: 0)  /* Quoted schema name */
          + strlen(zTable)*2 + 2                  /* Quoted table name */
          + nCol*2;            /* Space for ",?" for each column */
    zSql = sqlite3_malloc64( nByte*2 + 20 + nCol*2 );
    zSql = sqlite3_malloc64( nByte );
    if( zSql==0 ){
      import_cleanup(&sCtx);
      shell_out_of_memory();
    }
    if( zSchema ){
      sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\".\"%w\" VALUES(?", 
      sqlite3_snprintf(nByte, zSql, "INSERT INTO \"%w\".\"%w\" VALUES(?", 
                       zSchema, zTable);
    }else{
      sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable);
      sqlite3_snprintf(nByte, zSql, "INSERT INTO \"%w\" VALUES(?", zTable);
    }
    j = strlen30(zSql);
    for(i=1; i<nCol; i++){
      zSql[j++] = ',';
      zSql[j++] = '?';
    }
    zSql[j++] = ')';
    zSql[j] = 0;
    assert( j<nByte );
    if( eVerbose>=2 ){
      oputf("Insert using: %s\n", zSql);
    }
    rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
    sqlite3_free(zSql);
    zSql = 0;
    if( rc ){

#define WHERE_GROUPBY          0x0040 /* pOrderBy is really a GROUP BY */
#define WHERE_DISTINCTBY       0x0080 /* pOrderby is really a DISTINCT clause */
#define WHERE_WANT_DISTINCT    0x0100 /* All output needs to be distinct */
#define WHERE_SORTBYGROUP      0x0200 /* Support sqlite3WhereIsSorted() */
#define WHERE_AGG_DISTINCT     0x0400 /* Query is "SELECT agg(DISTINCT ...)" */
#define WHERE_ORDERBY_LIMIT    0x0800 /* ORDERBY+LIMIT on the inner loop */
#define WHERE_RIGHT_JOIN       0x1000 /* Processing a RIGHT JOIN */
                        /*     0x2000    not currently used */
#define WHERE_KEEP_ALL_JOINS   0x2000 /* Do not do the omit-noop-join opt */
#define WHERE_USE_LIMIT        0x4000 /* Use the LIMIT in cost estimates */
                        /*     0x8000    not currently used */

/* Allowed return values from sqlite3WhereIsDistinct()
*/
#define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
#define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */

  KeyInfo keyinfo;
  UnpackedRecord *pUnpacked;      /* Unpacked version of aRecord[] */
  UnpackedRecord *pNewUnpacked;   /* Unpacked version of new.* record */
  int iNewReg;                    /* Register for new.* values */
  int iBlobWrite;                 /* Value returned by preupdate_blobwrite() */
  i64 iKey1;                      /* First key value passed to hook */
  i64 iKey2;                      /* Second key value passed to hook */
  Mem oldipk;                     /* Memory cell holding "old" IPK value */
  Mem *aNew;                      /* Array of new.* values */
  Table *pTab;                    /* Schema object being updated */
  Index *pPk;                     /* PK index if pTab is WITHOUT ROWID */
};

/*
** An instance of this object is used to pass an vector of values into

    iIdx = sqlite3TableColumnToIndex(p->pPk, iIdx);
  }
  if( iIdx>=p->pCsr->nField || iIdx<0 ){
    rc = SQLITE_RANGE;
    goto preupdate_old_out;
  }

  if( iIdx==p->pTab->iPKey ){
    pMem = *ppValue = &p->oldipk;
    sqlite3VdbeMemSetInt64(pMem, p->iKey1);
  }else{
  /* If the old.* record has not yet been loaded into memory, do so now. */
  if( p->pUnpacked==0 ){
    u32 nRec;
    u8 *aRec;
    /* If the old.* record has not yet been loaded into memory, do so now. */
    if( p->pUnpacked==0 ){
      u32 nRec;
      u8 *aRec;

    assert( p->pCsr->eCurType==CURTYPE_BTREE );
    nRec = sqlite3BtreePayloadSize(p->pCsr->uc.pCursor);
    aRec = sqlite3DbMallocRaw(db, nRec);
    if( !aRec ) goto preupdate_old_out;
    rc = sqlite3BtreePayload(p->pCsr->uc.pCursor, 0, nRec, aRec);
    if( rc==SQLITE_OK ){
      p->pUnpacked = vdbeUnpackRecord(&p->keyinfo, nRec, aRec);
      if( !p->pUnpacked ) rc = SQLITE_NOMEM;
    }
    if( rc!=SQLITE_OK ){
      sqlite3DbFree(db, aRec);
      goto preupdate_old_out;
    }
    p->aRecord = aRec;
  }
      assert( p->pCsr->eCurType==CURTYPE_BTREE );
      nRec = sqlite3BtreePayloadSize(p->pCsr->uc.pCursor);
      aRec = sqlite3DbMallocRaw(db, nRec);
      if( !aRec ) goto preupdate_old_out;
      rc = sqlite3BtreePayload(p->pCsr->uc.pCursor, 0, nRec, aRec);
      if( rc==SQLITE_OK ){
        p->pUnpacked = vdbeUnpackRecord(&p->keyinfo, nRec, aRec);
        if( !p->pUnpacked ) rc = SQLITE_NOMEM;
      }
      if( rc!=SQLITE_OK ){
        sqlite3DbFree(db, aRec);
        goto preupdate_old_out;
      }
      p->aRecord = aRec;
    }

  pMem = *ppValue = &p->pUnpacked->aMem[iIdx];
    pMem = *ppValue = &p->pUnpacked->aMem[iIdx];
  if( iIdx==p->pTab->iPKey ){
    sqlite3VdbeMemSetInt64(pMem, p->iKey1);
  }else if( iIdx>=p->pUnpacked->nField ){
    *ppValue = (sqlite3_value *)columnNullValue();
  }else if( p->pTab->aCol[iIdx].affinity==SQLITE_AFF_REAL ){
    if( pMem->flags & (MEM_Int|MEM_IntReal) ){
      testcase( pMem->flags & MEM_Int );
      testcase( pMem->flags & MEM_IntReal );
      sqlite3VdbeMemRealify(pMem);
    if( iIdx>=p->pUnpacked->nField ){
      *ppValue = (sqlite3_value *)columnNullValue();
    }else if( p->pTab->aCol[iIdx].affinity==SQLITE_AFF_REAL ){
      if( pMem->flags & (MEM_Int|MEM_IntReal) ){
        testcase( pMem->flags & MEM_Int );
        testcase( pMem->flags & MEM_IntReal );
        sqlite3VdbeMemRealify(pMem);
      }
    }
  }

 preupdate_old_out:
  sqlite3Error(db, rc);
  return sqlite3ApiExit(db, rc);
}

**
** The returned value must be freed by the caller using sqlite3ValueFree().
*/
sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe *v, int iVar, u8 aff){
  assert( iVar>0 );
  if( v ){
    Mem *pMem = &v->aVar[iVar-1];
    assert( (v->db->flags & SQLITE_EnableQPSG)==0 );
    assert( (v->db->flags & SQLITE_EnableQPSG)==0 
         || (v->db->mDbFlags & DBFLAG_InternalFunc)!=0 );
    if( 0==(pMem->flags & MEM_Null) ){
      sqlite3_value *pRet = sqlite3ValueNew(v->db);
      if( pRet ){
        sqlite3VdbeMemCopy((Mem *)pRet, pMem);
        sqlite3ValueApplyAffinity(pRet, aff, SQLITE_UTF8);
      }
      return pRet;
    }
  }
  return 0;
}

/*
** Configure SQL variable iVar so that binding a new value to it signals
** to sqlite3_reoptimize() that re-preparing the statement may result
** in a better query plan.
*/
void sqlite3VdbeSetVarmask(Vdbe *v, int iVar){
  assert( iVar>0 );
  assert( (v->db->flags & SQLITE_EnableQPSG)==0 );
  assert( (v->db->flags & SQLITE_EnableQPSG)==0 
       || (v->db->mDbFlags & DBFLAG_InternalFunc)!=0 );
  if( iVar>=32 ){
    v->expmask |= 0x80000000;
  }else{
    v->expmask |= ((u32)1 << (iVar-1));
  }
}

  db->pPreUpdate = &preupdate;
  db->xPreUpdateCallback(db->pPreUpdateArg, db, op, zDb, zTbl, iKey1, iKey2);
  db->pPreUpdate = 0;
  sqlite3DbFree(db, preupdate.aRecord);
  vdbeFreeUnpacked(db, preupdate.keyinfo.nKeyField+1, preupdate.pUnpacked);
  vdbeFreeUnpacked(db, preupdate.keyinfo.nKeyField+1, preupdate.pNewUnpacked);
  sqlite3VdbeMemRelease(&preupdate.oldipk);
  if( preupdate.aNew ){
    int i;
    for(i=0; i<pCsr->nField; i++){
      sqlite3VdbeMemRelease(&preupdate.aNew[i]);
    }
    sqlite3DbNNFreeNN(db, preupdate.aNew);
  }
}
#endif /* SQLITE_ENABLE_PREUPDATE_HOOK */

            }else{
              assert( isCov==WHERE_EXPRIDX );
              WHERETRACE(0x200,
                 ("-> %s might be a covering expression index"
                  " according to whereIsCoveringIndex()\n", pProbe->zName));
            }
          }
        }else if( m==0 ){
        }else if( m==0 
           && (HasRowid(pTab) || pWInfo->pSelect!=0 || sqlite3FaultSim(700))
        ){
          WHERETRACE(0x200,
             ("-> %s a covering index according to bitmasks\n",
             pProbe->zName, m==0 ? "is" : "is not"));
          pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED;
        }
      }

**   5) The table must not have an inner-join ON or USING clause if there is
**      a RIGHT JOIN anywhere in the query.  Otherwise the ON/USING clause
**      might move from the right side to the left side of the RIGHT JOIN.
**      Note: Due to (2), this condition can only arise if the table is
**      the right-most table of a subquery that was flattened into the
**      main query and that subquery was the right-hand operand of an
**      inner join that held an ON or USING clause.
**   6) The ORDER BY clause has 63 or fewer terms
**   7) The omit-noop-join optimization is enabled.
**
** Items (1), (6), and (7) are checked by the caller.
**
** For example, given:
**
**     CREATE TABLE t1(ipk INTEGER PRIMARY KEY, v1);
**     CREATE TABLE t2(ipk INTEGER PRIMARY KEY, v2);
**     CREATE TABLE t3(ipk INTEGER PRIMARY KEY, v3);
**

    tabUsed |= sqlite3WhereExprListUsage(&pWInfo->sMaskSet, pWInfo->pOrderBy);
  }
  hasRightJoin = (pWInfo->pTabList->a[0].fg.jointype & JT_LTORJ)!=0;
  for(i=pWInfo->nLevel-1; i>=1; i--){
    WhereTerm *pTerm, *pEnd;
    SrcItem *pItem;
    WhereLoop *pLoop;
    Bitmask m1;
    pLoop = pWInfo->a[i].pWLoop;
    pItem = &pWInfo->pTabList->a[pLoop->iTab];
    if( (pItem->fg.jointype & (JT_LEFT|JT_RIGHT))!=JT_LEFT ) continue;
    if( (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)==0
     && (pLoop->wsFlags & WHERE_ONEROW)==0
    ){
      continue;

       && pTerm->pExpr->w.iJoin==pItem->iCursor
      ){
        break;  /* restriction (5) */
      }
    }
    if( pTerm<pEnd ) continue;
    WHERETRACE(0xffffffff, ("-> drop loop %c not used\n", pLoop->cId));
    m1 = MASKBIT(i)-1;
    pWInfo->revMask = (m1 & pWInfo->revMask) | ((pWInfo->revMask>>1) & ~m1);
    notReady &= ~pLoop->maskSelf;
    for(pTerm=pWInfo->sWC.a; pTerm<pEnd; pTerm++){
      if( (pTerm->prereqAll & pLoop->maskSelf)!=0 ){
        pTerm->wtFlags |= TERM_CODED;
      }
    }
    if( i!=pWInfo->nLevel-1 ){

  memset(&sWLB, 0, sizeof(sWLB));

  /* An ORDER/GROUP BY clause of more than 63 terms cannot be optimized */
  testcase( pOrderBy && pOrderBy->nExpr==BMS-1 );
  if( pOrderBy && pOrderBy->nExpr>=BMS ){
    pOrderBy = 0;
    wctrlFlags &= ~WHERE_WANT_DISTINCT;
    wctrlFlags |= WHERE_KEEP_ALL_JOINS; /* Disable omit-noop-join opt */
  }

  /* The number of tables in the FROM clause is limited by the number of
  ** bits in a Bitmask
  */
  testcase( pTabList->nSrc==BMS );
  if( pTabList->nSrc>BMS ){

  ** procedure to keep the sqlite3WhereBegin() procedure from becoming
  ** too large.  If sqlite3WhereBegin() becomes too large, that prevents
  ** some C-compiler optimizers from in-lining the
  ** sqlite3WhereCodeOneLoopStart() procedure, and it is important to
  ** in-line sqlite3WhereCodeOneLoopStart() for performance reasons.
  */
  notReady = ~(Bitmask)0;
  if( pWInfo->nLevel>=2
   && pResultSet!=0                         /* these two combine to guarantee */
   && 0==(wctrlFlags & WHERE_AGG_DISTINCT)  /* condition (1) above */
   && OptimizationEnabled(db, SQLITE_OmitNoopJoin)
  if( pWInfo->nLevel>=2       /* Must be a join, or this opt8n is pointless */
   && pResultSet!=0           /* Condition (1) */
   && 0==(wctrlFlags & (WHERE_AGG_DISTINCT|WHERE_KEEP_ALL_JOINS)) /* (1),(6) */
   && OptimizationEnabled(db, SQLITE_OmitNoopJoin)                /* (7) */
  ){
    notReady = whereOmitNoopJoin(pWInfo, notReady);
    nTabList = pWInfo->nLevel;
    assert( nTabList>0 );
  }

  /* Check to see if there are any SEARCH loops that might benefit from

    VdbeOp *pOp
  ){
    if( (db->flags & SQLITE_VdbeAddopTrace)==0 ) return;
    sqlite3VdbePrintOp(0, pc, pOp);
  }
#endif

#ifdef SQLITE_DEBUG
/*
** Return true if cursor iCur is opened by instruction k of the
** bytecode.  Used inside of assert() only.
*/
static int cursorIsOpen(Vdbe *v, int iCur, int k){
  while( k>=0 ){
    VdbeOp *pOp = sqlite3VdbeGetOp(v,k--);
    if( pOp->p1!=iCur ) continue;
    if( pOp->opcode==OP_Close ) return 0;
    if( pOp->opcode==OP_OpenRead ) return 1;
    if( pOp->opcode==OP_OpenWrite ) return 1;
    if( pOp->opcode==OP_OpenDup ) return 1;
    if( pOp->opcode==OP_OpenAutoindex ) return 1;
    if( pOp->opcode==OP_OpenEphemeral ) return 1;
  }
  return 0;
}
#endif /* SQLITE_DEBUG */

/*
** Generate the end of the WHERE loop.  See comments on
** sqlite3WhereBegin() for additional information.
*/
void sqlite3WhereEnd(WhereInfo *pWInfo){
  Parse *pParse = pWInfo->pParse;
  Vdbe *v = pParse->pVdbe;

            pOp->p1 = pLevel->iIdxCur;
            OpcodeRewriteTrace(db, k, pOp);
          }else{
            /* Unable to translate the table reference into an index
            ** reference.  Verify that this is harmless - that the
            ** table being referenced really is open.
            */
#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
            assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
            if( pLoop->wsFlags & WHERE_IDX_ONLY ){
                 || cursorIsOpen(v,pOp->p1,k)
                 || pOp->opcode==OP_Offset
            );
#else
              sqlite3ErrorMsg(pParse, "internal query planner error");
              pParse->rc = SQLITE_INTERNAL;
            assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
                 || cursorIsOpen(v,pOp->p1,k)
            );
#endif
            }
          }
        }else if( pOp->opcode==OP_Rowid ){
          pOp->p1 = pLevel->iIdxCur;
          pOp->opcode = OP_IdxRowid;
          OpcodeRewriteTrace(db, k, pOp);
        }else if( pOp->opcode==OP_IfNullRow ){
          pOp->p1 = pLevel->iIdxCur;

    sqlite3VdbeSetVarmask(pParse->pVdbe, iCol);
    assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
  }else if( op==TK_STRING ){
    assert( !ExprHasProperty(pRight, EP_IntValue) );
     z = (u8*)pRight->u.zToken;
  }
  if( z ){

    /* Count the number of prefix characters prior to the first wildcard */
    /* Count the number of prefix bytes prior to the first wildcard.
    ** or U+fffd character.  If the underlying database has a UTF16LE
    ** encoding, then only consider ASCII characters.  Note that the
    ** encoding of z[] is UTF8 - we are dealing with only UTF8 here in
    ** this code, but the database engine itself might be processing
    ** content using a different encoding. */
    cnt = 0;
    while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
      cnt++;
      if( c==wc[3] && z[cnt]!=0 ) cnt++;
      if( c==wc[3] && z[cnt]!=0 ){
        cnt++;
      }else if( c>=0x80 ){
        const u8 *z2 = z+cnt-1;
        if( sqlite3Utf8Read(&z2)==0xfffd || ENC(db)==SQLITE_UTF16LE ){
          cnt--;
          break;
        }else{
          cnt = (int)(z2-z);
        }
      }
    }

    /* The optimization is possible only if (1) the pattern does not begin
    ** with a wildcard and if (2) the non-wildcard prefix does not end with
    ** an (illegal 0xff) character, or (3) the pattern does not consist of
    ** a single escape character. The second condition is necessary so
    ** that we can increment the prefix key to find an upper bound for the
    ** range search. The third is because the caller assumes that the pattern
    ** consists of at least one character after all escapes have been
    ** removed.  */
    if( (cnt>1 || (cnt>0 && z[0]!=wc[3])) && 255!=(u8)z[cnt-1] ){
    if( (cnt>1 || (cnt>0 && z[0]!=wc[3])) && ALWAYS(255!=(u8)z[cnt-1]) ){
      Expr *pPrefix;

      /* A "complete" match if the pattern ends with "*" or "%" */
      *pisComplete = c==wc[0] && z[cnt+1]==0;
      *pisComplete = c==wc[0] && z[cnt+1]==0 && ENC(db)!=SQLITE_UTF16LE;

      /* Get the pattern prefix.  Remove all escapes from the prefix. */
      pPrefix = sqlite3Expr(db, TK_STRING, (char*)z);
      if( pPrefix ){
        int iFrom, iTo;
        char *zNew;
        assert( !ExprHasProperty(pPrefix, EP_IntValue) );

# 2024-08-19
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# https://sqlite.org/forum/forumpost/eaa0a09786c6368b
#
# Apparently SQLite has been miscomputing leap-year dates before
# the year 0400.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Skip this whole file if date and time functions are omitted
# at compile-time
#
ifcapable {!datetime} {
  finish_test
  return
}

# Data sources:
#   1-10  https://ssd.jpl.nasa.gov/tools/jdc/#/cd
#   11    Jean Meeus, Astronomical Algorithms, ISBN 0-943396-61-1, p.59
#   12    https://en.wikipedia.org/wiki/Julian_day
#   
# ID YEAR MONTH DAY JD
set date5data {
   1 2024     2  29 2460369.5
   2 2024     3   1 2460370.5
   3 2023     2  28 2460003.5
   4 2023     3   1 2460004.5
   5 2000     2  29 2451603.5
   6 2000     3   1 2451604.5
   7 1900     2  28 2415078.5
   8 1900     3   1 2415079.5
   9 1712     2  29 2346413.5
  10 1712     3   1 2346414.5
  11 1977     4  26 2443259.5
  12 2013     1   1 2456293.5
}

foreach {id y m d jd} $date5data {
  set date [format %04d-%02d-%02d $y $m $d]
  do_execsql_test date5-jd$jd {
    SELECT date($::jd);
  } $date
  do_execsql_test date5-cal/$date {
    SELECT julianday($::date);
  } $jd
  for {set i 1} {$y+400*$i<=9999} {incr i} {
    set y2 [expr {$y+400*$i}]
    set date2 [format %04d-%02d-%02d $y2 $m $d]
    set jd2 [expr {$jd+146097*$i}]
    do_execsql_test date5-jd$jd2 {
      SELECT date($::jd2);
    } $date2
    do_execsql_test date5-cal/$date2 {
      SELECT julianday($::date2);
    } $jd2
  }
  for {set i 1} {$y-400*$i>=-4712} {incr i} {
    set y2 [expr {$y-400*$i}]
    if {$y2<0} {
      set date2 [format -%04d-%02d-%02d [expr {-$y2}] $m $d]
    } else {
      set date2 [format %04d-%02d-%02d $y2 $m $d]
    }
    set jd2 [expr {$jd-146097*$i}]
    do_execsql_test date5-jd$jd2 {
      SELECT date($::jd2);
    } $date2
    do_execsql_test date5-cal/$date2 {
      SELECT julianday($::date2);
    } $jd2
  }
}

finish_test

static void reportInvariantFailed(
  sqlite3_stmt *pOrig,   /* The original query */
  sqlite3_stmt *pTest,   /* The alternative test query with a missing row */
  int iRow,              /* Row number in pOrig */
  unsigned int dbOpt,    /* Optimization flags on pOrig */
  int noOpt              /* True if opt flags inverted for pTest */
);

/*
** Special parameter binding, for testing and debugging purposes.
**
**     $int_NNN      ->   integer value NNN
**     $text_TTTT    ->   floating point value TTT with destructor
*/
static void bindDebugParameters(sqlite3_stmt *pStmt){
  int nVar = sqlite3_bind_parameter_count(pStmt);
  int i;
  for(i=0; i<nVar; i++){
    const char *zVar = sqlite3_bind_parameter_name(pStmt, i+1);
    if( zVar==0 ) continue;
    if( strncmp(zVar, "$int_", 5)==0 ){
      sqlite3_bind_int(pStmt, i+1, atoi(&zVar[5]));
    }else
    if( strncmp(zVar, "$text_", 6)==0 ){
      char *zBuf = sqlite3_malloc64( strlen(zVar)-5 );
      if( zBuf ){
        memcpy(zBuf, &zVar[6], strlen(zVar)-5);
        sqlite3_bind_text64(pStmt, i+1, zBuf, -1, sqlite3_free, SQLITE_UTF8);
      }
    }
  }
}

/*
** Do an invariant check on pStmt.  iCnt determines which invariant check to
** perform.  The first check is iCnt==0.
**
** *pbCorrupt is a flag that, if true, indicates that the database file
** is known to be corrupt.  A value of non-zero means "yes, the database

             sqlite3_errmsg(db), zTest);
    }
    sqlite3_free(zTest);
    sqlite3_finalize(pTestStmt);
    return rc;
  }
  sqlite3_free(zTest);
  bindDebugParameters(pTestStmt);
  nCol = sqlite3_column_count(pStmt);
  for(i=0; i<nCol; i++){
    rc = sqlite3_bind_value(pTestStmt,i+1+nParam,sqlite3_column_value(pStmt,i));
    if( rc!=SQLITE_OK && rc!=SQLITE_RANGE ){
      sqlite3_finalize(pTestStmt);
      return rc;
    }

    sqlite3_prepare_v2(db, sqlite3_sql(pStmt), -1, &pCk, 0);
    sqlite3_db_config(db, SQLITE_DBCONFIG_REVERSE_SCANORDER, iOrigRSO, 0);
    if( eVerbosity>=2 ){
      char *zSql = sqlite3_expanded_sql(pCk);
      printf("invariant-validity-check #2:\n%s\n", zSql);
      sqlite3_free(zSql);
    }
    bindDebugParameters(pCk);
    while( (rc = sqlite3_step(pCk))==SQLITE_ROW ){
      for(i=0; i<nCol; i++){
        if( !sameValue(pStmt, i, pTestStmt, i, 0) ) break;
      }
      if( i>=nCol ) break;
    }
    sqlite3_finalize(pCk);

      if( eVerbosity>=2 ){
        char *zSql = sqlite3_expanded_sql(pCk);
        printf("invariant-validity-check #3:\n%s\n", zSql);
        sqlite3_free(zSql);
      }

      sqlite3_reset(pTestStmt);
      bindDebugParameters(pCk);
      while( (rc = sqlite3_step(pTestStmt))==SQLITE_ROW ){
        for(i=0; i<nCol; i++){
          if( !sameValue(pStmt, i, pTestStmt, i, pCk) ) break;
        }
        if( i>=nCol ){
          sqlite3_finalize(pCk);
          goto not_a_fault;

  sqlite3_str_append(pTest, zIn, (int)nIn);
  sqlite3_str_append(pTest, ")", 1);
  rc = sqlite3_prepare_v2(db, sqlite3_str_value(pTest), -1, &pBase, 0);
  if( rc ){
    sqlite3_finalize(pBase);
    pBase = pStmt;
  }
  bindDebugParameters(pBase);
  for(i=0; i<sqlite3_column_count(pStmt); i++){
    const char *zColName = sqlite3_column_name(pBase,i);
    const char *zSuffix = zColName ? strrchr(zColName, ':') : 0;
    if( zSuffix 
     && isdigit(zSuffix[1])
     && (zSuffix[1]>'3' || isdigit(zSuffix[2]))
    ){

do_eqp_test 12.3 {
  SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98;
} {
  QUERY PLAN
  |--SCAN t1
  `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
}

# 2024-09-05 https://sqlite.org/forum/forumpost/8a1e467e905b8d27
# When performing the Omit-Noop-Join optimization, if FROM clause terms
# to the right of the omitted join have the reverse-order bit set in the
# WhereInfo.revMask bitmask, those bits need to be shifted to account
# for the omitted join.
#
reset_db
do_execsql_test 13.0 {
  CREATE TABLE t1(a1 INTEGER PRIMARY KEY, b1 INT);
  CREATE TABLE t2(c2 INT, d2 INTEGER PRIMARY KEY);
  CREATE TABLE t3(e3 INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES(33,0);
  INSERT INTO t2 VALUES(33,1),(33,2);
}
do_execsql_test 13.1 {
  SELECT t1.a1, t2.d2
    FROM (t1 LEFT JOIN t3 ON t3.e3=t1.b1) JOIN t2 ON t2.c2=t1.a1
   WHERE t1.a1=33
   ORDER BY t2.d2 DESC;
} {33 2 33 1}

finish_test

} 456

ifcapable vtab {
  do_execsql_test 4.1 {
    SELECT * FROM json_tree('{"\u0017":1}','$."\x17"');
  } {{\x17} 1 integer 1 1 null {$."\x17"} {$}}
}

# JSON PATH parsing bug involving backslash escapes, reported via
# private email from Florent De'Neve on 2024-09-04.
#
do_execsql_test 5.1 {
  SELECT json_extract('{"A\"Key":1}', '$.A"Key');
} 1
do_execsql_test 5.2 {
  SELECT json_extract('{"A\"Key":1}', '$."A\"Key"');
} 1
do_execsql_test 5.3 {
  SELECT JSON_SET('{}', '$."\"Key"', 1);
} {{{"\"Key":1}}}

finish_test

         EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25'
      }]
      regexp {SCAN t2} $res
    } {1}
  }
  do_test like-9.5.1 {
    set res [sqlite3_exec_hex db {
       SELECT x FROM t2 WHERE x LIKE '%fe%25'
       SELECT 1 FROM t2 WHERE x LIKE '%fe%25'
    }]
  } {0 {}}
  } {0 {1 1}}
  ifcapable explain {
    do_test like-9.5.2 {
      set res [sqlite3_exec_hex db {
         EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25'
      }]
      regexp {INDEX i2} $res
    } {1}
    } {0}
  }

  # Do an SQL statement.  Append the search count to the end of the result.
  #
  proc count sql {
    set ::sqlite_search_count 0
    set ::sqlite_like_count 0

  PRAGMA writable_schema=ON;
  INSERT INTO sqlite_schema DEFAULT VALUES RETURNING sqlite_schema.name;
} {{}}

do_execsql_test 21.1 {
  INSERT INTO sqlite_temp_schema DEFAULT VALUES RETURNING sqlite_temp_schema.name;
} {{}}

#-------------------------------------------------------------------------
reset_db 
do_execsql_test 22.0 {
  PRAGMA writable_schema=ON;
  CREATE TABLE xyz (a);
}
do_catchsql_test 22.1 {
  INSERT INTO sqlite_temp_schema DEFAULT VALUES 
    RETURNING
    (SELECT * FROM xyz AS sqlite_master WHERE a=sqlite_master.name);
} {1 {no such column: sqlite_master.name}}


finish_test

      append sql { UNION ALL SELECT 99999999}
      do_test select7-6.2 {
        catchsql $sql
      } {1 {too many terms in compound SELECT}}
    }
  }
}

# https://issues.chromium.org/issues/358174302
# Need to support an unlimited number of terms in a VALUES clause, even
# if some of those terms contain double-quoted string literals.
#
do_execsql_test select7-6.5 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a,b,c);
}
sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 10
sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 0
do_catchsql_test select7-6.6 {
  INSERT INTO t1 VALUES
    (NULL,0,""),  (X'',0.0,0.0),  (X'',X'',""),  (0.0,0.0,""),  (NULL,NULL,0.0),
    (0,"",0),  (0.0,X'',0),  ("",X'',0.0),  (0.0,X'',NULL),  (0,NULL,""),
    (0,"",NULL),  (0.0,NULL,X''),  ("",X'',NULL),  (NULL,0,""),
    (0,NULL,0),  (X'',X'',0.0);
} {1 {no such column: "" - should this be a string literal in single-quotes?}}
do_execsql_test select7-6.7 {
  SELECT count(*) FROM t1;
} {0}
sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
do_catchsql_test select7-6.8 {
  INSERT INTO t1 VALUES
    (NULL,0,""),  (X'',0.0,0.0),  (X'',X'',""),  (0.0,0.0,""),  (NULL,NULL,0.0),
    (0,"",0),  (0.0,X'',0),  ("",X'',0.0),  (0.0,X'',NULL),  (0,NULL,""),
    (0,"",NULL),  (0.0,NULL,X''),  ("",X'',NULL),  (NULL,0,""),
    (0,NULL,0),  (X'',X'',0.0);
} {0 {}}
do_execsql_test select7-6.9 {
  SELECT count(*) FROM t1;
} {16}

# This block of tests verifies that bug aa92c76cd4 is fixed.
#
do_test select7-7.1 {
  execsql {
    CREATE TABLE t3(a REAL);
    INSERT INTO t3 VALUES(44.0);

  puts $out {aaa|bbb}
  close $out
  forcedelete test.db
  catchcmd :memory: {CREATE TABLE t1(a TEXT, b TEXT, c AS (a||b));
.import shell5.csv t1
SELECT * FROM t1;}
} {0 aaa|bbb|aaabbb}

#-------------------------------------------------------------------------

do_test shell5-8.1 {

  set out [open shell5.csv w]
  fconfigure $out -translation lf
  puts $out x
  close $out

  catchcmd :memory: {.import --csv shell5.csv '""""""""""""""""""""""""""""""""""""""""""""""'}
} {0 {}}

finish_test

  CREATE INDEX t1x ON t1(likely(x));
  INSERT INTO t1 VALUES(1),(2),(4),(8);
}
do_execsql_test 77.2 {
  SELECT max(~likely(x)) FILTER (WHERE true) FROM t1 INDEXED BY t1x GROUP BY x;
} {-2 -3 -5 -9}

# 2024-05-23 https://sqlite.org/forum/forumpost/bf8f43aa522c2299
#
# A bug in group_concat() when used as a window function, reported
# just hours after the 3.46.0 release, though first appearing
# in 3.28.0.
#
# When used as a window function, a group_concat() was not
# correctly distinguishing between NULL and empty-string for
# its return value.
#
do_execsql_test 78.1 {
  SELECT quote(group_concat(x) OVER ()) FROM (SELECT '' AS x);
} ''
do_execsql_test 78.2 {
  SELECT quote(group_concat(x) OVER (
      ORDER BY y RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING
    )) FROM (SELECT 'abc' AS x, 1 AS y);
} NULL

finish_test

  SELECT group_concat(a,',') OVER win FROM t1 
  WINDOW win AS (
    ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  )
} {
  5 5,4 5,4,1 5,4,1,6 5,4,1,6,3 5,4,1,6,3,2
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 2.0 {
  CREATE TABLE t1(x);
}

sqlite3_create_aggregate db

breakpoint
do_catchsql_test 2.1 {
  SELECT min(x) OVER w1 FROM t1
    WINDOW w1 AS (PARTITION BY x_count(x) OVER w1);
} {1 {x_count() may not be used as a window function}}

do_catchsql_test 2.2 {
  SELECT min(x) FILTER (WHERE x_count(x) OVER w1) OVER w1 FROM t1
    WINDOW w1 AS (PARTITION BY x OVER w1);
} {1 {near "OVER": syntax error}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  BEGIN TRANSACTION;
    CREATE TABLE t2(c1 INT, c2 REAL);
    INSERT INTO t2 VALUES
    (447,0.0), (448,0.0), (449,0.0), (452,0.0), (453,0.0), (454,0.0), (455,0.0),
    (456,0.0), (459,0.0), (460,0.0), (462,0.0), (463,0.0), (466,0.0), (467,0.0),
    (468,0.0), (469,0.0), (470,0.0), (473,0.0), (474,0.0), (475,0.0), (476,0.0),
    (477,0.0), (480,0.0), (481,0.0), (482,0.0), (483,0.0), (484,0.0), (487,0.0),
    (488,0.0), (489,0.0), (490,0.0), (491,0.0), (494,0.0), (495,0.0), (496,0.0),
    (497,0.0), (498,0.0), (501,0.0), (502,0.0), (503,0.0), (504,0.0), (505,0.0),
    (508,0.0), (509,0.0), (510,0.0), (511,0.0), (512,0.0), (515,0.0), (516,0.0),
    (517,0.0), (518,0.0), (519,0.0), (522,0.0), (523,0.0), (524,0.0), (525,0.0),
    (526,0.0), (529,0.0), (530,0.0), (531,0.0), (532,0.0), (533,0.0), (536,0.0),
    (537,1.0), (538,0.0), (539,0.0), (540,0.0), (543,0.0), (544,0.0);
  COMMIT;
}

do_execsql_test 3.1 {
  select c1, max(c2) over (order by c1 range 366.0 preceding) from t2;
} {
  447 0.0 448 0.0 449 0.0 452 0.0 453 0.0 454 0.0 455 0.0 456 0.0 459 0.0 
  460 0.0 462 0.0 463 0.0 466 0.0 467 0.0 468 0.0 469 0.0 470 0.0 473 0.0 
  474 0.0 475 0.0 476 0.0 477 0.0 480 0.0 481 0.0 482 0.0 483 0.0 484 0.0 
  487 0.0 488 0.0 489 0.0 490 0.0 491 0.0 494 0.0 495 0.0 496 0.0 497 0.0 
  498 0.0 501 0.0 502 0.0 503 0.0 504 0.0 505 0.0 508 0.0 509 0.0 510 0.0 
  511 0.0 512 0.0 515 0.0 516 0.0 517 0.0 518 0.0 519 0.0 522 0.0 523 0.0 
  524 0.0 525 0.0 526 0.0 529 0.0 530 0.0 531 0.0 532 0.0 533 0.0 536 0.0 
  537 1.0 538 1.0 539 1.0 540 1.0 543 1.0 544 1.0
}


finish_test