mz_sql/plan/
typeconv.rs

1// Copyright Materialize, Inc. and contributors. All rights reserved.
2//
3// Use of this software is governed by the Business Source License
4// included in the LICENSE file.
5//
6// As of the Change Date specified in that file, in accordance with
7// the Business Source License, use of this software will be governed
8// by the Apache License, Version 2.0.
9
10//! Maintains a catalog of valid casts between [`mz_repr::SqlScalarType`]s, as well as
11//! other cast-related functions.
12
13use std::cell::RefCell;
14use std::collections::BTreeMap;
15use std::sync::LazyLock;
16
17use dynfmt::{Format, SimpleCurlyFormat};
18use itertools::Itertools;
19use mz_expr::func::{CastArrayToJsonb, CastListToJsonb};
20use mz_expr::{VariadicFunc, func};
21use mz_repr::{ColumnName, Datum, ScalarBaseType, SqlColumnType, SqlRelationType, SqlScalarType};
22
23use crate::catalog::TypeCategory;
24use crate::plan::error::PlanError;
25use crate::plan::hir::{
26    AbstractColumnType, CoercibleScalarExpr, CoercibleScalarType, HirScalarExpr, UnaryFunc,
27};
28use crate::plan::query::{ExprContext, QueryContext};
29use crate::plan::scope::Scope;
30
31/// Like func::sql_impl_func, but for casts.
32fn sql_impl_cast(expr: &'static str) -> CastTemplate {
33    let invoke = crate::func::sql_impl(expr);
34    CastTemplate::new(move |ecx, _ccx, from_type, _to_type| {
35        // Oddly, this needs to be able to gracefully fail so we can detect unmet dependencies.
36        let mut out = invoke(ecx.qcx, vec![from_type.clone()]).ok()?;
37        Some(move |e| {
38            out.splice_parameters(&[e], 0);
39            out
40        })
41    })
42}
43
44fn sql_impl_cast_per_context(casts: &[(CastContext, &'static str)]) -> CastTemplate {
45    let casts: BTreeMap<CastContext, _> = casts
46        .iter()
47        .map(|(ccx, expr)| (ccx.clone(), crate::func::sql_impl(expr)))
48        .collect();
49    CastTemplate::new(move |ecx, ccx, from_type, _to_type| {
50        let invoke = &casts[&ccx];
51        let r = invoke(ecx.qcx, vec![from_type.clone()]);
52        let mut out = r.ok()?;
53        Some(move |e| {
54            out.splice_parameters(&[e], 0);
55            out
56        })
57    })
58}
59
60/// A cast is a function that takes a `ScalarExpr` to another `ScalarExpr`.
61type Cast = Box<dyn FnOnce(HirScalarExpr) -> HirScalarExpr>;
62
63/// A cast template is a function that produces a `Cast` given a concrete input
64/// and output type. A template can return `None` to indicate that it is
65/// incapable of producing a cast for the specified types.
66///
67/// Cast templates are used to share code for similar casts, where the input or
68/// output type is of one "category" of type. For example, a single cast
69/// template handles converting from strings to any list type. Without cast
70/// templates, we'd have to enumerate every possible list -> list conversion,
71/// which is impractical.
72struct CastTemplate(
73    Box<
74        dyn Fn(&ExprContext, CastContext, &SqlScalarType, &SqlScalarType) -> Option<Cast>
75            + Send
76            + Sync,
77    >,
78);
79
80impl CastTemplate {
81    fn new<T, C>(t: T) -> CastTemplate
82    where
83        T: Fn(&ExprContext, CastContext, &SqlScalarType, &SqlScalarType) -> Option<C>
84            + Send
85            + Sync
86            + 'static,
87        C: FnOnce(HirScalarExpr) -> HirScalarExpr + 'static,
88    {
89        CastTemplate(Box::new(move |ecx, ccx, from_ty, to_ty| {
90            Some(Box::new(t(ecx, ccx, from_ty, to_ty)?))
91        }))
92    }
93}
94
95impl From<UnaryFunc> for CastTemplate {
96    fn from(u: UnaryFunc) -> CastTemplate {
97        CastTemplate::new(move |_ecx, _ccx, _from, _to| {
98            let u = u.clone();
99            Some(move |expr: HirScalarExpr| expr.call_unary(u))
100        })
101    }
102}
103
104impl<const N: usize> From<[UnaryFunc; N]> for CastTemplate {
105    fn from(funcs: [UnaryFunc; N]) -> CastTemplate {
106        CastTemplate::new(move |_ecx, _ccx, _from, _to| {
107            let funcs = funcs.clone();
108            Some(move |mut expr: HirScalarExpr| {
109                for func in funcs {
110                    expr = expr.call_unary(func.clone());
111                }
112                expr
113            })
114        })
115    }
116}
117
118/// STRING to REG*
119///
120/// A reg* type represents a specific type of object by oid.
121///
122/// Casting from a string to a reg*:
123/// - Accepts a string that looks like an OID and converts the value to the
124///   specified reg* type. This is available in all cases except explicitly
125///   casting text values to regclass (e.g. `SELECT '2'::text::regclass`)
126/// - Resolves non-OID-appearing strings to objects. If this string resolves to
127///   more than one OID (e.g. functions), it errors.
128///
129/// The below code provides a template to accomplish this for various reg*
130/// types. Arguments in order are:
131/// - 0: type catalog name this is casting to
132/// - 1: the category of this reg for the error message
133/// - 2: Whether or not to permit passing through numeric values as OIDs
134const STRING_REG_CAST_TEMPLATE: &str = "
135(SELECT
136CASE
137    WHEN $1 IS NULL THEN NULL
138-- Handle OID-like input, if available via {2}
139    WHEN {2} AND pg_catalog.substring($1, 1, 1) BETWEEN '0' AND '9' THEN
140        $1::pg_catalog.oid::pg_catalog.{0}
141    ELSE (
142    -- String case; look up that the item exists
143        SELECT o.oid
144        FROM mz_unsafe.mz_error_if_null(
145            (
146                -- We need to ensure a distinct here in the case of e.g. functions,
147                -- where multiple items share a GlobalId.
148                SELECT DISTINCT id AS name_id
149                FROM mz_internal.mz_resolve_object_name('{0}', $1)
150            ),
151            -- TODO: Support the correct error code for does not exist (42883).
152            '{1} \"' || $1 || '\" does not exist'
153        ) AS i (name_id),
154        -- Lateral lets us error separately from DNE case
155        LATERAL (
156            SELECT
157                CASE
158            -- Handle too many OIDs
159                WHEN mz_catalog.list_length(mz_catalog.list_agg(oid)) > 1 THEN
160                    mz_unsafe.mz_error_if_null(
161                        NULL::pg_catalog.{0},
162                        'more than one {1} named \"' || $1 || '\"'
163                    )
164            -- Resolve object name's OID if we know there is only one
165                ELSE
166                    CAST(mz_catalog.list_agg(oid)[1] AS pg_catalog.{0})
167                END
168            FROM mz_catalog.mz_objects
169            WHERE id = name_id
170            GROUP BY id
171        ) AS o (oid)
172    )
173END)";
174
175static STRING_TO_REGCLASS_EXPLICIT: LazyLock<String> = LazyLock::new(|| {
176    SimpleCurlyFormat
177        .format(STRING_REG_CAST_TEMPLATE, ["regclass", "relation", "false"])
178        .unwrap()
179        .to_string()
180});
181
182static STRING_TO_REGCLASS_COERCED: LazyLock<String> = LazyLock::new(|| {
183    SimpleCurlyFormat
184        .format(STRING_REG_CAST_TEMPLATE, ["regclass", "relation", "true"])
185        .unwrap()
186        .to_string()
187});
188
189static STRING_TO_REGPROC: LazyLock<String> = LazyLock::new(|| {
190    SimpleCurlyFormat
191        .format(STRING_REG_CAST_TEMPLATE, ["regproc", "function", "true"])
192        .unwrap()
193        .to_string()
194});
195
196static STRING_TO_REGTYPE: LazyLock<String> = LazyLock::new(|| {
197    SimpleCurlyFormat
198        .format(STRING_REG_CAST_TEMPLATE, ["regtype", "type", "true"])
199        .unwrap()
200        .to_string()
201});
202
203const REG_STRING_CAST_TEMPLATE: &str = "(
204SELECT
205    COALESCE(mz_internal.mz_global_id_to_name(o.id), CAST($1 AS pg_catalog.oid)::pg_catalog.text)
206    AS text
207FROM
208  (
209        SELECT
210          (
211            SELECT DISTINCT id
212            FROM
213              mz_catalog.mz_objects AS o
214                JOIN
215                  mz_internal.mz_object_oid_alias AS a
216                  ON o.type = a.object_type
217            WHERE
218              oid = CAST($1 AS pg_catalog.oid)
219                AND
220              a.oid_alias = '{0}'
221          )
222      )
223      AS o
224)";
225
226static REGCLASS_TO_STRING: LazyLock<String> = LazyLock::new(|| {
227    SimpleCurlyFormat
228        .format(REG_STRING_CAST_TEMPLATE, ["regclass"])
229        .unwrap()
230        .to_string()
231});
232
233static REGPROC_TO_STRING: LazyLock<String> = LazyLock::new(|| {
234    SimpleCurlyFormat
235        .format(REG_STRING_CAST_TEMPLATE, ["regproc"])
236        .unwrap()
237        .to_string()
238});
239
240static REGTYPE_TO_STRING: LazyLock<String> = LazyLock::new(|| {
241    SimpleCurlyFormat
242        .format(REG_STRING_CAST_TEMPLATE, ["regtype"])
243        .unwrap()
244        .to_string()
245});
246
247/// Describes the context of a cast.
248///
249/// n.b. this type derived `Ord, PartialOrd` and the ordering of these values
250/// has semantics meaning; casts are only permitted when the caller's cast
251/// context is geq the ccx we store, which is the minimum required context.
252#[derive(Debug, Clone, Copy, Eq, PartialEq, Ord, PartialOrd)]
253pub enum CastContext {
254    /// Implicit casts are "no-brainer" casts that apply automatically in
255    /// expressions. They are typically lossless, such as `SqlScalarType::Int32` to
256    /// `SqlScalarType::Int64`.
257    Implicit,
258    /// Assignment casts are "reasonable" casts that make sense to apply
259    /// automatically in `INSERT` statements, but are surprising enough that
260    /// they don't apply implicitly in expressions.
261    Assignment,
262    /// Explicit casts are casts that are possible but may be surprising, like
263    /// casting `SqlScalarType::Json` to `SqlScalarType::Int32`, and therefore they do
264    /// not happen unless explicitly requested by the user with a cast operator.
265    Explicit,
266    /// Coerced casts permit different behavior when a type is coerced from a
267    /// string literal vs. a value of type `pg_catalog::text`.
268    ///
269    /// The only call site that should pass this value in to this module is
270    /// string coercion.
271    Coerced,
272}
273
274/// The implementation of a cast.
275struct CastImpl {
276    template: CastTemplate,
277    context: CastContext,
278}
279
280macro_rules! casts(
281    {
282        $(
283            $from_to:expr => $cast_context:ident: $cast_template:expr
284        ),+
285    } => {{
286        let mut m = BTreeMap::new();
287        $(
288            m.insert($from_to, CastImpl {
289                template: $cast_template.into(),
290                context: CastContext::$cast_context,
291            });
292        )+
293        m
294    }};
295);
296
297static VALID_CASTS: LazyLock<BTreeMap<(ScalarBaseType, ScalarBaseType), CastImpl>> = LazyLock::new(
298    || {
299        use ScalarBaseType::*;
300        use UnaryFunc::*;
301
302        casts! {
303            // BOOL
304            (Bool, Int32) => Explicit: CastBoolToInt32(func::CastBoolToInt32),
305            (Bool, Int64) => Explicit: CastBoolToInt64(func::CastBoolToInt64),
306            (Bool, String) => Assignment: CastBoolToString(func::CastBoolToString),
307
308            //INT16
309            (Int16, Int32) => Implicit: CastInt16ToInt32(func::CastInt16ToInt32),
310            (Int16, Int64) => Implicit: CastInt16ToInt64(func::CastInt16ToInt64),
311            (Int16, UInt16) => Assignment: CastInt16ToUint16(func::CastInt16ToUint16),
312            (Int16, UInt32) => Assignment: CastInt16ToUint32(func::CastInt16ToUint32),
313            (Int16, UInt64) => Assignment: CastInt16ToUint64(func::CastInt16ToUint64),
314            (Int16, Float32) => Implicit: CastInt16ToFloat32(func::CastInt16ToFloat32),
315            (Int16, Float64) => Implicit: CastInt16ToFloat64(func::CastInt16ToFloat64),
316            (Int16, Numeric) => Implicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
317                let s = to_type.unwrap_numeric_max_scale();
318                Some(move |e: HirScalarExpr| e.call_unary(CastInt16ToNumeric(func::CastInt16ToNumeric(s))))
319            }),
320            (Int16, Oid) => Implicit: [
321                CastInt16ToInt32(func::CastInt16ToInt32),
322                CastInt32ToOid(func::CastInt32ToOid),
323            ],
324            (Int16, RegClass) => Implicit: [
325                CastInt16ToInt32(func::CastInt16ToInt32),
326                CastInt32ToOid(func::CastInt32ToOid),
327                CastOidToRegClass(func::CastOidToRegClass),
328            ],
329            (Int16, RegProc) => Implicit: [
330                CastInt16ToInt32(func::CastInt16ToInt32),
331                CastInt32ToOid(func::CastInt32ToOid),
332                CastOidToRegProc(func::CastOidToRegProc),
333            ],
334            (Int16, RegType) => Implicit: [
335                CastInt16ToInt32(func::CastInt16ToInt32),
336                CastInt32ToOid(func::CastInt32ToOid),
337                CastOidToRegType(func::CastOidToRegType),
338            ],
339            (Int16, String) => Assignment: CastInt16ToString(func::CastInt16ToString),
340
341            //INT32
342            (Int32, Bool) => Explicit: CastInt32ToBool(func::CastInt32ToBool),
343            (Int32, Oid) => Implicit: CastInt32ToOid(func::CastInt32ToOid),
344            (Int32, RegClass) => Implicit: [
345                CastInt32ToOid(func::CastInt32ToOid),
346                CastOidToRegClass(func::CastOidToRegClass),
347            ],
348            (Int32, RegProc) => Implicit: [
349                CastInt32ToOid(func::CastInt32ToOid),
350                CastOidToRegProc(func::CastOidToRegProc),
351            ],
352            (Int32, RegType) => Implicit: [
353                CastInt32ToOid(func::CastInt32ToOid),
354                CastOidToRegType(func::CastOidToRegType),
355            ],
356            (Int32, PgLegacyChar) => Explicit: CastInt32ToPgLegacyChar(func::CastInt32ToPgLegacyChar),
357            (Int32, Int16) => Assignment: CastInt32ToInt16(func::CastInt32ToInt16),
358            (Int32, Int64) => Implicit: CastInt32ToInt64(func::CastInt32ToInt64),
359            (Int32, UInt16) => Assignment: CastInt32ToUint16(func::CastInt32ToUint16),
360            (Int32, UInt32) => Assignment: CastInt32ToUint32(func::CastInt32ToUint32),
361            (Int32, UInt64) => Assignment: CastInt32ToUint64(func::CastInt32ToUint64),
362            (Int32, Float32) => Implicit: CastInt32ToFloat32(func::CastInt32ToFloat32),
363            (Int32, Float64) => Implicit: CastInt32ToFloat64(func::CastInt32ToFloat64),
364            (Int32, Numeric) => Implicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
365                let s = to_type.unwrap_numeric_max_scale();
366                Some(move |e: HirScalarExpr| e.call_unary(CastInt32ToNumeric(func::CastInt32ToNumeric(s))))
367            }),
368            (Int32, String) => Assignment: CastInt32ToString(func::CastInt32ToString),
369
370            // INT64
371            (Int64, Bool) => Explicit: CastInt64ToBool(func::CastInt64ToBool),
372            (Int64, Int16) => Assignment: CastInt64ToInt16(func::CastInt64ToInt16),
373            (Int64, Int32) => Assignment: CastInt64ToInt32(func::CastInt64ToInt32),
374            (Int64, UInt16) => Assignment: CastInt64ToUint16(func::CastInt64ToUint16),
375            (Int64, UInt32) => Assignment: CastInt64ToUint32(func::CastInt64ToUint32),
376            (Int64, UInt64) => Assignment: CastInt64ToUint64(func::CastInt64ToUint64),
377            (Int64, Numeric) => Implicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
378                let s = to_type.unwrap_numeric_max_scale();
379                Some(move |e: HirScalarExpr| e.call_unary(CastInt64ToNumeric(func::CastInt64ToNumeric(s))))
380            }),
381            (Int64, Float32) => Implicit: CastInt64ToFloat32(func::CastInt64ToFloat32),
382            (Int64, Float64) => Implicit: CastInt64ToFloat64(func::CastInt64ToFloat64),
383            (Int64, Oid) => Implicit: CastInt64ToOid(func::CastInt64ToOid),
384            (Int64, RegClass) => Implicit: [
385                CastInt64ToOid(func::CastInt64ToOid),
386                CastOidToRegClass(func::CastOidToRegClass),
387            ],
388            (Int64, RegProc) => Implicit: [
389                CastInt64ToOid(func::CastInt64ToOid),
390                CastOidToRegProc(func::CastOidToRegProc),
391            ],
392            (Int64, RegType) => Implicit: [
393                CastInt64ToOid(func::CastInt64ToOid),
394                CastOidToRegType(func::CastOidToRegType),
395            ],
396            (Int64, String) => Assignment: CastInt64ToString(func::CastInt64ToString),
397
398            // UINT16
399            (UInt16, UInt32) => Implicit: CastUint16ToUint32(func::CastUint16ToUint32),
400            (UInt16, UInt64) => Implicit: CastUint16ToUint64(func::CastUint16ToUint64),
401            (UInt16, Int16) => Assignment: CastUint16ToInt16(func::CastUint16ToInt16),
402            (UInt16, Int32) => Implicit: CastUint16ToInt32(func::CastUint16ToInt32),
403            (UInt16, Int64) => Implicit: CastUint16ToInt64(func::CastUint16ToInt64),
404            (UInt16, Numeric) => Implicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
405                let s = to_type.unwrap_numeric_max_scale();
406                Some(move |e: HirScalarExpr| e.call_unary(CastUint16ToNumeric(func::CastUint16ToNumeric(s))))
407            }),
408            (UInt16, Float32) => Implicit: CastUint16ToFloat32(func::CastUint16ToFloat32),
409            (UInt16, Float64) => Implicit: CastUint16ToFloat64(func::CastUint16ToFloat64),
410            (UInt16, String) => Assignment: CastUint16ToString(func::CastUint16ToString),
411
412            // UINT32
413            (UInt32, UInt16) => Assignment: CastUint32ToUint16(func::CastUint32ToUint16),
414            (UInt32, UInt64) => Implicit: CastUint32ToUint64(func::CastUint32ToUint64),
415            (UInt32, Int16) => Assignment: CastUint32ToInt16(func::CastUint32ToInt16),
416            (UInt32, Int32) => Assignment: CastUint32ToInt32(func::CastUint32ToInt32),
417            (UInt32, Int64) => Implicit: CastUint32ToInt64(func::CastUint32ToInt64),
418            (UInt32, Numeric) => Implicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
419                let s = to_type.unwrap_numeric_max_scale();
420                Some(move |e: HirScalarExpr| e.call_unary(CastUint32ToNumeric(func::CastUint32ToNumeric(s))))
421            }),
422            (UInt32, Float32) => Implicit: CastUint32ToFloat32(func::CastUint32ToFloat32),
423            (UInt32, Float64) => Implicit: CastUint32ToFloat64(func::CastUint32ToFloat64),
424            (UInt32, String) => Assignment: CastUint32ToString(func::CastUint32ToString),
425
426            // UINT64
427            (UInt64, UInt16) => Assignment: CastUint64ToUint16(func::CastUint64ToUint16),
428            (UInt64, UInt32) => Assignment: CastUint64ToUint32(func::CastUint64ToUint32),
429            (UInt64, Int16) => Assignment: CastUint64ToInt16(func::CastUint64ToInt16),
430            (UInt64, Int32) => Assignment: CastUint64ToInt32(func::CastUint64ToInt32),
431            (UInt64, Int64) => Assignment: CastUint64ToInt64(func::CastUint64ToInt64),
432            (UInt64, Numeric) => Implicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
433                let s = to_type.unwrap_numeric_max_scale();
434                Some(move |e: HirScalarExpr| e.call_unary(CastUint64ToNumeric(func::CastUint64ToNumeric(s))))
435            }),
436            (UInt64, Float32) => Implicit: CastUint64ToFloat32(func::CastUint64ToFloat32),
437            (UInt64, Float64) => Implicit: CastUint64ToFloat64(func::CastUint64ToFloat64),
438            (UInt64, String) => Assignment: CastUint64ToString(func::CastUint64ToString),
439
440            // MZ_TIMESTAMP
441            (MzTimestamp, String) => Assignment: CastMzTimestampToString(func::CastMzTimestampToString),
442            (MzTimestamp, Timestamp) => Assignment: CastMzTimestampToTimestamp(func::CastMzTimestampToTimestamp),
443            (MzTimestamp, TimestampTz) => Assignment: CastMzTimestampToTimestampTz(func::CastMzTimestampToTimestampTz),
444            (String, MzTimestamp) => Assignment: CastStringToMzTimestamp(func::CastStringToMzTimestamp),
445            (UInt64, MzTimestamp) => Implicit: CastUint64ToMzTimestamp(func::CastUint64ToMzTimestamp),
446            (UInt32, MzTimestamp) => Implicit: CastUint32ToMzTimestamp(func::CastUint32ToMzTimestamp),
447            (Int64, MzTimestamp) => Implicit: CastInt64ToMzTimestamp(func::CastInt64ToMzTimestamp),
448            (Int32, MzTimestamp) => Implicit: CastInt32ToMzTimestamp(func::CastInt32ToMzTimestamp),
449            (Numeric, MzTimestamp) => Implicit: CastNumericToMzTimestamp(func::CastNumericToMzTimestamp),
450            (Timestamp, MzTimestamp) => Implicit: CastTimestampToMzTimestamp(func::CastTimestampToMzTimestamp),
451            (TimestampTz, MzTimestamp) => Implicit: CastTimestampTzToMzTimestamp(func::CastTimestampTzToMzTimestamp),
452            (Date, MzTimestamp) => Implicit: CastDateToMzTimestamp(func::CastDateToMzTimestamp),
453
454            // OID
455            (Oid, Int32) => Assignment: CastOidToInt32(func::CastOidToInt32),
456            (Oid, Int64) => Assignment: CastOidToInt32(func::CastOidToInt32),
457            (Oid, String) => Explicit: CastOidToString(func::CastOidToString),
458            (Oid, RegClass) => Implicit: CastOidToRegClass(func::CastOidToRegClass),
459            (Oid, RegProc) => Implicit: CastOidToRegProc(func::CastOidToRegProc),
460            (Oid, RegType) => Implicit: CastOidToRegType(func::CastOidToRegType),
461
462            // REGCLASS
463            (RegClass, Oid) => Implicit: CastRegClassToOid(func::CastRegClassToOid),
464            (RegClass, String) => Explicit: sql_impl_cast(&REGCLASS_TO_STRING),
465
466            // REGPROC
467            (RegProc, Oid) => Implicit: CastRegProcToOid(func::CastRegProcToOid),
468            (RegProc, String) => Explicit: sql_impl_cast(&REGPROC_TO_STRING),
469
470            // REGTYPE
471            (RegType, Oid) => Implicit: CastRegTypeToOid(func::CastRegTypeToOid),
472            (RegType, String) => Explicit: sql_impl_cast(&REGTYPE_TO_STRING),
473
474            // FLOAT32
475            (Float32, Int16) => Assignment: CastFloat32ToInt16(func::CastFloat32ToInt16),
476            (Float32, Int32) => Assignment: CastFloat32ToInt32(func::CastFloat32ToInt32),
477            (Float32, Int64) => Assignment: CastFloat32ToInt64(func::CastFloat32ToInt64),
478            (Float32, UInt16) => Assignment: CastFloat32ToUint16(func::CastFloat32ToUint16),
479            (Float32, UInt32) => Assignment: CastFloat32ToUint32(func::CastFloat32ToUint32),
480            (Float32, UInt64) => Assignment: CastFloat32ToUint64(func::CastFloat32ToUint64),
481            (Float32, Float64) => Implicit: CastFloat32ToFloat64(func::CastFloat32ToFloat64),
482            (Float32, Numeric) => Assignment: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
483                let s = to_type.unwrap_numeric_max_scale();
484                Some(move |e: HirScalarExpr| e.call_unary(CastFloat32ToNumeric(func::CastFloat32ToNumeric(s))))
485            }),
486            (Float32, String) => Assignment: CastFloat32ToString(func::CastFloat32ToString),
487
488            // FLOAT64
489            (Float64, Int16) => Assignment: CastFloat64ToInt16(func::CastFloat64ToInt16),
490            (Float64, Int32) => Assignment: CastFloat64ToInt32(func::CastFloat64ToInt32),
491            (Float64, Int64) => Assignment: CastFloat64ToInt64(func::CastFloat64ToInt64),
492            (Float64, UInt16) => Assignment: CastFloat64ToUint16(func::CastFloat64ToUint16),
493            (Float64, UInt32) => Assignment: CastFloat64ToUint32(func::CastFloat64ToUint32),
494            (Float64, UInt64) => Assignment: CastFloat64ToUint64(func::CastFloat64ToUint64),
495            (Float64, Float32) => Assignment: CastFloat64ToFloat32(func::CastFloat64ToFloat32),
496            (Float64, Numeric) => Assignment: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
497                let s = to_type.unwrap_numeric_max_scale();
498                Some(move |e: HirScalarExpr| e.call_unary(CastFloat64ToNumeric(func::CastFloat64ToNumeric(s))))
499            }),
500            (Float64, String) => Assignment: CastFloat64ToString(func::CastFloat64ToString),
501
502            // DATE
503            (Date, Timestamp) => Implicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
504                let p = to_type.unwrap_timestamp_precision();
505                Some(move |e: HirScalarExpr| e.call_unary(CastDateToTimestamp(func::CastDateToTimestamp(p))))
506            }),
507            (Date, TimestampTz) => Implicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
508                let p = to_type.unwrap_timestamp_precision();
509                Some(move |e: HirScalarExpr| e.call_unary(CastDateToTimestampTz(func::CastDateToTimestampTz(p))))
510            }),
511            (Date, String) => Assignment: CastDateToString(func::CastDateToString),
512
513            // TIME
514            (Time, Interval) => Implicit: CastTimeToInterval(func::CastTimeToInterval),
515            (Time, String) => Assignment: CastTimeToString(func::CastTimeToString),
516
517            // TIMESTAMP
518            (Timestamp, Date) => Assignment: CastTimestampToDate(func::CastTimestampToDate),
519            (Timestamp, TimestampTz) => Implicit: CastTemplate::new(|_ecx, _ccx, from_type, to_type| {
520                let from = from_type.unwrap_timestamp_precision();
521                let to = to_type.unwrap_timestamp_precision();
522                Some(move |e: HirScalarExpr| e.call_unary(CastTimestampToTimestampTz(func::CastTimestampToTimestampTz{from, to})))
523            }),
524            (Timestamp, Timestamp) => Assignment: CastTemplate::new(|_ecx, _ccx, from_type, to_type| {
525                let from = from_type.unwrap_timestamp_precision();
526                let to = to_type.unwrap_timestamp_precision();
527                Some(move |e: HirScalarExpr| e.call_unary(AdjustTimestampPrecision(func::AdjustTimestampPrecision{from, to})))
528            }),
529            (Timestamp, Time) => Assignment: CastTimestampToTime(func::CastTimestampToTime),
530            (Timestamp, String) => Assignment: CastTimestampToString(func::CastTimestampToString),
531
532            // TIMESTAMPTZ
533            (TimestampTz, Date) => Assignment: CastTimestampTzToDate(func::CastTimestampTzToDate),
534            (TimestampTz, Timestamp) => Assignment: CastTemplate::new(|_ecx, _ccx, from_type, to_type| {
535                let from = from_type.unwrap_timestamp_precision();
536                let to = to_type.unwrap_timestamp_precision();
537                Some(move |e: HirScalarExpr| e.call_unary(CastTimestampTzToTimestamp(func::CastTimestampTzToTimestamp{from, to})))
538            }),
539            (TimestampTz, TimestampTz) => Assignment: CastTemplate::new(|_ecx, _ccx, from_type, to_type| {
540                let from = from_type.unwrap_timestamp_precision();
541                let to = to_type.unwrap_timestamp_precision();
542                Some(move |e: HirScalarExpr| e.call_unary(AdjustTimestampTzPrecision(func::AdjustTimestampTzPrecision{from, to})))
543            }),
544            (TimestampTz, Time) => Assignment: CastTimestampTzToTime(func::CastTimestampTzToTime),
545            (TimestampTz, String) => Assignment: CastTimestampTzToString(func::CastTimestampTzToString),
546
547            // INTERVAL
548            (Interval, Time) => Assignment: CastIntervalToTime(func::CastIntervalToTime),
549            (Interval, String) => Assignment: CastIntervalToString(func::CastIntervalToString),
550
551            // BYTES
552            (Bytes, String) => Assignment: CastBytesToString(func::CastBytesToString),
553
554            // STRING
555            (String, Bool) => Explicit: CastStringToBool(func::CastStringToBool),
556            (String, Int16) => Explicit: CastStringToInt16(func::CastStringToInt16),
557            (String, Int32) => Explicit: CastStringToInt32(func::CastStringToInt32),
558            (String, Int64) => Explicit: CastStringToInt64(func::CastStringToInt64),
559            (String, UInt16) => Explicit: CastStringToUint16(func::CastStringToUint16),
560            (String, UInt32) => Explicit: CastStringToUint32(func::CastStringToUint32),
561            (String, UInt64) => Explicit: CastStringToUint64(func::CastStringToUint64),
562            (String, Oid) => Explicit: CastStringToOid(func::CastStringToOid),
563
564            // STRING to REG*
565            // A reg* type represents a specific type of object by oid.
566            // Converting from string to reg* does a lookup of the object name
567            // in the corresponding mz_catalog table and expects exactly one object to match it.
568            // You can also specify (in postgres) a string that's a valid
569            // int4 and it'll happily cast it (without verifying that the int4 matches
570            // an object oid).
571            // TODO: Support the correct error code for does not exist (42883).
572            (String, RegClass) => Explicit: sql_impl_cast_per_context(
573                &[
574                    (CastContext::Explicit, &STRING_TO_REGCLASS_EXPLICIT),
575                    (CastContext::Coerced, &STRING_TO_REGCLASS_COERCED)
576                ]
577            ),
578            (String, RegProc) => Explicit: sql_impl_cast(&STRING_TO_REGPROC),
579            (String, RegType) => Explicit: sql_impl_cast(&STRING_TO_REGTYPE),
580
581            (String, Float32) => Explicit: CastStringToFloat32(func::CastStringToFloat32),
582            (String, Float64) => Explicit: CastStringToFloat64(func::CastStringToFloat64),
583            (String, Numeric) => Explicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
584                let s = to_type.unwrap_numeric_max_scale();
585                Some(move |e: HirScalarExpr| e.call_unary(CastStringToNumeric(func::CastStringToNumeric(s))))
586            }),
587            (String, Date) => Explicit: CastStringToDate(func::CastStringToDate),
588            (String, Time) => Explicit: CastStringToTime(func::CastStringToTime),
589            (String, Timestamp) => Explicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
590                let p = to_type.unwrap_timestamp_precision();
591                Some(move |e: HirScalarExpr| e.call_unary(CastStringToTimestamp(func::CastStringToTimestamp(p))))
592            }),
593            (String, TimestampTz) => Explicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
594                let p = to_type.unwrap_timestamp_precision();
595                Some(move |e: HirScalarExpr| e.call_unary(CastStringToTimestampTz(func::CastStringToTimestampTz(p))))
596            }),
597            (String, Interval) => Explicit: CastStringToInterval(func::CastStringToInterval),
598            (String, Bytes) => Explicit: CastStringToBytes(func::CastStringToBytes),
599            (String, Jsonb) => Explicit: CastStringToJsonb(func::CastStringToJsonb),
600            (String, Uuid) => Explicit: CastStringToUuid(func::CastStringToUuid),
601            (String, Array) => Explicit: CastTemplate::new(|ecx, ccx, from_type, to_type| {
602                let return_ty = to_type.clone();
603                let to_el_type = to_type.unwrap_array_element_type();
604                let cast_expr = plan_hypothetical_cast(ecx, ccx, from_type, to_el_type)?;
605                Some(|e: HirScalarExpr| e.call_unary(UnaryFunc::CastStringToArray(func::CastStringToArray {
606                    return_ty,
607                    cast_expr: Box::new(cast_expr),
608                })))
609            }),
610            (String, List) => Explicit: CastTemplate::new(|ecx, ccx, from_type, to_type| {
611                let return_ty = to_type.clone();
612                let to_el_type = to_type.unwrap_list_element_type();
613                let cast_expr = plan_hypothetical_cast(ecx, ccx, from_type, to_el_type)?;
614                Some(|e: HirScalarExpr| e.call_unary(UnaryFunc::CastStringToList(func::CastStringToList {
615                    return_ty,
616                    cast_expr: Box::new(cast_expr),
617                })))
618            }),
619            (String, Map) => Explicit: CastTemplate::new(|ecx, ccx, from_type, to_type| {
620                let return_ty = to_type.clone();
621                let to_val_type = to_type.unwrap_map_value_type();
622                let cast_expr = plan_hypothetical_cast(ecx, ccx, from_type, to_val_type)?;
623                Some(|e: HirScalarExpr| e.call_unary(UnaryFunc::CastStringToMap(func::CastStringToMap {
624                    return_ty,
625                    cast_expr: Box::new(cast_expr),
626                })))
627            }),
628            (String, Range) => Explicit: CastTemplate::new(|ecx, ccx, from_type, to_type| {
629                let return_ty = to_type.clone();
630                let to_el_type = to_type.unwrap_range_element_type();
631                let cast_expr = plan_hypothetical_cast(ecx, ccx, from_type, to_el_type)?;
632                Some(|e: HirScalarExpr| e.call_unary(UnaryFunc::CastStringToRange(func::CastStringToRange {
633                    return_ty,
634                    cast_expr: Box::new(cast_expr),
635                })))
636            }),
637            (String, Int2Vector) => Explicit: CastStringToInt2Vector(func::CastStringToInt2Vector),
638            (String, Char) => Implicit: CastTemplate::new(|_ecx, ccx, _from_type, to_type| {
639                let length = to_type.unwrap_char_length();
640                Some(move |e: HirScalarExpr| e.call_unary(CastStringToChar(func::CastStringToChar {length, fail_on_len: ccx != CastContext::Explicit})))
641            }),
642            (String, VarChar) => Implicit: CastTemplate::new(|_ecx, ccx, _from_type, to_type| {
643                let length = to_type.unwrap_varchar_max_length();
644                Some(move |e: HirScalarExpr| e.call_unary(CastStringToVarChar(func::CastStringToVarChar {length, fail_on_len: ccx != CastContext::Explicit})))
645            }),
646            (String, PgLegacyChar) => Assignment: CastStringToPgLegacyChar(func::CastStringToPgLegacyChar),
647            // CHAR
648            (Char, String) => Implicit: CastCharToString(func::CastCharToString),
649            (Char, Char) => Implicit: CastTemplate::new(|_ecx, ccx, _from_type, to_type| {
650                let length = to_type.unwrap_char_length();
651                Some(move |e: HirScalarExpr| e.call_unary(CastStringToChar(func::CastStringToChar {length, fail_on_len: ccx != CastContext::Explicit})))
652            }),
653            (Char, VarChar) => Implicit: CastTemplate::new(|_ecx, ccx, _from_type, to_type| {
654                let length = to_type.unwrap_varchar_max_length();
655                Some(move |e: HirScalarExpr| e.call_unary(CastStringToVarChar(func::CastStringToVarChar {length, fail_on_len: ccx != CastContext::Explicit})))
656            }),
657            (Char, PgLegacyChar) => Assignment: CastStringToPgLegacyChar(func::CastStringToPgLegacyChar),
658
659            // VARCHAR
660            (VarChar, String) => Implicit: CastVarCharToString(func::CastVarCharToString),
661            (VarChar, Char) => Implicit: CastTemplate::new(|_ecx, ccx, _from_type, to_type| {
662                let length = to_type.unwrap_char_length();
663                Some(move |e: HirScalarExpr| e.call_unary(CastStringToChar(func::CastStringToChar {length, fail_on_len: ccx != CastContext::Explicit})))
664            }),
665            (VarChar, VarChar) => Implicit: CastTemplate::new(|_ecx, ccx, _from_type, to_type| {
666                let length = to_type.unwrap_varchar_max_length();
667                Some(move |e: HirScalarExpr| e.call_unary(CastStringToVarChar(func::CastStringToVarChar {length, fail_on_len: ccx != CastContext::Explicit})))
668            }),
669            (VarChar, PgLegacyChar) => Assignment: CastStringToPgLegacyChar(func::CastStringToPgLegacyChar),
670
671            // PG LEGACY CHAR
672            (PgLegacyChar, String) => Implicit: CastPgLegacyCharToString(func::CastPgLegacyCharToString),
673            (PgLegacyChar, Char) => Assignment: CastPgLegacyCharToChar(func::CastPgLegacyCharToChar),
674            (PgLegacyChar, VarChar) => Assignment: CastPgLegacyCharToVarChar(func::CastPgLegacyCharToVarChar),
675            (PgLegacyChar, Int32) => Explicit: CastPgLegacyCharToInt32(func::CastPgLegacyCharToInt32),
676
677            // PG LEGACY NAME
678            // Under the hood VarChars and Name's are just Strings, so we can re-use existing methods
679            // on Strings and VarChars instead of defining new ones.
680            (PgLegacyName, String) => Implicit: CastVarCharToString(func::CastVarCharToString),
681            (PgLegacyName, Char) => Assignment: CastTemplate::new(|_ecx, ccx, _from_type, to_type| {
682                let length = to_type.unwrap_char_length();
683                Some(move |e: HirScalarExpr| e.call_unary(CastStringToChar(func::CastStringToChar {length, fail_on_len: ccx != CastContext::Explicit})))
684            }),
685            (PgLegacyName, VarChar) => Assignment: CastTemplate::new(|_ecx, ccx, _from_type, to_type| {
686                let length = to_type.unwrap_varchar_max_length();
687                Some(move |e: HirScalarExpr| e.call_unary(CastStringToVarChar(func::CastStringToVarChar {length, fail_on_len: ccx != CastContext::Explicit})))
688            }),
689            (String, PgLegacyName) => Implicit: CastStringToPgLegacyName(func::CastStringToPgLegacyName),
690            (Char, PgLegacyName) => Implicit: CastStringToPgLegacyName(func::CastStringToPgLegacyName),
691            (VarChar, PgLegacyName) => Implicit: CastStringToPgLegacyName(func::CastStringToPgLegacyName),
692
693            // RECORD
694            (Record, String) => Assignment: CastTemplate::new(|_ecx, _ccx, from_type, _to_type| {
695                let ty = from_type.clone();
696                Some(|e: HirScalarExpr| e.call_unary(CastRecordToString(func::CastRecordToString { ty })))
697            }),
698            (Record, Record) => Implicit: CastTemplate::new(|ecx, ccx, from_type, to_type| {
699                if from_type.unwrap_record_element_type().len() != to_type.unwrap_record_element_type().len() {
700                    return None;
701                }
702
703                if let (l @ SqlScalarType::Record {custom_id: Some(..), ..}, r) = (from_type, to_type) {
704                    // Changing `from`'s custom_id requires at least Assignment context
705                    if ccx == CastContext::Implicit && l != r {
706                        return None;
707                    }
708                }
709
710                let cast_exprs = from_type.unwrap_record_element_type()
711                    .iter()
712                    .zip_eq(to_type.unwrap_record_element_type())
713                    .map(|(f, t)| plan_hypothetical_cast(ecx, ccx, f, t))
714                    .collect::<Option<Box<_>>>()?;
715                let to = to_type.clone();
716                Some(|e: HirScalarExpr| e.call_unary(CastRecord1ToRecord2(func::CastRecord1ToRecord2 { return_ty: to, cast_exprs })))
717            }),
718
719            // ARRAY
720            (Array, String) => Assignment: CastTemplate::new(|_ecx, _ccx, from_type, _to_type| {
721                let ty = from_type.clone();
722                Some(|e: HirScalarExpr| e.call_unary(CastArrayToString(func::CastArrayToString { ty })))
723            }),
724            (Array, List) => Explicit: CastArrayToListOneDim(func::CastArrayToListOneDim),
725            (Array, Array) => Explicit: CastTemplate::new(|ecx, ccx, from_type, to_type| {
726                let inner_from_type = from_type.unwrap_array_element_type();
727                let inner_to_type = to_type.unwrap_array_element_type();
728                let cast_expr = plan_hypothetical_cast(ecx, ccx, inner_from_type, inner_to_type)?;
729                let return_ty = to_type.clone();
730
731                Some(move |e: HirScalarExpr| e.call_unary(CastArrayToArray(func::CastArrayToArray { return_ty, cast_expr: Box::new(cast_expr) })))
732            }),
733
734            // INT2VECTOR
735            (Int2Vector, Array) => Implicit: CastTemplate::new(|_ecx, _ccx, _from_type, _to_type| {
736                Some(|e: HirScalarExpr| e.call_unary(UnaryFunc::CastInt2VectorToArray(func::CastInt2VectorToArray)))
737            }),
738            (Int2Vector, String) => Explicit: CastInt2VectorToString(func::CastInt2VectorToString),
739
740            // LIST
741            (List, String) => Assignment: CastTemplate::new(|_ecx, _ccx, from_type, _to_type| {
742                let ty = from_type.clone();
743                Some(|e: HirScalarExpr| e.call_unary(CastListToString(func::CastListToString { ty })))
744            }),
745            (List, List) => Implicit: CastTemplate::new(|ecx, ccx, from_type, to_type| {
746
747                if let (l @ SqlScalarType::List {custom_id: Some(..), ..}, r) = (from_type, to_type) {
748                    // Changing `from`'s custom_id requires at least Assignment context
749                    if ccx == CastContext::Implicit && !l.base_eq(r) {
750                        return None;
751                    }
752                }
753
754                let return_ty = to_type.clone();
755                let from_el_type = from_type.unwrap_list_element_type();
756                let to_el_type = to_type.unwrap_list_element_type();
757                let cast_expr = plan_hypothetical_cast(ecx, ccx, from_el_type, to_el_type)?;
758                Some(|e: HirScalarExpr| e.call_unary(UnaryFunc::CastList1ToList2(func::CastList1ToList2 {
759                    return_ty,
760                    cast_expr: Box::new(cast_expr),
761                })))
762            }),
763
764            // MAP
765            (Map, String) => Assignment: CastTemplate::new(|_ecx, _ccx, from_type, _to_type| {
766                let ty = from_type.clone();
767                Some(|e: HirScalarExpr| e.call_unary(CastMapToString(func::CastMapToString { ty })))
768            }),
769
770            // JSONB
771            (Jsonb, Bool) => Explicit: CastJsonbToBool(func::CastJsonbToBool),
772            (Jsonb, Int16) => Explicit: CastJsonbToInt16(func::CastJsonbToInt16),
773            (Jsonb, Int32) => Explicit: CastJsonbToInt32(func::CastJsonbToInt32),
774            (Jsonb, Int64) => Explicit: CastJsonbToInt64(func::CastJsonbToInt64),
775            (Jsonb, Float32) => Explicit: CastJsonbToFloat32(func::CastJsonbToFloat32),
776            (Jsonb, Float64) => Explicit: CastJsonbToFloat64(func::CastJsonbToFloat64),
777            (Jsonb, Numeric) => Explicit: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
778                let s = to_type.unwrap_numeric_max_scale();
779                Some(move |e: HirScalarExpr| e.call_unary(CastJsonbToNumeric(func::CastJsonbToNumeric(s))))
780            }),
781            (Jsonb, String) => Assignment: CastJsonbToString(func::CastJsonbToString),
782
783            // UUID
784            (Uuid, String) => Assignment: CastUuidToString(func::CastUuidToString),
785
786            // Numeric
787            (Numeric, Numeric) => Assignment: CastTemplate::new(|_ecx, _ccx, _from_type, to_type| {
788                let scale = to_type.unwrap_numeric_max_scale();
789                Some(move |e: HirScalarExpr| match scale {
790                    None => e,
791                    Some(scale) => e.call_unary(UnaryFunc::AdjustNumericScale(func::AdjustNumericScale(scale))),
792                })
793            }),
794            (Numeric, Float32) => Implicit: CastNumericToFloat32(func::CastNumericToFloat32),
795            (Numeric, Float64) => Implicit: CastNumericToFloat64(func::CastNumericToFloat64),
796            (Numeric, Int16) => Assignment: CastNumericToInt16(func::CastNumericToInt16),
797            (Numeric, Int32) => Assignment: CastNumericToInt32(func::CastNumericToInt32),
798            (Numeric, Int64) => Assignment: CastNumericToInt64(func::CastNumericToInt64),
799            (Numeric, UInt16) => Assignment: CastNumericToUint16(func::CastNumericToUint16),
800            (Numeric, UInt32) => Assignment: CastNumericToUint32(func::CastNumericToUint32),
801            (Numeric, UInt64) => Assignment: CastNumericToUint64(func::CastNumericToUint64),
802            (Numeric, String) => Assignment: CastNumericToString(func::CastNumericToString),
803
804            // Range
805            (Range, String) => Assignment: CastTemplate::new(|_ecx, _ccx, from_type, _to_type| {
806                let ty = from_type.clone();
807                Some(|e: HirScalarExpr| e.call_unary(CastRangeToString(func::CastRangeToString { ty })))
808            }),
809
810            // MzAclItem
811            (MzAclItem, String) => Explicit: sql_impl_cast("(
812                SELECT
813                    (CASE
814                        WHEN grantee_role_id = 'p' THEN ''
815                        ELSE COALESCE(grantee_role.name, grantee_role_id)
816                    END)
817                    || '='
818                    || mz_internal.mz_aclitem_privileges($1)
819                    || '/'
820                    || COALESCE(grantor_role.name, grantor_role_id)
821                FROM
822                    (SELECT mz_internal.mz_aclitem_grantee($1) AS grantee_role_id),
823                    (SELECT mz_internal.mz_aclitem_grantor($1) AS grantor_role_id)
824                LEFT JOIN mz_catalog.mz_roles AS grantee_role ON grantee_role_id = grantee_role.id
825                LEFT JOIN mz_catalog.mz_roles AS grantor_role ON grantor_role_id = grantor_role.id
826            )"),
827            (MzAclItem, AclItem) => Explicit: sql_impl_cast("(
828                SELECT makeaclitem(
829                    (CASE mz_internal.mz_aclitem_grantee($1)
830                        WHEN 'p' THEN 0
831                        ELSE (SELECT oid FROM mz_catalog.mz_roles WHERE id = mz_internal.mz_aclitem_grantee($1))
832                    END),
833                    (SELECT oid FROM mz_catalog.mz_roles WHERE id = mz_internal.mz_aclitem_grantor($1)),
834                    (SELECT array_to_string(mz_internal.mz_format_privileges(mz_internal.mz_aclitem_privileges($1)), ',')),
835                    -- GRANT OPTION isn't implemented so we hardcode false.
836                    false
837                )
838            )"),
839
840            // AclItem
841            (AclItem, String) => Explicit: sql_impl_cast("(
842                SELECT
843                    (CASE grantee_oid
844                        WHEN 0 THEN ''
845                        ELSE COALESCE(grantee_role.name, grantee_oid::text)
846                    END)
847                    || '='
848                    || mz_internal.aclitem_privileges($1)
849                    || '/'
850                    || COALESCE(grantor_role.name, grantor_oid::text)
851                FROM
852                    (SELECT mz_internal.aclitem_grantee($1) AS grantee_oid),
853                    (SELECT mz_internal.aclitem_grantor($1) AS grantor_oid)
854                LEFT JOIN mz_catalog.mz_roles AS grantee_role ON grantee_oid = grantee_role.oid
855                LEFT JOIN mz_catalog.mz_roles AS grantor_role ON grantor_oid = grantor_role.oid
856            )"),
857            (AclItem, MzAclItem) => Explicit: sql_impl_cast("(
858                SELECT mz_internal.make_mz_aclitem(
859                    (CASE mz_internal.aclitem_grantee($1)
860                        WHEN 0 THEN 'p'
861                        ELSE (SELECT id FROM mz_catalog.mz_roles WHERE oid = mz_internal.aclitem_grantee($1))
862                    END),
863                    (SELECT id FROM mz_catalog.mz_roles WHERE oid = mz_internal.aclitem_grantor($1)),
864                    (SELECT array_to_string(mz_internal.mz_format_privileges(mz_internal.aclitem_privileges($1)), ','))
865                )
866            )")
867        }
868    },
869);
870
871/// Get casts directly between two [`SqlScalarType`]s, with control over the
872/// allowed [`CastContext`].
873fn get_cast(
874    ecx: &ExprContext,
875    ccx: CastContext,
876    from: &SqlScalarType,
877    to: &SqlScalarType,
878) -> Option<Cast> {
879    use CastContext::*;
880
881    if from == to || (ccx == Implicit && from.base_eq(to)) {
882        return Some(Box::new(|expr| expr));
883    }
884
885    let imp = VALID_CASTS.get(&(from.into(), to.into()))?;
886    let template = if ccx >= imp.context {
887        Some(&imp.template)
888    } else {
889        None
890    };
891    template.and_then(|template| (template.0)(ecx, ccx, from, to))
892}
893
894/// Converts an expression to `SqlScalarType::String`.
895///
896/// All types are convertible to string, so this never fails.
897pub fn to_string(ecx: &ExprContext, expr: HirScalarExpr) -> HirScalarExpr {
898    plan_cast(ecx, CastContext::Explicit, expr, &SqlScalarType::String)
899        .expect("cast known to exist")
900}
901
902/// Converts an expression to `SqlScalarType::Jsonb`.
903///
904/// The rules are as follows:
905///   * `SqlScalarType::Boolean`s become JSON booleans.
906///   * All numeric types are converted to `Float64`s, then become JSON numbers.
907///   * Records are converted to a JSON object where the record's field names
908///     are the keys of the object, and the record's fields are recursively
909///     converted to JSON by `to_jsonb`.
910///   * Other types are converted to strings by their usual cast function an
911//      become JSON strings.
912pub fn to_jsonb(ecx: &ExprContext, expr: HirScalarExpr) -> HirScalarExpr {
913    use SqlScalarType::*;
914
915    match ecx.scalar_type(&expr) {
916        Bool | Jsonb | Numeric { .. } => {
917            expr.call_unary(UnaryFunc::CastJsonbableToJsonb(func::CastJsonbableToJsonb))
918        }
919        Int16 | Int32 | Int64 | UInt16 | UInt32 | UInt64 | Float32 | Float64 => plan_cast(
920            ecx,
921            CastContext::Explicit,
922            expr,
923            &Numeric { max_scale: None },
924        )
925        .expect("cast known to exist")
926        .call_unary(UnaryFunc::CastJsonbableToJsonb(func::CastJsonbableToJsonb)),
927        Record { fields, .. } => {
928            let mut exprs = vec![];
929            for (i, (name, _ty)) in fields.iter().enumerate() {
930                exprs.push(HirScalarExpr::literal(
931                    Datum::String(name),
932                    SqlScalarType::String,
933                ));
934                exprs.push(to_jsonb(
935                    ecx,
936                    expr.clone()
937                        .call_unary(UnaryFunc::RecordGet(func::RecordGet(i))),
938                ));
939            }
940            HirScalarExpr::call_variadic(VariadicFunc::JsonbBuildObject, exprs)
941        }
942        ref ty @ List {
943            ref element_type, ..
944        }
945        | ref ty @ Array(ref element_type) => {
946            // Construct a new expression context with one column whose type
947            // is the container's element type.
948            let qcx = QueryContext::root(ecx.qcx.scx, ecx.qcx.lifetime);
949            let ecx = ExprContext {
950                qcx: &qcx,
951                name: "to_jsonb",
952                scope: &Scope::empty(),
953                relation_type: &SqlRelationType::new(vec![element_type.clone().nullable(true)]),
954                allow_aggregates: false,
955                allow_subqueries: false,
956                allow_parameters: false,
957                allow_windows: false,
958            };
959
960            // Create an element-casting expression by calling `to_jsonb` on
961            // an expression that references the first column in a row.
962            let cast_element = to_jsonb(&ecx, HirScalarExpr::column(0));
963            let cast_element = cast_element
964                .lower_uncorrelated()
965                .expect("to_jsonb does not produce correlated expressions on uncorrelated input");
966
967            // The `Cast{Array|List}ToJsonb` functions take the element-casting
968            // expression as an argument and evaluate the expression against
969            // each element of the container at runtime.
970            let func = match ty {
971                List { .. } => UnaryFunc::CastListToJsonb(CastListToJsonb {
972                    cast_element: Box::new(cast_element),
973                }),
974                Array { .. } => UnaryFunc::CastArrayToJsonb(CastArrayToJsonb {
975                    cast_element: Box::new(cast_element),
976                }),
977                _ => unreachable!("validated above"),
978            };
979
980            expr.call_unary(func)
981        }
982        Date
983        | Time
984        | Timestamp { .. }
985        | TimestampTz { .. }
986        | Interval
987        | PgLegacyChar
988        | PgLegacyName
989        | Bytes
990        | String
991        | Char { .. }
992        | VarChar { .. }
993        | Uuid
994        | Oid
995        | Map { .. }
996        | RegProc
997        | RegType
998        | RegClass
999        | Int2Vector
1000        | MzTimestamp
1001        | Range { .. }
1002        | MzAclItem
1003        | AclItem => to_string(ecx, expr)
1004            .call_unary(UnaryFunc::CastJsonbableToJsonb(func::CastJsonbableToJsonb)),
1005    }
1006}
1007
1008/// Guesses the most-common type among a set of [`SqlScalarType`]s that all members
1009/// can be cast to. Returns `None` if a common type cannot be deduced.
1010///
1011/// Note that this function implements the type-determination components of
1012/// Postgres' ["`UNION`, `CASE`, and Related Constructs"][union-type-conv] type
1013/// conversion.
1014///
1015/// [union-type-conv]: https://www.postgresql.org/docs/12/typeconv-union-case.html
1016pub fn guess_best_common_type(
1017    ecx: &ExprContext,
1018    types: &[CoercibleScalarType],
1019) -> Result<SqlScalarType, PlanError> {
1020    // This function is a translation of `select_common_type` in PostgreSQL with
1021    // the addition of our near match logic, which supports Materialize
1022    // non-linear type promotions.
1023    // https://github.com/postgres/postgres/blob/d1b307eef/src/backend/parser/parse_coerce.c#L1288-L1308
1024
1025    // If every type is a literal record with the same number of fields, the
1026    // best common type is a record with that number of fields. We recursively
1027    // guess the best type for each field.
1028    if let Some(CoercibleScalarType::Record(field_tys)) = types.first() {
1029        if types
1030            .iter()
1031            .all(|t| matches!(t, CoercibleScalarType::Record(fts) if field_tys.len() == fts.len()))
1032        {
1033            let mut fields = vec![];
1034            for i in 0..field_tys.len() {
1035                let name = ColumnName::from(format!("f{}", fields.len() + 1));
1036                let mut guesses = vec![];
1037                let mut nullable = false;
1038                for ty in types {
1039                    let field_ty = match ty {
1040                        CoercibleScalarType::Record(fts) => fts[i].clone(),
1041                        _ => unreachable!(),
1042                    };
1043                    if field_ty.nullable() {
1044                        nullable = true;
1045                    }
1046                    guesses.push(field_ty.scalar_type());
1047                }
1048                let guess = guess_best_common_type(ecx, &guesses)?;
1049                fields.push((name, guess.nullable(nullable)));
1050            }
1051            return Ok(SqlScalarType::Record {
1052                fields: fields.into(),
1053                custom_id: None,
1054            });
1055        }
1056    }
1057
1058    // Remove unknown types, and collect them.
1059    let mut types: Vec<_> = types.into_iter().filter_map(|v| v.as_coerced()).collect();
1060
1061    // In the case of mixed ints and uints, replace uints with their near match
1062    let contains_int = types.iter().any(|t| {
1063        matches!(
1064            t,
1065            SqlScalarType::Int16 | SqlScalarType::Int32 | SqlScalarType::Int64
1066        )
1067    });
1068
1069    for t in types.iter_mut() {
1070        if contains_int
1071            && matches!(
1072                t,
1073                SqlScalarType::UInt16 | SqlScalarType::UInt32 | SqlScalarType::UInt64
1074            )
1075        {
1076            *t = t.near_match().expect("unsigned ints have near matches")
1077        }
1078    }
1079
1080    let mut types = types.iter();
1081
1082    let mut candidate = match types.next() {
1083        // If no known types, fall back to `String`.
1084        None => return Ok(SqlScalarType::String),
1085        // Start by guessing the first type.
1086        Some(t) => t,
1087    };
1088
1089    let preferred_type = TypeCategory::from_type(candidate).preferred_type();
1090
1091    for typ in types {
1092        if TypeCategory::from_type(candidate) != TypeCategory::from_type(typ) {
1093            // The next type is in a different category; give up.
1094            sql_bail!(
1095                "{} types {} and {} cannot be matched",
1096                ecx.name,
1097                ecx.humanize_scalar_type(candidate, false),
1098                ecx.humanize_scalar_type(typ, false),
1099            );
1100        };
1101
1102        // If this type is the preferred type, make it the candidate.
1103        if preferred_type.as_ref() != Some(candidate)
1104            && can_cast(ecx, CastContext::Implicit, candidate, typ)
1105            && !can_cast(ecx, CastContext::Implicit, typ, candidate)
1106        {
1107            // The current candidate is not the preferred type for its category
1108            // and the next type is implicitly convertible to the current
1109            // candidate, but not vice-versa, so take the next type as the new
1110            // candidate.
1111            candidate = typ;
1112        }
1113    }
1114    Ok(candidate.without_modifiers())
1115}
1116
1117pub fn plan_coerce<'a>(
1118    ecx: &'a ExprContext,
1119    e: CoercibleScalarExpr,
1120    coerce_to: &SqlScalarType,
1121) -> Result<HirScalarExpr, PlanError> {
1122    use CoercibleScalarExpr::*;
1123
1124    Ok(match e {
1125        Coerced(e) => e,
1126
1127        LiteralNull => HirScalarExpr::literal_null(coerce_to.clone()),
1128
1129        LiteralString(s) => {
1130            let lit = HirScalarExpr::literal(Datum::String(&s), SqlScalarType::String);
1131            // Per PostgreSQL, string literal explicitly casts to the base type.
1132            // The caller is responsible for applying any desired modifiers
1133            // (with either implicit or explicit semantics) via a separate call
1134            // to `plan_cast`.
1135            let coerce_to_base = &coerce_to.without_modifiers();
1136            plan_cast(ecx, CastContext::Coerced, lit, coerce_to_base)?
1137        }
1138
1139        LiteralRecord(exprs) => {
1140            let arity = exprs.len();
1141            let coercions = match coerce_to {
1142                SqlScalarType::Record { fields, .. } if fields.len() == arity => fields
1143                    .iter()
1144                    .map(|(_name, ty)| &ty.scalar_type)
1145                    .cloned()
1146                    .collect(),
1147                _ => vec![SqlScalarType::String; exprs.len()],
1148            };
1149            let mut out = vec![];
1150            for (e, coerce_to) in exprs.into_iter().zip_eq(coercions) {
1151                out.push(plan_coerce(ecx, e, &coerce_to)?);
1152            }
1153            HirScalarExpr::call_variadic(
1154                VariadicFunc::RecordCreate {
1155                    field_names: (0..arity)
1156                        .map(|i| ColumnName::from(format!("f{}", i + 1)))
1157                        .collect(),
1158                },
1159                out,
1160            )
1161        }
1162
1163        Parameter(n) => {
1164            let prev = ecx.param_types().borrow_mut().insert(n, coerce_to.clone());
1165            if let Some(prev) = prev {
1166                if prev != *coerce_to {
1167                    sql_bail!(
1168                        "there are contradicting constraints for the type of parameter ${}: should be both {} and {}",
1169                        n,
1170                        ecx.humanize_scalar_type(&prev, false),
1171                        ecx.humanize_scalar_type(coerce_to, false),
1172                    );
1173                }
1174            }
1175            HirScalarExpr::parameter(n)
1176        }
1177    })
1178}
1179
1180/// Similar to `plan_cast`, but for situations where you only know the type of
1181/// the input expression (`from`) and not the expression itself. The returned
1182/// expression refers to the first column of some imaginary row, where the first
1183/// column is assumed to have type `from`.
1184///
1185/// If casting from `from` to `to` is not possible, returns `None`.
1186pub fn plan_hypothetical_cast(
1187    ecx: &ExprContext,
1188    ccx: CastContext,
1189    from: &SqlScalarType,
1190    to: &SqlScalarType,
1191) -> Option<mz_expr::MirScalarExpr> {
1192    // Reconstruct an expression context where the expression is evaluated on
1193    // the "first column" of some imaginary row.
1194    let mut scx = ecx.qcx.scx.clone();
1195    scx.param_types = RefCell::new(BTreeMap::new());
1196    let qcx = QueryContext::root(&scx, ecx.qcx.lifetime);
1197    let relation_type = SqlRelationType {
1198        column_types: vec![SqlColumnType {
1199            nullable: true,
1200            scalar_type: from.clone(),
1201        }],
1202        keys: vec![vec![0]],
1203    };
1204    let ecx = ExprContext {
1205        qcx: &qcx,
1206        name: "plan_hypothetical_cast",
1207        scope: &Scope::empty(),
1208        relation_type: &relation_type,
1209        allow_aggregates: false,
1210        allow_subqueries: true,
1211        allow_parameters: true,
1212        allow_windows: false,
1213    };
1214
1215    let col_expr = HirScalarExpr::column(0);
1216
1217    // Determine the `ScalarExpr` required to cast our column to the target
1218    // component type.
1219    plan_cast(&ecx, ccx, col_expr, to)
1220        .ok()?
1221        // TODO(jkosh44) Support casts that have correlated implementations.
1222        .lower_uncorrelated()
1223        .ok()
1224}
1225
1226/// Plans a cast between [`SqlScalarType`]s, specifying which types of casts are
1227/// permitted using [`CastContext`].
1228///
1229/// # Errors
1230///
1231/// If a cast between the `ScalarExpr`'s base type and the specified type is:
1232/// - Not possible, e.g. `Bytes` to `Interval`
1233/// - Not permitted, e.g. implicitly casting from `Float64` to `Float32`.
1234/// - Not implemented yet
1235pub fn plan_cast(
1236    ecx: &ExprContext,
1237    ccx: CastContext,
1238    expr: HirScalarExpr,
1239    to: &SqlScalarType,
1240) -> Result<HirScalarExpr, PlanError> {
1241    let from = ecx.scalar_type(&expr);
1242
1243    // Close over `ccx`, `from`, and `to` to simplify error messages in the
1244    // face of intermediate expressions.
1245    let cast_inner = |from, to, expr| match get_cast(ecx, ccx, from, to) {
1246        Some(cast) => Ok(cast(expr)),
1247        None => Err(PlanError::InvalidCast {
1248            name: ecx.name.into(),
1249            ccx,
1250            from: ecx.humanize_scalar_type(from, false),
1251            to: ecx.humanize_scalar_type(to, false),
1252        }),
1253    };
1254
1255    // Get cast which might include parameter rewrites + generating intermediate
1256    // expressions.
1257    //
1258    // String-like types get special handling to match PostgreSQL.
1259    // See: https://github.com/postgres/postgres/blob/6b04abdfc/src/backend/parser/parse_coerce.c#L3205-L3223
1260    let from_category = TypeCategory::from_type(&from);
1261    let to_category = TypeCategory::from_type(to);
1262    if from_category == TypeCategory::String && to_category != TypeCategory::String {
1263        // Converting from stringlike to something non-stringlike. Handle as if
1264        // `from` were a `SqlScalarType::String.
1265        cast_inner(&SqlScalarType::String, to, expr)
1266    } else if from_category != TypeCategory::String && to_category == TypeCategory::String {
1267        // Converting from non-stringlike to something stringlike. Convert to a
1268        // `SqlScalarType::String` and then to the desired type.
1269        let expr = cast_inner(&from, &SqlScalarType::String, expr)?;
1270        cast_inner(&SqlScalarType::String, to, expr)
1271    } else {
1272        // Standard cast.
1273        cast_inner(&from, to, expr)
1274    }
1275}
1276
1277/// Reports whether it is possible to perform a cast from the specified types.
1278pub fn can_cast(
1279    ecx: &ExprContext,
1280    ccx: CastContext,
1281    cast_from: &SqlScalarType,
1282    cast_to: &SqlScalarType,
1283) -> bool {
1284    get_cast(ecx, ccx, cast_from, cast_to).is_some()
1285}