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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
# name: test/sql/topn/top_n_materialization.test
# description: Test Top N Optimization
# group: [topn]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE test AS SELECT i, i + 1 AS j, i + 2 AS k, -i AS l FROM range(10) t(i);
statement ok
SET explain_output='optimized_only'
# Top N optimization
# this gets turned into a row-id join
query II
explain SELECT * FROM test ORDER BY j DESC LIMIT 2;
----
logical_opt <REGEX>:.*COMPARISON_JOIN.*
query IIII
SELECT * FROM test ORDER BY j DESC LIMIT 2;
----
9 10 11 -9
8 9 10 -8
query II
explain SELECT * FROM test ORDER BY j, i LIMIT 2;
----
logical_opt <REGEX>:.*COMPARISON_JOIN.*
query IIII
SELECT * FROM test ORDER BY j, i LIMIT 2;
----
0 1 2 0
1 2 3 -1
# this does not, we cannot remove any columns by turning it into a row-id join
query II
explain SELECT i FROM test ORDER BY i LIMIT 2;
----
logical_opt <!REGEX>:.*COMPARISON_JOIN.*
# we cannot do this with volatile expressions
query II
explain SELECT * FROM (SELECT i + random() AS i, j, k, l FROM test) ORDER BY i LIMIT 2;
----
logical_opt <!REGEX>:.*COMPARISON_JOIN.*
# top-n with expressions
query IIII
SELECT * FROM (SELECT -i i, -j j, -k k, -l l FROM test) ORDER BY -j DESC LIMIT 2
----
-9 -10 -11 9
-8 -9 -10 8
# multiple layers
query IIII
SELECT * FROM (SELECT 100 + i i, 1000 + j j, 10000 + k k, 100000 + l l FROM (SELECT -i i, -j j, -k k, -l l FROM test)) ORDER BY j DESC LIMIT 2
----
100 999 9998 100000
99 998 9997 100001
# limit + offset
query II
explain SELECT * FROM test LIMIT 2 OFFSET 2;
----
logical_opt <REGEX>:.*COMPARISON_JOIN.*
query IIII
SELECT * FROM test LIMIT 2 OFFSET 2;
----
2 3 4 -2
3 4 5 -3
# sample
query II
explain SELECT * FROM test USING SAMPLE 2 ROWS
----
logical_opt <REGEX>:.*COMPARISON_JOIN.*
# we can only use joins when we are sampling rows
query II
explain SELECT * FROM test USING SAMPLE 10%
----
logical_opt <!REGEX>:.*COMPARISON_JOIN.*
# order on expression
query IIII
SELECT * FROM test ORDER BY -j DESC LIMIT 2
----
0 1 2 0
1 2 3 -1
# projection in subquery
query IIII
SELECT * FROM (SELECT -i i, -j j, -k k, -l l FROM test) ORDER BY -j DESC LIMIT 2
----
-9 -10 -11 9
-8 -9 -10 8
# filter after top-n
query IIII
SELECT * FROM (
SELECT * FROM test ORDER BY j DESC LIMIT 2
) WHERE i=8
----
8 9 10 -8
query I
SELECT l FROM (
SELECT * FROM test ORDER BY j DESC LIMIT 2
) WHERE k=10
----
-8
# now with varchar columns
statement ok
CREATE OR REPLACE TABLE test AS SELECT i, printf('%02d', i + 1) AS j, printf('%02d', i + 2) AS k, -i AS l FROM range(10) t(i);
query IIII
SELECT * FROM test ORDER BY j DESC LIMIT 2;
----
9 10 11 -9
8 09 10 -8
query IIII
SELECT j, k, l, i FROM test WHERE i > 5 ORDER BY j DESC LIMIT 2;
----
10 11 -9 9
09 10 -8 8
# test late materialization setting
statement ok
SET late_materialization_max_rows=0
query II
explain SELECT * FROM test ORDER BY j DESC LIMIT 2;
----
logical_opt <!REGEX>:.*COMPARISON_JOIN.*
statement ok
RESET late_materialization_max_rows
query II
explain SELECT * FROM test ORDER BY j DESC LIMIT 2;
----
logical_opt <REGEX>:.*COMPARISON_JOIN.*
|
