fraiseql_core/runtime/
window.rs

1//! Window Function SQL Generation
2//!
3//! Generates database-specific SQL for window functions.
4//!
5//! # Supported Databases
6//!
7//! - **PostgreSQL**: Full support (all functions + GROUPS frames + frame exclusion)
8//! - **MySQL 8.0+**: Full support (no GROUPS, no frame exclusion)
9//! - **SQLite 3.25+**: Basic support (no GROUPS, no `PERCENT_RANK/CUME_DIST`)
10//! - **SQL Server**: Full support (STDEV/VAR naming difference)
11
12use std::fmt::Write as _;
13
14use crate::{
15    backend::{GenericWhereGenerator, PostgresDialect, types::DatabaseType},
16    compiler::{
17        aggregation::OrderDirection,
18        window_functions::{
19            FrameBoundary, FrameExclusion, FrameType, WindowExecutionPlan, WindowFrame,
20            WindowFunction, WindowFunctionType,
21        },
22    },
23    error::{FraiseQLError, Result},
24};
25
26/// Generated SQL for window function query
27#[derive(Debug, Clone)]
28pub struct WindowSql {
29    /// Parameterized SQL template. WHERE-clause **values** use dialect-specific
30    /// placeholders (`$1`, `?`, `@P1`) and are never interpolated.
31    ///
32    /// **Identifiers here are user-controlled.** Aliases and dimension paths come from the
33    /// request, so they are constrained rather than trusted: every one is rejected unless
34    /// it matches `[_A-Za-z][_0-9A-Za-z]*` (`WindowPlanner::validate_identifier_charset`),
35    /// and dimension paths are additionally checked against
36    /// [`crate::compiler::window_allowlist::WindowAllowlist`] wherever the schema
37    /// enumerates them. The FROM target is the resolved fact table, not the request's
38    /// `table` key.
39    ///
40    /// This comment previously claimed the identifiers were "schema-derived",
41    /// "allowlist-validated" and "not user-controlled at runtime". All three were false on
42    /// this path: the allowlist was only ever consulted by a second planner that nothing
43    /// outside tests called (deleted in #881), and four sinks interpolated request strings
44    /// verbatim (#794). Keep this description honest — it is load-bearing for anyone
45    /// auditing the emitters below.
46    pub raw_sql: String,
47
48    /// Bind parameters in placeholder order, passed to
49    /// `execute_parameterized_aggregate`.
50    pub parameters: Vec<serde_json::Value>,
51}
52
53/// Window function SQL generator
54pub struct WindowSqlGenerator {
55    database_type: DatabaseType,
56}
57
58impl WindowSqlGenerator {
59    /// Create new generator for database type
60    #[must_use]
61    pub const fn new(database_type: DatabaseType) -> Self {
62        Self { database_type }
63    }
64
65    /// Generate SQL from window execution plan
66    ///
67    /// # Errors
68    ///
69    /// Returns error if:
70    /// - Unsupported function for database
71    /// - Invalid frame specification
72    /// - WHERE clause generation fails
73    pub fn generate(&self, plan: &WindowExecutionPlan) -> Result<WindowSql> {
74        match self.database_type {
75            DatabaseType::PostgreSQL => self.generate_postgres(plan),
76        }
77    }
78
79    /// Generate PostgreSQL window function SQL
80    fn generate_postgres(&self, plan: &WindowExecutionPlan) -> Result<WindowSql> {
81        let mut sql = String::from("SELECT ");
82        let mut parameters = Vec::new();
83
84        // Add regular SELECT columns
85        for (i, col) in plan.select.iter().enumerate() {
86            if i > 0 {
87                sql.push_str(", ");
88            }
89            let _ = write!(sql, "{} AS {}", col.expression, col.alias);
90        }
91
92        // Add window functions
93        for window in &plan.windows {
94            if !plan.select.is_empty() || sql.len() > "SELECT ".len() {
95                sql.push_str(", ");
96            }
97            sql.push_str(&self.generate_window_function(window)?);
98        }
99
100        // FROM clause
101        let _ = write!(sql, " FROM {}", plan.table);
102
103        // WHERE clause (if any) — use parameterized generation to avoid literal
104        // value escaping and enable the database to cache execution plans.
105        if let Some(clause) = &plan.where_clause {
106            let gen = GenericWhereGenerator::new(PostgresDialect);
107            let (where_sql, where_params) = gen.generate(clause)?;
108            sql.push_str(" WHERE ");
109            sql.push_str(&where_sql);
110            parameters.extend(where_params);
111        }
112
113        // ORDER BY clause
114        if !plan.order_by.is_empty() {
115            sql.push_str(" ORDER BY ");
116            for (i, order) in plan.order_by.iter().enumerate() {
117                if i > 0 {
118                    sql.push_str(", ");
119                }
120                #[allow(clippy::match_same_arms)]
121                // Reason: non_exhaustive enum requires catch-all; explicit Asc arm documents intent
122                let dir = match order.direction {
123                    OrderDirection::Asc => "ASC",
124                    OrderDirection::Desc => "DESC",
125                    _ => "ASC",
126                };
127                // Fields in the outer ORDER BY may be JSONB path expressions
128                // (e.g. `data->>'category'`) or window aliases (e.g. `rank`); they
129                // are validated at planner parse time and must not be identifier-quoted.
130                let _ = write!(sql, "{} {}", order.field, dir);
131            }
132        }
133
134        // LIMIT / OFFSET
135        if let Some(limit) = plan.limit {
136            let _ = write!(sql, " LIMIT {limit}");
137        }
138        if let Some(offset) = plan.offset {
139            let _ = write!(sql, " OFFSET {offset}");
140        }
141
142        Ok(WindowSql {
143            raw_sql: sql,
144            parameters,
145        })
146    }
147
148    /// Generate window function expression
149    fn generate_window_function(&self, window: &WindowFunction) -> Result<String> {
150        let func_sql = self.generate_function_call(&window.function)?;
151        let mut sql = format!("{func_sql} OVER (");
152
153        // PARTITION BY — values are pre-validated SQL expressions (may be JSONB paths
154        // like `data->>'col'`); rejection of unsafe chars happens at planner parse time.
155        if !window.partition_by.is_empty() {
156            sql.push_str("PARTITION BY ");
157            sql.push_str(&window.partition_by.join(", "));
158        }
159
160        // ORDER BY — same: values validated at parse time, may be JSONB expressions.
161        if !window.order_by.is_empty() {
162            if !window.partition_by.is_empty() {
163                sql.push(' ');
164            }
165            sql.push_str("ORDER BY ");
166            for (i, order) in window.order_by.iter().enumerate() {
167                if i > 0 {
168                    sql.push_str(", ");
169                }
170                #[allow(clippy::match_same_arms)]
171                // Reason: non_exhaustive enum requires catch-all; explicit Asc arm documents intent
172                let dir = match order.direction {
173                    OrderDirection::Asc => "ASC",
174                    OrderDirection::Desc => "DESC",
175                    _ => "ASC",
176                };
177                let _ = write!(sql, "{} {}", order.field, dir);
178            }
179        }
180
181        // Frame clause
182        if let Some(frame) = &window.frame {
183            if !window.partition_by.is_empty() || !window.order_by.is_empty() {
184                sql.push(' ');
185            }
186            sql.push_str(&self.generate_frame_clause(frame)?);
187        }
188
189        sql.push(')');
190        let _ = write!(sql, " AS {}", window.alias);
191
192        Ok(sql)
193    }
194
195    /// Generate function call SQL
196    fn generate_function_call(&self, function: &WindowFunctionType) -> Result<String> {
197        let sql = match function {
198            WindowFunctionType::RowNumber => "ROW_NUMBER()".to_string(),
199            WindowFunctionType::Rank => "RANK()".to_string(),
200            WindowFunctionType::DenseRank => "DENSE_RANK()".to_string(),
201            WindowFunctionType::Ntile { n } => format!("NTILE({n})"),
202            WindowFunctionType::PercentRank => "PERCENT_RANK()".to_string(),
203            WindowFunctionType::CumeDist => "CUME_DIST()".to_string(),
204
205            WindowFunctionType::Lag {
206                field,
207                offset,
208                default,
209            } => {
210                if let Some(default_val) = default {
211                    format!("LAG({field}, {offset}, {default_val})")
212                } else {
213                    format!("LAG({field}, {offset})")
214                }
215            },
216            WindowFunctionType::Lead {
217                field,
218                offset,
219                default,
220            } => {
221                if let Some(default_val) = default {
222                    format!("LEAD({field}, {offset}, {default_val})")
223                } else {
224                    format!("LEAD({field}, {offset})")
225                }
226            },
227            WindowFunctionType::FirstValue { field } => format!("FIRST_VALUE({field})"),
228            WindowFunctionType::LastValue { field } => format!("LAST_VALUE({field})"),
229            WindowFunctionType::NthValue { field, n } => format!("NTH_VALUE({field}, {n})"),
230
231            WindowFunctionType::Sum { field } => format!("SUM({field})"),
232            WindowFunctionType::Avg { field } => format!("AVG({field})"),
233            WindowFunctionType::Count { field: Some(field) } => format!("COUNT({field})"),
234            WindowFunctionType::Count { field: None } => "COUNT(*)".to_string(),
235            WindowFunctionType::Min { field } => format!("MIN({field})"),
236            WindowFunctionType::Max { field } => format!("MAX({field})"),
237            WindowFunctionType::Stddev { field } => format!("STDDEV({field})"),
238            WindowFunctionType::Variance { field } => format!("VARIANCE({field})"),
239        };
240
241        Ok(sql)
242    }
243
244    /// Generate window frame clause
245    fn generate_frame_clause(&self, frame: &WindowFrame) -> Result<String> {
246        let frame_type = match frame.frame_type {
247            FrameType::Rows => "ROWS",
248            FrameType::Range => "RANGE",
249            FrameType::Groups => {
250                if !matches!(self.database_type, DatabaseType::PostgreSQL) {
251                    return Err(FraiseQLError::validation(
252                        "GROUPS frame type only supported on PostgreSQL",
253                    ));
254                }
255                "GROUPS"
256            },
257        };
258
259        let start = self.format_frame_boundary(&frame.start);
260        let end = self.format_frame_boundary(&frame.end);
261
262        let mut sql = format!("{frame_type} BETWEEN {start} AND {end}");
263
264        // Frame exclusion (PostgreSQL only)
265        if let Some(exclusion) = &frame.exclusion {
266            if matches!(self.database_type, DatabaseType::PostgreSQL) {
267                let excl = match exclusion {
268                    FrameExclusion::CurrentRow => "EXCLUDE CURRENT ROW",
269                    FrameExclusion::Group => "EXCLUDE GROUP",
270                    FrameExclusion::Ties => "EXCLUDE TIES",
271                    FrameExclusion::NoOthers => "EXCLUDE NO OTHERS",
272                };
273                let _ = write!(sql, " {excl}");
274            }
275        }
276
277        Ok(sql)
278    }
279
280    /// Format frame boundary
281    #[must_use]
282    pub fn format_frame_boundary(&self, boundary: &FrameBoundary) -> String {
283        match boundary {
284            FrameBoundary::UnboundedPreceding => "UNBOUNDED PRECEDING".to_string(),
285            FrameBoundary::NPreceding { n } => format!("{n} PRECEDING"),
286            FrameBoundary::CurrentRow => "CURRENT ROW".to_string(),
287            FrameBoundary::NFollowing { n } => format!("{n} FOLLOWING"),
288            FrameBoundary::UnboundedFollowing => "UNBOUNDED FOLLOWING".to_string(),
289        }
290    }
291}
fraiseql_core/runtime/window.rs

fraiseql_core/runtime/
window.rs
