/ Check-in [bff460ec]
Login

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

Overview
Comment:Add test file where8.test. (CVS 6072)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:bff460ec2c3e250de034a6b34312b897bb2f3749
User & Date: danielk1977 2008-12-29 18:33:33
Context
2008-12-29
23:45
Always make sure WhereClause objects are initialized to zero when they are first allocated. (CVS 6073) check-in: 7d0ae55d user: drh tags: trunk
18:33
Add test file where8.test. (CVS 6072) check-in: bff460ec user: danielk1977 tags: trunk
14:51
Remove an old variable declaration that was commented out using a C++ comment. (CVS 6071) check-in: 0b972f14 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 responsible 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.348 2008/12/29 14:51:06 danielk1977 Exp $
           19  +** $Id: where.c,v 1.349 2008/12/29 18:33:33 danielk1977 Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   
    23     23   /*
    24     24   ** Trace output macros
    25     25   */
    26     26   #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
................................................................................
   730    730   ** A WhereOrTerm object is computed and attached to the term under
   731    731   ** analysis, regardless of the outcome of the analysis.  Hence:
   732    732   **
   733    733   **     WhereTerm.wtFlags   |=  TERM_ORINFO
   734    734   **     WhereTerm.u.pOrInfo  =  a dynamically allocated WhereOrTerm object
   735    735   **
   736    736   ** The term being analyzed must have two or more of OR-connected subterms.
   737         -** A single subterms might be a set of AND-connected sub-subterms.
          737  +** A single subterm might be a set of AND-connected sub-subterms.
   738    738   ** Examples of terms under analysis:
   739    739   **
   740    740   **     (A)     t1.x=t2.y OR t1.x=t2.z OR t1.y=15 OR t1.z=t3.a+5
   741    741   **     (B)     x=expr1 OR expr2=x OR x=expr3
   742    742   **     (C)     t1.x=t2.y OR (t1.x=t2.z AND t1.y=15)
   743    743   **     (D)     x=expr1 OR (y>11 AND y<22 AND z LIKE '*hello*')
   744    744   **     (E)     (p.a=1 AND q.b=2 AND r.c=3) OR (p.x=4 AND q.y=5 AND r.z=6)

