output truncated with ELT when using DISTINCT

Hello,

when using both DISTINCT and ELT in a query, I experience problems :
some strings returned by ELT are truncated.

tested on :
MySQL 4.0.17 + Linux_thread on FreeBSD 4.8
MySQL 3.23.38 on Linux
MySQL 4.0.16 on MacOS X 10.3.2

-- beginning of script --
CREATE TABLE `trunc` (
`id` int(11) NOT NULL auto_increment,
`myfield` int(11) NOT NULL default '1',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM ;

INSERT INTO `trunc` (`id`, `myfield`) VALUES (3, 3);
INSERT INTO `trunc` (`id`, `myfield`) VALUES (4, 3);
INSERT INTO `trunc` (`id`, `myfield`) VALUES (7, 2);
INSERT INTO `trunc` (`id`, `myfield`) VALUES (8, 1);
INSERT INTO `trunc` (`id`, `myfield`) VALUES (16, 1);

SELECT DISTINCT id,
ELT(myfield,'12345678901234567890','123456','ABCDEFGHIJ') AS outp
FROM trunc
ORDER BY outp ASC;
-- end of script --

this returns :

+----+------------+
| id | outp |
+----+------------+
| 7 | 123456 |
| 8 | 1234567890 |
| 16 | 1234567890 |
| 3 | ABCDEFGHIJ |
| 4 | ABCDEFGHIJ |
+----+------------+

and should return :

+----+----------------------+
| id | outp |
+----+----------------------+
| 7 | 123456 |
| 8 | 12345678901234567890 |
| 16 | 12345678901234567890 |
| 3 | ABCDEFGHIJ |
| 4 | ABCDEFGHIJ |
+----+----------------------+

Is there a clean work around ?

Patrick Proniewski
--
je cherche un poste d'admin-sys Mac/UNIX
(ou une jeune et jolie femme riche)
http://patpro.net/cv.php


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

output truncated with ELT when using DISTINCT

Hi Patrick,

Patrick Proniewski writes:
> Hello,
>
> when using both DISTINCT and ELT in a query, I experience problems :
> some strings returned by ELT are truncated.
>
> tested on :
> MySQL 4.0.17 + Linux_thread on FreeBSD 4.8
> MySQL 3.23.38 on Linux
> MySQL 4.0.16 on MacOS X 10.3.2


Thanks for the bug report!

> Is there a clean work around ?

Seems to work in 4.1, but 4.0 fails. I will enter this into
the bugs database, it should be fixed by the next release.

Regards,

- Jani

For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Jani Tolonen
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

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