github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/tkt2640.test (about)

     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