You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2025/08/15 20:49:37 UTC

[I] Support aggregates and constant filters in `QUALIFY` [datafusion]

alamb opened a new issue, #17210:
URL: https://github.com/apache/datafusion/issues/17210

   @haohuaijin added support for `QUALIFY` in 
   - https://github.com/apache/datafusion/pull/16933
   
   
   @Vedin  has pointed out some follow on work here
   
   Hi @haohuaijin, I accidentally worked on the same feature. The approach is the same. So, I don't want anyhow conflict with your contribution and will just wait until your PR is merged. I just want to highlight 2 cases that probably are not currently covered in your PR. 
   1. Aggregate functions with `QUALIFY` 
   ```sql
   CREATE TABLE qt (i INT, p VARCHAR, o INT) AS VALUES
     (1, 'A', 1),
     (2, 'A', 2),
     (3, 'B', 1),
     (4, 'B', 2);
   
   SELECT p, SUM(o) AS s
   FROM qt
   GROUP BY p
   QUALIFY RANK() OVER (ORDER BY s DESC) = 1
   ORDER BY p;
   ```
   2. Constant filter + QUALIFY
   ```sql
   CREATE TABLE web_base_events_this_run (
     domain_sessionid VARCHAR,
     app_id VARCHAR,
     page_view_id VARCHAR,
     derived_tstamp TIMESTAMP,
     dvce_created_tstamp TIMESTAMP,
     event_id VARCHAR
   ) AS SELECT * FROM VALUES
     ('ds1', 'appA', NULL, '2025-01-01 10:00:00'::timestamp, '2025-01-01 10:05:00'::timestamp, 'e1'),
     ('ds1', 'appA', NULL, '2025-01-01 11:00:00'::timestamp, '2025-01-01 11:00:00'::timestamp, 'e2'),
     ('ds1', 'appA', 'pv', '2025-01-01 12:00:00'::timestamp, '2025-01-01 12:00:00'::timestamp, 'e3'),
     ('ds2', 'appB', NULL, '2025-01-01 09:00:00'::timestamp, '2025-01-01 09:10:00'::timestamp, 'e4'),
     ('ds2', 'appB', NULL, '2025-01-01 09:05:00'::timestamp, '2025-01-01 09:09:00'::timestamp, 'e5');
     
     SELECT domain_sessionid, app_id
   FROM web_base_events_this_run
   WHERE page_view_id IS NULL
   QUALIFY ROW_NUMBER() OVER (
     PARTITION BY domain_sessionid
     ORDER BY derived_tstamp, dvce_created_tstamp, event_id
   ) = 1
   ORDER BY domain_sessionid;
   ```
   I covered the first one by adding `qualify` expressions to `aggr_expr_haystack`:
   ```rust
           let aggr_expr_haystack = select_exprs
               .iter()
               .chain(having_expr_opt.iter())
               .chain(qualify_expr_opt_pre_aggr.iter());
   ```
   The second one required me to change the logic in `common_subexpr_eliminate.rs`. You can check it out here (https://github.com/Embucket/datafusion/pull/34/). Maybe you'll come up with a better solution. 
   Hope this helps.
   
   _Originally posted by @Vedin in https://github.com/apache/datafusion/issues/16933#issuecomment-3184394834_
               


-- 
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: github-unsubscribe@datafusion.apache.org.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Support aggregates and constant filters in `QUALIFY` [datafusion]

Posted by "rkrishn7 (via GitHub)" <gi...@apache.org>.
rkrishn7 commented on issue #17210:
URL: https://github.com/apache/datafusion/issues/17210#issuecomment-3218682803

   Hello! I've opened https://github.com/apache/datafusion/pull/17313 that should fix the aggregate problem.
   
   As for the second concern, the example provided works with the original `QUALIFY` implementation. Maybe we can add some more context regarding the specific problem?
   
   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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Support aggregates and constant filters in `QUALIFY` [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #17210: Support aggregates and constant filters in `QUALIFY`
URL: https://github.com/apache/datafusion/issues/17210


-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Support aggregates and constant filters in `QUALIFY` [datafusion]

Posted by "Vedin (via GitHub)" <gi...@apache.org>.
Vedin commented on issue #17210:
URL: https://github.com/apache/datafusion/issues/17210#issuecomment-3220355903

   > Hello! I've opened [#17313](https://github.com/apache/datafusion/pull/17313) that should fix the aggregate problem.
   > 
   > As for the second concern, the example provided works with the original `QUALIFY` implementation. Maybe we can add some more context regarding the specific problem?
   > 
   > Thanks!
   
   Yeah, the provided example really works for me as well, but there are still some problems with `common_sub_expression_eliminate` rule. I have tried this example with your changes, and it appears to be failing. So, reproducible is:
   ```sql
   CREATE TABLE bulk_import_entities (
     id INT,
     _task_instance INT,
     _uploaded_at TIMESTAMP
   ) AS VALUES
     (1, 1, '2025-01-01 10:00:00'::timestamp),
     (1, 2, '2025-01-02 09:00:00'::timestamp),
     (1, 2, '2025-01-03 08:00:00'::timestamp),
     (2, 1, '2025-01-01 11:00:00'::timestamp),
     (2, 2, '2025-01-02 12:00:00'::timestamp),
     (3, 1, '2025-01-01 13:00:00'::timestamp);
   
   SELECT id, _task_instance
   FROM bulk_import_entities
   WHERE _task_instance = (
     SELECT MAX(_task_instance) FROM bulk_import_entities
   )
   QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY _uploaded_at) = 1
   ORDER BY id;
   ```
   with error: 
   ```
   Optimizer rule 'common_sub_expression_eliminate' failed
   caused by
   Error during planning: Window has mismatch between number of expressions (1) and number of fields in schema (0)
   ```
   While the correct output is 
   ```
   1 2
   2 2
   ```
   


-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org