BUG #2131: SQL Query Bug ?

The following bug has been logged online:

Bug reference: 2131
Logged by: kenichi nakanishi
Email address: kenichi_nakanishi(at)fukuicsk(dot)co(dot)jp
PostgreSQL version: 8.1.x Japanese
Operating system: Windows 2000 Professional Japanese
Description: SQL Query Bug ?
Details:

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.
Could you check these things ?
Thank you.

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