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, TranspileTimestampAddLiteralInterval) {
367 : // Analyzer keeps `$interval(7, DAY)` (DAY part enum = 3) rather than
368 : // folding to a TYPE_INTERVAL literal for this shape.
369 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
370 1 : "SELECT TIMESTAMP_ADD(timestamp, INTERVAL 7 DAY) AS t2 "
371 1 : "FROM transactions");
372 1 : ASSERT_NE(stmt, nullptr);
373 1 : TestTranspiler t;
374 1 : std::string sql = t.Transpile(stmt);
375 2 : ASSERT_FALSE(sql.empty()) << "TIMESTAMP_ADD literal interval must transpile";
376 2 : EXPECT_NE(sql.find("bq_timestamp_add"), std::string::npos) << sql;
377 2 : EXPECT_NE(sql.find(", 7, 3)"), std::string::npos) << sql;
378 1 : }
379 :
380 1 : TEST_F(TranspilerTest, TranspileTimestampSubColumnAmountInterval) {
381 : // Non-literal amount keeps the `$interval(amount, part)` shape; the
382 : // emit forwards the amount SQL with the part enum (HOUR = 7).
383 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
384 1 : "SELECT TIMESTAMP_SUB(t.timestamp, INTERVAL p.id HOUR) AS t2 "
385 1 : "FROM transactions t CROSS JOIN people p");
386 1 : ASSERT_NE(stmt, nullptr);
387 1 : TestTranspiler t;
388 1 : std::string sql = t.Transpile(stmt);
389 2 : ASSERT_FALSE(sql.empty()) << "TIMESTAMP_SUB column interval must transpile";
390 2 : EXPECT_NE(sql.find("bq_timestamp_sub"), std::string::npos) << sql;
391 2 : EXPECT_NE(sql.find(", 7)"), std::string::npos) << sql;
392 1 : }
393 :
394 1 : TEST_F(TranspilerTest, TranspileNotInStringList) {
395 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
396 1 : "SELECT origin FROM transactions "
397 1 : "WHERE origin NOT IN ('status_x', 'status_y')");
398 1 : ASSERT_NE(stmt, nullptr);
399 1 : TestTranspiler t;
400 1 : std::string sql = t.Transpile(stmt);
401 2 : ASSERT_FALSE(sql.empty()) << "value-list NOT IN must transpile";
402 2 : EXPECT_NE(sql.find(" IN ('status_x', 'status_y')"), std::string::npos) << sql;
403 2 : EXPECT_NE(sql.find("NOT "), std::string::npos) << sql;
404 1 : }
405 :
406 1 : TEST_F(TranspilerTest, TranspileCoalesceAroundCountStar) {
407 : // Analyzer wraps the deferred aggregate in `$with_side_effects`;
408 : // emission must strip the companion and keep COALESCE(COUNT(*), 0).
409 1 : const ::googlesql::ResolvedStatement* stmt =
410 1 : Analyze("SELECT COALESCE(COUNT(*), 0) AS c FROM transactions");
411 1 : ASSERT_NE(stmt, nullptr);
412 1 : TestTranspiler t;
413 1 : std::string sql = t.Transpile(stmt);
414 2 : ASSERT_FALSE(sql.empty()) << "COALESCE(COUNT(*),0) must transpile";
415 2 : EXPECT_NE(sql.find("COALESCE"), std::string::npos) << sql;
416 2 : EXPECT_NE(sql.find("COUNT(*)"), std::string::npos)
417 2 : << "expected DuckDB COUNT(*): " << sql;
418 2 : EXPECT_EQ(sql.find("with_side_effects"), std::string::npos) << sql;
419 1 : }
420 :
421 1 : TEST_F(TranspilerTest, TranspileDateFuncsBenchShape) {
422 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
423 1 : "SELECT EXTRACT(YEAR FROM DATE_ADD(DATE '2020-01-01', INTERVAL id "
424 1 : "DAY)) AS yr, COUNT(*) AS cnt FROM people GROUP BY yr ORDER BY yr");
425 1 : ASSERT_NE(stmt, nullptr);
426 1 : TestTranspiler t;
427 1 : std::string sql = t.Transpile(stmt);
428 2 : ASSERT_FALSE(sql.empty()) << "transpile failed";
429 2 : EXPECT_NE(sql.find("bq_date_add"), std::string::npos) << sql;
430 2 : EXPECT_NE(sql.find("bq_extract"), std::string::npos) << sql;
431 2 : EXPECT_EQ(sql.find(" DAY"), std::string::npos) << sql;
432 1 : }
433 :
434 1 : TEST_F(TranspilerTest, TranspileFloatSumCastsAggregateToDouble) {
435 1 : const ::googlesql::ResolvedStatement* stmt =
436 1 : Analyze("SELECT SUM(CAST(id AS FLOAT64)) AS total FROM people");
437 1 : ASSERT_NE(stmt, nullptr);
438 1 : TestTranspiler t;
439 1 : std::string sql = t.Transpile(stmt);
440 2 : ASSERT_FALSE(sql.empty()) << "transpile failed";
441 2 : EXPECT_NE(sql.find("SUM("), std::string::npos) << sql;
442 1 : }
443 :
444 1 : TEST_F(TranspilerTest, TranspileUnnestArrayBenchShape) {
445 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
446 1 : "SELECT COUNT(*) AS cnt, SUM(x) AS total FROM arr_table, "
447 1 : "UNNEST(arr_table.arr) AS x");
448 1 : ASSERT_NE(stmt, nullptr);
449 1 : TestTranspiler t;
450 1 : std::string sql = t.Transpile(stmt);
451 2 : ASSERT_FALSE(sql.empty()) << "transpile failed";
452 2 : EXPECT_NE(sql.find("unnest("), std::string::npos) << sql;
453 2 : EXPECT_NE(sql.find("__bq_l"), std::string::npos) << sql;
454 2 : EXPECT_NE(sql.find("\"cnt\""), std::string::npos) << sql;
455 1 : }
456 :
457 1 : TEST_F(TranspilerTest, TranspileCrossJoinUnnestSelect) {
458 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
459 1 : "SELECT n, m FROM UNNEST(GENERATE_ARRAY(1, 10)) AS n "
460 1 : "CROSS JOIN UNNEST(GENERATE_ARRAY(1, 2)) AS m");
461 1 : ASSERT_NE(stmt, nullptr);
462 1 : TestTranspiler t;
463 1 : std::string sql = t.Transpile(stmt);
464 2 : ASSERT_FALSE(sql.empty()) << "cross join unnest select must transpile";
465 2 : EXPECT_NE(sql.find("CROSS JOIN"), std::string::npos) << sql;
466 1 : }
467 :
468 1 : TEST_F(TranspilerTest, TranspileCrossJoinUnnestComputedSelect) {
469 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
470 1 : "SELECT n + (m - 1) * 1000000 AS id "
471 1 : "FROM UNNEST(GENERATE_ARRAY(1, 10)) AS n "
472 1 : "CROSS JOIN UNNEST(GENERATE_ARRAY(1, 2)) AS m");
473 1 : ASSERT_NE(stmt, nullptr);
474 1 : TestTranspiler t;
475 1 : std::string sql = t.Transpile(stmt);
476 2 : ASSERT_FALSE(sql.empty()) << "computed cross join unnest must transpile";
477 2 : EXPECT_NE(sql.find("CROSS JOIN"), std::string::npos) << sql;
478 1 : }
479 :
480 : } // namespace transpiler
481 : } // namespace duckdb
482 : } // namespace engine
483 : } // namespace backend
484 : } // namespace bigquery_emulator
|
