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.260 2007/09/12 15:41:01 drh Exp $
*/
#include "sqliteInt.h"

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

    for(i=0; i<pList->nExpr; i++){
      mask |= exprTableUsage(pMaskSet, pList->a[i].pExpr);
    }
  }
  return mask;
}
static Bitmask exprSelectTableUsage(ExprMaskSet *pMaskSet, Select *pS){
  Bitmask mask = 0;
  while( pS ){


    mask |= exprListTableUsage(pMaskSet, pS->pEList);
    mask |= exprListTableUsage(pMaskSet, pS->pGroupBy);
    mask |= exprListTableUsage(pMaskSet, pS->pOrderBy);
    mask |= exprTableUsage(pMaskSet, pS->pWhere);
    mask |= exprTableUsage(pMaskSet, pS->pHaving);
    pS = pS->pPrior;
  }
  return mask;
}

/*
** Return TRUE if the given operator is one of the operators that is
** allowed for an indexable WHERE clause term.  The allowed operators are

# 2007 Sep 12
#
# 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 is to test that ticket #2640 has been fixed.
#
# $Id: tkt2640.test,v 1.1 2007/09/12 15:41:01 drh Exp $
#

# The problem in ticket #2640 was that the query optimizer was 
# not recognizing all uses of tables within subqueries in the
# WHERE clause.  If the subquery contained a compound SELECT,
# then tables that were used by terms of the compound other than
# the last term would not be recognized as dependencies.
# So if one of the SELECT statements within a compound made
# use of a table that occurs later in a join, the query
# optimizer would not recognize this and would try to evaluate
# the subquery too early, before that tables value had been
# established.

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

do_test tkt2640-1.1 {
  execsql {
    CREATE TABLE persons(person_id, name);
    INSERT INTO persons VALUES(1,'fred');
    INSERT INTO persons VALUES(2,'barney');
    INSERT INTO persons VALUES(3,'wilma');
    INSERT INTO persons VALUES(4,'pebbles');
    INSERT INTO persons VALUES(5,'bambam');
    CREATE TABLE directors(person_id);
    INSERT INTO directors VALUES(5);
    INSERT INTO directors VALUES(3);
    CREATE TABLE writers(person_id);
    INSERT INTO writers VALUES(2);
    INSERT INTO writers VALUES(3);
    INSERT INTO writers VALUES(4);
    SELECT DISTINCT p.name
      FROM persons p, directors d
     WHERE d.person_id=p.person_id
       AND NOT EXISTS (
             SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
             EXCEPT
             SELECT person_id FROM writers w
           );
  }
} {wilma}
do_test tkt2640-1.2 {
  execsql {
    SELECT DISTINCT p.name
      FROM persons p CROSS JOIN directors d
     WHERE d.person_id=p.person_id
       AND NOT EXISTS (
             SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
             EXCEPT
             SELECT person_id FROM writers w
           );
  }
} {wilma}
do_test tkt2640-1.3 {
  execsql {
    SELECT DISTINCT p.name
      FROM directors d CROSS JOIN persons p
     WHERE d.person_id=p.person_id
       AND NOT EXISTS (
             SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
             EXCEPT
             SELECT person_id FROM writers w
           );
  }
} {wilma}
do_test tkt2640-1.4 {
  execsql {
    SELECT DISTINCT p.name
      FROM persons p, directors d
     WHERE d.person_id=p.person_id
       AND NOT EXISTS (
             SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
             EXCEPT
             SELECT person_id FROM writers w
           );
  }
} {wilma}
do_test tkt2640-1.5 {
  execsql {
    SELECT DISTINCT p.name
      FROM persons p CROSS JOIN directors d
     WHERE d.person_id=p.person_id
       AND NOT EXISTS (
             SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
             EXCEPT
             SELECT person_id FROM writers w
           );
  }
} {wilma}
do_test tkt2640-1.6 {
  execsql {
    SELECT DISTINCT p.name
      FROM directors d CROSS JOIN persons p
     WHERE d.person_id=p.person_id
       AND NOT EXISTS (
             SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
             EXCEPT
             SELECT person_id FROM writers w
           );
  }
} {wilma}



finish_test