Return to tkt2640.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/tkt2640.test, revision 1.1

1.1     ! misho       1: # 2007 Sep 12
        !             2: #
        !             3: # The author disclaims copyright to this source code. In place of
        !             4: # 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 the
        !            19: # WHERE clause.  If the subquery contained a compound SELECT,
        !            20: # then tables that were used by terms of the compound other than
        !            21: # the last term would not be recognized as dependencies.
        !            22: # So if one of the SELECT statements within a compound made
        !            23: # use of a table that occurs later in a join, the query
        !            24: # optimizer would not recognize this and would try to evaluate
        !            25: # the subquery too early, before that tables value had been
        !            26: # established.
        !            27: 
        !            28: set testdir [file dirname $argv0]
        !            29: source $testdir/tester.tcl
        !            30: 
        !            31: ifcapable !subquery||!compound {
        !            32:   finish_test
        !            33:   return
        !            34: }
        !            35: 
        !            36: do_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.name
        !            52:       FROM persons p, directors d
        !            53:      WHERE d.person_id=p.person_id
        !            54:        AND NOT EXISTS (
        !            55:              SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
        !            56:              EXCEPT
        !            57:              SELECT person_id FROM writers w
        !            58:            );
        !            59:   }
        !            60: } {wilma}
        !            61: do_test tkt2640-1.2 {
        !            62:   execsql {
        !            63:     SELECT DISTINCT p.name
        !            64:       FROM persons p CROSS JOIN directors d
        !            65:      WHERE d.person_id=p.person_id
        !            66:        AND NOT EXISTS (
        !            67:              SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
        !            68:              EXCEPT
        !            69:              SELECT person_id FROM writers w
        !            70:            );
        !            71:   }
        !            72: } {wilma}
        !            73: do_test tkt2640-1.3 {
        !            74:   execsql {
        !            75:     SELECT DISTINCT p.name
        !            76:       FROM directors d CROSS JOIN persons p
        !            77:      WHERE d.person_id=p.person_id
        !            78:        AND NOT EXISTS (
        !            79:              SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
        !            80:              EXCEPT
        !            81:              SELECT person_id FROM writers w
        !            82:            );
        !            83:   }
        !            84: } {wilma}
        !            85: do_test tkt2640-1.4 {
        !            86:   execsql {
        !            87:     SELECT DISTINCT p.name
        !            88:       FROM persons p, directors d
        !            89:      WHERE d.person_id=p.person_id
        !            90:        AND NOT EXISTS (
        !            91:              SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
        !            92:              EXCEPT
        !            93:              SELECT person_id FROM writers w
        !            94:            );
        !            95:   }
        !            96: } {wilma}
        !            97: do_test tkt2640-1.5 {
        !            98:   execsql {
        !            99:     SELECT DISTINCT p.name
        !           100:       FROM persons p CROSS JOIN directors d
        !           101:      WHERE d.person_id=p.person_id
        !           102:        AND NOT EXISTS (
        !           103:              SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
        !           104:              EXCEPT
        !           105:              SELECT person_id FROM writers w
        !           106:            );
        !           107:   }
        !           108: } {wilma}
        !           109: do_test tkt2640-1.6 {
        !           110:   execsql {
        !           111:     SELECT DISTINCT p.name
        !           112:       FROM directors d CROSS JOIN persons p
        !           113:      WHERE d.person_id=p.person_id
        !           114:        AND NOT EXISTS (
        !           115:              SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
        !           116:              EXCEPT
        !           117:              SELECT person_id FROM writers w
        !           118:            );
        !           119:   }
        !           120: } {wilma}
        !           121: 
        !           122: 
        !           123: 
        !           124: finish_test