Debian Codegraph @

ext/fts5/test/fts5leftjoin.test

raw
1 # 2014 June 17
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 script is testing the FTS5 module.
13 #
14
15 source [file join [file dirname [info script]] fts5_common.tcl]
16 set testprefix fts5leftjoin
17
18 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
19 ifcapable !fts5 {
20 finish_test
21 return
22 }
23
24 do_execsql_test 1.0 {
25 CREATE VIRTUAL TABLE vt USING fts5(x);
26 INSERT INTO vt VALUES('abc');
27 INSERT INTO vt VALUES('xyz');
28
29 CREATE TABLE t1(a INTEGER PRIMARY KEY);
30 INSERT INTO t1 VALUES(1), (2);
31 }
32
33 do_execsql_test 1.1 {
34 SELECT * FROM t1 LEFT JOIN (
35 SELECT rowid AS rrr, * FROM vt WHERE vt MATCH 'abc'
36 ) ON t1.a = rrr
37 } {1 1 abc 2 {} {}}
38
39 do_execsql_test 1.2 {
40 SELECT * FROM t1 LEFT JOIN vt ON (vt MATCH 'abc')
41 } {1 abc 2 abc}
42
43
44 do_execsql_test 1.3 {
45 DELETE FROM t1;
46 INSERT INTO t1 VALUES(14);
47 }
48
49 do_execsql_test 1.4 {
50 SELECT * FROM vt LEFT JOIN t1 ON vt.rowid = 1;
51 } {
52 abc 14
53 xyz {}
54 }
55
56 #-------------------------------------------------------------------------
57 reset_db
58 do_execsql_test 2.0 {
59 CREATE VIRTUAL TABLE t0 USING fts5(a,b);
60 INSERT INTO t0(a,b)VALUES(1,0);
61 CREATE TABLE t1(x);
62 }
63
64 do_execsql_test 2.1 {
65 SELECT * FROM t0 LEFT JOIN t1;
66 } {1 0 {}}
67
68 breakpoint
69 do_catchsql_test 2.2 {
70 SELECT * FROM t0 LEFT JOIN t1 ON t0.b MATCH '1';
71 } {1 {no query solution}}
72
73 do_execsql_test 2.3 {
74 SELECT * FROM t0 LEFT JOIN t1 ON +b MATCH '1';
75 } {1 0 {}}
76
77 #-------------------------------------------------------------------------
78 reset_db
79
80 do_execsql_test 3.0 {
81 CREATE VIRTUAL TABLE t0 USING fts5(c0, c1);
82 INSERT INTO t0(c0,c1) VALUES (1,0);
83 }
84
85 do_catchsql_test 3.1 {
86 SELECT * FROM t0
87 LEFT JOIN ( SELECT 0 AS col_0 )
88 ON ((((t0.c1 MATCH '1')AND(CASE WHEN t0.c0 THEN CAST(t0.c1 AS INTEGER) ELSE 1 END))));
89 } {1 {no query solution}}
90
91
92 finish_test