Skip to main content

uqa_sql/binding/
stored_routines.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Exact routine binding for catalog-owned statements.
8
9use crate::ast::FunctionBinding;
10use crate::plan::{
11    AccessPathPlan, CommandPlan, ComputePlan, ConflictActionPlan, CtePlan, DeletePlan, InsertPlan,
12    JoinExecutionStrategy, MergePlan, ProjectionPlan, QueryBlockPlan, QueryPlan, RelationalPlan,
13    SourcePlan, UnifiedPlan, UpdatePlan,
14};
15use crate::SQLError;
16use crate::{RowSchema, ScalarExpr};
17
18use crate::{binding::context::BindingContext, routines::RoutineResolution};
19use uqa_core::Value;
20
21/// Routine metadata and immutable namespace inputs for a stored statement.
22pub struct CatalogRoutineContext<'a, 'q> {
23    pub routines: &'a dyn RoutineResolution,
24    pub binding: &'a BindingContext<'q>,
25}
26
27pub struct BoundStatementRoutines {
28    pub query: Option<QueryPlan>,
29    /// What binding recorded for the statement's syntax, in syntax order.
30    pub sites: super::syntax_sites::SyntaxSites,
31    /// The columns a query statement returns; a command's binding query is not its result.
32    pub query_output: Option<RowSchema>,
33}
34
35#[derive(Debug, Clone)]
36pub struct BoundRoutineReference {
37    pub name: String,
38    pub binding: Option<FunctionBinding>,
39}
40
41struct CommandRoutineInputs {
42    ctes: Vec<CtePlan>,
43    source: Option<SourcePlan>,
44    expressions: Vec<ScalarExpr>,
45    subqueries: Vec<QueryPlan>,
46    outer: RowSchema,
47}
48
49/// Bind a copy of a catalog-owned statement lowered from its stored syntax; `params` types the positional parameters its syntax references.
50pub fn bind_catalog_statement_routines(
51    context: &CatalogRoutineContext<'_, '_>,
52    plan: &UnifiedPlan,
53    params: &[crate::SQLParam],
54) -> Result<BoundStatementRoutines, SQLError> {
55    bind_catalog_statement(context, plan, None, |query, outer| {
56        crate::binding::bind_syntax_query_plan_routines(
57            context.routines,
58            query,
59            params,
60            context.binding,
61            outer,
62        )
63    })
64}
65
66/// Retain the original syntax correspondence after full statement input analysis has converted literals in a separate plan.
67pub(crate) fn bind_prepared_catalog_statement_routines(
68    context: &CatalogRoutineContext<'_, '_>,
69    syntax: &UnifiedPlan,
70    prepared: &UnifiedPlan,
71    params: &[crate::SQLParam],
72) -> Result<BoundStatementRoutines, SQLError> {
73    bind_catalog_statement(context, syntax, Some(prepared), |query, outer| {
74        crate::binding::bind_syntax_query_plan_routines(
75            context.routines,
76            query,
77            params,
78            context.binding,
79            outer,
80        )
81    })
82}
83
84pub(super) fn bind_catalog_statement_composite_inputs(
85    context: &CatalogRoutineContext<'_, '_>,
86    plan: &UnifiedPlan,
87    params: &[crate::SQLParam],
88    outer: Option<&RowSchema>,
89) -> Result<BoundStatementRoutines, SQLError> {
90    bind_catalog_statement(context, plan, None, |query, command_outer| {
91        let mut scope = super::SchemaScope::for_analysis(context.binding)?;
92        scope.scalar_binding = super::ScalarBindingMode::CompositeInputs;
93        scope.preserve_syntax_shape = true;
94        scope.stored_expression_outer = outer.cloned();
95        let combined = match (command_outer, outer) {
96            (Some(left), Some(right)) => Some(RowSchema::join(left, right, [])),
97            (Some(schema), None) | (None, Some(schema)) => Some(schema.clone()),
98            (None, None) => None,
99        };
100        scope.bind_query_routines_for_storage(context.routines, query, params, combined.as_ref())
101    })
102}
103
104fn bind_catalog_statement(
105    context: &CatalogRoutineContext<'_, '_>,
106    plan: &UnifiedPlan,
107    prepared: Option<&UnifiedPlan>,
108    bind: impl FnOnce(&mut QueryPlan, Option<&RowSchema>) -> Result<RowSchema, SQLError>,
109) -> Result<BoundStatementRoutines, SQLError> {
110    let lower = |plan: &UnifiedPlan| -> Result<_, SQLError> {
111        match plan {
112            UnifiedPlan::Query(query) => {
113                let mut query = (**query).clone();
114                mark_query_relations_bound(&mut query);
115                Ok(Some((query, None)))
116            }
117            UnifiedPlan::Command(command) => command_statement_query(context, command),
118        }
119    };
120    let lowered = lower(plan)?;
121    let Some((lowered, outer)) = lowered else {
122        return Ok(BoundStatementRoutines {
123            query: None,
124            sites: super::syntax_sites::SyntaxSites::default(),
125            query_output: None,
126        });
127    };
128    let mut query = match prepared {
129        Some(prepared) => {
130            lower(prepared)?
131                .ok_or_else(|| {
132                    SQLError::Internal(
133                        "prepared catalog statement lost its analyzed expressions".into(),
134                    )
135                })?
136                .0
137        }
138        None => lowered.clone(),
139    };
140    let output = bind(&mut query, outer.as_ref())?;
141    let sites = if matches!(plan, UnifiedPlan::Command(_)) {
142        super::syntax_sites::command_query_syntax_sites(&lowered, &query)?
143    } else {
144        super::syntax_sites::query_syntax_sites(&lowered, &query)?
145    };
146    Ok(BoundStatementRoutines {
147        query: Some(query),
148        sites,
149        query_output: matches!(plan, UnifiedPlan::Query(_)).then_some(output),
150    })
151}
152
153pub fn mark_catalog_statement_relations_bound(plan: &mut UnifiedPlan) -> Result<(), SQLError> {
154    match plan {
155        UnifiedPlan::Query(query) => mark_query_relations_bound(query),
156        UnifiedPlan::Command(command) => match command.as_mut() {
157            CommandPlan::Insert(plan) => {
158                plan.relations_bound = true;
159                for cte in &mut plan.ctes {
160                    mark_cte_relations_bound(&mut cte.body);
161                }
162                if let Some(source) = &mut plan.source {
163                    mark_query_relations_bound(source);
164                }
165                for subquery in &mut plan.subqueries {
166                    mark_query_relations_bound(subquery);
167                }
168            }
169            CommandPlan::Update(plan) => {
170                plan.relations_bound = true;
171                for cte in &mut plan.ctes {
172                    mark_cte_relations_bound(&mut cte.body);
173                }
174                if let Some(source) = &mut plan.source {
175                    mark_source_relations_bound(source);
176                }
177                for subquery in &mut plan.subqueries {
178                    mark_query_relations_bound(subquery);
179                }
180            }
181            CommandPlan::Delete(plan) => {
182                plan.relations_bound = true;
183                for cte in &mut plan.ctes {
184                    mark_cte_relations_bound(&mut cte.body);
185                }
186                if let Some(source) = &mut plan.source {
187                    mark_source_relations_bound(source);
188                }
189                for subquery in &mut plan.subqueries {
190                    mark_query_relations_bound(subquery);
191                }
192            }
193            CommandPlan::Notify { .. } => {}
194            CommandPlan::Merge(plan) => {
195                for cte in &mut plan.ctes {
196                    mark_cte_relations_bound(&mut cte.body);
197                }
198                mark_source_relations_bound(&mut plan.source);
199                for subquery in &mut plan.subqueries {
200                    mark_query_relations_bound(subquery);
201                }
202            }
203            _ => {
204                return Err(SQLError::Internal(
205                    "catalog-owned statement lowered to an unsupported command".into(),
206                ));
207            }
208        },
209    }
210    Ok(())
211}
212
213/// A command's syntax as one query whose select list holds the command's expressions in syntax order, so the command binds and reads back like a query. `DEFAULT` markers have no scalar syntax to bind.
214fn command_statement_query(
215    context: &CatalogRoutineContext<'_, '_>,
216    command: &CommandPlan,
217) -> Result<Option<(QueryPlan, Option<RowSchema>)>, SQLError> {
218    let Some(inputs) = command_statement_routine_inputs(context, command)? else {
219        return Ok(None);
220    };
221    let projections = inputs
222        .expressions
223        .into_iter()
224        .map(|expr| ProjectionPlan {
225            // DEFAULT has a syntax position but no independently bound value in this analysis query.
226            expr: if matches!(expr, ScalarExpr::Default) {
227                ScalarExpr::Literal(Value::Int(1))
228            } else {
229                expr
230            },
231            alias: None,
232        })
233        .chain(std::iter::once(ProjectionPlan {
234            expr: ScalarExpr::Literal(Value::Int(1)),
235            alias: None,
236        }))
237        .collect();
238    let mut query = QueryPlan {
239        analyzed_output: None,
240        analyzed_source: None,
241        analyzed_grouping: None,
242        relation_privilege_subject: None,
243        relations_bound: true,
244        ctes: inputs.ctes,
245        root: RelationalPlan::QueryBlock(Box::new(QueryBlockPlan {
246            deferred_outer_rewrites: Vec::new(),
247            privilege_columns: uqa_core::memory::OwnedSet::new(),
248            windows: Vec::new(),
249            projections,
250            from: inputs.source,
251            r#where: None,
252            compute: ComputePlan::Project,
253            group_by: Vec::new(),
254            grouping_sets: Vec::new(),
255            group_distinct: false,
256            having: None,
257            order_by: Vec::new(),
258            limit: None,
259            with_ties: false,
260            offset: None,
261            distinct: false,
262            distinct_on: Vec::new(),
263            subqueries: inputs.subqueries,
264            access: AccessPathPlan::Row,
265            locking: Vec::new(),
266        })),
267    };
268    mark_query_relations_bound(&mut query);
269    Ok(Some((query, Some(inputs.outer))))
270}
271
272fn command_statement_routine_inputs(
273    context: &CatalogRoutineContext<'_, '_>,
274    command: &CommandPlan,
275) -> Result<Option<CommandRoutineInputs>, SQLError> {
276    match command {
277        CommandPlan::Insert(plan) => insert_statement_routine_inputs(context, plan).map(Some),
278        CommandPlan::Update(plan) => update_statement_routine_inputs(context, plan).map(Some),
279        CommandPlan::Delete(plan) => delete_statement_routine_inputs(context, plan).map(Some),
280        CommandPlan::Merge(plan) => Ok(Some(merge_statement_routine_inputs(plan))),
281        CommandPlan::Notify { .. } => Ok(None),
282        _ => Err(SQLError::Internal(
283            "catalog-owned statement lowered to an unsupported command".into(),
284        )),
285    }
286}
287
288fn merge_statement_routine_inputs(plan: &MergePlan) -> CommandRoutineInputs {
289    let target = SourcePlan::Table {
290        executable_query: None,
291        bound_columns: None,
292        name: plan.target.clone(),
293        qualifier: plan.target_qualifier.clone(),
294        alias: plan.target_alias.clone(),
295        column_aliases: Vec::new(),
296        include_descendants: plan.include_descendants,
297    };
298    let source = SourcePlan::Join {
299        comma: false,
300        left: Box::new(target),
301        right: plan.source.clone(),
302        kind: crate::ast::JoinKind::Cross,
303        on: None,
304        using: None,
305        natural: false,
306        alias: None,
307        column_aliases: Vec::new(),
308        lateral: false,
309        strategy: JoinExecutionStrategy::default(),
310    };
311    let mut expressions = vec![plan.join_condition.clone()];
312    for clause in &plan.when_clauses {
313        match clause {
314            crate::plan::MergeWhenPlan::UpdateMatched {
315                condition,
316                assignments,
317            }
318            | crate::plan::MergeWhenPlan::UpdateNotMatchedBySource {
319                condition,
320                assignments,
321            } => {
322                expressions.extend(condition.iter().cloned());
323                expressions.extend(
324                    assignments
325                        .iter()
326                        .flat_map(crate::plan::AssignmentPlan::expressions)
327                        .cloned(),
328                );
329            }
330            crate::plan::MergeWhenPlan::InsertNotMatched {
331                condition,
332                columns,
333                values,
334                ..
335            } => {
336                expressions.extend(condition.iter().cloned());
337                expressions.extend(
338                    columns
339                        .iter()
340                        .flat_map(crate::ast::AssignmentTarget::expressions)
341                        .cloned(),
342                );
343                expressions.extend(values.iter().cloned());
344            }
345            crate::plan::MergeWhenPlan::DeleteMatched { condition }
346            | crate::plan::MergeWhenPlan::DeleteNotMatchedBySource { condition }
347            | crate::plan::MergeWhenPlan::NothingMatched { condition }
348            | crate::plan::MergeWhenPlan::NothingNotMatched { condition }
349            | crate::plan::MergeWhenPlan::NothingNotMatchedBySource { condition } => {
350                expressions.extend(condition.iter().cloned());
351            }
352        }
353    }
354    expressions.extend(
355        plan.returning
356            .iter()
357            .map(|projection| projection.expr.clone()),
358    );
359    CommandRoutineInputs {
360        ctes: plan.ctes.clone(),
361        source: Some(source),
362        expressions,
363        subqueries: plan.subqueries.clone(),
364        outer: RowSchema::default(),
365    }
366}
367
368fn insert_statement_routine_inputs(
369    context: &CatalogRoutineContext<'_, '_>,
370    plan: &InsertPlan,
371) -> Result<CommandRoutineInputs, SQLError> {
372    let mut expressions = plan
373        .columns
374        .iter()
375        .flat_map(crate::ast::AssignmentTarget::expressions)
376        .chain(plan.rows.iter().flatten())
377        .cloned()
378        .collect::<Vec<_>>();
379    if let Some(conflict) = &plan.on_conflict {
380        expressions.extend(conflict.expressions.iter().cloned());
381        expressions.extend(conflict.predicate.iter().map(Box::as_ref).cloned());
382        if let ConflictActionPlan::Update {
383            assignments,
384            predicate,
385        } = &conflict.action
386        {
387            expressions.extend(
388                assignments
389                    .iter()
390                    .flat_map(crate::plan::AssignmentPlan::expressions)
391                    .cloned(),
392            );
393            expressions.extend(predicate.iter().map(Box::as_ref).cloned());
394        }
395    }
396    expressions.extend(
397        plan.returning
398            .iter()
399            .map(|projection| projection.expr.clone()),
400    );
401    let source = plan.source.as_ref().map(|source| SourcePlan::Subquery {
402        captured_output: None,
403        lateral: false,
404        body: Box::new((**source).clone()),
405        alias: Some("__uqa_catalog_statement_source".into()),
406        column_aliases: Vec::new(),
407    });
408    let mut outer = statement_target_outer_schema(
409        context,
410        &plan.table,
411        &plan.target_qualifier,
412        &plan.returning_aliases,
413    )?;
414    // `ON CONFLICT DO UPDATE` also sees the proposed row as `excluded`.
415    if matches!(
416        plan.on_conflict.as_ref().map(|conflict| &conflict.action),
417        Some(ConflictActionPlan::Update { .. })
418    ) {
419        let target = statement_target_schema(context, &plan.table, &plan.target_qualifier)?;
420        let excluded = RowSchema::with_relation_qualifier(&target, "excluded");
421        outer = RowSchema::join(&outer, &excluded, std::iter::empty::<String>());
422    }
423    Ok(CommandRoutineInputs {
424        ctes: plan.ctes.clone(),
425        source,
426        expressions,
427        subqueries: plan.subqueries.clone(),
428        outer,
429    })
430}
431
432fn update_statement_routine_inputs(
433    context: &CatalogRoutineContext<'_, '_>,
434    plan: &UpdatePlan,
435) -> Result<CommandRoutineInputs, SQLError> {
436    let mut expressions = plan
437        .assignments
438        .iter()
439        .flat_map(crate::plan::AssignmentPlan::expressions)
440        .cloned()
441        .collect::<Vec<_>>();
442    expressions.extend(plan.predicate.iter().cloned());
443    expressions.extend(
444        plan.returning
445            .iter()
446            .map(|projection| projection.expr.clone()),
447    );
448    Ok(CommandRoutineInputs {
449        ctes: plan.ctes.clone(),
450        source: plan.source.as_deref().cloned(),
451        expressions,
452        subqueries: plan.subqueries.clone(),
453        outer: statement_target_outer_schema(
454            context,
455            &plan.table,
456            &plan.target_qualifier,
457            &plan.returning_aliases,
458        )?,
459    })
460}
461
462fn delete_statement_routine_inputs(
463    context: &CatalogRoutineContext<'_, '_>,
464    plan: &DeletePlan,
465) -> Result<CommandRoutineInputs, SQLError> {
466    let mut expressions = plan.predicate.iter().cloned().collect::<Vec<_>>();
467    expressions.extend(
468        plan.returning
469            .iter()
470            .map(|projection| projection.expr.clone()),
471    );
472    Ok(CommandRoutineInputs {
473        ctes: plan.ctes.clone(),
474        source: plan.source.as_deref().cloned(),
475        expressions,
476        subqueries: plan.subqueries.clone(),
477        outer: statement_target_outer_schema(
478            context,
479            &plan.table,
480            &plan.target_qualifier,
481            &plan.returning_aliases,
482        )?,
483    })
484}
485
486fn statement_target_outer_schema(
487    context: &CatalogRoutineContext<'_, '_>,
488    table: &str,
489    target_qualifier: &str,
490    aliases: &crate::ast::ReturningAliases,
491) -> Result<RowSchema, SQLError> {
492    let target = statement_target_schema(context, table, target_qualifier)?;
493    Ok(crate::semantics::returning_expression_schema(
494        &target,
495        target_qualifier,
496        aliases,
497        None,
498    ))
499}
500
501fn statement_target_schema(
502    context: &CatalogRoutineContext<'_, '_>,
503    table: &str,
504    target_qualifier: &str,
505) -> Result<RowSchema, SQLError> {
506    let target = crate::binding::analyze_source_plan_schema(
507        context.routines,
508        &SourcePlan::Table {
509            executable_query: None,
510            bound_columns: None,
511            name: table.to_string(),
512            qualifier: target_qualifier.to_string(),
513            alias: None,
514            column_aliases: Vec::new(),
515            include_descendants: true,
516        },
517        &[],
518        context.binding,
519        None,
520    )?;
521    Ok(
522        RowSchema::with_types(target.columns().to_vec(), target.column_types().to_vec())
523            .with_column_collations(
524                (0..target.len()).map(|position| target.column_collation(position)),
525            ),
526    )
527}
528
529/// Routine identities of a bound stored expression in syntax order.
530pub fn collect_expression_routine_references(
531    expression: &crate::plan::ExpressionPlan,
532) -> Result<Vec<BoundRoutineReference>, SQLError> {
533    Ok(super::syntax_sites::expression_syntax_sites(expression, expression)?.routines)
534}
535
536fn mark_cte_relations_bound(body: &mut crate::plan::CtePlanBody) {
537    match body {
538        crate::plan::CtePlanBody::Query(query) => mark_query_relations_bound(query),
539        crate::plan::CtePlanBody::Command(command) => {
540            match command.as_mut() {
541                CommandPlan::Insert(plan) => {
542                    plan.relations_bound = true;
543                    plan.target_relation_bound = true;
544                }
545                CommandPlan::Update(plan) => {
546                    plan.relations_bound = true;
547                    plan.target_relation_bound = true;
548                }
549                CommandPlan::Delete(plan) => {
550                    plan.relations_bound = true;
551                    plan.target_relation_bound = true;
552                }
553                _ => {}
554            }
555            if let Some(ctes) = command.ctes_mut() {
556                for cte in ctes {
557                    mark_cte_relations_bound(&mut cte.body);
558                }
559            }
560            for query in command.query_inputs_mut() {
561                mark_query_relations_bound(query);
562            }
563            if let Some(source) = command.source_input_mut() {
564                mark_source_relations_bound(source);
565            }
566        }
567    }
568}
569
570pub(crate) fn mark_query_relations_bound(query: &mut QueryPlan) {
571    query.relations_bound = true;
572    for cte in &mut query.ctes {
573        mark_cte_relations_bound(&mut cte.body);
574    }
575    match &mut query.root {
576        RelationalPlan::QueryBlock(block) => {
577            if let Some(source) = &mut block.from {
578                mark_source_relations_bound(source);
579            }
580            for subquery in &mut block.subqueries {
581                mark_query_relations_bound(subquery);
582            }
583        }
584        RelationalPlan::SetOp {
585            left,
586            right,
587            subqueries,
588            ..
589        } => {
590            mark_query_relations_bound(left);
591            mark_query_relations_bound(right);
592            for subquery in subqueries {
593                mark_query_relations_bound(subquery);
594            }
595        }
596        RelationalPlan::Values { subqueries, .. } => {
597            for subquery in subqueries {
598                mark_query_relations_bound(subquery);
599            }
600        }
601    }
602}
603
604fn mark_source_relations_bound(source: &mut SourcePlan) {
605    match source {
606        crate::plan::SourcePlan::RetainedRows(_) => {}
607        SourcePlan::Join { left, right, .. } => {
608            mark_source_relations_bound(left);
609            mark_source_relations_bound(right);
610        }
611        SourcePlan::Subquery { body, .. } => mark_query_relations_bound(body),
612        SourcePlan::Table { .. }
613        | SourcePlan::Values { .. }
614        | SourcePlan::Function { .. }
615        | SourcePlan::FunctionGroup { .. } => {}
616    }
617}
618
619pub mod analysis;