Added test/where8.test.

            1  +# 2008 December 23
            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  +# This file implements regression tests for SQLite library. The focus
           12  +# is testing of where.c. More specifically, the focus is the optimization
           13  +# of WHERE clauses that feature the OR operator.
           14  +#
           15  +# $Id: where8.test,v 1.1 2008/12/29 18:33:33 danielk1977 Exp $
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +# Test organization:
           21  +#
           22  +#   where8-1.*: Tests to demonstrate simple cases work with a single table
           23  +#               in the FROM clause.
           24  +#
           25  +#   where8-2.*: Tests surrounding virtual tables and the OR optimization.
           26  +#
           27  +#   where8-3.*: Tests with more than one table in the FROM clause.
           28  +# 
           29  +
           30  +proc execsql_status {sql {db db}} {
           31  +  set result [uplevel $db eval [list $sql]]
           32  +  concat $result [db status step] [db status sort]
           33  +}
           34  +
           35  +proc execsql_status2 {sql {db db}} {
           36  +  set ::sqlite_search_count 0
           37  +breakpoint
           38  +  set result [uplevel [list execsql_status $sql $db]]
           39  +  concat $result $::sqlite_search_count
           40  +}
           41  +
           42  +do_test where8-1.1 {
           43  +  execsql {
           44  +    CREATE TABLE t1(a, b, c);
           45  +    CREATE INDEX i1 ON t1(a);
           46  +    CREATE INDEX i2 ON t1(b);
           47  +
           48  +    INSERT INTO t1 VALUES(1,  'one',   'I');
           49  +    INSERT INTO t1 VALUES(2,  'two',   'II');
           50  +    INSERT INTO t1 VALUES(3,  'three', 'III');
           51  +    INSERT INTO t1 VALUES(4,  'four',  'IV');
           52  +    INSERT INTO t1 VALUES(5,  'five',  'V');
           53  +    INSERT INTO t1 VALUES(6,  'six',   'VI');
           54  +    INSERT INTO t1 VALUES(7,  'seven', 'VII');
           55  +    INSERT INTO t1 VALUES(8,  'eight', 'VIII');
           56  +    INSERT INTO t1 VALUES(9,  'nine',  'IX');
           57  +    INSERT INTO t1 VALUES(10, 'ten',   'X');
           58  +  }
           59  +} {}
           60  +
           61  +do_test where8-1.2 { 
           62  +  execsql_status2 { SELECT c FROM t1 WHERE a = 1 OR b = 'nine' }
           63  +} {I IX 0 0 6}
           64  +
           65  +do_test where8-1.3 { 
           66  +  execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' }
           67  +} {II IX X 0 0 6}
           68  +
           69  +do_test where8-1.4 { 
           70  +  execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' }
           71  +} {II III IX X 0 0 9}
           72  +
           73  +do_test where8-1.5 { 
           74  +  execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' }
           75  +} {IV V IX X 0 0 9}
           76  +
           77  +do_test where8-1.6 { 
           78  +  execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid }
           79  +} {I III 0 0}
           80  +
           81  +do_test where8-1.7 { 
           82  +  execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a }
           83  +} {I III 0 1}
           84  +
           85  +do_test where8-1.8 {
           86  +  # 18 searches. 9 on the index cursor and 9 on the table cursor.
           87  +  execsql_status2 { SELECT c FROM t1 WHERE a > 1 AND c LIKE 'I%' }
           88  +} {II III IV IX 0 0 18}
           89  +
           90  +do_test where8-1.9 {
           91  +  execsql_status2 { SELECT c FROM t1 WHERE a >= 9 OR b <= 'eight' }
           92  +} {VIII IX X 0 0 6}
           93  +
           94  +do_test where8-1.10 {
           95  +  execsql_status2 { 
           96  +    SELECT c FROM t1 WHERE (a >= 9 AND c != 'X') OR b <= 'eight' 
           97  +  }
           98  +} {VIII IX 0 0 7}
           99  +
          100  +do_test where8-1.11 {
          101  +  execsql_status2 { 
          102  +    SELECT c FROM t1 WHERE (a >= 4 AND a <= 6) OR b = 'nine' 
          103  +  }
          104  +} {IV V VI IX 0 0 10}
          105  +
          106  +do_test where8-1.12.1 {
          107  +  execsql_status2 { 
          108  +    SELECT c FROM t1 WHERE a IN(1, 2, 3) OR a = 5
          109  +  }
          110  +} {I II III V 0 0 14}
          111  +
          112  +do_test where8-1.12.2 {
          113  +  execsql_status2 { 
          114  +    SELECT c FROM t1 WHERE +a IN(1, 2, 3) OR +a = 5
          115  +  }
          116  +} {I II III V 9 0 9}
          117  +
          118  +
          119  +#--------------------------------------------------------------------------
          120  +# Tests where8-2.*: Virtual tables
          121  +# 
          122  +
          123  +if 0 {
          124  +ifcapable vtab {
          125  +  # Register the 'echo' module used for testing virtual tables.
          126  +  #
          127  +  register_echo_module [sqlite3_connection_pointer db]
          128  +
          129  +  do_test where8-2.1 {
          130  +    execsql {
          131  +      CREATE VIRTUAL TABLE e1 USING echo(t1);
          132  +      SELECT b FROM e1;
          133  +    }
          134  +  } {one two three four five six seven eight nine ten}
          135  +
          136  +  do_test where8-2.2.1 {
          137  +    set echo_module ""
          138  +    execsql {
          139  +      SELECT c FROM e1 WHERE a=1 OR b='three';
          140  +    }
          141  +  } {I III}
          142  +  do_test where8-2.2.2 {
          143  +    set echo_module
          144  +  } {TODO: What should this be?}
          145  +}
          146  +}
          147  +
          148  +#--------------------------------------------------------------------------
          149  +# Tests where8-3.*: Cases with multiple tables in the FROM clause.
          150  +# 
          151  +do_test where8-3.1 {
          152  +  execsql {
          153  +    CREATE TABLE t2(d, e, f);
          154  +    CREATE INDEX i3 ON t2(d);
          155  +    CREATE INDEX i4 ON t2(e);
          156  +
          157  +    INSERT INTO t2 VALUES(1,  NULL,         'I');
          158  +    INSERT INTO t2 VALUES(2,  'four',       'IV');
          159  +    INSERT INTO t2 VALUES(3,  NULL,         'IX');
          160  +    INSERT INTO t2 VALUES(4,  'sixteen',    'XVI');
          161  +    INSERT INTO t2 VALUES(5,  NULL,         'XXV');
          162  +    INSERT INTO t2 VALUES(6,  'thirtysix',  'XXXVI');
          163  +    INSERT INTO t2 VALUES(7,  'fortynine',  'XLIX');
          164  +    INSERT INTO t2 VALUES(8,  'sixtyeight', 'LXIV');
          165  +    INSERT INTO t2 VALUES(9,  'eightyone',  'LXXXIX');
          166  +    INSERT INTO t2 VALUES(10, NULL,         'C');
          167  +  }
          168  +} {}
          169  +
          170  +do_test where8-3.2 {
          171  +  execsql_status {
          172  +    SELECT a, d FROM t1, t2 WHERE b=e
          173  +  }
          174  +} {4 2 9 0}
          175  +
          176  +do_test where8-3.3 {
          177  +  execsql_status {
          178  +    SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = 6
          179  +  }
          180  +} {2 6 3 6 0 0}
          181  +
          182  +do_test where8-3.4 {
          183  +  execsql_status {
          184  +    SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = a
          185  +  }
          186  +} {2 2 3 3 0 0}
          187  +
          188  +do_test where8-3.5 {
          189  +  execsql_status {
          190  +    SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')
          191  +  }
          192  +} {2 2 2 4 3 3 3 4 0 0}
          193  +
          194  +do_test where8-3.6 {
          195  +  # The first part of the WHERE clause in this query, (a=2 OR a=3) is
          196  +  # transformed into "a IN (2, 3)". This is why the sort is required.
          197  +  #
          198  +  execsql_status {
          199  +    SELECT a, d 
          200  +    FROM t1, t2 
          201  +    WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')
          202  +    ORDER BY t1.rowid
          203  +  }
          204  +} {2 2 2 4 3 3 3 4 0 1}
          205  +do_test where8-3.7 {
          206  +  execsql_status {
          207  +    SELECT a, d 
          208  +    FROM t1, t2 
          209  +    WHERE a = 2 AND (d = a OR e = 'sixteen')
          210  +    ORDER BY t1.rowid
          211  +  }
          212  +} {2 2 2 4 0 0}
          213  +do_test where8-3.8 {
          214  +  execsql_status {
          215  +    SELECT a, d 
          216  +    FROM t1, t2 
          217  +    WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen')
          218  +    ORDER BY t1.rowid
          219  +  }
          220  +} {2 2 2 4 3 3 3 4 0 0}
          221  +
          222  +do_test where8-3.9 {
          223  +  execsql_status {
          224  +    SELECT a, d 
          225  +    FROM t1, t2 
          226  +    WHERE (a = 2 OR b = 'three' OR c = 'IX') AND (d = a OR e = 'sixteen')
          227  +    ORDER BY t1.rowid
          228  +  }
          229  +} {2 2 2 4 3 3 3 4 9 4 9 9 9 0}
          230  +
          231  +finish_test
          232  +