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
|
# name: test/optimizer/pullup_filters.test
# description: Test Filters Pull Up
# group: [optimizer]
statement ok
PRAGMA explain_output = 'PHYSICAL_ONLY'
statement ok
SET disabled_optimizers TO 'common_subplan'
statement ok
CREATE TABLE vals1 AS SELECT i AS i, i AS j FROM range(0, 11, 1) t1(i)
statement ok
CREATE TABLE vals2(k BIGINT, l BIGINT)
statement ok
INSERT INTO vals2 SELECT * FROM vals1
## INNER JOIN: pull up a single filter in cross product from LHS
query II
EXPLAIN SELECT * FROM (SELECT * FROM vals1, vals2 WHERE i=5) tbl1, (SELECT * FROM vals1, vals2) tbl2 WHERE tbl1.i=tbl2.i
----
physical_plan <REGEX>:.*=5.*=5.*
## INNER JOIN: pull up a single filter in cross product from RHS
query II
EXPLAIN SELECT * FROM (SELECT * FROM vals1, vals2) tbl1, (SELECT * FROM vals1, vals2 WHERE i=5) tbl2 WHERE tbl1.i=tbl2.i
----
physical_plan <REGEX>:.*=5.*=5.*
## INNER JOIN: pull up two filters in cross product from LHS
query II
EXPLAIN SELECT * FROM (SELECT * FROM vals1, vals2 WHERE i=5 AND k=3) tbl1, (SELECT * FROM vals1, vals2) tbl2 WHERE tbl1.i=tbl2.i AND tbl1.k=tbl2.k
----
physical_plan <REGEX>:(.*=5.*=3.*=5.*=3.*|.*=3.*=5.*=3.*=5.*)
## INNER JOIN: pull up two filters in cross product from RHS
query II
EXPLAIN SELECT * FROM (SELECT * FROM vals1, vals2) tbl1, (SELECT * FROM vals1, vals2 WHERE i=5 AND k=3) tbl2 WHERE tbl1.i=tbl2.i AND tbl1.k=tbl2.k
----
physical_plan <REGEX>:(.*=5.*=3.*=5.*=3.*|.*=3.*=5.*=3.*=5.*)
#### LEFT JOIN: pull up a single filter from LHS ####
query II
EXPLAIN SELECT * FROM (SELECT * FROM vals1 WHERE i=5) tbl1 LEFT JOIN (SELECT * FROM vals1) AS tbl2 ON tbl1.i=tbl2.i
----
physical_plan <REGEX>:.*=5.*=5.*
#### LEFT JOIN: filters should not pull up from RHS ####
query II
EXPLAIN SELECT * FROM (SELECT * FROM vals1) tbl1 LEFT JOIN (SELECT * FROM vals1 WHERE i=5) AS tbl2 ON tbl1.i=tbl2.i
----
physical_plan <!REGEX>:.*=5.*=5.*
#### LEFT JOIN: pull up two filters from cross product in the LHS ####
query II
EXPLAIN SELECT * FROM (SELECT * FROM vals1, vals2 WHERE i=5 AND k=10) tbl1 LEFT OUTER JOIN (SELECT * FROM vals1, vals2) tbl2 ON tbl1.i=tbl2.i AND tbl1.k=tbl2.k
----
physical_plan <REGEX>:((.*=5.*=10.*=5.*=10.*)|(.*=10.*=5.*=10.*=5.*))
## INNER JOIN: pull up filter from LHS ####
query II
EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM vals1, vals2 WHERE i=5) tbl1, (SELECT * FROM vals1, vals2) tbl2 WHERE tbl1.i=tbl1.k AND tbl1.i=tbl2.k AND tbl1.i=tbl2.i
----
physical_plan <REGEX>:.*=5.*=5.*=5.*=5.*
## INNER JOIN: pull up filters from RHS ####
query II
EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM vals1, vals2) tbl1, (SELECT * FROM vals1, vals2 WHERE i=5) tbl2 WHERE tbl1.i=tbl1.k AND tbl1.i=tbl2.k AND tbl1.i=tbl2.i
----
physical_plan <REGEX>:.*=5.*=5.*=5.*=5.*
## INTERSECT: pull up filters from LHS
query II
EXPLAIN SELECT * FROM (SELECT * FROM vals1, vals2 WHERE i=3 AND k=5 INTERSECT SELECT * FROM vals1, vals2) tbl1;
----
physical_plan <REGEX>:((.*=3.*=5.*=3.*=5.*)|(.*=5.*=3.*=5.*=3.*))
## INTERSECT: pull up filters from RHS
query II
EXPLAIN SELECT * FROM (SELECT * FROM vals1, vals2 INTERSECT SELECT * FROM vals1, vals2 WHERE i=3 AND k=5) tbl1;
----
physical_plan <REGEX>:((.*=3.*=5.*=3.*=5.*)|(.*=5.*=3.*=5.*=3.*))
statement ok
create table orders as select range o_orderkey from range(10)
statement ok
create table lineitem as select range % 10 l_orderkey from range(100)
# down here we test that we can pull filters out of explicitly joined relations (using JOIN syntax rather than WHERE)
# even though we are explicitly joining on l_orderkey both times,
# we can derive that o1.o_orderkey = o2.o_orderkey
# once we've derived this, the join order optimizer finds it should join o1 with o2 before joining with lineitem
# rather than joining lineitem with o1 and o2 directly
# so we should see lineitem first in the regex, and then 2x orders (deeper because joined first)
statement ok
PRAGMA explain_output='OPTIMIZED_ONLY'
query II
explain
select count(*)
from lineitem l
join orders o1
on (l.l_orderkey = o1.o_orderkey)
join orders o2
on (l.l_orderkey = o2.o_orderkey)
----
logical_opt <REGEX>:.*lineitem.*orders.*orders.*
# if we disable the FilterPullup, we get the original join order again:
# orders first (joined last), then lineitem, then orders
statement ok
set disabled_optimizers to 'filter_pullup'
query II
explain
select count(*)
from lineitem l
join orders o1
on (l.l_orderkey = o1.o_orderkey)
join orders o2
on (l.l_orderkey = o2.o_orderkey)
----
logical_opt <REGEX>:.*orders.*lineitem.*orders.*
|
