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

uqa_sql/binding/
stored_routines.rs
