[Bug] logical view with aggregation query failed

[dtkavin]

Search before asking

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

Version

2.1

What's Wrong?

When creating a logical view with group by, the query will report an error

ERROR 1105 (HY000): errCode = 2, detailMessage = select list expression not produced by aggregation output (missing from GROUP BY clause?): date(CAST(`t1` AS datetimev2(0)))

after set enable_fallback_to_original_planner = false, error like this:

ERROR 1105 (HY000): errCode = 2, detailMessage = t1 not in aggregate's output

What You Expected?

Expect the query to be correct.

How to Reproduce?

DROP TABLE IF EXISTS `test_tbl_src_01`;

CREATE TABLE `test_tbl_src_01` (
  `dt` date NULL,
  `k4` varchar(120) NULL,
  `code2` varchar(120) NULL
) ENGINE=OLAP
DUPLICATE KEY(`dt`, `k4`)
DISTRIBUTED BY HASH(`dt`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"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 `test_tbl_src_01` VALUES ('2025-03-01','ABC123','1234567');


DROP TABLE IF EXISTS `test_tbl_src_02`;

CREATE TABLE `test_tbl_src_02` (
  `t1` varchar(120) NULL,
  `k1` varchar(120) NULL,
  `k2` varchar(120) NULL,
  `d1` decimal(19,4) NULL,
  `k3` varchar(120) NULL,
  `dt` date NULL
) ENGINE=OLAP
DUPLICATE KEY(`t1`)
DISTRIBUTED BY HASH(`t1`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"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 `test_tbl_src_02` VALUES ('2025-03-01 10:04:25','ABC123','1234567',1.0000,'Doris001','2025-03-01');

drop view test_date_error;

CREATE VIEW test_date_error
AS
SELECT dt AS `日期`, k4 AS `编码1`, code2 AS `编码2`, cnt AS `数量`
FROM (
  SELECT dt, k4, code2, cnt
  FROM (
    SELECT aa.dt, aa.k4, aa.code2, cnt
    FROM test_tbl_src_01 aa
      LEFT JOIN (
        SELECT date(t1) AS t1, k1, k2
          , SUM(d1) AS cnt
        FROM test_tbl_src_02
        WHERE k3 IN ('Doris001', 'Doris001')
          AND dt >= date_sub(date(now()), INTERVAL 6 MONTH)
        GROUP BY date(t1), k1, k2
      ) cc
      ON aa.dt = cc.t1
        AND aa.k4 = cc.k1
        AND aa.code2 = cc.k2
  ) ee
) ff;

select * from test_date_error;

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