SQLite

** once prior to any call to seriesColumn() or seriesRowid() or
** seriesEof().
**
** The query plan selected by seriesBestIndex is passed in the idxNum
** parameter.  (idxStr is not used in this implementation.)  idxNum
** is a bitmask showing which constraints are available:
**
**   0x01:    start=VALUE
**   0x02:    stop=VALUE
**   0x04:    step=VALUE


**   0x08:    descending order
**   0x10:    ascending order
**   0x20:    LIMIT  VALUE
**   0x40:    OFFSET  VALUE
**
** This routine should initialize the cursor and position it so that it
** is pointing at the first row, or pointing off the end of the table
** (so that seriesEof() will return true) if the table is empty.
*/
static int seriesFilter(
  sqlite3_vtab_cursor *pVtabCursor,
  int idxNum, const char *idxStrUnused,
  int argc, sqlite3_value **argv
){
  series_cursor *pCur = (series_cursor *)pVtabCursor;
  int i = 0;
  (void)idxStrUnused;
  if( idxNum & 0x01 ){
    pCur->ss.iBase = sqlite3_value_int64(argv[i++]);
  }else{
    pCur->ss.iBase = 0;
  }
  if( idxNum & 0x02 ){
    pCur->ss.iTerm = sqlite3_value_int64(argv[i++]);
  }else{
    pCur->ss.iTerm = 0xffffffff;
  }
  if( idxNum & 0x04 ){
    pCur->ss.iStep = sqlite3_value_int64(argv[i++]);
    if( pCur->ss.iStep==0 ){
      pCur->ss.iStep = 1;
    }else if( pCur->ss.iStep<0 ){
      if( (idxNum & 0x10)==0 ) idxNum |= 0x08;
    }
  }else{
    pCur->ss.iStep = 1;
  }
  if( idxNum & 0x20 ){
    sqlite3_int64 iLimit = sqlite3_value_int64(argv[i++]);
    sqlite3_int64 iTerm;
    if( idxNum & 0x40 ){
      sqlite3_int64 iOffset = sqlite3_value_int64(argv[i++]);
      if( iOffset>0 ){
        pCur->ss.iBase += pCur->ss.iStep*iOffset;
      }
    }
    if( iLimit>=0 ){
      iTerm = pCur->ss.iBase + (iLimit - 1)*pCur->ss.iStep;
      if( pCur->ss.iStep<0 ){
        if( iTerm>pCur->ss.iTerm ) pCur->ss.iTerm = iTerm;
      }else{
        if( iTerm<pCur->ss.iTerm ) pCur->ss.iTerm = iTerm;
      }
    }
  }
  for(i=0; i<argc; i++){
    if( sqlite3_value_type(argv[i])==SQLITE_NULL ){
      /* If any of the constraints have a NULL value, then return no rows.
      ** See ticket https://www.sqlite.org/src/info/fac496b61722daf2 */
      pCur->ss.iBase = 1;
      pCur->ss.iTerm = 0;
      pCur->ss.iStep = 1;
      break;
    }
  }
  if( idxNum & 0x08 ){
    pCur->ss.isReversing = pCur->ss.iStep > 0;
  }else{
    pCur->ss.isReversing = pCur->ss.iStep < 0;
  }
  setupSequence( &pCur->ss );
  return SQLITE_OK;
}

