# 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.3 2008/08/04 03:51:24 danielk1977 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

ifcapable !subquery||!compound {

  finish_test

  return

}

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