Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #8613: getting null when null is concatenated with string

BUG #8613: getting null when null is concatenated with string

From
rajasekhar5c1@gmail.com
Date:
The following bug has been logged on the website:

Bug reference:      8613
Logged by:          rajasekhar5c1
Email address:      rajasekhar5c1@gmail.com
PostgreSQL version: 9.2.1
Operating system:   linux
Description:

when i fire below query on one of our servers


     select null || 'query' ,'|'


the result is null nothing is displayed


when i fire the same query on another server which has same version of
postgresql server


i am getting result as


 query


postgres version on both servers are


1) EnterpriseDB 9.2.1.3 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-52), 32-bit 2) EnterpriseDB 9.2.1.3 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat
4.1.2-52), 64-bit


why this different behavior?

Re: BUG #8613: getting null when null is concatenated with string

From
Kevin Grittner
Date:
"rajasekhar5c1@gmail.com" <rajasekhar5c1@gmail.com> wrote:=0A=0A> when i fi=
re below query on one of our servers=0A>=0A>=A0=A0=A0=A0 select null || 'qu=
ery' ,'|'=0A>=0A> the result is null nothing is displayed=0A>=0A> when i fi=
re the same query on another server which has same=0A> version of postgresq=
l server=0A>=0A> i am getting result as=0A>=0A> query=0A=0AThis is not a bu=
g.=0A=0AFor community PostgreSQL, NULL is the correct result.=A0 It is what=
=0Ais required by the SQL standard, since concatenating a known value=0Awit=
h an unknown value yields an unknown result.=0A=0A> postgres version on bot=
h servers are=0A>=0A> 1) EnterpriseDB 9.2.1.3 on i686-pc-linux-gnu, compile=
d by gcc (GCC) 4.1.2=0A> 20080704 (Red Hat 4.1.2-52), 32-bit 2) EnterpriseD=
B 9.2.1.3 on=0A> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 2008=
0704 (Red Hat=0A> 4.1.2-52), 64-bit=0A=0AHowever, you are not running commu=
nity PostgreSQL, you are running=0AEDB PostgreSQL Plus Advanced Server (PPA=
S), which has an Oracle=0Acompatibility mode which treats an empty string a=
nd NULL as=0Amore-or-less the same thing.=A0 Your best bet for such an issu=
e would=0Abe to open a support ticket with EDB.=0A=0A--=0AKevin Grittner=0A=
EDB: http://www.enterprisedb.com=0AThe Enterprise PostgreSQL Company

Re: BUG #8613: getting null when null is concatenated with string

From
Tom Lane
Date:
rajasekhar5c1@gmail.com writes:
> when i fire below query on one of our servers
>      select null || 'query' ,'|'
> the result is null nothing is displayed

That's the expected result.

> when i fire the same query on another server which has same version of
> postgresql server i am getting result as
>  query

Interesting.  I think this is what would be expected in Oracle, which
thinks that null and empty string are the same thing.

> postgres version on both servers are
> 1) EnterpriseDB 9.2.1.3 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2
> 20080704 (Red Hat 4.1.2-52), 32-bit 2) EnterpriseDB 9.2.1.3 on
> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat
> 4.1.2-52), 64-bit

Well, I think you'd need to take that up with EnterpriseDB.  I don't
know anything about what they do to Postgres' null handling, but it
wouldn't surprise me to hear that they've kluged it up to more nearly
match Oracle's non-standards-compliant behavior.

            regards, tom lane