Thread

1. concenation of strings and null values

   Tino Wildenhain <tino@wildenhain.de> — 2003-10-07T08:40:49Z

   Hi,
   
   SELECT 'abc'::text || 'def'::text;
   
   returns 'abcdef' as we know.
   
   SELECT 'abc'::text || ''::text;
   
   returns 'abc'
   
   SELECT 'abc'::text || null::text;
   
   returns null
   
   The last example looks like a bug,
   but if it is intentionally so, its
   at least very annoying and inconvenient.
   
   Can someone enlighten me if this is
   in accordance to some not so transparent
   rules of SQL92 or '99 and if so, how
   to work around this?
   
   Regards
   Tino Wildenhain
   
   
   

2. Re: concenation of strings and null values

   Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-10-07T09:00:40Z

   On Tue, 7 Oct 2003, Tino Wildenhain wrote:
   
   > Hi,
   > 
   > SELECT 'abc'::text || 'def'::text;
   > 
   > returns 'abcdef' as we know.
   > 
   > SELECT 'abc'::text || ''::text;
   > 
   > returns 'abc'
   > 
   > SELECT 'abc'::text || null::text;
   > 
   > returns null
   > 
   > The last example looks like a bug,
   > but if it is intentionally so, its
   > at least very annoying and inconvenient.
   
   Looks correct to me. Sure, might be annoying but we'd rather have correctness I
   think :)
   
   What you're asking for the equivalent of is:
   
   	something1 || ???? || something3 to equate to: something1something3
   
   which you obviously can't say in the general case because you don't know if
   ???? really is an empty string or in fact something much more significant as in
   the example:
   
   	'do' || 'not' || 'concatinate'
   
   Therefore the whole expression should evaluate to null otherwise you'd never
   know you didn't actually know if you should or shouldn't concatinate.
   
   > 
   > Can someone enlighten me if this is
   > in accordance to some not so transparent
   > rules of SQL92 or '99 and if so, how
   > to work around this?
   
   In this instance use coalesce() as in: SELECT 'abc' || coalesce(null,'');
   
   
   -- 
   Nigel J. Andrews
   
   
   

3. Re: concenation of strings and null values

   Dieter Fischer <dieter.fischer@grid-it.ch> — 2003-10-07T09:20:14Z

   Hello
   
   > SELECT 'abc'::text || null::text;
   >
   > The last example looks like a bug,
   > but if it is intentionally so, its
   > at least very annoying and inconvenient.
   
   NULL means here UNKOWN. You cannot add or concatenate something to an
   undefined Value.
   
   Regards
   
   Dieter
   
   
   

4. Re: concenation of strings and null values

   Tino Wildenhain <tino@wildenhain.de> — 2003-10-07T09:25:29Z

   Nigel J. Andrews wrote:
   > On Tue, 7 Oct 2003, Tino Wildenhain wrote:
   > 
   > 
   >>Hi,
   >>
   >>SELECT 'abc'::text || 'def'::text;
   >>
   >>returns 'abcdef' as we know.
   >>
   >>SELECT 'abc'::text || ''::text;
   >>
   >>returns 'abc'
   >>
   >>SELECT 'abc'::text || null::text;
   >>
   >>returns null
   >>
   >>The last example looks like a bug,
   >>but if it is intentionally so, its
   >>at least very annoying and inconvenient.
   > 
   > 
   > Looks correct to me. Sure, might be annoying but we'd rather have correctness I
   > think :)
   > 
   > What you're asking for the equivalent of is:
   > 
   > 	something1 || ???? || something3 to equate to: something1something3
   > 
   > which you obviously can't say in the general case because you don't know if
   > ???? really is an empty string or in fact something much more significant as in
   > the example:
   
   not really, this behavoir is only with null values because all other
   values are casted to text. So null (or none, or undefined ...) in most
   languages map to "" (Empty string) when concenated.
   It may be however the current behavior is according to SQL9[29],
   since 1+null is null too (is a case in aggregates, isn't it?)
   but I dont know of a human readable reference of the SQL specs.
   
   
   > 
   > 	'do' || 'not' || 'concatinate'
   > 
   > Therefore the whole expression should evaluate to null otherwise you'd never
   > know you didn't actually know if you should or shouldn't concatinate.
   > 
   > 
   >>Can someone enlighten me if this is
   >>in accordance to some not so transparent
   >>rules of SQL92 or '99 and if so, how
   >>to work around this?
   > 
   > 
   > In this instance use coalesce() as in: SELECT 'abc' || coalesce(null,'');
   
   This works ;) Thanks :-)
   
   
   

5. Re: concenation of strings and null values

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-10-07T10:28:43Z

   Tino Wildenhain <tino@wildenhain.de> writes:
   > Can someone enlighten me if this is
   > in accordance to some not so transparent
   > rules of SQL92 or '99 and if so, how
   > to work around this?
   
   It is per spec: in SQL92 6.13 <string value expression>:
   
            2) If <concatenation> is specified, then let S1 and S2 be the re-
               sult of the <character value expression> and <character factor>,
               respectively.
   
               Case:
   
               a) If either S1 or S2 is the null value, then the result of the
                 <concatenation> is the null value.
   
   
   			regards, tom lane
   
   

6. Re: concenation of strings and null values

   Greg Stark <gsstark@mit.edu> — 2003-10-07T14:58:09Z

   Tino Wildenhain <tino@wildenhain.de> writes:
   
   > So null (or none, or undefined ...) in most
   > languages map to "" (Empty string) 
   
   Not in SQL. In SQL null means "unknown". If it's unknown then it's still
   unknown when there's something attached to the beginning of it.
   
   -- 
   greg
   
   
   

7. Re: concenation of strings and null values

   Gaetano Mendola <mendola@bigfoot.com> — 2003-10-07T21:33:00Z

   Dieter Fischer (grid-it) wrote:
   
   > Hello
   > 
   > 
   >>SELECT 'abc'::text || null::text;
   >>
   >>The last example looks like a bug,
   >>but if it is intentionally so, its
   >>at least very annoying and inconvenient.
   > 
   > 
   > NULL means here UNKOWN. You cannot add or concatenate something to an
   > undefined Value.
   
   
   You can but the result is UNKNOWN.
   
   Regards
   Gaetano Mendola