SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.365 2008/04/01 18:04:11 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**

    pExpr->iColumn = pExpr->op;
    pExpr->op = TK_REGISTER;
  }
  return inReg;
}

/*
** Return TRUE if pExpr is an constant expression that is appropriate
** for factoring out of a loop.  Appropriate expressions are:
**
**    *  Any expression that evaluates to two or more opcodes.
**
**    *  Any OP_Integer, OP_Real, OP_String, OP_Blob, OP_Null, 
**       or OP_Variable that does not need to be placed in a 
**       specific register.
**
** There is no point in factoring out single-instruction constant
** expressions that need to be placed in a particular register.  
** We could factor them out, but then we would end up adding an
** OP_SCopy instruction to move the value into the correct register
** later.  We might as well just use the original instruction and
** avoid the OP_SCopy.
*/
static int isAppropriateForFactoring(Expr *p){
  if( !sqlite3ExprIsConstantNotJoin(p) ){
    return 0;  /* Only constant expressions are appropriate for factoring */
  }
  if( (p->flags & EP_FixedDest)==0 ){
    return 1;  /* Any constant without a fixed destination is appropriate */
  }
  while( p->op==TK_UPLUS ) p = p->pLeft;
  switch( p->op ){
#ifndef SQLITE_OMIT_BLOB_LITERAL
    case TK_BLOB:
#endif
    case TK_VARIABLE:
    case TK_INTEGER:
    case TK_FLOAT:
    case TK_NULL:
    case TK_STRING: {
      testcase( p->op==TK_BLOB );
      testcase( p->op==TK_VARIABLE );
      testcase( p->op==TK_INTEGER );
      testcase( p->op==TK_FLOAT );
      testcase( p->op==TK_NULL );
      testcase( p->op==TK_STRING );
      /* Single-instruction constants with a fixed destination are
      ** better done in-line.  If we factor them, they will just end
      ** up generating an OP_SCopy to move the value to the destination
      ** register. */
      return 0;
    }
    case TK_UMINUS: {
       if( p->pLeft->op==TK_FLOAT || p->pLeft->op==TK_INTEGER ){
         return 0;
       }
       break;
    }
    default: {
      break;
    }
  }
  return 1;
}

/*
** If pExpr is a constant expression that is appropriate for
** factoring out of a loop, then evaluate the expression
** into a register and convert the expression into a TK_REGISTER
** expression.
*/
static int evalConstExpr(void *pArg, Expr *pExpr){
  Parse *pParse = (Parse*)pArg;
  switch( pExpr->op ){
    case TK_REGISTER: {
      return 1;
    }
    case TK_FUNCTION:
    case TK_AGG_FUNCTION:
    case TK_CONST_FUNC: {
      /* The arguments to a function have a fixed destination.
      ** Mark them this way to avoid generated unneeded OP_SCopy
      ** instructions. 
      */
      ExprList *pList = pExpr->pList;
      if( pList ){
        int i = pList->nExpr;
        struct ExprList_item *pItem = pList->a;
        for(; i>0; i--, pItem++){
          if( pItem->pExpr ) pItem->pExpr->flags |= EP_FixedDest;
        }
      }
      break;
    }
  }
  if( isAppropriateForFactoring(pExpr) ){
    int r1 = ++pParse->nMem;
    int r2;
    r2 = sqlite3ExprCodeTarget(pParse, pExpr, r1);
    if( r1!=r2 ) sqlite3ReleaseTempReg(pParse, r1);
    pExpr->iColumn = pExpr->op;
    pExpr->op = TK_REGISTER;
    pExpr->iTable = r2;

/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.688 2008/04/01 18:04:11 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if it was run
** (otherwise we get an empty default).

#define EP_Error      0x0008  /* Expression contains one or more errors */
#define EP_Distinct   0x0010  /* Aggregate function with DISTINCT keyword */
#define EP_VarSelect  0x0020  /* pSelect is correlated, not constant */
#define EP_Dequoted   0x0040  /* True if the string has been dequoted */
#define EP_InfixFunc  0x0080  /* True for an infix function: LIKE, GLOB, etc */
#define EP_ExpCollate 0x0100  /* Collating sequence specified explicitly */
#define EP_AnyAff     0x0200  /* Can take a cached column of any affinity */
#define EP_FixedDest  0x0400  /* Result needed in a specific register */

/*
** These macros can be used to test, set, or clear bits in the 
** Expr.flags field.
*/
#define ExprHasProperty(E,P)     (((E)->flags&(P))==(P))
#define ExprHasAnyProperty(E,P)  (((E)->flags&(P))!=0)

#
#***********************************************************************
#
# Test cases designed to exercise and verify the logic for
# factoring constant expressions out of loops and for
# common subexpression eliminations.
#
# $Id: cse.test,v 1.5 2008/04/01 18:04:11 drh Exp $
#

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

do_test cse-1.1 {
  execsql {

  }
} {11 11 12 22 21 22}
do_test cse-1.5 {
  execsql {
    SELECT CASE a WHEN 1 THEN b WHEN 2 THEN c ELSE d END, b, c, d FROM t1
  }
} {11 11 12 13 22 21 22 23}
do_test cse-1.6.1 {
  execsql {
    SELECT CASE b WHEN 11 THEN -b WHEN 21 THEN -c ELSE -d END, b, c, d FROM t1
  }
} {-11 11 12 13 -22 21 22 23}
do_test cse-1.6.2 {
  execsql {
    SELECT CASE b+1 WHEN c THEN d WHEN e THEN f ELSE 999 END, b, c, d FROM t1
  }
} {13 11 12 13 23 21 22 23}
do_test cse-1.6.3 {
  execsql {
    SELECT CASE WHEN b THEN d WHEN e THEN f ELSE 999 END, b, c, d FROM t1
  }
} {13 11 12 13 23 21 22 23}
do_test cse-1.6.4 {
  execsql {
    SELECT b, c, d, CASE WHEN b THEN d WHEN e THEN f ELSE 999 END FROM t1
  }
} {11 12 13 13 21 22 23 23}
do_test cse-1.6.5 {
  execsql {
    SELECT b, c, d, CASE WHEN 0 THEN d WHEN e THEN f ELSE 999 END FROM t1
  }
} {11 12 13 15 21 22 23 25}
do_test cse-1.7 {
  execsql {
    SELECT a, -a, ~a, NOT a, NOT NOT a, a-a, a+a, a*a, a/a, a FROM t1
  }
} {1 -1 -2 0 1 0 2 1 1 1 2 -2 -3 0 1 0 4 4 1 2}
do_test cse-1.8 {
  execsql {

  execsql {
    SELECT coalesce(b,c,d,e), a, b, c, d, e FROM t1 WHERE a=2
    UNION ALL
    SELECT coalesce(e,d,c,b), e, d, c, b, a FROM t1 WHERE a=1
  }
} {21 2 21 22 23 24 14 14 13 12 11 1}
do_test cse-1.13 {



  execsql {
     SELECT upper(b), typeof(b), b FROM t1
  }
} {11 integer 11 21 integer 21}
do_test cse-1.14 {



  execsql {
     SELECT b, typeof(b), upper(b), typeof(b), b FROM t1
  }
} {11 integer 11 integer 11 21 integer 21 integer 21}

# Overflow the column cache.  Create queries involving more and more
# columns until the cache overflows.  Verify correct operation throughout.