From: Joerg Bruehe
Date: February 22 2011 12:08pm
Subject: Re: contact gives empty result
List-Archive: http://lists.mysql.com/mysql/224465
Message-Id: <4D63A727.4020802@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

Hi Almar, all!


Almar van Pel wrote:
> Hello all,
>=20
> =20
>=20
> I'm trying to get a TEXT field updated with its own content and an extr=
a
> string by using concat. The query looks something like this:
>=20
> =20
>=20
> update field_comment set field_comment =3D concat(field_comment, '\n
> my_new_string');
>=20
> =20
>=20
> 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 wit=
hout
> 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=F6rg

--=20
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