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
|
/* ignore runonopt plan diff - extra PhysicalFinalizing-SuppressOuts */
USE plato;
PRAGMA config.flags('OptimizerFlags', 'ExtractOrPredicatesOverEquiJoin');
pragma yt.DisableFuseOperations;
pragma yt.DisableOptimizers="HorizontalJoin,MultiHorizontalJoin,OutHorizontalJoin";
$a = AsList(
AsStruct(23 AS key, 3 AS subkey, Nothing(String?) AS value),
AsStruct(37 AS key, 5 AS subkey, Nothing(String?) AS value),
AsStruct(75 AS key, 1 AS subkey, Nothing(String?) AS value),
AsStruct(150 AS key, 1 AS subkey, Just("AAA") AS value),
AsStruct(150 AS key, 3 AS subkey, Just("III") AS value),
AsStruct(150 AS key, 8 AS subkey, Just("ZZZ") AS value),
AsStruct(200 AS key, 7 AS subkey, Nothing(String?) AS value),
AsStruct(527 AS key, 4 AS subkey, Nothing(String?) AS value),
AsStruct(761 AS key, 6 AS subkey, Nothing(String?) AS value),
AsStruct(911 AS key, 2 AS subkey, Just("KKK") AS value),
);
$b = AsList(
AsStruct(1 AS key, 1001 AS subkey, Nothing(String?) AS value),
AsStruct(150 AS key, 150 AS subkey, Just("AAB") AS value),
AsStruct(3 AS key, 3003 AS subkey, Nothing(String?) AS value),
AsStruct(150 AS key, 200 AS subkey, Just("AAD") AS value),
AsStruct(911 AS key, 5005 AS subkey, Just("AAE") AS value),
);
INSERT INTO @a SELECT * FROM AS_TABLE($a);
INSERT INTO @b SELECT * FROM AS_TABLE($b);
COMMIT;
$a_with_filter = SELECT * FROM @a WHERE key != 10;
$b_with_filter = SELECT * FROM @b WHERE key != 10;
$c_with_filter = SELECT * FROM @b WHERE key != 11;
$d_with_filter = SELECT * FROM @b WHERE key != 12;
-- Predicates for inputs
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM @a AS a JOIN @b AS b ON a.key = b.key
WHERE (a.subkey = 3 AND b.subkey = 150) OR (a.subkey = 2 AND b.subkey = 5005) OR (a.subkey = 8 AND b.subkey = 200);
-- > push (a.subkey = 3 OR a.subkey = 2 OR a.subkey = 8) to a
-- > push (b.subkey = 150 OR b.subkey = 5005 OR b.subkey = 200) to b
-- > predicates for a and b must be eliminated by PhysicalOptimizer-UnessentialFilter (direct reads)
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM $a_with_filter AS a JOIN @b AS b ON a.key = b.key
WHERE (a.subkey = 3 AND b.subkey = 150) OR (a.subkey = 2 AND b.subkey = 5005) OR (a.subkey = 8 AND b.subkey = 200);
-- > push (a.subkey = 3 OR a.subkey = 2 OR a.subkey = 8) to a
-- > push (b.subkey = 150 OR b.subkey = 5005 OR b.subkey = 200) to b
-- > predicates for b must be eliminated (direct read)
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM $a_with_filter AS a JOIN $b_with_filter AS b ON a.key = b.key
WHERE (a.subkey = 3 AND b.subkey = 150) OR (a.subkey = 2 AND b.subkey = 5005) OR (a.subkey = 8 AND b.subkey = 200);
-- > push (a.subkey = 3 OR a.subkey = 2 OR a.subkey = 8) to a
-- > push (b.subkey = 150 OR b.subkey = 5005 OR b.subkey = 200) to b
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM $a_with_filter AS a JOIN $b_with_filter AS b ON a.key = b.key
WHERE (a.subkey = 3 AND b.subkey = 150) OR a.subkey = 2 OR (a.subkey = 8 AND b.subkey = 200);
-- > push (a.subkey = 3 OR a.subkey = 2 OR a.subkey = 8) to a
-- > can't extract predicates for b
-- Constant predicates
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM $a_with_filter AS a JOIN @b AS b ON a.key = b.key
WHERE (a.subkey = 3 AND b.subkey = 150 AND 'a' == 'b') OR (a.subkey = 2 AND b.subkey = 5005 AND 'c' == 'd') OR (a.subkey = 8 AND b.subkey = 200 AND 'e' == 'f');
-- > push (a.subkey = 3 OR a.subkey = 2 OR a.subkey = 8) and ('a' == 'b' OR 'c' == 'd' OR 'e' == 'f') to a
-- > push (b.subkey = 150 OR b.subkey = 5005 OR b.subkey = 200) and ('a' == 'b' OR 'c' == 'd' OR 'e' == 'f') to b
-- > predicates for b must be eliminated (direct read)
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM $a_with_filter AS a JOIN $b_with_filter AS b ON a.key = b.key
WHERE (a.subkey = 3 AND b.subkey = 150 AND 'a' == 'b') OR (a.subkey = 2 AND b.subkey = 5005 AND 'c' == 'd') OR (a.subkey = 8 AND b.subkey = 200 AND 'e' == 'f');
-- > push (a.subkey = 3 OR a.subkey = 2 OR a.subkey = 8) and ('a' == 'b' OR 'c' == 'd' OR 'e' == 'f') to a
-- > push (b.subkey = 150 OR b.subkey = 5005 OR b.subkey = 200) and ('a' == 'b' OR 'c' == 'd' OR 'e' == 'f') to b
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM $a_with_filter AS a JOIN $b_with_filter AS b ON a.key = b.key
WHERE (a.subkey = 3 AND b.subkey = 150 AND 'a' == 'b') OR (a.subkey = 2 AND b.subkey = 5005) OR (a.subkey = 8 AND b.subkey = 200 AND 'e' == 'f');
-- > push (a.subkey = 3 OR a.subkey = 2 OR a.subkey = 8) to a
-- > push (b.subkey = 150 OR b.subkey = 5005 OR b.subkey = 200) to b
-- > can't extract constant predicates
-- Predicate expansion
SELECT
a.key, b.key, c.key, d.key
FROM $a_with_filter AS a
JOIN $b_with_filter AS b ON a.key = b.key
JOIN $c_with_filter AS c ON b.key = c.key
JOIN $d_with_filter AS d ON c.key = d.key
WHERE
(a.subkey = 1 AND b.subkey = 3 AND c.subkey = 6 AND d.subkey = 10)
OR (
a.subkey = 2
AND (
(b.subkey = 4 AND c.subkey = 7 AND d.subkey = 11)
OR (
b.subkey = 5
AND ((c.subkey = 8 AND d.subkey = 12) OR (c.subkey = 9 AND d.subkey = 13))
)
)
);
-- > push (a.subkey IN (1, 2, 2, 2)) to a
-- > push (b.subkey IN (3, 4, 5, 5)) to b
-- > push (c.subkey IN (6, 7, 8, 9)) to c
-- > push (d.subkey IN (10, 11, 12, 13)) to d
-- Strict
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM $a_with_filter AS a JOIN $b_with_filter AS b ON a.key = b.key
WHERE (a.key = 1 AND Unwrap(b.value) = "AAA" and b.key = 1) OR (a.value = Just("AAA") AND b.key = 2);
-- > push (a.key = 1 OR a.value = Just("AAA")) to a
-- > can't extract predicates for b
-- Misc
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM $a_with_filter AS a JOIN $b_with_filter AS b ON a.key = b.key
WHERE (a.subkey = b.key AND 'a' == 'b') OR (a.subkey = 2 AND 'c' == 'd') OR (a.subkey = 8 AND 'e' == 'f');
-- > push ('a' == 'b' OR 'c' == 'd' OR 'e' == 'f') to a
-- > push ('a' == 'b' OR 'c' == 'd' OR 'e' == 'f') to b
-- > can't extract non-constant predicates for inputs
SELECT
a.key, a.subkey, a.value, b.key, b.subkey, b.value
FROM $a_with_filter AS a JOIN $b_with_filter AS b ON a.key = b.key
WHERE (a.subkey = 3 AND b.subkey = 150) OR (a.subkey = 2 AND b.subkey = 5005 AND RandomNumber(JoinTableRow()) == 0) OR (a.subkey = 8 AND b.subkey = 200);
-- > can't extract predicates for inputs (JoinTableRow() prevents extraction)
|
