Skip to main content

uqa_sql/semantics/
volatility.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! SQL function volatility and expression rewrite safety over catalog metadata.
8//!
9//! Volatility is a semantic property, not merely an optimizer hint.  A
10//! `VOLATILE` call may not be duplicated, elided, moved to a different join
11//! level, or hidden behind a statement-local view cache.  Keep the decision in
12//! one place so the view, CTE, predicate-pushdown, column-pruning, and `DPccp`
13//! paths cannot drift apart.
14
15use std::collections::BTreeSet;
16
17use crate::ast::{FunctionBinding, FunctionVolatility};
18use crate::plan::{QueryBlockPlan, QueryPlan, RelationalPlan, SourcePlan, UnifiedPlan};
19use crate::SQLError;
20use crate::ScalarExpr;
21
22/// Metadata needed to classify SQL expressions without invoking a routine or reading a row.
23pub trait VolatilityCatalog {
24    fn host_function_volatility(&self, name: &str) -> Option<FunctionVolatility>;
25    fn routine_volatilities(
26        &self,
27        name: &str,
28        binding: Option<&FunctionBinding>,
29    ) -> Option<Vec<FunctionVolatility>>;
30    fn view_query(&self, name: &str) -> Result<Option<QueryPlan>, SQLError>;
31
32    /// Lend the selected view plan for one read. Catalogs retaining immutable definitions can override this adapter without constructing an owned plan.
33    fn with_view_query(
34        &self,
35        name: &str,
36        read: &mut dyn FnMut(&QueryPlan) -> Result<bool, SQLError>,
37    ) -> Result<Option<bool>, SQLError> {
38        self.view_query(name)?.as_ref().map(read).transpose()
39    }
40}
41
42use super::builtin_function_dispatch_name;
43
44/// Resolve the volatility of the implementation that can run for `name`.
45///
46/// Rust extension callbacks default to `VOLATILE`, while registrations with
47/// explicit options use their declared volatility. SQL routine overloads are
48/// combined conservatively: a name is only non-volatile when every overload
49/// registered under it is non-volatile. This remains correct before runtime
50/// argument coercion selects a particular overload.
51pub fn function_volatility(
52    catalog: &dyn VolatilityCatalog,
53    name: &str,
54    argument_count: usize,
55) -> FunctionVolatility {
56    function_volatility_with_binding(catalog, name, None, argument_count)
57}
58
59pub fn function_binding_is_volatile(
60    catalog: &dyn VolatilityCatalog,
61    name: &str,
62    binding: Option<&FunctionBinding>,
63    argument_count: usize,
64) -> bool {
65    function_volatility_with_binding(catalog, name, binding, argument_count)
66        == FunctionVolatility::Volatile
67}
68
69fn is_intrinsic_scalar_syntax(binding: Option<&FunctionBinding>) -> bool {
70    binding
71        .and_then(|binding| binding.dispatch)
72        .is_some_and(crate::ast::FunctionDispatch::is_concatenation)
73        || matches!(
74            binding.and_then(|binding| binding.dispatch),
75            Some(
76                crate::ast::FunctionDispatch::NumericOperator(_)
77                    | crate::ast::FunctionDispatch::Pattern { .. }
78                    | crate::ast::FunctionDispatch::PatternEscape { .. }
79                    | crate::ast::FunctionDispatch::Collate { .. }
80                    | crate::ast::FunctionDispatch::BooleanTest(_)
81                    | crate::ast::FunctionDispatch::ArraySubscripts
82                    | crate::ast::FunctionDispatch::ArraySlices
83                    | crate::ast::FunctionDispatch::ArraySlicesWithBounds(_)
84            )
85        )
86}
87
88pub fn function_volatility_with_binding(
89    catalog: &dyn VolatilityCatalog,
90    name: &str,
91    binding: Option<&FunctionBinding>,
92    argument_count: usize,
93) -> FunctionVolatility {
94    let builtin = builtin_function_volatility(name, binding, argument_count);
95    if is_intrinsic_scalar_syntax(binding) || builtin == FunctionVolatility::Volatile {
96        return builtin;
97    }
98    let identity = super::functions::lowercase_function_name(name);
99
100    // Registrations made through the original APIs retain the conservative
101    // VOLATILE default. Explicit options let pure callbacks participate in
102    // the same optimizer rules as declared SQL routines.
103    if let Some(volatility) = catalog.host_function_volatility(&identity) {
104        return volatility;
105    }
106
107    if let Some(volatility) = sql_routine_volatility(catalog, &identity, binding) {
108        return volatility;
109    }
110    builtin
111}
112
113/// Classify an already selected built-in without consulting host or SQL routine registrations. Callers that have not resolved the routine must use the catalog-aware entry point instead.
114pub fn builtin_function_volatility(
115    name: &str,
116    binding: Option<&FunctionBinding>,
117    argument_count: usize,
118) -> FunctionVolatility {
119    if let Some(crate::ast::FunctionDispatch::Concat { oid }) =
120        binding.and_then(|binding| binding.dispatch)
121    {
122        return oid
123            .and_then(|oid| crate::type_resolution::binary_operator_by_oid(i64::from(oid)))
124            .filter(|operator| operator.name == "||")
125            .map_or(FunctionVolatility::Stable, |operator| operator.volatility());
126    }
127    if is_intrinsic_scalar_syntax(binding) {
128        return FunctionVolatility::Immutable;
129    }
130    let lower = builtin_function_dispatch_name(name);
131    if builtin_is_volatile(&lower) {
132        return FunctionVolatility::Volatile;
133    }
134
135    // UQA retrieval/graph functions not listed above read the statement's
136    // catalog snapshot.  Session/catalog introspection functions have the same
137    // statement-stable contract.  All remaining built-ins are value-pure.
138    if crate::registry::is_registered(&lower)
139        || matches!(
140            lower.as_ref(),
141            "current_schema"
142                | "concat"
143                | "concat_ws"
144                | "format"
145                | "pg_typeof"
146                | "pg_collation_for"
147                | "typeof"
148                | "row_to_json"
149                | "to_json"
150                | "to_jsonb"
151                | "json_build_object"
152                | "jsonb_build_object"
153                | "json_build_array"
154                | "jsonb_build_array"
155                | "to_char"
156                | "to_date"
157                | "to_number"
158                | "now"
159                | "current_date"
160                | "current_time"
161                | "current_timestamp"
162                | "localtime"
163                | "localtimestamp"
164                | "statement_timestamp"
165                | "transaction_timestamp"
166                | "current_schemas"
167                | "current_setting"
168                | "pg_backend_pid"
169                | "pg_my_temp_schema"
170                | "pg_is_other_temp_schema"
171                | "pg_trigger_depth"
172                | "version"
173                | "pg_listening_channels"
174                | "to_regclass"
175                | "to_regcollation"
176                | "to_regnamespace"
177                | "to_regproc"
178                | "to_regprocedure"
179                | "to_regrole"
180                | "to_regtype"
181                | "current_database"
182                | "current_catalog"
183                | "current_user"
184                | "session_user"
185                | "list_analyzers"
186                | "fts_index_stats"
187                | "pg_get_expr"
188                | "pg_get_partkeydef"
189                | "pg_get_serial_sequence"
190                | "pg_sequence_parameters"
191                | "pg_get_triggerdef"
192                | "pg_get_ruledef"
193                | "pg_get_viewdef"
194                | "pg_get_indexdef"
195                | "pg_get_constraintdef"
196                | "pg_get_function_arguments"
197                | "pg_get_function_identity_arguments"
198                | "pg_get_function_result"
199                | "pg_get_functiondef"
200            | "pg_get_function_sqlbody"
201                | "format_type"
202                | "pg_describe_object"
203            | "obj_description"
204                | "pg_has_role"
205                | "pg_get_userbyid"
206                | "has_database_privilege"
207                | "has_table_privilege"
208                | "has_column_privilege"
209                | "has_schema_privilege"
210                | "has_sequence_privilege"
211                | "has_function_privilege"
212                | "has_type_privilege"
213                // The enum support functions that read a type's label list see labels added later in the transaction.
214                | "enum_first"
215                | "enum_last"
216                | "enum_range"
217        )
218        || builtin_overload_is_stable(&lower, binding, argument_count)
219    {
220        FunctionVolatility::Stable
221    } else {
222        FunctionVolatility::Immutable
223    }
224}
225
226fn builtin_overload_is_stable(
227    name: &str,
228    binding: Option<&FunctionBinding>,
229    argument_count: usize,
230) -> bool {
231    (matches!(name, "age" | "timezone") && argument_count == 1)
232        || (name == "to_timestamp" && argument_count == 2)
233        || (matches!(name, "quote_literal" | "quote_nullable")
234            && binding.is_none_or(|binding| binding.argument_types.as_slice() != ["text"]))
235        || (matches!(name, "date_part" | "extract" | "date_trunc")
236            && argument_count == 2
237            && binding.is_none_or(|binding| {
238                binding
239                    .argument_types
240                    .iter()
241                    .any(|ty| matches!(ty.as_str(), "timestamptz" | "timestamp with time zone"))
242            }))
243}
244
245// These built-ins mutate catalog/session state or derive a fresh value on every evaluation.
246fn builtin_is_volatile(name: &str) -> bool {
247    matches!(
248        name,
249        "random"
250            | "setseed"
251            | "set_config"
252            | "pg_sleep"
253            | "pg_sleep_for"
254            | "pg_sleep_until"
255            | "pg_notify"
256            | "pg_notification_queue_usage"
257            | "array_sample"
258            | "nextval"
259            | "currval"
260            | "lastval"
261            | "setval"
262            | "pg_get_sequence_data"
263            | "pg_sequence_last_value"
264            | "clock_timestamp"
265            | "timeofday"
266            | "gen_random_uuid"
267            | "uuidv4"
268            | "uuidv7"
269            | "create_analyzer"
270            | "drop_analyzer"
271            | "set_table_analyzer"
272            | "graph_create"
273            | "graph_drop"
274            | "create_graph"
275            | "drop_graph"
276            | "graph_exists"
277            | "create_vlabel"
278            | "create_elabel"
279            | "drop_label"
280            | "alter_graph"
281            | "cypher"
282            | "deep_learn"
283            // Retrieval calibration learns and persists parameters on a
284            // cache miss; it therefore is not a read-only scalar operation.
285            | "bayesian_match"
286            | "bayesian_match_with_prior"
287            | "fts_match"
288            | "multi_field_match"
289    )
290}
291
292fn sql_routine_volatility(
293    catalog: &dyn VolatilityCatalog,
294    identity: &str,
295    binding: Option<&FunctionBinding>,
296) -> Option<FunctionVolatility> {
297    let overloads = catalog.routine_volatilities(identity, binding)?;
298    if overloads.contains(&FunctionVolatility::Volatile) {
299        return Some(FunctionVolatility::Volatile);
300    }
301    if overloads.contains(&FunctionVolatility::Stable) {
302        return Some(FunctionVolatility::Stable);
303    }
304    Some(FunctionVolatility::Immutable)
305}
306
307pub fn expr_contains_volatile_function(catalog: &dyn VolatilityCatalog, expr: &ScalarExpr) -> bool {
308    expr_contains_volatile_function_with(catalog, expr, true)
309}
310
311/// Inspect calls in this scalar expression only. The caller must inspect query-valued children through their owning arena before moving or duplicating an expression.
312pub fn scalar_calls_are_volatile(catalog: &dyn VolatilityCatalog, expr: &ScalarExpr) -> bool {
313    expr_contains_volatile_function_with(catalog, expr, false)
314}
315
316/// Query-level walks inspect a block's subquery plans themselves, so their expression scan treats a subquery reference as opaque-but-inspected (`conservative_subqueries == false`) instead of assuming volatility.
317fn expr_contains_volatile_function_with(
318    catalog: &dyn VolatilityCatalog,
319    expr: &ScalarExpr,
320    conservative_subqueries: bool,
321) -> bool {
322    let mut volatile = false;
323    expr.visit(&mut |part| {
324        if volatile {
325            return;
326        }
327        match part {
328            ScalarExpr::Func {
329                name,
330                binding,
331                args,
332                ..
333            } => {
334                volatile =
335                    function_volatility_with_binding(catalog, name, binding.as_ref(), args.len())
336                        == FunctionVolatility::Volatile;
337            }
338            ScalarExpr::WindowCall { name, args, .. } => {
339                volatile =
340                    function_volatility(catalog, name, args.len()) == FunctionVolatility::Volatile;
341            }
342            // Query-valued children are inspected by the enclosing QueryPlan. At expression-only rewrite sites, retaining the conservative rule prevents an opaque child query from being duplicated or reordered.
343            ScalarExpr::ScalarSubquery(_)
344            | ScalarExpr::Exists { .. }
345            | ScalarExpr::InSubquery { .. } => volatile = conservative_subqueries,
346            _ => {}
347        }
348    });
349    volatile
350}
351
352/// Inspect a scalar plan with its query arena instead of treating every subquery as an opaque volatile call.
353pub fn expression_plan_contains_volatile_function(
354    catalog: &dyn VolatilityCatalog,
355    plan: &crate::plan::ExpressionPlan,
356) -> Result<bool, SQLError> {
357    scalar_with_subqueries_contains_volatile_function(catalog, &plan.scalar, &plan.subqueries)
358}
359
360/// Inspect a borrowed expression and only the query children it references in its owning arena. Other projections may share that arena without contributing effects to this expression.
361pub fn scalar_with_subqueries_contains_volatile_function(
362    catalog: &dyn VolatilityCatalog,
363    scalar: &ScalarExpr,
364    subqueries: &[QueryPlan],
365) -> Result<bool, SQLError> {
366    scalar_in_scope_contains_volatile_function(catalog, scalar, subqueries, &|_| false)
367}
368
369/// Inspect an expression's referenced subqueries under its enclosing CTE namespace. Those external producers are owned and evaluated by their defining query, not by this scalar expression.
370pub fn scalar_in_scope_contains_volatile_function(
371    catalog: &dyn VolatilityCatalog,
372    scalar: &ScalarExpr,
373    subqueries: &[QueryPlan],
374    visible_ctes: &dyn Fn(&str) -> bool,
375) -> Result<bool, SQLError> {
376    if scalar_calls_are_volatile(catalog, scalar) {
377        return Ok(true);
378    }
379    let mut result = Ok(false);
380    scalar.visit(&mut |node| {
381        if !matches!(result, Ok(false)) {
382            return;
383        }
384        let index = match node {
385            ScalarExpr::ScalarSubquery(index)
386            | ScalarExpr::Exists {
387                subquery: index, ..
388            }
389            | ScalarExpr::InSubquery {
390                subquery: index, ..
391            } => *index,
392            _ => return,
393        };
394        // An unresolved arena reference cannot authorize eliminating its expression; the ordinary validation path retains its diagnostic.
395        result = subqueries.get(index).map_or(Ok(true), |query| {
396            query_contains_volatile_function_inner(
397                catalog,
398                query,
399                &mut BTreeSet::new(),
400                visible_ctes,
401            )
402        });
403    });
404    result
405}
406
407/// The block's own subquery plans are inspected separately by the query-level walk, so subquery references here are not conservatively volatile.
408pub fn select_contains_volatile_function(
409    catalog: &dyn VolatilityCatalog,
410    block: &QueryBlockPlan,
411) -> bool {
412    block
413        .deferred_outer_rewrites
414        .iter()
415        .flat_map(super::view_rewrite::DeferredOuterRewrite::expressions)
416        .any(|expression| expr_contains_volatile_function_with(catalog, &expression.scalar, true))
417        || block.projections.iter().any(|projection| {
418            expr_contains_volatile_function_with(catalog, &projection.expr, false)
419        })
420        || block
421            .r#where
422            .as_ref()
423            .is_some_and(|expr| expr_contains_volatile_function_with(catalog, expr, false))
424        || block
425            .group_by
426            .iter()
427            .any(|expr| expr_contains_volatile_function_with(catalog, expr, false))
428        || block.grouping_sets.iter().any(|set| {
429            set.iter()
430                .any(|expr| expr_contains_volatile_function_with(catalog, expr, false))
431        })
432        || block
433            .having
434            .as_ref()
435            .is_some_and(|expr| expr_contains_volatile_function_with(catalog, expr, false))
436        || block
437            .order_by
438            .iter()
439            .any(|order| expr_contains_volatile_function_with(catalog, &order.expr, false))
440        || block
441            .limit
442            .as_ref()
443            .is_some_and(|expr| expr_contains_volatile_function_with(catalog, expr, false))
444        || block
445            .offset
446            .as_ref()
447            .is_some_and(|expr| expr_contains_volatile_function_with(catalog, expr, false))
448        || block
449            .distinct_on
450            .iter()
451            .any(|expr| expr_contains_volatile_function_with(catalog, expr, false))
452}
453
454/// Inspect a complete query, including transitive view dependencies.
455pub fn query_contains_volatile_function(
456    catalog: &dyn VolatilityCatalog,
457    plan: &QueryPlan,
458) -> Result<bool, SQLError> {
459    query_contains_volatile_function_inner(catalog, plan, &mut BTreeSet::new(), &|_| false)
460}
461
462fn query_contains_volatile_function_inner(
463    catalog: &dyn VolatilityCatalog,
464    plan: &QueryPlan,
465    visiting_views: &mut BTreeSet<String>,
466    outer: &dyn Fn(&str) -> bool,
467) -> Result<bool, SQLError> {
468    let scope = |name: &str| plan.ctes.iter().any(|cte| cte.name == name) || outer(name);
469    let recursive = plan.ctes.iter().any(|cte| cte.recursive);
470    for (index, cte) in plan.ctes.iter().enumerate() {
471        let visible = if recursive {
472            &plan.ctes[..]
473        } else {
474            &plan.ctes[..index]
475        };
476        let definition_scope =
477            |name: &str| visible.iter().any(|cte| cte.name == name) || outer(name);
478        if match &cte.body {
479            crate::plan::CtePlanBody::Query(query) => query_contains_volatile_function_inner(
480                catalog,
481                query,
482                visiting_views,
483                &definition_scope,
484            )?,
485            crate::plan::CtePlanBody::Command(_) => true,
486        } {
487            return Ok(true);
488        }
489    }
490    match &plan.root {
491        RelationalPlan::QueryBlock(block) => {
492            if select_contains_volatile_function(catalog, block) {
493                return Ok(true);
494            }
495            for subquery in &block.subqueries {
496                if query_contains_volatile_function_inner(
497                    catalog,
498                    subquery,
499                    visiting_views,
500                    &scope,
501                )? {
502                    return Ok(true);
503                }
504            }
505            if let Some(source) = &block.from {
506                source_contains_volatile_function(catalog, source, visiting_views, &scope)
507            } else {
508                Ok(false)
509            }
510        }
511        RelationalPlan::SetOp {
512            left,
513            right,
514            order_by,
515            limit,
516            offset,
517            subqueries,
518            ..
519        } => {
520            if query_contains_volatile_function_inner(catalog, left, visiting_views, &scope)?
521                || query_contains_volatile_function_inner(catalog, right, visiting_views, &scope)?
522                || order_by
523                    .iter()
524                    .any(|order| scalar_calls_are_volatile(catalog, &order.expr))
525                || limit
526                    .as_ref()
527                    .is_some_and(|expr| scalar_calls_are_volatile(catalog, expr))
528                || offset
529                    .as_ref()
530                    .is_some_and(|expr| scalar_calls_are_volatile(catalog, expr))
531            {
532                return Ok(true);
533            }
534            for subquery in subqueries {
535                if query_contains_volatile_function_inner(
536                    catalog,
537                    subquery,
538                    visiting_views,
539                    &scope,
540                )? {
541                    return Ok(true);
542                }
543            }
544            Ok(false)
545        }
546        RelationalPlan::Values { rows, subqueries } => {
547            if rows
548                .iter()
549                .flatten()
550                .any(|expr| scalar_calls_are_volatile(catalog, expr))
551            {
552                return Ok(true);
553            }
554            for subquery in subqueries {
555                if query_contains_volatile_function_inner(
556                    catalog,
557                    subquery,
558                    visiting_views,
559                    &scope,
560                )? {
561                    return Ok(true);
562                }
563            }
564            Ok(false)
565        }
566    }
567}
568
569fn source_contains_volatile_function(
570    catalog: &dyn VolatilityCatalog,
571    source: &SourcePlan,
572    visiting_views: &mut BTreeSet<String>,
573    scope: &dyn Fn(&str) -> bool,
574) -> Result<bool, SQLError> {
575    match source {
576        crate::plan::SourcePlan::RetainedRows(_) => Ok(false),
577        SourcePlan::Table { name, .. } => {
578            if super::cte_reference_name(name).is_some_and(|name| scope(&name)) {
579                // Each visible CTE definition was already inspected in its defining query. A same-named catalog view is a different relation.
580                return Ok(false);
581            }
582            let key = name.to_ascii_lowercase();
583            if !visiting_views.insert(key.clone()) {
584                return Ok(false);
585            }
586            let result = catalog
587                .with_view_query(name, &mut |view| {
588                    query_contains_volatile_function_inner(catalog, view, visiting_views, &|_| {
589                        false
590                    })
591                })
592                .map(|result| result.unwrap_or(false));
593            visiting_views.remove(&key);
594            result
595        }
596        SourcePlan::Join {
597            left, right, on, ..
598        } => {
599            if on
600                .as_ref()
601                .is_some_and(|expr| scalar_calls_are_volatile(catalog, expr))
602            {
603                return Ok(true);
604            }
605            Ok(
606                source_contains_volatile_function(catalog, left, visiting_views, scope)?
607                    || source_contains_volatile_function(catalog, right, visiting_views, scope)?,
608            )
609        }
610        SourcePlan::Values { rows, .. } => Ok(rows
611            .iter()
612            .flatten()
613            .any(|expr| scalar_calls_are_volatile(catalog, expr))),
614        SourcePlan::Function {
615            name,
616            binding,
617            args,
618            ..
619        } => Ok(
620            function_volatility_with_binding(catalog, name, binding.as_ref(), args.len())
621                == FunctionVolatility::Volatile
622                || args
623                    .iter()
624                    .any(|expr| scalar_calls_are_volatile(catalog, expr)),
625        ),
626        SourcePlan::FunctionGroup { functions, .. } => Ok(functions.iter().any(|function| {
627            function_volatility_with_binding(
628                catalog,
629                &function.name,
630                function.binding.as_ref(),
631                function.args.len(),
632            ) == FunctionVolatility::Volatile
633                || function
634                    .args
635                    .iter()
636                    .any(|expr| scalar_calls_are_volatile(catalog, expr))
637        })),
638        SourcePlan::Subquery { body, .. } => {
639            query_contains_volatile_function_inner(catalog, body, visiting_views, scope)
640        }
641    }
642}
643
644/// Whether scalar optimizer rewrites or `DPccp` join enumeration must preserve volatile call scheduling. Borrow the plan and its expression roots; inspecting constants must not copy their retained payloads or allocate command child lists.
645pub fn unified_plan_contains_volatile_function(
646    catalog: &dyn VolatilityCatalog,
647    plan: &UnifiedPlan,
648) -> bool {
649    let mut volatile = false;
650    plan.visit_scalar_expressions(&mut |expression| {
651        if !volatile {
652            volatile = scalar_calls_are_volatile(catalog, expression);
653        }
654    });
655    volatile
656}
657
658#[cfg(test)]
659mod tests;