[Bug] Doris returns incorrect query results when there are multiple window functions in the select and one of the window functions applies the topN optimization.

[htyoung]

Search before asking

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

Version

I can reproduce it on versions 2.1.7, other versions have not been verified

What's Wrong?

 It will return incorrect results when using the Nereids optimizer

What You Expected?

Nereids optimizer can return correct results

How to Reproduce?

create database tmp;

CREATE TABLE tmp.`test_table` (
  `mop` varchar(200) NULL,
  `mis` int NULL,
  `result_cpv` double NULL
) ENGINE=OLAP
DUPLICATE KEY(`mop`)
DISTRIBUTED BY HASH(`mop`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

insert into tmp.test_table values ("2024-08",3,23.82),
("2024-10",3,21.24),
("2024-04",12,NULL),
("2024-04",3,61.92),
("2024-11",3,12.45),
("2024-05",3,54.54),
("2024-06",3,41.52),
("2024-12",3,5.04),
("2024-07",3,25.38),
("2024-09",3,23.21),
("2024-03",3,115.28),
("2024-03",12,NULL);

SELECT /*+set_var(enable_nereids_planner=false)*/
    mop,
    mis,
    case when mis=3 then result_cpv end as 3mis_cpv,
    case when mis=12 then result_cpv end as 12mis_cpv,
    rn
FROM
    (
        SELECT
            mop,
            mis,
            round(sum(result_cpv) OVER(PARTITION BY mop order by mis),2) as result_cpv,
            row_number() over (PARTITION by mis order by mop desc) rn
        FROM
            tmp.test_table t
    ) t
WHERE
    rn = 1
order by
    mop,mis
;

SELECT /*+set_var(enable_nereids_planner=true)*/
    mop,
    mis,
    case when mis=3 then result_cpv end as 3mis_cpv,
    case when mis=12 then result_cpv end as 12mis_cpv,
    rn
FROM
    (
        SELECT
            mop,
            mis,
            round(sum(result_cpv) OVER(PARTITION BY mop order by mis),2) as result_cpv,
            row_number() over (PARTITION by mis order by mop desc) rn
        FROM
            tmp.test_table t
    ) t
WHERE
    rn = 1
order by
    mop,mis
;

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

[htyoung]

I have try it on version 2.1.8，found it fixed. Related PR: #44655 #44617 #38393