[I] DB clean cli command on dag_run not working as expected [airflow]

["romsharon98 (via GitHub)" <gi...@apache.org>]

romsharon98 opened a new issue, #37689:
URL: https://github.com/apache/airflow/issues/37689

   ### Apache Airflow version
   
   Other Airflow 2 version (please specify below)
   
   ### If "Other Airflow 2 version" selected, which one?
   
   2.7.1
   
   ### What happened?
   
   I am running
   ```bash
   airflow db clean -y -v -t dag_run --clean-before-timestamp 26-11-2023
   ```
   the output of this command is
   ```sql
   SELECT base.*
   FROM dag_run AS base LEFT OUTER JOIN (SELECT dag_id, max(dag_run.start_date) AS max_date_per_group
   FROM dag_run
   WHERE external_trigger = false GROUP BY dag_id) AS latest ON base.dag_id = latest.dag_id AND base.start_date = max_date_per_group
   WHERE base.start_date < :start_date_1 AND max_date_per_group IS NULL
   Checking table dag_run
   Found 0 rows meeting deletion criteria.
   ```
   
   I have a row in the db:
   | dag_id | queued_at | execution_date | start_date | end_date | last_scheduling_decision | updated_at
   | :---: | :---: | :---: | :---: | :---: | :---: | :---: | 
   | airflow-test | 2023-10-23 13:14.. | 2023-07-23 14:35:00.. | 2023-10-23 13:14.. | 2023-10-23 13:15.. |2023-10-23 13:15.. | 2023-10-23 13:15.. 
   
   this row is not deleted
   
   
   ### What you think should happen instead?
   
   I think the row above should be deleted.
   this condition in the query is looking strange and I don't understand the purpose of it 
   ```sql
   base.start_date = max_date_per_group
   ```
   
   also I think the condition should use `updated_at` and not `start_date`
   
   ### How to reproduce
   
   run a dag with old start date.
   run db clean with a date after the start_date
   check the dag is not deleted from `dag_run` table
   
   ### Operating System
   
   linux
   
   ### Versions of Apache Airflow Providers
   
   _No response_
   
   ### Deployment
   
   Official Apache Airflow Helm Chart
   
   ### Deployment details
   
   _No response_
   
   ### Anything else?
   
   _No response_
   
   ### Are you willing to submit PR?
   
   - [X] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
   


-- 
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@airflow.apache.org.apache.org

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

Re: [I] DB clean cli command on dag_run not working as expected [airflow]

["shahar1 (via GitHub)" <gi...@apache.org>]

shahar1 commented on issue #37689:
URL: https://github.com/apache/airflow/issues/37689#issuecomment-2374823297

   > Hi @shahar1 - I'm working on the Issue #42003 and I can take a look at this issue as well. Can you pls have the ticket assigned to me? I'll take a look at it. Thanks!
   
   I assigned you, good luck :)


-- 
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@airflow.apache.org

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

Re: [I] DB clean cli command on dag_run not working as expected [airflow]

["pratik-m (via GitHub)" <gi...@apache.org>]

pratik-m commented on issue #37689:
URL: https://github.com/apache/airflow/issues/37689#issuecomment-2374819675

   Hi @shahar1  - I'm working on the Issue #42003 and I can take a look at this issue as well. Can you pls have the ticket assigned to me? I'll take a look at it. 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@airflow.apache.org

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