Line data Source code
1 : // Join materialization tests for `SemanticExecutor` (R17 hash
2 : // equi-join). Every test asserts end results only: eligible shapes
3 : // take `MaterializeHashEquiJoinRows`, the rest keep the nested loop,
4 : // and both must produce identical rows. Fixture shared via
5 : // `executor_test_fixture.h`.
6 :
7 : #include <cstdint>
8 : #include <string>
9 : #include <utility>
10 : #include <vector>
11 :
12 : #include "backend/engine/semantic/executor.h"
13 : #include "backend/engine/semantic/executor_test_fixture.h"
14 : #include "backend/storage/storage.h"
15 : #include "gtest/gtest.h"
16 :
17 : namespace bigquery_emulator {
18 : namespace backend {
19 : namespace engine {
20 : namespace semantic {
21 : namespace {
22 :
23 : class SemanticExecutorJoinTest : public SemanticExecutorTest {
24 : protected:
25 6 : std::vector<storage::Row> Run(const std::string& sql) {
26 6 : const auto* stmt = Analyze(sql, MakeAnalyzerOptions());
27 6 : EXPECT_NE(stmt, nullptr);
28 6 : if (stmt == nullptr) return {};
29 6 : SemanticExecutor exec;
30 6 : auto source = exec.ExecuteQuery(MakeRequest(sql), *stmt, catalog_.get());
31 12 : EXPECT_TRUE(source.ok()) << source.status();
32 6 : std::vector<storage::Row> rows;
33 6 : if (!source.ok()) return rows;
34 23 : while (true) {
35 23 : storage::Row row;
36 23 : auto has = (*source)->Next(&row);
37 46 : EXPECT_TRUE(has.ok()) << has.status();
38 23 : if (!has.ok() || !*has) break;
39 17 : rows.push_back(std::move(row));
40 17 : }
41 6 : return rows;
42 6 : }
43 : };
44 :
45 : // INNER equi-join over STRING keys with duplicates on both sides:
46 : // hash-path eligible; every key pair must appear with full
47 : // multiplicity (2 x 2 = 4 rows for the duplicated key).
48 1 : TEST_F(SemanticExecutorJoinTest, InnerEquiJoinDuplicateKeysKeepMultiplicity) {
49 1 : const std::string sql =
50 1 : "SELECT l.k, l.lv, r.rv FROM ("
51 1 : " SELECT 'a' AS k, 1 AS lv UNION ALL SELECT 'a', 2 UNION ALL"
52 1 : " SELECT 'b', 3 UNION ALL SELECT 'z', 9"
53 1 : ") l JOIN ("
54 1 : " SELECT 'a' AS k, 10 AS rv UNION ALL SELECT 'a', 20 UNION ALL"
55 1 : " SELECT 'b', 30"
56 1 : ") r ON l.k = r.k "
57 1 : "ORDER BY l.k, l.lv, r.rv";
58 1 : std::vector<storage::Row> rows = Run(sql);
59 1 : ASSERT_EQ(rows.size(), 5u); // 'a': 2x2 = 4, 'b': 1, 'z': unmatched.
60 1 : const std::vector<std::vector<int64_t>> want = {
61 1 : {1, 10}, {1, 20}, {2, 10}, {2, 20}, {3, 30}};
62 6 : for (size_t i = 0; i < want.size(); ++i) {
63 10 : EXPECT_EQ(rows[i].cells[1].int64_value(), want[i][0]) << "row " << i;
64 10 : EXPECT_EQ(rows[i].cells[2].int64_value(), want[i][1]) << "row " << i;
65 5 : }
66 1 : }
67 :
68 : // LEFT equi-join: unmatched left rows null-extend, and NULL join
69 : // keys never match (SQL equality), including NULL-vs-NULL.
70 1 : TEST_F(SemanticExecutorJoinTest, LeftEquiJoinNullExtendsAndNullKeysMiss) {
71 1 : const std::string sql =
72 1 : "SELECT l.id, r.rv FROM ("
73 1 : " SELECT 1 AS id, 'a' AS k UNION ALL"
74 1 : " SELECT 2, CAST(NULL AS STRING) UNION ALL"
75 1 : " SELECT 3, 'missing'"
76 1 : ") l LEFT JOIN ("
77 1 : " SELECT 'a' AS k, 10 AS rv UNION ALL"
78 1 : " SELECT CAST(NULL AS STRING), 99"
79 1 : ") r ON l.k = r.k "
80 1 : "ORDER BY l.id";
81 1 : std::vector<storage::Row> rows = Run(sql);
82 1 : ASSERT_EQ(rows.size(), 3u);
83 1 : EXPECT_EQ(rows[0].cells[1].int64_value(), 10);
84 : // Row 2 has a NULL key: must NOT match the NULL-keyed right row.
85 1 : EXPECT_TRUE(rows[1].cells[1].is_null());
86 1 : EXPECT_TRUE(rows[2].cells[1].is_null());
87 1 : }
88 :
89 : // Equality keys plus a non-equality residual conjunct: the residual
90 : // must filter candidate pairs after the hash probe, and a left row
91 : // whose candidates all fail the residual must still null-extend.
92 1 : TEST_F(SemanticExecutorJoinTest, LeftEquiJoinResidualFiltersCandidates) {
93 1 : const std::string sql =
94 1 : "SELECT l.id, r.rv FROM ("
95 1 : " SELECT 1 AS id, 'a' AS k, 5 AS lo UNION ALL"
96 1 : " SELECT 2, 'a', 50"
97 1 : ") l LEFT JOIN ("
98 1 : " SELECT 'a' AS k, 10 AS rv UNION ALL SELECT 'a', 60"
99 1 : ") r ON l.k = r.k AND r.rv > l.lo "
100 1 : "ORDER BY l.id, r.rv";
101 1 : std::vector<storage::Row> rows = Run(sql);
102 1 : ASSERT_EQ(rows.size(), 3u);
103 : // id=1 (lo=5): both rv=10 and rv=60 pass. id=2 (lo=50): only rv=60.
104 1 : EXPECT_EQ(rows[0].cells[1].int64_value(), 10);
105 1 : EXPECT_EQ(rows[1].cells[1].int64_value(), 60);
106 1 : EXPECT_EQ(rows[2].cells[1].int64_value(), 60);
107 1 : }
108 :
109 : // Composite (two-column) hash key: both equalities must hold.
110 1 : TEST_F(SemanticExecutorJoinTest, InnerEquiJoinCompositeKey) {
111 1 : const std::string sql =
112 1 : "SELECT l.v, r.w FROM ("
113 1 : " SELECT 't1' AS tenant, 1 AS id, 100 AS v UNION ALL"
114 1 : " SELECT 't1', 2, 200 UNION ALL"
115 1 : " SELECT 't2', 1, 300"
116 1 : ") l JOIN ("
117 1 : " SELECT 't1' AS tenant, 1 AS id, 111 AS w UNION ALL"
118 1 : " SELECT 't2', 1, 333"
119 1 : ") r ON l.tenant = r.tenant AND l.id = r.id "
120 1 : "ORDER BY l.v";
121 1 : std::vector<storage::Row> rows = Run(sql);
122 1 : ASSERT_EQ(rows.size(), 2u);
123 1 : EXPECT_EQ(rows[0].cells[0].int64_value(), 100);
124 1 : EXPECT_EQ(rows[0].cells[1].int64_value(), 111);
125 1 : EXPECT_EQ(rows[1].cells[0].int64_value(), 300);
126 1 : EXPECT_EQ(rows[1].cells[1].int64_value(), 333);
127 1 : }
128 :
129 : // Non-equality join (no hashable equi conjunct) keeps the nested
130 : // loop and still produces correct LEFT OUTER rows.
131 1 : TEST_F(SemanticExecutorJoinTest, NonEquiLeftJoinUsesNestedLoop) {
132 1 : const std::string sql =
133 1 : "SELECT l.id, r.rv FROM ("
134 1 : " SELECT 1 AS id, 5 AS k UNION ALL"
135 1 : " SELECT 2, 50"
136 1 : ") l LEFT JOIN ("
137 1 : " SELECT 10 AS rv UNION ALL SELECT 60"
138 1 : ") r ON r.rv > l.k "
139 1 : "ORDER BY l.id, r.rv";
140 1 : std::vector<storage::Row> rows = Run(sql);
141 1 : ASSERT_EQ(rows.size(), 3u);
142 : // id=1 (k=5): both 10 and 60. id=2 (k=50): only 60.
143 1 : EXPECT_EQ(rows[0].cells[1].int64_value(), 10);
144 1 : EXPECT_EQ(rows[1].cells[1].int64_value(), 60);
145 1 : EXPECT_EQ(rows[2].cells[1].int64_value(), 60);
146 1 : }
147 :
148 : // Swapped-side equality (`r.k = l.k`) must still hash-plan; result
149 : // parity with the canonical order.
150 1 : TEST_F(SemanticExecutorJoinTest, InnerEquiJoinSwappedEqualitySides) {
151 1 : const std::string sql =
152 1 : "SELECT l.lv, r.rv FROM ("
153 1 : " SELECT 1 AS k, 100 AS lv UNION ALL SELECT 2, 200"
154 1 : ") l JOIN ("
155 1 : " SELECT 1 AS k, 11 AS rv UNION ALL SELECT 3, 33"
156 1 : ") r ON r.k = l.k "
157 1 : "ORDER BY l.lv";
158 1 : std::vector<storage::Row> rows = Run(sql);
159 1 : ASSERT_EQ(rows.size(), 1u);
160 1 : EXPECT_EQ(rows[0].cells[0].int64_value(), 100);
161 1 : EXPECT_EQ(rows[0].cells[1].int64_value(), 11);
162 1 : }
163 :
164 : } // namespace
165 : } // namespace semantic
166 : } // namespace engine
167 : } // namespace backend
168 : } // namespace bigquery_emulator
|
