xref: /sqlite-3.40.0/test/windowpushd.test (revision 903fdd48) 
1# 2021 February 23
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 testing the push-down optimization when
13# WHERE constraints are pushed down into a sub-query that uses
14# window functions.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19set testprefix windowpushd
20
21do_execsql_test 1.0 {
22  CREATE TABLE t1(id INTEGER PRIMARY KEY, grp_id);
23  CREATE INDEX i1 ON t1(grp_id);
24  CREATE VIEW lll AS SELECT
25    row_number() OVER (PARTITION BY grp_id),
26    grp_id, id
27  FROM t1
28}
29
30do_execsql_test 1.1 {
31  INSERT INTO t1 VALUES
32    (1, 2), (2, 3), (3, 3), (4, 1), (5, 1),
33    (6, 1), (7, 1), (8, 1), (9, 3), (10, 3),
34    (11, 2), (12, 3), (13, 3), (14, 2), (15, 1),
35    (16, 2), (17, 1), (18, 2), (19, 3), (20, 2)
36}
37
38do_execsql_test 1.2 {
39  SELECT * FROM lll
40} {
41  1 1 4 2 1 5 3 1 6 4 1 7 5 1 8 6 1 15 7 1 17
42  1 2 1 2 2 11 3 2 14 4 2 16 5 2 18 6 2 20
43  1 3 2 2 3 3 3 3 9 4 3 10 5 3 12 6 3 13 7 3 19
44}
45
46do_execsql_test 1.3 {
47  SELECT * FROM lll WHERE grp_id=2
48} {
49  1 2 1 2 2 11 3 2 14 4 2 16 5 2 18 6 2 20
50}
51
52do_eqp_test 1.4 {
53  SELECT * FROM lll WHERE grp_id=2
54} {SEARCH TABLE t1 USING COVERING INDEX i1 (grp_id=?)}
55
56#-------------------------------------------------------------------------
57reset_db
58do_execsql_test 2.0 {
59  CREATE TABLE t1(a, b, c, d);
60  INSERT INTO t1 VALUES('A', 'C', 1,  0.1);
61  INSERT INTO t1 VALUES('A', 'D', 2,  0.2);
62  INSERT INTO t1 VALUES('A', 'E', 3,  0.3);
63  INSERT INTO t1 VALUES('A', 'C', 4,  0.4);
64  INSERT INTO t1 VALUES('B', 'D', 5,  0.5);
65  INSERT INTO t1 VALUES('B', 'E', 6,  0.6);
66  INSERT INTO t1 VALUES('B', 'C', 7,  0.7);
67  INSERT INTO t1 VALUES('B', 'D', 8,  0.8);
68  INSERT INTO t1 VALUES('C', 'E', 9,  0.9);
69  INSERT INTO t1 VALUES('C', 'C', 10, 1.0);
70  INSERT INTO t1 VALUES('C', 'D', 11, 1.1);
71  INSERT INTO t1 VALUES('C', 'E', 12, 1.2);
72
73  CREATE INDEX i1 ON t1(a);
74  CREATE INDEX i2 ON t1(b);
75
76  CREATE VIEW v1 AS SELECT a, c, max(c) OVER (PARTITION BY a) FROM t1;
77
78  CREATE VIEW v2 AS SELECT a, c,
79      max(c) OVER (PARTITION BY a),
80      row_number() OVER ()
81  FROM t1;
82
83  CREATE VIEW v3 AS SELECT b, d,
84      max(d) OVER (PARTITION BY b),
85      row_number() OVER (PARTITION BY b)
86  FROM t1;
87}
88
89foreach tn {0 1} {
90  optimization_control db push-down $tn
91
92  do_execsql_test 2.$tn.1.1 {
93    SELECT * FROM v1;
94  } {
95    A 1 4   A 2 4   A 3 4   A 4 4
96    B 5 8   B 6 8   B 7 8   B 8 8
97    C 9 12  C 10 12 C 11 12 C 12 12
98  }
99
100  do_execsql_test 2.$tn.1.2 {
101    SELECT * FROM v1 WHERE a IN ('A', 'B');
102  } {
103    A 1 4   A 2 4   A 3 4   A 4 4
104    B 5 8   B 6 8   B 7 8   B 8 8
105  }
106
107  do_execsql_test 2.$tn.1.3 {
108    SELECT * FROM v1 WHERE a IS 'C'
109  } {
110    C 9 12  C 10 12 C 11 12 C 12 12
111  }
112
113  if {$tn==1} {
114    do_eqp_test 2.$tn.1.4 {
115      SELECT * FROM v1 WHERE a IN ('A', 'B');
116    } {USING INDEX i1 (a=?)}
117
118    do_eqp_test 2.$tn.1.5 {
119      SELECT * FROM v1 WHERE a = 'c' COLLATE nocase
120    } {USING INDEX i1}
121  }
122
123  do_execsql_test 2.$tn.2.1 {
124    SELECT * FROM v2;
125  } {
126    A 1 4 1    A 2 4 2     A 3 4 3      A 4 4 4
127    B 5 8 5    B 6 8 6     B 7 8 7      B 8 8 8
128    C 9 12 9   C 10 12 10  C 11 12 11   C 12 12 12
129  }
130
131  do_execsql_test 2.$tn.2.2 {
132    SELECT * FROM v2 WHERE a = 'C';
133  } {
134    C 9 12 9   C 10 12 10  C 11 12 11   C 12 12 12
135  }
136
137  do_execsql_test 2.$tn.3.1 { SELECT * FROM v3; } {
138    C 0.1 1.0 1 C 0.4 1.0 2 C 0.7 1.0 3 C 1.0 1.0 4
139    D 0.2 1.1 1 D 0.5 1.1 2 D 0.8 1.1 3 D 1.1 1.1 4
140    E 0.3 1.2 1 E 0.6 1.2 2 E 0.9 1.2 3 E 1.2 1.2 4
141  }
142
143  do_execsql_test 2.$tn.3.2 { SELECT * FROM v3 WHERE b<'E' } {
144    C 0.1 1.0 1 C 0.4 1.0 2 C 0.7 1.0 3 C 1.0 1.0 4
145    D 0.2 1.1 1 D 0.5 1.1 2 D 0.8 1.1 3 D 1.1 1.1 4
146  }
147
148  if {$tn==1} {
149    do_eqp_test 2.$tn.3.3 {
150      SELECT * FROM v3 WHERE b='E'
151    } {USING INDEX i2 (b=?)}
152  }
153
154}
155
156
157
158
159finish_test
160
161