=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
POSTGRESQL BUG REPORT TEMPLATE
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D


Your name : Roberto Jo=E3o Lopes Garcia
Your email address : roberto (AT) mha (DOT) com.br


System Configuration
---------------------
Architecture (example: Intel Pentium) : Dual Intel Pentium III

Operating System (example: Linux 2.4.18) : 2.4.17

PostgreSQL version (example: PostgreSQL-7.4.1): PostgreSQL-7.4.1

Compiler used (example: gcc 2.95.2) : 2.95.3


Please enter a FULL description of your problem:
------------------------------------------------

When I use string concatenator || and there are fields of type date whith N=
ULL values
the result is an enpty string. Please see example:

cd=3D# select version();
version
---------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 registro)

cd=3D#
cd=3D# \d cd_rev
Tabela "public.cd_rev"
Coluna | Tipo | Modificadores
-----------+---------------+------------------
nr_proj | character(5) |
area | character(3) |
nm_arq | character(8) |
nr_rev | character(3) |
titulo | character(52) |
resp | character(10) |
aprov | character(10) |
data | character(10) |
sql_data | date |
rev_nr | smallint |
descricao | text | default ''::text
=CDndices:
"i_rev_unique" =FAnico, btree (nm_arq, nr_rev)
"i_rev_aprov" btree (aprov)
"i_rev_area" btree (area)
"i_rev_data" btree (data)
"i_rev_nm_arq" btree (nm_arq)
"i_rev_nr_proj" btree (nr_proj)
"i_rev_nr_rev" btree (nr_rev)
"i_rev_resp" btree (resp)
"i_rev_rev_nr" btree (rev_nr)
"i_rev_sql_data" btree (sql_data)

cd=3D# select nm_arq, nr_rev, data, sql_data from cd_rev where sql_data is =
null limit 5;
nm_arq | nr_rev | data | sql_data
----------+--------+------------+----------
C0004511 | 0 | /04/2001 |
C0004512 | 0 | /04/2001 |
C0004513 | 0 | /04/2001 |
C0004514 | 0 | /04/2001 |
C0004515 | 0 | /04/2001 |
(5 registros)


cd=3D#
cd=3D# select 'ERROR: ' || nm_arq || ' ' || nr_rev || ' ' || data as =
"WHITHOUT FIELD sql_data" from cd_rev where nm_arq=3D'C0004511';
WHITHOUT FIELD sql_data
------------------------------
ERROR: C0004511 0 /04/2001
(1 registro)

cd=3D#
cd=3D#
cd=3D# select 'ERROR: ' || nm_arq || ' ' || nr_rev || ' ' || data || =
' ' || sql_data as "WHITH FIELD sql_data" from cd_rev where nm_arq=3D'C=
0004511';
WHITH FIELD sql_data
----------------------

(1 registro)

cd=3D#
cd=3D# select nm_arq, nr_rev, data, sql_data from cd_rev where sql_data is =
not null limit 5;
nm_arq | nr_rev | data | sql_data
----------+--------+------------+------------
A9909901 | 0 | 18/12/1997 | 1997-12-18
A9909901 | 1 | 24/04/1998 | 1998-04-24
A9909901 | 2 | 27/07/1998 | 1998-07-27
C0000003 | ERR | 09/05/2001 | 2001-05-09
C0000101 | 0 | 07/08/2000 | 2000-08-07
(5 registros)


cd=3D#
cd=3D#
cd=3D# select 'OK: ' || nm_arq || ' ' || nr_rev || ' ' || data || ' =
' || sql_data as "WHITH FIELD sql_data" from cd_rev where nm_arq=3D'C0000=
101';
WHITH FIELD sql_data
-----------------------------------------
OK: C0000101 0 07/08/2000 2000-08-07
(1 registro)

cd=3D#
cd=3D#


Any questions, please e-mail me

Roberto

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

This problem occur in any table whith NULL date type.




If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Thu, Jul 01, 2004 at 16:42:29 -0300,
Roberto João Lopes Garcia <roberto (AT) mha (DOT) com.br> wrote:
This isn't a bug. If either operand of || is NULL, then the result is NULL.
You probably want to use the coalesce function to change NULLs to empty
strings.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Thu, 1 Jul 2004, Roberto [iso-8859-1] Jo=E3o Lopes Garcia wrote:

The result is NULL. This is the correct result.

"If either S1 or S2 is the null value, then the result of the
<concatenation> is the null value."

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)