Performing GROUP_CONCAT ... ORDER BY operation on subquery results results in column does not exist error

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 从子查询结果中 进行 GROUP_CONCAT … ORDER BY 操作，会报列不存在的错误

| username: 海石花47

[TiDB Usage Environment] Production Environment
[TiDB Version] TiDB6.1
[Reproduction Path]
sql1: This SQL execution will report an error:

> 1105 - Can't find column Column#57 in schema Column: [pre_dim.dim_performance_roster_month.role_id,pre_dim.dim_performance_roster_month.role_name,pre_dim.dim_performance_roster_month.role_id,pre_dim.dim_performance_roster_month.role_name] Unique key: []

SELECT 
    GROUP_CONCAT(DISTINCT `role_id` ORDER BY `role_id` ASC SEPARATOR '&&' ) role_ids,
    GROUP_CONCAT(DISTINCT `role_name` ORDER BY `role_id` ASC SEPARATOR '&&' ) role_names
FROM(
    SELECT 
        COALESCE(b.role_id,a.role_id) role_id
        ,COALESCE(b.role_name,a.role_name) role_name
    FROM pre_dim.`dim_performance_roster_month` a
    LEFT JOIN pre_dim.`dim_performance_roster_month` b ON a.user_id = b.user_id
) a1

sql2: This SQL execution (removed order by …) has no issues, no errors

SELECT 
    GROUP_CONCAT(DISTINCT `role_id` ) role_ids,
    GROUP_CONCAT(DISTINCT `role_name` ) role_names
FROM(
    SELECT 
        COALESCE(b.role_id,a.role_id) role_id
        ,COALESCE(b.role_name,a.role_name) role_name
    FROM pre_dim.`dim_performance_roster_month` a
    LEFT JOIN pre_dim.`dim_performance_roster_month` b ON a.user_id = b.user_id
) a1

sql3: Putting the subquery into a table (test.test_dim_performance_roster_month) and then querying, no issues

SELECT 
    GROUP_CONCAT(DISTINCT `role_id` ORDER BY `role_id` ASC SEPARATOR '&&' ) role_ids,
    GROUP_CONCAT(DISTINCT `role_name` ORDER BY `role_id` ASC SEPARATOR '&&' ) role_names
FROM(
    SELECT * from test.test_dim_performance_roster_month
) a1

[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

2
| username: 海石花47 | Original post link

Dear experts, please help~ Why is this happening?

3
| username: WalterWj | Original post link

Does MySQL report an error?

4
| username: tidb菜鸟一只 | Original post link

It seems that there is indeed this issue. Writing it this way should be fine.

5
| username: 海石花47 | Original post link

Writing it this way doesn’t work, it says there’s a syntax error?

6
| username: 海石花47 | Original post link 
Writing it this way works, awesome, thank you~ But why is this? Is it a bug?
7
| username: 海石花47 | Original post link

I also found that if I remove the COALESCE function in my sql1, there is no problem…

It seems that: when GROUP_CONCAT + ORDER BY encounters a “subquery with a null value checking function,” an error occurs? Is it a bug?

8
| username: 海石花47 | Original post link

There is no problem in TiDB 3.0… Why??? Confused

9
| username: tidb菜鸟一只 | Original post link

Because later versions of TiDB rewrote the GROUP_CONCAT function, MySQL does not have this issue either.

10
| username: 海石花47 | Original post link

How did you know… :joy:

11
| username: tidb菜鸟一只 | Original post link

I have looked at the source code, but I couldn’t figure out what caused this bug. However, TiDB did rewrite this function, and the versions after 3.0 are different from the ones before 3.0.

12
| username: 海石花47 | Original post link

Awesome :+1:

13
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.