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

uqa_sql/schema/
generated.rs
