siriume commented on issue #14372: URL: https://github.com/apache/doris/issues/14372#issuecomment-1319801423
> 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. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
