MariaDB - Column created with 'AS' has a default value of 1024

Question

I have this query

SELECT Stations.Instance
    ,stations.ID
    ,stations.Name
    ,Stations.Lat
    ,Stations.Lon
    ,(
        SELECT GROUP_CONCAT(Line SEPARATOR ';')
        FROM transportData.timeTables
        WHERE timeTables.Station = stations.ID
        GROUP BY timeTables.Station
        ) AS 'Lines'
    ,(
        SELECT GROUP_CONCAT(Weekdays SEPARATOR ';')
        FROM transportData.timeTables
        WHERE timeTables.Station = stations.ID
        GROUP BY timeTables.Station
        ) AS 'Weekdays'
    ,(
        SELECT GROUP_CONCAT(Weekends SEPARATOR ';')
        FROM transportData.timeTables
        WHERE timeTables.Station = stations.ID
        GROUP BY timeTables.Station
        ) AS 'Weekends'
FROM `Stations`
INNER JOIN transportData.stations ON stations.Lat = Stations.Lat
    AND stations.Lon = Stations.Lon
WHERE Instance = '$instance'

that is supposed to return in the Weekdays column a list of Weekdays that have the same Station column as the ID column of transportData.stations. Same goes for Weekends.

My issue here is the fact that these columns are usually large, about 2000 characters, and with this query, I could concatenate up to about 10 of these, so that's a lot of characters.

The query above works, but returns the Weekdays and Weekends with a maximum length of 1024, which is not at all enough.

How can I tell MariaDB that I want the result in more than 1024 characters?

Unrelated: Consider LEFT JOIN and conditional aggregation instead of those correlated sub-queries. — jarlh, Feb 26 '18 at 12:03
What datatype are your `transportData.timeTables.Weekdays` and `transportData.timeTables.Weekends` columns? — eggyal, Feb 26 '18 at 12:03

score 1 · Accepted Answer · answered Feb 26 '18 at 12:05

1

You can extend the group_concat limit changing the my.ini

group_concat_max_len = 10000

answered Feb 26 '18 at 12:05

ScaisEdge

131,976
10
91
107

Right idea, but I don't think 10,000 is long enough to store 10 copies of a column with 2,000 characters. – Gordon Linoff Feb 26 '18 at 12:09
This works great, I used the max length as the 32 bits maximum – George Lazu Feb 26 '18 at 12:38
My values was only a suggestion .. and the OP seems that have found the correct length.. – ScaisEdge Feb 26 '18 at 12:39

MariaDB - Column created with 'AS' has a default value of 1024

1 Answers1