Skip site navigation (1) Skip section navigation (2)

Re: BUG #2131: SQL Query Bug ?

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: kenichi nakanishi <kenichi_nakanishi(at)fukuicsk(dot)co(dot)jp>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2131: SQL Query Bug ?
Date: 2005-12-27 17:28:06
Message-ID: 20051227172806.GA70199@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-bugs
On Mon, Dec 26, 2005 at 03:47:36PM +0000, kenichi nakanishi wrote:
> I found something strange result when using a following sql sentence,
> "select xxx || ' / ' || yyyy || ' / ' || zzzz as aaa from TABLE",
> sometime I could get empty results.
> When using same scentence on linux platform, I could get correct results.
> So I think it's a bug on windows version.

Do the Linux and Windows platforms have the same data?  Might any
of the columns on the Windows system be NULL?  Concatenating anything
with NULL results in NULL, so that could be the problem.

test=> CREATE TABLE foo (col1 text, col2 text);
CREATE TABLE
test=> INSERT INTO foo (col1, col2) VALUES ('aaa', 'bbb');
INSERT 0 1
test=> INSERT INTO foo (col1, col2) VALUES ('ccc', NULL);
INSERT 0 1
test=> INSERT INTO foo (col1, col2) VALUES (NULL, 'ddd');
INSERT 0 1
test=> SELECT col1, col2, col1 || col2 FROM foo;
 col1 | col2 | ?column? 
------+------+----------
 aaa  | bbb  | aaabbb
 ccc  |      | 
      | ddd  | 
(3 rows)

If you want to treat NULL as an empty string then use COALESCE:

test=> SELECT col1, col2, COALESCE(col1, '') || COALESCE(col2, '') FROM foo;
 col1 | col2 | ?column? 
------+------+----------
 aaa  | bbb  | aaabbb
 ccc  |      | ccc
      | ddd  | ddd
(3 rows)

-- 
Michael Fuhr

In response to

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2005-12-27 17:36:38
Subject: Re: BUG #2128: unable to install win32 version of pgsql 8.1.1
Previous:From: Bruce MomjianDate: 2005-12-27 15:48:54
Subject: Re: BUG #2124: Error "relation with OID ... does not exist" when

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group