From: | Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> |
---|---|
To: | david(dot)g(dot)johnston(at)gmail(dot)com |
Cc: | ryanmurf(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Minor difference in behavior between +/- |
Date: | 2023-01-26 02:43:06 |
Message-ID: | 20230126.114306.402487386804660943.horikyota.ntt@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
At Wed, 25 Jan 2023 07:12:18 -0700, "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> wrote in
> On Tuesday, January 24, 2023, Ryan Murphy <ryanmurf(at)gmail(dot)com> wrote:
>
> > SELECT NULL - date '2023-01-05';
> > -> result is null
> > SELECT NULL + date '2023-01-05';
> > -> result is [42725] ERROR: operator is not unique: unknown + date Hint:
> > Could not choose a best candidate operator. You might need to add explicit
> > type casts. Position: 13
> >
> >
> There is no bug here.
>
>
> > I would expect both of those to be null.
> >
>
> I get why you expect that but we expend little to no effort to handle null
> in a type agnostic way. Type inference has to happen first and regardless
> of whether the literal is null. Type inference failed in the + case but
> not the - case. That is just happenstance of what operators happen to
> exist in the system.
In a bit more deatil, the type inference mechanism assumes the null as
a date since the other input is a date. In this case, the operator
"date - date" exists but "date + date" doesn't.
I'm not sure whether the SQL standard defines a binary operator should
be "strict/or return null on null input", but it seems to to me that
we assume a case of non-strict binary operators, or maybe we simply
dont' care about the case of null operands.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-01-26 15:23:48 | Re: FW: Query execution failure |
Previous Message | Pete Storer | 2023-01-25 22:17:07 | FW: Query execution failure |