SQLite

** a known value due to WHERE clause constraints of the form COLUMN=VALUE.
*/
typedef struct WhereConst WhereConst;
struct WhereConst {
  Parse *pParse;   /* Parsing context */
  int nConst;      /* Number for COLUMN=CONSTANT terms */
  int nChng;       /* Number of times a constant is propagated */
  int bHasAffBlob; /* At least one column in apExpr[] as affinity BLOB */
  Expr **apExpr;   /* [i*2] is COLUMN and [i*2+1] is VALUE */
};

/*
** Add a new entry to the pConst object.  Except, do not add duplicate
** pColumn entires.  Also, do not add if doing so would not be appropriate.
**

    const Expr *pE2 = pConst->apExpr[i*2];
    assert( pE2->op==TK_COLUMN );
    if( pE2->iTable==pColumn->iTable
     && pE2->iColumn==pColumn->iColumn
    ){
      return;  /* Already present.  Return without doing anything. */
    }
  }
  if( sqlite3ExprAffinity(pColumn)==SQLITE_AFF_BLOB ){
    pConst->bHasAffBlob = 1;
  }

  pConst->nConst++;
  pConst->apExpr = sqlite3DbReallocOrFree(pConst->pParse->db, pConst->apExpr,
                         pConst->nConst*2*sizeof(Expr*));
  if( pConst->apExpr==0 ){
    pConst->nConst = 0;

  }
  if( pLeft->op==TK_COLUMN && sqlite3ExprIsConstant(pRight) ){
    constInsert(pConst,pLeft,pRight,pExpr);
  }
}

/*
** This is a helper function for Walker callback propagateConstantExprRewrite().
**
** Argument pExpr is a candidate expression to be replaced by a value. If 
** pExpr is equivalent to one of the columns named in pWalker->u.pConst, 
** then overwrite it with the corresponding value. Except, do not do so
** if argument bIgnoreAffBlob is non-zero and the affinity of pExpr
** is SQLITE_AFF_BLOB.
*/
static int propagateConstantExprRewriteOne(
  WhereConst *pConst,
  Expr *pExpr, 
  int bIgnoreAffBlob
){
  int i;

  if( pExpr->op!=TK_COLUMN ) return WRC_Continue;
  if( ExprHasProperty(pExpr, EP_FixedCol|EP_FromJoin) ){
    testcase( ExprHasProperty(pExpr, EP_FixedCol) );
    testcase( ExprHasProperty(pExpr, EP_FromJoin) );
    return WRC_Continue;
  }

  for(i=0; i<pConst->nConst; i++){
    Expr *pColumn = pConst->apExpr[i*2];
    if( pColumn==pExpr ) continue;
    if( pColumn->iTable!=pExpr->iTable ) continue;
    if( pColumn->iColumn!=pExpr->iColumn ) continue;
    if( bIgnoreAffBlob && sqlite3ExprAffinity(pColumn)==SQLITE_AFF_BLOB ){
      break;
    }
    /* A match is found.  Add the EP_FixedCol property */
    pConst->nChng++;
    ExprClearProperty(pExpr, EP_Leaf);
    ExprSetProperty(pExpr, EP_FixedCol);
    assert( pExpr->pLeft==0 );
    pExpr->pLeft = sqlite3ExprDup(pConst->pParse->db, pConst->apExpr[i*2+1], 0);
    break;
  }
  return WRC_Prune;
}

/*
** This is a Walker expression callback. pExpr is a node from the WHERE
** clause of a SELECT statement. This function examines pExpr to see if
** any substitutions based on the contents of pWalker->u.pConst should
** be made to pExpr or its immediate children.
**
** A substitution is made if:
**
**   + pExpr is a column with an affinity other than BLOB that matches
**     one of the columns in pWalker->u.pConst, or
**
**   + pExpr is a binary comparison operator (=, <=, >=, <, >) that
**     uses an affinity other than TEXT and one of its immediate
**     children is a column that matches one of the columns in 
**     pWalker->u.pConst.
*/
static int propagateConstantExprRewrite(Walker *pWalker, Expr *pExpr){
  WhereConst *pConst = pWalker->u.pConst;
  assert( TK_GT==TK_EQ+1 );
  assert( TK_LE==TK_EQ+2 );
  assert( TK_LT==TK_EQ+3 );
  assert( TK_GE==TK_EQ+4 );
  if( pConst->bHasAffBlob ){
    if( (pExpr->op>=TK_EQ && pExpr->op<=TK_GE)
     || pExpr->op==TK_IS
    ){
      propagateConstantExprRewriteOne(pConst, pExpr->pLeft, 0);
      if( sqlite3ExprAffinity(pExpr->pLeft)!=SQLITE_AFF_TEXT ){
        propagateConstantExprRewriteOne(pConst, pExpr->pRight, 0);
      }
    }
  }
  return propagateConstantExprRewriteOne(pConst, pExpr, pConst->bHasAffBlob);
}

/*
** The WHERE-clause constant propagation optimization.
**
** If the WHERE clause contains terms of the form COLUMN=CONSTANT or
** CONSTANT=COLUMN that are top-level AND-connected terms that are not
** part of a ON clause from a LEFT JOIN, then throughout the query

  Walker w;
  int nChng = 0;
  x.pParse = pParse;
  do{
    x.nConst = 0;
    x.nChng = 0;
    x.apExpr = 0;
    x.bHasAffBlob = 0;
    findConstInWhere(&x, p->pWhere);
    if( x.nConst ){
      memset(&w, 0, sizeof(w));
      w.pParse = pParse;
      w.xExprCallback = propagateConstantExprRewrite;
      w.xSelectCallback = sqlite3SelectWalkNoop;
      w.xSelectCallback2 = 0;

# 2021 May 27
#
# 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.
#
#***********************************************************************
# Tests focused on the "constant propagation" that occurs within the
# WHERE clause of a SELECT statemente.
#

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


do_execsql_test 1.0 {
  CREATE TABLE t1(a, b INTEGER, c TEXT, d REAL, e BLOB);
  INSERT INTO t1 VALUES(10.0, 10.0, 10.0, 10.0, 10.0);
  SELECT * FROM t1;
} {
  10.0 10 10.0 10.0 10.0
}

do_execsql_test 1.1.1 {
  SELECT a=10, a = '10.0', a LIKE '10.0' FROM t1;
} {1 0 1}
do_execsql_test 1.1.2 {
  SELECT count(*) FROM t1 WHERE a=10 AND a = '10.0'
} {0}
do_execsql_test 1.1.3 {
  SELECT count(*) FROM t1 WHERE a=10 AND a LIKE '10.0'
} {1}
do_execsql_test 1.1.4 {
  SELECT count(*) FROM t1 WHERE a='10.0' AND a LIKE '10.0'
} {0}

do_execsql_test 1.2.1 {
  SELECT b=10, b = '10.0', b LIKE '10.0', b LIKE '10' FROM t1;
} {1 1 0 1}
do_execsql_test 1.2.2 {
  SELECT count(*) FROM t1 WHERE b=10 AND b = '10.0'
} {1}
do_execsql_test 1.2.3 {
  SELECT count(*) FROM t1 WHERE b=10 AND b LIKE '10.0'
} {0}
do_execsql_test 1.2.4 {
  SELECT count(*) FROM t1 WHERE b='10.0' AND b LIKE '10.0'
} {0}
do_execsql_test 1.2.3 {
  SELECT count(*) FROM t1 WHERE b=10 AND b LIKE '10'
} {1}
do_execsql_test 1.2.4 {
  SELECT count(*) FROM t1 WHERE b='10.0' AND b LIKE '10'
} {1}

do_execsql_test 1.3.1 {
  SELECT c=10, c = 10.0, c = '10.0', c LIKE '10.0' FROM t1;
} {0 1 1 1}
do_execsql_test 1.3.2 {
  SELECT count(*) FROM t1 WHERE c=10 AND c = '10.0'
} {0}
do_execsql_test 1.3.3 {
  SELECT count(*) FROM t1 WHERE c=10 AND c LIKE '10.0'
} {0}
do_execsql_test 1.3.4 {
  SELECT count(*) FROM t1 WHERE c='10.0' AND c LIKE '10.0'
} {1}
do_execsql_test 1.3.5 {
  SELECT count(*) FROM t1 WHERE c=10.0 AND c = '10.0'
} {1}
do_execsql_test 1.3.6 {
  SELECT count(*) FROM t1 WHERE c=10.0 AND c LIKE '10.0'
} {1}

do_execsql_test 1.4.1 {
  SELECT d=10, d = 10.0, d = '10.0', d LIKE '10.0', d LIKE '10' FROM t1;
} {1 1 1 1 0}
do_execsql_test 1.4.2 {
  SELECT count(*) FROM t1 WHERE d=10 AND d = '10.0'
} {1}
do_execsql_test 1.4.3 {
  SELECT count(*) FROM t1 WHERE d=10 AND d LIKE '10.0'
} {1}
do_execsql_test 1.4.4 {
  SELECT count(*) FROM t1 WHERE d='10.0' AND d LIKE '10.0'
} {1}
do_execsql_test 1.4.5 {
  SELECT count(*) FROM t1 WHERE d='10' AND d LIKE '10.0'
} {1}

do_execsql_test 1.5.1 {
  SELECT e=10, e = '10.0', e LIKE '10.0', e LIKE '10' FROM t1;
} {1 0 1 0}
do_execsql_test 1.5.2 {
  SELECT count(*) FROM t1 WHERE e=10 AND e = '10.0'
} {0}
do_execsql_test 1.5.3 {
  SELECT count(*) FROM t1 WHERE e=10 AND e LIKE '10.0'
} {1}
do_execsql_test 1.5.4 {
  SELECT count(*) FROM t1 WHERE e='10.0' AND e LIKE '10.0'
} {0}
do_execsql_test 1.5.5 {
  SELECT count(*) FROM t1 WHERE e=10.0 AND e LIKE '10.0'
} {1}

finish_test