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 super::SchemaExpressionCatalog;
10use crate::ast::ForeignKey;
11use crate::ast::{ColumnDef, Expr, GeneratedColumnKind, TableKeyConstraint};
12use crate::{
13    assignment::conversion::convert_value_to_column_type, semantics::aggregates, ColumnType,
14    SQLError,
15};
16
17pub(crate) mod eligibility;
18pub(super) mod typing;
19
20pub fn prepare_generated_columns(
21    engine: &dyn SchemaExpressionCatalog,
22    qualifier: &str,
23    columns: &mut [ColumnDef],
24    key_constraints: &[TableKeyConstraint],
25    foreign_keys: &[ForeignKey],
26) -> Result<(), SQLError> {
27    let snapshot = columns.to_vec();
28    for (index, column) in snapshot.iter().enumerate() {
29        let Some(generated) = column.generated.as_ref() else {
30            continue;
31        };
32        if column.default.is_some() {
33            return Err(SQLError::TypeMismatch(format!(
34                "both default and generation expression specified for column `{}`",
35                column.name
36            )));
37        }
38        if column.auto_increment.is_some() {
39            return Err(SQLError::TypeMismatch(format!(
40                "both identity and generation expression specified for column `{}`",
41                column.name
42            )));
43        }
44        if generated.kind == GeneratedColumnKind::Virtual {
45            validate_virtual_column_envelope(column, key_constraints, foreign_keys)?;
46        }
47        let plan = crate::plan::ExpressionPlan::lower((*generated.expression).clone());
48        if !plan.subqueries.is_empty() {
49            return Err(SQLError::TypeMismatch(
50                "cannot use subquery in column generation expression".into(),
51            ));
52        }
53        if aggregates::contains_aggregate(engine, &plan.scalar) {
54            return Err(SQLError::TypeMismatch(
55                "aggregate functions are not allowed in column generation expressions".into(),
56            ));
57        }
58        validate_generation_expression(
59            engine,
60            qualifier,
61            &snapshot,
62            &generated.expression,
63            generated.kind,
64        )?;
65        let prepared = columns[index]
66            .generated
67            .as_mut()
68            .ok_or_else(|| SQLError::Internal("generated column disappeared".into()))?;
69        bind_schema_column_references(&mut prepared.expression, qualifier);
70        let (expression_type, function_dependencies) =
71            typing::infer_generation_expression(engine, &snapshot, &mut prepared.expression)?;
72        crate::catalog::regrole_dependencies::reject_stored_regrole_constants(
73            engine,
74            &prepared.expression,
75            Some(&column.ty),
76        )?;
77        if let typing::GenerationType::UnknownLiteral(value) = &expression_type {
78            convert_value_to_column_type(uqa_core::Value::Str(value.clone()), &column.ty)?;
79        } else if !typing::generation_type_assignable_to(&expression_type, &column.ty) {
80            return Err(SQLError::TypeMismatch(format!(
81                "column `{}` has type {} but generation expression has type {}",
82                column.name,
83                crate::catalog::type_metadata::column_type_name(&column.ty),
84                typing::generation_type_name(&expression_type)
85            )));
86        }
87        prepared.function_dependencies = function_dependencies;
88    }
89    Ok(())
90}
91
92fn validate_virtual_column_envelope(
93    column: &ColumnDef,
94    key_constraints: &[TableKeyConstraint],
95    foreign_keys: &[ForeignKey],
96) -> Result<(), SQLError> {
97    if contains_engine_defined_type(&column.ty) {
98        return Err(SQLError::TypeMismatch(format!(
99            "virtual generated column `{}` cannot use a user-defined type",
100            column.name
101        )));
102    }
103    if column.primary_key
104        || key_constraints.iter().any(|constraint| {
105            constraint.kind == crate::ast::TableKeyConstraintKind::PrimaryKey
106                && constraint
107                    .columns
108                    .iter()
109                    .chain(&constraint.included_columns)
110                    .any(|name| name == &column.name)
111        })
112    {
113        return Err(SQLError::Unsupported(
114            "primary keys on virtual generated columns are not supported".into(),
115        ));
116    }
117    if column.unique
118        || key_constraints.iter().any(|constraint| {
119            constraint.kind == crate::ast::TableKeyConstraintKind::Unique
120                && constraint
121                    .columns
122                    .iter()
123                    .chain(&constraint.included_columns)
124                    .any(|name| name == &column.name)
125        })
126    {
127        return Err(SQLError::Unsupported(
128            "unique constraints on virtual generated columns are not supported".into(),
129        ));
130    }
131    if column.references.is_some()
132        || foreign_keys.iter().any(|foreign_key| {
133            foreign_key
134                .local_columns
135                .iter()
136                .any(|name| name == &column.name)
137        })
138    {
139        return Err(SQLError::Unsupported(
140            "foreign key constraints on virtual generated columns are not supported".into(),
141        ));
142    }
143    Ok(())
144}
145
146fn contains_engine_defined_type(ty: &ColumnType) -> bool {
147    match ty {
148        ColumnType::Vector(_) | ColumnType::Tensor(_) => true,
149        ColumnType::Array(element) => contains_engine_defined_type(element),
150        _ => false,
151    }
152}
153
154#[expect(
155    clippy::too_many_lines,
156    reason = "preserves generated coercion diagnostics"
157)]
158fn validate_generation_expression(
159    engine: &dyn SchemaExpressionCatalog,
160    qualifier: &str,
161    columns: &[ColumnDef],
162    expression: &Expr,
163    kind: GeneratedColumnKind,
164) -> Result<(), SQLError> {
165    match expression {
166        Expr::Column(name) => validate_generation_column_reference(columns, name),
167        Expr::QualifiedColumn {
168            qualifier: expression_qualifier,
169            column,
170            ..
171        } => {
172            if expression_qualifier != qualifier {
173                return Err(SQLError::UnknownTable(expression_qualifier.clone()));
174            }
175            validate_generation_column_reference(columns, column)
176        }
177        Expr::Func {
178            name,
179            binding,
180            args,
181            distinct,
182            order_by,
183            filter,
184            ..
185        } => {
186            if *distinct || !order_by.is_empty() || filter.is_some() {
187                return Err(SQLError::TypeMismatch(
188                    "aggregate syntax is not allowed in column generation expressions".into(),
189                ));
190            }
191            if kind == GeneratedColumnKind::Virtual
192                && binding
193                    .as_ref()
194                    .and_then(|binding| binding.dispatch)
195                    .is_none()
196                && (engine
197                    .registered_runtime_function_volatility(name)
198                    .is_some()
199                    || engine.lookup_visible_sql_functions(name)?.is_some())
200            {
201                return Err(SQLError::Diagnostic {
202                    sqlstate: "0A000".into(),
203                    message: "generation expression uses user-defined function".into(),
204                    detail: Some("Virtual generated columns that make use of user-defined functions are not yet supported.".into()),
205                    hint: None,
206                });
207            }
208            for argument in args {
209                validate_generation_expression(engine, qualifier, columns, argument, kind)?;
210            }
211            Ok(())
212        }
213        Expr::Array(items) | Expr::Row(items) | Expr::And(items) | Expr::Or(items) => {
214            for item in items {
215                validate_generation_expression(engine, qualifier, columns, item, kind)?;
216            }
217            Ok(())
218        }
219        Expr::Binary { lhs, rhs, .. } => {
220            validate_generation_expression(engine, qualifier, columns, lhs, kind)?;
221            validate_generation_expression(engine, qualifier, columns, rhs, kind)
222        }
223        Expr::Not(inner)
224        | Expr::UnaryMinus(inner)
225        | Expr::IsNull { expr: inner, .. }
226        | Expr::Cast { expr: inner, .. } => {
227            validate_generation_expression(engine, qualifier, columns, inner, kind)
228        }
229        Expr::Between { expr, low, high } => {
230            validate_generation_expression(engine, qualifier, columns, expr, kind)?;
231            validate_generation_expression(engine, qualifier, columns, low, kind)?;
232            validate_generation_expression(engine, qualifier, columns, high, kind)
233        }
234        Expr::InList { expr, list, .. } => {
235            validate_generation_expression(engine, qualifier, columns, expr, kind)?;
236            for item in list {
237                validate_generation_expression(engine, qualifier, columns, item, kind)?;
238            }
239            Ok(())
240        }
241        Expr::Case {
242            base,
243            when,
244            else_branch,
245        } => {
246            if let Some(base) = base {
247                validate_generation_expression(engine, qualifier, columns, base, kind)?;
248            }
249            for (condition, result) in when {
250                validate_generation_expression(engine, qualifier, columns, condition, kind)?;
251                validate_generation_expression(engine, qualifier, columns, result, kind)?;
252            }
253            if let Some(else_branch) = else_branch {
254                validate_generation_expression(engine, qualifier, columns, else_branch, kind)?;
255            }
256            Ok(())
257        }
258        Expr::Default | Expr::Param(_) => Err(SQLError::TypeMismatch(
259            "parameters and DEFAULT are not allowed in column generation expressions".into(),
260        )),
261        Expr::Star | Expr::QualifiedStar(_) => Err(SQLError::TypeMismatch(
262            "whole-row references are not allowed in column generation expressions".into(),
263        )),
264        Expr::InternalColumn(_) => Err(SQLError::Internal(
265            "executor-only column reached generation expression validation".into(),
266        )),
267        Expr::WindowCall { .. } => Err(SQLError::TypeMismatch(
268            "window functions are not allowed in column generation expressions".into(),
269        )),
270        Expr::ScalarSubquery(_) | Expr::Exists { .. } | Expr::InSubquery { .. } => Err(
271            SQLError::TypeMismatch("cannot use subquery in column generation expression".into()),
272        ),
273        Expr::Literal(_) | Expr::TypedLiteral { .. } => Ok(()),
274    }
275}
276
277pub fn bind_schema_column_references(expression: &mut Expr, qualifier: &str) {
278    if let Expr::QualifiedColumn {
279        qualifier: expression_qualifier,
280        column,
281    } = expression
282    {
283        if expression_qualifier == qualifier {
284            *expression = Expr::Column(column.clone());
285        }
286        return;
287    }
288    match expression {
289        Expr::Func {
290            args,
291            order_by,
292            filter,
293            ..
294        } => {
295            for argument in args {
296                bind_schema_column_references(argument, qualifier);
297            }
298            for order in order_by {
299                bind_schema_column_references(&mut order.expr, qualifier);
300            }
301            if let Some(filter) = filter {
302                bind_schema_column_references(filter, qualifier);
303            }
304        }
305        Expr::Array(items) | Expr::Row(items) | Expr::And(items) | Expr::Or(items) => {
306            for item in items {
307                bind_schema_column_references(item, qualifier);
308            }
309        }
310        Expr::Binary { lhs, rhs, .. } => {
311            bind_schema_column_references(lhs, qualifier);
312            bind_schema_column_references(rhs, qualifier);
313        }
314        Expr::Not(inner)
315        | Expr::UnaryMinus(inner)
316        | Expr::IsNull { expr: inner, .. }
317        | Expr::Cast { expr: inner, .. } => {
318            bind_schema_column_references(inner, qualifier);
319        }
320        Expr::Between { expr, low, high } => {
321            bind_schema_column_references(expr, qualifier);
322            bind_schema_column_references(low, qualifier);
323            bind_schema_column_references(high, qualifier);
324        }
325        Expr::InList { expr, list, .. } => {
326            bind_schema_column_references(expr, qualifier);
327            for item in list {
328                bind_schema_column_references(item, qualifier);
329            }
330        }
331        Expr::Case {
332            base,
333            when,
334            else_branch,
335        } => {
336            if let Some(base) = base {
337                bind_schema_column_references(base, qualifier);
338            }
339            for (condition, result) in when {
340                bind_schema_column_references(condition, qualifier);
341                bind_schema_column_references(result, qualifier);
342            }
343            if let Some(else_branch) = else_branch {
344                bind_schema_column_references(else_branch, qualifier);
345            }
346        }
347        Expr::Star
348        | Expr::QualifiedStar(_)
349        | Expr::Default
350        | Expr::Column(_)
351        | Expr::QualifiedColumn { .. }
352        | Expr::InternalColumn(_)
353        | Expr::Literal(_)
354        | Expr::TypedLiteral { .. }
355        | Expr::Param(_)
356        | Expr::WindowCall { .. }
357        | Expr::ScalarSubquery(_)
358        | Expr::Exists { .. }
359        | Expr::InSubquery { .. } => {}
360    }
361}
362
363fn validate_generation_column_reference(columns: &[ColumnDef], name: &str) -> Result<(), SQLError> {
364    let Some(column) = columns.iter().find(|column| column.name == name) else {
365        return Err(SQLError::UnknownColumn(name.to_string()));
366    };
367    if column.generated.is_some() {
368        return Err(SQLError::TypeMismatch(format!(
369            "cannot use generated column `{name}` in column generation expression"
370        )));
371    }
372    Ok(())
373}
uqa_sql/schema/generated.rs

uqa_sql/schema/
generated.rs
