Обсуждение: Bug #604: string join problem

Erol Ozcan (erol@infotron.com.tr) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
string join problem

Long Description
I have a major problem with string joining in sql query in Postgresql 7.1.3. It is always return null string if any of
columnhas null value.
 
I attached some example code sources to explain the problem.

Sample Code
CREATE TABLE phone
(
  phn_id                 INT                NOT NULL PRIMARY KEY,
  phn_country            INT                NULL,
  phn_area               INT                NULL,
  phn_number             INT                NULL,
  phn_ext                INT                NULL
);
INSERT INTO phone VALUES( 30, 90, 216, 4921002, NULL );
SELECT '+'|| phn_country||'-'||phn_area||'-'||phn_number FROM phone WHERE phn_id=30;
    ?column?     
-----------------
 +90-216-4921002
(1 row)

SELECT '+'||phn_country||'-'||phn_area||'-'||phn_number||' ext:'||phn_ext FROM phone WHERE phn_id=30;
 ?column? 
----------
 
(1 row)


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> I have a major problem with string joining in sql query in Postgresql 7.1.3. It is always return null string if any
ofcolumn has null value. 

This is not a bug.  SQL92 6.13 general rule 2a saith:

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


Consider using something like

    COALESCE(col1,'') || COALESCE(col2,'') || ...

if you want to treat NULLs as empty strings.  Also consider whether you
shouldn't have stored the fields as empty strings in the first place.
NULL and empty string are not at all the same thing; if your data design
treats them as interchangeable then your design is broken, IMHO.

            regards, tom lane

Tom Lane wrote:

> pgsql-bugs@postgresql.org writes:
> > I have a major problem with string joining in sql query in Postgresql 7.1.3. It is always return null string if any
ofcolumn has null value. 
>
> This is not a bug.  SQL92 6.13 general rule 2a saith:
>
>             a) If either S1 or S2 is the null value, then the result of the
>               <concatenation> is the null value.
>
> Consider using something like
>
>         COALESCE(col1,'') || COALESCE(col2,'') || ...
>
> if you want to treat NULLs as empty strings.  Also consider whether you
> shouldn't have stored the fields as empty strings in the first place.
> NULL and empty string are not at all the same thing; if your data design
> treats them as interchangeable then your design is broken, IMHO.
>
>                         regards, tom lane

Thanks for the reply. COALESCE is working good, however,  I wrote a PL/pgSQL procedure to get more proper results.

Best Regards,
--
==+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+==
Erol Ozcan                    mailto:erol@infotron.com.tr
Software Engineer         http://www.infotron.com.tr
                                     Tel: +90-216-4921002
info(+)TRON  A.S                 Istanbul/Turkey
==+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+==