[Bug] Doris View Order by 会过度列裁剪导致列为空

[liliyuanshang]

Search before asking

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

Version

doris 4.0.1

What's Wrong?

SELECT *
FROM view_name
ORDER BY a ASC, b
LIMIT 100; 视图的列过度裁剪，除order by的a，b列，其余列为空 create view a as SELECT
c1,c2,c3
FROM (
SELECT
*
FLOOR(
TIMESTAMPDIFF(
HOUR,
DATE_SUB(datatime1, INTERVAL 10 DAY),
COALESCE(datatime2, CURRENT_TIMESTAMP())
) / 24
) AS c
FROM table
) t1

What You Expected?

修正这个bug，

How to Reproduce?

No response

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

[aryash45]

Hi @liliyuanshang @morningman , I'm interested in working on this issue.

Could you please help clarify the following:

1. Minimal Reproducible Example (MRE): Could you provide the complete SQL statements with:

   • The exact CREATE TABLE statement for the base table
   • The exact CREATE VIEW statement
   • The exact SELECT query that triggers the bug
   • Sample test data (if possible)

2. Current vs Expected Behavior:

   • Which specific columns are becoming empty/NULL?
   • What is the expected output?
   • Can you share the actual query result vs expected result?

3. Error Details:

   • Are there any error logs or exception stack traces?
   • Does this happen consistently or only under certain conditions?

4. Environment:

   • Is this reproducible in version 4.0.1 specifically, or other versions too?
   • Which backend (BE) and frontend (FE) versions were used?

5. Root Cause (if known):

   • Is this related to how the query optimizer prunes columns for views with ORDER BY?
   • Are there any related issues or discussions about this?

Once I have these details, I'll be able to create a proper reproduction test case and submit a fix with regression tests.

Thanks!

[liliyuanshang]

2、确切的CREATE VIEW声明&nbsp;CREATE OR REPLACE VIEW view_name AS SELECT id, c1, c2, c3, CASE WHEN delivered_time IS NOT NULL THEN 'Delivered' WHEN otd_now&nbsp;< 3 THEN 'Normal' WHEN otd_now&nbsp;< 5 THEN 'Level1' WHEN otd_now&nbsp;< 7 THEN 'Level2' WHEN otd_now&nbsp;&gt;= 7 THEN 'Level3' ELSE NULL END AS otd_status, c4, CURRENT_TIMESTAMP() AS data_update_time FROM （SELECT *, FLOOR( TIMESTAMPDIFF( HOUR, DATE_SUB(standard_otd_time, INTERVAL 10 DAY),&nbsp; COALESCE(delivered_time, CURRENT_TIMESTAMP())&nbsp; ) / 24 ) AS otd_now FROM table_name ）t ; 3、确切的SELECT触发该错误的查询 SELECT&nbsp;* FROM table WHERE 1 and region&nbsp;= 'NA' ORDER BY is_delivered ASC, CAST(otd AS SIGNED) DESC LIMIT 100; 4、当前行为与预期行为 预期查询显示视图所有列，实际除order by case when 等需要的列全为空，发现：在order by中加入一列，则查询时该列不会为空。 5、环境: 这在4.0.1版本中是否可以重现 原始邮件 发件人：ARYASH GUPTA ***@***.***&gt; 发件时间：2026年3月15日 13:08 收件人：apache/doris ***@***.***&gt; 抄送：离离原上 ***@***.***&gt;, Mention ***@***.***&gt; 主题：Re: [apache/doris] [Bug] Doris View Order by 会过度列裁剪导致列为空 (Issue #61219) aryash45 left a comment (apache/doris#61219) Hi @liliyuanshang @morningman&nbsp;, I'm interested in working on this issue. Could you please help clarify the following: Minimal Reproducible Example (MRE): Could you provide the complete SQL statements with: The exact CREATE TABLE&nbsp;statement for the base table The exact CREATE VIEW&nbsp;statement The exact SELECT&nbsp;query that triggers the bug Sample test data (if possible) Current vs Expected Behavior: Which specific columns are becoming empty/NULL? What is the expected output? Can you share the actual query result vs expected result? Error Details: Are there any error logs or exception stack traces? Does this happen consistently or only under certain conditions? Environment: Is this reproducible in version 4.0.1 specifically, or other versions too? Which backend (BE) and frontend (FE) versions were used? Root Cause&nbsp;(if known): Is this related to how the query optimizer prunes columns for views with ORDER BY? Are there any related issues or discussions about this? Once I have these details, I'll be able to create a proper reproduction test case and submit a fix with regression tests. Thanks! — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.

[aryash45]

thanks @liliyuanshang will work on it and submit a pr asap

[morrySnow]

it is not a column prunning bug, it is a bug about topn lazy materialization

-- create table
create table t1(id int, c1 int, c2 int, c3 int, c4 int, standard_otd_time datetime, delivered_time datetime);

-- insert some data
insert into t1 values(1, 1, 2, 3, 4, now(), now());

-- create view
CREATE OR REPLACE VIEW view_name AS
SELECT
    id,
    c1,
    c2,
    c3,
    CASE
        WHEN delivered_time IS NOT NULL THEN 'Delivered'
        WHEN otd_now < 3 THEN 'Normal'
        WHEN otd_now < 5 THEN 'Level1'
        WHEN otd_now < 7 THEN 'Level2'
        WHEN otd_now >= 7 THEN 'Level3'
        ELSE NULL
    END AS otd_status,
    c4,
    CURRENT_TIMESTAMP() AS data_update_time
FROM (
    SELECT
        *,
        FLOOR(
            TIMESTAMPDIFF(
                HOUR,
                DATE_SUB(standard_otd_time, INTERVAL 10 DAY),
                COALESCE(delivered_time, CURRENT_TIMESTAMP())
            ) / 24
        ) AS otd_now
    FROM t1
) t;

-- query it, the result is wrong
explain verbose SELECT * 
FROM view_name
WHERE 1
ORDER BY data_update_time ASC, otd_status DESC
LIMIT 100;

result is:

+--------+--------+--------+--------+------------+--------+---------------------+
| id     | c1     | c2     | c3     | otd_status | c4     | data_update_time    |
+--------+--------+--------+--------+------------+--------+---------------------+
| <null> | <null> | <null> | <null> | Delivered  | <null> | 2026-03-24 10:58:33 |
+--------+--------+--------+--------+------------+--------+---------------------+

-- turn off topn set topn lazy materialization, then could get correct result. 
set topn_lazy_materialization_threshold = 0;

+----+----+----+----+------------+----+---------------------+
| id | c1 | c2 | c3 | otd_status | c4 | data_update_time    |
+----+----+----+----+------------+----+---------------------+
| 1  | 1  | 2  | 3  | Delivered  | 4  | 2026-03-24 10:58:25 |
+----+----+----+----+------------+----+---------------------+