xref: /sqlite-3.40.0/test/windowD.test (revision 410fac35) 
1# 2022 June 2
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
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15set testprefix windowD
16
17
18do_execsql_test 1.0 {
19  CREATE TABLE t0(c0 TEXT);
20  CREATE VIEW v0(c0, c1)
21    AS SELECT CUME_DIST() OVER (PARTITION BY t0.c0), TRUE FROM t0;
22  INSERT INTO t0 VALUES ('x');
23}
24
25do_execsql_test 1.1 {
26  SELECT ('500') IS (v0.c1) FROM v0;
27} {
28  0
29}
30
31do_execsql_test 1.2 {
32  SELECT (('500') IS (v0.c1)) FROM v0, t0;
33} {
34  0
35}
36
37do_execsql_test 1.2 {
38  SELECT (('500') IS (v0.c1)) IS FALSE FROM v0;
39} {
40  1
41}
42
43do_execsql_test 1.3 {
44  SELECT * FROM v0;
45} {
46  1.0 1
47}
48
49do_execsql_test 1.4 {
50  SELECT * FROM v0 WHERE ('500' IS v0.c1) IS FALSE;
51} {
52  1.0 1
53}
54
55#-------------------------------------------------------------------------
56
57reset_db
58do_execsql_test 2.0 {
59  CREATE TABLE t1(x);
60  INSERT INTO t1 VALUES('value');
61  CREATE VIEW v1(a, b, c, d) AS SELECT 1, 2, TRUE, FALSE FROM t1;
62}
63
64do_execsql_test 2.1 {
65  SELECT 500 IS a, 500 IS b, 500 IS c, 500 IS d FROM v1
66} {0 0 0 0}
67
68do_execsql_test 2.2 {
69  SELECT * FROM v1 WHERE 500 IS c;
70} {}
71
72do_execsql_test 2.3 {
73  SELECT * FROM v1 WHERE 500 IS d;
74} {}
75
76do_execsql_test 2.4 {
77  CREATE VIEW v2 AS SELECT max(x) OVER () AS a, TRUE AS c FROM t1;
78}
79
80do_execsql_test 2.5 {
81  SELECT 500 IS c FROM v2;
82} 0
83
84do_execsql_test 2.6 {
85  SELECT * FROM v2 WHERE 500 IS c;
86} {}
87
88
89
90
91
92
93finish_test
94
95