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        relations_bound: true,
240        ctes: inputs.ctes,
241        root: RelationalPlan::QueryBlock(Box::new(QueryBlockPlan {
242            privilege_columns: std::collections::BTreeSet::default(),
243            windows: Vec::new(),
244            projections,
245            from: inputs.source,
246            r#where: None,
247            compute: ComputePlan::Project,
248            group_by: Vec::new(),
249            grouping_sets: Vec::new(),
250            group_distinct: false,
251            having: None,
252            order_by: Vec::new(),
253            limit: None,
254            with_ties: false,
255            offset: None,
256            distinct: false,
257            distinct_on: Vec::new(),
258            subqueries: inputs.subqueries,
259            access: AccessPathPlan::Row,
260            locking: Vec::new(),
261        })),
262    };
263    mark_query_relations_bound(&mut query);
264    Ok(Some((query, Some(inputs.outer))))
265}
266
267fn command_statement_routine_inputs(
268    context: &CatalogRoutineContext<'_, '_>,
269    command: &CommandPlan,
270) -> Result<Option<CommandRoutineInputs>, SQLError> {
271    match command {
272        CommandPlan::Insert(plan) => insert_statement_routine_inputs(context, plan).map(Some),
273        CommandPlan::Update(plan) => update_statement_routine_inputs(context, plan).map(Some),
274        CommandPlan::Delete(plan) => delete_statement_routine_inputs(context, plan).map(Some),
275        CommandPlan::Merge(plan) => Ok(Some(merge_statement_routine_inputs(plan))),
276        CommandPlan::Notify { .. } => Ok(None),
277        _ => Err(SQLError::Internal(
278            "catalog-owned statement lowered to an unsupported command".into(),
279        )),
280    }
281}
282
283fn merge_statement_routine_inputs(plan: &MergePlan) -> CommandRoutineInputs {
284    let target = SourcePlan::Table {
285        executable_query: None,
286        bound_columns: None,
287        name: plan.target.clone(),
288        qualifier: plan.target_qualifier.clone(),
289        alias: plan.target_alias.clone(),
290        column_aliases: Vec::new(),
291        include_descendants: plan.include_descendants,
292    };
293    let source = SourcePlan::Join {
294        left: Box::new(target),
295        right: plan.source.clone(),
296        kind: crate::ast::JoinKind::Cross,
297        on: None,
298        using: None,
299        natural: false,
300        alias: None,
301        column_aliases: Vec::new(),
302        lateral: false,
303        strategy: JoinExecutionStrategy::default(),
304    };
305    let mut expressions = vec![plan.join_condition.clone()];
306    for clause in &plan.when_clauses {
307        match clause {
308            crate::plan::MergeWhenPlan::UpdateMatched {
309                condition,
310                assignments,
311            }
312            | crate::plan::MergeWhenPlan::UpdateNotMatchedBySource {
313                condition,
314                assignments,
315            } => {
316                expressions.extend(condition.iter().cloned());
317                expressions.extend(
318                    assignments
319                        .iter()
320                        .flat_map(crate::plan::AssignmentPlan::expressions)
321                        .cloned(),
322                );
323            }
324            crate::plan::MergeWhenPlan::InsertNotMatched {
325                condition,
326                columns,
327                values,
328                ..
329            } => {
330                expressions.extend(condition.iter().cloned());
331                expressions.extend(
332                    columns
333                        .iter()
334                        .flat_map(crate::ast::AssignmentTarget::expressions)
335                        .cloned(),
336                );
337                expressions.extend(values.iter().cloned());
338            }
339            crate::plan::MergeWhenPlan::DeleteMatched { condition }
340            | crate::plan::MergeWhenPlan::DeleteNotMatchedBySource { condition }
341            | crate::plan::MergeWhenPlan::NothingMatched { condition }
342            | crate::plan::MergeWhenPlan::NothingNotMatched { condition }
343            | crate::plan::MergeWhenPlan::NothingNotMatchedBySource { condition } => {
344                expressions.extend(condition.iter().cloned());
345            }
346        }
347    }
348    expressions.extend(
349        plan.returning
350            .iter()
351            .map(|projection| projection.expr.clone()),
352    );
353    CommandRoutineInputs {
354        ctes: plan.ctes.clone(),
355        source: Some(source),
356        expressions,
357        subqueries: plan.subqueries.clone(),
358        outer: RowSchema::default(),
359    }
360}
361
362fn insert_statement_routine_inputs(
363    context: &CatalogRoutineContext<'_, '_>,
364    plan: &InsertPlan,
365) -> Result<CommandRoutineInputs, SQLError> {
366    let mut expressions = plan
367        .columns
368        .iter()
369        .flat_map(crate::ast::AssignmentTarget::expressions)
370        .chain(plan.rows.iter().flatten())
371        .cloned()
372        .collect::<Vec<_>>();
373    if let Some(conflict) = &plan.on_conflict {
374        expressions.extend(conflict.expressions.iter().cloned());
375        expressions.extend(conflict.predicate.iter().map(Box::as_ref).cloned());
376        if let ConflictActionPlan::Update {
377            assignments,
378            predicate,
379        } = &conflict.action
380        {
381            expressions.extend(
382                assignments
383                    .iter()
384                    .flat_map(crate::plan::AssignmentPlan::expressions)
385                    .cloned(),
386            );
387            expressions.extend(predicate.iter().map(Box::as_ref).cloned());
388        }
389    }
390    expressions.extend(
391        plan.returning
392            .iter()
393            .map(|projection| projection.expr.clone()),
394    );
395    let source = plan.source.as_ref().map(|source| SourcePlan::Subquery {
396        body: Box::new((**source).clone()),
397        alias: Some("__uqa_catalog_statement_source".into()),
398        column_aliases: Vec::new(),
399    });
400    let mut outer = statement_target_outer_schema(
401        context,
402        &plan.table,
403        &plan.target_qualifier,
404        &plan.returning_aliases,
405    )?;
406    // `ON CONFLICT DO UPDATE` also sees the proposed row as `excluded`.
407    if matches!(
408        plan.on_conflict.as_ref().map(|conflict| &conflict.action),
409        Some(ConflictActionPlan::Update { .. })
410    ) {
411        let target = statement_target_schema(context, &plan.table, &plan.target_qualifier)?;
412        let excluded = RowSchema::with_relation_qualifier(&target, "excluded");
413        outer = RowSchema::join(&outer, &excluded, std::iter::empty::<String>());
414    }
415    Ok(CommandRoutineInputs {
416        ctes: plan.ctes.clone(),
417        source,
418        expressions,
419        subqueries: plan.subqueries.clone(),
420        outer,
421    })
422}
423
424fn update_statement_routine_inputs(
425    context: &CatalogRoutineContext<'_, '_>,
426    plan: &UpdatePlan,
427) -> Result<CommandRoutineInputs, SQLError> {
428    let mut expressions = plan
429        .assignments
430        .iter()
431        .flat_map(crate::plan::AssignmentPlan::expressions)
432        .cloned()
433        .collect::<Vec<_>>();
434    expressions.extend(plan.predicate.iter().cloned());
435    expressions.extend(
436        plan.returning
437            .iter()
438            .map(|projection| projection.expr.clone()),
439    );
440    Ok(CommandRoutineInputs {
441        ctes: plan.ctes.clone(),
442        source: plan.source.as_deref().cloned(),
443        expressions,
444        subqueries: plan.subqueries.clone(),
445        outer: statement_target_outer_schema(
446            context,
447            &plan.table,
448            &plan.target_qualifier,
449            &plan.returning_aliases,
450        )?,
451    })
452}
453
454fn delete_statement_routine_inputs(
455    context: &CatalogRoutineContext<'_, '_>,
456    plan: &DeletePlan,
457) -> Result<CommandRoutineInputs, SQLError> {
458    let mut expressions = plan.predicate.iter().cloned().collect::<Vec<_>>();
459    expressions.extend(
460        plan.returning
461            .iter()
462            .map(|projection| projection.expr.clone()),
463    );
464    Ok(CommandRoutineInputs {
465        ctes: plan.ctes.clone(),
466        source: plan.source.as_deref().cloned(),
467        expressions,
468        subqueries: plan.subqueries.clone(),
469        outer: statement_target_outer_schema(
470            context,
471            &plan.table,
472            &plan.target_qualifier,
473            &plan.returning_aliases,
474        )?,
475    })
476}
477
478fn statement_target_outer_schema(
479    context: &CatalogRoutineContext<'_, '_>,
480    table: &str,
481    target_qualifier: &str,
482    aliases: &crate::ast::ReturningAliases,
483) -> Result<RowSchema, SQLError> {
484    let target = statement_target_schema(context, table, target_qualifier)?;
485    Ok(crate::semantics::returning_expression_schema(
486        &target,
487        target_qualifier,
488        aliases,
489        None,
490    ))
491}
492
493fn statement_target_schema(
494    context: &CatalogRoutineContext<'_, '_>,
495    table: &str,
496    target_qualifier: &str,
497) -> Result<RowSchema, SQLError> {
498    let target = crate::binding::analyze_source_plan_schema(
499        context.routines,
500        &SourcePlan::Table {
501            executable_query: None,
502            bound_columns: None,
503            name: table.to_string(),
504            qualifier: target_qualifier.to_string(),
505            alias: None,
506            column_aliases: Vec::new(),
507            include_descendants: true,
508        },
509        &[],
510        context.binding,
511        None,
512    )?;
513    Ok(
514        RowSchema::with_types(target.columns().to_vec(), target.column_types().to_vec())
515            .with_column_collations(
516                (0..target.len()).map(|position| target.column_collation(position)),
517            ),
518    )
519}
520
521/// Routine identities of a bound stored expression in syntax order.
522pub fn collect_expression_routine_references(
523    expression: &crate::plan::ExpressionPlan,
524) -> Result<Vec<BoundRoutineReference>, SQLError> {
525    Ok(super::syntax_sites::expression_syntax_sites(expression, expression)?.routines)
526}
527
528fn mark_cte_relations_bound(body: &mut crate::plan::CtePlanBody) {
529    match body {
530        crate::plan::CtePlanBody::Query(query) => mark_query_relations_bound(query),
531        crate::plan::CtePlanBody::Command(command) => {
532            match command.as_mut() {
533                CommandPlan::Insert(plan) => {
534                    plan.relations_bound = true;
535                    plan.target_relation_bound = true;
536                }
537                CommandPlan::Update(plan) => {
538                    plan.relations_bound = true;
539                    plan.target_relation_bound = true;
540                }
541                CommandPlan::Delete(plan) => {
542                    plan.relations_bound = true;
543                    plan.target_relation_bound = true;
544                }
545                _ => {}
546            }
547            if let Some(ctes) = command.ctes_mut() {
548                for cte in ctes {
549                    mark_cte_relations_bound(&mut cte.body);
550                }
551            }
552            for query in command.query_inputs_mut() {
553                mark_query_relations_bound(query);
554            }
555            if let Some(source) = command.source_input_mut() {
556                mark_source_relations_bound(source);
557            }
558        }
559    }
560}
561
562fn mark_query_relations_bound(query: &mut QueryPlan) {
563    query.relations_bound = true;
564    for cte in &mut query.ctes {
565        mark_cte_relations_bound(&mut cte.body);
566    }
567    match &mut query.root {
568        RelationalPlan::QueryBlock(block) => {
569            if let Some(source) = &mut block.from {
570                mark_source_relations_bound(source);
571            }
572            for subquery in &mut block.subqueries {
573                mark_query_relations_bound(subquery);
574            }
575        }
576        RelationalPlan::SetOp {
577            left,
578            right,
579            subqueries,
580            ..
581        } => {
582            mark_query_relations_bound(left);
583            mark_query_relations_bound(right);
584            for subquery in subqueries {
585                mark_query_relations_bound(subquery);
586            }
587        }
588        RelationalPlan::Values { subqueries, .. } => {
589            for subquery in subqueries {
590                mark_query_relations_bound(subquery);
591            }
592        }
593    }
594}
595
596fn mark_source_relations_bound(source: &mut SourcePlan) {
597    match source {
598        crate::plan::SourcePlan::RetainedRows(_) => {}
599        SourcePlan::Join { left, right, .. } => {
600            mark_source_relations_bound(left);
601            mark_source_relations_bound(right);
602        }
603        SourcePlan::Subquery { body, .. } => mark_query_relations_bound(body),
604        SourcePlan::Table { .. }
605        | SourcePlan::Values { .. }
606        | SourcePlan::Function { .. }
607        | SourcePlan::FunctionGroup { .. } => {}
608    }
609}
610
611pub mod analysis;