0

I'm confused with using GROUP_CONCAT() in MySQL.

  • tb1 has composite index : (c1, c2)
SELECT tmp1.c1, 
    tmp1.c2,
    count(tmp2.c2) as cnt
        FROM 
          (SELECT c1, c2
            FROM tb1 
            WHERE 1=1 
              AND register_ymdt >= '2021-09-15' 
              AND register_ymdt < ADDDATE('2022-03-15', 1)
          ) tmp1
          LEFT OUTER JOIN (
            SELECT c1, c2
            FROM tb2
            WHERE 
              c3 IS NOT NULL
          ) tmp2
          ON tmp1.c1 = tmp2.c1 and tmp1.c2 = tmp2.c2
            where tmp1.c1 = 'some value'
            group by tmp1.c1, tmp1.c2;
c1 c2 cnt
1 4 0
1 5 0
1 6 0
1 13 0

So, I expected the query below results in

c1 c2 cnt
1 4,5,6,13 0 
SELECT tmp.c1, GROUP_CONCAT(tmp.c2), GROUP_CONCAT(tmp.cnt) (
  SELECT tmp1.c1, 
    tmp1.c2,
    count(tmp2.c2) as cnt
        FROM 
          (SELECT c1, c2
            FROM tb1 
            WHERE 1=1 
              AND register_ymdt >= '2021-09-15' 
              AND register_ymdt < ADDDATE('2022-03-15', 1)
          ) tmp1
          LEFT OUTER JOIN (
            SELECT c1, c2
            FROM tb2
            WHERE 
              c3 IS NOT NULL
          ) tmp2
          ON tmp1.c1 = tmp2.c1 and tmp1.c2 = tmp2.c2
            where tmp1.c1 = 'some value'
            group by tmp1.c1, tmp1.c2) tmp
                 group by tmp.c1;

However, the result below is what I've got

c1 c2 cnt
1 5,6,13,4 0

and sometimes, I got different result like:

c1 c2 cnt
1 6,13,4,5 0

Of course, If I use ORDER BY in GROUP_CONCAT clause, I'll get what I expected. But, I wonder that why the ordering c2 value was changed when using GROUP_CONCAT.

zz9z9
  • 11
  • 3
  • 1
    The order of the results aren't guaranteed unless you specify an ORDER BY clause. ["...To sort values in the result, use the ORDER BY clause"](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat) – SOS Mar 15 '22 at 07:04
  • 2
    A SQL result without an `ORDER BY` clause is always considered unordered. The order is arbitrary and even running the exact same query twice can result in different orders. If two queries are even different, as is the case with your queries, the more likely it is that the order is different, too. – Thorsten Kettner Mar 15 '22 at 07:05

0 Answers0