471,050 Members | 1,331 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,050 software developers and data experts.

column width in union

Hi

Column width in a series of UNIONs is determined by the width of the
first SELECT. Here is an example:

mysql> ( SELECT "foo" ) UNION ( SELECT "fnord" );
+-----+
| foo |
+-----+
| foo |
| fno |
+-----+
2 rows in set (0.00 sec)

This is in MySQL 4.0.21.

In my real queries, I cannot know ahead of time the maximum length of
any column as returned by any of the SELECT blocks. My workaround is
to start my UNION chain with a

SELECT "xxxxxxxxxxxxxxxx",
"xxxxxxxxxxxxxxxx",
...

And then in the code that uses the results, ignore any rows that have
"xxxxxxxxxxxxxxxx" in them. This is extremely kludgy.

There was as thread from 2003-08-24 with the subject "Union and Order
By give strange results in Mysql 4.0.13" which describes this problem.
It was unresolved (only workarounded) over a year ago.

Does anyone know of a real fix?

-Michal
Jul 20 '05 #1
1 2009
mikeg wrote:
Column width in a series of UNIONs is determined by the width of the
first SELECT. ... Does anyone know of a real fix?


I thought of using CAST(foo as CHAR(16)), but that seems to be an error.

How about using a temporary table?

CREATE TEMPORARY TABLE unionResult ( foo CHAR(16 );
INSERT INTO unionResult SELECT "foo";
INSERT INTO unionResult SELECT "fnord";
INSERT INTO unionResult SELECT "12345678901234567";
SELECT foo FROM unionResult;
+------------------+
| foo |
+------------------+
| foo |
| fnord |
| 1234567890123456 |
+------------------+

Regards,
Bill K.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by Mike Fellows | last post: by
7 posts views Thread by Rajesh.............................. | last post: by
reply views Thread by Allan | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.