SQLite

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.183 2005/11/21 12:48:24 drh Exp $
** $Id: where.c,v 1.184 2005/11/26 14:08:08 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)

    return 0;
  }
  *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0;
  *pnPattern = cnt;
  return 1;
}
#endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */

/*
** If the pBase expression originated in the ON or USING clause of
** a join, then transfer the appropriate markings over to derived.
*/
static void transferJoinMarkings(Expr *pDerived, Expr *pBase){
  pDerived->flags |= pBase->flags & EP_FromJoin;
  pDerived->iRightJoinTable = pBase->iRightJoinTable;
}


/*
** The input to this routine is an WhereTerm structure with only the
** "pExpr" field filled in.  The job of this routine is to analyze the
** subexpression and populate all the other fields of the WhereTerm
** structure.
**

      pDup = sqlite3Expr(TK_COLUMN, 0, 0, 0);
      if( pDup ){
        pDup->iTable = iCursor;
        pDup->iColumn = iColumn;
      }
      pNew = sqlite3Expr(TK_IN, pDup, 0, 0);
      if( pNew ){
        transferJoinMarkings(pNew, pExpr);
        pNew->pList = pList;
      }else{
        sqlite3ExprListDelete(pList);
      }
      pTerm->pExpr = pNew;
      pTerm->flags |= TERM_DYNAMIC;
      exprAnalyze(pSrc, pMaskSet, pWC, idxTerm);

# 2005 November 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket #1537 is
# fixed.  
#

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

do_test tkt1537-1.1 {
  execsql {
    CREATE TABLE t1(id, a1, a2);
    INSERT INTO t1 VALUES(1, NULL, NULL);
    INSERT INTO t1 VALUES(2, 1, 3);
    CREATE TABLE t2(id, b);
    INSERT INTO t2 VALUES(3, 1);
    INSERT INTO t2 VALUES(4, NULL);
    SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=+b;
  }
} {1 {} {} {} {} 2 1 3 3 1}
do_test tkt1537-1.2 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b;
  }
} {1 {} {} {} {} 2 1 3 3 1}
do_test tkt1537-1.3 {
  execsql {
    SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;
  }
} {3 1 2 1 3 4 {} {} {} {}}
do_test tkt1537-1.4 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
  }
} {1 {} {} {} {} 2 1 3 3 1}
do_test tkt1537-1.5 {
  execsql {
    SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
  }
} {3 1 2 1 3 4 {} {} {} {}}
do_test tkt1537-1.6 {
  execsql {
    CREATE INDEX t1a1 ON t1(a1);
    CREATE INDEX t1a2 ON t1(a2);
    CREATE INDEX t2b ON t2(b);
    SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b;
  }
} {1 {} {} {} {} 2 1 3 3 1}
do_test tkt1537-1.7 {
  execsql {
    SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;
  }
} {3 1 2 1 3 4 {} {} {} {}}
do_test tkt1537-1.8 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
  }
} {1 {} {} {} {} 2 1 3 3 1}
do_test tkt1537-1.9 {
  execsql {
    SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
  }
} {3 1 2 1 3 4 {} {} {} {}}

execsql {
  DROP INDEX t1a1;
  DROP INDEX t1a2;
  DROP INDEX t2b;
}

do_test tkt1537-2.1 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2;
  }
} {1 {} {} {} {} 2 1 3 3 1}
do_test tkt1537-2.2 {
  execsql {
    CREATE INDEX t2b ON t2(b);
    SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2;
  }
} {1 {} {} {} {} 2 1 3 3 1}
do_test tkt1537-2.3 {
  execsql {
    SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2;
  }
} {3 1 2 1 3 4 {} {} {} {}}
do_test tkt1537-2.4 {
  execsql {
    CREATE INDEX t1a1 ON t1(a1);
    CREATE INDEX t1a2 ON t1(a2);
    SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2;
  }
} {3 1 2 1 3 4 {} {} {} {}}

do_test tkt1537-3.1 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b GLOB 'abc*' WHERE t1.id=1;
  }
} {1 {} {} {} {}}
do_test tkt1537-3.2 {
  execsql { 
    SELECT * FROM t2 LEFT JOIN t1 ON a1 GLOB 'abc*' WHERE t2.id=3;
  }
} {3 1 {} {} {}}


finish_test