SQLite

      pIdx->zColAff[n] = aff;
    }
    pIdx->zColAff[n] = 0;
  }
 
  return pIdx->zColAff;
}

/*
** Compute an affinity string for a table.   Space is obtained
** from sqlite3DbMalloc().  The caller is responsible for freeing
** the space when done.
*/
char *sqlite3TableAffinityStr(sqlite3 *db, const Table *pTab){
  char *zColAff;
  zColAff = (char *)sqlite3DbMallocRaw(db, pTab->nCol+1);
  if( zColAff ){
    int i, j;
    for(i=j=0; i<pTab->nCol; i++){
      if( (pTab->aCol[i].colFlags & COLFLAG_VIRTUAL)==0 ){
        zColAff[j++] = pTab->aCol[i].affinity;
      }
    }
    do{
      zColAff[j--] = 0;
    }while( j>=0 && zColAff[j]<=SQLITE_AFF_BLOB );
  }
  return zColAff;  
}

/*
** Make changes to the evolving bytecode to do affinity transformations
** of values that are about to be gathered into a row for table pTab.
**
** For ordinary (legacy, non-strict) tables:
** -----------------------------------------

** the last opcode generated.  The new OP_TypeCheck needs to be inserted
** before the OP_MakeRecord.  The new OP_TypeCheck should use the same
** register set as the OP_MakeRecord.  If iReg>0 then register iReg is
** the first of a series of registers that will form the new record.
** Apply the type checking to that array of registers.
*/
void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){
  int i;
  char *zColAff;
  if( pTab->tabFlags & TF_Strict ){
    if( iReg==0 ){
      /* Move the previous opcode (which should be OP_MakeRecord) forward
      ** by one slot and insert a new OP_TypeCheck where the current
      ** OP_MakeRecord is found */
      VdbeOp *pPrev;

      sqlite3VdbeAddOp2(v, OP_TypeCheck, iReg, pTab->nNVCol);
      sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
    }
    return;
  }
  zColAff = pTab->zColAff;
  if( zColAff==0 ){
    zColAff = sqlite3TableAffinityStr(0, pTab);

    if( !zColAff ){
      sqlite3OomFault(sqlite3VdbeDb(v));
      return;
    }










    pTab->zColAff = zColAff;
  }
  assert( zColAff!=0 );
  i = sqlite3Strlen30NN(zColAff);
  if( i ){
    if( iReg ){
      sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);

    case SRT_EphemTab: {
      int r1 = sqlite3GetTempRange(pParse, nPrefixReg+1);
      testcase( eDest==SRT_Table );
      testcase( eDest==SRT_EphemTab );
      testcase( eDest==SRT_Fifo );
      testcase( eDest==SRT_DistFifo );
      sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nResultCol, r1+nPrefixReg);
      if( pDest->zAffSdst ){
        sqlite3VdbeChangeP4(v, -1, pDest->zAffSdst, nResultCol);
      }
#ifndef SQLITE_OMIT_CTE
      if( eDest==SRT_DistFifo ){
        /* If the destination is DistFifo, then cursor (iParm+1) is open
        ** on an ephemeral index. If the current row is already present
        ** in the index, do not write it to the output. If not, add the
        ** current row to the index and proceed with writing it to the
        ** output table as well.  */

**              (17d1) aggregate, or
**              (17d2) DISTINCT
**        (17e) the subquery may not contain window functions, and
**        (17f) the subquery must not be the RHS of a LEFT JOIN.
**        (17g) either the subquery is the first element of the outer
**              query or there are no RIGHT or FULL JOINs in any arm
**              of the subquery.  (This is a duplicate of condition (27b).)
**        (17h) The corresponding result set expressions in all arms of the
**              compound must have the same affinity.
**
**        The parent and sub-query may contain WHERE clauses. Subject to
**        rules (11), (13) and (14), they may also contain ORDER BY,
**        LIMIT and OFFSET clauses.  The subquery cannot use any compound
**        operator other than UNION ALL because all the other compound
**        operators have an implied DISTINCT which is disallowed by
**        restriction (4).

  /* Restriction (17): If the sub-query is a compound SELECT, then it must
  ** use only the UNION ALL operator. And none of the simple select queries
  ** that make up the compound SELECT are allowed to be aggregate or distinct
  ** queries.
  */
  if( pSub->pPrior ){
    int ii;
    if( pSub->pOrderBy ){
      return 0;  /* Restriction (20) */
    }
    if( isAgg || (p->selFlags & SF_Distinct)!=0 || isOuterJoin>0 ){
      return 0; /* (17d1), (17d2), or (17f) */
    }
    for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){

        return 0;   /* Restrictions (17g), (27b) */
      }
      testcase( pSub1->pSrc->nSrc>1 );
    }

    /* Restriction (18). */
    if( p->pOrderBy ){

      for(ii=0; ii<p->pOrderBy->nExpr; ii++){
        if( p->pOrderBy->a[ii].u.x.iOrderByCol==0 ) return 0;
      }
    }

    /* Restriction (23) */
    if( (p->selFlags & SF_Recursive) ) return 0;

    /* Restriction (17h) */
    for(ii=0; ii<pSub->pEList->nExpr; ii++){
      char aff;
      assert( pSub->pEList->a[ii].pExpr!=0 );
      aff = sqlite3ExprAffinity(pSub->pEList->a[ii].pExpr);
      for(pSub1=pSub->pPrior; pSub1; pSub1=pSub1->pPrior){
        assert( pSub1->pEList!=0 );
        assert( pSub1->pEList->nExpr>ii );
        assert( pSub1->pEList->a[ii].pExpr!=0 );
        if( sqlite3ExprAffinity(pSub1->pEList->a[ii].pExpr)!=aff ){
          return 0;
        }
      }
    }

    if( pSrc->nSrc>1 ){
      if( pParse->nSelect>500 ) return 0;
      if( OptimizationDisabled(db, SQLITE_FlttnUnionAll) ) return 0;
      aCsrMap = sqlite3DbMallocZero(db, ((i64)pParse->nTab+1)*sizeof(int));
      if( aCsrMap ) aCsrMap[0] = pParse->nTab;
    }

        onceAddr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
        VdbeComment((v, "materialize %!S", pItem));
      }else{
        VdbeNoopComment((v, "materialize %!S", pItem));
      }
      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
      ExplainQueryPlan((pParse, 1, "MATERIALIZE %!S", pItem));
      dest.zAffSdst = sqlite3TableAffinityStr(db, pItem->pTab);
      sqlite3Select(pParse, pSub, &dest);
      sqlite3DbFree(db, dest.zAffSdst);
      dest.zAffSdst = 0;
      pItem->pTab->nRowLogEst = pSub->nSelectRow;
      if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
      sqlite3VdbeAddOp2(v, OP_Return, pItem->regReturn, topAddr+1);
      VdbeComment((v, "end %!S", pItem));
      sqlite3VdbeJumpHere(v, topAddr);
      sqlite3ClearTempRegCache(pParse);
      if( pItem->fg.isCte && pItem->fg.isCorrelated==0 ){

*/
struct SelectDest {
  u8 eDest;            /* How to dispose of the results.  One of SRT_* above. */
  int iSDParm;         /* A parameter used by the eDest disposal method */
  int iSDParm2;        /* A second parameter for the eDest disposal method */
  int iSdst;           /* Base register where results are written */
  int nSdst;           /* Number of registers allocated */
  char *zAffSdst;      /* Affinity used for SRT_Set, SRT_Table, and similar */
  ExprList *pOrderBy;  /* Key columns for SRT_Queue and SRT_DistQueue */
};

/*
** During code generation of statements that do inserts into AUTOINCREMENT
** tables, the following information is attached to the Table.u.autoInc.p
** pointer of each autoincrement table to record some side information that

  (u8)(((u32)(B)<(u32)0x80)?(*(A)=(unsigned char)(B)),1:\
  sqlite3PutVarint((A),(B)))
#define getVarint    sqlite3GetVarint
#define putVarint    sqlite3PutVarint


const char *sqlite3IndexAffinityStr(sqlite3*, Index*);
char *sqlite3TableAffinityStr(sqlite3*,const Table*);
void sqlite3TableAffinity(Vdbe*, Table*, int);
char sqlite3CompareAffinity(const Expr *pExpr, char aff2);
int sqlite3IndexAffinityOk(const Expr *pExpr, char idx_affinity);
char sqlite3TableColumnAffinity(const Table*,int);
char sqlite3ExprAffinity(const Expr *pExpr);
int sqlite3Atoi64(const char*, i64*, int, u8);
int sqlite3DecOrHexToI64(const char*, i64*);

/*
**      optimization_control DB OPT BOOLEAN
**
** Enable or disable query optimizations using the sqlite3_test_control()
** interface.  Disable if BOOLEAN is false and enable if BOOLEAN is true.
** OPT is the name of the optimization to be disabled.  OPT can also be a
** list or optimizations names, in which case all optimizations named are
** enabled or disabled.
**
** Each invocation of this control overrides all prior invocations.  The
** changes are not cumulative.
*/
static int SQLITE_TCLAPI optimization_control(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  int i;
  sqlite3 *db;
  const char *zOpt;
  int onoff;
  int mask = 0;
  int cnt = 0;
  static const struct {
    const char *zOptName;
    int mask;
  } aOpt[] = {
    { "all",                 SQLITE_AllOpts        },
    { "none",                0                     },
    { "query-flattener",     SQLITE_QueryFlattener },
    { "groupby-order",       SQLITE_GroupByOrder   },
    { "factor-constants",    SQLITE_FactorOutConst },
    { "distinct-opt",        SQLITE_DistinctOpt    },
    { "cover-idx-scan",      SQLITE_CoverIdxScan   },
    { "order-by-idx-join",   SQLITE_OrderByIdxJoin },
    { "transitive",          SQLITE_Transitive     },
    { "omit-noop-join",      SQLITE_OmitNoopJoin   },
    { "stat4",               SQLITE_Stat4          },
    { "skip-scan",           SQLITE_SkipScan       },
    { "push-down",           SQLITE_PushDown       },
    { "balanced-merge",      SQLITE_BalancedMerge  },
    { "propagate-const",     SQLITE_PropagateConst },

  };

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  if( Tcl_GetBooleanFromObj(interp, objv[3], &onoff) ) return TCL_ERROR;
  zOpt = Tcl_GetString(objv[2]);
  for(i=0; i<sizeof(aOpt)/sizeof(aOpt[0]); i++){
    if( strstr(zOpt, aOpt[i].zOptName)!=0 ){
      mask |= aOpt[i].mask;

      cnt++;
    }
  }
  if( onoff ) mask = ~mask;
  if( cnt==0 ){
    Tcl_AppendResult(interp, "unknown optimization - should be one of:",
                     (char*)0);
    for(i=0; i<sizeof(aOpt)/sizeof(aOpt[0]); i++){
      Tcl_AppendResult(interp, " ", aOpt[i].zOptName, (char*)0);
    }
    return TCL_ERROR;
  }
  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, mask);
  Tcl_SetObjResult(interp, Tcl_NewIntObj(mask));
  return TCL_OK;
}

/*
**     load_static_extension DB NAME ...
**
** Load one or more statically linked extensions.

reset_db
do_execsql_test cast-9.0 {
  CREATE TABLE t0(c0);
  INSERT INTO t0(c0) VALUES (0);
  CREATE VIEW v1(c0, c1) AS 
    SELECT CAST(0.0 AS NUMERIC), COUNT(*) OVER () FROM t0;
  SELECT v1.c0 FROM v1, t0 WHERE v1.c0=0; 
} {0}


finish_test

  1 one 2 two 3 three 4 four 5 five 6 six
}

do_execsql_test 1.3 {
  SELECT a, b FROM i1, t1 WHERE a=x ORDER BY a
} {1 one 2 two 5 five 6 six}


# 2022-10-31 part of ticket 57c47526c34f01e8
# The queries below were causing an assertion fault in
# the comparison operators of the VDBE.
#
reset_db
database_never_corrupt
optimization_control db all 0
do_execsql_test 1.10 {
  CREATE TABLE t0(c0 INT);
  INSERT INTO t0 VALUES(0);
  CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
  INSERT INTO t1_a VALUES(1,'one');
  CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT);
  INSERT INTO t1_b VALUES(2,'two');
  CREATE VIEW t1 AS SELECT a, b FROM t1_a UNION ALL SELECT c, c FROM t1_b;
  SELECT * FROM (SELECT t1.a, t1.b AS b, t0.c0 FROM t0, t1);
} {1 one 0 2 2 0}
do_execsql_test 1.11 {
  SELECT * FROM (SELECT t1.a, t1.b AS b, t0.c0 FROM t0, t1) WHERE b=2;
} {2 2 0}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 2.1.0 {
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES(1, 'one');

reset_db
do_execsql_test 7.1 {
  WITH c1(x) AS (VALUES(0) UNION ALL SELECT 100+x FROM c1 WHERE x<100 UNION ALL SELECT 1+x FROM c1 WHERE x<1)
  SELECT x, y, '|'
    FROM c1 AS x1, (SELECT x+1 AS y FROM c1 WHERE x<1 UNION ALL SELECT 1+x FROM c1 WHERE 1<x) AS x2
   ORDER BY x, y;
} {0 1 | 0 101 | 0 102 | 1 1 | 1 101 | 1 102 | 100 1 | 100 101 | 100 102 | 101 1 | 101 101 | 101 102 |}

# 2022-10-31 ticket https://sqlite.org/src/info/57c47526c34f01e8
# dbsqlfuzz 37230460b46b3b6049f0d768eb801f3428189382
# UNION ALL subqueries or views which have arms with different
# affinities should not be flattened.
#
reset_db
do_execsql_test 8.1 {
  CREATE TABLE t0(c0 INT);
  INSERT INTO t0 VALUES(0);
  CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
  INSERT INTO t1_a VALUES(1,'one');
  INSERT INTO t1_a VALUES(4,'four');
  CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT);
  INSERT INTO t1_b VALUES(2,'two');
  INSERT INTO t1_b VALUES(5,'five');
  CREATE TABLE t1_c(e INTEGER PRIMARY KEY, f TEXT);
  INSERT INTO t1_c VALUES(3,'three');
  INSERT INTO t1_c VALUES(6,'six');
  CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0;
  CREATE VIEW t1 AS 
    SELECT a, b FROM t1_a   UNION ALL
    SELECT c, c FROM t1_b   UNION ALL
    SELECT e, f FROM t1_c;
}
optimization_control db all 1
do_execsql_test 8.2 {
  SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2;
} {2 2 0 {}}
do_execsql_test 8.3 {
  SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2.0;
} {}
do_execsql_test 8.4 {
  SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b='2';
} {2 2 0 {}}
optimization_control db query-flattener,push-down 0
do_execsql_test 8.5 {
  SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2;
} {2 2 0 {}}
do_execsql_test 8.6 {
  SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2.0;
} {}
do_execsql_test 8.7 {
  SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b='2';
} {2 2 0 {}}
optimization_control db all 0
do_execsql_test 8.8 {
  SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2;
} {2 2 0 {}}
do_execsql_test 8.9 {
  SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b=2.0;
} {}
do_execsql_test 8.10 {
  SELECT * FROM (SELECT t1.a, t1.b, t0.c0 AS c, v0.c0 AS d FROM t0 LEFT JOIN v0 ON v0.c0>'0',t1) WHERE b='2';
} {2 2 0 {}}


finish_test