uqa_sql/schema/
generated.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! `PostgreSQL` 18 generated-column validation and row computation.
8
9use crate::ast::ForeignKey;
10use crate::ast::{ColumnDef, Expr, GeneratedColumnKind};
11use crate::{semantics::aggregates, ColumnType, SQLError};
12
13pub(crate) mod eligibility;
14mod virtual_security;
15
16#[cfg(test)]
17mod tests;
18
19pub fn prepare_generated_columns(
20    context: &super::SchemaBindingContext<'_, '_>,
21    qualifier: &str,
22    columns: &mut [ColumnDef],
23    foreign_keys: &[ForeignKey],
24) -> Result<(), SQLError> {
25    let snapshot = columns.to_vec();
26    for index in 0..columns.len() {
27        prepare_generated_column(context, qualifier, &snapshot, columns, index, foreign_keys)?;
28    }
29    Ok(())
30}
31
32/// Validate and bind the generation expression of `columns[index]`, if it has one, against the relation's columns as `snapshot` describes them before any of their expressions was bound.
33pub fn prepare_generated_column(
34    context: &super::SchemaBindingContext<'_, '_>,
35    qualifier: &str,
36    snapshot: &[ColumnDef],
37    columns: &mut [ColumnDef],
38    index: usize,
39    foreign_keys: &[ForeignKey],
40) -> Result<(), SQLError> {
41    let engine = context.catalog;
42    let column = &snapshot[index];
43    let Some(generated) = column.generated.as_ref() else {
44        return Ok(());
45    };
46    if column.default.is_some() {
47        return Err(SQLError::TypeMismatch(format!(
48            "both default and generation expression specified for column `{}`",
49            column.name
50        )));
51    }
52    if column.auto_increment.is_some() {
53        return Err(SQLError::TypeMismatch(format!(
54            "both identity and generation expression specified for column `{}`",
55            column.name
56        )));
57    }
58    if generated.kind == GeneratedColumnKind::Virtual {
59        validate_virtual_column_envelope(column, foreign_keys)?;
60    }
61    check_generation_shape(engine, snapshot, &generated.expression)?;
62    validate_generation_expression(qualifier, snapshot, &generated.expression)?;
63    if generated.kind == GeneratedColumnKind::Virtual {
64        virtual_security::check_virtual_host_functions(engine, &generated.expression)?;
65    }
66    let prepared = columns[index]
67        .generated
68        .as_mut()
69        .ok_or_else(|| SQLError::Internal("generated column disappeared".into()))?;
70    eligibility::check_host_return_types(&mut prepared.expression, |name| {
71        engine
72            .registered_runtime_function_volatility(name)
73            .is_some()
74    })?;
75    bind_schema_column_references(&mut prepared.expression, qualifier);
76    let planned = engine.plan_schema_expression(&prepared.expression, snapshot)?;
77    if !planned.immutable {
78        return Err(eligibility::non_immutable_function());
79    }
80    *prepared.expression = planned.expression;
81    if generated.kind == GeneratedColumnKind::Virtual {
82        virtual_security::check_virtual_generated_security(engine, snapshot, &prepared.expression)?;
83    }
84    crate::catalog::regrole_dependencies::reject_stored_regrole_constants(
85        engine,
86        &prepared.expression,
87        Some(&column.ty),
88    )?;
89    if let Some(source) = &planned.ty {
90        super::defaults::check_assignable(source, &column.ty, &column.name, "default expression")?;
91    } else if matches!(
92        prepared.expression.as_ref(),
93        Expr::Literal(uqa_core::Value::Str(_) | uqa_core::Value::Null)
94    ) {
95        crate::catalog::stored_ast::read_unknown_stored_literal(
96            crate::FunctionTypeResolver::enum_labels(engine),
97            crate::FunctionTypeResolver::catalog_input_functions(engine),
98            &mut prepared.expression,
99            &column.ty,
100            false,
101        )?;
102    }
103    let mut dependencies = Vec::new();
104    crate::catalog::stored_ast::visit_stored_expression(&mut prepared.expression, &mut |node| {
105        if let Expr::Func {
106            binding: Some(binding),
107            ..
108        } = node
109        {
110            if !binding.builtin && !dependencies.contains(binding) {
111                dependencies.push(binding.clone());
112            }
113        }
114        Ok(())
115    })?;
116    prepared.function_dependencies = dependencies;
117    // The `reg*` input functions read the objects a generation expression names when the column is defined, so the stored constants follow renames.
118    super::dependencies::oid_alias::read_oid_alias_constants(engine, &mut prepared.expression)?;
119    // The generation result is assigned to the column; its routines, user-defined types and enum constants are stored by identity as parse analysis stores them.
120    crate::catalog::stored_ast::fold_assigned_stored_literal(
121        &mut prepared.expression,
122        &column.ty,
123        crate::FunctionTypeResolver::enum_labels(engine),
124    )?;
125    let typed = (*prepared.expression).clone();
126    super::defaults::bind_stored_schema_expression_with_schema(
127        context,
128        &mut prepared.expression,
129        typed,
130        &crate::RowSchema::with_column_definitions(Some(qualifier), snapshot),
131    )?;
132    Ok(())
133}
134
135/// What a generation expression cannot contain, as `cookDefault` and parse analysis reject it: a subquery, an aggregate, a window function and a set-returning function. The table's columns type the calls the expression makes.
136fn check_generation_shape(
137    engine: &dyn super::SchemaExpressionCatalog,
138    columns: &[ColumnDef],
139    expression: &Expr,
140) -> Result<(), SQLError> {
141    let plan = crate::plan::ExpressionPlan::lower(expression.clone());
142    let schema = crate::RowSchema::with_types(
143        columns.iter().map(|column| column.name.clone()).collect(),
144        columns
145            .iter()
146            .map(|column| Some(column.ty.clone()))
147            .collect(),
148    );
149    if !plan.subqueries.is_empty() {
150        return Err(generation_error(
151            "0A000",
152            "cannot use subquery in column generation expression",
153        ));
154    }
155    if aggregates::contains_aggregate(engine, &plan.scalar) {
156        return Err(generation_error(
157            "42803",
158            "aggregate functions are not allowed in column generation expressions",
159        ));
160    }
161    if crate::semantics::windows::expr_has_window(&plan.scalar) {
162        return Err(generation_error(
163            "42P20",
164            "window functions are not allowed in column generation expressions",
165        ));
166    }
167    if crate::semantics::sets::validation::expression_may_return_set(
168        engine,
169        engine,
170        &plan.scalar,
171        &schema,
172        &[],
173    )? {
174        return Err(generation_error(
175            "0A000",
176            "set-returning functions are not allowed in column generation expressions",
177        ));
178    }
179    Ok(())
180}
181
182/// A virtual generated column cannot take a user-defined type or a foreign key; `DefineIndex` rejects it as a key column, after the key's other checks.
183fn validate_virtual_column_envelope(
184    column: &ColumnDef,
185    foreign_keys: &[ForeignKey],
186) -> Result<(), SQLError> {
187    if virtual_security::is_user_defined_type(&column.ty) {
188        return Err(SQLError::Diagnostic {
189            sqlstate: "0A000".into(),
190            message: format!(
191                "virtual generated column \"{}\" cannot have a user-defined type",
192                column.name
193            ),
194            detail: Some(virtual_security::USER_DEFINED_TYPE_DETAIL.into()),
195            hint: None,
196        });
197    }
198    if contains_engine_defined_type(&column.ty) {
199        return Err(SQLError::TypeMismatch(format!(
200            "virtual generated column `{}` cannot use a user-defined type",
201            column.name
202        )));
203    }
204    if column.references.is_some()
205        || foreign_keys.iter().any(|foreign_key| {
206            foreign_key
207                .local_columns
208                .iter()
209                .any(|name| name == &column.name)
210        })
211    {
212        return Err(SQLError::Unsupported(
213            "foreign key constraints on virtual generated columns are not supported".into(),
214        ));
215    }
216    Ok(())
217}
218
219fn contains_engine_defined_type(ty: &ColumnType) -> bool {
220    match ty {
221        ColumnType::Vector(_) | ColumnType::Tensor(_) => true,
222        ColumnType::Array(element) => contains_engine_defined_type(element),
223        _ => false,
224    }
225}
226
227fn validate_generation_expression(
228    qualifier: &str,
229    columns: &[ColumnDef],
230    expression: &Expr,
231) -> Result<(), SQLError> {
232    match expression {
233        Expr::Column(name) => validate_generation_column_reference(columns, name),
234        Expr::QualifiedColumn {
235            qualifier: expression_qualifier,
236            column,
237            ..
238        } => {
239            if expression_qualifier != qualifier {
240                return Err(SQLError::UnknownTable(expression_qualifier.clone()));
241            }
242            validate_generation_column_reference(columns, column)
243        }
244        Expr::Func {
245            args,
246            distinct,
247            order_by,
248            filter,
249            ..
250        } => {
251            if *distinct || !order_by.is_empty() || filter.is_some() {
252                return Err(SQLError::TypeMismatch(
253                    "aggregate syntax is not allowed in column generation expressions".into(),
254                ));
255            }
256            for argument in args {
257                validate_generation_expression(qualifier, columns, argument)?;
258            }
259            Ok(())
260        }
261        Expr::Array(items)
262        | Expr::Row(items)
263        | Expr::CompositeRow { items, .. }
264        | Expr::And(items)
265        | Expr::Or(items) => {
266            for item in items {
267                validate_generation_expression(qualifier, columns, item)?;
268            }
269            Ok(())
270        }
271        Expr::Binary { lhs, rhs, .. } => {
272            validate_generation_expression(qualifier, columns, lhs)?;
273            validate_generation_expression(qualifier, columns, rhs)
274        }
275        Expr::Not(inner)
276        | Expr::UnaryMinus(inner)
277        | Expr::IsNull { expr: inner, .. }
278        | Expr::Cast { expr: inner, .. } => {
279            validate_generation_expression(qualifier, columns, inner)
280        }
281        Expr::Between { expr, low, high } => {
282            validate_generation_expression(qualifier, columns, expr)?;
283            validate_generation_expression(qualifier, columns, low)?;
284            validate_generation_expression(qualifier, columns, high)
285        }
286        Expr::InList { expr, list, .. } => {
287            validate_generation_expression(qualifier, columns, expr)?;
288            for item in list {
289                validate_generation_expression(qualifier, columns, item)?;
290            }
291            Ok(())
292        }
293        Expr::Case {
294            base,
295            when,
296            else_branch,
297        } => {
298            if let Some(base) = base {
299                validate_generation_expression(qualifier, columns, base)?;
300            }
301            for (condition, result) in when {
302                validate_generation_expression(qualifier, columns, condition)?;
303                validate_generation_expression(qualifier, columns, result)?;
304            }
305            if let Some(else_branch) = else_branch {
306                validate_generation_expression(qualifier, columns, else_branch)?;
307            }
308            Ok(())
309        }
310        Expr::Default | Expr::Param(_) => Err(SQLError::TypeMismatch(
311            "parameters and DEFAULT are not allowed in column generation expressions".into(),
312        )),
313        Expr::Star | Expr::QualifiedStar(_) => Err(SQLError::TypeMismatch(
314            "whole-row references are not allowed in column generation expressions".into(),
315        )),
316        Expr::InternalColumn(_) => Err(SQLError::Internal(
317            "executor-only column reached generation expression validation".into(),
318        )),
319        Expr::WindowCall { .. } => Err(generation_error(
320            "42P20",
321            "window functions are not allowed in column generation expressions",
322        )),
323        Expr::ScalarSubquery(_) | Expr::Exists { .. } | Expr::InSubquery { .. } => {
324            Err(generation_error(
325                "0A000",
326                "cannot use subquery in column generation expression",
327            ))
328        }
329        Expr::Literal(_) | Expr::TypedLiteral { .. } => Ok(()),
330    }
331}
332
333pub fn bind_schema_column_references(expression: &mut Expr, qualifier: &str) {
334    if let Expr::QualifiedColumn {
335        qualifier: expression_qualifier,
336        column,
337    } = expression
338    {
339        if expression_qualifier == qualifier {
340            *expression = Expr::Column(column.clone());
341        }
342        return;
343    }
344    match expression {
345        Expr::Func {
346            args,
347            order_by,
348            filter,
349            ..
350        } => {
351            for argument in args {
352                bind_schema_column_references(argument, qualifier);
353            }
354            for order in order_by {
355                bind_schema_column_references(&mut order.expr, qualifier);
356            }
357            if let Some(filter) = filter {
358                bind_schema_column_references(filter, qualifier);
359            }
360        }
361        Expr::Array(items)
362        | Expr::Row(items)
363        | Expr::CompositeRow { items, .. }
364        | Expr::And(items)
365        | Expr::Or(items) => {
366            for item in items {
367                bind_schema_column_references(item, qualifier);
368            }
369        }
370        Expr::Binary { lhs, rhs, .. } => {
371            bind_schema_column_references(lhs, qualifier);
372            bind_schema_column_references(rhs, qualifier);
373        }
374        Expr::Not(inner)
375        | Expr::UnaryMinus(inner)
376        | Expr::IsNull { expr: inner, .. }
377        | Expr::Cast { expr: inner, .. } => {
378            bind_schema_column_references(inner, qualifier);
379        }
380        Expr::Between { expr, low, high } => {
381            bind_schema_column_references(expr, qualifier);
382            bind_schema_column_references(low, qualifier);
383            bind_schema_column_references(high, qualifier);
384        }
385        Expr::InList { expr, list, .. } => {
386            bind_schema_column_references(expr, qualifier);
387            for item in list {
388                bind_schema_column_references(item, qualifier);
389            }
390        }
391        Expr::Case {
392            base,
393            when,
394            else_branch,
395        } => {
396            if let Some(base) = base {
397                bind_schema_column_references(base, qualifier);
398            }
399            for (condition, result) in when {
400                bind_schema_column_references(condition, qualifier);
401                bind_schema_column_references(result, qualifier);
402            }
403            if let Some(else_branch) = else_branch {
404                bind_schema_column_references(else_branch, qualifier);
405            }
406        }
407        Expr::Star
408        | Expr::QualifiedStar(_)
409        | Expr::Default
410        | Expr::Column(_)
411        | Expr::QualifiedColumn { .. }
412        | Expr::InternalColumn(_)
413        | Expr::Literal(_)
414        | Expr::TypedLiteral { .. }
415        | Expr::Param(_)
416        | Expr::WindowCall { .. }
417        | Expr::ScalarSubquery(_)
418        | Expr::Exists { .. }
419        | Expr::InSubquery { .. } => {}
420    }
421}
422
423fn validate_generation_column_reference(columns: &[ColumnDef], name: &str) -> Result<(), SQLError> {
424    let Some(column) = columns.iter().find(|column| column.name == name) else {
425        return Err(SQLError::UnknownColumn(name.to_string()));
426    };
427    if column.generated.is_some() {
428        return Err(SQLError::Diagnostic {
429            sqlstate: "42P17".into(),
430            message: format!(
431                "cannot use generated column \"{name}\" in column generation expression"
432            ),
433            detail: Some("A generated column cannot reference another generated column.".into()),
434            hint: None,
435        });
436    }
437    Ok(())
438}
439
440fn generation_error(sqlstate: &str, message: &str) -> SQLError {
441    SQLError::Routine {
442        sqlstate: sqlstate.into(),
443        message: message.into(),
444    }
445}
uqa_sql/schema/generated.rs

uqa_sql/schema/
generated.rs
