List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:February 22 2011 12:08pm
Subject:Re: contact gives empty result
View as plain text  
Hi Almar, all!


Almar van Pel wrote:
> Hello all,
> 
>  
> 
> I'm trying to get a TEXT field updated with its own content and an extra
> string by using concat. The query looks something like this:
> 
>  
> 
> update field_comment set field_comment = concat(field_comment, '\n
> my_new_string');
> 
>  
> 
> I've noticed that in this case the update doesn't work when the content of
> the field is empty.  I tried setting up a testcase, and as long as the
> textfield is NULL concat doesn't return anything. Is this 'as designed' or a
> bug? Should it work as designed, would anyone know another easy way without
> setting a byte first?

You have not understood the concept of NULL in SQL:
NULL does not mean "empty", it means "unknown".

If you concat some text to an unknown text, what should the result be?
It must be unknown again, as the start of the result is still unknown.

In general, in SQL all operations that get (at least) one NULL operand
again return NULL; the only exception that comes to my mind is the "IS
NULL" predicate.
Especially, the comparison of two NULL values does not return TRUE,
rather it returns NULL (because it cannot be determined whether those
two "unknown" operands both have the same value).

So if you took NULL to represent an empty string, you made a wrong
design assumption and should change it: Explicitly set those fields to
empty strings ''.


HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bruehe@stripped
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603

Thread
contact gives empty resultAlmar van Pel 22 Feb
  • Re: contact gives empty resultJoerg Bruehe22 Feb
    • Re: contact gives empty resultJaime Crespo Rinc√≥n22 Feb
      • RE: contact gives empty resultAlmar van Pel 22 Feb