Thread: string concatenator || and NULL values

============================================================================                       POSTGRESQL BUG
REPORTTEMPLATE 
============================================================================


Your name        :    Roberto João Lopes Garcia
Your email address    :  roberto@mha.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 NULL values
the result is an enpty string. Please see example:

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

cd=#
cd=# \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 
Índices:   "i_rev_unique" único, 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=# 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=#
cd=#  select 'ERROR: ' || nm_arq || '  ' || nr_rev  || '  ' ||  data  as "WHITHOUT FIELD sql_data" from cd_rev where
nm_arq='C0004511'; WHITHOUT FIELD sql_data 
------------------------------ERROR: C0004511  0  /04/2001
(1 registro)

cd=#
cd=#
cd=#  select 'ERROR: ' || nm_arq || '  ' || nr_rev  || '  ' ||  data  || '  ' || sql_data   as "WHITH FIELD sql_data"
fromcd_rev where nm_arq='C0004511';WHITH FIELD sql_data 
----------------------

(1 registro)

cd=#
cd=# 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-18A9909901 | 1      | 24/04/1998
|1998-04-24A9909901 | 2      | 27/07/1998 | 1998-07-27C0000003 | ERR    | 09/05/2001 | 2001-05-09C0000101 | 0      |
07/08/2000| 2000-08-07 
(5 registros)


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

cd=#
cd=#


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:
---------------------------------------------------------------------

On Thu, Jul 01, 2004 at 16:42:29 -0300,
  Roberto João Lopes Garcia <roberto@mha.com.br> wrote:
>
> When I use string concatenator || and there are fields of type date whith NULL values
> the result is an enpty string. Please see example:

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.

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

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

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."