Thread

string concatenator || and NULL values

Roberto João Lopes Garcia <roberto@mha.com.br> — 2004-07-01T19:42:29Z

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


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"  from cd_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-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=#
cd=#
cd=#  select 'OK: ' || nm_arq || '  ' || nr_rev  || '  ' ||  data  || '  ' || sql_data  as "WHITH FIELD sql_data"  from cd_rev where 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:
---------------------------------------------------------------------