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
95
|
# name: test/sql/limit/test_parallel_limit_nested_null.test_slow
# description: Test parallel limit execution with nested types and nulls
# group: [limit]
statement ok
PRAGMA enable_verification
statement ok
PRAGMA threads=8
statement ok
CREATE TABLE other_table AS SELECT 337 i UNION ALL SELECT 94824 UNION ALL SELECT 177979 UNION ALL SELECT 477979;
foreach table_type TABLE VIEW
statement ok
CREATE ${table_type} integers AS SELECT i, case when i%2=0 then [i, NULL, i + 2] else [NULL, i + 1, NULL] end AS k, {'i': case when i%2=0 then null else i end} AS l FROM range(1000000) tbl(i);
query II
SELECT k, l FROM integers LIMIT 5
----
[0, NULL, 2] {'i': NULL}
[NULL, 2, NULL] {'i': 1}
[2, NULL, 4] {'i': NULL}
[NULL, 4, NULL] {'i': 3}
[4, NULL, 6] {'i': NULL}
query II
SELECT k, l FROM integers WHERE i>197832 OR i=334 LIMIT 5
----
[334, NULL, 336] {'i': NULL}
[NULL, 197834, NULL] {'i': 197833}
[197834, NULL, 197836] {'i': NULL}
[NULL, 197836, NULL] {'i': 197835}
[197836, NULL, 197838] {'i': NULL}
query II
SELECT k, l FROM integers WHERE i>197832 LIMIT 5
----
[NULL, 197834, NULL] {'i': 197833}
[197834, NULL, 197836] {'i': NULL}
[NULL, 197836, NULL] {'i': 197835}
[197836, NULL, 197838] {'i': NULL}
[NULL, 197838, NULL] {'i': 197837}
query II
SELECT k, l FROM integers WHERE i>497832 LIMIT 5
----
[NULL, 497834, NULL] {'i': 497833}
[497834, NULL, 497836] {'i': NULL}
[NULL, 497836, NULL] {'i': 497835}
[497836, NULL, 497838] {'i': NULL}
[NULL, 497838, NULL] {'i': 497837}
# large offset
query II
SELECT k, l FROM integers WHERE i>497832 LIMIT 5 OFFSET 100000;
----
[NULL, 597834, NULL] {'i': 597833}
[597834, NULL, 597836] {'i': NULL}
[NULL, 597836, NULL] {'i': 597835}
[597836, NULL, 597838] {'i': NULL}
[NULL, 597838, NULL] {'i': 597837}
query II
SELECT k, l FROM integers WHERE i>497832 LIMIT 5 OFFSET 1000000;
----
# IN-clause (semi join)
query II sort
SELECT k, l FROM integers WHERE i IN (SELECT * FROM other_table)
----
[94824, NULL, 94826] {'i': NULL}
[NULL, 177980, NULL] {'i': 177979}
[NULL, 338, NULL] {'i': 337}
[NULL, 477980, NULL] {'i': 477979}
query II
(SELECT k, l FROM integers WHERE i>197832 LIMIT 5) UNION ALL (SELECT k, l FROM integers WHERE i>497832 LIMIT 5)
----
[NULL, 197834, NULL] {'i': 197833}
[197834, NULL, 197836] {'i': NULL}
[NULL, 197836, NULL] {'i': 197835}
[197836, NULL, 197838] {'i': NULL}
[NULL, 197838, NULL] {'i': 197837}
[NULL, 497834, NULL] {'i': 497833}
[497834, NULL, 497836] {'i': NULL}
[NULL, 497836, NULL] {'i': 497835}
[497836, NULL, 497838] {'i': NULL}
[NULL, 497838, NULL] {'i': 497837}
statement ok
DROP ${table_type} integers
endloop
|
