Union truncates returned columns

[Debbie-Leigh]

Hi,

I have a problem with a union statement. I'm trying to use the following statement:

   (SELECT tbl1.dnldaccess_id
	   , 0                  AS order_id
	   , tbl1.product_id
	   , tbl1.password
	   , tbl1.download_num
	   , product_name
	   , ''                 AS full_name
	   , DATE_FORMAT(tbl1.effective_ts,'" . DATEFMT_LONG_DB . "') AS effective_ts
	FROM " . $strTblName                . " tbl1
	   , " . TBLSALES_PRODUCT           . " tbl3 
     WHERE tbl1.order_id      = 0 
	 AND tbl1.product_id    = tbl3.product_id 
   ) UNION (
    SELECT tbl1.dnldaccess_id
	   , tbl1.order_id
	   , 0                  AS product_id
	   , tbl1.password
	   , tbl1.download_num
	   , ''                 AS product_name
	   , CONCAT(tbl5.title_abbr,' ',tbl4.first_name,' ',tbl4.last_name) AS full_name
	   , DATE_FORMAT(tbl1.effective_ts,'" . DATEFMT_LONG_DB . "') AS effective_ts
	FROM " . $strTblName                . " tbl1
	   , " . TBLSALES_ORDER             . " tbl2 
	   , " . TBLSALES_CUSTOMER          . " tbl4 
	   , " . TBLSALES_TITLE             . " tbl5 
     WHERE tbl1.order_id      <> 0 
	 AND tbl1.order_id      = tbl2.order_id 
	 AND tbl2.customer_id   = tbl4.customer_id 
	 AND tbl4.title_id      = tbl5.title_id 
  ORDER BY product_name
         , full_name
         , tbl1.dnldaccess_id

The problem is that the first query always seems control the width of the the resulting columns no matter which way round I have the statements. So in this format, nothing appears in the full_name column unless I increase the number of spaces in the first select, in which case the resulting field will only be as wide as the number of spaces in the first select. The problem is that the maximum width that full_name can be is 113 characters which is a bit cumbersome to put into the query to say the least and as the resulting column seems to be a fixed width, it is always 113 characters rather than just the width of the data.

The same thing happens with the product_name field when the selects are swapped.

The selects work perfectly standalone, but this quirk only seems to happen when they are combined with the union.

Can anyone suggest a way to stop the union interfering with the column widths, please?

Debbie-Leigh

[bastien]

Hi debbie,

if you read the manual for the union query you would have found this:

The types and lengths of the columns in the result set of a UNION take into account the values retrieved by all the SELECT statements. Before MySQL 4.1.1, a limitation of UNION is that only the values from the first SELECT were used to determine result column types and lengths. This could result in value truncation if, for example, the first SELECT retrieves shorter values than the second SELECT:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| b |
+---------------+

That limitation has been removed as of MySQL 4.1.1:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+

If your db is later that v411, you can spec the column width in the select

[Debbie-Leigh]

Hi,

Thanks bastien, I did end up finding that using RPAD(' ', 120) for the literal columns stopped the truncation, but it's good to know that the problem has been fixed. Unfortunately, I'll have to stick with this solution for the time being as my hoster only has 4.0.18 at the moment. Oh well, patience is a virtue I suppose. :-)

Debbie-Leigh