1use std::collections::BTreeSet;
16
17use crate::ast::{FunctionBinding, FunctionVolatility};
18use crate::plan::{QueryBlockPlan, QueryPlan, RelationalPlan, SourcePlan, UnifiedPlan};
19use crate::SQLError;
20use crate::ScalarExpr;
21
22pub 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
33use super::builtin_function_dispatch_name;
34
35pub fn function_volatility(
43 catalog: &dyn VolatilityCatalog,
44 name: &str,
45 argument_count: usize,
46) -> FunctionVolatility {
47 function_volatility_with_binding(catalog, name, None, argument_count)
48}
49
50pub fn function_binding_is_volatile(
51 catalog: &dyn VolatilityCatalog,
52 name: &str,
53 binding: Option<&FunctionBinding>,
54 argument_count: usize,
55) -> bool {
56 function_volatility_with_binding(catalog, name, binding, argument_count)
57 == FunctionVolatility::Volatile
58}
59
60fn is_intrinsic_scalar_syntax(binding: Option<&FunctionBinding>) -> bool {
61 binding
62 .and_then(|binding| binding.dispatch)
63 .is_some_and(crate::ast::FunctionDispatch::is_concatenation)
64 || matches!(
65 binding.and_then(|binding| binding.dispatch),
66 Some(
67 crate::ast::FunctionDispatch::NumericOperator(_)
68 | crate::ast::FunctionDispatch::Pattern { .. }
69 | crate::ast::FunctionDispatch::PatternEscape { .. }
70 | crate::ast::FunctionDispatch::Collate { .. }
71 | crate::ast::FunctionDispatch::BooleanTest(_)
72 | crate::ast::FunctionDispatch::ArraySubscripts
73 | crate::ast::FunctionDispatch::ArraySlices
74 | crate::ast::FunctionDispatch::ArraySlicesWithBounds(_)
75 )
76 )
77}
78
79pub fn function_volatility_with_binding(
80 catalog: &dyn VolatilityCatalog,
81 name: &str,
82 binding: Option<&FunctionBinding>,
83 argument_count: usize,
84) -> FunctionVolatility {
85 let builtin = builtin_function_volatility(name, binding, argument_count);
86 if is_intrinsic_scalar_syntax(binding) || builtin == FunctionVolatility::Volatile {
87 return builtin;
88 }
89 let identity = name.to_ascii_lowercase();
90
91 if let Some(volatility) = catalog.host_function_volatility(&identity) {
95 return volatility;
96 }
97
98 if let Some(volatility) = sql_routine_volatility(catalog, &identity, binding) {
99 return volatility;
100 }
101 builtin
102}
103
104pub fn builtin_function_volatility(
106 name: &str,
107 binding: Option<&FunctionBinding>,
108 argument_count: usize,
109) -> FunctionVolatility {
110 if let Some(crate::ast::FunctionDispatch::Concat { oid }) =
111 binding.and_then(|binding| binding.dispatch)
112 {
113 return oid
114 .and_then(|oid| crate::type_resolution::binary_operator_by_oid(i64::from(oid)))
115 .filter(|operator| operator.name == "||")
116 .map_or(FunctionVolatility::Stable, |operator| operator.volatility());
117 }
118 if is_intrinsic_scalar_syntax(binding) {
119 return FunctionVolatility::Immutable;
120 }
121 let lower = builtin_function_dispatch_name(&name.to_ascii_lowercase());
122 if builtin_is_volatile(&lower) {
123 return FunctionVolatility::Volatile;
124 }
125
126 if crate::registry::is_registered(&lower)
130 || matches!(
131 lower.as_str(),
132 "current_schema"
133 | "concat"
134 | "concat_ws"
135 | "format"
136 | "pg_typeof"
137 | "pg_collation_for"
138 | "typeof"
139 | "row_to_json"
140 | "to_json"
141 | "to_jsonb"
142 | "json_build_object"
143 | "jsonb_build_object"
144 | "json_build_array"
145 | "jsonb_build_array"
146 | "to_char"
147 | "to_date"
148 | "to_number"
149 | "now"
150 | "current_date"
151 | "current_time"
152 | "current_timestamp"
153 | "localtime"
154 | "localtimestamp"
155 | "statement_timestamp"
156 | "transaction_timestamp"
157 | "current_schemas"
158 | "current_setting"
159 | "pg_backend_pid"
160 | "pg_my_temp_schema"
161 | "pg_is_other_temp_schema"
162 | "pg_trigger_depth"
163 | "version"
164 | "pg_listening_channels"
165 | "to_regclass"
166 | "to_regcollation"
167 | "to_regnamespace"
168 | "to_regproc"
169 | "to_regprocedure"
170 | "to_regrole"
171 | "to_regtype"
172 | "current_database"
173 | "current_catalog"
174 | "current_user"
175 | "session_user"
176 | "list_analyzers"
177 | "fts_index_stats"
178 | "pg_get_expr"
179 | "pg_get_partkeydef"
180 | "pg_get_serial_sequence"
181 | "pg_sequence_parameters"
182 | "pg_get_triggerdef"
183 | "pg_get_ruledef"
184 | "pg_get_viewdef"
185 | "pg_get_indexdef"
186 | "pg_get_constraintdef"
187 | "pg_get_function_arguments"
188 | "pg_get_function_identity_arguments"
189 | "pg_get_function_result"
190 | "pg_get_functiondef"
191 | "pg_get_function_sqlbody"
192 | "format_type"
193 | "pg_describe_object"
194 | "obj_description"
195 | "pg_has_role"
196 | "pg_get_userbyid"
197 | "has_database_privilege"
198 | "has_table_privilege"
199 | "has_column_privilege"
200 | "has_schema_privilege"
201 | "has_sequence_privilege"
202 | "has_function_privilege"
203 | "has_type_privilege"
204 | "enum_first"
206 | "enum_last"
207 | "enum_range"
208 )
209 || builtin_overload_is_stable(&lower, binding, argument_count)
210 {
211 FunctionVolatility::Stable
212 } else {
213 FunctionVolatility::Immutable
214 }
215}
216
217fn builtin_overload_is_stable(
218 name: &str,
219 binding: Option<&FunctionBinding>,
220 argument_count: usize,
221) -> bool {
222 (matches!(name, "age" | "timezone") && argument_count == 1)
223 || (name == "to_timestamp" && argument_count == 2)
224 || (matches!(name, "quote_literal" | "quote_nullable")
225 && binding.is_none_or(|binding| binding.argument_types.as_slice() != ["text"]))
226 || (matches!(name, "date_part" | "extract" | "date_trunc")
227 && argument_count == 2
228 && binding.is_none_or(|binding| {
229 binding
230 .argument_types
231 .iter()
232 .any(|ty| matches!(ty.as_str(), "timestamptz" | "timestamp with time zone"))
233 }))
234}
235
236fn builtin_is_volatile(name: &str) -> bool {
238 matches!(
239 name,
240 "random"
241 | "setseed"
242 | "set_config"
243 | "pg_sleep"
244 | "pg_sleep_for"
245 | "pg_sleep_until"
246 | "pg_notify"
247 | "pg_notification_queue_usage"
248 | "array_sample"
249 | "nextval"
250 | "currval"
251 | "lastval"
252 | "setval"
253 | "pg_get_sequence_data"
254 | "pg_sequence_last_value"
255 | "clock_timestamp"
256 | "timeofday"
257 | "gen_random_uuid"
258 | "uuidv4"
259 | "uuidv7"
260 | "create_analyzer"
261 | "drop_analyzer"
262 | "set_table_analyzer"
263 | "graph_create"
264 | "graph_drop"
265 | "create_graph"
266 | "drop_graph"
267 | "graph_exists"
268 | "create_vlabel"
269 | "create_elabel"
270 | "drop_label"
271 | "alter_graph"
272 | "cypher"
273 | "deep_learn"
274 | "bayesian_match"
277 | "bayesian_match_with_prior"
278 | "fts_match"
279 | "multi_field_match"
280 )
281}
282
283fn sql_routine_volatility(
284 catalog: &dyn VolatilityCatalog,
285 identity: &str,
286 binding: Option<&FunctionBinding>,
287) -> Option<FunctionVolatility> {
288 let overloads = catalog.routine_volatilities(identity, binding)?;
289 if overloads.contains(&FunctionVolatility::Volatile) {
290 return Some(FunctionVolatility::Volatile);
291 }
292 if overloads.contains(&FunctionVolatility::Stable) {
293 return Some(FunctionVolatility::Stable);
294 }
295 Some(FunctionVolatility::Immutable)
296}
297
298pub fn expr_contains_volatile_function(catalog: &dyn VolatilityCatalog, expr: &ScalarExpr) -> bool {
299 expr_contains_volatile_function_with(catalog, expr, true)
300}
301
302fn expr_contains_volatile_function_with(
304 catalog: &dyn VolatilityCatalog,
305 expr: &ScalarExpr,
306 conservative_subqueries: bool,
307) -> bool {
308 let mut volatile = false;
309 expr.visit(&mut |part| {
310 if volatile {
311 return;
312 }
313 match part {
314 ScalarExpr::Func {
315 name,
316 binding,
317 args,
318 ..
319 } => {
320 volatile =
321 function_volatility_with_binding(catalog, name, binding.as_ref(), args.len())
322 == FunctionVolatility::Volatile;
323 }
324 ScalarExpr::WindowCall { name, args, .. } => {
325 volatile =
326 function_volatility(catalog, name, args.len()) == FunctionVolatility::Volatile;
327 }
328 ScalarExpr::ScalarSubquery(_)
330 | ScalarExpr::Exists { .. }
331 | ScalarExpr::InSubquery { .. } => volatile = conservative_subqueries,
332 _ => {}
333 }
334 });
335 volatile
336}
337
338pub fn expression_plan_contains_volatile_function(
340 catalog: &dyn VolatilityCatalog,
341 plan: &crate::plan::ExpressionPlan,
342) -> Result<bool, SQLError> {
343 if expr_contains_volatile_function_with(catalog, &plan.scalar, false) {
344 return Ok(true);
345 }
346 for query in &plan.subqueries {
347 if query_contains_volatile_function(catalog, query)? {
348 return Ok(true);
349 }
350 }
351 Ok(false)
352}
353
354pub fn select_contains_volatile_function(
356 catalog: &dyn VolatilityCatalog,
357 block: &QueryBlockPlan,
358) -> bool {
359 block
360 .projections
361 .iter()
362 .any(|projection| expr_contains_volatile_function_with(catalog, &projection.expr, false))
363 || block
364 .r#where
365 .as_ref()
366 .is_some_and(|expr| expr_contains_volatile_function_with(catalog, expr, false))
367 || block
368 .group_by
369 .iter()
370 .any(|expr| expr_contains_volatile_function_with(catalog, expr, false))
371 || block.grouping_sets.iter().any(|set| {
372 set.iter()
373 .any(|expr| expr_contains_volatile_function_with(catalog, expr, false))
374 })
375 || block
376 .having
377 .as_ref()
378 .is_some_and(|expr| expr_contains_volatile_function_with(catalog, expr, false))
379 || block
380 .order_by
381 .iter()
382 .any(|order| expr_contains_volatile_function_with(catalog, &order.expr, false))
383 || block
384 .limit
385 .as_ref()
386 .is_some_and(|expr| expr_contains_volatile_function_with(catalog, expr, false))
387 || block
388 .offset
389 .as_ref()
390 .is_some_and(|expr| expr_contains_volatile_function_with(catalog, expr, false))
391 || block
392 .distinct_on
393 .iter()
394 .any(|expr| expr_contains_volatile_function_with(catalog, expr, false))
395}
396
397pub fn query_contains_volatile_function(
399 catalog: &dyn VolatilityCatalog,
400 plan: &QueryPlan,
401) -> Result<bool, SQLError> {
402 query_contains_volatile_function_inner(catalog, plan, &mut BTreeSet::new())
403}
404
405fn query_contains_volatile_function_inner(
406 catalog: &dyn VolatilityCatalog,
407 plan: &QueryPlan,
408 visiting_views: &mut BTreeSet<String>,
409) -> Result<bool, SQLError> {
410 for cte in &plan.ctes {
411 if match &cte.body {
412 crate::plan::CtePlanBody::Query(query) => {
413 query_contains_volatile_function_inner(catalog, query, visiting_views)?
414 }
415 crate::plan::CtePlanBody::Command(_) => true,
416 } {
417 return Ok(true);
418 }
419 }
420 match &plan.root {
421 RelationalPlan::QueryBlock(block) => {
422 if select_contains_volatile_function(catalog, block) {
423 return Ok(true);
424 }
425 for subquery in &block.subqueries {
426 if query_contains_volatile_function_inner(catalog, subquery, visiting_views)? {
427 return Ok(true);
428 }
429 }
430 if let Some(source) = &block.from {
431 source_contains_volatile_function(catalog, source, visiting_views)
432 } else {
433 Ok(false)
434 }
435 }
436 RelationalPlan::SetOp {
437 left,
438 right,
439 order_by,
440 limit,
441 offset,
442 subqueries,
443 ..
444 } => {
445 if query_contains_volatile_function_inner(catalog, left, visiting_views)?
446 || query_contains_volatile_function_inner(catalog, right, visiting_views)?
447 || order_by
448 .iter()
449 .any(|order| expr_contains_volatile_function(catalog, &order.expr))
450 || limit
451 .as_ref()
452 .is_some_and(|expr| expr_contains_volatile_function(catalog, expr))
453 || offset
454 .as_ref()
455 .is_some_and(|expr| expr_contains_volatile_function(catalog, expr))
456 {
457 return Ok(true);
458 }
459 for subquery in subqueries {
460 if query_contains_volatile_function_inner(catalog, subquery, visiting_views)? {
461 return Ok(true);
462 }
463 }
464 Ok(false)
465 }
466 RelationalPlan::Values { rows, subqueries } => {
467 if rows
468 .iter()
469 .flatten()
470 .any(|expr| expr_contains_volatile_function(catalog, expr))
471 {
472 return Ok(true);
473 }
474 for subquery in subqueries {
475 if query_contains_volatile_function_inner(catalog, subquery, visiting_views)? {
476 return Ok(true);
477 }
478 }
479 Ok(false)
480 }
481 }
482}
483
484fn source_contains_volatile_function(
485 catalog: &dyn VolatilityCatalog,
486 source: &SourcePlan,
487 visiting_views: &mut BTreeSet<String>,
488) -> Result<bool, SQLError> {
489 match source {
490 SourcePlan::Table { name, .. } => {
491 let key = name.to_ascii_lowercase();
492 if !visiting_views.insert(key.clone()) {
493 return Ok(false);
494 }
495 let result = match catalog.view_query(name)? {
496 Some(view) => {
497 query_contains_volatile_function_inner(catalog, &view, visiting_views)
498 }
499 None => Ok(false),
500 };
501 visiting_views.remove(&key);
502 result
503 }
504 SourcePlan::Join {
505 left, right, on, ..
506 } => {
507 if on
508 .as_ref()
509 .is_some_and(|expr| expr_contains_volatile_function(catalog, expr))
510 {
511 return Ok(true);
512 }
513 Ok(
514 source_contains_volatile_function(catalog, left, visiting_views)?
515 || source_contains_volatile_function(catalog, right, visiting_views)?,
516 )
517 }
518 SourcePlan::Values { rows, .. } => Ok(rows
519 .iter()
520 .flatten()
521 .any(|expr| expr_contains_volatile_function(catalog, expr))),
522 SourcePlan::Function {
523 name,
524 binding,
525 args,
526 ..
527 } => Ok(
528 function_volatility_with_binding(catalog, name, binding.as_ref(), args.len())
529 == FunctionVolatility::Volatile
530 || args
531 .iter()
532 .any(|expr| expr_contains_volatile_function(catalog, expr)),
533 ),
534 SourcePlan::FunctionGroup { functions, .. } => Ok(functions.iter().any(|function| {
535 function_volatility_with_binding(
536 catalog,
537 &function.name,
538 function.binding.as_ref(),
539 function.args.len(),
540 ) == FunctionVolatility::Volatile
541 || function
542 .args
543 .iter()
544 .any(|expr| expr_contains_volatile_function(catalog, expr))
545 })),
546 SourcePlan::Subquery { body, .. } => {
547 query_contains_volatile_function_inner(catalog, body, visiting_views)
548 }
549 }
550}
551
552pub fn unified_plan_contains_volatile_function(
556 catalog: &dyn VolatilityCatalog,
557 plan: &UnifiedPlan,
558) -> bool {
559 let mut inspected = plan.clone();
560 let mut volatile = false;
561 inspected.rewrite_scalar_expressions(&mut |expr| {
562 if volatile {
563 return;
564 }
565 match expr {
566 ScalarExpr::Func {
567 name,
568 binding,
569 args,
570 ..
571 } => {
572 volatile =
573 function_volatility_with_binding(catalog, name, binding.as_ref(), args.len())
574 == FunctionVolatility::Volatile;
575 }
576 ScalarExpr::WindowCall { name, args, .. } => {
577 volatile =
578 function_volatility(catalog, name, args.len()) == FunctionVolatility::Volatile;
579 }
580 _ => {}
581 }
582 });
583 volatile
584}
585
586#[cfg(test)]
587mod tests {
588 use super::{
589 expr_contains_volatile_function, FunctionBinding, FunctionVolatility, QueryPlan, SQLError,
590 ScalarExpr, VolatilityCatalog,
591 };
592
593 struct EmptyCatalog;
594 impl VolatilityCatalog for EmptyCatalog {
595 fn host_function_volatility(&self, _: &str) -> Option<FunctionVolatility> {
596 None
597 }
598 fn routine_volatilities(
599 &self,
600 _: &str,
601 _: Option<&FunctionBinding>,
602 ) -> Option<Vec<FunctionVolatility>> {
603 None
604 }
605 fn view_query(&self, _: &str) -> Result<Option<QueryPlan>, SQLError> {
606 Ok(None)
607 }
608 }
609 use crate::ast::{FrameExclusion, FrameMode};
610 use crate::{ScalarFrameBound, ScalarWindowFrame, ScalarWindowSpec};
611
612 #[test]
613 fn collation_volatility_follows_only_its_operand() {
614 struct Shadowed;
615 impl VolatilityCatalog for Shadowed {
616 fn host_function_volatility(&self, _: &str) -> Option<FunctionVolatility> {
617 Some(FunctionVolatility::Volatile)
618 }
619 fn routine_volatilities(
620 &self,
621 _: &str,
622 _: Option<&FunctionBinding>,
623 ) -> Option<Vec<FunctionVolatility>> {
624 unreachable!("host function wins ordinary calls")
625 }
626 fn view_query(&self, _: &str) -> Result<Option<QueryPlan>, SQLError> {
627 unreachable!("no view")
628 }
629 }
630 for (input, expected) in [
631 ("'x' COLLATE \"C\"", false),
632 ("nextval('effect')::text COLLATE \"C\"", true),
633 ("\"COLLATE\"('x')", true),
634 ("true IS TRUE", false),
635 ("(nextval('effect') > 0) IS NOT FALSE", true),
636 ("\"IS TRUE\"(true)", true),
637 ("(ARRAY[1,2])[1]", false),
638 ("(ARRAY[1,2])[:1]", false),
639 ("(ARRAY[1,2])[nextval('effect')]", true),
640 ("(ARRAY[1,2])[:nextval('effect')]", true),
641 ("slice(ARRAY[1,2], 1, 2)", true),
642 ("subscript(ARRAY[1,2], 1)", true),
643 ] {
644 let crate::Statement::Select(mut query) = crate::compile(&format!("SELECT {input}"))
645 .unwrap()
646 .remove(0)
647 else {
648 panic!("SELECT")
649 };
650 let scalar =
651 crate::plan::ExpressionPlan::lower(query.projections.remove(0).expr).scalar;
652 assert_eq!(
653 expr_contains_volatile_function(&Shadowed, &scalar),
654 expected,
655 "{input}"
656 );
657 }
658 let binding =
659 FunctionBinding::dispatched(crate::ast::FunctionDispatch::Collate { oid: Some(950) });
660 assert_eq!(
661 super::builtin_function_volatility("nextval", Some(&binding), 2),
662 FunctionVolatility::Immutable
663 );
664 }
665
666 #[test]
667 fn builtin_constants_preserve_postgresql_statement_and_session_inputs() {
668 for name in [
670 "concat",
671 "concat_ws",
672 "format",
673 "pg_typeof",
674 "row_to_json",
675 "to_json",
676 "to_jsonb",
677 "json_build_object",
678 "jsonb_build_array",
679 "to_char",
680 "to_date",
681 "to_number",
682 "has_table_privilege",
683 "has_column_privilege",
684 ] {
685 assert_eq!(
686 super::builtin_function_volatility(name, None, 2),
687 FunctionVolatility::Stable,
688 "{name}"
689 );
690 }
691 for name in ["repeat", "upper"] {
692 assert_eq!(
693 super::builtin_function_volatility(name, None, 2),
694 FunctionVolatility::Immutable,
695 "{name}"
696 );
697 }
698 assert_eq!(
699 super::builtin_function_volatility("nextval", None, 1),
700 FunctionVolatility::Volatile
701 );
702 }
703
704 #[test]
705 fn sequence_introspection_volatility_matches_postgresql() {
706 for (name, expected) in [
707 ("pg_get_sequence_data", FunctionVolatility::Volatile),
708 ("pg_sequence_last_value", FunctionVolatility::Volatile),
709 ("pg_sequence_parameters", FunctionVolatility::Stable),
710 ] {
711 for qualified in [name.to_string(), format!("pg_catalog.{name}")] {
712 assert_eq!(
713 super::function_volatility(&EmptyCatalog, &qualified, 1),
714 expected,
715 "{qualified}"
716 );
717 }
718 }
719 }
720
721 #[test]
722 fn timestamp_zone_volatility_preserves_session_dependence() {
723 assert_eq!(
724 super::builtin_function_volatility("timezone", None, 1),
725 FunctionVolatility::Stable
726 );
727 assert_eq!(
728 super::builtin_function_volatility("timezone", None, 2),
729 FunctionVolatility::Immutable
730 );
731 }
732
733 #[test]
734 fn volatility_inspection_includes_window_frame_expressions() {
735 let expression = ScalarExpr::WindowCall {
736 name: "sum".into(),
737 args: vec![ScalarExpr::Column("amount".into())],
738 spec: ScalarWindowSpec {
739 definition: None,
740 partition_by: Vec::new(),
741 order_by: Vec::new(),
742 frame: Some(ScalarWindowFrame {
743 mode: FrameMode::Rows,
744 start: ScalarFrameBound::Preceding(Box::new(ScalarExpr::Func {
745 order_syntax: crate::ast::FunctionOrderSyntax::Ordinary,
746 name: "random".into(),
747 binding: None,
748 args: Vec::new(),
749 distinct: false,
750 order_by: Vec::new(),
751 filter: None,
752 })),
753 end: ScalarFrameBound::CurrentRow,
754 between: true,
755 exclusion: FrameExclusion::NoOthers,
756 }),
757 },
758 filter: None,
759 modifiers: crate::ast::WindowCallModifiers::default(),
760 };
761 assert!(expr_contains_volatile_function(&EmptyCatalog, &expression));
762 }
763 #[test]
764 fn scalar_plan_volatility_inspects_its_subquery_body() {
765 for (sql, expected) in [
766 ("SELECT (SELECT 1 / 0)", false),
767 ("SELECT (SELECT nextval('ticks'))", true),
768 ] {
769 let crate::plan::UnifiedPlan::Query(query) =
770 crate::plan::UnifiedPlan::lower(crate::compile(sql).unwrap().remove(0))
771 else {
772 panic!("query");
773 };
774 let crate::plan::RelationalPlan::QueryBlock(mut block) = query.root else {
775 panic!("query block");
776 };
777 let plan = crate::plan::ExpressionPlan {
778 scalar: block.projections.remove(0).expr,
779 subqueries: block.subqueries,
780 };
781 assert_eq!(
782 super::expression_plan_contains_volatile_function(&EmptyCatalog, &plan).unwrap(),
783 expected,
784 "{sql}"
785 );
786 }
787 }
788}