/ Check-in [8e79a0c2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fixes for OMIT_SUBQUERY builds: Disable where clause OR->IN optimization. Include EXISTS keyword (for new CREATE TABLE syntax). Test file fixes. (CVS 2943)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:8e79a0c24a03ccf960d6ccfb7c6b9b0f7c614e9b
User & Date: danielk1977 2006-01-14 08:02:28
Context
2006-01-15
00:13
Documentation updates. Fix to date.c. But most importantly: database connections are now allowed to change threads as long as they are not holding a lock. (CVS 2944) check-in: 03c422ec user: drh tags: trunk
2006-01-14
08:02
Fixes for OMIT_SUBQUERY builds: Disable where clause OR->IN optimization. Include EXISTS keyword (for new CREATE TABLE syntax). Test file fixes. (CVS 2943) check-in: 8e79a0c2 user: danielk1977 tags: trunk
2006-01-13
18:06
Account for multi-byte characters in ALTER TABLE code. Fix for #1609. (CVS 2942) check-in: d634f8b2 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  This module is reponsible for
    14     14   ** generating the code that loops through a table looking for applicable
    15     15   ** rows.  Indices are selected and used to speed the search when doing
    16     16   ** so is applicable.  Because this module is responsible for selecting
    17     17   ** indices, you might also think of this module as the "query optimizer".
    18     18   **
    19         -** $Id: where.c,v 1.197 2006/01/13 15:58:43 danielk1977 Exp $
           19  +** $Id: where.c,v 1.198 2006/01/14 08:02:28 danielk1977 Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   
    23     23   /*
    24     24   ** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
    25     25   */
    26     26   #define BMS  (sizeof(Bitmask)*8)
................................................................................
   639    639         pTerm = &pWC->a[idxTerm];
   640    640         pWC->a[idxNew].iParent = idxTerm;
   641    641       }
   642    642       pTerm->nChild = 2;
   643    643     }
   644    644   #endif /* SQLITE_OMIT_BETWEEN_OPTIMIZATION */
   645    645   
   646         -#ifndef SQLITE_OMIT_OR_OPTIMIZATION
          646  +#if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY)
   647    647     /* Attempt to convert OR-connected terms into an IN operator so that
   648    648     ** they can make use of indices.  Example:
   649    649     **
   650    650     **      x = expr1  OR  expr2 = x  OR  x = expr3
   651    651     **
   652    652     ** is converted into
   653    653     **
   654    654     **      x IN (expr1,expr2,expr3)
          655  +  **
          656  +  ** This optimization must be omitted if OMIT_SUBQUERY is defined because
          657  +  ** the compiler for the the IN operator is part of sub-queries.
   655    658     */
   656    659     else if( pExpr->op==TK_OR ){
   657    660       int ok;
   658    661       int i, j;
   659    662       int iColumn, iCursor;
   660    663       WhereClause sOr;
   661    664       WhereTerm *pOrTerm;

Changes to test/alter.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #*************************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this script is testing the ALTER TABLE statement.
    13     13   #
    14         -# $Id: alter.test,v 1.13 2006/01/13 18:06:40 danielk1977 Exp $
           14  +# $Id: alter.test,v 1.14 2006/01/14 08:02:28 danielk1977 Exp $
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
    21     21   ifcapable !altertable {
................................................................................
   556    556   do_test alter-5.1 {
   557    557     string length $::tbl_name
   558    558   } {7}
   559    559   do_test alter-5.2 {
   560    560     execsql "
   561    561       CREATE TABLE ${tbl_name}(a, b, c);
   562    562     "
          563  +  set ::oid [execsql {SELECT max(oid) FROM sqlite_master}]
   563    564     execsql {
   564         -    SELECT sql FROM sqlite_master 
   565         -        WHERE oid = (SELECT max(oid) FROM sqlite_master);
          565  +    SELECT sql FROM sqlite_master WHERE oid = $::oid;
   566    566     }
   567    567   } "{CREATE TABLE ${::tbl_name}(a, b, c)}"
   568    568   set ::tbl_name2 "abcXdef"
   569    569   do_test alter-5.2 {
   570    570     execsql "
   571    571       ALTER TABLE $::tbl_name RENAME TO $::tbl_name2 
   572    572     "
   573    573     execsql {
   574         -    SELECT sql FROM sqlite_master 
   575         -        WHERE oid = (SELECT max(oid) FROM sqlite_master);
          574  +    SELECT sql FROM sqlite_master WHERE oid = $::oid;
   576    575     }
   577    576   } "{CREATE TABLE '${::tbl_name2}'(a, b, c)}"
   578    577   do_test alter-5.3 {
   579    578     execsql "
   580    579       ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name
   581    580     "
   582    581     execsql {
   583         -    SELECT sql FROM sqlite_master 
   584         -        WHERE oid = (SELECT max(oid) FROM sqlite_master);
          582  +    SELECT sql FROM sqlite_master WHERE oid = $::oid;
   585    583     }
   586    584   } "{CREATE TABLE '${::tbl_name}'(a, b, c)}"
   587    585   set ::col_name ghi\1234\jkl
   588    586   do_test alter-5.4 {
   589    587     execsql "
   590    588       ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR
   591    589     "
   592    590     execsql {
   593         -    SELECT sql FROM sqlite_master 
   594         -        WHERE oid = (SELECT max(oid) FROM sqlite_master);
          591  +    SELECT sql FROM sqlite_master WHERE oid = $::oid;
   595    592     }
   596    593   } "{CREATE TABLE '${::tbl_name}'(a, b, c, $::col_name VARCHAR)}"
   597    594   set ::col_name2 B\3421\A
   598    595   do_test alter-5.5 {
   599    596     db close
   600    597     sqlite3 db test.db
   601    598     execsql "
   602    599       ALTER TABLE $::tbl_name ADD COLUMN $::col_name2
   603    600     "
   604    601     execsql {
   605         -    SELECT sql FROM sqlite_master 
   606         -        WHERE oid = (SELECT max(oid) FROM sqlite_master);
          602  +    SELECT sql FROM sqlite_master WHERE oid = $::oid;
   607    603     }
   608    604   } "{CREATE TABLE '${::tbl_name}'(a, b, c, $::col_name VARCHAR, $::col_name2)}"
   609    605   do_test alter-5.6 {
   610    606     execsql "
   611    607       INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5);
   612    608       SELECT $::col_name, $::col_name2 FROM $::tbl_name;
   613    609     "
   614    610   } {4 5}
   615    611   
   616    612   finish_test
   617    613   

Changes to test/check.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing CHECK constraints
    13     13   #
    14         -# $Id: check.test,v 1.6 2005/11/14 22:29:06 drh Exp $
           14  +# $Id: check.test,v 1.7 2006/01/14 08:02:28 danielk1977 Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Only run these tests if the build includes support for CHECK constraints
    20     20   ifcapable !check {
    21     21     finish_test
................................................................................
   147    147   } {1 {constraint failed}}
   148    148   do_test check-2.6 {
   149    149     catchsql {
   150    150       INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
   151    151     }
   152    152   } {1 {constraint failed}}
   153    153   
   154         -do_test check-3.1 {
   155         -  catchsql {
   156         -    CREATE TABLE t3(
   157         -      x, y, z,
   158         -      CHECK( x<(SELECT min(x) FROM t1) )
   159         -    );
   160         -  }
   161         -} {1 {subqueries prohibited in CHECK constraints}}
          154  +ifcapable subquery {
          155  +  do_test check-3.1 {
          156  +    catchsql {
          157  +      CREATE TABLE t3(
          158  +        x, y, z,
          159  +        CHECK( x<(SELECT min(x) FROM t1) )
          160  +      );
          161  +    }
          162  +  } {1 {subqueries prohibited in CHECK constraints}}
          163  +}
          164  +
   162    165   do_test check-3.2 {
   163    166     execsql {
   164    167       SELECT name FROM sqlite_master ORDER BY name
   165    168     }
   166    169   } {t1 t2}
   167    170   do_test check-3.3 {
   168    171     catchsql {

Changes to test/descidx3.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #*************************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this script is descending indices.
    13     13   #
    14         -# $Id: descidx3.test,v 1.1 2006/01/02 18:24:40 drh Exp $
           14  +# $Id: descidx3.test,v 1.2 2006/01/14 08:02:28 danielk1977 Exp $
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # This procedure sets the value of the file-format in file 'test.db'
    21     21   # to $newval. Also, the schema cookie is incremented.
................................................................................
   118    118   } {5 2 4 3 8 6 7 9}
   119    119   do_test descidx3-3.5 {
   120    120     execsql {
   121    121       SELECT i FROM t1 WHERE b>-9999 AND b<x'ffffffff'
   122    122     }
   123    123   } {9 7 6 8 3 4 2 5}
   124    124   
   125         -do_test descidx3-4.1 {
   126         -  execsql {
   127         -    UPDATE t1 SET a=2 WHERE i<6;
   128         -    SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
   129         -  }
   130         -} {8 6 2 4 3}
   131         -do_test descidx3-4.2 {
   132         -  execsql {
   133         -    UPDATE t1 SET a=1;
   134         -    SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
   135         -  }
   136         -} {2 4 3 8 6}
   137         -do_test descidx3-4.3 {
   138         -  execsql {
   139         -    UPDATE t1 SET b=2;
   140         -    SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
   141         -  }
   142         -} {9 7 6 8 3 4 2 5 1}
          125  +ifcapable subquery {
          126  +  # If the subquery capability is not compiled in to the binary, then
          127  +  # the IN(...) operator is not available. Hence these tests cannot be 
          128  +  # run.
          129  +  do_test descidx3-4.1 {
          130  +    execsql {
          131  +      UPDATE t1 SET a=2 WHERE i<6;
          132  +      SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
          133  +    }
          134  +  } {8 6 2 4 3}
          135  +  do_test descidx3-4.2 {
          136  +    execsql {
          137  +      UPDATE t1 SET a=1;
          138  +      SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
          139  +    }
          140  +  } {2 4 3 8 6}
          141  +  do_test descidx3-4.3 {
          142  +    execsql {
          143  +      UPDATE t1 SET b=2;
          144  +      SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
          145  +    }
          146  +  } {9 7 6 8 3 4 2 5 1}
          147  +}
   143    148   
   144    149   finish_test

Changes to test/misc2.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for miscellanous features that were
    14     14   # left out of other test files.
    15     15   #
    16         -# $Id: misc2.test,v 1.22 2005/04/29 02:10:00 drh Exp $
           16  +# $Id: misc2.test,v 1.23 2006/01/14 08:02:28 danielk1977 Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   ifcapable {trigger} {
    22     22   # Test for ticket #360
    23     23   #
................................................................................
   253    253   # Ticket #1229.  Sometimes when a "NEW.X" appears in a SELECT without
   254    254   # a FROM clause deep within a trigger, the code generator is unable to
   255    255   # trace the NEW.X back to an original table and thus figure out its
   256    256   # declared datatype.
   257    257   #
   258    258   # The SQL code below was causing a segfault.
   259    259   #
   260         -do_test misc2-10.1 {
   261         -  execsql {
   262         -    CREATE TABLE t1229(x);
   263         -    CREATE TRIGGER r1229 BEFORE INSERT ON t1229 BEGIN
   264         -      INSERT INTO t1229 SELECT y FROM (SELECT new.x y);
   265         -    END;
   266         -    INSERT INTO t1229 VALUES(1);
   267         -  }
   268         -} {}
          260  +ifcapable subquery {
          261  +  do_test misc2-10.1 {
          262  +    execsql {
          263  +      CREATE TABLE t1229(x);
          264  +      CREATE TRIGGER r1229 BEFORE INSERT ON t1229 BEGIN
          265  +        INSERT INTO t1229 SELECT y FROM (SELECT new.x y);
          266  +      END;
          267  +      INSERT INTO t1229 VALUES(1);
          268  +    }
          269  +  } {}
          270  +}
   269    271   
   270    272   finish_test

Changes to test/misc5.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for miscellanous features that were
    14     14   # left out of other test files.
    15     15   #
    16         -# $Id: misc5.test,v 1.8 2006/01/05 14:22:34 danielk1977 Exp $
           16  +# $Id: misc5.test,v 1.9 2006/01/14 08:02:28 danielk1977 Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   # Build records using the MakeRecord opcode such that the size of the 
    22     22   # header is at the transition point in the size of a varint.
    23     23   #
................................................................................
   435    435   4611686018427387903\
   436    436   4611686018427387904\
   437    437   4611686018427387905"
   438    438   
   439    439   # Ticket #1210.  Do proper reference counting of Table structures
   440    440   # so that deeply nested SELECT statements can be flattened correctly.
   441    441   #
   442         -do_test misc5-3.1 {
   443         -  execsql {
   444         -    CREATE TABLE songs(songid, artist, timesplayed);
   445         -    INSERT INTO songs VALUES(1,'one',1);
   446         -    INSERT INTO songs VALUES(2,'one',2);
   447         -    INSERT INTO songs VALUES(3,'two',3);
   448         -    INSERT INTO songs VALUES(4,'three',5);
   449         -    INSERT INTO songs VALUES(5,'one',7);
   450         -    INSERT INTO songs VALUES(6,'two',11);
   451         -    SELECT DISTINCT artist 
   452         -    FROM (    
   453         -     SELECT DISTINCT artist    
   454         -     FROM songs      
   455         -     WHERE songid IN (    
   456         -      SELECT songid    
   457         -      FROM songs    
   458         -      WHERE LOWER(artist) = (    
   459         -        SELECT DISTINCT LOWER(artist)    
   460         -        FROM (      
   461         -          SELECT DISTINCT artist,sum(timesplayed) AS total      
   462         -          FROM songs      
   463         -          GROUP BY LOWER(artist)      
   464         -          ORDER BY total DESC      
   465         -          LIMIT 10    
   466         -        )    
   467         -        WHERE artist <> '' 
          442  +ifcapable subquery {
          443  +  do_test misc5-3.1 {
          444  +    execsql {
          445  +      CREATE TABLE songs(songid, artist, timesplayed);
          446  +      INSERT INTO songs VALUES(1,'one',1);
          447  +      INSERT INTO songs VALUES(2,'one',2);
          448  +      INSERT INTO songs VALUES(3,'two',3);
          449  +      INSERT INTO songs VALUES(4,'three',5);
          450  +      INSERT INTO songs VALUES(5,'one',7);
          451  +      INSERT INTO songs VALUES(6,'two',11);
          452  +      SELECT DISTINCT artist 
          453  +      FROM (    
          454  +       SELECT DISTINCT artist    
          455  +       FROM songs      
          456  +       WHERE songid IN (    
          457  +        SELECT songid    
          458  +        FROM songs    
          459  +        WHERE LOWER(artist) = (    
          460  +          SELECT DISTINCT LOWER(artist)    
          461  +          FROM (      
          462  +            SELECT DISTINCT artist,sum(timesplayed) AS total      
          463  +            FROM songs      
          464  +            GROUP BY LOWER(artist)      
          465  +            ORDER BY total DESC      
          466  +            LIMIT 10    
          467  +          )    
          468  +          WHERE artist <> '' 
          469  +        )  
          470  +       )       
   468    471         )  
   469         -     )       
   470         -    )  
   471         -    ORDER BY LOWER(artist) ASC;
   472         -  }
   473         -} {two}
          472  +      ORDER BY LOWER(artist) ASC;
          473  +    }
          474  +  } {two}
          475  +}
   474    476   
   475    477   # Ticket #1370.  Do not overwrite small files (less than 1024 bytes)
   476    478   # when trying to open them as a database.
   477    479   #
   478    480   do_test misc5-4.1 {
   479    481     db close
   480    482     file delete -force test.db

Changes to test/select1.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the SELECT statement.
    13     13   #
    14         -# $Id: select1.test,v 1.46 2006/01/13 13:01:20 danielk1977 Exp $
           14  +# $Id: select1.test,v 1.47 2006/01/14 08:02:28 danielk1977 Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Try to select on a non-existant table.
    20     20   #
    21     21   do_test select1-1.1 {
................................................................................
   799    799       }
   800    800     } {x 1 x 3}
   801    801   } ;# ifcapable compound
   802    802   
   803    803   
   804    804   # Check for a VDBE stack growth problem that existed at one point.
   805    805   #
   806         -do_test select1-13.1 {
   807         -  execsql {
   808         -    BEGIN;
   809         -    create TABLE abc(a, b, c, PRIMARY KEY(a, b));
   810         -    INSERT INTO abc VALUES(1, 1, 1);
   811         -  }
   812         -  for {set i 0} {$i<10} {incr i} {
          806  +ifcapable subquery {
          807  +  do_test select1-13.1 {
          808  +    execsql {
          809  +      BEGIN;
          810  +      create TABLE abc(a, b, c, PRIMARY KEY(a, b));
          811  +      INSERT INTO abc VALUES(1, 1, 1);
          812  +    }
          813  +    for {set i 0} {$i<10} {incr i} {
          814  +      execsql {
          815  +        INSERT INTO abc SELECT a+(select max(a) FROM abc), 
          816  +            b+(select max(a) FROM abc), c+(select max(a) FROM abc) FROM abc;
          817  +      }
          818  +    }
          819  +    execsql {COMMIT}
          820  +  
          821  +    # This used to seg-fault when the problem existed.
   813    822       execsql {
   814         -      INSERT INTO abc SELECT a+(select max(a) FROM abc), 
   815         -          b+(select max(a) FROM abc), c+(select max(a) FROM abc) FROM abc;
          823  +      SELECT count(
          824  +        (SELECT a FROM abc WHERE a = NULL AND b >= upper.c) 
          825  +      ) FROM abc AS upper;
   816    826       }
   817         -  }
   818         -  execsql {COMMIT}
   819         -
   820         -  # This used to seg-fault when the problem existed.
   821         -  execsql {
   822         -    SELECT count(
   823         -      (SELECT a FROM abc WHERE a = NULL AND b >= upper.c) 
   824         -    ) FROM abc AS upper;
   825         -  }
   826         -} {0}
          827  +  } {0}
          828  +}
   827    829   
   828    830   finish_test
   829    831   

Changes to test/shared.test.

     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12         -# $Id: shared.test,v 1.11 2006/01/11 14:09:32 danielk1977 Exp $
           12  +# $Id: shared.test,v 1.12 2006/01/14 08:02:29 danielk1977 Exp $
    13     13   
    14     14   set testdir [file dirname $argv0]
    15     15   source $testdir/tester.tcl
    16     16   db close
    17     17   
    18     18   ifcapable !shared_cache {
    19     19     finish_test
................................................................................
   187    187     "
   188    188     execsql {SELECT * FROM sqlite_master} db2
   189    189     execsql {PRAGMA read_uncommitted = 1} db2
   190    190   
   191    191     set ret [list]
   192    192     db2 eval {SELECT i FROM seq} {
   193    193       if {$i < 4} {
   194         -      execsql {
   195         -        INSERT INTO seq SELECT i + (SELECT max(i) FROM seq), x FROM seq;
          194  +      set max [execsql {SELECT max(i) FROM seq}]
          195  +      db eval {
          196  +        INSERT INTO seq SELECT i + $max, x FROM seq;
   196    197         }
   197    198       }
   198    199       lappend ret $i
   199    200     }
   200    201     set ret
   201    202   } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
   202    203   do_test shared-3.1.2 {

Changes to test/tkt1443.test.

    30     30   #
    31     31   # NOTE:  Yes, in spite of the name of this file (tkt1443.test) this
    32     32   # test is for ticket #1433 not #1443.  I mistyped the name when I was
    33     33   # creating the file and I had already checked in the file by the wrong
    34     34   # name be the time I noticed the error.  With CVS it is a really hassle
    35     35   # to change filenames, so I'll just leave it as is.  No harm done.
    36     36   #
    37         -# $Id: tkt1443.test,v 1.2 2005/09/17 13:29:24 drh Exp $
           37  +# $Id: tkt1443.test,v 1.3 2006/01/14 08:02:29 danielk1977 Exp $
    38     38   
    39     39   set testdir [file dirname $argv0]
    40     40   source $testdir/tester.tcl
    41     41   
           42  +ifcapable !subquery {
           43  +  finish_test
           44  +  return
           45  +}
    42     46   
    43     47   # Construct the sample database.
    44     48   #
    45     49   do_test tkt1443-1.0 {
    46     50     sqlite3 db :memory:
    47     51     execsql {
    48     52       CREATE TABLE Items(

Changes to test/tkt1449.test.

    12     12   #
    13     13   # This file implements tests to verify that ticket #1449 has been
    14     14   # fixed.  
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
           19  +
           20  +# Somewhere in tkt1449-1.1 is a VIEW definition that uses a subquery.
           21  +# So we cannot run this file if subqueries are not available.
           22  +ifcapable !subquery {
           23  +  finish_test
           24  +  return
           25  +}
    19     26   
    20     27   # The following schema generated problems in ticket #1449.  We've retained
    21     28   # the original schema here because it is some unbelievably complex, it seemed
    22     29   # like a good test case for SQLite.
    23     30   #
    24     31   do_test tkt1449-1.1 {
    25     32     execsql {

Changes to test/tkt1473.test.

    62     62     }
    63     63   } {}
    64     64   do_test tkt1473-1.9 {
    65     65     execsql {
    66     66       SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=0
    67     67     }
    68     68   } {}
           69  +
           70  +# Everything from this point on depends on sub-queries. So skip it
           71  +# if sub-queries are not available.
           72  +ifcapable !subquery {
           73  +  finish_test
           74  +  return
           75  +}
    69     76   
    70     77   do_test tkt1473-2.2 {
    71     78     execsql {
    72     79       SELECT (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=0)
    73     80     }
    74     81   } {1}
    75     82   do_test tkt1473-2.3 {

Changes to test/tkt1537.test.

    34     34     }
    35     35   } {1 {} {} {} {} 2 1 3 3 1}
    36     36   do_test tkt1537-1.3 {
    37     37     execsql {
    38     38       SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;
    39     39     }
    40     40   } {3 1 2 1 3 4 {} {} {} {}}
    41         -do_test tkt1537-1.4 {
    42         -  execsql {
    43         -    SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
    44         -  }
    45         -} {1 {} {} {} {} 2 1 3 3 1}
    46         -do_test tkt1537-1.5 {
    47         -  execsql {
    48         -    SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
    49         -  }
    50         -} {3 1 2 1 3 4 {} {} {} {}}
           41  +ifcapable subquery {
           42  +  do_test tkt1537-1.4 {
           43  +    execsql {
           44  +      SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
           45  +    }
           46  +  } {1 {} {} {} {} 2 1 3 3 1}
           47  +  do_test tkt1537-1.5 {
           48  +    execsql {
           49  +      SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
           50  +    }
           51  +  } {3 1 2 1 3 4 {} {} {} {}}
           52  +}
    51     53   do_test tkt1537-1.6 {
    52     54     execsql {
    53     55       CREATE INDEX t1a1 ON t1(a1);
    54     56       CREATE INDEX t1a2 ON t1(a2);
    55     57       CREATE INDEX t2b ON t2(b);
    56     58       SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b;
    57     59     }
    58     60   } {1 {} {} {} {} 2 1 3 3 1}
    59     61   do_test tkt1537-1.7 {
    60     62     execsql {
    61     63       SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;
    62     64     }
    63     65   } {3 1 2 1 3 4 {} {} {} {}}
    64         -do_test tkt1537-1.8 {
    65         -  execsql {
    66         -    SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
    67         -  }
    68         -} {1 {} {} {} {} 2 1 3 3 1}
    69         -do_test tkt1537-1.9 {
    70         -  execsql {
    71         -    SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
    72         -  }
    73         -} {3 1 2 1 3 4 {} {} {} {}}
           66  +
           67  +ifcapable subquery {
           68  +  do_test tkt1537-1.8 {
           69  +    execsql {
           70  +      SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
           71  +    }
           72  +  } {1 {} {} {} {} 2 1 3 3 1}
           73  +  do_test tkt1537-1.9 {
           74  +    execsql {
           75  +      SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
           76  +    }
           77  +  } {3 1 2 1 3 4 {} {} {} {}}
           78  +}
    74     79   
    75     80   execsql {
    76     81     DROP INDEX t1a1;
    77     82     DROP INDEX t1a2;
    78     83     DROP INDEX t2b;
    79     84   }
    80     85   

Changes to test/view.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing VIEW statements.
    13     13   #
    14         -# $Id: view.test,v 1.27 2005/08/20 03:03:04 drh Exp $
           14  +# $Id: view.test,v 1.28 2006/01/14 08:02:29 danielk1977 Exp $
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17     17   
    18     18   # Omit this entire file if the library is not configured with views enabled.
    19     19   ifcapable !view {
    20     20     finish_test
    21     21     return
................................................................................
   424    424       CREATE TABLE t4(a COLLATE NOCASE);
   425    425       INSERT INTO t4 VALUES('This');
   426    426       INSERT INTO t4 VALUES('this');
   427    427       INSERT INTO t4 VALUES('THIS');
   428    428       SELECT * FROM t4 WHERE a = 'THIS';
   429    429     }
   430    430   } {This this THIS}
   431         -do_test view-11.2 {
   432         -  execsql {
   433         -    SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS';
   434         -  }
   435         -} {This this THIS}
          431  +ifcapable subquery {
          432  +  do_test view-11.2 {
          433  +    execsql {
          434  +      SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS';
          435  +    }
          436  +  } {This this THIS}
          437  +}
   436    438   do_test view-11.3 {
   437    439     execsql {
   438    440       CREATE VIEW v11 AS SELECT * FROM t4;
   439    441       SELECT * FROM v11 WHERE a = 'THIS';
   440    442     }
   441    443   } {This this THIS}
   442    444   

Changes to test/where2.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the use of indices in WHERE clauses
    13     13   # based on recent changes to the optimizer.
    14     14   #
    15         -# $Id: where2.test,v 1.5 2005/08/13 16:13:06 drh Exp $
           15  +# $Id: where2.test,v 1.6 2006/01/14 08:02:29 danielk1977 Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Build some test data
    21     21   #
    22     22   do_test where2-1.0 {
................................................................................
   120    120     queryplan {
   121    121       SELECT * FROM t1 ORDER BY rowid DESC LIMIT 2
   122    122     }
   123    123   } {100 6 10201 10207 99 6 10000 10006 nosort t1 *}
   124    124   
   125    125   # The IN operator can be used by indices at multiple layers
   126    126   #
   127         -do_test where2-4.1 {
   128         -  queryplan {
   129         -    SELECT * FROM t1 WHERE z IN (10207,10006) AND y IN (10000,10201)
   130         -                     AND x>0 AND x<10
   131         -    ORDER BY w
   132         -  }
   133         -} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
   134         -do_test where2-4.2 {
   135         -  queryplan {
   136         -    SELECT * FROM t1 WHERE z IN (10207,10006) AND y=10000
   137         -                     AND x>0 AND x<10
   138         -    ORDER BY w
   139         -  }
   140         -} {99 6 10000 10006 sort t1 i1zyx}
   141         -do_test where2-4.3 {
   142         -  queryplan {
   143         -    SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
   144         -                     AND x>0 AND x<10
   145         -    ORDER BY w
   146         -  }
   147         -} {99 6 10000 10006 sort t1 i1zyx}
   148         -do_test where2-4.4 {
   149         -  queryplan {
   150         -    SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
   151         -                     AND y IN (10000,10201)
   152         -                     AND x>0 AND x<10
   153         -    ORDER BY w
   154         -  }
   155         -} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
   156         -do_test where2-4.5 {
   157         -  queryplan {
   158         -    SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
   159         -                     AND y IN (SELECT 10000 UNION SELECT 10201)
   160         -                     AND x>0 AND x<10
   161         -    ORDER BY w
   162         -  }
   163         -} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
   164         -do_test where2-4.6 {
   165         -  queryplan {
   166         -    SELECT * FROM t1
   167         -     WHERE x IN (1,2,3,4,5,6,7,8)
   168         -       AND y IN (10000,10001,10002,10003,10004,10005)
   169         -     ORDER BY 2
   170         -  }
   171         -} {99 6 10000 10006 sort t1 i1xy}
          127  +ifcapable subquery {
          128  +  do_test where2-4.1 {
          129  +    queryplan {
          130  +      SELECT * FROM t1 WHERE z IN (10207,10006) AND y IN (10000,10201)
          131  +                       AND x>0 AND x<10
          132  +      ORDER BY w
          133  +    }
          134  +  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
          135  +  do_test where2-4.2 {
          136  +    queryplan {
          137  +      SELECT * FROM t1 WHERE z IN (10207,10006) AND y=10000
          138  +                       AND x>0 AND x<10
          139  +      ORDER BY w
          140  +    }
          141  +  } {99 6 10000 10006 sort t1 i1zyx}
          142  +  do_test where2-4.3 {
          143  +    queryplan {
          144  +      SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
          145  +                       AND x>0 AND x<10
          146  +      ORDER BY w
          147  +    }
          148  +  } {99 6 10000 10006 sort t1 i1zyx}
          149  +  do_test where2-4.4 {
          150  +    queryplan {
          151  +      SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
          152  +                       AND y IN (10000,10201)
          153  +                       AND x>0 AND x<10
          154  +      ORDER BY w
          155  +    }
          156  +  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
          157  +  do_test where2-4.5 {
          158  +    queryplan {
          159  +      SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
          160  +                       AND y IN (SELECT 10000 UNION SELECT 10201)
          161  +                       AND x>0 AND x<10
          162  +      ORDER BY w
          163  +    }
          164  +  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
          165  +  do_test where2-4.6 {
          166  +    queryplan {
          167  +      SELECT * FROM t1
          168  +       WHERE x IN (1,2,3,4,5,6,7,8)
          169  +         AND y IN (10000,10001,10002,10003,10004,10005)
          170  +       ORDER BY 2
          171  +    }
          172  +  } {99 6 10000 10006 sort t1 i1xy}
          173  +
          174  +  # Duplicate entires on the RHS of an IN operator do not cause duplicate
          175  +  # output rows.
          176  +  #
          177  +  do_test where2-4.6 {
          178  +    queryplan {
          179  +      SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
          180  +      ORDER BY w
          181  +    }
          182  +  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
          183  +  do_test where2-4.7 {
          184  +    queryplan {
          185  +      SELECT * FROM t1 WHERE z IN (
          186  +         SELECT 10207 UNION ALL SELECT 10006
          187  +         UNION ALL SELECT 10006 UNION ALL SELECT 10207)
          188  +      ORDER BY w
          189  +    }
          190  +  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
   172    191   
   173         -# Duplicate entires on the RHS of an IN operator do not cause duplicate
   174         -# output rows.
   175         -#
   176         -do_test where2-4.6 {
   177         -  queryplan {
   178         -    SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
   179         -    ORDER BY w
   180         -  }
   181         -} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
   182         -do_test where2-4.7 {
   183         -  queryplan {
   184         -    SELECT * FROM t1 WHERE z IN (
   185         -       SELECT 10207 UNION ALL SELECT 10006
   186         -       UNION ALL SELECT 10006 UNION ALL SELECT 10207)
   187         -    ORDER BY w
   188         -  }
   189         -} {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
          192  +} ;# ifcapable subquery
   190    193   
   191    194   # The use of an IN operator disables the index as a sorter.
   192    195   #
   193    196   do_test where2-5.1 {
   194    197     queryplan {
   195    198       SELECT * FROM t1 WHERE w=99 ORDER BY w
   196    199     }
   197    200   } {99 6 10000 10006 nosort t1 i1w}
   198         -do_test where2-5.2 {
   199         -  queryplan {
   200         -    SELECT * FROM t1 WHERE w IN (99) ORDER BY w
   201         -  }
   202         -} {99 6 10000 10006 sort t1 i1w}
          201  +
          202  +ifcapable subquery {
          203  +  do_test where2-5.2 {
          204  +    queryplan {
          205  +      SELECT * FROM t1 WHERE w IN (99) ORDER BY w
          206  +    }
          207  +  } {99 6 10000 10006 sort t1 i1w}
          208  +}
   203    209   
   204    210   # Verify that OR clauses get translated into IN operators.
   205    211   #
          212  +set ::idx {}
          213  +ifcapable subquery {set ::idx i1w}
   206    214   do_test where2-6.1 {
   207    215     queryplan {
   208    216       SELECT * FROM t1 WHERE w=99 OR w=100 ORDER BY +w
   209    217     }
   210         -} {99 6 10000 10006 100 6 10201 10207 sort t1 i1w}
          218  +} [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
   211    219   do_test where2-6.2 {
   212    220     queryplan {
   213    221       SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=w ORDER BY +w
   214    222     }
   215         -} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 i1w}
          223  +} [list 6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
          224  +
   216    225   do_test where2-6.3 {
   217    226     queryplan {
   218    227       SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w
   219    228     }
   220    229   } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
   221    230   do_test where2-6.4 {
   222    231     queryplan {
   223    232       SELECT * FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w
   224    233     }
   225    234   } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
          235  +
          236  +set ::idx {}
          237  +ifcapable subquery {set ::idx i1zyx}
   226    238   do_test where2-6.5 {
   227    239     queryplan {
   228    240       SELECT b.* FROM t1 a, t1 b
   229    241        WHERE a.w=1 AND (a.y=b.z OR b.z=10)
   230    242        ORDER BY +b.w
   231    243     }
   232         -} {1 0 4 4 2 1 9 10 sort a i1w b i1zyx}
          244  +} [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
   233    245   do_test where2-6.6 {
   234    246     queryplan {
   235    247       SELECT b.* FROM t1 a, t1 b
   236    248        WHERE a.w=1 AND (b.z=10 OR a.y=b.z OR b.z=10)
   237    249        ORDER BY +b.w
   238    250     }
   239         -} {1 0 4 4 2 1 9 10 sort a i1w b i1zyx}
          251  +} [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
   240    252   
   241    253   # Unique queries (queries that are guaranteed to return only a single
   242    254   # row of result) do not call the sorter.  But all tables must give
   243    255   # a unique result.  If any one table in the join does not give a unique
   244    256   # result then sorting is necessary.
   245    257   #
   246    258   do_test where2-7.1 {

Changes to tool/mkkeywordhash.c.

   151    151     { "DROP",             "TK_DROP",         ALWAYS                 },
   152    152     { "END",              "TK_END",          ALWAYS                 },
   153    153     { "EACH",             "TK_EACH",         TRIGGER                },
   154    154     { "ELSE",             "TK_ELSE",         ALWAYS                 },
   155    155     { "ESCAPE",           "TK_ESCAPE",       ALWAYS                 },
   156    156     { "EXCEPT",           "TK_EXCEPT",       COMPOUND               },
   157    157     { "EXCLUSIVE",        "TK_EXCLUSIVE",    ALWAYS                 },
   158         -  { "EXISTS",           "TK_EXISTS",       SUBQUERY               },
          158  +  { "EXISTS",           "TK_EXISTS",       ALWAYS                 },
   159    159     { "EXPLAIN",          "TK_EXPLAIN",      EXPLAIN                },
   160    160     { "FAIL",             "TK_FAIL",         CONFLICT|TRIGGER       },
   161    161     { "FOR",              "TK_FOR",          TRIGGER                },
   162    162     { "FOREIGN",          "TK_FOREIGN",      FKEY                   },
   163    163     { "FROM",             "TK_FROM",         ALWAYS                 },
   164    164     { "FULL",             "TK_JOIN_KW",      ALWAYS                 },
   165    165     { "GLOB",             "TK_LIKE_KW",      ALWAYS                 },