Line data Source code
1 : #include "backend/engine/duckdb/transpiler/transpiler_test_fixture.h"
2 : #include "googlesql/public/types/array_type.h"
3 :
4 : // R9: Anti-join over QUALIFY-deduped views — DuckDB binder "column id not
5 : // found". Indexed in conformance/REGRESSIONS.md. Part of the transpiler
6 : // binding property-test suite: generated query compositions must always
7 : // transpile to SQL that binds in DuckDB.
8 :
9 : namespace bigquery_emulator {
10 : namespace backend {
11 : namespace engine {
12 : namespace duckdb {
13 : namespace transpiler {
14 : namespace {
15 :
16 : constexpr int kCompositionGeneratorSeed = 0x06060606;
17 : constexpr int kCompositionGeneratorCases = 24;
18 : constexpr int kDistinctAfterDedupGeneratorCases = 12;
19 :
20 : std::string WrapQualifyDedupSubquery(absl::string_view partition_col,
21 26 : absl::string_view inner_sql) {
22 26 : return absl::StrCat(
23 26 : "SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ",
24 26 : partition_col,
25 26 : " ORDER BY ",
26 26 : partition_col,
27 26 : " DESC) AS rn FROM (",
28 26 : inner_sql,
29 26 : ")) WHERE rn = 1");
30 26 : }
31 :
32 84 : uint32_t LcgNext(uint32_t* state) {
33 84 : *state = *state * 1664525u + 1013904223u;
34 84 : return *state;
35 84 : }
36 :
37 : } // namespace
38 :
39 : class TranspilerCompositionTest : public TranspilerBindFixture {
40 : protected:
41 12 : void SetUp() override {
42 12 : TranspilerBindFixture::SetUp();
43 :
44 12 : const ::googlesql::ArrayType* profile_tags_type = nullptr;
45 12 : ASSERT_TRUE(
46 12 : type_factory_
47 12 : ->MakeArrayType(type_factory_->get_string(), &profile_tags_type)
48 12 : .ok());
49 12 : auto profiles = std::make_unique<::googlesql::SimpleTable>(
50 12 : "profiles",
51 12 : std::vector<::googlesql::SimpleTable::NameAndType>{
52 12 : {"id", type_factory_->get_int64()},
53 12 : {"name", type_factory_->get_string()},
54 12 : });
55 12 : catalog_->AddOwnedTable(std::move(profiles));
56 :
57 12 : auto dedup_profiles = std::make_unique<::googlesql::SimpleTable>(
58 12 : "dedup_profiles",
59 12 : std::vector<::googlesql::SimpleTable::NameAndType>{
60 12 : {"id", type_factory_->get_int64()},
61 12 : {"city", type_factory_->get_string()},
62 12 : {"tags", profile_tags_type},
63 12 : {"source_updated_at", type_factory_->get_timestamp()},
64 12 : });
65 12 : catalog_->AddOwnedTable(std::move(dedup_profiles));
66 :
67 12 : auto bq_orders = std::make_unique<::googlesql::SimpleTable>(
68 12 : "bq_orders",
69 12 : std::vector<::googlesql::SimpleTable::NameAndType>{
70 12 : {"order_id", type_factory_->get_int64()},
71 12 : {"customer_id", type_factory_->get_int64()},
72 12 : });
73 12 : catalog_->AddOwnedTable(std::move(bq_orders));
74 :
75 12 : const ::googlesql::ArrayType* vals_type = nullptr;
76 12 : ASSERT_TRUE(
77 12 : type_factory_->MakeArrayType(type_factory_->get_int64(), &vals_type)
78 12 : .ok());
79 12 : auto items = std::make_unique<::googlesql::SimpleTable>(
80 12 : "items",
81 12 : std::vector<::googlesql::SimpleTable::NameAndType>{
82 12 : {"id", type_factory_->get_int64()},
83 12 : {"vals", vals_type},
84 12 : });
85 12 : catalog_->AddOwnedTable(std::move(items));
86 :
87 12 : const ::googlesql::ArrayType* tags_type = nullptr;
88 12 : ASSERT_TRUE(
89 12 : type_factory_->MakeArrayType(type_factory_->get_string(), &tags_type)
90 12 : .ok());
91 12 : auto arrays = std::make_unique<::googlesql::SimpleTable>(
92 12 : "arrays",
93 12 : std::vector<::googlesql::SimpleTable::NameAndType>{
94 12 : {"tags", tags_type},
95 12 : });
96 12 : catalog_->AddOwnedTable(std::move(arrays));
97 :
98 12 : const ::googlesql::ArrayType* collection_ids_type = nullptr;
99 12 : ASSERT_TRUE(
100 12 : type_factory_
101 12 : ->MakeArrayType(type_factory_->get_int64(), &collection_ids_type)
102 12 : .ok());
103 12 : auto products = std::make_unique<::googlesql::SimpleTable>(
104 12 : "products",
105 12 : std::vector<::googlesql::SimpleTable::NameAndType>{
106 12 : {"id", type_factory_->get_int64()},
107 12 : {"title", type_factory_->get_string()},
108 12 : {"is_published", type_factory_->get_bool()},
109 12 : {"collection_ids", collection_ids_type},
110 12 : });
111 12 : catalog_->AddOwnedTable(std::move(products));
112 :
113 12 : auto collections = std::make_unique<::googlesql::SimpleTable>(
114 12 : "collections",
115 12 : std::vector<::googlesql::SimpleTable::NameAndType>{
116 12 : {"id", type_factory_->get_int64()},
117 12 : {"title", type_factory_->get_string()},
118 12 : {"is_published", type_factory_->get_bool()},
119 12 : {"products_count", type_factory_->get_int64()},
120 12 : });
121 12 : catalog_->AddOwnedTable(std::move(collections));
122 :
123 12 : ExecDdl("CREATE TABLE bq_orders (order_id BIGINT, customer_id BIGINT)");
124 12 : ExecDdl("CREATE TABLE profiles (id BIGINT, name VARCHAR)");
125 12 : ExecDdl("CREATE TABLE people (id BIGINT, name VARCHAR)");
126 12 : ExecDdl("CREATE TABLE items (id BIGINT, vals BIGINT[])");
127 12 : ExecDdl("CREATE TABLE arrays (tags STRING[])");
128 12 : ExecDdl(
129 12 : "CREATE TABLE dedup_profiles (id BIGINT, city VARCHAR, tags STRING[], "
130 12 : "source_updated_at TIMESTAMPTZ)");
131 12 : ExecDdl(
132 12 : "CREATE TABLE products (id BIGINT, title VARCHAR, is_published "
133 12 : "BOOLEAN, collection_ids BIGINT[])");
134 12 : ExecDdl(
135 12 : "CREATE TABLE collections (id BIGINT, title VARCHAR, is_published "
136 12 : "BOOLEAN, products_count BIGINT)");
137 12 : }
138 : };
139 :
140 1 : TEST_F(TranspilerCompositionTest, DistinctCityAfterQualifyDedupBinds) {
141 1 : static constexpr const char kSql[] = R"sql(
142 1 : SELECT DISTINCT city
143 1 : FROM (
144 1 : SELECT * FROM dedup_profiles
145 1 : QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY source_updated_at DESC) = 1
146 1 : )
147 1 : WHERE city IS NOT NULL
148 1 : )sql";
149 1 : AssertSqlTranspileBinds(kSql);
150 1 : }
151 :
152 1 : TEST_F(TranspilerCompositionTest, DistinctUnnestAfterQualifyDedupBinds) {
153 1 : static constexpr const char kSql[] = R"sql(
154 1 : SELECT DISTINCT tag
155 1 : FROM (
156 1 : SELECT * FROM dedup_profiles
157 1 : QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY source_updated_at DESC) = 1
158 1 : ), UNNEST(tags) AS tag
159 1 : )sql";
160 1 : AssertSqlTranspileBinds(kSql);
161 1 : }
162 :
163 1 : TEST_F(TranspilerCompositionTest, CorrelatedUnnestFromTableBinds) {
164 1 : static constexpr const char kSql[] = R"sql(
165 1 : SELECT id, n
166 1 : FROM items, UNNEST(items.vals) AS n
167 1 : ORDER BY id, n
168 1 : )sql";
169 1 : AssertSqlTranspileBinds(kSql);
170 1 : }
171 :
172 : // R13: UNNEST + GROUP BY inside a CTE, then outer LEFT JOIN on the
173 : // unnested value — CTE alias/rn state must not leak into the outer join.
174 1 : TEST_F(TranspilerCompositionTest, UnnestGroupByInCteThenJoinBinds) {
175 1 : static constexpr const char kSql[] = R"sql(
176 1 : WITH product_counts AS (
177 1 : SELECT col_id, COUNT(DISTINCT p.id) AS calculated_product_count
178 1 : FROM products p, UNNEST(p.collection_ids) AS col_id
179 1 : GROUP BY col_id
180 1 : )
181 1 : SELECT
182 1 : c.id AS collection_id,
183 1 : c.title AS collection_title,
184 1 : c.products_count AS collection_table_count,
185 1 : COALESCE(pc.calculated_product_count, 0) AS calculated_product_count,
186 1 : (c.products_count - COALESCE(pc.calculated_product_count, 0)) AS discrepancy
187 1 : FROM collections c
188 1 : LEFT JOIN product_counts pc ON c.id = pc.col_id
189 1 : WHERE COALESCE(c.products_count, 0) != COALESCE(pc.calculated_product_count, 0)
190 1 : )sql";
191 1 : AssertSqlTranspileBinds(kSql);
192 1 : }
193 :
194 : // R13: correlated UNNEST in CTE (no GROUP BY) + outer JOIN — anchors and
195 : // join sides must not reference stale `__bq_j_<id>` aliases.
196 1 : TEST_F(TranspilerCompositionTest, UnnestInCteThenJoinBinds) {
197 1 : static constexpr const char kSql[] = R"sql(
198 1 : WITH product_collections AS (
199 1 : SELECT p.id AS product_id, col_id
200 1 : FROM products p, UNNEST(p.collection_ids) AS col_id
201 1 : )
202 1 : SELECT c.id AS collection_id, c.title AS collection_title, pc.product_id
203 1 : FROM collections c
204 1 : LEFT JOIN product_collections pc ON c.id = pc.col_id
205 1 : )sql";
206 1 : AssertSqlTranspileBinds(kSql);
207 1 : }
208 :
209 1 : TEST_F(TranspilerCompositionTest, CoreUsageUnnestArrayShapeBinds) {
210 1 : static constexpr const char kSql[] = R"sql(
211 1 : SELECT tag FROM arrays, UNNEST(tags) AS tag
212 1 : )sql";
213 1 : AssertSqlTranspileBinds(kSql);
214 1 : }
215 :
216 1 : TEST_F(TranspilerCompositionTest, NestedUnnestCrossProductBinds) {
217 1 : static constexpr const char kSql[] = R"sql(
218 1 : SELECT n, m
219 1 : FROM UNNEST(GENERATE_ARRAY(1, 2)) AS n
220 1 : CROSS JOIN UNNEST(GENERATE_ARRAY(10, 11)) AS m
221 1 : )sql";
222 1 : AssertSqlTranspileBinds(kSql);
223 1 : }
224 :
225 1 : TEST_F(TranspilerCompositionTest, OrphanOrdersQualifyDedupAntiJoinBinds) {
226 1 : static constexpr const char kSql[] = R"sql(
227 1 : SELECT o.order_id
228 1 : FROM (
229 1 : SELECT * FROM bq_orders
230 1 : QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_id) = 1
231 1 : ) o
232 1 : LEFT JOIN (
233 1 : SELECT * FROM profiles
234 1 : QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) = 1
235 1 : ) p ON o.customer_id = p.id
236 1 : WHERE p.id IS NULL
237 1 : ORDER BY o.order_id
238 1 : )sql";
239 1 : AssertSqlTranspileBinds(kSql);
240 1 : }
241 :
242 1 : TEST_F(TranspilerCompositionTest, OrphanOrdersSubqueryDedupAntiJoinBinds) {
243 1 : static constexpr const char kSql[] = R"sql(
244 1 : SELECT o.order_id, o.customer_id
245 1 : FROM (
246 1 : SELECT order_id, customer_id FROM (
247 1 : SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_id) AS rn
248 1 : FROM (
249 1 : SELECT 1 AS order_id, 10 AS customer_id UNION ALL
250 1 : SELECT 2 AS order_id, 99 AS customer_id
251 1 : )
252 1 : ) WHERE rn = 1
253 1 : ) o
254 1 : LEFT JOIN (
255 1 : SELECT id FROM (
256 1 : SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
257 1 : FROM (SELECT 10 AS id)
258 1 : ) WHERE rn = 1
259 1 : ) p ON o.customer_id = p.id
260 1 : WHERE o.customer_id IS NOT NULL AND p.id IS NULL
261 1 : )sql";
262 1 : AssertSqlTranspileBinds(kSql);
263 1 : }
264 :
265 1 : TEST_F(TranspilerCompositionTest, NestedQualifyJoinCteExceptBinds) {
266 1 : static constexpr const char kSql[] = R"sql(
267 1 : WITH dedup AS (
268 1 : SELECT id, name FROM (
269 1 : SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
270 1 : FROM people
271 1 : ) WHERE rn = 1
272 1 : )
273 1 : SELECT a.id FROM dedup a
274 1 : LEFT JOIN dedup b ON a.id = b.id
275 1 : WHERE b.id IS NULL
276 1 : EXCEPT DISTINCT
277 1 : SELECT CAST(0 AS INT64) AS id
278 1 : )sql";
279 1 : AssertSqlTranspileBinds(kSql);
280 1 : }
281 :
282 1 : TEST_F(TranspilerCompositionTest, SeededCompositionGeneratorBinds) {
283 1 : uint32_t rng = kCompositionGeneratorSeed;
284 25 : for (int i = 0; i < kCompositionGeneratorCases; ++i) {
285 24 : const int base = static_cast<int>(LcgNext(&rng) % 2);
286 24 : const int wrap1 = static_cast<int>(LcgNext(&rng) % 3);
287 24 : const int wrap2 = static_cast<int>(LcgNext(&rng) % 3);
288 :
289 24 : const char* key_col = base == 0 ? "id" : "order_id";
290 24 : std::string inner = base == 0
291 24 : ? "SELECT id, name FROM people"
292 24 : : "SELECT order_id, customer_id FROM bq_orders";
293 :
294 24 : if (wrap1 == 1) {
295 5 : inner = WrapQualifyDedupSubquery(key_col, inner);
296 19 : } else if (wrap1 == 2) {
297 9 : inner = absl::StrCat("SELECT * EXCEPT(rn) FROM (",
298 9 : WrapQualifyDedupSubquery(key_col, inner),
299 9 : ")");
300 9 : }
301 :
302 24 : std::string sql;
303 24 : if (wrap2 == 0) {
304 6 : sql = absl::StrCat(
305 6 : "SELECT ", key_col, " FROM (", inner, ") t WHERE ", key_col, " >= 0");
306 18 : } else if (wrap2 == 1) {
307 10 : sql = absl::StrCat("WITH w AS (", inner, ") SELECT COUNT(*) AS c FROM w");
308 10 : } else {
309 8 : sql = absl::StrCat("SELECT a.",
310 8 : key_col,
311 8 : " FROM (",
312 8 : inner,
313 8 : ") a LEFT JOIN (",
314 8 : inner,
315 8 : ") b ON a.",
316 8 : key_col,
317 8 : " = b.",
318 8 : key_col,
319 8 : " WHERE b.",
320 8 : key_col,
321 8 : " IS NULL");
322 8 : }
323 :
324 24 : SCOPED_TRACE(absl::StrCat("case=", i, " sql=", sql));
325 24 : AssertSqlTranspileBinds(sql);
326 24 : }
327 1 : }
328 :
329 1 : TEST_F(TranspilerCompositionTest, SeededDistinctAfterDedupGeneratorBinds) {
330 1 : uint32_t rng = kCompositionGeneratorSeed ^ 0xD157111Cu;
331 13 : for (int i = 0; i < kDistinctAfterDedupGeneratorCases; ++i) {
332 12 : const int wrap3 = static_cast<int>(LcgNext(&rng) % 3);
333 12 : const std::string deduped = WrapQualifyDedupSubquery(
334 12 : "id", "SELECT id, city, tags FROM dedup_profiles");
335 :
336 12 : std::string sql;
337 12 : if (wrap3 == 0) {
338 3 : sql = absl::StrCat(
339 3 : "SELECT DISTINCT city FROM (", deduped, ") WHERE city IS NOT NULL");
340 9 : } else if (wrap3 == 1) {
341 4 : sql = absl::StrCat(
342 4 : "SELECT DISTINCT tag FROM (", deduped, "), UNNEST(tags) AS tag");
343 5 : } else {
344 5 : sql = absl::StrCat(
345 5 : "SELECT city, COUNT(*) AS c FROM (", deduped, ") GROUP BY city");
346 5 : }
347 :
348 12 : SCOPED_TRACE(absl::StrCat("distinct_case=", i, " sql=", sql));
349 12 : AssertSqlTranspileBinds(sql);
350 12 : }
351 1 : }
352 :
353 : } // namespace transpiler
354 : } // namespace duckdb
355 : } // namespace engine
356 : } // namespace backend
357 : } // namespace bigquery_emulator
|
