[Bug] wrong result when useing group by.

[siriume]

Search before asking

• I had searched in the issues and found no similar issues.

Version

branch-1.2-lts
Git : git://lts12@9de1fec

What's Wrong?

Test table like this

CREATE TABLE `tb_holiday` (
  `date` bigint(20) NOT NULL COMMENT '日期',
  `holiday` tinyint(4) NOT NULL COMMENT '节假日',
  `holiday_cn` varchar(9) NOT NULL COMMENT '节假日'
) ENGINE=OLAP
UNIQUE KEY(`date`)
COMMENT '节假日'
DISTRIBUTED BY HASH(`date`) BUCKETS 4
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"in_memory" = "false",
"storage_format" = "V2",
"compression" = "LZ4",
"disable_auto_compaction" = "false"
);

sql like this

SELECT
 date_format(date, '%x%v') AS `date`,
 count(date) AS `diff_days`
FROM `tb_holiday`
WHERE `date` between 20221111 AND 20221116
GROUP BY date
ORDER BY date;

result like this

202245,1
202245,1
202245,1
202246,1
202246,1
202246,1

What You Expected?

result like this

202245,3
202246,3

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[luozenglin]

This is due to a conflict between the column name date and the alias date, you can try changing date_format(date, '%x%v') AS date to date_format(date, '%x%v') AS format_date.

I got the same result by executing the following sql in doris and mysql:

mysql> SELECT  date_format(date, '%x%v') AS `date`,  count(date) AS `diff_days` FROM cost GROUP BY date ORDER BY date;
+--------+-----------+
| date   | diff_days |
+--------+-----------+
| 202053 |         4 |
| 202053 |         1 |
| 202053 |         1 |
+--------+-----------+
3 rows in set, 1 warning (0.00 sec)

mysql> SELECT  date_format(date, '%x%v') AS `format_date`,  count(date) AS `diff_days` FROM cost GROUP BY format_date ORDER BY format_date;
+-------------+-----------+
| format_date | diff_days |
+-------------+-----------+
| 202053      |         6 |
+-------------+-----------+
1 row in set (0.00 sec)

[siriume]

This is due to a conflict between the column name date and the alias date, you can try changing date_format(date, '%x%v') AS date to date_format(date, '%x%v') AS format_date.

I got the same result by executing the following sql in doris and mysql:

mysql> SELECT  date_format(date, '%x%v') AS `date`,  count(date) AS `diff_days` FROM cost GROUP BY date ORDER BY date;
+--------+-----------+
| date   | diff_days |
+--------+-----------+
| 202053 |         4 |
| 202053 |         1 |
| 202053 |         1 |
+--------+-----------+
3 rows in set, 1 warning (0.00 sec)

mysql> SELECT  date_format(date, '%x%v') AS `format_date`,  count(date) AS `diff_days` FROM cost GROUP BY format_date ORDER BY format_date;
+-------------+-----------+
| format_date | diff_days |
+-------------+-----------+
| 202053      |         6 |
+-------------+-----------+
1 row in set (0.00 sec)

According to the execution order of MySQL, group by should give priority to using Alias. In my other Doris cluster (the master branch compiled at the end of last month) , this SQL executes correctly.

[starocean999]

Group by gives higher priority to column name than alias, but order by prefer alias. This is the same as mysql. Master branch has corrected this behavior recently.

[siriume]

GROUP BY date --> mysql column name, doris now is column name
HAVING date = "202201" --> mysql column name, doris now is alias

SELECT date_format(date, '%x%v') AS `date`, count(date) AS `diff_days`
FROM tb_holiday
where `date` between 20220101 AND 20220106
GROUP BY `date` -- mysql column name, doris now is column name
HAVING `date` = "202201" -- mysql column name, doris now is alias
ORDER BY date;

if change sql like this:

SELECT date_format(date, '%x%v') AS `_date`, count(date) AS `diff_days`
FROM tb_holiday
where `date` between 20220101 AND 20220106
GROUP BY _date
HAVING `_date` = "202201"
ORDER BY date; -- doris error: [HY000][1105] errCode = 2, detailMessage = ORDER BY expression not produced by aggregation output (missing from GROUP BY clause?): `date`, mysql execute success.

[siriume]

Group by gives higher priority to column name than alias, but order by prefer alias. This is the same as mysql. Master branch has corrected this behavior recently.

query like this

SELECT date_format(date, '%x%v') AS `date`, count(date) AS `diff_days`
FROM tb_holiday
where `date` between 20220101 AND 20220106
GROUP BY `date`
ORDER BY date;

in 1.1.4-rc01, result like this

202152,2
202201,4

in master f49a0da, even before this commit.

202152,2
202201,4

in master 9de1fec

202152,1
202152,1
202201,1
202201,1
202201,1
202201,1

if doris change this action in 1.2.0 stable release, Please confirm as soon as possible and reply to this issue, cause we need to modify a lot of code to avoid inconsistent results after the upgrade(1.2.0 stable).

[starocean999]

Group by gives higher priority to column name than alias, but order by prefer alias. This is the same as mysql. Master branch has corrected this behavior recently.

query like this

SELECT date_format(date, '%x%v') AS `date`, count(date) AS `diff_days`
FROM tb_holiday
where `date` between 20220101 AND 20220106
GROUP BY `date`
ORDER BY date;

in 1.1.4-rc01, result like this

202152,2
202201,4

in master f49a0da, even before this commit.

202152,2
202201,4

in master 9de1fec

202152,1
202152,1
202201,1
202201,1
202201,1
202201,1

if doris change this action in 1.2.0 stable release, Please confirm as soon as possible and reply to this issue, cause we need to modify a lot of code to avoid inconsistent results after the upgrade(1.2.0 stable).

Sorry for the inconstent behavior between old version and 1.2.0. Master and 1.2.0 have been modified to keep same behavior with mysql( having clause as you point out is not correct and to be fixed soon). I'm afraid you have to change the old sql to get the correct result in 1.2.0. Sorry for the inconvenience again.