| Bug #34720 | Looks like the CONCAT function is truncating the output | ||
|---|---|---|---|
| Submitted: | 21 Feb 2008 11:29 | Modified: | 21 Feb 2008 17:27 |
| Reporter: | Santhinesh Nagendran | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.1.22-RC, 5.0, 5.1, 6.0 BK | OS: | Linux (Linux database-112-2-1.db.overlay.intra 2.6.18-53.1.6.el5 #1 SMP Wed Jan 23 11:28:47 EST 2008 x86_64) |
| Assigned to: | CPU Architecture: | Any | |
[21 Feb 2008 13:55]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior with test data. Could you please provide output of SHOW CREATE TABLE metrics and SELECT * FROM metrics with where condition same as in problem query.
[21 Feb 2008 14:55]
Santhinesh Nagendran
Thanks for your quick response . Here are the create statement and the output you requested. show create table metrics; +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | metrics | CREATE TABLE `metrics` ( `id` int(11) NOT NULL AUTO_INCREMENT, `metric_name` varchar(255) DEFAULT NULL, `value` float DEFAULT '0', `created_at` datetime DEFAULT NULL, `metrics_for` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=477 DEFAULT CHARSET=utf8 | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) select * from metrics m1, metrics m2 where m1.metric_name='referrals_all_time' and m2.metric_name = 'overlays_created_all_time' and m1.metrics_for = m2.metrics_for order by m1.metrics_for; +-----+--------------------+-------+---------------------+-------------+-----+---------------------------+-------+---------------------+-------------+ | id | metric_name | value | created_at | metrics_for | id | metric_name | value | created_at | metrics_for | +-----+--------------------+-------+---------------------+-------------+-----+---------------------------+-------+---------------------+-------------+ | 344 | referrals_all_time | 344 | 2008-02-15 00:00:00 | 2008-02-14 | 353 | overlays_created_all_time | 398 | 2008-02-15 00:00:00 | 2008-02-14 | | 361 | referrals_all_time | 691 | 2008-02-16 00:00:00 | 2008-02-15 | 370 | overlays_created_all_time | 510 | 2008-02-16 00:00:00 | 2008-02-15 | | 378 | referrals_all_time | 826 | 2008-02-17 00:00:00 | 2008-02-16 | 387 | overlays_created_all_time | 546 | 2008-02-17 00:00:00 | 2008-02-16 | | 395 | referrals_all_time | 987 | 2008-02-18 00:00:00 | 2008-02-17 | 404 | overlays_created_all_time | 591 | 2008-02-18 00:00:00 | 2008-02-17 | | 412 | referrals_all_time | 1119 | 2008-02-19 00:00:00 | 2008-02-18 | 421 | overlays_created_all_time | 646 | 2008-02-19 00:00:00 | 2008-02-18 | | 446 | referrals_all_time | 1307 | 2008-02-20 00:00:00 | 2008-02-19 | 455 | overlays_created_all_time | 710 | 2008-02-20 00:00:00 | 2008-02-19 | | 463 | referrals_all_time | 1586 | 2008-02-21 00:00:00 | 2008-02-20 | 472 | overlays_created_all_time | 754 | 2008-02-21 00:00:00 | 2008-02-20 | +-----+--------------------+-------+---------------------+-------------+-----+---------------------------+-------+---------------------+-------------+ 7 rows in set (0.00 sec)
[21 Feb 2008 17:27]
Sveta Smirnova
Thank you for the feedback.
Verified as described.
To repeat paste:
CREATE TABLE `metrics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`metric_name` varchar(255) DEFAULT NULL,
`value` float DEFAULT '0',
`created_at` datetime DEFAULT NULL,
`metrics_for` date DEFAULT NULL,
PRIMARY KEY (`id`)
);
insert into metrics values(344, 'referrals_all_time', 344, '2008-02-15 00:00:00', '2008-02-14'),
(353, 'overlays_created_all_time', 398, '2008-02-15 00:00:00', '2008-02-14');
select concat('update metrics set value = ',m1.value / m2.value ,' where metrics_for = \'',date_format(m1.metrics_for,'%Y-%m-%d'),'\';123')
from metrics m1, metrics m2 where m1.metric_name='referrals_all_time'
and m2.metric_name = 'overlays_created_all_time'
and m1.metrics_for = m2.metrics_for
order by m1.metrics_for;
Workaround: remove ORDER BY
[27 Oct 2008 16:37]
Vasile Ceteras
this is a very serious issue, I use concat a lot in my job, and I would not upgrade to 5.1 if this is not fixed.
Description: The CONCAT function does not work in the below query How to repeat: ### Looks like the 3 last chars of the concat output are being truncated: select concat('update metrics set value = ',m1.value / m2.value ,' where metrics_for = \'',date_format(m1.metrics_for,'%Y-%m-%d'),'\';123') from metrics m1, metrics m2 where m1.metric_name='referrals_all_time' and m2.metric_name = 'overlays_created_all_time' and m1.metrics_for = m2.metrics_for order by m1.metrics_for; Output: (the 123 is not printed) update metrics set value = 0.8643216080402 where metrics_for = '2008-02-14'; update metrics set value = 1.3549019607843 where metrics_for = '2008-02-15'; update metrics set value = 1.5128205128205 where metrics_for = '2008-02-16'; update metrics set value = 1.6700507614213 where metrics_for = '2008-02-17'; update metrics set value = 1.7321981424149 where metrics_for = '2008-02-18'; update metrics set value = 1.8382559774965 where metrics_for = '2008-02-19';