public inbox for [email protected]  

help / color / mirror / Atom feed

From: jian he <[email protected]>
To: Vik Fearing <[email protected]>
Cc: Corey Huinker <[email protected]>
Cc: Isaac Morland <[email protected]>
Cc: [email protected]
Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions
Date: Thu, 24 Jul 2025 21:44:22 +0800
Message-ID: <CACJufxHhUD4u7PXUzT1T6hM9ZcQyj3eTyLMQmzREj+_5ca0_ug@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com>
	<CAMsGm5dpfm2PHL8XZvC-JSd+UPkgx3rpReUA=G=4+rUCH+Ntcw@mail.gmail.com>
	<CADkLM=eD_S8mGhPfu5+hXXvXgR0-cxGpGd9dgPzD+nCuO7HFaQ@mail.gmail.com>
	<CACJufxHCMzrHOW=wRe8L30rMhB3sjwAv1LE928Fa7sxMu1Tx-g@mail.gmail.com>
	<[email protected]>
	<CACJufxGRAnwJzu7nMq4ZP=yqa1Sz=qR+mR1TmY0aCDjJoJRRtg@mail.gmail.com>
	<[email protected]>

On Tue, Jul 22, 2025 at 8:26 PM Vik Fearing <[email protected]> wrote:
>
>
> On 22/07/2025 12:19, jian he wrote:
> > On Tue, Jul 22, 2025 at 2:45 PM Vik Fearing <[email protected]> wrote:
> >> It was accepted into the standard after 2023 was released.  I am the
> >> author of this change in the standard, so feel free to ask me anything
> >> you're unsure about.
> >>
> > is the generally syntax as mentioned in this thread:
> > CAST(source_expression AS target_type DEFAULT default_expression ON ERROR)
> >
> > if so, what's the restriction of default_expression?
>
>
> The actual syntax is:
>
>
> <cast specification> ::=
>      CAST <left paren>
>          <cast operand> AS <cast target>
>          [ FORMAT <cast template> ]
>          [ <cast error behavior> ON CONVERSION ERROR ]
>          <right paren>
>
> "CONVERSION" is probably a noise word, but it is there because A) Oracle
> wanted it there, and B) it makes sense because if the <cast error
> behavior> fails, that is still a failure of the entire CAST.
>
>
> The <cast error behavior> is:
>
>
> <cast error behavior> ::=
>      ERROR
>    | NULL
>    | DEFAULT <value expression>
>
>

hi.

just want to confirm my understanding of ``[ FORMAT <cast template> ]``.

SELECT CAST('2022-13-32' AS DATE FORMAT 'YYYY-MM-DD' DEFAULT NULL ON
CONVERSION ERROR);
will return NULL.
because  ``SELECT to_date('2022-13-32', 'YYYY-MM-DD');``
will error out, so the above query will fall back to the DEFAULT
expression evaluation.

view thread (75+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions
  In-Reply-To: <CACJufxHhUD4u7PXUzT1T6hM9ZcQyj3eTyLMQmzREj+_5ca0_ug@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox