fraiseql_core/compiler/window_functions/codegen.rs
1use super::{
2 FactTableMetadata, FraiseQLError, OrderByClause, PartitionByColumn, Result, SelectColumn,
3 WindowExecutionPlan, WindowFunction, WindowFunctionRequest, WindowFunctionSpec,
4 WindowFunctionType, WindowOrderBy, WindowRequest, WindowSelectColumn,
5};
6use crate::compiler::window_allowlist::WindowAllowlist;
7
8/// The output aliases visible to an **in-window** clause: none.
9///
10/// A window function's `OVER (…)` runs before any window column exists, so it
11/// cannot order by — or read — a sibling's alias. Only the final ORDER BY sees
12/// them. Naming the empty set makes that asymmetry explicit at each call site
13/// rather than leaving it implicit in an argument that happens to be empty.
14static NO_OUTPUT_ALIASES: std::sync::LazyLock<std::collections::HashSet<String>> =
15 std::sync::LazyLock::new(std::collections::HashSet::new);
16
17// =============================================================================
18// WindowPlanner - Converts high-level WindowRequest to WindowExecutionPlan
19// =============================================================================
20
21/// High-level window planner that validates semantic names against metadata.
22///
23/// Converts `WindowRequest` (user-friendly semantic names) to `WindowExecutionPlan`
24/// (SQL expressions ready for execution).
25///
26/// # Example
27///
28/// ```text
29/// let request = WindowRequest { ... };
30/// let metadata = FactTableMetadata { ... };
31/// let plan = WindowPlanner::plan(request, &metadata)?;
32/// // plan now has SQL expressions like "dimensions->>'category'" instead of "category"
33/// ```
34pub struct WindowPlanner;
35
36impl WindowPlanner {
37 /// Convert high-level `WindowRequest` to executable `WindowExecutionPlan`.
38 ///
39 /// # Arguments
40 ///
41 /// * `request` - High-level window request with semantic names
42 /// * `metadata` - Fact table metadata for validation and expression generation
43 ///
44 /// # Errors
45 ///
46 /// Returns error if:
47 /// - Referenced measures don't exist in metadata
48 /// - Referenced filter columns don't exist
49 /// - Window function field references are invalid
50 pub fn plan(
51 request: WindowRequest,
52 metadata: &FactTableMetadata,
53 ) -> Result<WindowExecutionPlan> {
54 // SECURITY (#795): the FROM target must be the fact table the root field already
55 // resolved, never the client's `table` key. Two separate channels selected the
56 // relation; only this one was checked, so a request could name any relation — or a
57 // whole subquery — and, because the RLS policy is looked up by that same name,
58 // naming an unpolicied table dropped the tenant filter entirely.
59 if request.table_name != metadata.table_name {
60 return Err(FraiseQLError::Validation {
61 message: format!(
62 "window query 'table' ({}) does not match the fact table resolved from the \
63 query field ({}); the relation is determined by the schema, not the request",
64 request.table_name, metadata.table_name
65 ),
66 path: None,
67 });
68 }
69
70 // SECURITY (#794): defence-in-depth on top of the unconditional charset checks
71 // below. Built here, in the planner the shipped binary actually calls — the
72 // allowlist existed but was consulted only by a second, unreachable planner
73 // (deleted in #881, which moved its test coverage onto this one).
74 let allowlist = WindowAllowlist::from_metadata(metadata);
75
76 // Convert select columns to SQL expressions
77 let select = Self::convert_select_columns(&request.select, metadata, &allowlist)?;
78
79 // Convert window functions to SQL expressions
80 let windows = Self::convert_window_functions(&request.windows, metadata, &allowlist)?;
81
82 // Convert final ORDER BY to SQL expressions
83 // The FINAL ORDER BY runs *after* the window functions, so every window
84 // and select alias is a real output column at that point and must be
85 // accepted (#1014). The in-window ORDER BY inside `OVER (…)` runs
86 // *before* they exist, so it gets the allowlist alone.
87 let output_aliases: std::collections::HashSet<String> = request
88 .windows
89 .iter()
90 .map(|w| w.alias.clone())
91 .chain(request.select.iter().map(|c| c.alias().to_string()))
92 .collect();
93 let order_by =
94 Self::convert_order_by(&request.order_by, metadata, &allowlist, &output_aliases)?;
95
96 Ok(WindowExecutionPlan {
97 // Use the resolved name, not the request's — belt and braces with the check above.
98 table: metadata.table_name.clone(),
99 select,
100 windows,
101 where_clause: request.where_clause,
102 order_by,
103 limit: request.limit,
104 offset: request.offset,
105 })
106 }
107
108 /// Convert semantic select columns to SQL expressions.
109 fn convert_select_columns(
110 columns: &[WindowSelectColumn],
111 metadata: &FactTableMetadata,
112 allowlist: &WindowAllowlist,
113 ) -> Result<Vec<SelectColumn>> {
114 columns
115 .iter()
116 .map(|col| Self::convert_single_select_column(col, metadata, allowlist))
117 .collect()
118 }
119
120 fn convert_single_select_column(
121 column: &WindowSelectColumn,
122 metadata: &FactTableMetadata,
123 allowlist: &WindowAllowlist,
124 ) -> Result<SelectColumn> {
125 // SECURITY (#794): every arm below emits `<expr> AS <alias>` verbatim, so the alias
126 // is validated once here rather than in each arm — the bug was that one arm carried
127 // a check its siblings did not.
128 Self::validate_alias(column.alias())?;
129 match column {
130 WindowSelectColumn::Measure { name, alias } => {
131 // Validate measure exists
132 if !metadata.measures.iter().any(|m| m.name == *name) {
133 return Err(FraiseQLError::Validation {
134 message: format!(
135 "Measure '{}' not found in fact table '{}'",
136 name, metadata.table_name
137 ),
138 path: None,
139 });
140 }
141 // Measure columns are direct SQL columns
142 Ok(SelectColumn {
143 expression: name.clone(),
144 alias: alias.clone(),
145 })
146 },
147 WindowSelectColumn::Dimension { path, alias } => {
148 // SECURITY (#794): `path` is interpolated as a single-quoted JSONB key, so an
149 // embedded quote breaks out of the literal and appends arbitrary SQL.
150 Self::validate_dimension_path(path, metadata, allowlist)?;
151 let expression = format!("{}->>'{}'", metadata.dimensions.name, path);
152 Ok(SelectColumn {
153 expression,
154 alias: alias.clone(),
155 })
156 },
157 WindowSelectColumn::Filter { name, alias } => {
158 // Validate filter column exists
159 if !metadata.denormalized_filters.iter().any(|f| f.name == *name) {
160 return Err(FraiseQLError::Validation {
161 message: format!(
162 "Filter column '{}' not found in fact table '{}'",
163 name, metadata.table_name
164 ),
165 path: None,
166 });
167 }
168 // Filter columns are direct SQL columns
169 Ok(SelectColumn {
170 expression: name.clone(),
171 alias: alias.clone(),
172 })
173 },
174 }
175 }
176
177 /// Convert semantic window functions to SQL expressions.
178 fn convert_window_functions(
179 windows: &[WindowFunctionRequest],
180 metadata: &FactTableMetadata,
181 allowlist: &WindowAllowlist,
182 ) -> Result<Vec<WindowFunction>> {
183 windows
184 .iter()
185 .map(|w| Self::convert_single_window_function(w, metadata, allowlist))
186 .collect()
187 }
188
189 fn convert_single_window_function(
190 request: &WindowFunctionRequest,
191 metadata: &FactTableMetadata,
192 allowlist: &WindowAllowlist,
193 ) -> Result<WindowFunction> {
194 // SECURITY (#794): emitted by `write!(sql, " AS {}", window.alias)`.
195 Self::validate_alias(&request.alias)?;
196
197 // Convert function spec to function type
198 let function = Self::convert_function_spec(&request.function, metadata, allowlist)?;
199
200 // Convert PARTITION BY columns to SQL expressions
201 let partition_by = request
202 .partition_by
203 .iter()
204 .map(|p| Self::convert_partition_by(p, metadata, allowlist))
205 .collect::<Result<Vec<_>>>()?;
206
207 // Convert ORDER BY within window to SQL expressions
208 let order_by = request
209 .order_by
210 .iter()
211 .map(|o| Self::convert_window_order_by(o, metadata, allowlist, &NO_OUTPUT_ALIASES))
212 .collect::<Result<Vec<_>>>()?;
213
214 Ok(WindowFunction {
215 function,
216 alias: request.alias.clone(),
217 partition_by,
218 order_by,
219 frame: request.frame.clone(),
220 })
221 }
222
223 /// Convert high-level function spec to low-level function type with SQL expressions.
224 fn convert_function_spec(
225 spec: &WindowFunctionSpec,
226 metadata: &FactTableMetadata,
227 allowlist: &WindowAllowlist,
228 ) -> Result<WindowFunctionType> {
229 match spec {
230 // Ranking functions - no field conversion needed
231 WindowFunctionSpec::RowNumber => Ok(WindowFunctionType::RowNumber),
232 WindowFunctionSpec::Rank => Ok(WindowFunctionType::Rank),
233 WindowFunctionSpec::DenseRank => Ok(WindowFunctionType::DenseRank),
234 WindowFunctionSpec::Ntile { n } => Ok(WindowFunctionType::Ntile { n: *n }),
235 WindowFunctionSpec::PercentRank => Ok(WindowFunctionType::PercentRank),
236 WindowFunctionSpec::CumeDist => Ok(WindowFunctionType::CumeDist),
237
238 // Value functions - need field conversion
239 WindowFunctionSpec::Lag {
240 field,
241 offset,
242 default,
243 } => {
244 let sql_field =
245 Self::resolve_field_to_sql(field, metadata, allowlist, &NO_OUTPUT_ALIASES)?;
246 Ok(WindowFunctionType::Lag {
247 field: sql_field,
248 offset: *offset,
249 default: default.clone(),
250 })
251 },
252 WindowFunctionSpec::Lead {
253 field,
254 offset,
255 default,
256 } => {
257 let sql_field =
258 Self::resolve_field_to_sql(field, metadata, allowlist, &NO_OUTPUT_ALIASES)?;
259 Ok(WindowFunctionType::Lead {
260 field: sql_field,
261 offset: *offset,
262 default: default.clone(),
263 })
264 },
265 WindowFunctionSpec::FirstValue { field } => {
266 let sql_field =
267 Self::resolve_field_to_sql(field, metadata, allowlist, &NO_OUTPUT_ALIASES)?;
268 Ok(WindowFunctionType::FirstValue { field: sql_field })
269 },
270 WindowFunctionSpec::LastValue { field } => {
271 let sql_field =
272 Self::resolve_field_to_sql(field, metadata, allowlist, &NO_OUTPUT_ALIASES)?;
273 Ok(WindowFunctionType::LastValue { field: sql_field })
274 },
275 WindowFunctionSpec::NthValue { field, n } => {
276 let sql_field =
277 Self::resolve_field_to_sql(field, metadata, allowlist, &NO_OUTPUT_ALIASES)?;
278 Ok(WindowFunctionType::NthValue {
279 field: sql_field,
280 n: *n,
281 })
282 },
283
284 // Aggregate as window functions - need measure conversion
285 WindowFunctionSpec::RunningSum { measure } => {
286 Self::validate_measure(measure, metadata)?;
287 Ok(WindowFunctionType::Sum {
288 field: measure.clone(),
289 })
290 },
291 WindowFunctionSpec::RunningAvg { measure } => {
292 Self::validate_measure(measure, metadata)?;
293 Ok(WindowFunctionType::Avg {
294 field: measure.clone(),
295 })
296 },
297 WindowFunctionSpec::RunningCount => Ok(WindowFunctionType::Count { field: None }),
298 WindowFunctionSpec::RunningCountField { field } => {
299 let sql_field =
300 Self::resolve_field_to_sql(field, metadata, allowlist, &NO_OUTPUT_ALIASES)?;
301 Ok(WindowFunctionType::Count {
302 field: Some(sql_field),
303 })
304 },
305 WindowFunctionSpec::RunningMin { measure } => {
306 Self::validate_measure(measure, metadata)?;
307 Ok(WindowFunctionType::Min {
308 field: measure.clone(),
309 })
310 },
311 WindowFunctionSpec::RunningMax { measure } => {
312 Self::validate_measure(measure, metadata)?;
313 Ok(WindowFunctionType::Max {
314 field: measure.clone(),
315 })
316 },
317 WindowFunctionSpec::RunningStddev { measure } => {
318 Self::validate_measure(measure, metadata)?;
319 Ok(WindowFunctionType::Stddev {
320 field: measure.clone(),
321 })
322 },
323 WindowFunctionSpec::RunningVariance { measure } => {
324 Self::validate_measure(measure, metadata)?;
325 Ok(WindowFunctionType::Variance {
326 field: measure.clone(),
327 })
328 },
329 }
330 }
331
332 /// Convert PARTITION BY column to SQL expression.
333 fn convert_partition_by(
334 partition: &PartitionByColumn,
335 metadata: &FactTableMetadata,
336 allowlist: &WindowAllowlist,
337 ) -> Result<String> {
338 match partition {
339 PartitionByColumn::Dimension { path } => {
340 // SECURITY (#794): the same unvalidated JSONB-key interpolation as the
341 // select arm. Both sinks now go through one entry point.
342 Self::validate_dimension_path(path, metadata, allowlist)?;
343 Ok(format!("{}->>'{}'", metadata.dimensions.name, path))
344 },
345 PartitionByColumn::Filter { name } => {
346 if !metadata.denormalized_filters.iter().any(|f| f.name == *name) {
347 return Err(FraiseQLError::Validation {
348 message: format!(
349 "Filter column '{}' not found in fact table '{}'",
350 name, metadata.table_name
351 ),
352 path: None,
353 });
354 }
355 Ok(name.clone())
356 },
357 PartitionByColumn::Measure { name } => {
358 Self::validate_measure(name, metadata)?;
359 Ok(name.clone())
360 },
361 }
362 }
363
364 /// Convert window ORDER BY to SQL expression.
365 fn convert_window_order_by(
366 order: &WindowOrderBy,
367 metadata: &FactTableMetadata,
368 allowlist: &WindowAllowlist,
369 output_aliases: &std::collections::HashSet<String>,
370 ) -> Result<OrderByClause> {
371 let field = Self::resolve_field_to_sql(&order.field, metadata, allowlist, output_aliases)?;
372 Ok(OrderByClause::new(field, order.direction))
373 }
374
375 /// Convert final ORDER BY to SQL expressions.
376 fn convert_order_by(
377 orders: &[WindowOrderBy],
378 metadata: &FactTableMetadata,
379 allowlist: &WindowAllowlist,
380 output_aliases: &std::collections::HashSet<String>,
381 ) -> Result<Vec<OrderByClause>> {
382 orders
383 .iter()
384 .map(|o| Self::convert_window_order_by(o, metadata, allowlist, output_aliases))
385 .collect()
386 }
387
388 /// Resolve a semantic field name to its SQL expression.
389 ///
390 /// Priority:
391 /// 1. Check if it's a measure (direct column)
392 /// 2. Check if it's a filter column (direct column)
393 /// 3. Treat as a dimension path (JSONB extraction) — only if the name is a valid GraphQL
394 /// identifier (`[_A-Za-z][_0-9A-Za-z]*`), to prevent SQL injection via the single-quoted key
395 /// in `data->>'field'` expressions.
396 fn resolve_field_to_sql(
397 field: &str,
398 metadata: &FactTableMetadata,
399 allowlist: &WindowAllowlist,
400 output_aliases: &std::collections::HashSet<String>,
401 ) -> Result<String> {
402 // Check if it's a measure
403 if metadata.measures.iter().any(|m| m.name == field) {
404 return Ok(field.to_string());
405 }
406
407 // Check if it's a filter column
408 if metadata.denormalized_filters.iter().any(|f| f.name == field) {
409 return Ok(field.to_string());
410 }
411
412 // A window or select alias — a real output column by the time the FINAL
413 // ORDER BY runs, and the whole ask of #1014. `output_aliases` is empty
414 // for the in-window clause, so a window function cannot order by its own
415 // sibling's alias inside `OVER (…)`, where the column does not yet exist.
416 //
417 // Emitted bare (`rank`), not as `dimensions->>'rank'` — which is NULL,
418 // which is what silently sorted nothing.
419 if output_aliases.contains(field) {
420 // Already vetted by `validate_alias` when the alias was accepted;
421 // re-checked here so this branch cannot become a new injection seam
422 // if the two ever drift.
423 Self::validate_alias(field)?;
424 return Ok(field.to_string());
425 }
426
427 // Validate identifier before embedding in JSONB extraction expression.
428 // Without this check, a field like "x'; DROP TABLE t; --" would produce
429 // `data->>'x'; DROP TABLE t; --'`, breaking the SQL structure.
430 Self::validate_field_identifier(field)?;
431
432 // Schema allowlist (#794/#1014): defence-in-depth *on top of* the charset
433 // check above, never a replacement for it. A no-op when the schema
434 // declares no fact-table metadata, which is the same "cannot adjudicate
435 // → pass" semantics the rest of this family uses.
436 allowlist.validate(field, "order by")?;
437
438 // Dimension path
439 Ok(format!("{}->>'{}'", metadata.dimensions.name, field))
440 }
441
442 /// Validate a result alias before it is emitted as `<expr> AS <alias>` (#794).
443 ///
444 /// Aliases reach `write!(sql, "{} AS {}", …)` in `runtime/window.rs` with no quoting,
445 /// so an alias of `c, (SELECT … FROM pg_authid) AS leak` appends an entire extra
446 /// SELECT column — and `WindowProjector::project` copies every returned column into
447 /// the GraphQL response, so the attacker reads the result.
448 ///
449 /// Rejects rather than quotes: the alias is also the response key, so rejecting keeps
450 /// legitimate keys byte-identical, matching `aggregate_parser::validate_dimension_key`.
451 ///
452 /// # Errors
453 ///
454 /// Returns [`FraiseQLError::Validation`] if `alias` is outside `[_A-Za-z][_0-9A-Za-z]*`.
455 fn validate_alias(alias: &str) -> Result<()> {
456 Self::validate_identifier_charset(alias, "window alias")
457 }
458
459 /// Validate a JSONB dimension path before it is interpolated into `data->>'path'` (#794).
460 ///
461 /// Two layers, in this order:
462 /// 1. **Charset** — unconditional, and the load-bearing guard. It has to be, because the
463 /// allowlist below no-ops when the schema declares no dimension paths, which is the
464 /// realistic default (the same reasoning `aggregate_parser` records for its own parse-time
465 /// check).
466 /// 2. **Allowlist** — defence-in-depth against the declared schema, applied only when the
467 /// schema actually enumerates dimension paths. Where it declares none there is no constraint
468 /// to enforce, and requiring membership would reject legitimate undeclared paths.
469 ///
470 /// # Errors
471 ///
472 /// Returns [`FraiseQLError::Validation`] if `path` is outside `[_A-Za-z][_0-9A-Za-z]*`,
473 /// or is not a declared dimension when the schema enumerates them.
474 fn validate_dimension_path(
475 path: &str,
476 metadata: &FactTableMetadata,
477 allowlist: &WindowAllowlist,
478 ) -> Result<()> {
479 Self::validate_identifier_charset(path, "window dimension path")?;
480 if metadata.dimensions.paths.is_empty() {
481 return Ok(());
482 }
483 allowlist.validate(path, "dimension")
484 }
485
486 /// The single charset check for every client-supplied identifier on the window path.
487 ///
488 /// `[_A-Za-z][_0-9A-Za-z]*`, rejected rather than escaped. Every caller that
489 /// interpolates a request-supplied string into SQL — aliases, dimension paths, order-by
490 /// fields — routes through here, so a new sink cannot quietly acquire a different
491 /// (or absent) rule. #794 existed because `resolve_field_to_sql` had a check and its
492 /// four sibling sinks did not.
493 fn validate_identifier_charset(value: &str, context: &str) -> Result<()> {
494 let mut chars = value.chars();
495 let first_ok = chars.next().is_some_and(|c| c.is_ascii_alphabetic() || c == '_');
496 let rest_ok = chars.all(|c| c.is_ascii_alphanumeric() || c == '_');
497 if first_ok && rest_ok {
498 Ok(())
499 } else {
500 Err(FraiseQLError::Validation {
501 message: format!(
502 "{context} '{value}' contains invalid characters; \
503 only [_A-Za-z][_0-9A-Za-z]* is allowed"
504 ),
505 path: None,
506 })
507 }
508 }
509
510 /// Validate that `field` is a safe GraphQL identifier: `[_A-Za-z][_0-9A-Za-z]*`.
511 ///
512 /// Field names are embedded as single-quoted string keys in JSONB extraction
513 /// expressions (`data->>'field'`). Any character outside this set must be rejected.
514 fn validate_field_identifier(field: &str) -> Result<()> {
515 let mut chars = field.chars();
516 let first_ok = chars.next().is_some_and(|c| c.is_ascii_alphabetic() || c == '_');
517 let rest_ok = chars.all(|c| c.is_ascii_alphanumeric() || c == '_');
518 if first_ok && rest_ok {
519 Ok(())
520 } else {
521 Err(crate::error::FraiseQLError::Validation {
522 message: format!(
523 "window field '{field}' contains invalid characters; \
524 only [_A-Za-z][_0-9A-Za-z]* is allowed"
525 ),
526 path: None,
527 })
528 }
529 }
530
531 /// Validate that a measure exists in metadata.
532 fn validate_measure(measure: &str, metadata: &FactTableMetadata) -> Result<()> {
533 if !metadata.measures.iter().any(|m| m.name == *measure) {
534 return Err(FraiseQLError::Validation {
535 message: format!(
536 "Measure '{}' not found in fact table '{}'",
537 measure, metadata.table_name
538 ),
539 path: None,
540 });
541 }
542 Ok(())
543 }
544}