Skip to main content

uqa_sql/semantics/sets/
validation.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Set-returning expression detection and SQL context validation.
8
9mod production;
10pub use production::{
11    expression_may_return_set_with_control, expressions_may_return_set_with_control,
12    function_may_return_set_with_control, SetFunctionAnalysis,
13};
14
15use crate::ast::FunctionBinding;
16use crate::plan::{ProjectionPlan, QueryBlockPlan, SourcePlan};
17use crate::{FunctionTypeResolver, RowSchema, ScalarExpr, ScalarFrameBound};
18use crate::{SQLError, SQLParam};
19
20use super::placement::{
21    conditional_set_error, row_comparison_set_error, set_argument_error, top_level_from,
22    SetPlacement,
23};
24use super::placement_scope::placing_set_calls;
25use super::{PhysicalProjection, SetFunctionCatalog};
26use crate::binding::context::BindingContext;
27use crate::semantics::aggregates::is_aggregate;
28
29pub fn builtin_returns_set(name: &str) -> bool {
30    matches!(
31        name,
32        "generate_series"
33            | "pg_listening_channels"
34            | "unnest"
35            | "regexp_matches"
36            | "regexp_split_to_table"
37            | "string_to_table"
38            | "json_array_elements"
39            | "jsonb_array_elements"
40            | "json_array_elements_text"
41            | "jsonb_array_elements_text"
42            | "json_each"
43            | "jsonb_each"
44            | "json_each_text"
45            | "jsonb_each_text"
46            | "json_object_keys"
47            | "jsonb_object_keys"
48            | "aclexplode"
49    )
50}
51
52pub fn function_may_return_set(
53    engine: &dyn crate::routines::RoutineResolution,
54    resolver: &dyn FunctionTypeResolver,
55    name: &str,
56    binding: Option<&FunctionBinding>,
57    args: &[ScalarExpr],
58    schema: &RowSchema,
59    params: &[SQLParam],
60) -> Result<bool, SQLError> {
61    function_may_return_set_with_control(
62        SetFunctionAnalysis {
63            routines: engine,
64            types: resolver,
65        },
66        name,
67        binding,
68        args,
69        schema,
70        params,
71        &uqa_core::memory::ProductionControl::uncontrolled(),
72    )
73}
74
75pub fn resolve_set_function_binding(
76    engine: &dyn SetFunctionCatalog,
77    resolver: &dyn FunctionTypeResolver,
78    name: &str,
79    binding: Option<&FunctionBinding>,
80    args: &[ScalarExpr],
81    schema: &RowSchema,
82    params: &[SQLParam],
83) -> Result<Option<FunctionBinding>, SQLError> {
84    let (argument_names, argument_types, explicit_variadic) =
85        crate::function_call_argument_signature(args, schema, params, Some(resolver))?;
86    let unbound_without_routines = binding.is_none()
87        && engine
88            .lookup_visible_sql_functions_for_analysis(name)?
89            .is_none();
90    if let Some(resolved) = crate::resolve_fixed_builtin_call(
91        name,
92        binding,
93        &argument_names,
94        &argument_types,
95        explicit_variadic,
96        Some(resolver),
97    )? {
98        // A call keeps its recorded built-in binding. A stored call analyzed before its built-in had catalog signatures takes the selected declaration, which EXECUTE authorization identifies.
99        return Ok((binding.is_none() || !resolved.selected.binding.builtin)
100            .then_some(resolved.selected.binding));
101    }
102    if unbound_without_routines {
103        return Ok(None);
104    }
105    let Some(function) = engine.resolve_static_sql_function_match(
106        name,
107        binding,
108        &argument_names,
109        &argument_types,
110        explicit_variadic,
111    )?
112    else {
113        return Ok(binding.cloned());
114    };
115    Ok(Some(function.binding()))
116}
117
118pub fn projections_may_return_set(
119    engine: &dyn SetFunctionCatalog,
120    resolver: &dyn FunctionTypeResolver,
121    projections: &[PhysicalProjection],
122    schema: &RowSchema,
123    params: &[SQLParam],
124) -> Result<bool, SQLError> {
125    for (_, expression) in projections {
126        if expression_may_return_set(engine, resolver, expression, schema, params)? {
127            return Ok(true);
128        }
129    }
130    Ok(false)
131}
132
133pub fn expression_may_return_set(
134    engine: &dyn crate::routines::RoutineResolution,
135    resolver: &dyn FunctionTypeResolver,
136    expression: &ScalarExpr,
137    schema: &RowSchema,
138    params: &[SQLParam],
139) -> Result<bool, SQLError> {
140    expression_may_return_set_with_control(
141        engine,
142        resolver,
143        expression,
144        schema,
145        params,
146        &uqa_core::memory::ProductionControl::uncontrolled(),
147    )
148}
149
150fn expressions_may_return_set<'a>(
151    engine: &dyn crate::routines::RoutineResolution,
152    resolver: &dyn FunctionTypeResolver,
153    expressions: impl IntoIterator<Item = &'a ScalarExpr>,
154    schema: &RowSchema,
155    params: &[SQLParam],
156) -> Result<bool, SQLError> {
157    for expression in expressions {
158        if expression_may_return_set(engine, resolver, expression, schema, params)? {
159            return Ok(true);
160        }
161    }
162    Ok(false)
163}
164
165fn frame_bound_expression(bound: &ScalarFrameBound) -> Option<&ScalarExpr> {
166    match bound {
167        ScalarFrameBound::Preceding(expression) | ScalarFrameBound::Following(expression) => {
168            Some(expression)
169        }
170        ScalarFrameBound::UnboundedPreceding
171        | ScalarFrameBound::UnboundedFollowing
172        | ScalarFrameBound::CurrentRow => None,
173    }
174}
175
176/// Reject `expressions` when any contains a set-returning call outside its subqueries.
177fn reject_set_descendant<'a>(
178    engine: &dyn SetFunctionCatalog,
179    resolver: &dyn FunctionTypeResolver,
180    expressions: impl IntoIterator<Item = &'a ScalarExpr>,
181    schema: &RowSchema,
182    params: &[SQLParam],
183    error: impl FnOnce() -> SQLError,
184) -> Result<(), SQLError> {
185    if expressions_may_return_set(engine, resolver, expressions, schema, params)? {
186        return Err(error());
187    }
188    Ok(())
189}
190
191/// Reject a set-returning call in a clause whose expression kind admits none.
192fn reject_set_placement<'a>(
193    engine: &dyn SetFunctionCatalog,
194    resolver: &dyn FunctionTypeResolver,
195    expressions: impl IntoIterator<Item = &'a ScalarExpr>,
196    schema: &RowSchema,
197    params: &[SQLParam],
198    placement: SetPlacement,
199) -> Result<(), SQLError> {
200    if expressions_may_return_set(engine, resolver, expressions, schema, params)? {
201        placement.admit(true)?;
202    }
203    Ok(())
204}
205
206/// Check a target-list expression in `PostgreSQL`'s transformation order: each construct's inputs first, then the construct.
207#[expect(
208    clippy::too_many_lines,
209    reason = "one ordered dispatch covers every scalar expression"
210)]
211fn validate_set_context(
212    engine: &dyn SetFunctionCatalog,
213    resolver: &dyn FunctionTypeResolver,
214    expression: &ScalarExpr,
215    schema: &RowSchema,
216    params: &[SQLParam],
217) -> Result<(), SQLError> {
218    let validate = |expression| validate_set_context(engine, resolver, expression, schema, params);
219    let returns_set =
220        |expression| expression_may_return_set(engine, resolver, expression, schema, params);
221    match expression {
222        ScalarExpr::Func {
223            name,
224            args,
225            order_by,
226            filter,
227            ..
228        } => {
229            for argument in args {
230                validate(argument)?;
231            }
232            if let Some(filter) = filter {
233                reject_set_placement(
234                    engine,
235                    resolver,
236                    [filter.as_ref()],
237                    schema,
238                    params,
239                    SetPlacement::Filter,
240                )?;
241            }
242            for order in order_by {
243                validate(&order.expr)?;
244            }
245            if is_aggregate(engine, expression) {
246                reject_set_descendant(
247                    engine,
248                    resolver,
249                    args.iter().chain(order_by.iter().map(|order| &order.expr)),
250                    schema,
251                    params,
252                    || {
253                        conditional_set_error(
254                            "aggregate function calls cannot contain set-returning function calls",
255                        )
256                    },
257                )?;
258            } else if crate::semantics::builtin_function_dispatch_name(name) == "coalesce" {
259                reject_set_descendant(engine, resolver, args, schema, params, || {
260                    conditional_set_error("set-returning functions are not allowed in COALESCE")
261                })?;
262            }
263        }
264        ScalarExpr::WindowCall {
265            args, spec, filter, ..
266        } => {
267            for argument in args {
268                validate(argument)?;
269            }
270            if let Some(filter) = filter {
271                reject_set_placement(
272                    engine,
273                    resolver,
274                    [filter.as_ref()],
275                    schema,
276                    params,
277                    SetPlacement::Filter,
278                )?;
279            }
280            reject_set_descendant(engine, resolver, args, schema, params, || {
281                conditional_set_error(
282                    "window function calls cannot contain set-returning function calls",
283                )
284            })?;
285            for expression in spec
286                .partition_by
287                .iter()
288                .chain(spec.order_by.iter().map(|order| &order.expr))
289            {
290                validate(expression)?;
291            }
292            reject_set_placement(
293                engine,
294                resolver,
295                spec.frame
296                    .iter()
297                    .flat_map(|frame| [&frame.start, &frame.end])
298                    .filter_map(|bound| frame_bound_expression(bound)),
299                schema,
300                params,
301                SetPlacement::WindowFrame,
302            )?;
303        }
304        ScalarExpr::Case {
305            base,
306            when,
307            else_branch,
308        } => {
309            if let Some(base) = base {
310                validate(base)?;
311            }
312            for (condition, result) in when {
313                validate(condition)?;
314                // A searched WHEN is coerced to boolean; a simple CASE compares a placeholder for its base with the WHEN value.
315                if returns_set(condition)? {
316                    return Err(set_argument_error("CASE/WHEN"));
317                }
318                validate(result)?;
319            }
320            if let Some(branch) = else_branch {
321                validate(branch)?;
322            }
323            let descendants = base
324                .iter()
325                .map(AsRef::as_ref)
326                .chain(
327                    when.iter()
328                        .flat_map(|(condition, result)| [condition, result]),
329                )
330                .chain(else_branch.iter().map(AsRef::as_ref));
331            reject_set_descendant(engine, resolver, descendants, schema, params, || {
332                conditional_set_error("set-returning functions are not allowed in CASE")
333            })?;
334        }
335        ScalarExpr::Not(inner) => {
336            validate(inner)?;
337            if returns_set(inner)? {
338                return Err(set_argument_error("NOT"));
339            }
340        }
341        ScalarExpr::UnaryMinus(inner)
342        | ScalarExpr::IsNull { expr: inner, .. }
343        | ScalarExpr::Cast { expr: inner, .. }
344        | ScalarExpr::Collated { expr: inner, .. } => validate(inner)?,
345        ScalarExpr::And(items) | ScalarExpr::Or(items) => {
346            let construct = if matches!(expression, ScalarExpr::And(_)) {
347                "AND"
348            } else {
349                "OR"
350            };
351            for item in items {
352                validate(item)?;
353                if returns_set(item)? {
354                    return Err(set_argument_error(construct));
355                }
356            }
357        }
358        ScalarExpr::InList { expr, list, .. } => {
359            validate(expr)?;
360            for item in list {
361                validate(item)?;
362            }
363            reject_set_descendant(engine, resolver, list, schema, params, || {
364                set_argument_error("IN")
365            })?;
366        }
367        ScalarExpr::Between { expr, low, high } => {
368            for item in [expr, low, high] {
369                validate(item)?;
370            }
371            reject_set_descendant(
372                engine,
373                resolver,
374                [expr.as_ref(), low.as_ref(), high.as_ref()],
375                schema,
376                params,
377                || set_argument_error("AND"),
378            )?;
379        }
380        ScalarExpr::Array(items)
381        | ScalarExpr::Row(items)
382        | ScalarExpr::CompositeRow { items, .. } => {
383            for item in items {
384                validate(item)?;
385            }
386        }
387        ScalarExpr::Binary { lhs, rhs, .. } => {
388            validate(lhs)?;
389            validate(rhs)?;
390            if matches!(
391                (lhs.as_ref(), rhs.as_ref()),
392                (ScalarExpr::Row(_), ScalarExpr::Row(_))
393            ) {
394                reject_set_descendant(
395                    engine,
396                    resolver,
397                    [lhs.as_ref(), rhs.as_ref()],
398                    schema,
399                    params,
400                    row_comparison_set_error,
401                )?;
402            }
403        }
404        ScalarExpr::InSubquery { expr, .. } => {
405            validate(expr)?;
406            reject_set_descendant(
407                engine,
408                resolver,
409                [expr.as_ref()],
410                schema,
411                params,
412                row_comparison_set_error,
413            )?;
414        }
415        ScalarExpr::Default
416        | ScalarExpr::Star
417        | ScalarExpr::QualifiedStar(_)
418        | ScalarExpr::Column(_)
419        | ScalarExpr::Position(_)
420        | ScalarExpr::InternalColumn(_)
421        | ScalarExpr::QualifiedColumn { .. }
422        | ScalarExpr::Literal(_)
423        | ScalarExpr::TypedLiteral { .. }
424        | ScalarExpr::RetainedLiteral(_)
425        | ScalarExpr::Param(_)
426        | ScalarExpr::ScalarSubquery(_)
427        | ScalarExpr::Exists { .. } => {}
428    }
429    Ok(())
430}
431
432pub fn validate_projection_set_contexts(
433    engine: &dyn SetFunctionCatalog,
434    resolver: &dyn FunctionTypeResolver,
435    projections: &[ProjectionPlan],
436    schema: &RowSchema,
437    params: &[SQLParam],
438) -> Result<(), SQLError> {
439    if !placing_set_calls() {
440        return Ok(());
441    }
442    for projection in projections {
443        validate_set_context(engine, resolver, &projection.expr, schema, params)?;
444    }
445    Ok(())
446}
447
448/// Check a query block's clauses in `PostgreSQL`'s transformation order: the target list, WHERE, HAVING, ORDER BY, GROUP BY, DISTINCT ON, OFFSET, LIMIT and FROM, then the row-locking clause. A block of a stored definition is not checked again.
449pub fn validate_query_set_contexts(
450    engine: &dyn SetFunctionCatalog,
451    resolver: &dyn FunctionTypeResolver,
452    statement: &QueryBlockPlan,
453    schema: &RowSchema,
454    params: &[SQLParam],
455) -> Result<(), SQLError> {
456    if !placing_set_calls() {
457        return Ok(());
458    }
459    validate_projection_set_contexts(engine, resolver, &statement.projections, schema, params)?;
460    for (clause, placement) in [
461        (statement.r#where.as_ref(), SetPlacement::Where),
462        (statement.having.as_ref(), SetPlacement::Having),
463    ] {
464        reject_set_placement(engine, resolver, clause, schema, params, placement)?;
465    }
466    for expression in statement
467        .order_by
468        .iter()
469        .map(|order| &order.expr)
470        .chain(statement.group_by.iter())
471        .chain(statement.grouping_sets.iter().flatten())
472        .chain(statement.distinct_on.iter())
473    {
474        validate_set_context(engine, resolver, expression, schema, params)?;
475    }
476    for (clause, placement) in [
477        (statement.offset.as_ref(), SetPlacement::Offset),
478        (statement.limit.as_ref(), SetPlacement::Limit),
479    ] {
480        reject_set_placement(engine, resolver, clause, schema, params, placement)?;
481    }
482    if let Some(source) = statement.from.as_ref() {
483        validate_source_set_contexts(engine, resolver, source, schema, params)?;
484    }
485    if let Some(locking) = statement.locking.first() {
486        for projection in &statement.projections {
487            if expression_may_return_set(engine, resolver, &projection.expr, schema, params)? {
488                return Err(SQLError::Unsupported(format!(
489                    "{} is not allowed with set-returning functions in the target list",
490                    locking.strength.sql_name()
491                )));
492            }
493        }
494    }
495    Ok(())
496}
497
498fn validate_source_set_contexts(
499    engine: &dyn SetFunctionCatalog,
500    resolver: &dyn FunctionTypeResolver,
501    source: &SourcePlan,
502    schema: &RowSchema,
503    params: &[SQLParam],
504) -> Result<(), SQLError> {
505    match source {
506        crate::plan::SourcePlan::RetainedRows(_) => {}
507        SourcePlan::Join {
508            left, right, on, ..
509        } => {
510            validate_source_set_contexts(engine, resolver, left, schema, params)?;
511            validate_source_set_contexts(engine, resolver, right, schema, params)?;
512            if let Some(condition) = on {
513                reject_set_placement(
514                    engine,
515                    resolver,
516                    [condition],
517                    schema,
518                    params,
519                    SetPlacement::JoinCondition,
520                )?;
521            }
522        }
523        SourcePlan::Values { rows, .. } => {
524            validate_values_set_contexts(engine, resolver, rows, schema, params)?;
525        }
526        SourcePlan::Function { args, .. } => {
527            reject_set_descendant(engine, resolver, args, schema, params, top_level_from)?;
528        }
529        SourcePlan::FunctionGroup { functions, .. } => {
530            for function in functions {
531                reject_set_descendant(
532                    engine,
533                    resolver,
534                    &function.args,
535                    schema,
536                    params,
537                    top_level_from,
538                )?;
539            }
540        }
541        SourcePlan::Subquery { .. } | SourcePlan::Table { .. } => {}
542    }
543    Ok(())
544}
545
546pub fn validate_source_set_contexts_before_build(
547    engine: &dyn SetFunctionCatalog,
548    resolver: &dyn FunctionTypeResolver,
549    source: &SourcePlan,
550    params: &[SQLParam],
551    ctes: &BindingContext<'_>,
552    outer: Option<&RowSchema>,
553) -> Result<(), SQLError> {
554    if !placing_set_calls() {
555        return Ok(());
556    }
557    validate_source_set_contexts_in_scope(
558        engine,
559        resolver,
560        source,
561        params,
562        ctes,
563        crate::binding::SourceScope::new(outer),
564    )
565}
566
567fn validate_source_set_contexts_in_scope(
568    engine: &dyn SetFunctionCatalog,
569    resolver: &dyn FunctionTypeResolver,
570    source: &SourcePlan,
571    params: &[SQLParam],
572    ctes: &BindingContext<'_>,
573    scope: crate::binding::SourceScope<'_>,
574) -> Result<(), SQLError> {
575    let input = (!matches!(source, SourcePlan::Join { .. }))
576        .then(|| scope.input(source))
577        .flatten();
578    let outer = input.as_ref();
579    match source {
580        crate::plan::SourcePlan::RetainedRows(_) => Ok(()),
581        SourcePlan::Join {
582            left,
583            right,
584            lateral,
585            kind,
586            ..
587        } => {
588            validate_source_set_contexts_in_scope(
589                engine,
590                resolver,
591                left,
592                params,
593                ctes,
594                scope.with_names(),
595            )?;
596            let left_schema = crate::binding::bind_source_plan_schema_in_scope(
597                engine,
598                left,
599                params,
600                ctes,
601                scope.with_names(),
602            )?;
603            let preceding = scope.right_namespace(left, &left_schema, *kind);
604            validate_source_set_contexts_in_scope(
605                engine,
606                resolver,
607                right,
608                params,
609                ctes,
610                crate::binding::SourceScope {
611                    outer: scope.outer,
612                    preceding: Some(&preceding),
613                    inherited_lateral: *lateral,
614                    retain_names: true,
615                },
616            )
617        }
618        SourcePlan::Values { rows, .. } => {
619            let empty = RowSchema::default();
620            validate_values_set_contexts(engine, resolver, rows, outer.unwrap_or(&empty), params)
621        }
622        SourcePlan::Function { args, .. } => {
623            let empty = RowSchema::default();
624            reject_set_descendant(
625                engine,
626                resolver,
627                args,
628                outer.unwrap_or(&empty),
629                params,
630                top_level_from,
631            )
632        }
633        SourcePlan::FunctionGroup { functions, .. } => {
634            let empty = RowSchema::default();
635            for function in functions {
636                reject_set_descendant(
637                    engine,
638                    resolver,
639                    &function.args,
640                    outer.unwrap_or(&empty),
641                    params,
642                    top_level_from,
643                )?;
644            }
645            Ok(())
646        }
647        SourcePlan::Subquery { .. } | SourcePlan::Table { .. } => Ok(()),
648    }
649}
650
651pub fn validate_values_set_contexts(
652    engine: &dyn SetFunctionCatalog,
653    resolver: &dyn FunctionTypeResolver,
654    rows: &[Vec<ScalarExpr>],
655    schema: &RowSchema,
656    params: &[SQLParam],
657) -> Result<(), SQLError> {
658    if !placing_set_calls() {
659        return Ok(());
660    }
661    reject_set_placement(
662        engine,
663        resolver,
664        rows.iter().flatten(),
665        schema,
666        params,
667        SetPlacement::Values,
668    )
669}
670
671#[cfg(test)]
672mod tests;