Debian Codegraph @

test/rowvalueA.test

raw
1 # 2021 July 6
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.
12 #
13
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix rowvalueA
17
18 do_execsql_test 1.0 {
19 SELECT (1, 2) IN ( (3, 4), (5, 6), (1, 3) );
20 } {0}
21
22 do_execsql_test 1.1 {
23 SELECT (1, 2) IN ( (3, 4), (5, 6), (1, 2) );
24 } {1}
25
26 do_execsql_test 1.2 {
27 SELECT (1, 2) IN ( (3, 2) );
28 } {0}
29
30 do_execsql_test 1.3 {
31 SELECT (1, 2) IN ( (1, 2) );
32 } {1}
33
34 do_execsql_test 1.4 {
35 SELECT (1, 2) IN ( );
36 } {0}
37
38 do_execsql_test 1.5 {
39 SELECT (1, 2) NOT IN ( );
40 } {1}
41
42 for {set ii 0} {$ii < 2000} {incr ii} {
43 lappend L "($ii, $ii)"
44 }
45
46 do_execsql_test 1.6.1 "
47 SELECT (400,400) IN ( [join $L ,] )
48 " 1
49
50 do_execsql_test 1.6.2 "
51 SELECT (1500,1500) IN ( [join $L ,] )
52 " 1
53
54 do_execsql_test 1.6.2 "
55 SELECT (1500,1499) IN ( [join $L ,] )
56 " 0
57
58 #-------------------------------------------------------------------------
59
60 do_catchsql_test 2.0 {
61 SELECT (1, 2) IN ( (1, 2), (3, 4, 5), (5, 6) )
62 } {1 {IN(...) element has 3 terms - expected 2}}
63
64 do_catchsql_test 2.1 {
65 SELECT (1, 2) IN ( (1, 2), 4, (5, 6) )
66 } {1 {IN(...) element has 1 term - expected 2}}
67
68 do_catchsql_test 2.2 {
69 SELECT (1, 2, 3) IN ( (1, 2), (3, 4), (5, 6) )
70 } {1 {IN(...) element has 2 terms - expected 3}}
71
72 do_catchsql_test 2.3 {
73 SELECT 2 IN ( (1, 2), (3, 4), (5, 6) )
74 } {1 {row value misused}}
75
76 #-------------------------------------------------------------------------
77 # Test the fix for forum post https://sqlite.org/forum/forumpost/6ceca07fc3
78 #
79 do_execsql_test 3.0 {
80 CREATE TABLE x2 (x, y);
81 INSERT INTO x2 VALUES (1234, 'abc');
82
83 CREATE TABLE x1 (a, b PRIMARY KEY COLLATE NOCASE) WITHOUT ROWID;
84 INSERT INTO x1 VALUES (1234, 'ABCD');
85 }
86
87 do_execsql_test 3.1 {
88 SELECT * FROM x2 CROSS JOIN x1 WHERE (1234, x2.y) > (x1.a, x1.b);
89 } {1234 abc 1234 ABCD}
90
91 do_execsql_test 3.2 {
92 CREATE INDEX x1a ON x1(a);
93 }
94
95 do_execsql_test 3.3 {
96 SELECT * FROM x2 CROSS JOIN x1 WHERE (1234, x2.y) > (x1.a, x1.b);
97 } {1234 abc 1234 ABCD}
98
99 #-------------------------------------------------------------------------
100 # Test the fix for forum post https://sqlite.org/forum/forumpost/7a308e933d
101 #
102 do_execsql_test 4.0 {
103 CREATE TABLE t1 (a PRIMARY KEY, b COLLATE NOCASE) WITHOUT ROWID;
104 INSERT INTO t1 VALUES ('BBB', 'a');
105 }
106 do_execsql_test 4.1 {
107 SELECT * FROM t1 WHERE (t1.a, t1.b) <= ('BBB', 'CCC');
108 } {BBB a}
109
110 do_execsql_test 4.2 {
111 DROP TABLE t1;
112 CREATE TABLE t0 (c0);
113 INSERT INTO t0 VALUES ('True');
114
115 CREATE TABLE t1 (c0 COLLATE NOCASE, c1 PRIMARY KEY) WITHOUT ROWID;
116 INSERT INTO t1 VALUES ('a', 1);
117 INSERT INTO t1 VALUES ('a', 'True');
118 }
119
120 do_execsql_test 4.3 {
121 SELECT * FROM t0, t1 WHERE (t1.c1, t1.c0) <= (t0.c0, t0.c0);
122 } {True a 1 True a True}
123
124 #-------------------------------------------------------------------------
125 # Bug report https://sqlite.org/bugs/info/2026-05-17T11:59:19Z
126 #
127 do_execsql_test 5.1 {
128 DROP TABLE t1;
129 CREATE TABLE t1(a TEXT, b TEXT);
130 CREATE INDEX t1_ab ON t1(a COLLATE NOCASE, b COLLATE NOCASE);
131 INSERT INTO t1 VALUES('a',null),('b', 'ABCD');
132 }
133 db null NULL
134 do_execsql_test 5.2 {
135 SELECT * FROM t1
136 WHERE ('B' COLLATE nocase,'abc')>(a,b)
137 ORDER BY +a;
138 } {a NULL b ABCD}
139 do_execsql_test 5.3 {
140 SELECT * FROM t1 NOT INDEXED
141 WHERE ('B' COLLATE nocase,'abc')>(a,b)
142 ORDER BY +a;
143 } {a NULL b ABCD}
144
145 finish_test