MySQL Bugs: #17273: CONCAT() returning truncated results

Bug #17273	CONCAT() returning truncated results
Submitted:	9 Feb 2006 13:52	Modified:	9 Feb 2006 16:16
Reporter:	Philip Sbrogna	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.13	OS:	Linux (Suse 10 OSS ppc64)
Assigned to:		CPU Architecture:	Any

Description:
BACKGROUND
I have a query (with joins) that uses the expression CONCAT(x, ' - ', y) in the result set where x & y are fields of type LONGTEXT. The query also has an ORDER BY clause.

DETAILS
The problem is that the result string is truncated to the length of the middle literal. If I wrap the CONCAT() in a CONVERT(CONCAT(), CHAR(255))- the correct untruncated data is returned. The cause seems to be the ORDER BY clause though, if I remove the ORDER BY clause, the correct untruncated data is returned.

The query follows, line 002 is the problem expression. With the CONVERT, it works, without the CONCERT, the results of the CONCAT are truncated. Also, and I suspect this is the important part, if the ORDER BY clause is removed, the correct result is returned.

001 SELECT hAID.k AS k, 
002   CONVERT(CONCAT(hAEDATE.v, ' - ', hATITLE.v), CHAR(255)) AS v
003   FROM tbl_hns AS hSTITLE
004   INNER JOIN tbl_hns AS hAID
005   ON hAID.k LIKE 'CMS\_CONSULT\_%\_ARTICLE\_[%]\_SLUG'
006   AND hAID.v= hSTITLE.v
007   INNER JOIN tbl_hns AS hATITLE
008   ON hATITLE.k= CONCAT(SUBSTRING_INDEX(hAID.k, '_', 6), '_', 'TITLE')
009   INNER JOIN tbl_hns AS hAEDATE
010   ON hAEDATE.k= CONCAT(SUBSTRING_INDEX(hAID.k, '_', 6), '_', 'EDATE')
011   WHERE hSTITLE.k= 'CMS_CONSULT_SLUG_[SLUG49297P]_STITLE'
012   ORDER BY CONCAT(hAEDATE.v, ' - ', hATITLE.v);

This same query and data worked on "mysql  Ver 12.22 Distrib 4.0.17, for apple-darwin7.2.0 (powerpc)" and only reared it's ugly head when ported to "mysql  Ver 14.7 Distrib 4.1.13, for suse-linux (powerpc) using readline 5.0".

How to repeat:
Note: It's intesting to note that the below worked fine when there was only 1 record in the test table, when there was two records (ie. the JOIN had to do some work), the problem reocured.

CREATE TABLE `test` (
  `A` longtext NOT NULL,
  `B` longtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERT INTO `test` (A, B) VALUES
  ('2005-01-01', 'foo'),
  ('2005-02-01', 'bar');

SELECT 
  CONCAT(t1.X, ' - ', t2.Y) AS x
  FROM test AS t1
  JOIN test AS t2
  ON LEFT(t1.Y, 1)= 'b'
  ORDER BY CONCAT(t1.X, ' - ', t2.Y)

verified on 4.1.12, not reproduceable on 4.1.14 and later

Is it possible it's some kind of background type coercion causing the problem? Because the first parameter (to the CONCAT, looks like a date). 

Also, I don't know if its the ORDER BY causing the problem, the JOIN & ORDER BY are required to cause the problem. With just the JOIN or ORDER BY,  the correct data is returned.

Correction- Sample query to reproduce references X & Y fields, should be A & B.

After I used EXPLAIN EXTENDED on the test query, it seems like the problem occurs when "using temporary" is in the "Extra" column of the EXPLAIN EXTENDED resultset.