| 1 |
# 2018 May 8 |
| 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 window1 |
| 17 |
|
| 18 |
ifcapable !windowfunc { |
| 19 |
finish_test |
| 20 |
return |
| 21 |
} |
| 22 |
|
| 23 |
do_execsql_test 1.0 { |
| 24 |
CREATE TABLE t1(a, b, c, d); |
| 25 |
INSERT INTO t1 VALUES(1, 2, 3, 4); |
| 26 |
INSERT INTO t1 VALUES(5, 6, 7, 8); |
| 27 |
INSERT INTO t1 VALUES(9, 10, 11, 12); |
| 28 |
} |
| 29 |
|
| 30 |
do_execsql_test 1.1 { |
| 31 |
SELECT sum(b) OVER () FROM t1 |
| 32 |
} {18 18 18} |
| 33 |
|
| 34 |
do_execsql_test 1.2 { |
| 35 |
SELECT a, sum(b) OVER () FROM t1 |
| 36 |
} {1 18 5 18 9 18} |
| 37 |
|
| 38 |
do_execsql_test 1.3 { |
| 39 |
SELECT a, 4 + sum(b) OVER () FROM t1 |
| 40 |
} {1 22 5 22 9 22} |
| 41 |
|
| 42 |
do_execsql_test 1.4 { |
| 43 |
SELECT a + 4 + sum(b) OVER () FROM t1 |
| 44 |
} {23 27 31} |
| 45 |
|
| 46 |
do_execsql_test 1.5 { |
| 47 |
SELECT a, sum(b) OVER (PARTITION BY c) FROM t1 |
| 48 |
} {1 2 5 6 9 10} |
| 49 |
|
| 50 |
foreach {tn sql} { |
| 51 |
1 "SELECT sum(b) OVER () FROM t1" |
| 52 |
2 "SELECT sum(b) OVER (PARTITION BY c) FROM t1" |
| 53 |
3 "SELECT sum(b) OVER (ORDER BY c) FROM t1" |
| 54 |
4 "SELECT sum(b) OVER (PARTITION BY d ORDER BY c) FROM t1" |
| 55 |
5 "SELECT sum(b) FILTER (WHERE a>0) OVER (PARTITION BY d ORDER BY c) FROM t1" |
| 56 |
6 "SELECT sum(b) OVER (ORDER BY c RANGE UNBOUNDED PRECEDING) FROM t1" |
| 57 |
7 "SELECT sum(b) OVER (ORDER BY c ROWS 45 PRECEDING) FROM t1" |
| 58 |
8 "SELECT sum(b) OVER (ORDER BY c RANGE CURRENT ROW) FROM t1" |
| 59 |
9 "SELECT sum(b) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING |
| 60 |
AND CURRENT ROW) FROM t1" |
| 61 |
10 "SELECT sum(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING |
| 62 |
AND UNBOUNDED FOLLOWING) FROM t1" |
| 63 |
} { |
| 64 |
do_test 2.$tn { lindex [catchsql $sql] 0 } 0 |
| 65 |
} |
| 66 |
|
| 67 |
foreach {tn sql} { |
| 68 |
1 "SELECT * FROM t1 WHERE sum(b) OVER ()" |
| 69 |
2 "SELECT * FROM t1 GROUP BY sum(b) OVER ()" |
| 70 |
3 "SELECT * FROM t1 GROUP BY a HAVING sum(b) OVER ()" |
| 71 |
} { |
| 72 |
do_catchsql_test 3.$tn $sql {1 {misuse of window function sum()}} |
| 73 |
} |
| 74 |
|
| 75 |
do_execsql_test 4.0 { |
| 76 |
CREATE TABLE t2(a, b, c); |
| 77 |
INSERT INTO t2 VALUES(0, 0, 0); |
| 78 |
INSERT INTO t2 VALUES(1, 1, 1); |
| 79 |
INSERT INTO t2 VALUES(2, 0, 2); |
| 80 |
INSERT INTO t2 VALUES(3, 1, 0); |
| 81 |
INSERT INTO t2 VALUES(4, 0, 1); |
| 82 |
INSERT INTO t2 VALUES(5, 1, 2); |
| 83 |
INSERT INTO t2 VALUES(6, 0, 0); |
| 84 |
} |
| 85 |
|
| 86 |
do_execsql_test 4.1 { |
| 87 |
SELECT a, sum(a) OVER (PARTITION BY b) FROM t2; |
| 88 |
} { |
| 89 |
0 12 2 12 4 12 6 12 1 9 3 9 5 9 |
| 90 |
} |
| 91 |
|
| 92 |
do_execsql_test 4.2 { |
| 93 |
SELECT a, sum(a) OVER (PARTITION BY b) FROM t2 ORDER BY a; |
| 94 |
} { |
| 95 |
0 12 1 9 2 12 3 9 4 12 5 9 6 12 |
| 96 |
} |
| 97 |
|
| 98 |
do_execsql_test 4.3 { |
| 99 |
SELECT a, sum(a) OVER () FROM t2 ORDER BY a; |
| 100 |
} { |
| 101 |
0 21 1 21 2 21 3 21 4 21 5 21 6 21 |
| 102 |
} |
| 103 |
|
| 104 |
do_execsql_test 4.4 { |
| 105 |
SELECT a, sum(a) OVER (ORDER BY a) FROM t2; |
| 106 |
} { |
| 107 |
0 0 1 1 2 3 3 6 4 10 5 15 6 21 |
| 108 |
} |
| 109 |
|
| 110 |
do_execsql_test 4.5 { |
| 111 |
SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a) FROM t2 ORDER BY a |
| 112 |
} { |
| 113 |
0 0 1 1 2 2 3 4 4 6 5 9 6 12 |
| 114 |
} |
| 115 |
|
| 116 |
do_execsql_test 4.6 { |
| 117 |
SELECT a, sum(a) OVER (PARTITION BY c ORDER BY a) FROM t2 ORDER BY a |
| 118 |
} { |
| 119 |
0 0 1 1 2 2 3 3 4 5 5 7 6 9 |
| 120 |
} |
| 121 |
|
| 122 |
do_execsql_test 4.7 { |
| 123 |
SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a DESC) FROM t2 ORDER BY a |
| 124 |
} { |
| 125 |
0 12 1 9 2 12 3 8 4 10 5 5 6 6 |
| 126 |
} |
| 127 |
|
| 128 |
do_execsql_test 4.8 { |
| 129 |
SELECT a, |
| 130 |
sum(a) OVER (PARTITION BY b ORDER BY a DESC), |
| 131 |
sum(a) OVER (PARTITION BY c ORDER BY a) |
| 132 |
FROM t2 ORDER BY a |
| 133 |
} { |
| 134 |
0 12 0 |
| 135 |
1 9 1 |
| 136 |
2 12 2 |
| 137 |
3 8 3 |
| 138 |
4 10 5 |
| 139 |
5 5 7 |
| 140 |
6 6 9 |
| 141 |
} |
| 142 |
|
| 143 |
do_execsql_test 4.9 { |
| 144 |
SELECT a, |
| 145 |
sum(a) OVER (ORDER BY a), |
| 146 |
avg(a) OVER (ORDER BY a) |
| 147 |
FROM t2 ORDER BY a |
| 148 |
} { |
| 149 |
0 0 0.0 |
| 150 |
1 1 0.5 |
| 151 |
2 3 1.0 |
| 152 |
3 6 1.5 |
| 153 |
4 10 2.0 |
| 154 |
5 15 2.5 |
| 155 |
6 21 3.0 |
| 156 |
} |
| 157 |
|
| 158 |
do_execsql_test 4.10.1 { |
| 159 |
SELECT a, |
| 160 |
count() OVER (ORDER BY a DESC), |
| 161 |
string_agg(a, '.') OVER (ORDER BY a DESC) |
| 162 |
FROM t2 ORDER BY a DESC |
| 163 |
} { |
| 164 |
6 1 6 |
| 165 |
5 2 6.5 |
| 166 |
4 3 6.5.4 |
| 167 |
3 4 6.5.4.3 |
| 168 |
2 5 6.5.4.3.2 |
| 169 |
1 6 6.5.4.3.2.1 |
| 170 |
0 7 6.5.4.3.2.1.0 |
| 171 |
} |
| 172 |
|
| 173 |
do_execsql_test 4.10.2 { |
| 174 |
SELECT a, |
| 175 |
count(*) OVER (ORDER BY a DESC), |
| 176 |
group_concat(a, '.') OVER (ORDER BY a DESC) |
| 177 |
FROM t2 ORDER BY a DESC |
| 178 |
} { |
| 179 |
6 1 6 |
| 180 |
5 2 6.5 |
| 181 |
4 3 6.5.4 |
| 182 |
3 4 6.5.4.3 |
| 183 |
2 5 6.5.4.3.2 |
| 184 |
1 6 6.5.4.3.2.1 |
| 185 |
0 7 6.5.4.3.2.1.0 |
| 186 |
} |
| 187 |
|
| 188 |
do_catchsql_test 5.1 { |
| 189 |
SELECT ntile(0) OVER (ORDER BY a) FROM t2; |
| 190 |
} {1 {argument of ntile must be a positive integer}} |
| 191 |
do_catchsql_test 5.2 { |
| 192 |
SELECT ntile(-1) OVER (ORDER BY a) FROM t2; |
| 193 |
} {1 {argument of ntile must be a positive integer}} |
| 194 |
do_catchsql_test 5.3 { |
| 195 |
SELECT ntile('zbc') OVER (ORDER BY a) FROM t2; |
| 196 |
} {1 {argument of ntile must be a positive integer}} |
| 197 |
do_execsql_test 5.4 { |
| 198 |
CREATE TABLE t4(a, b); |
| 199 |
SELECT ntile(1) OVER (ORDER BY a) FROM t4; |
| 200 |
} {} |
| 201 |
|
| 202 |
#------------------------------------------------------------------------- |
| 203 |
reset_db |
| 204 |
do_execsql_test 6.1 { |
| 205 |
CREATE TABLE t1(x); |
| 206 |
INSERT INTO t1 VALUES(7), (6), (5), (4), (3), (2), (1); |
| 207 |
|
| 208 |
CREATE TABLE t2(x); |
| 209 |
INSERT INTO t2 VALUES('b'), ('a'); |
| 210 |
|
| 211 |
SELECT x, count(*) OVER (ORDER BY x) FROM t1; |
| 212 |
} {1 1 2 2 3 3 4 4 5 5 6 6 7 7} |
| 213 |
|
| 214 |
do_execsql_test 6.2 { |
| 215 |
SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1) |
| 216 |
ORDER BY 1, 2; |
| 217 |
} { |
| 218 |
a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7 |
| 219 |
b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7 |
| 220 |
} |
| 221 |
|
| 222 |
do_catchsql_test 6.3 { |
| 223 |
SELECT x, lag(x) FILTER (WHERE (x%2)=0) OVER w FROM t1 |
| 224 |
WINDOW w AS (ORDER BY x) |
| 225 |
} {1 {FILTER clause may only be used with aggregate window functions}} |
| 226 |
|
| 227 |
#------------------------------------------------------------------------- |
| 228 |
# Attempt to use a window function as an aggregate. And other errors. |
| 229 |
# |
| 230 |
reset_db |
| 231 |
do_execsql_test 7.0 { |
| 232 |
CREATE TABLE t1(x, y); |
| 233 |
INSERT INTO t1 VALUES(1, 2); |
| 234 |
INSERT INTO t1 VALUES(3, 4); |
| 235 |
INSERT INTO t1 VALUES(5, 6); |
| 236 |
INSERT INTO t1 VALUES(7, 8); |
| 237 |
INSERT INTO t1 VALUES(9, 10); |
| 238 |
} |
| 239 |
|
| 240 |
do_catchsql_test 7.1.1 { |
| 241 |
SELECT nth_value(x, 1) FROM t1; |
| 242 |
} {1 {misuse of window function nth_value()}} |
| 243 |
do_catchsql_test 7.1.2 { |
| 244 |
SELECT * FROM t1 WHERE nth_value(x, 1) OVER (ORDER BY y); |
| 245 |
} {1 {misuse of window function nth_value()}} |
| 246 |
do_catchsql_test 7.1.3 { |
| 247 |
SELECT count(*) FROM t1 GROUP BY y HAVING nth_value(x, 1) OVER (ORDER BY y); |
| 248 |
} {1 {misuse of window function nth_value()}} |
| 249 |
do_catchsql_test 7.1.4 { |
| 250 |
SELECT count(*) FROM t1 GROUP BY nth_value(x, 1) OVER (ORDER BY y); |
| 251 |
} {1 {misuse of window function nth_value()}} |
| 252 |
do_catchsql_test 7.1.5 { |
| 253 |
SELECT count(*) FROM t1 LIMIT nth_value(x, 1) OVER (); |
| 254 |
} {1 {no such column: x}} |
| 255 |
do_catchsql_test 7.1.6 { |
| 256 |
SELECT trim(x) OVER (ORDER BY y) FROM t1; |
| 257 |
} {1 {trim() may not be used as a window function}} |
| 258 |
do_catchsql_test 7.1.7 { |
| 259 |
SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y); |
| 260 |
} {1 {no such window: abc}} |
| 261 |
do_catchsql_test 7.1.8 { |
| 262 |
SELECT row_number(x) OVER () FROM t1 |
| 263 |
} {1 {wrong number of arguments to function row_number()}} |
| 264 |
|
| 265 |
do_execsql_test 7.2 { |
| 266 |
SELECT |
| 267 |
lead(y) OVER win, |
| 268 |
lead(y, 2) OVER win, |
| 269 |
lead(y, 3, 'default') OVER win |
| 270 |
FROM t1 |
| 271 |
WINDOW win AS (ORDER BY x) |
| 272 |
} { |
| 273 |
4 6 8 6 8 10 8 10 default 10 {} default {} {} default |
| 274 |
} |
| 275 |
|
| 276 |
do_execsql_test 7.3 { |
| 277 |
SELECT row_number() OVER (ORDER BY x) FROM t1 |
| 278 |
} {1 2 3 4 5} |
| 279 |
|
| 280 |
do_execsql_test 7.4 { |
| 281 |
SELECT |
| 282 |
row_number() OVER win, |
| 283 |
lead(x) OVER win |
| 284 |
FROM t1 |
| 285 |
WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
| 286 |
} {1 3 2 5 3 7 4 9 5 {}} |
| 287 |
|
| 288 |
#------------------------------------------------------------------------- |
| 289 |
# Attempt to use a window function in a view. |
| 290 |
# |
| 291 |
do_execsql_test 8.0 { |
| 292 |
CREATE TABLE t3(a, b, c); |
| 293 |
|
| 294 |
WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 ) |
| 295 |
INSERT INTO t3 SELECT i, i, i FROM s; |
| 296 |
|
| 297 |
CREATE VIEW v1 AS SELECT |
| 298 |
sum(b) OVER (ORDER BY c), |
| 299 |
min(b) OVER (ORDER BY c), |
| 300 |
max(b) OVER (ORDER BY c) |
| 301 |
FROM t3; |
| 302 |
|
| 303 |
CREATE VIEW v2 AS SELECT |
| 304 |
sum(b) OVER win, |
| 305 |
min(b) OVER win, |
| 306 |
max(b) OVER win |
| 307 |
FROM t3 |
| 308 |
WINDOW win AS (ORDER BY c); |
| 309 |
} |
| 310 |
|
| 311 |
do_execsql_test 8.1.1 { |
| 312 |
SELECT * FROM v1 |
| 313 |
} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} |
| 314 |
do_execsql_test 8.1.2 { |
| 315 |
SELECT * FROM v2 |
| 316 |
} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} |
| 317 |
|
| 318 |
db close |
| 319 |
sqlite3 db test.db |
| 320 |
do_execsql_test 8.2.1 { |
| 321 |
SELECT * FROM v1 |
| 322 |
} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} |
| 323 |
do_execsql_test 8.2.2 { |
| 324 |
SELECT * FROM v2 |
| 325 |
} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} |
| 326 |
|
| 327 |
#------------------------------------------------------------------------- |
| 328 |
# Attempt to use a window function in a trigger. |
| 329 |
# |
| 330 |
do_execsql_test 9.0 { |
| 331 |
CREATE TABLE t4(x, y); |
| 332 |
INSERT INTO t4 VALUES(1, 'g'); |
| 333 |
INSERT INTO t4 VALUES(2, 'i'); |
| 334 |
INSERT INTO t4 VALUES(3, 'l'); |
| 335 |
INSERT INTO t4 VALUES(4, 'g'); |
| 336 |
INSERT INTO t4 VALUES(5, 'a'); |
| 337 |
|
| 338 |
CREATE TABLE t5(x, y, m); |
| 339 |
CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN |
| 340 |
DELETE FROM t5; |
| 341 |
INSERT INTO t5 |
| 342 |
SELECT x, y, max(y) OVER xyz FROM t4 |
| 343 |
WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x); |
| 344 |
END; |
| 345 |
} |
| 346 |
|
| 347 |
do_execsql_test 9.1.1 { |
| 348 |
SELECT x, y, max(y) OVER xyz FROM t4 |
| 349 |
WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1 |
| 350 |
} {1 g g 2 i i 3 l l 4 g i 5 a l} |
| 351 |
|
| 352 |
do_execsql_test 9.1.2 { |
| 353 |
INSERT INTO t4 VALUES(6, 'm'); |
| 354 |
SELECT x, y, max(y) OVER xyz FROM t4 |
| 355 |
WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1 |
| 356 |
} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} |
| 357 |
|
| 358 |
do_execsql_test 9.1.3 { |
| 359 |
SELECT * FROM t5 ORDER BY 1 |
| 360 |
} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} |
| 361 |
|
| 362 |
do_execsql_test 9.2 { |
| 363 |
WITH aaa(x, y, z) AS ( |
| 364 |
SELECT x, y, max(y) OVER xyz FROM t4 |
| 365 |
WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) |
| 366 |
) |
| 367 |
SELECT * FROM aaa ORDER BY 1; |
| 368 |
} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} |
| 369 |
|
| 370 |
do_execsql_test 9.3 { |
| 371 |
WITH aaa(x, y, z) AS ( |
| 372 |
SELECT x, y, max(y) OVER xyz FROM t4 |
| 373 |
WINDOW xyz AS (ORDER BY x) |
| 374 |
) |
| 375 |
SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1; |
| 376 |
} {1 g g g 2 i i g 3 l l g 4 g l g 5 a l g 6 m m g} |
| 377 |
|
| 378 |
do_catchsql_test 9.4 { |
| 379 |
-- 2021-04-17 dbsqlfuzz d9cf66100064952b66951845dfab41de1c124611 |
| 380 |
DROP TABLE IF EXISTS t1; |
| 381 |
CREATE TABLE t1(a,b,c,d); |
| 382 |
DROP TABLE IF EXISTS t2; |
| 383 |
CREATE TABLE t2(x,y); |
| 384 |
CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN |
| 385 |
INSERT INTO t2(x,y) |
| 386 |
SELECT a, max(d) OVER w1 FROM t1 |
| 387 |
WINDOW w1 AS (PARTITION BY EXISTS(SELECT 1 FROM t1 WHERE c=?1) ); |
| 388 |
END; |
| 389 |
} {1 {trigger cannot use variables}} |
| 390 |
|
| 391 |
do_catchsql_test 9.4.2 { |
| 392 |
CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN |
| 393 |
INSERT INTO t1(a,b) |
| 394 |
SELECT a, max(d) OVER w1 FROM t1 |
| 395 |
WINDOW w1 AS ( |
| 396 |
ORDER BY a ROWS BETWEEN ? PRECEDING AND UNBOUNDED FOLLOWING |
| 397 |
); |
| 398 |
END; |
| 399 |
} {1 {trigger cannot use variables}} |
| 400 |
do_catchsql_test 9.4.3 { |
| 401 |
CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN |
| 402 |
INSERT INTO t1(a,b) |
| 403 |
SELECT a, max(d) OVER w1 FROM t1 |
| 404 |
WINDOW w1 AS ( |
| 405 |
ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND ? FOLLOWING |
| 406 |
); |
| 407 |
END; |
| 408 |
} {1 {trigger cannot use variables}} |
| 409 |
|
| 410 |
#------------------------------------------------------------------------- |
| 411 |
# |
| 412 |
do_execsql_test 10.0 { |
| 413 |
CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total); |
| 414 |
INSERT INTO sales VALUES |
| 415 |
('Alice', 'North', 34), |
| 416 |
('Frank', 'South', 22), |
| 417 |
('Charles', 'North', 45), |
| 418 |
('Darrell', 'South', 8), |
| 419 |
('Grant', 'South', 23), |
| 420 |
('Brad' , 'North', 22), |
| 421 |
('Elizabeth', 'South', 99), |
| 422 |
('Horace', 'East', 1); |
| 423 |
} |
| 424 |
|
| 425 |
# Best two salespeople from each region |
| 426 |
# |
| 427 |
do_execsql_test 10.1 { |
| 428 |
SELECT emp, region, total FROM ( |
| 429 |
SELECT |
| 430 |
emp, region, total, |
| 431 |
row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank |
| 432 |
FROM sales |
| 433 |
) WHERE rank<=2 ORDER BY region, total DESC |
| 434 |
} { |
| 435 |
Horace East 1 |
| 436 |
Charles North 45 |
| 437 |
Alice North 34 |
| 438 |
Elizabeth South 99 |
| 439 |
Grant South 23 |
| 440 |
} |
| 441 |
|
| 442 |
do_execsql_test 10.2 { |
| 443 |
SELECT emp, region, sum(total) OVER win FROM sales |
| 444 |
WINDOW win AS (PARTITION BY region ORDER BY total) |
| 445 |
} { |
| 446 |
Horace East 1 |
| 447 |
Brad North 22 |
| 448 |
Alice North 56 |
| 449 |
Charles North 101 |
| 450 |
Darrell South 8 |
| 451 |
Frank South 30 |
| 452 |
Grant South 53 |
| 453 |
Elizabeth South 152 |
| 454 |
} |
| 455 |
|
| 456 |
do_execsql_test 10.3 { |
| 457 |
SELECT emp, region, sum(total) OVER win FROM sales |
| 458 |
WINDOW win AS (PARTITION BY region ORDER BY total) |
| 459 |
LIMIT 5 |
| 460 |
} { |
| 461 |
Horace East 1 |
| 462 |
Brad North 22 |
| 463 |
Alice North 56 |
| 464 |
Charles North 101 |
| 465 |
Darrell South 8 |
| 466 |
} |
| 467 |
|
| 468 |
do_execsql_test 10.4 { |
| 469 |
SELECT emp, region, sum(total) OVER win FROM sales |
| 470 |
WINDOW win AS (PARTITION BY region ORDER BY total) |
| 471 |
LIMIT 5 OFFSET 2 |
| 472 |
} { |
| 473 |
Alice North 56 |
| 474 |
Charles North 101 |
| 475 |
Darrell South 8 |
| 476 |
Frank South 30 |
| 477 |
Grant South 53 |
| 478 |
} |
| 479 |
|
| 480 |
do_execsql_test 10.5 { |
| 481 |
SELECT emp, region, sum(total) OVER win FROM sales |
| 482 |
WINDOW win AS ( |
| 483 |
PARTITION BY region ORDER BY total |
| 484 |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 485 |
) |
| 486 |
} { |
| 487 |
Horace East 1 |
| 488 |
Brad North 101 |
| 489 |
Alice North 79 |
| 490 |
Charles North 45 |
| 491 |
Darrell South 152 |
| 492 |
Frank South 144 |
| 493 |
Grant South 122 |
| 494 |
Elizabeth South 99 |
| 495 |
} |
| 496 |
|
| 497 |
do_execsql_test 10.6 { |
| 498 |
SELECT emp, region, sum(total) OVER win FROM sales |
| 499 |
WINDOW win AS ( |
| 500 |
PARTITION BY region ORDER BY total |
| 501 |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 502 |
) LIMIT 5 OFFSET 2 |
| 503 |
} { |
| 504 |
Alice North 79 |
| 505 |
Charles North 45 |
| 506 |
Darrell South 152 |
| 507 |
Frank South 144 |
| 508 |
Grant South 122 |
| 509 |
} |
| 510 |
|
| 511 |
do_execsql_test 10.7 { |
| 512 |
SELECT emp, region, ( |
| 513 |
SELECT sum(total) OVER ( |
| 514 |
ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| 515 |
) || outer.emp FROM sales |
| 516 |
) FROM sales AS outer; |
| 517 |
} { |
| 518 |
Alice North 254Alice |
| 519 |
Frank South 254Frank |
| 520 |
Charles North 254Charles |
| 521 |
Darrell South 254Darrell |
| 522 |
Grant South 254Grant |
| 523 |
Brad North 254Brad |
| 524 |
Elizabeth South 254Elizabeth |
| 525 |
Horace East 254Horace |
| 526 |
} |
| 527 |
|
| 528 |
do_execsql_test 10.8 { |
| 529 |
SELECT emp, region, ( |
| 530 |
SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER ( |
| 531 |
ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| 532 |
) FROM sales |
| 533 |
) FROM sales AS outer; |
| 534 |
} { |
| 535 |
Alice North 220 |
| 536 |
Frank South 232 |
| 537 |
Charles North 209 |
| 538 |
Darrell South 246 |
| 539 |
Grant South 231 |
| 540 |
Brad North 232 |
| 541 |
Elizabeth South 155 |
| 542 |
Horace East 253 |
| 543 |
} |
| 544 |
|
| 545 |
#------------------------------------------------------------------------- |
| 546 |
# Check that it is not possible to use a window function in a CREATE INDEX |
| 547 |
# statement. |
| 548 |
# |
| 549 |
do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); } |
| 550 |
|
| 551 |
do_catchsql_test 11.1 { |
| 552 |
CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER (); |
| 553 |
} {1 {misuse of window function sum()}} |
| 554 |
do_catchsql_test 11.2 { |
| 555 |
CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER (); |
| 556 |
} {1 {misuse of window function lead()}} |
| 557 |
|
| 558 |
do_catchsql_test 11.3 { |
| 559 |
CREATE INDEX t6i ON t6(sum(b) OVER ()); |
| 560 |
} {1 {misuse of window function sum()}} |
| 561 |
do_catchsql_test 11.4 { |
| 562 |
CREATE INDEX t6i ON t6(lead(b) OVER ()); |
| 563 |
} {1 {misuse of window function lead()}} |
| 564 |
|
| 565 |
# 2018-09-17 ticket 510cde277783b5fb5de628393959849dff377eb3 |
| 566 |
# Endless loop on a query with window functions and a limit |
| 567 |
# |
| 568 |
do_execsql_test 12.100 { |
| 569 |
DROP TABLE IF EXISTS t1; |
| 570 |
CREATE TABLE t1(id INT, b VARCHAR, c VARCHAR); |
| 571 |
INSERT INTO t1 VALUES(1, 'A', 'one'); |
| 572 |
INSERT INTO t1 VALUES(2, 'B', 'two'); |
| 573 |
INSERT INTO t1 VALUES(3, 'C', 'three'); |
| 574 |
INSERT INTO t1 VALUES(4, 'D', 'one'); |
| 575 |
INSERT INTO t1 VALUES(5, 'E', 'two'); |
| 576 |
SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x |
| 577 |
FROM t1 WHERE id>1 |
| 578 |
ORDER BY b LIMIT 1; |
| 579 |
} {2 B two} |
| 580 |
do_execsql_test 12.110 { |
| 581 |
INSERT INTO t1 VALUES(6, 'F', 'three'); |
| 582 |
INSERT INTO t1 VALUES(7, 'G', 'one'); |
| 583 |
SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x |
| 584 |
FROM t1 WHERE id>1 |
| 585 |
ORDER BY b LIMIT 2; |
| 586 |
} {2 B two 3 C three} |
| 587 |
|
| 588 |
#------------------------------------------------------------------------- |
| 589 |
|
| 590 |
do_execsql_test 13.1 { |
| 591 |
DROP TABLE IF EXISTS t1; |
| 592 |
CREATE TABLE t1(a int, b int); |
| 593 |
INSERT INTO t1 VALUES(1,11); |
| 594 |
INSERT INTO t1 VALUES(2,12); |
| 595 |
} |
| 596 |
|
| 597 |
do_execsql_test 13.2.1 { |
| 598 |
SELECT a, rank() OVER(ORDER BY b) FROM t1; |
| 599 |
SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; |
| 600 |
} { |
| 601 |
1 1 2 2 2 1 1 2 |
| 602 |
} |
| 603 |
do_execsql_test 13.2.2 { |
| 604 |
SELECT a, rank() OVER(ORDER BY b) FROM t1 |
| 605 |
UNION ALL |
| 606 |
SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; |
| 607 |
} { |
| 608 |
1 1 2 2 2 1 1 2 |
| 609 |
} |
| 610 |
do_execsql_test 13.3 { |
| 611 |
SELECT a, rank() OVER(ORDER BY b) FROM t1 |
| 612 |
UNION |
| 613 |
SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; |
| 614 |
} { |
| 615 |
1 1 1 2 2 1 2 2 |
| 616 |
} |
| 617 |
|
| 618 |
do_execsql_test 13.4 { |
| 619 |
SELECT a, rank() OVER(ORDER BY b) FROM t1 |
| 620 |
EXCEPT |
| 621 |
SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; |
| 622 |
} { |
| 623 |
1 1 2 2 |
| 624 |
} |
| 625 |
|
| 626 |
do_execsql_test 13.5 { |
| 627 |
SELECT a, rank() OVER(ORDER BY b) FROM t1 |
| 628 |
INTERSECT |
| 629 |
SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; |
| 630 |
} {} |
| 631 |
|
| 632 |
# 2018-12-06 |
| 633 |
# https://sqlite.org/src/info/f09fcd17810f65f7 |
| 634 |
# Assertion fault when window functions are used. |
| 635 |
# |
| 636 |
# Root cause is the query flattener invoking sqlite3ExprDup() on |
| 637 |
# expressions that contain subqueries with window functions. The |
| 638 |
# sqlite3ExprDup() routine is not making correctly initializing |
| 639 |
# Select.pWin field of the subqueries. |
| 640 |
# |
| 641 |
sqlite3 db :memory: |
| 642 |
do_execsql_test 14.0 { |
| 643 |
SELECT * FROM( |
| 644 |
SELECT * FROM (SELECT 1 AS c) WHERE c IN ( |
| 645 |
SELECT (row_number() OVER()) FROM (VALUES (0)) |
| 646 |
) |
| 647 |
); |
| 648 |
} {1} |
| 649 |
do_execsql_test 14.1 { |
| 650 |
CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(12345); |
| 651 |
CREATE TABLE t2(c); INSERT INTO t2(c) VALUES(1); |
| 652 |
SELECT y, y+1, y+2 FROM ( |
| 653 |
SELECT c IN ( |
| 654 |
SELECT (row_number() OVER()) FROM t1 |
| 655 |
) AS y FROM t2 |
| 656 |
); |
| 657 |
} {1 2 3} |
| 658 |
|
| 659 |
# 2018-12-31 |
| 660 |
# https://sqlite.org/src/info/d0866b26f83e9c55 |
| 661 |
# Window function in correlated subquery causes assertion fault |
| 662 |
# |
| 663 |
do_catchsql_test 15.0 { |
| 664 |
WITH t(id, parent) AS ( |
| 665 |
SELECT CAST(1 AS INT), CAST(NULL AS INT) |
| 666 |
UNION ALL |
| 667 |
SELECT 2, NULL |
| 668 |
UNION ALL |
| 669 |
SELECT 3, 1 |
| 670 |
UNION ALL |
| 671 |
SELECT 4, 1 |
| 672 |
UNION ALL |
| 673 |
SELECT 5, 2 |
| 674 |
UNION ALL |
| 675 |
SELECT 6, 2 |
| 676 |
), q AS ( |
| 677 |
SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn |
| 678 |
FROM t |
| 679 |
WHERE parent IS NULL |
| 680 |
UNION ALL |
| 681 |
SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn |
| 682 |
FROM q |
| 683 |
JOIN t |
| 684 |
ON t.parent = q.id |
| 685 |
) |
| 686 |
SELECT * |
| 687 |
FROM q; |
| 688 |
} {1 {cannot use window functions in recursive queries}} |
| 689 |
do_execsql_test 15.1 { |
| 690 |
DROP TABLE IF EXISTS t1; |
| 691 |
DROP TABLE IF EXISTS t2; |
| 692 |
CREATE TABLE t1(x); |
| 693 |
INSERT INTO t1 VALUES('a'), ('b'), ('c'); |
| 694 |
CREATE TABLE t2(a, b); |
| 695 |
INSERT INTO t2 VALUES('X', 1), ('X', 2), ('Y', 2), ('Y', 3); |
| 696 |
SELECT x, ( |
| 697 |
SELECT sum(b) |
| 698 |
OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING |
| 699 |
AND UNBOUNDED FOLLOWING) |
| 700 |
FROM t2 WHERE b<x |
| 701 |
) FROM t1; |
| 702 |
} {a 3 b 3 c 3} |
| 703 |
|
| 704 |
do_execsql_test 15.2 { |
| 705 |
SELECT( |
| 706 |
WITH c AS( |
| 707 |
VALUES(1) |
| 708 |
) SELECT '' FROM c,c |
| 709 |
) x WHERE x+x; |
| 710 |
} {} |
| 711 |
|
| 712 |
#------------------------------------------------------------------------- |
| 713 |
|
| 714 |
do_execsql_test 16.0 { |
| 715 |
CREATE TABLE t7(a,b); |
| 716 |
INSERT INTO t7(rowid, a, b) VALUES |
| 717 |
(1, 1, 3), |
| 718 |
(2, 10, 4), |
| 719 |
(3, 100, 2); |
| 720 |
} |
| 721 |
|
| 722 |
do_execsql_test 16.1 { |
| 723 |
SELECT rowid, sum(a) OVER (PARTITION BY b IN (SELECT rowid FROM t7)) FROM t7; |
| 724 |
} { |
| 725 |
2 10 |
| 726 |
1 101 |
| 727 |
3 101 |
| 728 |
} |
| 729 |
|
| 730 |
do_execsql_test 16.2 { |
| 731 |
SELECT rowid, sum(a) OVER w1 FROM t7 |
| 732 |
WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7)); |
| 733 |
} { |
| 734 |
2 10 |
| 735 |
1 101 |
| 736 |
3 101 |
| 737 |
} |
| 738 |
|
| 739 |
#------------------------------------------------------------------------- |
| 740 |
do_execsql_test 17.0 { |
| 741 |
CREATE TABLE t8(a); |
| 742 |
INSERT INTO t8 VALUES(1), (2), (3); |
| 743 |
} |
| 744 |
|
| 745 |
do_execsql_test 17.1 { |
| 746 |
SELECT +sum(0) OVER () ORDER BY +sum(0) OVER (); |
| 747 |
} {0} |
| 748 |
|
| 749 |
do_execsql_test 17.2 { |
| 750 |
select +sum(a) OVER () FROM t8 ORDER BY +sum(a) OVER () DESC; |
| 751 |
} {6 6 6} |
| 752 |
|
| 753 |
do_execsql_test 17.3 { |
| 754 |
SELECT 10+sum(a) OVER (ORDER BY a) |
| 755 |
FROM t8 |
| 756 |
ORDER BY 10+sum(a) OVER (ORDER BY a) DESC; |
| 757 |
} {16 13 11} |
| 758 |
|
| 759 |
|
| 760 |
#------------------------------------------------------------------------- |
| 761 |
# Test error cases from chaining window definitions. |
| 762 |
# |
| 763 |
reset_db |
| 764 |
do_execsql_test 18.0 { |
| 765 |
DROP TABLE IF EXISTS t1; |
| 766 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER); |
| 767 |
INSERT INTO t1 VALUES(1, 'odd', 'one', 1); |
| 768 |
INSERT INTO t1 VALUES(2, 'even', 'two', 2); |
| 769 |
INSERT INTO t1 VALUES(3, 'odd', 'three', 3); |
| 770 |
INSERT INTO t1 VALUES(4, 'even', 'four', 4); |
| 771 |
INSERT INTO t1 VALUES(5, 'odd', 'five', 5); |
| 772 |
INSERT INTO t1 VALUES(6, 'even', 'six', 6); |
| 773 |
} |
| 774 |
|
| 775 |
foreach {tn sql error} { |
| 776 |
1 { |
| 777 |
SELECT c, sum(d) OVER win2 FROM t1 |
| 778 |
WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), |
| 779 |
win2 AS (win1 ORDER BY b) |
| 780 |
} {cannot override frame specification of window: win1} |
| 781 |
|
| 782 |
2 { |
| 783 |
SELECT c, sum(d) OVER win2 FROM t1 |
| 784 |
WINDOW win1 AS (), |
| 785 |
win2 AS (win4 ORDER BY b) |
| 786 |
} {no such window: win4} |
| 787 |
|
| 788 |
3 { |
| 789 |
SELECT c, sum(d) OVER win2 FROM t1 |
| 790 |
WINDOW win1 AS (), |
| 791 |
win2 AS (win1 PARTITION BY d) |
| 792 |
} {cannot override PARTITION clause of window: win1} |
| 793 |
|
| 794 |
4 { |
| 795 |
SELECT c, sum(d) OVER win2 FROM t1 |
| 796 |
WINDOW win1 AS (ORDER BY b), |
| 797 |
win2 AS (win1 ORDER BY d) |
| 798 |
} {cannot override ORDER BY clause of window: win1} |
| 799 |
} { |
| 800 |
do_catchsql_test 18.1.$tn $sql [list 1 $error] |
| 801 |
} |
| 802 |
|
| 803 |
foreach {tn sql error} { |
| 804 |
1 { |
| 805 |
SELECT c, sum(d) OVER (win1 ORDER BY b) FROM t1 |
| 806 |
WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) |
| 807 |
} {cannot override frame specification of window: win1} |
| 808 |
|
| 809 |
2 { |
| 810 |
SELECT c, sum(d) OVER (win4 ORDER BY b) FROM t1 |
| 811 |
WINDOW win1 AS () |
| 812 |
} {no such window: win4} |
| 813 |
|
| 814 |
3 { |
| 815 |
SELECT c, sum(d) OVER (win1 PARTITION BY d) FROM t1 |
| 816 |
WINDOW win1 AS () |
| 817 |
} {cannot override PARTITION clause of window: win1} |
| 818 |
|
| 819 |
4 { |
| 820 |
SELECT c, sum(d) OVER (win1 ORDER BY d) FROM t1 |
| 821 |
WINDOW win1 AS (ORDER BY b) |
| 822 |
} {cannot override ORDER BY clause of window: win1} |
| 823 |
} { |
| 824 |
do_catchsql_test 18.2.$tn $sql [list 1 $error] |
| 825 |
} |
| 826 |
|
| 827 |
do_execsql_test 18.3.1 { |
| 828 |
SELECT string_agg(c, '.') OVER (PARTITION BY b ORDER BY c) |
| 829 |
FROM t1 |
| 830 |
} {four four.six four.six.two five five.one five.one.three} |
| 831 |
|
| 832 |
do_execsql_test 18.3.2 { |
| 833 |
SELECT group_concat(c, '.') OVER (win1 ORDER BY c) |
| 834 |
FROM t1 |
| 835 |
WINDOW win1 AS (PARTITION BY b) |
| 836 |
} {four four.six four.six.two five five.one five.one.three} |
| 837 |
|
| 838 |
do_execsql_test 18.3.3 { |
| 839 |
SELECT string_agg(c, '.') OVER win2 |
| 840 |
FROM t1 |
| 841 |
WINDOW win1 AS (PARTITION BY b), |
| 842 |
win2 AS (win1 ORDER BY c) |
| 843 |
} {four four.six four.six.two five five.one five.one.three} |
| 844 |
|
| 845 |
do_execsql_test 18.3.4 { |
| 846 |
SELECT group_concat(c, '.') OVER (win2) |
| 847 |
FROM t1 |
| 848 |
WINDOW win1 AS (PARTITION BY b), |
| 849 |
win2 AS (win1 ORDER BY c) |
| 850 |
} {four four.six four.six.two five five.one five.one.three} |
| 851 |
|
| 852 |
do_execsql_test 18.3.5 { |
| 853 |
SELECT string_agg(c, '.') OVER win5 |
| 854 |
FROM t1 |
| 855 |
WINDOW win1 AS (PARTITION BY b), |
| 856 |
win2 AS (win1), |
| 857 |
win3 AS (win2), |
| 858 |
win4 AS (win3), |
| 859 |
win5 AS (win4 ORDER BY c) |
| 860 |
} {four four.six four.six.two five five.one five.one.three} |
| 861 |
|
| 862 |
#------------------------------------------------------------------------- |
| 863 |
# Test RANGE <expr> PRECEDING/FOLLOWING when there are string, blob |
| 864 |
# and NULL values in the dataset. |
| 865 |
# |
| 866 |
reset_db |
| 867 |
do_execsql_test 19.0 { |
| 868 |
CREATE TABLE t1(a, b); |
| 869 |
INSERT INTO t1 VALUES |
| 870 |
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), |
| 871 |
('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10); |
| 872 |
} |
| 873 |
do_execsql_test 19.1 { |
| 874 |
SELECT a, sum(b) OVER (ORDER BY a) FROM t1; |
| 875 |
} {1 1 2 3 3 6 4 10 5 15 a 21 b 28 c 36 d 45 e 55} |
| 876 |
|
| 877 |
do_execsql_test 19.2.1 { |
| 878 |
SELECT a, sum(b) OVER ( |
| 879 |
ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING |
| 880 |
) FROM t1; |
| 881 |
} {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} |
| 882 |
do_execsql_test 19.2.2 { |
| 883 |
SELECT a, sum(b) OVER ( |
| 884 |
ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING |
| 885 |
) FROM t1 ORDER BY a ASC; |
| 886 |
} {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} |
| 887 |
|
| 888 |
do_execsql_test 19.3.1 { |
| 889 |
SELECT a, sum(b) OVER ( |
| 890 |
ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING |
| 891 |
) FROM t1; |
| 892 |
} {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} |
| 893 |
do_execsql_test 19.3.2 { |
| 894 |
SELECT a, sum(b) OVER ( |
| 895 |
ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING |
| 896 |
) FROM t1 ORDER BY a ASC; |
| 897 |
} {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} |
| 898 |
|
| 899 |
|
| 900 |
reset_db |
| 901 |
do_execsql_test 20.0 { |
| 902 |
CREATE TABLE t1(a, b); |
| 903 |
INSERT INTO t1 VALUES |
| 904 |
(NULL, 100), (NULL, 100), |
| 905 |
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), |
| 906 |
('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10); |
| 907 |
} |
| 908 |
do_execsql_test 20.1 { |
| 909 |
SELECT a, sum(b) OVER (ORDER BY a) FROM t1; |
| 910 |
} { |
| 911 |
{} 200 {} 200 1 201 2 203 3 206 4 210 5 215 |
| 912 |
a 221 b 228 c 236 d 245 e 255 |
| 913 |
} |
| 914 |
|
| 915 |
do_execsql_test 20.2.1 { |
| 916 |
SELECT a, sum(b) OVER ( |
| 917 |
ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING |
| 918 |
) FROM t1; |
| 919 |
} {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} |
| 920 |
do_execsql_test 20.2.2 { |
| 921 |
SELECT a, sum(b) OVER ( |
| 922 |
ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING |
| 923 |
) FROM t1 ORDER BY a ASC; |
| 924 |
} {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} |
| 925 |
|
| 926 |
do_execsql_test 20.3.1 { |
| 927 |
SELECT a, sum(b) OVER ( |
| 928 |
ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING |
| 929 |
) FROM t1; |
| 930 |
} {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} |
| 931 |
do_execsql_test 20.3.2 { |
| 932 |
SELECT a, sum(b) OVER ( |
| 933 |
ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING |
| 934 |
) FROM t1 ORDER BY a ASC; |
| 935 |
} {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} |
| 936 |
|
| 937 |
#------------------------------------------------------------------------- |
| 938 |
do_execsql_test 21.0 { |
| 939 |
CREATE TABLE keyword_tab( |
| 940 |
current, exclude, filter, following, groups, no, others, over, |
| 941 |
partition, preceding, range, ties, unbounded, window |
| 942 |
); |
| 943 |
} |
| 944 |
do_execsql_test 21.1 { |
| 945 |
SELECT |
| 946 |
current, exclude, filter, following, groups, no, others, over, |
| 947 |
partition, preceding, range, ties, unbounded, window |
| 948 |
FROM keyword_tab |
| 949 |
} |
| 950 |
|
| 951 |
#------------------------------------------------------------------------- |
| 952 |
foreach {tn expr err} { |
| 953 |
1 4.5 0 |
| 954 |
2 NULL 1 |
| 955 |
3 0.0 0 |
| 956 |
4 0.1 0 |
| 957 |
5 -0.1 1 |
| 958 |
6 '' 1 |
| 959 |
7 '2.0' 0 |
| 960 |
8 '2.0x' 1 |
| 961 |
9 x'1234' 1 |
| 962 |
10 '1.2' 0 |
| 963 |
} { |
| 964 |
set res {0 1} |
| 965 |
if {$err} {set res {1 {frame starting offset must be a non-negative number}} } |
| 966 |
do_catchsql_test 22.$tn.1 " |
| 967 |
WITH a(x, y) AS ( VALUES(1, 2) ) |
| 968 |
SELECT sum(x) OVER ( |
| 969 |
ORDER BY y RANGE BETWEEN $expr PRECEDING AND UNBOUNDED FOLLOWING |
| 970 |
) FROM a |
| 971 |
" $res |
| 972 |
|
| 973 |
set res {0 1} |
| 974 |
if {$err} {set res {1 {frame ending offset must be a non-negative number}} } |
| 975 |
do_catchsql_test 22.$tn.2 " |
| 976 |
WITH a(x, y) AS ( VALUES(1, 2) ) |
| 977 |
SELECT sum(x) OVER ( |
| 978 |
ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING |
| 979 |
) FROM a |
| 980 |
" $res |
| 981 |
} |
| 982 |
|
| 983 |
#------------------------------------------------------------------------- |
| 984 |
reset_db |
| 985 |
do_execsql_test 23.0 { |
| 986 |
CREATE TABLE t5(a, b, c); |
| 987 |
CREATE INDEX t5ab ON t5(a, b); |
| 988 |
} |
| 989 |
|
| 990 |
proc do_ordercount_test {tn sql nOrderBy} { |
| 991 |
set plan [execsql "EXPLAIN QUERY PLAN $sql"] |
| 992 |
uplevel [list do_test $tn [list regexp -all ORDER $plan] $nOrderBy] |
| 993 |
} |
| 994 |
|
| 995 |
do_ordercount_test 23.1 { |
| 996 |
SELECT |
| 997 |
sum(c) OVER (ORDER BY a, b), |
| 998 |
sum(c) OVER (PARTITION BY a ORDER BY b) |
| 999 |
FROM t5 |
| 1000 |
} 0 |
| 1001 |
|
| 1002 |
do_ordercount_test 23.2 { |
| 1003 |
SELECT |
| 1004 |
sum(c) OVER (ORDER BY b, a), |
| 1005 |
sum(c) OVER (PARTITION BY b ORDER BY a) |
| 1006 |
FROM t5 |
| 1007 |
} 1 |
| 1008 |
|
| 1009 |
do_ordercount_test 23.3 { |
| 1010 |
SELECT |
| 1011 |
sum(c) OVER (ORDER BY b, a), |
| 1012 |
sum(c) OVER (ORDER BY c, b) |
| 1013 |
FROM t5 |
| 1014 |
} 2 |
| 1015 |
|
| 1016 |
do_ordercount_test 23.4 { |
| 1017 |
SELECT |
| 1018 |
sum(c) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), |
| 1019 |
sum(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), |
| 1020 |
sum(c) OVER (ORDER BY b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
| 1021 |
FROM t5 |
| 1022 |
} 1 |
| 1023 |
|
| 1024 |
do_ordercount_test 23.5 { |
| 1025 |
SELECT |
| 1026 |
sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING), |
| 1027 |
sum(c) OVER (ORDER BY b+1 RANGE UNBOUNDED PRECEDING), |
| 1028 |
sum(c) OVER (ORDER BY b+1 GROUPS UNBOUNDED PRECEDING) |
| 1029 |
FROM t5 |
| 1030 |
} 1 |
| 1031 |
|
| 1032 |
do_ordercount_test 23.6 { |
| 1033 |
SELECT |
| 1034 |
sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING), |
| 1035 |
sum(c) OVER (ORDER BY b+2 RANGE UNBOUNDED PRECEDING), |
| 1036 |
sum(c) OVER (ORDER BY b+3 GROUPS UNBOUNDED PRECEDING) |
| 1037 |
FROM t5 |
| 1038 |
} 3 |
| 1039 |
|
| 1040 |
do_execsql_test 24.1 { |
| 1041 |
SELECT sum(44) OVER () |
| 1042 |
} {44} |
| 1043 |
|
| 1044 |
do_execsql_test 24.2 { |
| 1045 |
SELECT lead(44) OVER () |
| 1046 |
} {{}} |
| 1047 |
|
| 1048 |
#------------------------------------------------------------------------- |
| 1049 |
# |
| 1050 |
reset_db |
| 1051 |
do_execsql_test 25.0 { |
| 1052 |
CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ); |
| 1053 |
CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY ); |
| 1054 |
CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY ); |
| 1055 |
|
| 1056 |
INSERT INTO t1 VALUES(1), (3), (5); |
| 1057 |
INSERT INTO t2 VALUES (3), (5); |
| 1058 |
INSERT INTO t3 VALUES(10), (11), (12); |
| 1059 |
} |
| 1060 |
|
| 1061 |
do_execsql_test 25.1 { |
| 1062 |
SELECT t1.* FROM t1, t2 WHERE |
| 1063 |
t1_id=t2_id AND t1_id IN ( |
| 1064 |
SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3 |
| 1065 |
) |
| 1066 |
} |
| 1067 |
|
| 1068 |
do_execsql_test 25.2 { |
| 1069 |
SELECT t1.* FROM t1, t2 WHERE |
| 1070 |
t1_id=t2_id AND t1_id IN ( |
| 1071 |
SELECT row_number() OVER ( ORDER BY t1_id ) FROM t3 |
| 1072 |
) |
| 1073 |
} {3} |
| 1074 |
|
| 1075 |
#------------------------------------------------------------------------- |
| 1076 |
reset_db |
| 1077 |
do_execsql_test 26.0 { |
| 1078 |
CREATE TABLE t1(x); |
| 1079 |
CREATE TABLE t2(c); |
| 1080 |
} |
| 1081 |
|
| 1082 |
do_execsql_test 26.1 { |
| 1083 |
SELECT ( SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) ) FROM t2 |
| 1084 |
} {} |
| 1085 |
|
| 1086 |
do_execsql_test 26.2 { |
| 1087 |
INSERT INTO t1 VALUES(1), (2), (3), (4); |
| 1088 |
INSERT INTO t2 VALUES(2), (6), (8), (4); |
| 1089 |
SELECT c, c IN ( |
| 1090 |
SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) |
| 1091 |
) FROM t2 |
| 1092 |
} {2 1 6 0 8 0 4 1} |
| 1093 |
|
| 1094 |
do_execsql_test 26.3 { |
| 1095 |
DELETE FROM t1; |
| 1096 |
DELETE FROM t2; |
| 1097 |
|
| 1098 |
INSERT INTO t2 VALUES(1), (2), (3), (4); |
| 1099 |
INSERT INTO t1 VALUES(1), (1), (2), (3), (3), (3), (3), (4), (4); |
| 1100 |
|
| 1101 |
SELECT c, c IN ( |
| 1102 |
SELECT row_number() OVER () FROM ( SELECT 1 FROM t1 WHERE x=c ) |
| 1103 |
) FROM t2 |
| 1104 |
} {1 1 2 0 3 1 4 0} |
| 1105 |
|
| 1106 |
#------------------------------------------------------------------------- |
| 1107 |
reset_db |
| 1108 |
do_execsql_test 27.0 { |
| 1109 |
CREATE TABLE t1(x); |
| 1110 |
INSERT INTO t1 VALUES(NULL), (1), (2), (3), (4), (5); |
| 1111 |
} |
| 1112 |
do_execsql_test 27.1 { |
| 1113 |
SELECT min(x) FROM t1; |
| 1114 |
} {1} |
| 1115 |
do_execsql_test 27.2 { |
| 1116 |
SELECT min(x) OVER win FROM t1 |
| 1117 |
WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) |
| 1118 |
} {1 1 1 2 3 4} |
| 1119 |
|
| 1120 |
#------------------------------------------------------------------------- |
| 1121 |
|
| 1122 |
reset_db |
| 1123 |
do_execsql_test 28.1.1 { |
| 1124 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY); |
| 1125 |
INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0); |
| 1126 |
INSERT INTO t1 VALUES (13,'M', 'cc', NULL); |
| 1127 |
} |
| 1128 |
|
| 1129 |
do_execsql_test 28.1.2 { |
| 1130 |
SELECT string_agg(b,'') OVER w1 FROM t1 |
| 1131 |
WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING) |
| 1132 |
} { |
| 1133 |
{} {} |
| 1134 |
} |
| 1135 |
|
| 1136 |
do_execsql_test 28.2.1 { |
| 1137 |
CREATE TABLE t2(a TEXT, b INTEGER); |
| 1138 |
INSERT INTO t2 VALUES('A', NULL); |
| 1139 |
INSERT INTO t2 VALUES('B', NULL); |
| 1140 |
} |
| 1141 |
|
| 1142 |
do_execsql_test 28.2.1 { |
| 1143 |
DROP TABLE IF EXISTS t1; |
| 1144 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY); |
| 1145 |
INSERT INTO t1 VALUES |
| 1146 |
(10,'J', 'cc', NULL), |
| 1147 |
(11,'K', 'cc', 'xyz'), |
| 1148 |
(13,'M', 'cc', NULL); |
| 1149 |
} |
| 1150 |
|
| 1151 |
do_execsql_test 28.2.2 { |
| 1152 |
SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1 |
| 1153 |
WINDOW w1 AS |
| 1154 |
(ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING) |
| 1155 |
ORDER BY c, d, a; |
| 1156 |
} { |
| 1157 |
10 J cc NULL JM | |
| 1158 |
13 M cc NULL JM | |
| 1159 |
11 K cc 'xyz' K | |
| 1160 |
} |
| 1161 |
|
| 1162 |
#------------------------------------------------------------------------- |
| 1163 |
reset_db |
| 1164 |
|
| 1165 |
do_execsql_test 29.1 { |
| 1166 |
DROP TABLE IF EXISTS t1; |
| 1167 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY); |
| 1168 |
INSERT INTO t1 VALUES |
| 1169 |
(1, 'A', 'aa', 2.5), |
| 1170 |
(2, 'B', 'bb', 3.75), |
| 1171 |
(3, 'C', 'cc', 1.0), |
| 1172 |
(4, 'D', 'cc', 8.25), |
| 1173 |
(5, 'E', 'bb', 6.5), |
| 1174 |
(6, 'F', 'aa', 6.5), |
| 1175 |
(7, 'G', 'aa', 6.0), |
| 1176 |
(8, 'H', 'bb', 9.0), |
| 1177 |
(9, 'I', 'aa', 3.75), |
| 1178 |
(10,'J', 'cc', NULL), |
| 1179 |
(11,'K', 'cc', 'xyz'), |
| 1180 |
(12,'L', 'cc', 'xyZ'), |
| 1181 |
(13,'M', 'cc', NULL); |
| 1182 |
} |
| 1183 |
|
| 1184 |
do_execsql_test 29.2 { |
| 1185 |
SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1 |
| 1186 |
WINDOW w1 AS |
| 1187 |
(PARTITION BY c ORDER BY d DESC |
| 1188 |
RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING) |
| 1189 |
ORDER BY c, d, a; |
| 1190 |
} { |
| 1191 |
1 A aa 2.5 FG | |
| 1192 |
9 I aa 3.75 F | |
| 1193 |
7 G aa 6 {} | |
| 1194 |
6 F aa 6.5 {} | |
| 1195 |
2 B bb 3.75 HE | |
| 1196 |
5 E bb 6.5 H | |
| 1197 |
8 H bb 9 {} | |
| 1198 |
10 J cc NULL JM | |
| 1199 |
13 M cc NULL JM | |
| 1200 |
3 C cc 1 {} | |
| 1201 |
4 D cc 8.25 {} | |
| 1202 |
12 L cc 'xyZ' L | |
| 1203 |
11 K cc 'xyz' K | |
| 1204 |
} |
| 1205 |
|
| 1206 |
# 2019-07-18 |
| 1207 |
# Check-in [7ef7b23cbb1b9ace] (which was itself a fix for ticket |
| 1208 |
# https://sqlite.org/src/info/1be72aab9) introduced a new problem |
| 1209 |
# if the LHS of a BETWEEN operator is a WINDOW function. The problem |
| 1210 |
# was found by (the recently enhanced) dbsqlfuzz. |
| 1211 |
# |
| 1212 |
do_execsql_test 30.0 { |
| 1213 |
DROP TABLE IF EXISTS t1; |
| 1214 |
CREATE TABLE t1(a, b, c); |
| 1215 |
INSERT INTO t1 VALUES('BB','aa',399); |
| 1216 |
SELECT |
| 1217 |
count () OVER win1 NOT BETWEEN 'a' AND 'mmm', |
| 1218 |
count () OVER win3 |
| 1219 |
FROM t1 |
| 1220 |
WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING |
| 1221 |
EXCLUDE CURRENT ROW), |
| 1222 |
win2 AS (PARTITION BY b ORDER BY a), |
| 1223 |
win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING ); |
| 1224 |
} {1 1} |
| 1225 |
|
| 1226 |
#------------------------------------------------------------------------- |
| 1227 |
reset_db |
| 1228 |
do_execsql_test 31.1 { |
| 1229 |
CREATE TABLE t1(a, b); |
| 1230 |
CREATE TABLE t2(c, d); |
| 1231 |
CREATE TABLE t3(e, f); |
| 1232 |
|
| 1233 |
INSERT INTO t1 VALUES(1, 1); |
| 1234 |
INSERT INTO t2 VALUES(1, 1); |
| 1235 |
INSERT INTO t3 VALUES(1, 1); |
| 1236 |
} |
| 1237 |
|
| 1238 |
do_execsql_test 31.2 { |
| 1239 |
SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM ( |
| 1240 |
SELECT * FROM t2 |
| 1241 |
); |
| 1242 |
} {1} |
| 1243 |
|
| 1244 |
do_execsql_test 31.3 { |
| 1245 |
SELECT d IN (SELECT sum(c) OVER (PARTITION BY d ORDER BY e+c) FROM t3) FROM ( |
| 1246 |
SELECT * FROM t2 |
| 1247 |
); |
| 1248 |
} {1} |
| 1249 |
|
| 1250 |
do_catchsql_test 31.3 { |
| 1251 |
SELECT d IN ( |
| 1252 |
SELECT sum(c) OVER ( ROWS BETWEEN d FOLLOWING AND UNBOUNDED FOLLOWING) |
| 1253 |
FROM t3 |
| 1254 |
) |
| 1255 |
FROM ( |
| 1256 |
SELECT * FROM t2 |
| 1257 |
); |
| 1258 |
} {1 {frame starting offset must be a non-negative integer}} |
| 1259 |
|
| 1260 |
do_catchsql_test 31.3 { |
| 1261 |
SELECT d IN ( |
| 1262 |
SELECT sum(c) OVER ( ROWS BETWEEN CURRENT ROW AND c FOLLOWING) |
| 1263 |
FROM t3 |
| 1264 |
) |
| 1265 |
FROM ( |
| 1266 |
SELECT * FROM t2 |
| 1267 |
); |
| 1268 |
} {1 {frame ending offset must be a non-negative integer}} |
| 1269 |
|
| 1270 |
# 2019-11-16 chromium issue 1025467 |
| 1271 |
ifcapable altertable { |
| 1272 |
db close |
| 1273 |
sqlite3 db :memory: |
| 1274 |
do_catchsql_test 32.10 { |
| 1275 |
CREATE VIEW a AS SELECT NULL INTERSECT SELECT NULL ORDER BY s() OVER R; |
| 1276 |
CREATE TABLE a0 AS SELECT 0; |
| 1277 |
ALTER TABLE a0 RENAME TO S; |
| 1278 |
} {1 {error in view a: 1st ORDER BY term does not match any column in the result set}} |
| 1279 |
} |
| 1280 |
|
| 1281 |
reset_db |
| 1282 |
do_execsql_test 33.1 { |
| 1283 |
CREATE TABLE t1(aa, bb); |
| 1284 |
INSERT INTO t1 VALUES(1, 2); |
| 1285 |
INSERT INTO t1 VALUES(5, 6); |
| 1286 |
CREATE TABLE t2(x); |
| 1287 |
INSERT INTO t2 VALUES(1); |
| 1288 |
} |
| 1289 |
do_execsql_test 33.2 { |
| 1290 |
SELECT (SELECT DISTINCT sum(aa) OVER() FROM t1 ORDER BY 1), x FROM t2 |
| 1291 |
ORDER BY 1; |
| 1292 |
} {6 1} |
| 1293 |
|
| 1294 |
reset_db |
| 1295 |
do_execsql_test 34.1 { |
| 1296 |
CREATE TABLE t1(a,b,c); |
| 1297 |
} |
| 1298 |
do_execsql_test 34.2 { |
| 1299 |
SELECT avg(a) OVER ( |
| 1300 |
ORDER BY (SELECT sum(b) OVER () |
| 1301 |
FROM t1 ORDER BY ( |
| 1302 |
SELECT total(d) OVER (ORDER BY c) |
| 1303 |
FROM (SELECT 1 AS d) ORDER BY 1 |
| 1304 |
) |
| 1305 |
) |
| 1306 |
) |
| 1307 |
FROM t1; |
| 1308 |
} |
| 1309 |
|
| 1310 |
#------------------------------------------------------------------------- |
| 1311 |
reset_db |
| 1312 |
do_catchsql_test 35.0 { |
| 1313 |
SELECT * WINDOW f AS () ORDER BY name COLLATE nocase; |
| 1314 |
} {1 {no tables specified}} |
| 1315 |
|
| 1316 |
do_catchsql_test 35.1 { |
| 1317 |
VALUES(1) INTERSECT SELECT * WINDOW f AS () ORDER BY x COLLATE nocase; |
| 1318 |
} {1 {no tables specified}} |
| 1319 |
|
| 1320 |
do_execsql_test 35.2 { |
| 1321 |
CREATE TABLE t1(x); |
| 1322 |
INSERT INTO t1 VALUES(1), (2), (3); |
| 1323 |
VALUES(1) INTERSECT |
| 1324 |
SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1; |
| 1325 |
} {1} |
| 1326 |
|
| 1327 |
do_execsql_test 35.3 { |
| 1328 |
VALUES(8) EXCEPT |
| 1329 |
SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1; |
| 1330 |
} {8} |
| 1331 |
|
| 1332 |
do_execsql_test 35.4 { |
| 1333 |
VALUES(1) UNION |
| 1334 |
SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1; |
| 1335 |
} {1 3 6} |
| 1336 |
|
| 1337 |
# 2019-12-07 gramfuzz find |
| 1338 |
# |
| 1339 |
do_execsql_test 36.10 { |
| 1340 |
VALUES(count(*)OVER()); |
| 1341 |
} {1} |
| 1342 |
do_execsql_test 36.20 { |
| 1343 |
VALUES(count(*)OVER()),(2); |
| 1344 |
} {1 2} |
| 1345 |
do_execsql_test 36.30 { |
| 1346 |
VALUES(2),(count(*)OVER()); |
| 1347 |
} {2 1} |
| 1348 |
do_execsql_test 36.40 { |
| 1349 |
VALUES(2),(3),(count(*)OVER()),(4),(5); |
| 1350 |
} {2 3 1 4 5} |
| 1351 |
|
| 1352 |
# 2019-12-17 crash test case found by Yongheng and Rui |
| 1353 |
# See check-in 1ca0bd982ab1183b |
| 1354 |
# |
| 1355 |
reset_db |
| 1356 |
do_execsql_test 37.10 { |
| 1357 |
CREATE TABLE t0(a UNIQUE, b PRIMARY KEY); |
| 1358 |
CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1))) FROM t0; |
| 1359 |
SELECT c FROM v0 WHERE c BETWEEN 10 AND 20; |
| 1360 |
} {} |
| 1361 |
do_execsql_test 37.20 { |
| 1362 |
DROP VIEW v0; |
| 1363 |
CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1234))) FROM t0; |
| 1364 |
SELECT c FROM v0 WHERE c BETWEEN -10 AND 20; |
| 1365 |
} {} |
| 1366 |
|
| 1367 |
# 2019-12-20 mrigger reported problem with a FILTER clause on an aggregate |
| 1368 |
# in a join. |
| 1369 |
# |
| 1370 |
reset_db |
| 1371 |
do_catchsql_test 38.10 { |
| 1372 |
CREATE TABLE t0(c0); |
| 1373 |
CREATE TABLE t1(c0, c1 UNIQUE); |
| 1374 |
INSERT INTO t0(c0) VALUES(1); |
| 1375 |
INSERT INTO t1(c0,c1) VALUES(2,3); |
| 1376 |
SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(0) FILTER(WHERE t1.c1)); |
| 1377 |
} {1 {misuse of aggregate: AVG()}} |
| 1378 |
do_execsql_test 38.20 { |
| 1379 |
SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1; |
| 1380 |
} {1 1.0} |
| 1381 |
do_catchsql_test 38.30 { |
| 1382 |
SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1)); |
| 1383 |
} {1 {misuse of aggregate: AVG()}} |
| 1384 |
|
| 1385 |
reset_db |
| 1386 |
do_execsql_test 39.1 { |
| 1387 |
CREATE TABLE t0(c0 UNIQUE); |
| 1388 |
} |
| 1389 |
do_execsql_test 39.2 { |
| 1390 |
SELECT FIRST_VALUE(0) OVER(); |
| 1391 |
} {0} |
| 1392 |
do_execsql_test 39.3 { |
| 1393 |
SELECT * FROM t0 WHERE(c0, 0) IN(SELECT FIRST_VALUE(0) OVER(), 0); |
| 1394 |
} |
| 1395 |
do_execsql_test 39.4 { |
| 1396 |
SELECT * FROM t0 WHERE (t0.c0, 1) IN(SELECT NTILE(1) OVER(), 0 FROM t0); |
| 1397 |
} |
| 1398 |
|
| 1399 |
ifcapable rtree { |
| 1400 |
# 2019-12-25 ticket d87336c81c7d0873 |
| 1401 |
# |
| 1402 |
reset_db |
| 1403 |
do_catchsql_test 40.1 { |
| 1404 |
CREATE VIRTUAL TABLE t0 USING rtree(c0, c1, c2); |
| 1405 |
SELECT * FROM t0 |
| 1406 |
WHERE ((0,0) IN (SELECT COUNT(*),LAG(5)OVER(PARTITION BY 0) FROM t0),0)<=(c1,0); |
| 1407 |
} {0 {}} |
| 1408 |
} |
| 1409 |
|
| 1410 |
#------------------------------------------------------------------------- |
| 1411 |
reset_db |
| 1412 |
do_execsql_test 41.1 { |
| 1413 |
CREATE TABLE t1(a, b, c); |
| 1414 |
INSERT INTO t1 VALUES(NULL,'bb',355); |
| 1415 |
INSERT INTO t1 VALUES('CC','aa',158); |
| 1416 |
INSERT INTO t1 VALUES('GG','bb',929); |
| 1417 |
INSERT INTO t1 VALUES('FF','Rb',574); |
| 1418 |
} |
| 1419 |
|
| 1420 |
do_execsql_test 41.2 { |
| 1421 |
SELECT min(c) OVER ( |
| 1422 |
ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING |
| 1423 |
) FROM t1 |
| 1424 |
} {355 158 574 929} |
| 1425 |
|
| 1426 |
do_execsql_test 41.2 { |
| 1427 |
SELECT min(c) OVER ( |
| 1428 |
ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING |
| 1429 |
) << 100 FROM t1 |
| 1430 |
} {0 0 0 0} |
| 1431 |
|
| 1432 |
do_execsql_test 41.3 { |
| 1433 |
SELECT |
| 1434 |
min(c) OVER win3 << first_value(c) OVER win3, |
| 1435 |
min(c) OVER win3 << first_value(c) OVER win3 |
| 1436 |
FROM t1 |
| 1437 |
WINDOW win3 AS ( |
| 1438 |
PARTITION BY 6 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING |
| 1439 |
); |
| 1440 |
} {0 0 0 0 0 0 0 0} |
| 1441 |
|
| 1442 |
#------------------------------------------------------------------------- |
| 1443 |
reset_db |
| 1444 |
do_execsql_test 42.1 { |
| 1445 |
CREATE TABLE t1(a, b, c); |
| 1446 |
INSERT INTO t1 VALUES(1, 1, 1); |
| 1447 |
INSERT INTO t1 VALUES(2, 2, 2); |
| 1448 |
} |
| 1449 |
do_execsql_test 42.2 { |
| 1450 |
SELECT * FROM t1 WHERE (0, 0) IN ( SELECT count(*), 0 FROM t1 ) |
| 1451 |
} {} |
| 1452 |
do_execsql_test 42.3 { |
| 1453 |
SELECT * FROM t1 WHERE (2, 0) IN ( SELECT count(*), 0 FROM t1 ) |
| 1454 |
} {1 1 1 2 2 2} |
| 1455 |
|
| 1456 |
do_execsql_test 42.3 { |
| 1457 |
SELECT count(*), max(a) OVER () FROM t1 GROUP BY c; |
| 1458 |
} {1 2 1 2} |
| 1459 |
|
| 1460 |
do_execsql_test 42.4 { |
| 1461 |
SELECT sum(a), max(b) OVER () FROM t1; |
| 1462 |
} {3 1} |
| 1463 |
|
| 1464 |
do_execsql_test 42.5 { |
| 1465 |
CREATE TABLE t2(a, b); |
| 1466 |
INSERT INTO t2 VALUES('a', 1); |
| 1467 |
INSERT INTO t2 VALUES('a', 2); |
| 1468 |
INSERT INTO t2 VALUES('a', 3); |
| 1469 |
INSERT INTO t2 VALUES('b', 4); |
| 1470 |
INSERT INTO t2 VALUES('b', 5); |
| 1471 |
INSERT INTO t2 VALUES('b', 6); |
| 1472 |
} |
| 1473 |
|
| 1474 |
do_execsql_test 42.6 { |
| 1475 |
SELECT a, sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2 GROUP BY a; |
| 1476 |
} {a 6 6 b 15 21} |
| 1477 |
|
| 1478 |
do_execsql_test 42.7 { |
| 1479 |
SELECT sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2; |
| 1480 |
} {21 21} |
| 1481 |
|
| 1482 |
#------------------------------------------------------------------------- |
| 1483 |
reset_db |
| 1484 |
do_execsql_test 43.1.1 { |
| 1485 |
CREATE TABLE t1(x INTEGER PRIMARY KEY); |
| 1486 |
INSERT INTO t1 VALUES (10); |
| 1487 |
} |
| 1488 |
do_catchsql_test 43.1.2 { |
| 1489 |
SELECT count() OVER() AS m FROM t1 ORDER BY (SELECT m); |
| 1490 |
} {1 {misuse of aliased window function m}} |
| 1491 |
|
| 1492 |
reset_db |
| 1493 |
do_execsql_test 43.2.1 { |
| 1494 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); |
| 1495 |
INSERT INTO t1(a, b) VALUES(1, 10); -- 10 |
| 1496 |
INSERT INTO t1(a, b) VALUES(2, 15); -- 25 |
| 1497 |
INSERT INTO t1(a, b) VALUES(3, -5); -- 20 |
| 1498 |
INSERT INTO t1(a, b) VALUES(4, -5); -- 15 |
| 1499 |
INSERT INTO t1(a, b) VALUES(5, 20); -- 35 |
| 1500 |
INSERT INTO t1(a, b) VALUES(6, -11); -- 24 |
| 1501 |
} |
| 1502 |
|
| 1503 |
do_execsql_test 43.2.2 { |
| 1504 |
SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY 2 |
| 1505 |
} { |
| 1506 |
1 10 4 15 3 20 6 24 2 25 5 35 |
| 1507 |
} |
| 1508 |
|
| 1509 |
do_execsql_test 43.2.3 { |
| 1510 |
SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc |
| 1511 |
} { |
| 1512 |
1 10 4 15 3 20 6 24 2 25 5 35 |
| 1513 |
} |
| 1514 |
|
| 1515 |
do_execsql_test 43.2.4 { |
| 1516 |
SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc+5 |
| 1517 |
} { |
| 1518 |
1 10 4 15 3 20 6 24 2 25 5 35 |
| 1519 |
} |
| 1520 |
|
| 1521 |
do_catchsql_test 43.2.5 { |
| 1522 |
SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc) |
| 1523 |
} {1 {misuse of aliased window function abc}} |
| 1524 |
|
| 1525 |
do_catchsql_test 43.2.6 { |
| 1526 |
SELECT a, 1+sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc) |
| 1527 |
} {1 {misuse of aliased window function abc}} |
| 1528 |
|
| 1529 |
#------------------------------------------------------------------------- |
| 1530 |
reset_db |
| 1531 |
do_execsql_test 44.1 { |
| 1532 |
CREATE TABLE t0(c0); |
| 1533 |
} |
| 1534 |
|
| 1535 |
do_catchsql_test 44.2.1 { |
| 1536 |
SELECT ntile(0) OVER (); |
| 1537 |
} {1 {argument of ntile must be a positive integer}} |
| 1538 |
do_catchsql_test 44.2.2 { |
| 1539 |
SELECT (0, 0) IN(SELECT MIN(c0), NTILE(0) OVER()) FROM t0; |
| 1540 |
} {1 {argument of ntile must be a positive integer}} |
| 1541 |
|
| 1542 |
do_execsql_test 44.3.1 { |
| 1543 |
SELECT ntile(1) OVER (); |
| 1544 |
} {1} |
| 1545 |
do_execsql_test 44.3.2 { |
| 1546 |
SELECT (0, 0) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0; |
| 1547 |
} {0} |
| 1548 |
|
| 1549 |
do_execsql_test 44.4.2 { |
| 1550 |
INSERT INTO t0 VALUES(2), (1), (0); |
| 1551 |
SELECT (0, 1) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0; |
| 1552 |
} {1} |
| 1553 |
|
| 1554 |
#------------------------------------------------------------------------- |
| 1555 |
reset_db |
| 1556 |
do_execsql_test 45.1 { |
| 1557 |
CREATE TABLE t0(x); |
| 1558 |
CREATE TABLE t1(a); |
| 1559 |
INSERT INTO t1 VALUES(1000); |
| 1560 |
INSERT INTO t1 VALUES(1000); |
| 1561 |
INSERT INTO t0 VALUES(10000); |
| 1562 |
} |
| 1563 |
do_execsql_test 45.2 { |
| 1564 |
SELECT * FROM ( |
| 1565 |
SELECT sum (a) OVER() FROM t1 UNION ALL SELECT x FROM t0 |
| 1566 |
); |
| 1567 |
} {2000 2000 10000} |
| 1568 |
|
| 1569 |
#------------------------------------------------------------------------- |
| 1570 |
reset_db |
| 1571 |
do_execsql_test 46.1 { |
| 1572 |
CREATE TABLE t1 (a); |
| 1573 |
CREATE INDEX i1 ON t1(a); |
| 1574 |
|
| 1575 |
INSERT INTO t1 VALUES (10); |
| 1576 |
} |
| 1577 |
|
| 1578 |
do_execsql_test 46.2 { |
| 1579 |
SELECT (SELECT sum(a) OVER(ORDER BY a)) FROM t1 |
| 1580 |
} 10 |
| 1581 |
|
| 1582 |
do_execsql_test 46.3 { |
| 1583 |
SELECT * FROM t1 WHERE (SELECT sum(a) OVER(ORDER BY a)); |
| 1584 |
} 10 |
| 1585 |
|
| 1586 |
do_execsql_test 46.4 { |
| 1587 |
SELECT * FROM t1 NATURAL JOIN t1 |
| 1588 |
WHERE a=1 |
| 1589 |
OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10) |
| 1590 |
} 10 |
| 1591 |
|
| 1592 |
#------------------------------------------------------------------------- |
| 1593 |
reset_db |
| 1594 |
do_execsql_test 47.0 { |
| 1595 |
CREATE TABLE t1( |
| 1596 |
a, |
| 1597 |
e, |
| 1598 |
f, |
| 1599 |
g UNIQUE, |
| 1600 |
h UNIQUE |
| 1601 |
); |
| 1602 |
} |
| 1603 |
|
| 1604 |
do_execsql_test 47.1 { |
| 1605 |
CREATE VIEW t2(k) AS |
| 1606 |
SELECT e FROM t1 WHERE g = 'abc' OR h BETWEEN 10 AND f; |
| 1607 |
} |
| 1608 |
|
| 1609 |
do_catchsql_test 47.2 { |
| 1610 |
SELECT 234 FROM t2 |
| 1611 |
WHERE k=1 |
| 1612 |
OR (SELECT k FROM t2 WHERE (SELECT sum(a) OVER() FROM t1 GROUP BY 1)); |
| 1613 |
} {1 {misuse of window function sum()}} |
| 1614 |
|
| 1615 |
#------------------------------------------------------------------------- |
| 1616 |
reset_db |
| 1617 |
do_execsql_test 48.0 { |
| 1618 |
CREATE TABLE t1(a); |
| 1619 |
INSERT INTO t1 VALUES(1); |
| 1620 |
INSERT INTO t1 VALUES(2); |
| 1621 |
INSERT INTO t1 VALUES(3); |
| 1622 |
SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x)) |
| 1623 |
FROM (SELECT (SELECT sum(a) FROM t1) AS x FROM t1); |
| 1624 |
} {12 12 12} |
| 1625 |
|
| 1626 |
do_execsql_test 48.1 { |
| 1627 |
SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x)) |
| 1628 |
FROM (SELECT (SELECT sum(a) FROM t1 GROUP BY a) AS x FROM t1); |
| 1629 |
} {2 2 2} |
| 1630 |
|
| 1631 |
#------------------------------------------------------------------------- |
| 1632 |
reset_db |
| 1633 |
do_execsql_test 49.1 { |
| 1634 |
CREATE TABLE t1 (a PRIMARY KEY); |
| 1635 |
INSERT INTO t1 VALUES(1); |
| 1636 |
} |
| 1637 |
|
| 1638 |
do_execsql_test 49.2 { |
| 1639 |
SELECT b AS c FROM ( |
| 1640 |
SELECT a AS b FROM ( |
| 1641 |
SELECT a FROM t1 WHERE a=1 OR (SELECT sum(a) OVER ()) |
| 1642 |
) |
| 1643 |
WHERE b=1 OR b<10 |
| 1644 |
) |
| 1645 |
WHERE c=1 OR c>=10; |
| 1646 |
} {1} |
| 1647 |
|
| 1648 |
|
| 1649 |
#------------------------------------------------------------------------- |
| 1650 |
reset_db |
| 1651 |
do_execsql_test 50.0 { |
| 1652 |
CREATE TABLE t1 (a DOUBLE PRIMARY KEY); |
| 1653 |
INSERT INTO t1 VALUES(10.0); |
| 1654 |
} |
| 1655 |
|
| 1656 |
do_execsql_test 50.1 { |
| 1657 |
SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) |
| 1658 |
} {10.0} |
| 1659 |
|
| 1660 |
do_execsql_test 50.2 { |
| 1661 |
SELECT * FROM ( |
| 1662 |
SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) |
| 1663 |
) |
| 1664 |
WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 ) |
| 1665 |
} {10.0} |
| 1666 |
|
| 1667 |
do_execsql_test 50.3 { |
| 1668 |
SELECT a FROM ( |
| 1669 |
SELECT * FROM ( |
| 1670 |
SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) |
| 1671 |
) |
| 1672 |
WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 ) |
| 1673 |
) |
| 1674 |
WHERE a=1 OR a=10.0 |
| 1675 |
} {10.0} |
| 1676 |
|
| 1677 |
do_execsql_test 50.4 { |
| 1678 |
SELECT a FROM ( |
| 1679 |
SELECT * FROM ( |
| 1680 |
SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) |
| 1681 |
) |
| 1682 |
WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 ) |
| 1683 |
) |
| 1684 |
WHERE a=1 OR ((SELECT sum(a) OVER(ORDER BY a%8)) AND 10<=a) |
| 1685 |
} {10.0} |
| 1686 |
|
| 1687 |
do_execsql_test 50.5 { |
| 1688 |
SELECT * FROM (SELECT * FROM t1 NATURAL JOIN t1 WHERE a%1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum((SELECT * FROM (SELECT * FROM (SELECT * FROM t1 NATURAL JOIN t1 WHERE a%1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum((SELECT * FROM t1 NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)))OVER(ORDER BY a% 1 )) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND 10<=a)))OVER(ORDER BY a%5)) AND a<=10); |
| 1689 |
} {10.0} |
| 1690 |
|
| 1691 |
# 2020-04-03 ticket af4556bb5c285c08 |
| 1692 |
# |
| 1693 |
reset_db |
| 1694 |
do_catchsql_test 51.1 { |
| 1695 |
CREATE TABLE a(b, c); |
| 1696 |
SELECT c FROM a GROUP BY c |
| 1697 |
HAVING(SELECT(sum(b) OVER(ORDER BY b), |
| 1698 |
sum(b) OVER(PARTITION BY min(DISTINCT c), c ORDER BY b))); |
| 1699 |
} {1 {row value misused}} |
| 1700 |
|
| 1701 |
#------------------------------------------------------------------------- |
| 1702 |
reset_db |
| 1703 |
do_execsql_test 52.1 { |
| 1704 |
CREATE TABLE t1(a, b, c); |
| 1705 |
INSERT INTO t1 VALUES('AA','bb',356); |
| 1706 |
INSERT INTO t1 VALUES('CC','aa',158); |
| 1707 |
INSERT INTO t1 VALUES('BB','aa',399); |
| 1708 |
INSERT INTO t1 VALUES('FF','bb',938); |
| 1709 |
} |
| 1710 |
|
| 1711 |
do_execsql_test 52.2 { |
| 1712 |
SELECT |
| 1713 |
count() OVER win1, |
| 1714 |
sum(c) OVER win2, |
| 1715 |
first_value(c) OVER win2, |
| 1716 |
count(a) OVER (ORDER BY b) |
| 1717 |
FROM t1 |
| 1718 |
WINDOW |
| 1719 |
win1 AS (ORDER BY a), |
| 1720 |
win2 AS (PARTITION BY 6 ORDER BY a |
| 1721 |
RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING ); |
| 1722 |
} { |
| 1723 |
1 356 356 4 |
| 1724 |
2 399 399 2 |
| 1725 |
3 158 158 2 |
| 1726 |
4 938 938 4 |
| 1727 |
} |
| 1728 |
|
| 1729 |
do_execsql_test 52.3 { |
| 1730 |
SELECT |
| 1731 |
count() OVER (), |
| 1732 |
sum(c) OVER win2, |
| 1733 |
first_value(c) OVER win2, |
| 1734 |
count(a) OVER (ORDER BY b) |
| 1735 |
FROM t1 |
| 1736 |
WINDOW |
| 1737 |
win1 AS (ORDER BY a), |
| 1738 |
win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a |
| 1739 |
RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING ); |
| 1740 |
} { |
| 1741 |
4 356 356 4 |
| 1742 |
4 399 399 2 |
| 1743 |
4 158 158 2 |
| 1744 |
4 938 938 4 |
| 1745 |
} |
| 1746 |
|
| 1747 |
do_execsql_test 52.4 { |
| 1748 |
SELECT |
| 1749 |
count() OVER win1, |
| 1750 |
sum(c) OVER win2, |
| 1751 |
first_value(c) OVER win2, |
| 1752 |
count(a) OVER (ORDER BY b) |
| 1753 |
FROM t1 |
| 1754 |
WINDOW |
| 1755 |
win1 AS (ORDER BY a), |
| 1756 |
win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a |
| 1757 |
RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING ); |
| 1758 |
} { |
| 1759 |
1 356 356 4 |
| 1760 |
2 399 399 2 |
| 1761 |
3 158 158 2 |
| 1762 |
4 938 938 4 |
| 1763 |
} |
| 1764 |
|
| 1765 |
# 2020-05-23 |
| 1766 |
# ticket 7a5279a25c57adf1 |
| 1767 |
# |
| 1768 |
reset_db |
| 1769 |
do_execsql_test 53.0 { |
| 1770 |
CREATE TABLE a(c UNIQUE); |
| 1771 |
INSERT INTO a VALUES(4),(0),(9),(-9); |
| 1772 |
SELECT a.c |
| 1773 |
FROM a |
| 1774 |
JOIN a AS b ON a.c=4 |
| 1775 |
JOIN a AS e ON a.c=e.c |
| 1776 |
WHERE a.c=(SELECT (SELECT coalesce(lead(2) OVER(),0) + sum(d.c)) |
| 1777 |
FROM a AS d |
| 1778 |
WHERE a.c); |
| 1779 |
} {4 4 4 4} |
| 1780 |
|
| 1781 |
#------------------------------------------------------------------------- |
| 1782 |
reset_db |
| 1783 |
do_execsql_test 54.1 { |
| 1784 |
CREATE TABLE t1(a VARCHAR(20), b FLOAT); |
| 1785 |
INSERT INTO t1 VALUES('1',10.0); |
| 1786 |
} |
| 1787 |
|
| 1788 |
do_catchsql_test 54.2 { |
| 1789 |
SELECT * FROM ( |
| 1790 |
SELECT sum(b) OVER() AS c FROM t1 |
| 1791 |
UNION |
| 1792 |
SELECT b AS c FROM t1 |
| 1793 |
) WHERE c>10; |
| 1794 |
} {0 {}} |
| 1795 |
|
| 1796 |
do_execsql_test 54.3 { |
| 1797 |
INSERT INTO t1 VALUES('2',5.0); |
| 1798 |
INSERT INTO t1 VALUES('3',15.0); |
| 1799 |
} |
| 1800 |
|
| 1801 |
do_catchsql_test 54.4 { |
| 1802 |
SELECT * FROM ( |
| 1803 |
SELECT sum(b) OVER() AS c FROM t1 |
| 1804 |
UNION |
| 1805 |
SELECT b AS c FROM t1 |
| 1806 |
) WHERE c>10; |
| 1807 |
} {0 {15.0 30.0}} |
| 1808 |
|
| 1809 |
# 2020-06-05 ticket c8d3b9f0a750a529 |
| 1810 |
reset_db |
| 1811 |
do_execsql_test 55.1 { |
| 1812 |
CREATE TABLE a(b); |
| 1813 |
SELECT |
| 1814 |
(SELECT b FROM a |
| 1815 |
GROUP BY b |
| 1816 |
HAVING (SELECT COUNT()OVER() + lead(b)OVER(ORDER BY SUM(DISTINCT b) + b)) |
| 1817 |
) |
| 1818 |
FROM a |
| 1819 |
UNION |
| 1820 |
SELECT 99 |
| 1821 |
ORDER BY 1; |
| 1822 |
} {99} |
| 1823 |
|
| 1824 |
#------------------------------------------------------------------------ |
| 1825 |
reset_db |
| 1826 |
do_execsql_test 56.1 { |
| 1827 |
CREATE TABLE t1(a, b INTEGER); |
| 1828 |
CREATE TABLE t2(c, d); |
| 1829 |
} |
| 1830 |
do_catchsql_test 56.2 { |
| 1831 |
SELECT avg(b) FROM t1 |
| 1832 |
UNION ALL |
| 1833 |
SELECT min(c) OVER () FROM t2 |
| 1834 |
ORDER BY nosuchcolumn; |
| 1835 |
} {1 {1st ORDER BY term does not match any column in the result set}} |
| 1836 |
|
| 1837 |
reset_db |
| 1838 |
do_execsql_test 57.1 { |
| 1839 |
CREATE TABLE t4(a, b, c, d, e); |
| 1840 |
} |
| 1841 |
|
| 1842 |
do_catchsql_test 57.2 { |
| 1843 |
SELECT b FROM t4 |
| 1844 |
UNION |
| 1845 |
SELECT a FROM t4 |
| 1846 |
ORDER BY ( |
| 1847 |
SELECT sum(x) OVER() FROM ( |
| 1848 |
SELECT c AS x FROM t4 |
| 1849 |
UNION |
| 1850 |
SELECT d FROM t4 |
| 1851 |
ORDER BY (SELECT e FROM t4) |
| 1852 |
) |
| 1853 |
); |
| 1854 |
} {1 {1st ORDER BY term does not match any column in the result set}} |
| 1855 |
|
| 1856 |
# 2020-06-06 various dbsqlfuzz finds and |
| 1857 |
# ticket 0899cf62f597d7e7 |
| 1858 |
# |
| 1859 |
reset_db |
| 1860 |
do_execsql_test 57.1 { |
| 1861 |
CREATE TABLE t1(a, b, c); |
| 1862 |
INSERT INTO t1 VALUES(NULL,NULL,NULL); |
| 1863 |
SELECT |
| 1864 |
sum(a), |
| 1865 |
min(b) OVER (), |
| 1866 |
count(c) OVER (ORDER BY b) |
| 1867 |
FROM t1; |
| 1868 |
} {{} {} 0} |
| 1869 |
do_execsql_test 57.2 { |
| 1870 |
CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; |
| 1871 |
INSERT INTO v0 VALUES ( 10 ) ; |
| 1872 |
SELECT DISTINCT v1, lead(v1) OVER() FROM v0 GROUP BY v1 ORDER BY 2; |
| 1873 |
} {10 {}} |
| 1874 |
do_catchsql_test 57.3 { |
| 1875 |
DROP TABLE t1; |
| 1876 |
CREATE TABLE t1(a); |
| 1877 |
INSERT INTO t1(a) VALUES(22); |
| 1878 |
CREATE TABLE t3(y); |
| 1879 |
INSERT INTO t3(y) VALUES(5),(11),(-9); |
| 1880 |
SELECT ( |
| 1881 |
SELECT max(y) OVER( ORDER BY (SELECT x FROM (SELECT sum(y) AS x FROM t1))) |
| 1882 |
) |
| 1883 |
FROM t3; |
| 1884 |
} {0 5} |
| 1885 |
|
| 1886 |
# 2020-06-06 ticket 1f6f353b684fc708 |
| 1887 |
reset_db |
| 1888 |
do_execsql_test 58.1 { |
| 1889 |
CREATE TABLE a(a, b, c); |
| 1890 |
INSERT INTO a VALUES(1, 2, 3); |
| 1891 |
INSERT INTO a VALUES(4, 5, 6); |
| 1892 |
SELECT sum(345+b) OVER (ORDER BY b), |
| 1893 |
sum(avg(678)) OVER (ORDER BY c) FROM a; |
| 1894 |
} {347 678.0} |
| 1895 |
|
| 1896 |
# 2020-06-06 ticket e5504e987e419fb0 |
| 1897 |
do_catchsql_test 59.1 { |
| 1898 |
DROP TABLE IF EXISTS t1; |
| 1899 |
CREATE TABLE t1(x INTEGER PRIMARY KEY); |
| 1900 |
INSERT INTO t1 VALUES (123); |
| 1901 |
SELECT |
| 1902 |
ntile( (SELECT sum(x)) ) OVER(ORDER BY x), |
| 1903 |
min(x) OVER(ORDER BY x) |
| 1904 |
FROM t1; |
| 1905 |
} {1 {misuse of aggregate: sum()}} |
| 1906 |
|
| 1907 |
# 2020-06-07 ticket f7d890858f361402 |
| 1908 |
do_execsql_test 60.1 { |
| 1909 |
DROP TABLE IF EXISTS t1; |
| 1910 |
CREATE TABLE t1 (x INTEGER PRIMARY KEY); |
| 1911 |
INSERT INTO t1 VALUES (99); |
| 1912 |
SELECT EXISTS(SELECT count(*) OVER() FROM t1 ORDER BY sum(x) OVER()); |
| 1913 |
} {1} |
| 1914 |
|
| 1915 |
# 2020-06-07 test case generated by dbsqlfuzz showing how an AggInfo |
| 1916 |
# object might be referenced after the sqlite3Select() call that created |
| 1917 |
# it returns. This proves the need to persist all AggInfo objects until |
| 1918 |
# the Parse object is destroyed. |
| 1919 |
# |
| 1920 |
reset_db |
| 1921 |
do_catchsql_test 61.1 { |
| 1922 |
CREATE TABLE t1(a); |
| 1923 |
INSERT INTO t1 VALUES(5),(NULL),('seventeen'); |
| 1924 |
SELECT (SELECT max(x)OVER(ORDER BY x) % min(x)OVER(ORDER BY CASE x WHEN 889 THEN x WHEN x THEN x END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST((SELECT (SELECT max(x)OVER(ORDER BY x) / min(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN -true THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x) & sum ( a )OVER(ORDER BY CASE x WHEN -8 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a AS )) FROM t1) AS x FROM t1)) AS t1 )) FROM t1) AS x FROM t1)) AS x )) FROM t1) AS x FROM t1)) AS real)) FROM t1) AS x FROM t1); |
| 1925 |
} {0 {{} {} {}}} |
| 1926 |
|
| 1927 |
foreach tn {1 2} { |
| 1928 |
if {$tn==2} { optimization_control db query-flattener 0 } |
| 1929 |
do_catchsql_test 61.2.$tn { |
| 1930 |
SELECT |
| 1931 |
(SELECT max(x)OVER(ORDER BY x) / min(x) OVER() ) |
| 1932 |
FROM ( |
| 1933 |
SELECT (SELECT sum(a) FROM t1 ) AS x FROM t1 |
| 1934 |
) |
| 1935 |
|
| 1936 |
} {0 {1.0 1.0 1.0}} |
| 1937 |
} |
| 1938 |
|
| 1939 |
reset_db |
| 1940 |
optimization_control db all 0 |
| 1941 |
do_execsql_test 61.3.0 { |
| 1942 |
CREATE TABLE t1(a); |
| 1943 |
CREATE TABLE t2(y); |
| 1944 |
} |
| 1945 |
|
| 1946 |
do_execsql_test 61.3.1 { |
| 1947 |
SELECT ( |
| 1948 |
SELECT count(a) OVER ( ORDER BY (SELECT sum(y) FROM t2) ) |
| 1949 |
+ total(a) OVER() |
| 1950 |
) |
| 1951 |
FROM t1 |
| 1952 |
} {} |
| 1953 |
do_execsql_test 61.4.2 { |
| 1954 |
SELECT ( |
| 1955 |
SELECT count(a) OVER ( ORDER BY sum(a) ) |
| 1956 |
+ total(a) OVER() |
| 1957 |
) |
| 1958 |
FROM t1 |
| 1959 |
} {0.0} |
| 1960 |
|
| 1961 |
do_catchsql_test 61.4.3 { |
| 1962 |
SELECT |
| 1963 |
sum(a) OVER ( ORDER BY a ) |
| 1964 |
FROM t1 |
| 1965 |
ORDER BY (SELECT sum(a) FROM t2) |
| 1966 |
} {1 {misuse of aggregate: sum()}} |
| 1967 |
do_execsql_test 61.4.4 { |
| 1968 |
SELECT |
| 1969 |
sum(a) OVER ( ORDER BY a ) |
| 1970 |
FROM t1 |
| 1971 |
ORDER BY (SELECT sum(y) FROM t2) |
| 1972 |
} |
| 1973 |
|
| 1974 |
|
| 1975 |
#------------------------------------------------------------------------- |
| 1976 |
reset_db |
| 1977 |
do_execsql_test 62.1 { |
| 1978 |
CREATE TABLE t1(a VARCHAR(20), b FLOAT); |
| 1979 |
INSERT INTO t1 VALUES('1',10.0); |
| 1980 |
} |
| 1981 |
|
| 1982 |
do_execsql_test 62.2 { |
| 1983 |
SELECT * FROM ( |
| 1984 |
SELECT sum(b) OVER() AS c FROM t1 |
| 1985 |
UNION |
| 1986 |
SELECT b AS c FROM t1 |
| 1987 |
) WHERE c>10; |
| 1988 |
} |
| 1989 |
|
| 1990 |
do_execsql_test 62.3 { |
| 1991 |
INSERT INTO t1 VALUES('2',5.0); |
| 1992 |
INSERT INTO t1 VALUES('3',15.0); |
| 1993 |
} |
| 1994 |
|
| 1995 |
do_execsql_test 62.4 { |
| 1996 |
SELECT * FROM ( |
| 1997 |
SELECT sum(b) OVER() AS c FROM t1 |
| 1998 |
UNION |
| 1999 |
SELECT b AS c FROM t1 |
| 2000 |
) WHERE c>10; |
| 2001 |
} {15.0 30.0} |
| 2002 |
|
| 2003 |
#------------------------------------------------------------------------- |
| 2004 |
reset_db |
| 2005 |
do_execsql_test 63.1 { |
| 2006 |
CREATE TABLE t1(b, x); |
| 2007 |
CREATE TABLE t2(c, d); |
| 2008 |
CREATE TABLE t3(e, f); |
| 2009 |
} |
| 2010 |
|
| 2011 |
do_execsql_test 63.2 { |
| 2012 |
SELECT max(b) OVER( |
| 2013 |
ORDER BY SUM( |
| 2014 |
(SELECT c FROM t2 UNION SELECT x ORDER BY c) |
| 2015 |
) |
| 2016 |
) FROM t1; |
| 2017 |
} {{}} |
| 2018 |
|
| 2019 |
do_execsql_test 63.3 { |
| 2020 |
SELECT sum(b) over( |
| 2021 |
ORDER BY ( |
| 2022 |
SELECT max(b) OVER( |
| 2023 |
ORDER BY sum( |
| 2024 |
(SELECT x AS c UNION SELECT 1234 ORDER BY c) |
| 2025 |
) |
| 2026 |
) AS e |
| 2027 |
ORDER BY e |
| 2028 |
) |
| 2029 |
) |
| 2030 |
FROM t1; |
| 2031 |
} {{}} |
| 2032 |
|
| 2033 |
#------------------------------------------------------------------------- |
| 2034 |
reset_db |
| 2035 |
do_execsql_test 64.1 { |
| 2036 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b); |
| 2037 |
INSERT INTO t1 VALUES(1, 'abcd'); |
| 2038 |
INSERT INTO t1 VALUES(2, 'BCDE'); |
| 2039 |
INSERT INTO t1 VALUES(3, 'cdef'); |
| 2040 |
INSERT INTO t1 VALUES(4, 'DEFG'); |
| 2041 |
} |
| 2042 |
|
| 2043 |
do_execsql_test 64.2 { |
| 2044 |
SELECT rowid, max(b COLLATE nocase)||'' |
| 2045 |
FROM t1 |
| 2046 |
GROUP BY rowid |
| 2047 |
ORDER BY max(b COLLATE nocase)||''; |
| 2048 |
} {1 abcd 2 BCDE 3 cdef 4 DEFG} |
| 2049 |
|
| 2050 |
do_execsql_test 64.3 { |
| 2051 |
SELECT count() OVER (), rowid, max(b COLLATE nocase)||'' |
| 2052 |
FROM t1 |
| 2053 |
GROUP BY rowid |
| 2054 |
ORDER BY max(b COLLATE nocase)||''; |
| 2055 |
} {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG} |
| 2056 |
|
| 2057 |
do_execsql_test 64.4 { |
| 2058 |
SELECT count() OVER (), rowid, max(b COLLATE nocase) |
| 2059 |
FROM t1 |
| 2060 |
GROUP BY rowid |
| 2061 |
ORDER BY max(b COLLATE nocase); |
| 2062 |
} {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG} |
| 2063 |
|
| 2064 |
#------------------------------------------------------------------------- |
| 2065 |
reset_db |
| 2066 |
do_execsql_test 65.1 { |
| 2067 |
CREATE TABLE t1(c1); |
| 2068 |
INSERT INTO t1 VALUES('abcd'); |
| 2069 |
} |
| 2070 |
do_execsql_test 65.2 { |
| 2071 |
SELECT max(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1; |
| 2072 |
} {1} |
| 2073 |
|
| 2074 |
do_execsql_test 65.3 { |
| 2075 |
SELECT |
| 2076 |
count() OVER (), |
| 2077 |
group_concat(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1; |
| 2078 |
} {1 1} |
| 2079 |
|
| 2080 |
do_execsql_test 65.4 { |
| 2081 |
SELECT COUNT() OVER () LIKE lead(102030) OVER( |
| 2082 |
ORDER BY sum('abcdef' COLLATE nocase) IN (SELECT 54321) |
| 2083 |
) |
| 2084 |
FROM t1; |
| 2085 |
} {{}} |
| 2086 |
|
| 2087 |
#------------------------------------------------------------------------- |
| 2088 |
reset_db |
| 2089 |
|
| 2090 |
do_execsql_test 66.1 { |
| 2091 |
CREATE TABLE t1(a INTEGER); |
| 2092 |
INSERT INTO t1 VALUES(3578824042033200656); |
| 2093 |
INSERT INTO t1 VALUES(3029012920382354029); |
| 2094 |
} |
| 2095 |
|
| 2096 |
foreach {tn spec} { |
| 2097 |
1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" |
| 2098 |
2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING" |
| 2099 |
3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING" |
| 2100 |
4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" |
| 2101 |
5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" |
| 2102 |
6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING" |
| 2103 |
} { |
| 2104 |
do_execsql_test 66.2.$tn " |
| 2105 |
SELECT total(a) OVER ( $spec ) FROM t1 ORDER BY a |
| 2106 |
" { |
| 2107 |
3.02901292038235e+18 3.5788240420332e+18 |
| 2108 |
} |
| 2109 |
} |
| 2110 |
|
| 2111 |
|
| 2112 |
do_execsql_test 66.3 { |
| 2113 |
CREATE TABLE t2(a INTEGER); |
| 2114 |
INSERT INTO t2 VALUES(45); |
| 2115 |
INSERT INTO t2 VALUES(30); |
| 2116 |
} |
| 2117 |
|
| 2118 |
foreach {tn spec res} { |
| 2119 |
1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0} |
| 2120 |
2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING" {0.0 0.0} |
| 2121 |
3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING" {0.0 0.0} |
| 2122 |
4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0} |
| 2123 |
5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0} |
| 2124 |
6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING" {0.0 0.0} |
| 2125 |
} { |
| 2126 |
do_execsql_test 66.2.$tn " |
| 2127 |
SELECT total(a) OVER ( $spec ) FROM t2 ORDER BY a |
| 2128 |
" $res |
| 2129 |
} |
| 2130 |
|
| 2131 |
|
| 2132 |
#------------------------------------------------------------------------- |
| 2133 |
reset_db |
| 2134 |
do_execsql_test 67.0 { |
| 2135 |
CREATE TABLE t1(a, b, c); |
| 2136 |
CREATE TABLE t2(a, b, c); |
| 2137 |
} |
| 2138 |
|
| 2139 |
do_catchsql_test 67.1 { |
| 2140 |
SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY ( |
| 2141 |
SELECT nth_value(a,2) OVER w1 |
| 2142 |
WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) ) |
| 2143 |
) |
| 2144 |
} {1 {no such table: v1}} |
| 2145 |
|
| 2146 |
do_catchsql_test 67.2 { |
| 2147 |
SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY ( |
| 2148 |
SELECT nth_value(a,2) OVER w1 |
| 2149 |
WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM t2)) ) |
| 2150 |
) |
| 2151 |
} {1 {1st ORDER BY term does not match any column in the result set}} |
| 2152 |
|
| 2153 |
# 2021-05-07 |
| 2154 |
# Do not allow aggregate functions in the ORDER BY clause even if |
| 2155 |
# there are window functions in the result set. |
| 2156 |
# Forum: /forumpost/540fdfef77 |
| 2157 |
# |
| 2158 |
reset_db |
| 2159 |
do_catchsql_test 68.0 { |
| 2160 |
CREATE TABLE t1(a,b); |
| 2161 |
INSERT INTO t1(a,b) VALUES(0,0),(1,1),(2,4),(3,9),(4,99); |
| 2162 |
SELECT rowid, a, b, sum(a)OVER() FROM t1 ORDER BY count(b); |
| 2163 |
} {1 {misuse of aggregate: count()}} |
| 2164 |
|
| 2165 |
# 2021-05-22 |
| 2166 |
# Forum https://sqlite.org/forum/forumpost/7e484e225c |
| 2167 |
# |
| 2168 |
reset_db |
| 2169 |
do_catchsql_test 69.0 { |
| 2170 |
CREATE TABLE t1(a,b); |
| 2171 |
CREATE INDEX t1ba ON t1(b,a); |
| 2172 |
SELECT * FROM t1 WHERE b = (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a)); |
| 2173 |
} {1 {misuse of aggregate: sum()}} |
| 2174 |
do_catchsql_test 69.1 { |
| 2175 |
SELECT * FROM t1 WHERE b >= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a)); |
| 2176 |
} {1 {misuse of aggregate: sum()}} |
| 2177 |
do_catchsql_test 69.2 { |
| 2178 |
SELECT * FROM t1 WHERE b <= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a)); |
| 2179 |
} {1 {misuse of aggregate: sum()}} |
| 2180 |
|
| 2181 |
# 2021-06-23 |
| 2182 |
# Forum https://sqlite.org/forum/forumpost/31e0432608 |
| 2183 |
# |
| 2184 |
reset_db |
| 2185 |
do_execsql_test 70.0 { |
| 2186 |
CREATE TABLE t1(a); |
| 2187 |
} |
| 2188 |
do_execsql_test 70.1 { |
| 2189 |
SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY 2)) AS ca0 FROM t1 ORDER BY ca0; |
| 2190 |
} |
| 2191 |
do_execsql_test 70.2 { |
| 2192 |
SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY likely(2))) AS ca0 FROM t1 ORDER BY ca0; |
| 2193 |
} |
| 2194 |
|
| 2195 |
# 2021-11-07 |
| 2196 |
# Bug report from Wang Ke |
| 2197 |
# https://sqlite.org/forum/forumpost/9ba4f60ff8 |
| 2198 |
reset_db |
| 2199 |
do_catchsql_test 71.0 { |
| 2200 |
CREATE TABLE t0(a); |
| 2201 |
SELECT a FROM t0, (SELECT a AS b FROM t0) |
| 2202 |
WHERE (a,1)=(SELECT 2,2 UNION SELECT sum(b),max(b) OVER(ORDER BY b) ORDER BY 2) |
| 2203 |
AND b=4 |
| 2204 |
ORDER BY b; |
| 2205 |
} {/1 {.*}/} |
| 2206 |
|
| 2207 |
do_execsql_test 72.1 { |
| 2208 |
CREATE TABLE dual(dummy); INSERT INTO dual VALUES('X'); |
| 2209 |
CREATE VIEW v1(x,y) AS SELECT RANK() OVER (PARTITION BY 0), SUM(0) FROM dual; |
| 2210 |
SELECT * FROM v1 WHERE true; |
| 2211 |
} {1 0} |
| 2212 |
|
| 2213 |
#------------------------------------------------------------------------- |
| 2214 |
reset_db |
| 2215 |
|
| 2216 |
do_execsql_test 72.0 { |
| 2217 |
CREATE TABLE t0(c0); |
| 2218 |
INSERT INTO t0(c0) VALUES (0); |
| 2219 |
CREATE VIEW v0(c0) AS SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0; |
| 2220 |
} |
| 2221 |
do_execsql_test 72.1 { |
| 2222 |
SELECT COUNT(*) FROM ( |
| 2223 |
SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0 |
| 2224 |
) |
| 2225 |
WHERE ('1' IS NOT ('abcde' NOTNULL)); |
| 2226 |
} {1} |
| 2227 |
|
| 2228 |
# 2023-03-28 https://sqlite.org/forum/forumpost/dc3b92cfa0 (Song Liu) |
| 2229 |
# |
| 2230 |
reset_db |
| 2231 |
do_execsql_test 73.0 { |
| 2232 |
CREATE TABLE t1(a INT); |
| 2233 |
INSERT INTO t1(a) VALUES(1),(2),(4); |
| 2234 |
CREATE VIEW t2(b,c) AS SELECT * FROM t1 JOIN t1 A ORDER BY sum(0) OVER(PARTITION BY 0); |
| 2235 |
CREATE TRIGGER x1 INSTEAD OF UPDATE ON t2 BEGIN SELECT true; END; |
| 2236 |
} |
| 2237 |
do_execsql_test 73.1 { |
| 2238 |
SELECT * FROM t2; |
| 2239 |
} {1 1 1 2 1 4 2 1 2 2 2 4 4 1 4 2 4 4} |
| 2240 |
do_execsql_test 73.2 { |
| 2241 |
UPDATE t2 SET c=99 WHERE b=4 RETURNING *; |
| 2242 |
} {4 99 4 99 4 99} |
| 2243 |
do_execsql_test 73.3 { |
| 2244 |
SELECT *, nth_value(15,2) OVER() FROM t2, t1 WHERE b=4; |
| 2245 |
} { |
| 2246 |
4 1 1 15 |
| 2247 |
4 2 1 15 |
| 2248 |
4 4 1 15 |
| 2249 |
4 1 2 15 |
| 2250 |
4 2 2 15 |
| 2251 |
4 4 2 15 |
| 2252 |
4 1 4 15 |
| 2253 |
4 2 4 15 |
| 2254 |
4 4 4 15 |
| 2255 |
} |
| 2256 |
do_execsql_test 73.4 { |
| 2257 |
UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *; |
| 2258 |
} { |
| 2259 |
4 15 |
| 2260 |
4 15 |
| 2261 |
4 15 |
| 2262 |
4 15 |
| 2263 |
4 15 |
| 2264 |
4 15 |
| 2265 |
4 15 |
| 2266 |
4 15 |
| 2267 |
4 15 |
| 2268 |
} |
| 2269 |
do_execsql_test 73.5 { |
| 2270 |
DROP TRIGGER x1; |
| 2271 |
} |
| 2272 |
do_catchsql_test 73.6 { |
| 2273 |
UPDATE t2 SET c=99 WHERE b=4 RETURNING *; |
| 2274 |
} {1 {cannot modify t2 because it is a view}} |
| 2275 |
do_catchsql_test 73.7 { |
| 2276 |
UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *; |
| 2277 |
} {1 {cannot modify t2 because it is a view}} |
| 2278 |
|
| 2279 |
# 2023-03-28 https://sqlite.org/forum/forumpost/bad532820c |
| 2280 |
# |
| 2281 |
reset_db |
| 2282 |
do_execsql_test 74.0 { |
| 2283 |
CREATE TABLE t1 (a INT, b INT); |
| 2284 |
CREATE TABLE t2 (c INT, d INT); |
| 2285 |
CREATE INDEX idx ON t1(abs(a)); |
| 2286 |
INSERT INTO t1 VALUES(1,2),(3,4); |
| 2287 |
INSERT INTO t2 VALUES(5,6),(7,8); |
| 2288 |
} |
| 2289 |
do_execsql_test 74.1 { |
| 2290 |
SELECT ( |
| 2291 |
SELECT count( a ) FROM t2 LIMIT 1 |
| 2292 |
) |
| 2293 |
FROM t1; |
| 2294 |
} {2} ;# Verified using PG 14.2 |
| 2295 |
do_execsql_test 74.2 { |
| 2296 |
SELECT ( |
| 2297 |
SELECT count( a+c ) FROM t2 LIMIT 1 |
| 2298 |
) |
| 2299 |
FROM t1; |
| 2300 |
} {2 2} ;# verified on PG 14.2. Crashes PG 9.6! |
| 2301 |
do_execsql_test 74.3 { |
| 2302 |
SELECT ( |
| 2303 |
SELECT count( ( SELECT(sum(0) OVER(ORDER BY c, abs(a))) ) ) |
| 2304 |
FROM t2 GROUP BY c LIMIT 1 |
| 2305 |
) |
| 2306 |
FROM t1; |
| 2307 |
} {1 1} ;# verified on PG 14.2 |
| 2308 |
do_execsql_test 74.4 { |
| 2309 |
/* Original test case reported in https://sqlite.org/forum/forumpost/bad532820c |
| 2310 |
CREATE TABLE v0 (c1); |
| 2311 |
CREATE INDEX i ON v0 (c1, c1=1); |
| 2312 |
SELECT 0 FROM v0 AS a1 |
| 2313 |
WHERE (SELECT count((SELECT(sum(0) OVER(PARTITION BY(c1), (a1.c1=1) )))) |
| 2314 |
FROM v0 |
| 2315 |
GROUP BY hex(0)) |
| 2316 |
AND a1.c1=0; |
| 2317 |
} {} |
| 2318 |
|
| 2319 |
# 2023-04-11 https://sqlite.org/forum/forumpost/6c5678e3da |
| 2320 |
# An ALWAYS() turns out to be sometimes false. |
| 2321 |
# |
| 2322 |
do_execsql_test 75.0 { |
| 2323 |
DROP TABLE t1; |
| 2324 |
CREATE TABLE t1(a INT, b INT); |
| 2325 |
CREATE INDEX t1x ON t1(a+b); |
| 2326 |
} |
| 2327 |
do_catchsql_test 75.1 { |
| 2328 |
SELECT count((SELECT count(a0.a+a0.b) ORDER BY sum(0) OVER (PARTITION BY 0))) |
| 2329 |
FROM t1 AS a0 JOIN t1 AS a1 |
| 2330 |
GROUP BY a1.a; |
| 2331 |
} {1 {misuse of aggregate: count()}} |
| 2332 |
|
| 2333 |
# 2023-04-13 https://sqlite.org/forum/forumpost/0d48347967 |
| 2334 |
reset_db |
| 2335 |
do_execsql_test 76.0 { |
| 2336 |
CREATE TABLE t1(a INT, b INT); |
| 2337 |
INSERT INTO t1(a,b) VALUES (111,222),(111,223),(118,229); |
| 2338 |
CREATE INDEX t1a ON t1(a); |
| 2339 |
CREATE TABLE t2(x INT); |
| 2340 |
INSERT INTO t2 VALUES (333),(444),(555); |
| 2341 |
} |
| 2342 |
do_execsql_test 76.1 { |
| 2343 |
SELECT c, (SELECT c + sum(1) OVER ()) AS "res" |
| 2344 |
FROM t2 LEFT JOIN (SELECT +a AS c FROM t1) AS v1 ON true |
| 2345 |
GROUP BY c |
| 2346 |
ORDER by c; |
| 2347 |
} {111 112 118 119} |
| 2348 |
# ^^^^^^^^^^^^^^^^^-- results verified against PG 14.2 |
| 2349 |
|
| 2350 |
do_execsql_test 76.2 { |
| 2351 |
CREATE TABLE t3(x); |
| 2352 |
CREATE TABLE t4(y); |
| 2353 |
INSERT INTO t3 VALUES(100), (200), (400); |
| 2354 |
INSERT INTO t4 VALUES(100), (300), (400); |
| 2355 |
} |
| 2356 |
do_execsql_test 76.3 { |
| 2357 |
SELECT (SELECT y+sum(0) OVER ()) FROM t3 LEFT JOIN t4 ON x=y; |
| 2358 |
} {100 {} 400} |
| 2359 |
do_execsql_test 76.4 { |
| 2360 |
SELECT (SELECT y+sum(0) OVER ()) FROM t3 LEFT JOIN t4 ON x=y GROUP BY x; |
| 2361 |
} {100 {} 400} |
| 2362 |
do_execsql_test 76.5 { |
| 2363 |
SELECT (SELECT max(y)+sum(0) OVER ()) FROM t3 LEFT JOIN t4 ON x=y GROUP BY x; |
| 2364 |
} {100 {} 400} |
| 2365 |
|
| 2366 |
# 2023-05-23 https://sqlite.org/forum/forumpost/fbfe330a20 |
| 2367 |
# |
| 2368 |
reset_db |
| 2369 |
do_execsql_test 77.1 { |
| 2370 |
CREATE TABLE t1(x INT); |
| 2371 |
CREATE INDEX t1x ON t1(likely(x)); |
| 2372 |
INSERT INTO t1 VALUES(1),(2),(4),(8); |
| 2373 |
} |
| 2374 |
do_execsql_test 77.2 { |
| 2375 |
SELECT max(~likely(x)) FILTER (WHERE true) FROM t1 INDEXED BY t1x GROUP BY x; |
| 2376 |
} {-2 -3 -5 -9} |
| 2377 |
|
| 2378 |
# 2024-05-23 https://sqlite.org/forum/forumpost/bf8f43aa522c2299 |
| 2379 |
# |
| 2380 |
# A bug in group_concat() when used as a window function, reported |
| 2381 |
# just hours after the 3.46.0 release, though first appearing |
| 2382 |
# in 3.28.0. |
| 2383 |
# |
| 2384 |
# When used as a window function, a group_concat() was not |
| 2385 |
# correctly distinguishing between NULL and empty-string for |
| 2386 |
# its return value. |
| 2387 |
# |
| 2388 |
do_execsql_test 78.1 { |
| 2389 |
SELECT quote(group_concat(x) OVER ()) FROM (SELECT '' AS x); |
| 2390 |
} '' |
| 2391 |
do_execsql_test 78.2 { |
| 2392 |
SELECT quote(group_concat(x) OVER ( |
| 2393 |
ORDER BY y RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING |
| 2394 |
)) FROM (SELECT 'abc' AS x, 1 AS y); |
| 2395 |
} NULL |
| 2396 |
|
| 2397 |
#------------------------------------------------------------------------- |
| 2398 |
# Test that the following queries do not run for a very long time. |
| 2399 |
# |
| 2400 |
# https://sqlite.org/forum/forumpost/b1993c858f |
| 2401 |
# |
| 2402 |
do_execsql_test 79.0 { |
| 2403 |
CREATE TABLE t0 (c0 INTEGER ); |
| 2404 |
INSERT INTO t0 VALUES(1); |
| 2405 |
INSERT INTO t0 VALUES(2); |
| 2406 |
INSERT INTO t0 VALUES(3); |
| 2407 |
} |
| 2408 |
|
| 2409 |
do_execsql_test 79.1 { |
| 2410 |
SELECT COUNT(*) |
| 2411 |
OVER ( ROWS BETWEEN 0 FOLLOWING AND 100 FOLLOWING) |
| 2412 |
FROM t0; |
| 2413 |
} {3 2 1} |
| 2414 |
|
| 2415 |
do_execsql_test 79.2 { |
| 2416 |
SELECT COUNT(*) |
| 2417 |
OVER ( ORDER BY c0 RANGE BETWEEN 0 FOLLOWING AND 10_000_000_000 FOLLOWING ) |
| 2418 |
FROM t0; |
| 2419 |
} {3 2 1} |
| 2420 |
|
| 2421 |
do_execsql_test 79.3 { |
| 2422 |
SELECT sum(c0), COUNT(*) |
| 2423 |
OVER ( ORDER BY c0 RANGE BETWEEN 0 FOLLOWING AND 10_000_000_000 FOLLOWING ) |
| 2424 |
FROM t0; |
| 2425 |
} {6 1} |
| 2426 |
|
| 2427 |
finish_test |