starocean999 commented on PR #29463:
URL: https://github.com/apache/doris/pull/29463#issuecomment-1968471229
> 这个 pr 是有问题的, @starocean999 @englefly @morrySnow @xiaokang 相关的 sql 如下
>
> ```sql
> drop table if exists temp1;
> create table temp1 (
> a tinyint,
> b varchar(40),
> c varchar(80),
> d datetimev2,
> e bigint,
> f tinyint default '0',
> g bigint,
> h bigint
> )
> unique key(a, b, c, d, e)
> partition by range(d) (
> partition pmin values less than ("2023-08-01 00:00:00"),
> partition p202308 values less than ("2023-09-01 00:00:00"),
> partition p202309 values less than ("2023-10-01 00:00:00"),
> partition p202310 values less than ("2023-11-01 00:00:00"),
> partition p202311 values less than ("2023-12-01 00:00:00"),
> partition p202312 values less than ("2024-01-01 00:00:00"),
> partition p202401 values less than ("2024-02-01 00:00:00"),
> partition p202402 values less than ("2024-03-01 00:00:00")
> )
> distributed by hash(b) buckets auto
> properties (
> "enable_unique_key_merge_on_write" = "true",
> "estimate_partition_size" = "2G",
> "dynamic_partition.enable" = "true",
> "dynamic_partition.time_unit" = "month",
> "dynamic_partition.end" = "3",
> "dynamic_partition.prefix" = "p",
> "replication_allocation" = "tag.location.default: 1"
> );
>
>
> 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
> having ff >= 0 and ff < 180 and date_add(ctemind, interval ff day) >=
'2023-12-29' and ctemind = '2023-12-21';
> ```
>
> temp1 表的示例数据放在下个 comment
>
> 当前 pr 是从 2.0.4 开始加进去的, 上面的查询语句 在 2.0.4 之前数据是正确的, **最后 4 列分别是 `100` `1`
`81100` `36`** 从 2.0.4 开始(包含最新的 2.0.5), **最后 4 列成了 `100` `1` `100` `1`
这个查询结果是不对的**
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';`
--
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]
