Обсуждение: Minor difference in behavior between +/-
SELECT NULL - date '2023-01-05';
-> result is null
SELECT NULL + date '2023-01-05';
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
I would expect both of those to be null.
Thanks,
Ryan
On Tuesday, January 24, 2023, Ryan Murphy <ryanmurf@gmail.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.
David J.
At Wed, 25 Jan 2023 07:12:18 -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote in > On Tuesday, January 24, 2023, Ryan Murphy <ryanmurf@gmail.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