liuanxin commented on PR #29463: URL: https://github.com/apache/doris/pull/29463#issuecomment-1968532789
> the current behavior is correct, and the query result is the same as mysql. You can change the sql like bellow to get expected result: `SELECT * FROM ( SELECT ctemind aa, b bb, 0 cc, 0 dd, c ee, datediff(dates, ctemind) ff, sum(h) gg, count(distinct if(basic.h > 0, ctedi, null)) hh, sum(sum(h)) OVER (partition by ctegroup ORDER BY dates rows BETWEEN unbounded preceding AND current row) ii, sum(count(if(basic.h > 0, 1, null))) OVER (partition by ctegroup ORDER BY dates rows BETWEEN unbounded preceding AND current row) jj FROM (SELECT a, to_date(d) dates, b, c, g, h FROM temp1 i WHERE i.a IN (1, 3) AND i.b = 'abc' AND c = '123' ) basic INNER JOIN (SELECT b cteai, c cteci, g ctedi, min(if(a = 1, dates, null)) ctemind, dense_rank() OVER ( ORDER BY min(dates), b, c ) ctegroup FROM (SELECT a, to_date(d) dates, b, c, g, h FROM temp1 i WHERE i.a IN (1, 3) AND i.b = 'abc' AND c = '123' ) t GROUP BY cteai, cteci, ctedi HAVING ctemind is NOT NULL ) cte ON cte.cteai = basic.b AND cte.cteci = basic.c AND cte.ctedi = basic.g GROUP BY ctemind, b, c, dates, ctegroup ) tt where ff >= 0 AND ff < 180 AND date_add(aa, interval ff day) >= '2023-12-29' AND aa = '2023-12-21';` ----- 调整后的语句(having 子句可以保留 `ff >= 0 AND ff < 180 AND aa = '2023-12-21'`, 带函数方法的条件用`子查询 + where` 实现)是符合预期的, 想不到 having 中带函数时, 会有这样的差异表现 The adjusted statement (the having clause retains `ff >= 0 AND ff < 180 AND aa = '2023-12-21'`, and another condition with a functional method is implemented with a `subquery + where`) is in line with expectations, I didn’t expect that there would be such a difference when there is a function in the having statment. -- 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]
