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