2 messages in com.mysql.lists.bugsRe: [Fwd: Another funky bug (with elt...
FromSent OnAttachments
Peter Zaitsev26 Jan 2004 16:14 
Dean Ellis27 Jan 2004 09:22 
Subject:Re: [Fwd: Another funky bug (with elt())]
From:Dean Ellis (de@mysql.com)
Date:01/27/2004 09:22:28 AM
List:com.mysql.lists.bugs

Simon,

Found another bug while attempting to make varchar fields containing NULL return an empty rather than NULL, using (elt(isnull(field)+1,field)). If there is a less retarded way of doing this, feel free to point it out.

There was a bug in 4.0.17 with the ELT() function, but this has been corrected in the development tree and will be available in our next release.

That bug did not specifically relate to your test case, however the symptoms are similar and I can verify that your test case produces correct results with 4.0.18.

As for another way of making varchar columns return an empty string rather than NULL, here are a couple of better ways to do it:

SELECT IFNULL(column,'') FROM table; SELECT COALESCE(column,'') FROM table;

Commenting on your test:

...when it should return: +-----------------------------------+---------------+ | concat(elt(1,test_a.name),"Test") | name | +-----------------------------------+---------------+ | Still testing | Still testing | | Still testing | Still testing | | Testing | Testing | | Testing | Testing | +-----------------------------------+---------------+

Actually, your test case should (and does in 4.0.18) produce:

+-----------------------------------+---------------+ | concat(elt(1,test_a.name),"Test") | name | +-----------------------------------+---------------+ | Still testingTest | Still testing | | Still testingTest | Still testing | | TestingTest | Testing | | TestingTest | Testing | +-----------------------------------+---------------+

Best regards,

-- Dean Ellis, Support Engineer & Software Developer MySQL AB, www.mysql.com

Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/