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

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