gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/rowvaluevtab.test (about)

     1  # 2018 October 14
     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  
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  set ::testprefix rowvaluevtab
    17  
    18  ifcapable !vtab {
    19    finish_test
    20    return
    21  }
    22  
    23  register_echo_module db
    24  
    25  do_execsql_test 1.0 {
    26    CREATE TABLE t1(a, b, c);
    27    CREATE INDEX t1b ON t1(b);
    28    INSERT INTO t1 VALUES('one', 1, 1);
    29    INSERT INTO t1 VALUES('two', 1, 2);
    30    INSERT INTO t1 VALUES('three', 1, 3);
    31    INSERT INTO t1 VALUES('four', 2, 1);
    32    INSERT INTO t1 VALUES('five', 2, 2);
    33    INSERT INTO t1 VALUES('six', 2, 3);
    34    INSERT INTO t1 VALUES('seven', 3, 1);
    35    INSERT INTO t1 VALUES('eight', 3, 2);
    36    INSERT INTO t1 VALUES('nine', 3, 3);
    37  
    38    WITH s(i) AS (
    39      SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000
    40    ) INSERT INTO t1 SELECT NULL, NULL, NULL FROM s;
    41    CREATE VIRTUAL TABLE e1 USING echo(t1);
    42  }
    43  
    44  proc do_vfilter4_test {tn sql expected} {
    45    set res [list]
    46    db eval "explain $sql" {
    47      if {$opcode=="VFilter"} {
    48        lappend res $p4
    49      }
    50    }
    51    uplevel [list do_test $tn [list set {} $res] [list {*}$expected]]
    52  }
    53  
    54  do_execsql_test 1.1 {
    55    SELECT a FROM e1 WHERE (b, c) = (2, 2)
    56  } {five}
    57  do_vfilter4_test 1.1f {
    58    SELECT a FROM e1 WHERE (b, c) = (?, ?)
    59  } {{SELECT rowid, a, b, c FROM 't1' WHERE b = ?}}
    60  
    61  do_execsql_test 1.2 {
    62    SELECT a FROM e1 WHERE (b, c) > (2, 2)
    63  } {six seven eight nine}
    64  do_vfilter4_test 1.2f {
    65    SELECT a FROM e1 WHERE (b, c) > (2, 2)
    66  } {
    67    {SELECT rowid, a, b, c FROM 't1' WHERE b >= ?}
    68  }
    69  
    70  do_execsql_test 1.3 {
    71    SELECT a FROM e1 WHERE (b, c) >= (2, 2)
    72  } {five six seven eight nine}
    73  do_vfilter4_test 1.3f {
    74    SELECT a FROM e1 WHERE (b, c) >= (2, 2)
    75  } {
    76    {SELECT rowid, a, b, c FROM 't1' WHERE b >= ?}
    77  }
    78  
    79  do_execsql_test 1.3 {
    80    SELECT a FROM e1 WHERE (b, c) BETWEEN (1, 2) AND (2, 3)
    81  } {two three four five six}
    82  do_vfilter4_test 1.3f {
    83    SELECT a FROM e1 WHERE (b, c) BETWEEN (1, 2) AND (2, 3)
    84  } {
    85    {SELECT rowid, a, b, c FROM 't1' WHERE b >= ? AND b <= ?}
    86  }
    87  
    88  do_execsql_test 1.4 {
    89    SELECT a FROM e1 WHERE (b, c) IN ( VALUES(2, 2) )
    90  } {five}
    91  do_vfilter4_test 1.4f {
    92    SELECT a FROM e1 WHERE (b, c) IN ( VALUES(2, 2) )
    93  } {{SELECT rowid, a, b, c FROM 't1' WHERE b = ?}}
    94  
    95  finish_test