You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "liliyuanshang (via GitHub)" <gi...@apache.org> on 2026/03/11 07:56:13 UTC

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

liliyuanshang opened a new issue, #61219:
URL: https://github.com/apache/doris/issues/61219

   ### Search before asking
   
   - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) 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
   
   - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
   


-- 
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: commits-unsubscribe@doris.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

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

Posted by "aryash45 (via GitHub)" <gi...@apache.org>.
aryash45 commented on issue #61219:
URL: https://github.com/apache/doris/issues/61219#issuecomment-4062246764

   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!


-- 
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: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

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

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #61219: [Bug] Doris View Order by 会过度列裁剪导致列为空
URL: https://github.com/apache/doris/issues/61219


-- 
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: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

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

Posted by "liliyuanshang (via GitHub)" <gi...@apache.org>.
liliyuanshang commented on issue #61219:
URL: https://github.com/apache/doris/issues/61219#issuecomment-4073019461

   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.


-- 
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: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

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

Posted by "aryash45 (via GitHub)" <gi...@apache.org>.
aryash45 commented on issue #61219:
URL: https://github.com/apache/doris/issues/61219#issuecomment-4088776638

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


-- 
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: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

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

Posted by "morrySnow (via GitHub)" <gi...@apache.org>.
morrySnow commented on issue #61219:
URL: https://github.com/apache/doris/issues/61219#issuecomment-4115064548

   it is not a column prunning bug, it is a bug about topn lazy materialization
   
   ```sql
   
   -- 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 |
   +--------+--------+--------+--------+------------+--------+---------------------+
   ```
   
   ```sql
   -- 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 |
   +----+----+----+----+------------+----+---------------------+
   ```
   


-- 
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: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

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

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #61219:
URL: https://github.com/apache/doris/issues/61219#issuecomment-5753838282

   We're closing this issue because it hasn't been updated in a while.
   If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!


-- 
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: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org