[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 <[email protected]>
To: [email protected]
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/[email protected]