Weird behaviour of CONCAT() 1

[Sleidia]

Hello

There is one weird thing going on in the query below :

For an unknow reason, the number of character of all the results issued by
CONCAT() can't be superior to the length __STRING__.
If I replace "__STRING__" by nothing, the column test will be
empty for all records. If I replace "__STRING__" by "ab", all
the records for test will only show the first two characters.

Can someone explain what's going on?

Thanks!

SELECT
t1.*
, '__STRING__' as test
FROM
ecom_prod_cats AS t1
WHERE
t1.cat_parent_ID = "2"

UNION ALL

SELECT
t2.*
, CONCAT(t1.cat_ID, '.', t2.cat_ID) as test
FROM ecom_prod_cats AS t1
INNER JOIN ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
AND (t1.cat_ID = "13" OR t1.cat_ID = "17")
WHERE
t1.cat_parent_ID = "2"

UNION ALL

SELECT
t3.*
, CONCAT(t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID) AS test
FROM ecom_prod_cats AS t1
INNER JOIN ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
AND (t1.cat_ID = "13" OR t1.cat_ID = "17")
WHERE
t1.cat_parent_ID = "2"

UNION ALL

SELECT
t4.*
, CONCAT(t1.cat_ID, '.', t2.cat_ID, '.', t3.cat_ID, '.', t4.cat_ID) AS test
FROM ecom_prod_cats AS t1
INNER JOIN ecom_prod_cats AS t2 ON t2.cat_parent_ID = t1.cat_ID
INNER JOIN ecom_prod_cats AS t3 ON t3.cat_parent_ID = t2.cat_ID
INNER JOIN ecom_prod_cats AS t4 ON t4.cat_parent_ID = t3.cat_ID
AND (t1.cat_ID = "13" OR t1.cat_ID = "17")
WHERE
t1.cat_parent_ID = "2"

ORDER BY t1.cat_parent_ID, cat_ID

 

[r937]

this is not a problem with CONCAT

in a UNION query, the database creates a result set with column names and datatypes from the first SELECT in the UNION

the datatype of the string column is probably CHAR, where n is the length of the string in the first SELECT

just re-sequence your SELECTS in the UNION with the longest string first

r937.com | rudy.ca

 

[Sleidia]

Thanks Rudy

But then, why can the test result get as long as __STRING__, regardless of the length of the datatype?
For example, even if the datatype from the first SELECT in the UNION is CHAR(4), replacing "__STRING__" by "-----------------------------------" would allow the test result to have as many characters as there is hyphens. So basically, the allowed length seems to be decided by the length of the string in the third line of the query.

Also, how are you supposed to do when all the columns have a datatype with a length smaller than the concat result?

 

[r937]

yes, the allowed length is based on this column --

SELECT
t1.*
, [red]'__STRING__'[/red] as test
FROM ...

r937.com | rudy.ca

 

[Sleidia]

Ok but how can I have

SELECT
t1.*
, '' as test
FROM ...

without the concat result being trimmed to nothing?

Is it possible to define a datatype length for the virtual "test" column without the need to define an arbitrary value?

 

[r937]

just put the SELECT with the longest string first

r937.com | rudy.ca

 

[Sleidia]

Ok, I got it! Thanks