0

I have two tables with the same JSON column. My query needs to UNION some rows from both. The problem is that UNION trims the result (see: http://sqlfiddle.com/#!9/506e43/1/1). The solution here (Teradata string truncated after UNION ALL) is to cast the column to CHAR(n) but in my case, this won't work because the length of the result may vary from 200 to 2000 characters (or sometimes more).

Is there a better solution?

cristiancastrodc
  • 188
  • 1
  • 13

1 Answers1

0

I’m not sure if it’s an issue with the UNION, but I had a very similar issue with an application that was crashing for larger partners - we found out MySQL group_concat hard trimmed the output. For us it was anything over 1024 characters was dropped - and since we were expecting XML - undesired output.

You can set the max length of group_concat to something much larger in your my.ini file, or if you need for temporary large things, we sometimes set it during the session similar to this: MySQL and GROUP_CONCAT() maximum length

(From above post)

SET SESSION group_concat_max_len = 1000000; select group_concat(column) from table group by column

Here’s info in their docs - and you can click into the property for more info: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

Poat
  • 327
  • 1
  • 11
  • I'm marking this as the answer because it works. But for sure I'd like to understand why the query without UNION works but no the query without it. – cristiancastrodc Mar 10 '18 at 16:09
  • hmm, more research it seems potentially this may be an issue? when using union all and group_concat, if you don't set the length, people are getting truncated results: https://stackoverflow.com/questions/47733920/mysql-group-concatutf8-in-union-truncated-to-1024-3?rq=1 – Poat Mar 10 '18 at 21:56