MySQL Bugs: #6204: Result Column Length Incorrect Length in Union

Bug #6204	Result Column Length Incorrect Length in Union
Submitted:	21 Oct 2004 19:22	Modified:	21 Oct 2004 21:31
Reporter:	seth ladd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.17	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When running a union query w/ a literal string value as a query result, the length of all other rows for that column are set to the first row's length.  A query example is best to show this issue (illustrated in How to Repeat)

How to repeat:
SELECT  'All Business' AS  'Type', count(  *  )  AS  'Count'
FROM BUSINESS
UNION 
SELECT  'Account', count(  *  ) 
FROM ACCOUNT
UNION 
SELECT concat( state,  ' Business'  ) , count(  *  ) 
FROM BUSINESS
GROUP  BY state

this will give the results:

+--------------+-------+
| Type         | Count |
+--------------+-------+
| All Business |   281 |
| Account      |    31 |
| bregs_approv |   114 |
| interviewing |    59 |
| paying Busin |     4 |
| submitted Bu |   104 |
+--------------+-------+

Notice that the word 'Business' is truncated.  The lengths are all equal to the length of the string 'All Business'

To solve this problem in the meantime, I had to use RPAD on the 'All Business' string (the first string in the query.

Expected behaviour is to choose the longest string in the column for the length of all rows of the column.

Suggested fix:
Expected behaviour is to choose the longest string in the column for the length of all rows of the column.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

This is docummented limitation of 4.0 which  has been removed as of MySQL 4.1.1: