Tidb: Aggregate Function: group_concat with order by produces wrong result

[zhangysh1995]

Bug Report

Preview

mysql> select group_concat(a1 order by (t1.a IN (select a0 from t2)) desc) from t1; +--------------------------------------------------------------+ | group_concat(a1 order by (t1.a IN (select a0 from t2)) desc) | +--------------------------------------------------------------+ | c,a,b | +--------------------------------------------------------------+ 1 row in set (0.00 sec)

1. Minimal reproduce step (Required)

create table t1 (a int, a1 varchar(10)); create table t2 (a0 int); insert into t1 values (0,"a"),(0,"b"),(1,"c"); insert into t2 values (1),(2),(3); -- incorrect select group_concat(a1 order by (t1.a IN (select a0 from t2))) from t1; -- incorrect select group_concat(a1 order by a in (1,2,3) desc) from t1; -- correct select a1 from t1 order by 1 desc;

2. What did you expect to see? (Required)

````
mysql> select group_concat(a1 order by (t1.a IN (select a0 from t2)) desc) from t1;
+--------------------------------------------------------------+
| group_concat(a1 order by (t1.a IN (select a0 from t2)) desc) |
+--------------------------------------------------------------+
| c,b,a |
+--------------------------

mysql> select group_concat(a1 order by a in (1,2,3) desc) from t1;
+---------------------------------------------+
| group_concat(a1 order by a in (1,2,3) desc) |
+---------------------------------------------+
| c,b,a |
+---------------------------------------------+
1 row in set (0.00 sec)
````

3. What did you see instead (Required)

````
mysql> select group_concat(a1 order by (t1.a IN (select a0 from t2)) desc) from t1;
+--------------------------------------------------------------+
| group_concat(a1 order by (t1.a IN (select a0 from t2)) desc) |
+--------------------------------------------------------------+
| c,a,b |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select group_concat(a1 order by a in (1,2,3) desc) from t1;
+---------------------------------------------+
| group_concat(a1 order by a in (1,2,3) desc) |
+---------------------------------------------+
| c,a,b |
+---------------------------------------------+
1 row in set (0.00 sec)

````

4. Affected version (Required)

commit 402fd2a247fff00ebe696a7d7672bfb2008435e4 (HEAD -> master, origin/master, origin/HEAD) Author: lysu <[email protected]> Date: Tue Jun 30 19:31:37 2020 +0800

5. Root Cause Analysis

[HunDunDM]

/label component/coprocessor

[ichn-hu]

/unassign @fzhedu

[ichn-hu]

/assign @ichn-hu

[ichn-hu]

@zhangysh1995 this behavior is semantically correct, as there is no contraint on how we should deal with the order of elements of the same value. The in expression will evalue to false, false, true for the 3 rows in t1, and the result of c,b,a or c,a,b are both correct.

Welcome to raise any questions you have, otherwise we will just close this issue.

[ichn-hu]

@SunRunAway please help close this issue.

[zhangysh1995]

Sorry I just saw the comments. @ichn-hu

@zhangysh1995 this behavior is semantically correct, as there is no constraint on how we should deal with the order of elements of the same value. The IN expression will evaluate to false, false, true for the 3 rows in t1, and the result of c,b,a or c,a,b are both correct.

In the case of using a1 order by (t1.a IN (select a0 from t2)), the result false, false, true are evaluated as 0,0,1, which specifies the order of columns to be evaluated for the sort. _Notice here it is interpreted differently from the "value" of the result._ It means 0 is the 0th field in select, and a is the 1st field in select. Because there is no 0th field defined, it will order the result by the value of the 1st, which give the result c, b, a.

In this sense, c, a, b is a wrong answer.

[zhangysh1995]

Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1:

SELECT college, region, seed FROM tournament
ORDER BY region, seed;

SELECT college, region AS r, seed AS s FROM tournament
ORDER BY r, s;

SELECT college, region, seed FROM tournament
ORDER BY 2, 3;
To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this can be specified explicitly using the ASC keyword.

If ORDER BY occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future MySQL version.

Use of column positions is deprecated because the syntax has been removed from the SQL standard.

MySQL 5.7 doc, 8.0 doc

[ichn-hu]

Sorry I just saw the comments. @ichn-hu

@zhangysh1995 this behavior is semantically correct, as there is no constraint on how we should deal with the order of elements of the same value. The IN expression will evaluate to false, false, true for the 3 rows in t1, and the result of c,b,a or c,a,b are both correct.

In the case of using a1 order by (t1.a IN (select a0 from t2)), the result false, false, true are evaluated as 0,0,1, which specifies the order of columns to be evaluated for the sort. _Notice here it is interpreted differently from the _"value"_ of the result._ It means 0 is the 0th field in select, and a is the 1st field in select. Because there is no 0th field defined, it will order the result by the value of the 1st, which give the result c, b, a.

In this sense, c, a, b is a wrong answer.

May I beg to differ.

It is impossible that the values are interpreted as column number, because on a implementation's perspective, it requires a runtime information to know about the ordering rule.

Even if it is intepreted as "ordering the value by the first column of table 1", c,a,b and c,b,a are still both valid results.

More on the impossibility of the interpretion, the following example would defend my claim.

on MySQL 8.0.21

create table t3 (c1 int, c2 int, c3 char);
insert into t3 values (1, 0, "a"), (0, 1, "b");
select group_concat(c3 order by (c2 in (1, 2)) desc) from t3;

If your explanation is correct, then c2 in (1, 2) should evaluate into 0, 1, and we use the first column for ordering, we should have a,b as result, but actually, the output is b,a.

Thanks for you quick reply, let me know if you have any more thoughts. I will be around and working on your another issue https://github.com/pingcap/tidb/issues/18525.

Thank you again for your valuable challenges to the TiDB project.

[zhangysh1995]

If your explanation is correct, then c2 in (1, 2) should evaluate into 0, 1, and we use the first column for ordering, we should have a,b as result, but actually, the output is b,a.

You used desc to specify the sort order, then it will be b, a. I don't see a conflict with my explanation here.

````
mysql> select group_concat(c3 order by (c2 in (1, 2)) desc) from t3;
+-----------------------------------------------+
| group_concat(c3 order by (c2 in (1, 2)) desc) |
+-----------------------------------------------+
| b,a |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select group_concat(c3 order by (c2 in (1, 2)) asc) from t3;
+----------------------------------------------+
| group_concat(c3 order by (c2 in (1, 2)) asc) |
+----------------------------------------------+
| a,b |
+----------------------------------------------+
1 row in set (0.00 sec)
````

However, following this example, if I swap c2 in the table, I found a counterexample for my explanation:

create table t4 (c1 int, c2 int, c3 char); insert into t4 values (1, 1, "a"), (1, 0, "b"); select group_concat(c3 order by (c2 in (1, 2)) asc) from t4;

_My explanation was cited from a contributor for MySQL, based on the new example, I now suspect his claim._