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, EmitAggregateScanCountStarNoGroupBy) {
10 : // `SELECT COUNT(*) FROM people` analyzes to an AggregateScan
11 : // with an empty group_by_list and a single aggregate. The
12 : // aggregate column gets a synthesized name (`$agg1`); we assert
13 : // on it so any drift in the analyzer's naming surfaces here
14 : // rather than in the engine integration.
15 1 : const ::googlesql::ResolvedStatement* stmt =
16 1 : Analyze("SELECT COUNT(*) FROM people");
17 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
18 1 : ASSERT_NE(scan, nullptr);
19 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_AGGREGATE_SCAN);
20 1 : TestTranspiler t;
21 1 : EXPECT_EQ(
22 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>()),
23 1 : "SELECT COUNT(*) AS \"$agg1\" FROM (SELECT \"id\", \"name\" FROM "
24 1 : "\"people\")");
25 1 : }
26 :
27 1 : TEST_F(TranspilerTest, EmitAggregateScanSumGroupByColumn) {
28 : // SUM over a grouped column threads the column-ref through the
29 : // GROUP BY clause and the SELECT list. The grouping column's
30 : // ResolvedColumn::name() matches its source name (`id`), so the
31 : // AS alias collapses to the column reference.
32 1 : const ::googlesql::ResolvedStatement* stmt =
33 1 : Analyze("SELECT id, SUM(id) FROM people GROUP BY id");
34 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
35 1 : ASSERT_NE(scan, nullptr);
36 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_AGGREGATE_SCAN);
37 1 : TestTranspiler t;
38 1 : EXPECT_EQ(
39 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>()),
40 1 : "SELECT \"id\", SUM(\"id\") AS \"$agg1\" FROM (SELECT \"id\", "
41 1 : "\"name\" FROM \"people\") GROUP BY \"id\"");
42 1 : }
43 :
44 1 : TEST_F(TranspilerTest, EmitAggregateScanAvgMinMaxGroupBy) {
45 : // All three of AVG / MIN / MAX share the same emit path; one
46 : // test covers the lot. The output column for each aggregate is
47 : // again the analyzer-synthesized `$agg<n>` name.
48 1 : const ::googlesql::ResolvedStatement* stmt =
49 1 : Analyze("SELECT id, AVG(id), MIN(id), MAX(id) FROM people GROUP BY id");
50 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
51 1 : ASSERT_NE(scan, nullptr);
52 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_AGGREGATE_SCAN);
53 1 : TestTranspiler t;
54 1 : EXPECT_EQ(
55 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>()),
56 1 : "SELECT \"id\", AVG(\"id\") AS \"$agg1\", MIN(\"id\") AS \"$agg2\", "
57 1 : "MAX(\"id\") AS \"$agg3\" FROM (SELECT \"id\", \"name\" FROM "
58 1 : "\"people\") GROUP BY \"id\"");
59 1 : }
60 :
61 1 : TEST_F(TranspilerTest, EmitAggregateScanArrayAggMapsThroughTable) {
62 : // `ARRAY_AGG` is in the disposition table (`array_agg: ARRAY_AGG`),
63 : // so the lower path emits the DuckDB aggregate verbatim. This
64 : // exercises the table-driven dispatch from inside the AggregateScan
65 : // emit -- a direct counterpart to `EmitFunctionCallMappedFunction`
66 : // above for the aggregate code path.
67 1 : const ::googlesql::ResolvedStatement* stmt =
68 1 : Analyze("SELECT id, ARRAY_AGG(id) FROM people GROUP BY id");
69 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
70 1 : ASSERT_NE(scan, nullptr);
71 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_AGGREGATE_SCAN);
72 1 : TestTranspiler t;
73 1 : EXPECT_EQ(
74 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>()),
75 1 : "SELECT \"id\", if(count(\"id\") < count(*), "
76 1 : "error('ARRAY_AGG: input value must be not null'), list(\"id\")) AS "
77 1 : "\"$agg1\" FROM (SELECT *, row_number() OVER () AS \"__bq_input_rn\" "
78 1 : "FROM (SELECT \"id\", \"name\" FROM \"people\")) GROUP BY \"id\"");
79 1 : }
80 :
81 1 : TEST_F(TranspilerTest, EmitAggregateScanArrayAggOrderByLimitRewritesToList) {
82 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
83 1 : "SELECT ARRAY_AGG(x ORDER BY x LIMIT 2) FROM UNNEST([3, 1, 2, 4]) AS x");
84 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
85 1 : ASSERT_NE(scan, nullptr);
86 1 : TestTranspiler t;
87 1 : std::string sql =
88 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>());
89 2 : EXPECT_NE(sql.find("list_slice(list(\"x\" ORDER BY"), std::string::npos)
90 2 : << sql;
91 2 : EXPECT_NE(sql.find(", 1, 2)"), std::string::npos) << sql;
92 1 : }
93 :
94 1 : TEST_F(TranspilerTest, EmitAggregateScanStringAggLimitRewritesToArrayToString) {
95 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
96 1 : "SELECT STRING_AGG(fruit, ' & ' LIMIT 2) FROM "
97 1 : "UNNEST(['apple', 'pear', 'banana']) AS fruit");
98 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
99 1 : ASSERT_NE(scan, nullptr);
100 1 : TestTranspiler t;
101 1 : std::string sql =
102 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>());
103 2 : EXPECT_NE(sql.find("array_to_string(list_slice(list("), std::string::npos)
104 2 : << sql;
105 2 : EXPECT_NE(sql.find(", ' & ')"), std::string::npos) << sql;
106 1 : }
107 :
108 1 : TEST_F(TranspilerTest, EmitAggregateScanStringAggOrderByLimit) {
109 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
110 1 : "SELECT STRING_AGG(x, ',' ORDER BY x ASC LIMIT 2) FROM "
111 1 : "UNNEST(['c', 'a', 'b']) AS x");
112 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
113 1 : ASSERT_NE(scan, nullptr);
114 1 : TestTranspiler t;
115 1 : std::string sql =
116 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>());
117 2 : EXPECT_NE(sql.find("array_to_string(list_slice(list("), std::string::npos)
118 2 : << sql;
119 2 : EXPECT_NE(sql.find("ORDER BY \"x\" ASC"), std::string::npos) << sql;
120 1 : }
121 :
122 1 : TEST_F(TranspilerTest, EmitAggregateScanFallsBackOnUnsupportedAggregate) {
123 : // `APPROX_QUANTILES` is on the `unsupported` route per
124 : // `docs/ENGINE_POLICY.md`; the aggregate emit returns
125 : // "" and the AggregateScan emit propagates the empty string. The
126 : // engine surfaces UNIMPLEMENTED for the whole query.
127 1 : const ::googlesql::ResolvedStatement* stmt =
128 1 : Analyze("SELECT id, APPROX_QUANTILES(id, 2) FROM people GROUP BY id");
129 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
130 1 : ASSERT_NE(scan, nullptr);
131 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_AGGREGATE_SCAN);
132 1 : TestTranspiler t;
133 1 : EXPECT_EQ(
134 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>()),
135 1 : "");
136 1 : }
137 :
138 : // --- GROUPING SETS / ROLLUP / CUBE / GROUPING() ------------------------
139 : //
140 : // `docs/ENGINE_POLICY.md` Family 1. Each shape exercises
141 : // the `grouping_set_list` path in `EmitAggregateScan`:
142 : //
143 : // * Explicit GROUPING SETS -> `(a, b)`, `(a)`, `()` entries.
144 : // * ROLLUP -> single ROLLUP entry expands to N+1
145 : // grouping sets via `ResolvedRollup`.
146 : // * CUBE -> single CUBE entry expands to 2^N
147 : // grouping sets via `ResolvedCube`.
148 : // * GROUPING(<col>) -> `ResolvedGroupingCall` in
149 : // `grouping_call_list`; projects as
150 : // `GROUPING(<col>) AS "<output>"`.
151 :
152 1 : TEST_F(TranspilerTest, EmitAggregateScanGroupingSetsExplicit) {
153 : // `GROUP BY GROUPING SETS ((id, name), (id), ())` analyzes with
154 : // three `ResolvedGroupingSet` entries in `grouping_set_list`.
155 : // The emit lands as `GROUP BY GROUPING SETS ((<a>, <b>), (<a>),
156 : // ())`, with each grouping-set tuple referencing the SELECT-list
157 : // aliases for `group_by_list` columns. DuckDB resolves the alias
158 : // inside GROUP BY GROUPING SETS, so the emitted SQL is
159 : // self-contained.
160 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
161 1 : "SELECT id, name, COUNT(*) FROM people "
162 1 : "GROUP BY GROUPING SETS ((id, name), (id), ())");
163 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
164 1 : ASSERT_NE(scan, nullptr);
165 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_AGGREGATE_SCAN);
166 1 : TestTranspiler t;
167 1 : std::string sql =
168 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>());
169 2 : EXPECT_NE(sql.find(" GROUP BY GROUPING SETS ("), std::string::npos)
170 2 : << "expected GROUP BY GROUPING SETS keyword; got: " << sql;
171 2 : EXPECT_NE(sql.find("(\"id\", \"name\")"), std::string::npos)
172 2 : << "expected (id, name) tuple; got: " << sql;
173 2 : EXPECT_NE(sql.find("(\"id\")"), std::string::npos)
174 2 : << "expected (id) tuple; got: " << sql;
175 2 : EXPECT_NE(sql.find("()"), std::string::npos)
176 2 : << "expected empty () tuple; got: " << sql;
177 1 : }
178 :
179 1 : TEST_F(TranspilerTest, EmitAggregateScanRollupAnalyzerExpandsToSets) {
180 : // The analyzer canonicalizes `GROUP BY ROLLUP(id, name)` into
181 : // three `ResolvedGroupingSet` entries -- `(id, name), (id), ()`
182 : // -- in `grouping_set_list`, not a single `ResolvedRollup`. Pin
183 : // the expanded form here so the user-visible BigQuery semantics
184 : // (one row per ROLLUP step) are exercised end-to-end on the
185 : // DuckDB fast path. The `ResolvedRollup` direct-construction
186 : // test below pins the keyword emit code path for shapes that
187 : // upstream builders could produce.
188 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
189 1 : "SELECT id, name, COUNT(*) FROM people GROUP BY ROLLUP(id, name)");
190 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
191 1 : ASSERT_NE(scan, nullptr);
192 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_AGGREGATE_SCAN);
193 1 : TestTranspiler t;
194 1 : std::string sql =
195 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>());
196 2 : EXPECT_NE(sql.find("GROUP BY GROUPING SETS ("), std::string::npos)
197 2 : << "expected GROUP BY GROUPING SETS keyword; got: " << sql;
198 2 : EXPECT_NE(sql.find("(\"id\", \"name\")"), std::string::npos)
199 2 : << "expected (id, name) leaf; got: " << sql;
200 2 : EXPECT_NE(sql.find("(\"id\")"), std::string::npos)
201 2 : << "expected (id) subtotal; got: " << sql;
202 2 : EXPECT_NE(sql.find("()"), std::string::npos)
203 2 : << "expected () grand-total tuple; got: " << sql;
204 1 : }
205 :
206 1 : TEST_F(TranspilerTest, EmitAggregateScanCubeAnalyzerExpandsToSets) {
207 : // CUBE(id, name) canonicalizes to 2^2 = 4 grouping sets:
208 : // `(id, name), (id), (name), ()`. Same shape as the ROLLUP test
209 : // above; here we pin the full fan-out so a partial-emit
210 : // regression in `EmitGroupingSetEntry` shows up as a missing
211 : // tuple.
212 1 : const ::googlesql::ResolvedStatement* stmt =
213 1 : Analyze("SELECT id, name, COUNT(*) FROM people GROUP BY CUBE(id, name)");
214 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
215 1 : ASSERT_NE(scan, nullptr);
216 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_AGGREGATE_SCAN);
217 1 : TestTranspiler t;
218 1 : std::string sql =
219 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>());
220 2 : EXPECT_NE(sql.find("(\"id\", \"name\")"), std::string::npos) << sql;
221 2 : EXPECT_NE(sql.find("(\"id\")"), std::string::npos) << sql;
222 2 : EXPECT_NE(sql.find("(\"name\")"), std::string::npos) << sql;
223 2 : EXPECT_NE(sql.find("()"), std::string::npos) << sql;
224 1 : }
225 :
226 : // --- PIVOT / UNPIVOT -----------------------------------------------------
227 : //
228 : // `EmitPivotScan` lowers a BigQuery `PIVOT(<agg>(...) FOR <expr> IN
229 : // (<vals>))` to DuckDB conditional aggregation using `FILTER`. Each
230 : // `ResolvedPivotColumn` carries the (pivot_expr_index,
231 : // pivot_value_index) tuple plus the analyzer-chosen output column
232 : // name, which the emit aliases onto a `<agg> FILTER (WHERE
233 : // <for_expr> = <pivot_value>)` projection.
234 : //
235 : // `EmitUnpivotScan` lowers `UNPIVOT(<value_cols> FOR <label_col> IN
236 : // (<arg_groups>))` to a UNION ALL of per-arg SELECTs. Each branch
237 : // projects the input columns that pass through unchanged, renames
238 : // the arg's column refs to the value-column names, and adds the
239 : // arg's label literal under `label_column`. When `include_nulls()`
240 : // is false (the BigQuery default), each branch adds a
241 : // `WHERE NOT (val0 IS NULL AND ... AND valN IS NULL)` filter so the
242 : // EXCLUDE NULLS semantics match BigQuery.
243 :
244 1 : TEST_F(TranspilerTest, EmitPivotScanBuildsFilterAggregates) {
245 : // `SUM(amount) FOR kind IN ('A', 'B')` yields one `ResolvedPivotColumn`
246 : // per (pivot_expr_index=0, pivot_value_index=v) pair. The emit
247 : // projects each as `SUM("amount") FILTER (WHERE "kind" = '<v>')`
248 : // aliased to the analyzer-chosen output column name.
249 1 : const ::googlesql::ResolvedStatement* stmt =
250 1 : Analyze("SELECT * FROM sales PIVOT(SUM(amount) FOR kind IN ('A', 'B'))");
251 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
252 1 : ASSERT_NE(scan, nullptr);
253 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_PIVOT_SCAN);
254 1 : TestTranspiler t;
255 1 : std::string sql =
256 1 : t.EmitPivotScan(scan->GetAs<::googlesql::ResolvedPivotScan>());
257 2 : EXPECT_NE(sql.find("SUM(\"amount\") FILTER (WHERE \"kind\" = 'A')"),
258 2 : std::string::npos)
259 2 : << "expected SUM FILTER for 'A'; got: " << sql;
260 2 : EXPECT_NE(sql.find("SUM(\"amount\") FILTER (WHERE \"kind\" = 'B')"),
261 2 : std::string::npos)
262 2 : << "expected SUM FILTER for 'B'; got: " << sql;
263 2 : EXPECT_NE(sql.find(" GROUP BY \"region\""), std::string::npos)
264 2 : << "expected GROUP BY on the pass-through grouping column; got: " << sql;
265 1 : }
266 :
267 1 : TEST_F(TranspilerTest, EmitPivotScanMultipleAggregates) {
268 : // Two pivot expressions x two pivot values = four output columns.
269 : // The analyzer reuses one pivot_expr_list / pivot_value_list and
270 : // tags each output column with (expr_index, value_index); the emit
271 : // pulls the cached aggregate SQL for each index and pairs it with
272 : // the matching value's filter clause.
273 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
274 1 : "SELECT * FROM sales "
275 1 : "PIVOT(SUM(amount) AS s, COUNT(*) AS c FOR kind IN ('A', 'B'))");
276 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
277 1 : ASSERT_NE(scan, nullptr);
278 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_PIVOT_SCAN);
279 1 : TestTranspiler t;
280 1 : std::string sql =
281 1 : t.EmitPivotScan(scan->GetAs<::googlesql::ResolvedPivotScan>());
282 2 : EXPECT_NE(sql.find("SUM(\"amount\") FILTER (WHERE \"kind\" = 'A')"),
283 2 : std::string::npos)
284 2 : << sql;
285 2 : EXPECT_NE(sql.find("SUM(\"amount\") FILTER (WHERE \"kind\" = 'B')"),
286 2 : std::string::npos)
287 2 : << sql;
288 2 : EXPECT_NE(sql.find("COUNT(*) FILTER (WHERE \"kind\" = 'A')"),
289 2 : std::string::npos)
290 2 : << sql;
291 2 : EXPECT_NE(sql.find("COUNT(*) FILTER (WHERE \"kind\" = 'B')"),
292 2 : std::string::npos)
293 2 : << sql;
294 1 : }
295 :
296 1 : TEST_F(TranspilerTest, EmitUnpivotScanExcludeNullsByDefault) {
297 : // BigQuery's default UNPIVOT semantic (EXCLUDE NULLS). The emit
298 : // expands each input row via CROSS JOIN LATERAL (VALUES ...); the
299 : // outer WHERE filters out rows where every value column is NULL.
300 : // The label column is the analyzer's string label_list[i].
301 1 : const ::googlesql::ResolvedStatement* stmt =
302 1 : Analyze("SELECT * FROM wide UNPIVOT(value FOR quarter IN (q1, q2))");
303 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
304 1 : ASSERT_NE(scan, nullptr);
305 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_UNPIVOT_SCAN);
306 1 : TestTranspiler t;
307 1 : std::string sql =
308 1 : t.EmitUnpivotScan(scan->GetAs<::googlesql::ResolvedUnpivotScan>());
309 2 : EXPECT_NE(sql.find(" CROSS JOIN LATERAL (VALUES "), std::string::npos)
310 2 : << "expected LATERAL VALUES unpivot expansion; got: " << sql;
311 2 : EXPECT_NE(sql.find("'q1'"), std::string::npos)
312 2 : << "expected q1 label in VALUES tuple; got: " << sql;
313 2 : EXPECT_NE(sql.find("'q2'"), std::string::npos)
314 2 : << "expected q2 label in VALUES tuple; got: " << sql;
315 2 : EXPECT_NE(sql.find("WHERE NOT (u.\"value\" IS NULL)"), std::string::npos)
316 2 : << "expected EXCLUDE NULLS filter; got: " << sql;
317 1 : }
318 :
319 1 : TEST_F(TranspilerTest, EmitWithScanRecursiveHierarchyTraversal) {
320 : // Mirrors `conformance/fixtures/advanced_relational/recursive_cte.yaml`:
321 : // anchor arm projects a literal depth column (`0 AS depth`) whose
322 : // analyzer name is a synthesized `$colN`; the recursive arm joins
323 : // the CTE ref back to `org` and increments depth.
324 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
325 1 : "WITH RECURSIVE descents AS ("
326 1 : " SELECT employee, 0 AS depth FROM org WHERE manager IS NULL"
327 1 : " UNION ALL"
328 1 : " SELECT org.employee, depth + 1"
329 1 : " FROM descents JOIN org AS org ON org.manager = descents.employee"
330 1 : ")"
331 1 : "SELECT employee, depth FROM descents ORDER BY depth, employee");
332 1 : ASSERT_NE(stmt, nullptr);
333 1 : TestTranspiler t;
334 1 : std::string sql = t.Transpile(stmt);
335 2 : EXPECT_NE(sql.find("WITH RECURSIVE \"descents\""), std::string::npos) << sql;
336 2 : EXPECT_NE(sql.find("\"_cte_0\""), std::string::npos) << sql;
337 2 : EXPECT_NE(sql.find("\"_cte_1\""), std::string::npos) << sql;
338 2 : EXPECT_NE(sql.find(" UNION ALL "), std::string::npos) << sql;
339 2 : EXPECT_NE(sql.find("FROM \"descents\""), std::string::npos) << sql;
340 2 : EXPECT_NE(sql.find("\"employee\" AS \"_cte_0\""), std::string::npos) << sql;
341 2 : EXPECT_NE(sql.find("\"depth\" AS \"_cte_1\""), std::string::npos) << sql;
342 1 : }
343 :
344 1 : TEST_F(TranspilerTest, EmitWithScanRecursiveLowersToWithRecursive) {
345 : // `docs/ENGINE_POLICY.md` Family 4. A `WITH
346 : // RECURSIVE t AS (SELECT 1 AS n UNION ALL SELECT n FROM t) ...`
347 : // lowers to DuckDB's `WITH RECURSIVE`. The transpiler stages a
348 : // per-CTE context with stable anchor column names (`_cte_0`),
349 : // renames each arm's output columns onto those anchors, and
350 : // unions them; the recursive-arm `ResolvedRecursiveRefScan`
351 : // projects from the anchor names back to the analyzer's per-ref
352 : // column names.
353 : //
354 : // The recursive arm here is just `SELECT n FROM t` (no
355 : // `WHERE ... < ...`) so the test does not depend on the `$less`
356 : // operator's disposition entry, which is owned by a later
357 : // function-dispatch plan.
358 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
359 1 : "WITH RECURSIVE t AS ("
360 1 : " SELECT 1 AS n"
361 1 : " UNION ALL"
362 1 : " SELECT n FROM t"
363 1 : ")"
364 1 : "SELECT n FROM t");
365 1 : ASSERT_NE(stmt, nullptr);
366 1 : TestTranspiler t;
367 1 : std::string sql = t.Transpile(stmt);
368 2 : EXPECT_NE(sql.find("WITH RECURSIVE \"t\""), std::string::npos)
369 2 : << "expected WITH RECURSIVE keyword; got: " << sql;
370 2 : EXPECT_NE(sql.find("\"_cte_0\""), std::string::npos)
371 2 : << "expected anchor column name; got: " << sql;
372 2 : EXPECT_NE(sql.find(" UNION ALL "), std::string::npos)
373 2 : << "expected UNION ALL between anchor and recursive arm; got: " << sql;
374 2 : EXPECT_NE(sql.find("FROM \"t\""), std::string::npos)
375 2 : << "expected recursive ref to read FROM the CTE; got: " << sql;
376 1 : }
377 :
378 1 : TEST_F(TranspilerTest, EmitUnpivotScanIncludeNullsSkipsFilter) {
379 : // `INCLUDE NULLS` flips `include_nulls()` true; the outer query
380 : // should not carry the WHERE filter.
381 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
382 1 : "SELECT * FROM wide "
383 1 : "UNPIVOT INCLUDE NULLS (value FOR quarter IN (q1, q2))");
384 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
385 1 : ASSERT_NE(scan, nullptr);
386 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_UNPIVOT_SCAN);
387 1 : TestTranspiler t;
388 1 : std::string sql =
389 1 : t.EmitUnpivotScan(scan->GetAs<::googlesql::ResolvedUnpivotScan>());
390 2 : EXPECT_EQ(sql.find("WHERE NOT"), std::string::npos)
391 2 : << "INCLUDE NULLS should omit the WHERE filter; got: " << sql;
392 2 : EXPECT_NE(sql.find(" CROSS JOIN LATERAL (VALUES "), std::string::npos) << sql;
393 1 : }
394 :
395 1 : TEST_F(TranspilerTest, EmitAggregateScanGroupingCallProjectsBitMask) {
396 : // `SELECT GROUPING(id), ... FROM ... GROUP BY ROLLUP(id, name)`
397 : // creates a `ResolvedGroupingCall` in `grouping_call_list`. The
398 : // emit projects it as `GROUPING("id") AS "<output>"` so the
399 : // SELECT list exposes the bit at the analyzer-chosen output
400 : // column name.
401 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
402 1 : "SELECT id, name, GROUPING(id), COUNT(*) FROM people "
403 1 : "GROUP BY ROLLUP(id, name)");
404 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
405 1 : ASSERT_NE(scan, nullptr);
406 1 : ASSERT_EQ(scan->node_kind(), ::googlesql::RESOLVED_AGGREGATE_SCAN);
407 1 : TestTranspiler t;
408 1 : std::string sql =
409 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>());
410 2 : EXPECT_NE(sql.find("GROUPING(\"id\") AS "), std::string::npos)
411 2 : << "expected GROUPING projection; got: " << sql;
412 1 : }
413 :
414 1 : TEST_F(TranspilerTest, EmitAggregateScanSumFilterClause) {
415 1 : const ::googlesql::ResolvedStatement* stmt =
416 1 : Analyze("SELECT SUM(amount WHERE kind = 'A') FROM sales");
417 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
418 1 : ASSERT_NE(scan, nullptr);
419 1 : TestTranspiler t;
420 1 : std::string sql =
421 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>());
422 2 : EXPECT_NE(sql.find("SUM(\"amount\") FILTER (WHERE"), std::string::npos)
423 2 : << sql;
424 2 : EXPECT_NE(sql.find("\"kind\" = 'A'"), std::string::npos) << sql;
425 1 : }
426 :
427 1 : TEST_F(TranspilerTest, EmitAggregateScanArrayAggIgnoreNulls) {
428 1 : const ::googlesql::ResolvedStatement* stmt = Analyze(
429 1 : "SELECT ARRAY_AGG(x IGNORE NULLS) FROM UNNEST([1, NULL, 2]) AS x");
430 1 : const ::googlesql::ResolvedScan* scan = QueryInputScan(stmt);
431 1 : ASSERT_NE(scan, nullptr);
432 1 : TestTranspiler t;
433 1 : std::string sql =
434 1 : t.EmitAggregateScan(scan->GetAs<::googlesql::ResolvedAggregateScan>());
435 2 : EXPECT_NE(sql.find("FILTER (WHERE \"x\" IS NOT NULL)"), std::string::npos)
436 2 : << sql;
437 1 : }
438 :
439 : // --- Order By -----------------------------------------------------------
440 :
441 : } // namespace transpiler
442 : } // namespace duckdb
443 : } // namespace engine
444 : } // namespace backend
445 : } // namespace bigquery_emulator
|
