Return to tkt2640.test CVS log

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

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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