Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
10.2.4-4, 10.1.20, 10.2.4-5, 10.2.4-1, 10.2.4-2, 10.3.3-1
Description
GROUP_CONCAT supports ORDER BY and DISTINCT but not LIMIT clause.
Supporting LIMIT N[,M] could simplify queries.
something like
SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",date,cnt) ORDER BY cnt DESC),",",1)
could be written as
GROUP_CONCAT(CONCAT_WS(":",date,cnt) ORDER BY cnt DESC LIMIT 1)
this can be used in timeseries-like calculations. Suppose we have a table (date,cnt) and we want to produce a list of dates, for each year, where cnt was maximum for that year, showing such date,cnt for each year
currently this is written in an awkward manner and is also quite inefficient as we SELECT much more data that is necessary for the task:
SELECT LEFT(col1,4) AS _yyyy
, SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",DATE_FORMAT(col1,"%a %Y-%m-%d"),LPAD(col2,8," ")) ORDER BY col2 DESC),",",1) AS col3
FROM t1
GROUP BY 1
;
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
MDEV-11563 GROUP_CONCAT(DISTINCT ...) may produce a non-distinct list
-
- Closed
-
Activity
Add yet another test like:
set @x=-1;
|
execute STMT using @x;
|
and if it return error it is ok to push (if no let us talk)
LIMIT clause in GROUP_CONCAT() does not work in a view.
This can be tested using the example on https://mariadb.com/kb/en/library/group_concat/. If you create a view from the statement 'SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) FROM d;' than LIMIT does not do anything. Run the statement on its own and LIMIT works. I tested this in 10.3 and 10.4 docker container versions.
Is that how it should be? I would like to be able to use LIMIT in a view.
antoonp, thanks for reporting the bug, I have opened issue MDEV-18943 for this bug.
The branch was based on 10.2, so a rebase is done on top of 10.3.
The code is currently pushed to the branch 10.3-varun