Verifying GROUP_CONCAT limit without using variables

I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I’ve ranted on this here.

Normally, I would simply:

SELECT @@group_concat_max_len

However, I am using views, where session variables are not allowed. Using a stored function can do the trick, but I wanted to avoid stored routines. So here’s a very simple test case: is the current group_concat_max_len long enough or not? I’ll present the long version and the short version.

The long version

SELECT
  CHAR_LENGTH(
    GROUP_CONCAT(
      COLLATION_NAME SEPARATOR ''
    )
  )
FROM
  INFORMATION_SCHEMA.COLLATIONS;

If the result is 1024, we are in a bad shape. I happen to know that the total length of collation names is above 1800, and so it is trimmed down. Another variance of the above query would be:

SELECT
  CHAR_LENGTH(
    GROUP_CONCAT(
      COLLATION_NAME SEPARATOR ''
    )
  ) = SUM(CHAR_LENGTH(COLLATION_NAME))
    AS group_concat_max_len_is_long_enough
FROM
  INFORMATION_SCHEMA.COLLATIONS;

+-------------------------------------+
| group_concat_max_len_is_long_enough |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

The COLLATIONS, CHARACTER_SETS or COLLATION_CHARACTER_SET_APPLICABILITY tables provide with known to exist variables (assuming you did not compile MySQL with particular charsets). It’s possible to CONCAT, UNION or JOIN columns and tables to detect longer than 1800 characters in group_concat_max_len. I admit this is becoming ugly, so let’s move on.

The short version

Don’t want to rely on existing tables? Not sure what values to expect? Look at this:

SELECT CHAR_LENGTH(GROUP_CONCAT(REPEAT('0', 1025))) FROM DUAL

GROUP_CONCAT doesn’t really care about the number of rows. In the above example, I’m using a single row (retrieved from the DUAL virtual table), making sure it is long enough. Type in any number in place of 1025, and you have a metric for your group_concat_max_len.

SELECT
  CHAR_LENGTH(GROUP_CONCAT(REPEAT('0', 32768))) >= 32768 As group_concat_max_len_is_long_enough
FROM
  DUAL;
+-------------------------------------+
| group_concat_max_len_is_long_enough |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

The above makes a computation with REPEAT. One can replace this with a big constant.