/*
** SQLite will invoke this method one or more times while planning a query
** that uses the generate_series virtual table.  This routine needs to create
** a query plan for each invocation and compute an estimated cost for that
** plan.
**
** In this implementation idxNum is used to represent the
** query plan.  idxStr is unused.
**
** The query plan is represented by bits in idxNum:
**
**   0x01  start = $value  -- constraint exists
**   0x02  stop = $value   -- constraint exists
**   0x04  step = $value   -- constraint exists
**   0x08  output is in descending order
**   0x10  output is in ascending order
**   0x20  LIMIT $value    -- constraint exists
**   0x40  OFFSET $value   -- constraint exists
*/
static int seriesBestIndex(
  sqlite3_vtab *pVTab,
  sqlite3_index_info *pIdxInfo
){
  int i, j;              /* Loop over constraints */
  int idxNum = 0;        /* The query plan bitmask */
#ifndef ZERO_ARGUMENT_GENERATE_SERIES
  int bStartSeen = 0;    /* EQ constraint seen on the START column */
#endif
  int unusableMask = 0;  /* Mask of unusable constraints */
  int nArg = 0;          /* Number of arguments that seriesFilter() expects */
  int aIdx[5];           /* Constraints on start, stop, step, LIMIT, OFFSET */
  const struct sqlite3_index_constraint *pConstraint;

  /* This implementation assumes that the start, stop, and step columns
  ** are the last three columns in the virtual table. */
  assert( SERIES_COLUMN_STOP == SERIES_COLUMN_START+1 );
  assert( SERIES_COLUMN_STEP == SERIES_COLUMN_START+2 );

  aIdx[0] = aIdx[1] = aIdx[2] = aIdx[3] = aIdx[4] = -1;
  pConstraint = pIdxInfo->aConstraint;
  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
    int iCol;    /* 0 for start, 1 for stop, 2 for step */
    int iMask;   /* bitmask for those column */
    int op = pConstraint->op;
    if( op>=SQLITE_INDEX_CONSTRAINT_LIMIT
     && op<=SQLITE_INDEX_CONSTRAINT_OFFSET
    ){
      if( pConstraint->usable==0 ){
        /* do nothing */
      }else if( op==SQLITE_INDEX_CONSTRAINT_LIMIT ){
        aIdx[3] = i;
        idxNum |= 0x20;
      }else{
        assert( op==SQLITE_INDEX_CONSTRAINT_OFFSET );
        aIdx[4] = i;
        idxNum |= 0x40;
      }
      continue;
    }
    if( pConstraint->iColumn<SERIES_COLUMN_START ) continue;
    iCol = pConstraint->iColumn - SERIES_COLUMN_START;
    assert( iCol>=0 && iCol<=2 );
    iMask = 1 << iCol;
#ifndef ZERO_ARGUMENT_GENERATE_SERIES
    if( iCol==0 && op==SQLITE_INDEX_CONSTRAINT_EQ ){
      bStartSeen = 1;
    }
#endif
    if( pConstraint->usable==0 ){
      unusableMask |=  iMask;
      continue;
    }else if( op==SQLITE_INDEX_CONSTRAINT_EQ ){
      idxNum |= iMask;
      aIdx[iCol] = i;
    }
  }
  if( aIdx[3]==0 ){
    /* Ignore OFFSET if LIMIT is omitted */
    idxNum &= ~0x60;
    aIdx[4] = 0;
  }
  for(i=0; i<5; i++){
    if( (j = aIdx[i])>=0 ){
      pIdxInfo->aConstraintUsage[j].argvIndex = ++nArg;
      pIdxInfo->aConstraintUsage[j].omit =
         !SQLITE_SERIES_CONSTRAINT_VERIFY || i>=3;
    }
  }
  /* The current generate_column() implementation requires at least one
  ** argument (the START value).  Legacy versions assumed START=0 if the
  ** first argument was omitted.  Compile with -DZERO_ARGUMENT_GENERATE_SERIES
  ** to obtain the legacy behavior */
#ifndef ZERO_ARGUMENT_GENERATE_SERIES
  if( !bStartSeen ){
    sqlite3_free(pVTab->zErrMsg);
    pVTab->zErrMsg = sqlite3_mprintf(
        "first argument to \"generate_series()\" missing or unusable");
    return SQLITE_ERROR;
  }
