You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I have searched the existing issues, and I could not find an existing issue for this bug
Current Behavior
In time offset queries, the time constraint is applied to both the time spine subquery and the metric subquery. Here's the current SQL:
-- Compute Metrics via Expressions
SELECT
metric_time__day
, bookings_2_weeks_ago AS bookings_2_weeks_ago
FROM (
-- Join to Time Spine Dataset
-- Pass Only Elements:
-- ['bookings', 'metric_time__day']
-- Aggregate Measures
-- Compute Metrics via Expressions
SELECT
subq_12.metric_time__day AS metric_time__day
, SUM(subq_11.bookings) AS bookings_2_weeks_ago
FROM (
-- Date Spine
SELECT
ds AS metric_time__day
FROM ***************************.mf_time_spine subq_13
WHERE ds BETWEEN '2020-01-01' AND '2020-01-01'
) subq_12
INNER JOIN (
-- Read Elements From Semantic Model 'bookings_source'
-- Metric Time Dimension 'ds'
-- Constrain Time Range to [2020-01-01T00:00:00, 2020-01-01T00:00:00]
SELECT
DATE_TRUNC('day', ds) AS metric_time__day
, 1 AS bookings
FROM ***************************.fct_bookings bookings_source_src_10001
WHERE DATE_TRUNC('day', ds) BETWEEN '2020-01-01' AND '2020-01-01'
) subq_11
ON
subq_12.metric_time__day - INTERVAL 14 day = subq_11.metric_time__day
GROUP BY
subq_12.metric_time__day
) subq_17
Expected Behavior
Since the metric dates will be offset, the time constraint should not be applied in that subquery. The inner join to the time spine query will effectively apply that time constraint, instead.
-- Compute Metrics via Expressions
SELECT
metric_time__day
, bookings_2_weeks_ago AS bookings_2_weeks_ago
FROM (
-- Join to Time Spine Dataset
-- Pass Only Elements:
-- ['bookings', 'metric_time__day']
-- Aggregate Measures
-- Compute Metrics via Expressions
SELECT
subq_12.metric_time__day AS metric_time__day
, SUM(subq_11.bookings) AS bookings_2_weeks_ago
FROM (
-- Date Spine
SELECT
ds AS metric_time__day
FROM ***************************.mf_time_spine subq_13
WHERE ds BETWEEN '2020-01-01' AND '2020-01-01'
) subq_12
INNER JOIN (
-- Read Elements From Semantic Model 'bookings_source'
-- Metric Time Dimension 'ds'
-- Constrain Time Range to [2020-01-01T00:00:00, 2020-01-01T00:00:00]
SELECT
DATE_TRUNC('day', ds) AS metric_time__day
, 1 AS bookings
FROM ***************************.fct_bookings bookings_source_src_10001
) subq_11
ON
subq_12.metric_time__day - INTERVAL 14 day = subq_11.metric_time__day
GROUP BY
subq_12.metric_time__day
) subq_17
Steps To Reproduce
Query a time offset metric with a time constraint.
courtneyholcomb
changed the title
[Bug] Time constraint should be removed in metric subquery for time offset metrics
[SL-1357] [Bug] Time constraint should be removed in metric subquery for time offset metrics
Dec 5, 2023
Is this a new bug in metricflow?
Current Behavior
In time offset queries, the time constraint is applied to both the time spine subquery and the metric subquery. Here's the current SQL:
Expected Behavior
Since the metric dates will be offset, the time constraint should not be applied in that subquery. The inner join to the time spine query will effectively apply that time constraint, instead.
Steps To Reproduce
Query a time offset metric with a time constraint.
Relevant log output
No response
Environment
Which database are you using?
No response
Additional Context
No response
SL-1357
The text was updated successfully, but these errors were encountered: