Skip to main content

uqa_sql/semantics/view_rewrite/
validation.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Public-view and mapped-target validation for automatic DML rewriting.
8
9use super::updatability::{
10    merge_into_materialized_view, merge_with_rules, mixed_merge_paths, non_writable_column,
11    view_not_updatable, ColumnWrite, NotUpdatableReason, ViewCommand,
12};
13use super::{
14    instead_of_trigger_definition, view_query_shape, AutomaticViewLayer, BTreeSet,
15    ConflictActionPlan, ConflictPlan, DeletePlan, InsertPlan, MergePlan, MergeWhenPlan,
16    ReturningAliases, RowSchema, SQLError, ScalarExpr, TriggerEvent, UpdatePlan, ViewColumn,
17    ViewQueryShape, ViewRewriteContext,
18};
19
20pub(super) fn layer_column<'a>(
21    layer: &'a AutomaticViewLayer,
22    name: &str,
23) -> Option<&'a ViewColumn> {
24    layer.columns.iter().find(|column| column.name == name)
25}
26
27fn unknown_view_column(layer: &AutomaticViewLayer, column: &str) -> SQLError {
28    SQLError::UnknownColumn(format!("{}.{column}", layer.canonical_name))
29}
30
31pub(super) fn duplicate_insert_column(column: &str) -> SQLError {
32    SQLError::Routine {
33        sqlstate: "42701".into(),
34        message: format!("column \"{column}\" specified more than once"),
35    }
36}
37
38pub(super) fn duplicate_assignment(column: &str) -> SQLError {
39    SQLError::Routine {
40        sqlstate: "42601".into(),
41        message: format!("multiple assignments to same column \"{column}\""),
42    }
43}
44
45fn validate_view_target_columns<'a>(
46    layer: &AutomaticViewLayer,
47    targets: impl IntoIterator<Item = &'a crate::ast::AssignmentTarget<ScalarExpr>>,
48    duplicate: fn(&str) -> SQLError,
49) -> Result<(), SQLError> {
50    let mut seen = std::collections::BTreeMap::new();
51    for target in targets {
52        if layer_column(layer, &target.column).is_none() {
53            return Err(unknown_view_column(layer, &target.column));
54        }
55        let whole = target.is_whole_column();
56        if seen
57            .insert(&target.column, whole)
58            .is_some_and(|previous| previous || whole)
59        {
60            return Err(duplicate(&target.column));
61        }
62    }
63    Ok(())
64}
65
66pub(super) fn validate_mapped_columns(
67    targets: &[crate::ast::AssignmentTarget<ScalarExpr>],
68    duplicate: fn(&str) -> SQLError,
69) -> Result<(), SQLError> {
70    let mut seen = std::collections::BTreeMap::new();
71    for target in targets {
72        let whole = target.is_whole_column();
73        if seen
74            .insert(&target.column, whole)
75            .is_some_and(|previous| previous || whole)
76        {
77            return Err(duplicate(&target.column));
78        }
79    }
80    Ok(())
81}
82
83pub(super) fn validate_insert_targets(
84    layer: &AutomaticViewLayer,
85    plan: &InsertPlan,
86) -> Result<(), SQLError> {
87    validate_view_target_columns(layer, plan.columns.iter(), duplicate_insert_column)?;
88    let Some(conflict) = &plan.on_conflict else {
89        return Ok(());
90    };
91    for column in &conflict.conflict_columns {
92        if layer_column(layer, column).is_none() {
93            return Err(unknown_view_column(layer, column));
94        }
95    }
96    if let ConflictActionPlan::Update { assignments, .. } = &conflict.action {
97        validate_view_target_columns(
98            layer,
99            assignments
100                .iter()
101                .flat_map(|assignment| assignment.target.targets()),
102            duplicate_assignment,
103        )?;
104    }
105    Ok(())
106}
107
108pub(super) fn validate_update_targets(
109    layer: &AutomaticViewLayer,
110    plan: &UpdatePlan,
111) -> Result<(), SQLError> {
112    validate_view_target_columns(
113        layer,
114        plan.assignments
115            .iter()
116            .flat_map(|assignment| assignment.target.targets()),
117        duplicate_assignment,
118    )
119}
120
121pub(super) fn writable_column(
122    layer: &AutomaticViewLayer,
123    name: &str,
124    write: ColumnWrite,
125) -> Result<String, SQLError> {
126    let column = layer_column(layer, name)
127        .ok_or_else(|| SQLError::UnknownColumn(format!("{}.{name}", layer.canonical_name)))?;
128    column.writable_source_column.clone().ok_or_else(|| {
129        non_writable_column(
130            &layer.canonical_name,
131            name,
132            write,
133            layer.column_restriction(column),
134        )
135    })
136}
137
138/// Reject the first of the `modified` columns, in the view's column order, that the view cannot write, as `view_cols_are_auto_updatable` finds it for `rewriteTargetView`.
139pub(super) fn validate_writable_columns<'a>(
140    layer: &AutomaticViewLayer,
141    modified: impl IntoIterator<Item = &'a str>,
142    write: ColumnWrite,
143) -> Result<(), SQLError> {
144    let modified = modified.into_iter().collect::<BTreeSet<_>>();
145    match layer.columns.iter().find(|column| {
146        column.writable_source_column.is_none() && modified.contains(column.name.as_str())
147    }) {
148        Some(column) => Err(non_writable_column(
149            &layer.canonical_name,
150            &column.name,
151            write,
152            layer.column_restriction(column),
153        )),
154        None => Ok(()),
155    }
156}
157
158fn ambiguous_column(column: &str) -> SQLError {
159    SQLError::Routine {
160        sqlstate: "42702".into(),
161        message: format!("column reference \"{column}\" is ambiguous"),
162    }
163}
164
165#[derive(Clone, Copy)]
166pub(super) struct ExpressionScope<'a> {
167    pub(super) target_qualifier: &'a str,
168    pub(super) returning_aliases: Option<&'a ReturningAliases>,
169    pub(super) source: Option<&'a RowSchema>,
170    pub(super) include_excluded: bool,
171}
172
173impl ExpressionScope<'_> {
174    pub(super) fn row_image_qualifier(self, qualifier: &str) -> bool {
175        self.returning_aliases.is_some_and(|aliases| {
176            [
177                (aliases.old.as_str(), aliases.old_explicit),
178                (aliases.new.as_str(), aliases.new_explicit),
179            ]
180            .into_iter()
181            .any(|(alias, explicit)| {
182                qualifier == alias
183                    && (explicit
184                        || !self
185                            .source
186                            .is_some_and(|source| source.has_qualifier(alias)))
187            })
188        })
189    }
190
191    pub(super) fn target_qualifier(self, qualifier: &str) -> bool {
192        qualifier == self.target_qualifier
193            || (self.include_excluded && qualifier == "excluded")
194            || self.row_image_qualifier(qualifier)
195    }
196}
197
198pub(super) fn validate_view_expression(
199    expression: &ScalarExpr,
200    layer: &AutomaticViewLayer,
201    scope: ExpressionScope<'_>,
202) -> Result<(), SQLError> {
203    let mut expression = expression.clone();
204    let mut error = None;
205    crate::plan::rewrite_scalar_expression(&mut expression, &mut |node| {
206        if error.is_some() {
207            return;
208        }
209        match node {
210            ScalarExpr::Column(column) => {
211                let target = layer_column(layer, column).is_some();
212                let source = scope
213                    .source
214                    .is_some_and(|source| source.has_unqualified_column(column));
215                if target && (source || scope.include_excluded) {
216                    error = Some(ambiguous_column(column));
217                } else if !target && !source {
218                    error = Some(SQLError::UnknownColumn(column.to_string()));
219                }
220            }
221            ScalarExpr::QualifiedColumn { qualifier, column }
222                if scope.target_qualifier(qualifier) && layer_column(layer, column).is_none() =>
223            {
224                error = Some(SQLError::UnknownColumn(format!("{qualifier}.{column}")));
225            }
226            _ => {}
227        }
228    });
229    error.map_or(Ok(()), Err)
230}
231
232fn public_view_columns(
233    services: ViewRewriteContext<'_>,
234    view: &str,
235) -> Result<BTreeSet<String>, SQLError> {
236    let definition = services
237        .catalog
238        .view_definition(view)?
239        .ok_or_else(|| SQLError::UnknownTable(view.to_string()))?;
240    let schema = stored_view_schema(services, &definition)?;
241    Ok(schema
242        .columns()
243        .iter()
244        .enumerate()
245        .map(|(position, column)| schema.public_name(position).unwrap_or(column).to_string())
246        .collect())
247}
248
249fn validate_public_view_expression(
250    expression: &ScalarExpr,
251    public_columns: &BTreeSet<String>,
252    scope: ExpressionScope<'_>,
253) -> Result<(), SQLError> {
254    let mut expression = expression.clone();
255    let mut error = None;
256    crate::plan::rewrite_scalar_expression(&mut expression, &mut |node| {
257        if error.is_some() {
258            return;
259        }
260        match node {
261            ScalarExpr::Column(column) => {
262                let target = public_columns.contains(column.as_str());
263                let source = scope
264                    .source
265                    .is_some_and(|source| source.has_unqualified_column(column));
266                if target && (source || scope.include_excluded) {
267                    error = Some(ambiguous_column(column));
268                } else if !target && !source {
269                    error = Some(SQLError::UnknownColumn(column.to_string()));
270                }
271            }
272            ScalarExpr::QualifiedColumn { qualifier, column }
273                if scope.target_qualifier(qualifier)
274                    && !public_columns.contains(column.as_str()) =>
275            {
276                error = Some(SQLError::UnknownColumn(format!("{qualifier}.{column}")));
277            }
278            _ => {}
279        }
280    });
281    error.map_or(Ok(()), Err)
282}
283
284pub(super) fn validate_public_update_contract(
285    services: ViewRewriteContext<'_>,
286    plan: &UpdatePlan,
287    source: Option<&RowSchema>,
288) -> Result<(), SQLError> {
289    let columns = public_view_columns(services, &plan.table)?;
290    let ordinary_scope = ExpressionScope {
291        target_qualifier: &plan.target_qualifier,
292        returning_aliases: None,
293        source,
294        include_excluded: false,
295    };
296    for assignment in &plan.assignments {
297        for expression in assignment.expressions() {
298            validate_public_view_expression(expression, &columns, ordinary_scope)?;
299        }
300    }
301    if let Some(predicate) = plan.predicate.as_ref() {
302        validate_public_view_expression(predicate, &columns, ordinary_scope)?;
303    }
304    let returning_scope = ExpressionScope {
305        returning_aliases: Some(&plan.returning_aliases),
306        ..ordinary_scope
307    };
308    for projection in &plan.returning {
309        validate_public_view_expression(&projection.expr, &columns, returning_scope)?;
310    }
311    Ok(())
312}
313
314pub(super) fn validate_public_delete_contract(
315    services: ViewRewriteContext<'_>,
316    plan: &DeletePlan,
317    source: Option<&RowSchema>,
318) -> Result<(), SQLError> {
319    let columns = public_view_columns(services, &plan.table)?;
320    let ordinary_scope = ExpressionScope {
321        target_qualifier: &plan.target_qualifier,
322        returning_aliases: None,
323        source,
324        include_excluded: false,
325    };
326    if let Some(predicate) = plan.predicate.as_ref() {
327        validate_public_view_expression(predicate, &columns, ordinary_scope)?;
328    }
329    let returning_scope = ExpressionScope {
330        returning_aliases: Some(&plan.returning_aliases),
331        ..ordinary_scope
332    };
333    for projection in &plan.returning {
334        validate_public_view_expression(&projection.expr, &columns, returning_scope)?;
335    }
336    Ok(())
337}
338
339pub(super) fn validate_public_insert_contract(
340    services: ViewRewriteContext<'_>,
341    plan: &InsertPlan,
342) -> Result<(), SQLError> {
343    let columns = public_view_columns(services, &plan.table)?;
344    for predicate in plan.on_conflict.iter().flat_map(|conflict| {
345        conflict
346            .expressions
347            .iter()
348            .chain(conflict.predicate.iter().map(Box::as_ref))
349    }) {
350        validate_public_view_expression(
351            predicate,
352            &columns,
353            ExpressionScope {
354                target_qualifier: &plan.target_qualifier,
355                returning_aliases: None,
356                source: None,
357                include_excluded: false,
358            },
359        )?;
360    }
361    if let Some(ConflictPlan {
362        action:
363            ConflictActionPlan::Update {
364                assignments,
365                predicate,
366            },
367        ..
368    }) = &plan.on_conflict
369    {
370        let scope = ExpressionScope {
371            target_qualifier: &plan.target_qualifier,
372            returning_aliases: None,
373            source: None,
374            include_excluded: true,
375        };
376        for assignment in assignments {
377            for expression in assignment.expressions() {
378                validate_public_view_expression(expression, &columns, scope)?;
379            }
380        }
381        if let Some(predicate) = predicate {
382            validate_public_view_expression(predicate, &columns, scope)?;
383        }
384    }
385    let scope = ExpressionScope {
386        target_qualifier: &plan.target_qualifier,
387        returning_aliases: Some(&plan.returning_aliases),
388        source: None,
389        include_excluded: false,
390    };
391    for projection in &plan.returning {
392        validate_public_view_expression(&projection.expr, &columns, scope)?;
393    }
394    Ok(())
395}
396
397pub fn validate_public_merge_contract(
398    services: ViewRewriteContext<'_>,
399    plan: &MergePlan,
400    source: &RowSchema,
401) -> Result<(), SQLError> {
402    let columns = public_view_columns(services, &plan.target)?;
403    let matched_scope = ExpressionScope {
404        target_qualifier: &plan.target_qualifier,
405        returning_aliases: None,
406        source: Some(source),
407        include_excluded: false,
408    };
409    let target_only_scope = ExpressionScope {
410        source: None,
411        ..matched_scope
412    };
413    validate_public_view_expression(&plan.join_condition, &columns, matched_scope)?;
414    for clause in &plan.when_clauses {
415        match clause {
416            MergeWhenPlan::UpdateMatched {
417                condition,
418                assignments,
419            } => {
420                if let Some(condition) = condition {
421                    validate_public_view_expression(condition, &columns, matched_scope)?;
422                }
423                for assignment in assignments {
424                    for expression in assignment.expressions() {
425                        validate_public_view_expression(expression, &columns, matched_scope)?;
426                    }
427                }
428            }
429            MergeWhenPlan::DeleteMatched { condition }
430            | MergeWhenPlan::NothingMatched { condition } => {
431                if let Some(condition) = condition {
432                    validate_public_view_expression(condition, &columns, matched_scope)?;
433                }
434            }
435            MergeWhenPlan::UpdateNotMatchedBySource {
436                condition,
437                assignments,
438            } => {
439                if let Some(condition) = condition {
440                    validate_public_view_expression(condition, &columns, target_only_scope)?;
441                }
442                for assignment in assignments {
443                    validate_public_view_expression(
444                        &assignment.value,
445                        &columns,
446                        target_only_scope,
447                    )?;
448                }
449            }
450            MergeWhenPlan::DeleteNotMatchedBySource { condition }
451            | MergeWhenPlan::NothingNotMatchedBySource { condition } => {
452                if let Some(condition) = condition {
453                    validate_public_view_expression(condition, &columns, target_only_scope)?;
454                }
455            }
456            MergeWhenPlan::InsertNotMatched { .. } | MergeWhenPlan::NothingNotMatched { .. } => {}
457        }
458    }
459    let returning_scope = ExpressionScope {
460        returning_aliases: Some(&plan.returning_aliases),
461        ..matched_scope
462    };
463    for projection in &plan.returning {
464        validate_public_view_expression(&projection.expr, &columns, returning_scope)?;
465    }
466    Ok(())
467}
468
469pub(super) fn validate_merge_targets(
470    layer: &AutomaticViewLayer,
471    plan: &MergePlan,
472) -> Result<(), SQLError> {
473    for clause in &plan.when_clauses {
474        match clause {
475            MergeWhenPlan::UpdateMatched { assignments, .. }
476            | MergeWhenPlan::UpdateNotMatchedBySource { assignments, .. } => {
477                validate_view_target_columns(
478                    layer,
479                    assignments
480                        .iter()
481                        .flat_map(|assignment| assignment.target.targets()),
482                    duplicate_assignment,
483                )?;
484            }
485            MergeWhenPlan::InsertNotMatched { columns, .. } if !columns.is_empty() => {
486                validate_view_target_columns(layer, columns.iter(), duplicate_insert_column)?;
487            }
488            _ => {}
489        }
490    }
491    Ok(())
492}
493
494pub fn validate_public_merge_targets(
495    services: ViewRewriteContext<'_>,
496    plan: &MergePlan,
497) -> Result<(), SQLError> {
498    for clause in &plan.when_clauses {
499        match clause {
500            MergeWhenPlan::UpdateMatched { assignments, .. }
501            | MergeWhenPlan::UpdateNotMatchedBySource { assignments, .. } => {
502                validate_public_view_targets(
503                    services,
504                    &plan.target,
505                    assignments
506                        .iter()
507                        .flat_map(|assignment| assignment.target.column_names()),
508                )?;
509                validate_mapped_columns(
510                    &assignments
511                        .iter()
512                        .flat_map(|assignment| assignment.target.targets().iter().cloned())
513                        .collect::<Vec<_>>(),
514                    duplicate_assignment,
515                )?;
516            }
517            MergeWhenPlan::InsertNotMatched { columns, .. } if !columns.is_empty() => {
518                validate_public_view_targets(
519                    services,
520                    &plan.target,
521                    columns.iter().map(|target| target.column.as_str()),
522                )?;
523                validate_mapped_columns(columns, duplicate_insert_column)?;
524            }
525            _ => {}
526        }
527    }
528    Ok(())
529}
530
531fn validate_merge_rule_free(
532    services: ViewRewriteContext<'_>,
533    relation: &str,
534) -> Result<(), SQLError> {
535    let has_rules = [
536        crate::ast::RuleEvent::Insert,
537        crate::ast::RuleEvent::Update,
538        crate::ast::RuleEvent::Delete,
539    ]
540    .into_iter()
541    .map(|event| services.catalog.rules_for(relation, event))
542    .collect::<Result<Vec<_>, SQLError>>()?
543    .iter()
544    .any(|rules| !rules.is_empty());
545    if !has_rules {
546        return Ok(());
547    }
548    Err(merge_with_rules(relation))
549}
550
551#[derive(Debug, Clone, Copy, PartialEq, Eq)]
552pub enum MergeViewTargetPath {
553    AutomaticRewrite,
554    ViewTriggers,
555}
556
557/// Choose how a MERGE reaches view `plan.target`, as `RewriteQuery` and `rewriteTargetView` do: through the view's INSTEAD OF triggers when every action has one, and otherwise by rewriting the view onto its base relation, which its query must allow, with an updatable column when an action inserts or updates. When it does not, the first action in WHEN order without a trigger reports why; an automatically updatable view cannot mix the two paths.
558pub fn merge_view_target_path(
559    services: ViewRewriteContext<'_>,
560    plan: &MergePlan,
561) -> Result<MergeViewTargetPath, SQLError> {
562    let canonical = services
563        .catalog
564        .try_resolve_view_name(&plan.target)
565        .map_err(|error| SQLError::Internal(format!("resolve MERGE view: {error}")))?
566        .ok_or_else(|| SQLError::UnknownTable(plan.target.clone()))?;
567    let definition = services
568        .catalog
569        .view_definition(&canonical)?
570        .ok_or_else(|| SQLError::UnknownTable(plan.target.clone()))?;
571    if definition.kind == crate::catalog::view::StoredViewKind::Materialized {
572        return Err(merge_into_materialized_view(&canonical));
573    }
574    validate_merge_rule_free(services, &canonical)?;
575    let mut untriggered = None;
576    let mut triggered = false;
577    for clause in &plan.when_clauses {
578        let Some((event, command)) = merge_clause_command(clause) else {
579            continue;
580        };
581        if instead_of_trigger_definition(services, &canonical, event)? {
582            triggered = true;
583        } else if untriggered.is_none() {
584            untriggered = Some(command);
585        }
586    }
587    let Some(untriggered) = untriggered else {
588        return Ok(MergeViewTargetPath::ViewTriggers);
589    };
590    let writes_columns = plan.when_clauses.iter().any(|clause| {
591        matches!(
592            clause,
593            MergeWhenPlan::InsertNotMatched { .. }
594                | MergeWhenPlan::UpdateMatched { .. }
595                | MergeWhenPlan::UpdateNotMatchedBySource { .. }
596        )
597    });
598    let reason = match view_query_shape(services, &canonical)? {
599        Some(ViewQueryShape::Updatable(layer)) => (writes_columns && !layer.has_writable_column())
600            .then_some(NotUpdatableReason::NoUpdatableColumns),
601        Some(ViewQueryShape::NotUpdatable(reason)) => Some(reason),
602        None => return Err(SQLError::UnknownTable(plan.target.clone())),
603    };
604    if let Some(reason) = reason {
605        return Err(view_not_updatable(&canonical, untriggered, reason));
606    }
607    if triggered {
608        return Err(mixed_merge_paths(&canonical));
609    }
610    Ok(MergeViewTargetPath::AutomaticRewrite)
611}
612
613/// The trigger event and the view command of a MERGE action that writes, or `None` for `DO NOTHING`.
614fn merge_clause_command(clause: &MergeWhenPlan) -> Option<(TriggerEvent, ViewCommand)> {
615    match clause {
616        MergeWhenPlan::InsertNotMatched { .. } => {
617            Some((TriggerEvent::Insert, ViewCommand::MergeInsert))
618        }
619        MergeWhenPlan::UpdateMatched { .. } | MergeWhenPlan::UpdateNotMatchedBySource { .. } => {
620            Some((TriggerEvent::Update, ViewCommand::MergeUpdate))
621        }
622        MergeWhenPlan::DeleteMatched { .. } | MergeWhenPlan::DeleteNotMatchedBySource { .. } => {
623            Some((TriggerEvent::Delete, ViewCommand::MergeDelete))
624        }
625        MergeWhenPlan::NothingMatched { .. }
626        | MergeWhenPlan::NothingNotMatched { .. }
627        | MergeWhenPlan::NothingNotMatchedBySource { .. } => None,
628    }
629}
630
631pub(super) fn validate_public_view_targets<'a>(
632    services: ViewRewriteContext<'_>,
633    view: &str,
634    columns: impl IntoIterator<Item = &'a str>,
635) -> Result<(), SQLError> {
636    let definition = services
637        .catalog
638        .view_definition(view)?
639        .ok_or_else(|| SQLError::UnknownTable(view.to_string()))?;
640    let schema = stored_view_schema(services, &definition)?;
641    for column in columns {
642        if !schema.has_unqualified_column(column) {
643            return Err(SQLError::UnknownColumn(format!("{view}.{column}")));
644        }
645    }
646    Ok(())
647}
648
649/// Reject a view whose rules for `event` are conditional INSTEAD rules without an unconditional one, as `RewriteQuery` does before it tries to rewrite the view.
650pub(super) fn validate_direct_view_rule_path(
651    services: ViewRewriteContext<'_>,
652    view: &str,
653    event: crate::ast::RuleEvent,
654    command: ViewCommand,
655) -> Result<(), SQLError> {
656    let rules = services.catalog.rules_for(view, event)?;
657    let has_conditional_instead = rules
658        .iter()
659        .any(|rule| rule.definition.instead && rule.definition.condition.is_some());
660    let has_unconditional_instead = rules
661        .iter()
662        .any(|rule| rule.definition.instead && rule.definition.condition.is_none());
663    if has_conditional_instead && !has_unconditional_instead {
664        return Err(view_not_updatable(
665            view,
666            command,
667            NotUpdatableReason::ConditionalInsteadRule,
668        ));
669    }
670    Ok(())
671}
672
673use super::stored_view_schema;