1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
# 2022 June 2
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix windowD
do_execsql_test 1.0 {
CREATE TABLE t0(c0 TEXT);
CREATE VIEW v0(c0, c1)
AS SELECT CUME_DIST() OVER (PARTITION BY t0.c0), TRUE FROM t0;
INSERT INTO t0 VALUES ('x');
}
do_execsql_test 1.1 {
SELECT ('500') IS (v0.c1) FROM v0;
} {
0
}
do_execsql_test 1.2 {
SELECT (('500') IS (v0.c1)) FROM v0, t0;
} {
0
}
do_execsql_test 1.2 {
SELECT (('500') IS (v0.c1)) IS FALSE FROM v0;
} {
1
}
do_execsql_test 1.3 {
SELECT * FROM v0;
} {
1.0 1
}
do_execsql_test 1.4 {
SELECT * FROM v0 WHERE ('500' IS v0.c1) IS FALSE;
} {
1.0 1
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 2.0 {
CREATE TABLE t1(x);
INSERT INTO t1 VALUES('value');
CREATE VIEW v1(a, b, c, d) AS SELECT 1, 2, TRUE, FALSE FROM t1;
}
do_execsql_test 2.1 {
SELECT 500 IS a, 500 IS b, 500 IS c, 500 IS d FROM v1
} {0 0 0 0}
do_execsql_test 2.2 {
SELECT * FROM v1 WHERE 500 IS c;
} {}
do_execsql_test 2.3 {
SELECT * FROM v1 WHERE 500 IS d;
} {}
do_execsql_test 2.4 {
CREATE VIEW v2 AS SELECT max(x) OVER () AS a, TRUE AS c FROM t1;
}
do_execsql_test 2.5 {
SELECT 500 IS c FROM v2;
} 0
do_execsql_test 2.6 {
SELECT * FROM v2 WHERE 500 IS c;
} {}
finish_test
|
