Line data Source code
1 : #include "backend/engine/duckdb/transpiler/transpiler_test_fixture.h"
2 :
3 : namespace bigquery_emulator {
4 : namespace backend {
5 : namespace engine {
6 : namespace duckdb {
7 : namespace transpiler {
8 :
9 1 : TEST_F(TranspilerTest, TranspileSelectFromWhereGroupByOrderByLimit) {
10 : // Engine-level smoke check for the plan's "SELECT ... FROM ...
11 : // WHERE ... GROUP BY ... ORDER BY ... LIMIT" target. We don't
12 : // round-trip through DuckDB here -- the unit-test fixture has no
13 : // running DuckDB connection -- but we *do* drive the full
14 : // `Transpile(stmt)` pipeline so a regression in any one of
15 : // EmitQueryStmt / EmitLimitOffsetScan / EmitOrderByScan /
16 : // EmitAggregateScan / EmitFilterScan / EmitTableScan surfaces as
17 : // a string drift here. The engine-side smoke test (executing on
18 : // DuckDB) is left to a follow-up plan once the DuckDBEngine
19 : // integration is updated to dispatch on QueryStmt directly rather
20 : // than the StripPassThroughProjectScans subset; see
21 : // docs/ENGINE_POLICY.md for the engine wiring
22 : // that lands separately.
23 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
24 1 : "SELECT id, COUNT(*) AS c FROM people WHERE id > 0 GROUP BY id "
25 1 : "ORDER BY id LIMIT 10");
26 1 : ASSERT_NE(stmt, nullptr);
27 1 : TestTranspiler t;
28 : // Filter predicate (`>`) and LIMIT 10 over a synthesized aggregate
29 : // column thread together; the per-piece coverage above keeps each
30 : // emit honest, while this assertion pins the composition.
31 1 : std::string sql = t.Transpile(stmt);
32 1 : ASSERT_FALSE(sql.empty());
33 1 : EXPECT_NE(sql.find("WHERE (\"id\" > 0)"), std::string::npos);
34 1 : EXPECT_NE(sql.find("GROUP BY \"id\""), std::string::npos);
35 1 : EXPECT_NE(sql.find("ORDER BY \"id\" ASC"), std::string::npos);
36 1 : EXPECT_NE(sql.find("LIMIT 10"), std::string::npos);
37 1 : }
38 :
39 1 : TEST_F(TranspilerTest, TranspileGroupByAggregateOrderByLimit) {
40 : // BigFrames `groupby(...).mean().sort_values().head()` lowers to
41 : // GROUP BY + aggregate + ORDER BY aggregate output + LIMIT. The
42 : // ORDER BY column is an aggregate output, not a grouping key.
43 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
44 1 : "SELECT SUM(amount) AS total FROM orders GROUP BY order_id "
45 1 : "ORDER BY total DESC LIMIT 5");
46 1 : ASSERT_NE(stmt, nullptr);
47 1 : TestTranspiler t;
48 1 : std::string sql = t.Transpile(stmt);
49 2 : ASSERT_FALSE(sql.empty())
50 2 : << "OrderByScan over aggregate output must transpile";
51 1 : EXPECT_NE(sql.find("GROUP BY"), std::string::npos);
52 1 : EXPECT_NE(sql.find("ORDER BY"), std::string::npos);
53 1 : EXPECT_NE(sql.find("LIMIT 5"), std::string::npos);
54 1 : }
55 :
56 1 : TEST_F(TranspilerTest, TranspileGroupByHiddenKeyOrderByAggregateLimit) {
57 : // BigFrames groupby-mean keeps only the aggregate in the SELECT list
58 : // while still grouping by another column; ORDER BY sorts the aggregate
59 : // output column (often reusing the source column name as alias).
60 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
61 1 : "SELECT AVG(amount) AS amount FROM orders GROUP BY order_id "
62 1 : "ORDER BY amount DESC LIMIT 5");
63 1 : ASSERT_NE(stmt, nullptr);
64 1 : TestTranspiler t;
65 1 : std::string sql = t.Transpile(stmt);
66 1 : ASSERT_FALSE(sql.empty());
67 1 : }
68 :
69 1 : TEST_F(TranspilerTest, EmitOrderByScanOverAggregateScan) {
70 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
71 1 : "SELECT order_id, SUM(amount) AS total FROM orders GROUP BY order_id "
72 1 : "ORDER BY total DESC");
73 1 : ASSERT_NE(stmt, nullptr);
74 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
75 1 : ASSERT_NE(scan, nullptr);
76 : // Peel LimitOffsetScan if present; find OrderByScan in tree.
77 1 : while (scan != nullptr &&
78 1 : scan->node_kind() == ::googlesql::RESOLVED_LIMIT_OFFSET_SCAN) {
79 0 : scan = scan->GetAs<::googlesql::ResolvedLimitOffsetScan>()->input_scan();
80 0 : }
81 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_ORDER_BY_SCAN);
82 1 : TestTranspiler t;
83 1 : std::string sql =
84 1 : t.EmitOrderByScan(scan->GetAs<::googlesql::ResolvedOrderByScan>());
85 2 : ASSERT_FALSE(sql.empty())
86 2 : << "EmitOrderByScan returned empty for aggregate ORDER BY";
87 1 : EXPECT_NE(sql.find("ORDER BY"), std::string::npos);
88 1 : }
89 :
90 1 : TEST_F(TranspilerTest, TranspileGroupByOrderByAggregateExpr) {
91 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
92 1 : "SELECT order_id, SUM(amount) AS total FROM orders GROUP BY order_id "
93 1 : "ORDER BY SUM(amount) DESC LIMIT 5");
94 1 : ASSERT_NE(stmt, nullptr);
95 1 : TestTranspiler t;
96 1 : std::string sql = t.Transpile(stmt);
97 2 : ASSERT_FALSE(sql.empty()) << "ORDER BY aggregate expr must transpile";
98 1 : }
99 :
100 1 : TEST_F(TranspilerTest, TranspileOrderByScanRootWithoutLimit) {
101 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
102 1 : "SELECT order_id, SUM(amount) AS total FROM orders GROUP BY order_id "
103 1 : "ORDER BY total DESC");
104 1 : ASSERT_NE(stmt, nullptr);
105 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
106 1 : ASSERT_NE(scan, nullptr);
107 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_ORDER_BY_SCAN);
108 1 : TestTranspiler t;
109 1 : ASSERT_FALSE(t.Transpile(stmt).empty());
110 1 : }
111 :
112 1 : TEST_F(TranspilerTest, TranspileBigframesMeanStatsOrderByRowNumber) {
113 : // bigframes `Series.mean()` materializes multiple aggregates plus a
114 : // ROW_NUMBER() OVER (ORDER BY NULL) wrapper, then ORDER BY the
115 : // synthetic bfuid column — see captured SQL from snippet gate.
116 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(R"sql(
117 1 : SELECT `bfuid_col_2`, `mean` FROM (
118 1 : SELECT
119 1 : ROW_NUMBER() OVER (ORDER BY NULL ASC) - 1 AS `bfuid_col_2`,
120 1 : `t2`.`mean`
121 1 : FROM (
122 1 : SELECT AVG(`amount`) AS `mean` FROM `orders`
123 1 : ) AS `t2`
124 1 : ) AS `t`
125 1 : ORDER BY `bfuid_col_2` ASC NULLS LAST
126 1 : )sql");
127 1 : ASSERT_NE(stmt, nullptr);
128 1 : TestTranspiler t;
129 1 : std::string sql = t.Transpile(stmt);
130 2 : ASSERT_FALSE(sql.empty()) << "bigframes mean stats ORDER BY bfuid_col_2";
131 1 : }
132 :
133 1 : TEST_F(TranspilerTest, TranspileRowNumberOverFarmFingerprint) {
134 : // bigframes peek/cache assigns ROW_NUMBER() OVER (ORDER BY keys built
135 : // from FARM_FINGERPRINT(...)). Route classifier must not promote on the
136 : // string literal inside FARM_FINGERPRINT (ResolvedConstant).
137 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
138 1 : "SELECT ROW_NUMBER() OVER (ORDER BY FARM_FINGERPRINT('x') ASC) "
139 1 : "FROM orders");
140 1 : ASSERT_NE(stmt, nullptr);
141 1 : TestTranspiler t;
142 1 : std::string sql = t.Transpile(stmt);
143 2 : ASSERT_FALSE(sql.empty())
144 2 : << "ROW_NUMBER ORDER BY FARM_FINGERPRINT must transpile";
145 1 : EXPECT_NE(sql.find("ROW_NUMBER"), std::string::npos);
146 1 : }
147 :
148 1 : TEST_F(TranspilerTest, TranspileBigframesCacheJoinCoalesceOn) {
149 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
150 1 : "SELECT a.level_0, b.bfuid_col_2 FROM "
151 1 : "(SELECT 0 AS level_0, 'John' AS column_0) a "
152 1 : "LEFT OUTER JOIN "
153 1 : "(SELECT 0 AS bfuid_col_1, 'group_1' AS bfuid_col_2) b "
154 1 : "ON COALESCE(a.level_0, 0) = COALESCE(b.bfuid_col_1, 0) "
155 1 : "AND COALESCE(a.level_0, 1) = COALESCE(b.bfuid_col_1, 1)");
156 1 : ASSERT_NE(stmt, nullptr);
157 1 : TestTranspiler t;
158 2 : ASSERT_FALSE(t.Transpile(stmt).empty())
159 2 : << "bigframes cache COALESCE join ON must transpile";
160 1 : }
161 :
162 1 : TEST_F(TranspilerTest, TranspileUnnestJoinPreservesInputRn) {
163 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
164 1 : "SELECT a.level_0, b.column_0 FROM "
165 1 : "(SELECT * FROM UNNEST(ARRAY<STRUCT<level_0 INT64, column_0 STRING>>"
166 1 : "[STRUCT(0, 'John')]) AS level_0) a "
167 1 : "LEFT OUTER JOIN "
168 1 : "(SELECT * FROM UNNEST(ARRAY<STRUCT<level_0 INT64, column_0 STRING>>"
169 1 : "[STRUCT(0, 'group_1')]) AS level_0) b "
170 1 : "ON COALESCE(a.level_0, 0) = COALESCE(b.level_0, 0)");
171 1 : ASSERT_NE(stmt, nullptr);
172 1 : TestTranspiler t;
173 1 : std::string sql = t.Transpile(stmt);
174 2 : ASSERT_FALSE(sql.empty()) << "UNNEST join must transpile";
175 2 : EXPECT_NE(sql.find("\"__bq_input_rn\""), std::string::npos)
176 2 : << "join must preserve UNNEST ordinality column: " << sql;
177 1 : }
178 :
179 1 : TEST_F(TranspilerTest, TranspileBigframesMeanStatsFullAggregates) {
180 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(R"sql(
181 1 : SELECT `bfuid_col_2`, `count`, `min`, `max`, `std`, `mean`, `var`, `sum` FROM (
182 1 : SELECT
183 1 : ROW_NUMBER() OVER (ORDER BY NULL ASC) - 1 AS `bfuid_col_2`,
184 1 : `t2`.`count`, `t2`.`min`, `t2`.`max`, `t2`.`std`, `t2`.`mean`, `t2`.`var`, `t2`.`sum`
185 1 : FROM (
186 1 : SELECT
187 1 : COUNT(`amount`) AS `count`,
188 1 : MIN(`amount`) AS `min`,
189 1 : MAX(`amount`) AS `max`,
190 1 : STDDEV_SAMP(`amount`) AS `std`,
191 1 : AVG(`amount`) AS `mean`,
192 1 : VARIANCE(`amount`) AS `var`,
193 1 : COALESCE(SUM(`amount`), 0) AS `sum`
194 1 : FROM `orders`
195 1 : ) AS `t2`
196 1 : ) AS `t`
197 1 : ORDER BY `bfuid_col_2` ASC NULLS LAST
198 1 : )sql");
199 1 : ASSERT_NE(stmt, nullptr);
200 1 : TestTranspiler t;
201 1 : std::string sql = t.Transpile(stmt);
202 2 : ASSERT_FALSE(sql.empty()) << "full bigframes stats query must transpile";
203 1 : }
204 :
205 1 : TEST_F(TranspilerTest, TranspileLeftJoinOrphanOrdersWithAnalyticDedup) {
206 : // Regression for LEFT JOIN + ROW_NUMBER dedup (BigFrames cache shape):
207 : // analytic-captured ORDER BY keys must remap to __bq_j_* join aliases.
208 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(R"sql(
209 1 : SELECT o.id AS order_id, o.name AS order_name, o.profile_id, o.ts AS source_created_at
210 1 : FROM (
211 1 : SELECT id, name, profile_id, ts FROM (
212 1 : SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts DESC) AS rn
213 1 : FROM (SELECT 1 AS id, 'a' AS name, 42 AS profile_id, TIMESTAMP '2020-01-01' AS ts)
214 1 : ) WHERE rn = 1
215 1 : ) o
216 1 : LEFT JOIN (
217 1 : SELECT id, ts FROM (
218 1 : SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts DESC) AS rn
219 1 : FROM (SELECT 10 AS id, TIMESTAMP '2020-01-01' AS ts)
220 1 : ) WHERE rn = 1
221 1 : ) p ON o.profile_id = p.id
222 1 : WHERE o.profile_id IS NOT NULL AND p.id IS NULL
223 1 : )sql");
224 1 : ASSERT_NE(stmt, nullptr);
225 1 : TestTranspiler t;
226 1 : std::string sql = t.Transpile(stmt);
227 2 : ASSERT_FALSE(sql.empty()) << "orphan orders LEFT JOIN must transpile";
228 : // Post-join wrap must not reference bare partition/order keys (email
229 : // regression).
230 2 : EXPECT_EQ(
231 2 : sql.find(
232 2 : "\"__bq_j_1\", \"__bq_j_2\", \"__bq_j_3\", \"__bq_j_4\", \"id\""),
233 2 : std::string::npos)
234 2 : << sql;
235 2 : EXPECT_NE(sql.find("ORDER BY \"__bq_j_1\""), std::string::npos) << sql;
236 1 : }
237 :
238 1 : TEST_F(TranspilerTest, TranspileDistinctScalarAfterAnalyticDedup) {
239 : // R11: SELECT DISTINCT after ROW_NUMBER dedup must not ORDER BY columns
240 : // dropped by the DISTINCT/GROUP BY projection (email regression).
241 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(R"sql(
242 1 : SELECT DISTINCT city
243 1 : FROM (
244 1 : SELECT * FROM (
245 1 : SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY source_updated_at DESC) AS rn
246 1 : FROM (
247 1 : SELECT 1 AS id, 'Paris' AS city, FALSE AS is_deleted,
248 1 : TIMESTAMP '2025-01-01' AS source_updated_at
249 1 : UNION ALL
250 1 : SELECT 1, 'Paris', FALSE, TIMESTAMP '2025-06-01'
251 1 : )
252 1 : ) WHERE rn = 1
253 1 : )
254 1 : WHERE COALESCE(is_deleted, FALSE) = FALSE AND city IS NOT NULL
255 1 : )sql");
256 1 : ASSERT_NE(stmt, nullptr);
257 1 : TestTranspiler t;
258 1 : std::string sql = t.Transpile(stmt);
259 2 : ASSERT_FALSE(sql.empty()) << "DISTINCT city after dedup must transpile";
260 1 : const size_t group_by = sql.rfind(" GROUP BY ");
261 1 : const size_t order_by = group_by == std::string::npos
262 1 : ? sql.rfind(" ORDER BY ")
263 1 : : sql.find(" ORDER BY ", group_by);
264 1 : if (order_by != std::string::npos &&
265 1 : (group_by == std::string::npos || order_by > group_by)) {
266 0 : const std::string order_clause = sql.substr(order_by);
267 0 : EXPECT_EQ(order_clause.find("source_updated_at"), std::string::npos)
268 0 : << order_clause;
269 0 : EXPECT_EQ(order_clause.find("__bq_input_rn"), std::string::npos)
270 0 : << order_clause;
271 0 : }
272 1 : }
273 :
274 1 : TEST_F(TranspilerTest, TranspileDistinctUnnestAfterAnalyticDedup) {
275 : // R11: DISTINCT over UNNEST(tags) after dedup window (email regression).
276 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(R"sql(
277 1 : SELECT DISTINCT value
278 1 : FROM (
279 1 : SELECT * FROM (
280 1 : SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY source_updated_at DESC) AS rn
281 1 : FROM (
282 1 : SELECT 1 AS id, ['a', 'b'] AS tags, FALSE AS is_deleted,
283 1 : TIMESTAMP '2025-01-01' AS source_updated_at
284 1 : )
285 1 : ) WHERE rn = 1
286 1 : ), UNNEST(tags) AS value
287 1 : WHERE COALESCE(is_deleted, FALSE) = FALSE
288 1 : )sql");
289 1 : ASSERT_NE(stmt, nullptr);
290 1 : TestTranspiler t;
291 1 : std::string sql = t.Transpile(stmt);
292 2 : ASSERT_FALSE(sql.empty()) << "DISTINCT UNNEST after dedup must transpile";
293 1 : const size_t group_by = sql.rfind(" GROUP BY ");
294 1 : const size_t order_by = group_by == std::string::npos
295 1 : ? sql.rfind(" ORDER BY ")
296 1 : : sql.find(" ORDER BY ", group_by);
297 1 : if (order_by != std::string::npos &&
298 1 : (group_by == std::string::npos || order_by > group_by)) {
299 0 : const std::string order_clause = sql.substr(order_by);
300 0 : EXPECT_EQ(order_clause.find("source_updated_at"), std::string::npos)
301 0 : << order_clause;
302 0 : EXPECT_EQ(order_clause.find("__bq_input_rn"), std::string::npos)
303 0 : << order_clause;
304 0 : }
305 1 : }
306 :
307 1 : TEST_F(TranspilerTest, TranspileFullOuterJoinCoalesceOrderBy) {
308 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
309 1 : "SELECT COALESCE(CAST(u.id AS STRING), 'no-user') AS user_id, "
310 1 : "COALESCE(CAST(e.id AS STRING), 'no-event') AS event_id, "
311 1 : "COALESCE(u.name, '<none>') AS name, "
312 1 : "COALESCE(e.kind, '<none>') AS kind "
313 1 : "FROM (SELECT 1 AS id, 'ada' AS name) AS u "
314 1 : "FULL OUTER JOIN (SELECT 10 AS id, 1 AS user_id, 'login' AS kind) AS e "
315 1 : "ON u.id = e.user_id "
316 1 : "ORDER BY user_id, event_id");
317 1 : ASSERT_NE(stmt, nullptr);
318 1 : TestTranspiler t;
319 1 : std::string sql = t.Transpile(stmt);
320 2 : ASSERT_FALSE(sql.empty()) << "FULL OUTER JOIN COALESCE must transpile";
321 2 : EXPECT_NE(sql.find("ORDER BY \"user_id\""), std::string::npos) << sql;
322 2 : EXPECT_EQ(sql.find("__bq_j_6"), std::string::npos) << sql;
323 1 : }
324 :
325 1 : TEST_F(TranspilerTest, TranspileClusteredTableSampleQuery) {
326 : // golang-samples queryClusteredTable: global aggregates + filter +
327 : // named parameter. Regression for transpiler coverage on the
328 : // FilterScan -> AggregateScan -> QueryStmt shape used by clustered
329 : // table docs (COUNT(1), SUM(NUMERIC), COUNT(DISTINCT), TIMESTAMP).
330 1 : ::googlesql::AnalyzerOptions options = MakeAnalyzerOptions();
331 1 : ASSERT_TRUE(
332 1 : options.AddQueryParameter("wallet", type_factory_->get_string()).ok());
333 1 : const ::googlesql::ResolvedStatement* stmt = nullptr;
334 1 : stmt = AnalyzeWith(R"sql(
335 1 : SELECT
336 1 : COUNT(1) AS transactions,
337 1 : SUM(amount) AS total_paid,
338 1 : COUNT(DISTINCT destination) AS distinct_recipients
339 1 : FROM transactions
340 1 : WHERE timestamp > TIMESTAMP('2015-01-01')
341 1 : AND origin = @wallet
342 1 : )sql",
343 1 : options);
344 1 : ASSERT_NE(stmt, nullptr);
345 1 : TestTranspiler t;
346 1 : std::string sql = t.Transpile(stmt);
347 2 : ASSERT_FALSE(sql.empty()) << "queryClusteredTable sample must transpile";
348 1 : EXPECT_NE(sql.find("COUNT(1)"), std::string::npos);
349 1 : EXPECT_NE(sql.find("SUM(\"amount\")"), std::string::npos);
350 1 : EXPECT_NE(sql.find("COUNT(DISTINCT"), std::string::npos);
351 1 : }
352 :
353 1 : TEST_F(TranspilerTest, TranspileDateAddIntervalColumnRef) {
354 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
355 1 : "SELECT EXTRACT(YEAR FROM DATE_ADD(DATE '2020-01-01', INTERVAL id "
356 1 : "DAY)) AS yr FROM people");
357 1 : ASSERT_NE(stmt, nullptr);
358 1 : TestTranspiler t;
359 1 : std::string sql = t.Transpile(stmt);
360 2 : ASSERT_FALSE(sql.empty()) << "transpile failed";
361 2 : EXPECT_NE(sql.find("bq_date_add"), std::string::npos) << sql;
362 2 : EXPECT_NE(sql.find("bq_extract"), std::string::npos) << sql;
363 2 : EXPECT_EQ(sql.find(" DAY"), std::string::npos) << sql;
364 1 : }
365 :
366 1 : TEST_F(TranspilerTest, TranspileDateFuncsBenchShape) {
367 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
368 1 : "SELECT EXTRACT(YEAR FROM DATE_ADD(DATE '2020-01-01', INTERVAL id "
369 1 : "DAY)) AS yr, COUNT(*) AS cnt FROM people GROUP BY yr ORDER BY yr");
370 1 : ASSERT_NE(stmt, nullptr);
371 1 : TestTranspiler t;
372 1 : std::string sql = t.Transpile(stmt);
373 2 : ASSERT_FALSE(sql.empty()) << "transpile failed";
374 2 : EXPECT_NE(sql.find("bq_date_add"), std::string::npos) << sql;
375 2 : EXPECT_NE(sql.find("bq_extract"), std::string::npos) << sql;
376 2 : EXPECT_EQ(sql.find(" DAY"), std::string::npos) << sql;
377 1 : }
378 :
379 1 : TEST_F(TranspilerTest, TranspileFloatSumCastsAggregateToDouble) {
380 1 : const ::googlesql::ResolvedStatement* stmt =
381 1 : Analyze("SELECT SUM(CAST(id AS FLOAT64)) AS total FROM people");
382 1 : ASSERT_NE(stmt, nullptr);
383 1 : TestTranspiler t;
384 1 : std::string sql = t.Transpile(stmt);
385 2 : ASSERT_FALSE(sql.empty()) << "transpile failed";
386 2 : EXPECT_NE(sql.find("SUM("), std::string::npos) << sql;
387 1 : }
388 :
389 1 : TEST_F(TranspilerTest, TranspileUnnestArrayBenchShape) {
390 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
391 1 : "SELECT COUNT(*) AS cnt, SUM(x) AS total FROM arr_table, "
392 1 : "UNNEST(arr_table.arr) AS x");
393 1 : ASSERT_NE(stmt, nullptr);
394 1 : TestTranspiler t;
395 1 : std::string sql = t.Transpile(stmt);
396 2 : ASSERT_FALSE(sql.empty()) << "transpile failed";
397 2 : EXPECT_NE(sql.find("unnest("), std::string::npos) << sql;
398 2 : EXPECT_NE(sql.find("__bq_l"), std::string::npos) << sql;
399 2 : EXPECT_NE(sql.find("\"cnt\""), std::string::npos) << sql;
400 1 : }
401 :
402 1 : TEST_F(TranspilerTest, TranspileCrossJoinUnnestSelect) {
403 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
404 1 : "SELECT n, m FROM UNNEST(GENERATE_ARRAY(1, 10)) AS n "
405 1 : "CROSS JOIN UNNEST(GENERATE_ARRAY(1, 2)) AS m");
406 1 : ASSERT_NE(stmt, nullptr);
407 1 : TestTranspiler t;
408 1 : std::string sql = t.Transpile(stmt);
409 2 : ASSERT_FALSE(sql.empty()) << "cross join unnest select must transpile";
410 2 : EXPECT_NE(sql.find("CROSS JOIN"), std::string::npos) << sql;
411 1 : }
412 :
413 1 : TEST_F(TranspilerTest, TranspileCrossJoinUnnestComputedSelect) {
414 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
415 1 : "SELECT n + (m - 1) * 1000000 AS id "
416 1 : "FROM UNNEST(GENERATE_ARRAY(1, 10)) AS n "
417 1 : "CROSS JOIN UNNEST(GENERATE_ARRAY(1, 2)) AS m");
418 1 : ASSERT_NE(stmt, nullptr);
419 1 : TestTranspiler t;
420 1 : std::string sql = t.Transpile(stmt);
421 2 : ASSERT_FALSE(sql.empty()) << "computed cross join unnest must transpile";
422 2 : EXPECT_NE(sql.find("CROSS JOIN"), std::string::npos) << sql;
423 1 : }
424 :
425 : } // namespace transpiler
426 : } // namespace duckdb
427 : } // namespace engine
428 : } // namespace backend
429 : } // namespace bigquery_emulator
|
