[Fwd: Another funky bug (with elt())]

Simon,

I've forwarded this to the proper mailing list. Please use it
or even better http://bugs.mysql.com for reporting bugs in the future.


-----Forwarded Message-----
From: Simon Kirby
To: internals@lists.mysql.com
Subject: Another funky bug (with elt())
Date: Sun, 25 Jan 2004 15:14:26 -0800

[4.0.17, 3.23.58-Max]

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.

In any event, the following test code returns:
+-----------------------------------+---------------+
| concat(elt(1,test_a.name),"Test") | name |
+-----------------------------------+---------------+
| Stil | Still testing |
| Stil | Still testing |
| Test | Testing |
| Test | Testing |
+-----------------------------------+---------------+

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

Without the "concat()", the elt() function appears to return empty
strings. If the "order by" is left off, if the second table is removed,
or if either table has less than two rows, the problem does not occur.


To reproduce:
------------------------------------------------------------ -------------

create table test_a (
name varchar(32)
);

create table test_b (
name varchar(32)
);

insert into test_a (name) values ('Testing');
insert into test_a (name) values ('Still testing');

insert into test_b (name) values ('Testing');
insert into test_b (name) values ('Still testing');

select
concat(elt(1,test_a.name),"Test"),
test_a.name
from
test_a,
test_b
order by test_a.name;

drop table test_a;
drop table test_b;

------------------------------------------------------------ -------------

Cheers,

Simon-
--
Peter Zaitsev, Senior Support Engineer
MySQL AB, www.mysql.com

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


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [Fwd: Another funky bug (with elt())]

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/


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org