#endif
  if( (unusableMask & ~idxNum)!=0 ){
    /* The start, stop, and step columns are inputs.  Therefore if there
    ** are unusable constraints on any of start, stop, or step then
    ** this plan is unusable */
    return SQLITE_CONSTRAINT;
  }
  if( (idxNum & 0x03)==0x03 ){
    /* Both start= and stop= boundaries are available.  This is the 
    ** the preferred case */
    pIdxInfo->estimatedCost = (double)(2 - ((idxNum&4)!=0));
    pIdxInfo->estimatedRows = 1000;
    if( pIdxInfo->nOrderBy>=1 && pIdxInfo->aOrderBy[0].iColumn==0 ){
      if( pIdxInfo->aOrderBy[0].desc ){
        idxNum |= 0x08;
      }else{
        idxNum |= 0x10;
      }
      pIdxInfo->orderByConsumed = 1;
    }
  }else if( (idxNum & 0x21)==0x21 ){
    /* We have start= and LIMIT */
    pIdxInfo->estimatedRows = 2500;
  }else{
    /* If either boundary is missing, we have to generate a huge span
    ** of numbers.  Make this case very expensive so that the query
    ** planner will work hard to avoid it. */
    pIdxInfo->estimatedRows = 2147483647;
  }
  pIdxInfo->idxNum = idxNum;

** Return true if pTerm is a virtual table LIMIT or OFFSET term.
*/
static int isLimitTerm(WhereTerm *pTerm){
  assert( pTerm->eOperator==WO_AUX || pTerm->eMatchOp==0 );
  return pTerm->eMatchOp>=SQLITE_INDEX_CONSTRAINT_LIMIT
      && pTerm->eMatchOp<=SQLITE_INDEX_CONSTRAINT_OFFSET;
}

/*
** Return true if the first nCons constraints in the pUsage array are
** marked as in-use (have argvIndex>0). False otherwise.
*/
static int allConstraintsUsed(
  struct sqlite3_index_constraint_usage *aUsage, 
  int nCons
){
  int ii;
  for(ii=0; ii<nCons; ii++){
    if( aUsage[ii].argvIndex<=0 ) return 0;
  }
  return 1;
}

/*
** Argument pIdxInfo is already populated with all constraints that may
** be used by the virtual table identified by pBuilder->pNew->iTab. This
** function marks a subset of those constraints usable, invokes the
** xBestIndex method and adds the returned plan to pBuilder.
**

        ** (2) Multiple outputs from a single IN value will not merge
        ** together.  */
        pIdxInfo->orderByConsumed = 0;
        pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE;
        *pbIn = 1; assert( (mExclude & WO_IN)==0 );
      }

      /* Unless pbRetryLimit is non-NULL, there should be no LIMIT/OFFSET
      ** terms. And if there are any, they should follow all other terms. */
      assert( pbRetryLimit || !isLimitTerm(pTerm) );
      assert( !isLimitTerm(pTerm) || i>=nConstraint-2 );
      assert( !isLimitTerm(pTerm) || i==nConstraint-1 || isLimitTerm(pTerm+1) );

      if( isLimitTerm(pTerm) && (*pbIn || !allConstraintsUsed(pUsage, i)) ){
        /* If there is an IN(...) term handled as an == (separate call to
        ** xFilter for each value on the RHS of the IN) and a LIMIT or
        ** OFFSET term handled as well, the plan is unusable. Similarly,
        ** if there is a LIMIT/OFFSET and there are other unused terms,
        ** the plan cannot be used. In these cases set variable *pbRetryLimit
        ** to true to tell the caller to retry with LIMIT and OFFSET 
        ** disabled. */
        if( pIdxInfo->needToFreeIdxStr ){
          sqlite3_free(pIdxInfo->idxStr);
          pIdxInfo->idxStr = 0;
          pIdxInfo->needToFreeIdxStr = 0;
        }
        *pbRetryLimit = 1;
        return SQLITE_OK;

        /* If this term has child terms, then they are also part of the
        ** pWC->a[] array. So this term can be ignored, as a LIMIT clause
        ** will only be added if each of the child terms passes the
        ** (leftCursor==iCsr) test below.  */
        continue;
      }
      if( pWC->a[ii].leftCursor!=iCsr ) return;
      if( pWC->a[ii].prereqRight!=0 ) return;
    }

    /* Check condition (5). Return early if it is not met. */
    if( pOrderBy ){
      for(ii=0; ii<pOrderBy->nExpr; ii++){
        Expr *pExpr = pOrderBy->a[ii].pExpr;
        if( pExpr->op!=TK_COLUMN ) return;
        if( pExpr->iTable!=iCsr ) return;
        if( pOrderBy->a[ii].fg.sortFlags & KEYINFO_ORDER_BIGNULL ) return;
      }
    }

    /* All conditions are met. Add the terms to the where-clause object. */
    assert( p->pLimit->op==TK_LIMIT );


    if( p->iOffset!=0 && (p->selFlags & SF_Compound)==0 ){
      whereAddLimitExpr(pWC, p->iOffset, p->pLimit->pRight,
                        iCsr, SQLITE_INDEX_CONSTRAINT_OFFSET);
    }
    if( p->iOffset==0 || (p->selFlags & SF_Compound)==0 ){
      whereAddLimitExpr(pWC, p->iLimit, p->pLimit->pLeft,
                        iCsr, SQLITE_INDEX_CONSTRAINT_LIMIT);
    }
  }
}

/*
** Initialize a preallocated WhereClause structure.
*/
void sqlite3WhereClauseInit(

  set ::lFilterArgs
} {10}

do_test 2.2 {
  set ::lFilterArgs [list]
  execsql { SELECT * FROM vt1 LIMIT 5 OFFSET 50 }
  set ::lFilterArgs
} {{50 5}}

do_test 2.3 {
  set ::lFilterArgs [list]
  execsql { SELECT * FROM vt1 ORDER BY a, b LIMIT 1 OFFSET 1 }
  set ::lFilterArgs
} {{1 1}}

# 2024-04-26
#
# 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.
#
#***********************************************************************
# 
#

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

ifcapable !vtab {
  finish_test
  return
}

register_tcl_module db

proc vtab_command {lVal method args} {
  switch -- $method {
    xConnect {
      return "CREATE TABLE t1(a)"
    }

    xBestIndex {
      set hdl [lindex $args 0]
      set clist [$hdl constraints]
      set orderby [$hdl orderby]

      set idxstr [list]
      set res [list]

      set idx 0
      foreach c $clist {
        array set a $c
        if {$a(usable)==0} continue
        if {$a(op)=="limit" && ![info exists ::do_not_use_limit]} { 
          lappend idxstr limit
          lappend res omit $idx
        }
        if {$a(op)=="offset" && ![info exists ::do_not_use_offset]} { 
          lappend idxstr offset
          lappend res omit $idx
        }
        incr idx
      }

      return "cost 1000000 rows 1000000 idxnum 0 idxstr {$idxstr} $res"
    }

    xFilter {
      set idxstr [lindex $args 1]
      set LIMIT ""
      foreach a $idxstr b [lindex $args 2] {
        set x($a) $b
      }

      if {![info exists x(limit)]} { set x(limit) -1 }
      if {![info exists x(offset)]} { set x(offset) -1 }
      set LIMIT " LIMIT $x(limit) OFFSET $x(offset)"

      set idx 1
      foreach v $lVal {
        lappend lRow "($idx, '$v')"
        incr idx
      }

      return [list sql "
        SELECT * FROM ( VALUES [join $lRow ,]) $LIMIT
      "]
    }
  }

  return {}
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
  CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "A B C D E F a b");
} {}

