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 kumar Email Updates:
Status: Verified Impact on me:
None 
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
Triage: Triaged: D2 (Serious)

[21 Feb 2008 11:29] santhinesh kumar
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';
[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 kumar
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.