xref: /sqlite-3.40.0/test/rowvalue5.test (revision d96ab995) 
1# 2016 July 29
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
12# focus of this file is syntax errors involving row-values and
13# virtual tables.
14#
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18set ::testprefix rowvalue5
19
20ifcapable !vtab {
21  finish_test
22  return
23}
24
25proc vtab_command {method args} {
26  switch -- $method {
27    xConnect {
28      return "CREATE TABLE t1(a, b, c, d, expr)"
29    }
30
31    xBestIndex {
32      set COL(0) a
33      set COL(1) b
34      set COL(2) c
35      set COL(3) d
36      set COL(4) expr
37
38      set OP(eq) =
39      set OP(ne) !=
40      set OP(gt) >
41      set OP(le) <=
42      set OP(lt) <
43      set OP(ge) >=
44      set OP(match) MATCH
45      set OP(like) LIKE
46      set OP(glob) GLOB
47      set OP(regexp) REGEXP
48
49      set hdl [lindex $args 0]
50      set clist [$hdl constraints]
51
52      set ret [list]
53      set elist [list]
54      set i 0
55      foreach c $clist {
56        array set C $c
57        if {$C(usable)} {
58          lappend ret omit $i
59          lappend elist "$COL($C(column)) $OP($C(op)) %$i%"
60        }
61        incr i
62      }
63
64      lappend ret idxstr [join $elist " AND "]
65      #puts "xBestIndex: $ret"
66      return $ret
67    }
68
69    xFilter {
70      foreach {idxnum idxstr arglist} $args {}
71      set i 0
72      set ee $idxstr
73      foreach a $arglist {
74        if {[string is double $a]==0} {
75          set a "'[string map {' ''} $a]'"
76        }
77        set ee [string map [list "%$i%" $a] $ee]
78        incr i
79      }
80      set ee [string map [list "'" "''"] $ee]
81
82      set ret [list sql "SELECT 1, 'a', 'b', 'c', 'd', '$ee'"]
83      #puts "xFilter: $ret"
84      return $ret
85    }
86  }
87
88  return {}
89}
90
91register_tcl_module db
92do_execsql_test 1.0 {
93  CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
94} {}
95
96
97foreach {tn where res} {
98  1 "1"                              {{}}
99  2 "a=1"                            {{a = 1}}
100  3 "a=1 AND 4 = b"                  {{a = 1 AND b = 4}}
101  4 "c>'hello'"                      {{c > 'hello'}}
102  5 "c<='hel''lo'"                   {{c <= 'hel''lo'}}
103  6 "(a, b) = (SELECT 9, 10)"        {{a = 9 AND b = 10}}
104  7 "(+a, b) = (SELECT 'a', 'b')"    {{b = 'b'}}
105  8 "(a, +b) = (SELECT 'a', 'b')"    {{a = 'a'}}
106  11 "(+a, b) IN (SELECT 'a', 'b')"  {{b = 'b'}}
107  12 "(a, +b) IN (SELECT 'a', 'b')"  {{a = 'a'}}
108
109  13 "(a, b) < ('d', 'e')"           {{a <= 'd'}}
110  14 "(a, b) < ('a', 'c')"           {{a <= 'a'}}
111  15 "(a, b) <= ('a', 'b')"          {{a <= 'a'}}
112  16 "(a, b) < ('a', 'b')"           {}
113} {
114  do_execsql_test 1.$tn "SELECT expr FROM x1 WHERE $where" $res
115}
116
117finish_test
118