Re: bug or feature, || -operator and NULLs

On Wed, Oct 18, 2006 at 02:28:50PM +0200, Andreas Joseph Krogh wrote:
> On Wednesday 18 October 2006 14:15, Csaba Nagy wrote:
> > > The following query returns NULL in PG:
> > > SELECT NULL || 'fisk';
> > >
> > > But in Oracle, it returns 'fisk':
> > > SELECT NULL || 'fisk' FROM DUAL;
> > >
> > > The latter seems more logical...

> How many times do you *really* want to get the "not known" answer here instead
> of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'?

In general, if you pass a NULL to a function, you get a NULL return. An
operator is just a function call.

IIRC, this works on oracle too:

SELECT NULL = '';

returns true. On postgresql it return null (sql standard).

By following your suggestion we would get the following oddity:

SELECT NULL = '', NULL || 'fisk' = '' || 'fisk';

We would return NULL for the first and true for the second. Surely
that's not logical?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.