Skip to content

audit dates are wrongly interpolated in plan #3966

@serkef

Description

@serkef

The audit dates are 1 more than they should.

Example example_audit.sql:

MODEL (
  name sqlmesh_poc.example_audit,
  kind INCREMENTAL_BY_TIME_RANGE(
    time_column(date_id, '%Y-%m-%d')
  ),
  cron '@daily',
  partitioned_by (date_id),
  audits [unique_combination_of_columns(columns=(date_id, col_id))]
);

WITH sample_table AS (
  SELECT
    DATE('2025-02-01') as date_id,
    1::INT64 as col_id,
)
SELECT
  date_id,
  col_id
FROM
  sample_table
WHERE
  date_id BETWEEN @start_ds AND @end_ds

Running: sqlmesh --gateway dev plan test_audit --start 2025-02-01 --end 2025-02-01 --skip-tests
I see this audit being run (notice the between dates includes 2025-02-02:

SELECT COUNT(*) FROM (SELECT `date_id` AS `date_id`, `col_id` AS `col_id` FROM (SELECT * FROM `sqlmesh__sqlmesh_poc`.`sqlmesh_poc__example_audit__3536497751` AS `sqlmesh_poc__example_audit__3536497751` WHERE `date_id` BETWEEN CAST('2025-02-01' AS DATE) AND CAST('2025-02-02' AS DATE)) AS `_q_0` WHERE TRUE GROUP BY `date_id`, `col_id` HAVING COUNT(*) > 1) AS `audit`

This doesn't happen if we run explicitly the audits:
sqlmesh --gateway dev audit --start 2025-02-01 --end 2025-02-01 --model sqlmesh_poc.example_audit
Runs:

SELECT COUNT(*) FROM (SELECT `date_id` AS `date_id`, `col_id` AS `col_id` FROM (SELECT * FROM `sqlmesh__sqlmesh_poc`.`sqlmesh_poc__example_audit__3536497751` AS `sqlmesh_poc__example_audit__3536497751` WHERE `date_id` BETWEEN CAST('2025-02-01' AS DATE) AND CAST('2025-02-01' AS DATE)) AS `_q_0` WHERE TRUE GROUP BY `date_id`, `col_id` HAVING COUNT(*) > 1) AS `audit`

Metadata

Metadata

Assignees

No one assigned

    Labels

    BugSomething isn't working

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions