contact gives empty result

------=_NextPart_000_00FC_01CBD28E.87D71580
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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?



Below the testcase:



CREATE TABLE memo_test (id INT (1) UNSIGNED DEFAULT '0' NOT NULL, comments
TEXT, PRIMARY KEY(id)) TYPE = MyISAM;

INSERT INTO memo_test (id, comments) VALUES (1, NULL);

INSERT INTO memo_test (id, comments) VALUES (2, 'Hello');



select concat(comments, 'Does not work') from memo_test



+-----------------------------------+

| concat(comments, 'Does not work') |

+-----------------------------------+

| NULL |

| HelloDoes not work |

+-----------------------------------+



select concat('Something', comments, 'Does not work') from memo_test;

+------------------------------------------------+

| concat('Something', comments, 'Does not work') |

+------------------------------------------------+

| NULL |

| SomethingHelloDoes not work |

+------------------------------------------------+



Best regards,



Almar van Pel

www.makeweb.nl




------=_NextPart_000_00FC_01CBD28E.87D71580--

Re: contact gives empty result

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örg

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: contact gives empty result

2011/2/22 Joerg Bruehe :
> You have not understood the concept of NULL in SQL:
> NULL does not mean "empty", it means "unknown".
[...]

Apart form fully agreeing with Joerg, just a tip:
you can use the the IFNULL() operand as a workaround:


Anyway, you should get rid of misplaced NULL fields, as they also
affect performance.

--
Jaime Crespo
MySQL & Java Instructor
Software Developer
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: contact gives empty result

Hi Jaime, Joerg and All,

Thanks for the explaining. In all those years I've never actually seen =
this, quite surprising actually :). In this case the comments field was =
empty and indeed not set. As it could be filled during the proces I'll =
be adding an empty string to the field. I still need to update it with =
additional text.

Best regards,

Almar van Pel
www.makeweb.nl


-----Original Message-----
From: Jaime Crespo Rincón [mailto:jcrespo@warp.es]=20
Sent: dinsdag 22 februari 2011 13:20
To: Joerg Bruehe
Cc: mysql@lists.mysql.com; Almar van Pel
Subject: Re: contact gives empty result

2011/2/22 Joerg Bruehe :
> You have not understood the concept of NULL in SQL:
> NULL does not mean "empty", it means "unknown".
[...]

Apart form fully agreeing with Joerg, just a tip:
you can use the the IFNULL() operand as a workaround:
ion_ifnull>

Anyway, you should get rid of misplaced NULL fields, as they also
affect performance.

--=20
Jaime Crespo
MySQL & Java Instructor
Software Developer
Warp Networks


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dalmar@makeweb.nl


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg