AryaWu/sqlite

1# 2007 Sep 122#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11#12# This file is to test that ticket #2640 has been fixed.13#14# $Id: tkt2640.test,v 1.3 2008/08/04 03:51:24 danielk1977 Exp $15#16 17# The problem in ticket #2640 was that the query optimizer was 18# not recognizing all uses of tables within subqueries in the19# WHERE clause.  If the subquery contained a compound SELECT,20# then tables that were used by terms of the compound other than21# the last term would not be recognized as dependencies.22# So if one of the SELECT statements within a compound made23# use of a table that occurs later in a join, the query24# optimizer would not recognize this and would try to evaluate25# the subquery too early, before that tables value had been26# established.27 28set testdir [file dirname $argv0]29source $testdir/tester.tcl30 31ifcapable !subquery||!compound {32  finish_test33  return34}35 36do_test tkt2640-1.1 {37  execsql {38    CREATE TABLE persons(person_id, name);39    INSERT INTO persons VALUES(1,'fred');40    INSERT INTO persons VALUES(2,'barney');41    INSERT INTO persons VALUES(3,'wilma');42    INSERT INTO persons VALUES(4,'pebbles');43    INSERT INTO persons VALUES(5,'bambam');44    CREATE TABLE directors(person_id);45    INSERT INTO directors VALUES(5);46    INSERT INTO directors VALUES(3);47    CREATE TABLE writers(person_id);48    INSERT INTO writers VALUES(2);49    INSERT INTO writers VALUES(3);50    INSERT INTO writers VALUES(4);51    SELECT DISTINCT p.name52      FROM persons p, directors d53     WHERE d.person_id=p.person_id54       AND NOT EXISTS (55             SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id56             EXCEPT57             SELECT person_id FROM writers w58           );59  }60} {wilma}61do_test tkt2640-1.2 {62  execsql {63    SELECT DISTINCT p.name64      FROM persons p CROSS JOIN directors d65     WHERE d.person_id=p.person_id66       AND NOT EXISTS (67             SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id68             EXCEPT69             SELECT person_id FROM writers w70           );71  }72} {wilma}73do_test tkt2640-1.3 {74  execsql {75    SELECT DISTINCT p.name76      FROM directors d CROSS JOIN persons p77     WHERE d.person_id=p.person_id78       AND NOT EXISTS (79             SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id80             EXCEPT81             SELECT person_id FROM writers w82           );83  }84} {wilma}85do_test tkt2640-1.4 {86  execsql {87    SELECT DISTINCT p.name88      FROM persons p, directors d89     WHERE d.person_id=p.person_id90       AND NOT EXISTS (91             SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id92             EXCEPT93             SELECT person_id FROM writers w94           );95  }96} {wilma}97do_test tkt2640-1.5 {98  execsql {99    SELECT DISTINCT p.name100      FROM persons p CROSS JOIN directors d101     WHERE d.person_id=p.person_id102       AND NOT EXISTS (103             SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id104             EXCEPT105             SELECT person_id FROM writers w106           );107  }108} {wilma}109do_test tkt2640-1.6 {110  execsql {111    SELECT DISTINCT p.name112      FROM directors d CROSS JOIN persons p113     WHERE d.person_id=p.person_id114       AND NOT EXISTS (115             SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id116             EXCEPT117             SELECT person_id FROM writers w118           );119  }120} {wilma}121 122 123 124finish_test125