do_execsql_test 1.1 {
  CREATE TEMP TABLE t_unionall AS 
    SELECT * FROM x1 UNION ALL SELECT * FROM x2;

  CREATE TEMP TABLE t_intersect AS 
    SELECT * FROM x1 INTERSECT SELECT * FROM x2;

  CREATE TEMP TABLE t_union AS 
    SELECT * FROM x1 UNION SELECT * FROM x2;

  CREATE TEMP TABLE t_except AS 
    SELECT * FROM x1 EXCEPT SELECT * FROM x2;
}

foreach {tn limit} {
  1 "LIMIT 8" 
  2 "LIMIT 4" 
  3 "LIMIT 4 OFFSET 2" 
  4 "LIMIT 8 OFFSET 4" 
} {

  foreach {op tbl} {
    "UNION ALL" t_unionall
    "UNION"     t_union
    "INTERSECT" t_intersect
    "EXCEPT"    t_except
  } {

    set expect [execsql "SELECT * FROM $tbl $limit"]
    do_execsql_test 1.2.$tbl.$tn "SELECT * FROM (
      SELECT * FROM x1 $op SELECT * FROM x2
    ) $limit" $expect

  }

}

#-------------------------------------------------------------------------
reset_db
register_tcl_module db

do_execsql_test 2.0 {
  CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
  CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "a b e f");
} {}

do_execsql_test 2.1 {
  SELECT * FROM x1 
    EXCEPT
  SELECT * FROM x2
  LIMIT 3
} {c d}

#-------------------------------------------------------------------------
reset_db
register_tcl_module db
do_execsql_test 3.0 {
  CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "1 2 3 4 5 6 7 8 9 10");
} {}

do_execsql_test 3.1 {
  SELECT * FROM y1 WHERE a = COALESCE('8', a) LIMIT 3
} {8}

do_execsql_test 3.2 {
  SELECT * FROM y1 WHERE a = '2' LIMIT 3
} {2}

load_static_extension db series
do_execsql_test 3.3 {
  SELECT * FROM generate_series(1, 5) WHERE value = (value & 14) LIMIT 3
} {2 4}

do_execsql_test 3.4 {
  SELECT value FROM generate_series(1,10) WHERE value>2 LIMIT 4 OFFSET 1;
} {4 5 6 7}

set ::do_not_use_limit 1
do_execsql_test 3.5 {
  SELECT * FROM y1 LIMIT 5 OFFSET 3
} {4 5 6 7 8}
unset ::do_not_use_limit
set ::do_not_use_offset 1
do_execsql_test 3.6 {
  SELECT * FROM y1 LIMIT 5 OFFSET 3
} {4 5 6 7 8}
unset ::do_not_use_offset



finish_test

  } {1 {table pragma_compile_options may not be altered}}
  do_test tabfunc01-930 {
    catchsql {
      ALTER TABLE pragma_compile_options DROP COLUMN start;
    }
  } {1 {table pragma_compile_options may not be altered}}
}

#-----------------------------------------------------------------------------
# 2024-04-26  LIMIT and OFFSET passed into virtual tables
# https://sqlite.org/forum/forumpost/c243b8f856
#
do_execsql_test tabfunc01-900 {
  SELECT * FROM (
    SELECT * FROM generate_series(1,10)
    UNION ALL
    SELECT * FROM generate_series(101,104)
  ) LIMIT 10 OFFSET 5;
} {6 7 8 9 10 101 102 103 104}
do_execsql_test tabfunc01-910 {
  SELECT * FROM (
    SELECT * FROM generate_series(1,10)
    UNION ALL
    SELECT * FROM generate_series(101,104)
  ) LIMIT -1 OFFSET 5;
} {6 7 8 9 10 101 102 103 104}
do_execsql_test tabfunc01-920 {
  SELECT * FROM (
    SELECT * FROM generate_series(1,10)
    UNION ALL
    SELECT * FROM generate_series(101,104)
  ) LIMIT -1 OFFSET 0;
} {1 2 3 4 5 6 7 8 9 10 101 102 103 104}


# Free up memory allocations
intarray_addr
int64array_addr
doublearray_addr
textarray_addr