[Bug] derived metrics query cannot group by none metric-time dimension

[zangma121]

Is this a new bug in metricflow?

• I believe this is a new bug in metricflow
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

1. i have build model iv_station.sql content as followed content
   select TO_DATE(i.dtime,'YYYY-MM-DD') as dtime, i.DP0073, o.briefname
   from {{ source('raw_data', 'iv_station_day') }} i,{{ source('raw_data', 'org') }} o
   where i.item = o.code
   DP0073 is profit field,briefname is the company name
   2.iv_station.yml defined model
   semantic_models:

• name: iv_station_day_semantic_model
  description: "company every day profilt semantic model"
  model: ref('iv_station')

  defaults:
  agg_time_dimension: dtime # time dimension

  entities:

  • name: composite_key_entity
    type: primary
    expr: "dtime || '_' || briefname"

  dimensions:

  • name: dtime
    type: time
    type_params:
    time_granularity: day # day

  • name: company_name
    type: categorical
    expr: briefname

  measures:

  • name: total_profit
    description: "profile summary"
    agg: sum
    expr: dp0073
    3.defined metrics.yml
    metrics:

• name: nz_profit
  label: "nz profit"
  type: SIMPLE
  type_params:
  measure:
  name: total_profit
  join_to_timespine: false

• name: nz_profit_compare_lastyear_simple
  label: "Year-on-year difference in net profit"
  type: derived
  description: "Difference between current net profit and the same period last year"
  type_params:
  expr: "nz_profit - nz_profit_compare_lastyear"
  metrics:
  - name: nz_profit
  - name: nz_profit
  offset_window: 1 year
  alias: nz_profit_compare_lastyear

4. when i usimg mf query group by composite_key_entity__company_name
   mf query --metrics nz_profit_compare_lastyear_simple --group-by composite_key_entity__company_name --where "{{ TimeDimension('metric_time') }} >= '2025-01-01' AND {{ TimeDimension('metric_time') }} <= '2025-01-31' and {{ Dimension('composite_key_entity__company_name') }}='test' "
   \ Initiating query…
   ERROR: Got error(s) during query resolution.

Error #1:
Message:

The query includes a metric 'nz_profit_compare_lastyear_simple' that specifies a time offset in input metrics: (PydanticMetricInput(name='nz_profit', filter=None, alias=None, offset_window=None, of

fset_to_grain=None), PydanticMetricInput(name='nz_profit', filter=None, alias='nz_profit_compare_lastyear', offset_window=PydanticMetricTimeWindow(count=1, granularity=TimeGranularity.YEAR), offset_to_grain=None)). However, group-by-items do not include 'metric_time'.

Query Input:

Query(['nz_profit_compare_lastyear_simple'], ['composite_key_entity__company_name']

Issue Location:

[Resolve Query(['nz_profit_compare_lastyear_simple'])]
  -> [Resolve Metric('nz_profit_compare_lastyear_simple')]

5.add group by metric_time
mf query --metrics nz_profit_compare_lastyear_simple --group-by composite_key_entity__company_name,metric_time --where "{{ TimeDimension('metric_time') }} >= '2025-01-01' AND {{ TimeDimension('metric_time') }} <= '2025-01-31' and {{ Dimension('composite_key_entity__company_name') }}='test' "
v Success 🦄 - query completed after 0.22 seconds
🕳 Query returned an empty result set
6. sql explain as:
WITH sma_10002_cte AS (
SELECT
DATE_TRUNC('day', dtime) AS metric_time__day
, briefname AS composite_key_entity__company_name
, dp0073 AS total_profit
FROM "chukaiping"."public"."iv_station" iv_station_day_semantic_model_src_10000
)

SELECT
metric_time__day AS metric_time__day
, composite_key_entity__company_name AS composite_key_entity__company_name
, nz_profit - nz_profit_compare_lastyear AS nz_profit_compare_lastyear_simple
FROM (
SELECT
COALESCE(subq_5.metric_time__day, subq_14.metric_time__day) AS metric_time__day
, COALESCE(subq_5.composite_key_entity__company_name, subq_14.composite_key_entity__company_name) AS composite_key_entity__company_name
, MAX(subq_5.nz_profit) AS nz_profit
, MAX(subq_14.nz_profit_compare_lastyear) AS nz_profit_compare_lastyear
FROM (
SELECT
metric_time__day
, composite_key_entity__company_name
, SUM(total_profit) AS nz_profit
FROM (
SELECT
metric_time__day
, composite_key_entity__company_name
, total_profit
FROM sma_10002_cte sma_10002_cte
) subq_1
WHERE metric_time__day >= '2025-01-01' AND metric_time__day <= '2025-01-31' and composite_key_entity__company_name='test'
GROUP BY
metric_time__day
, composite_key_entity__company_name
) subq_5
FULL OUTER JOIN (
SELECT
metric_time__day
, composite_key_entity__company_name
, SUM(total_profit) AS nz_profit_compare_lastyear
FROM (
SELECT
time_spine_src_10000.date_day AS metric_time__day
, sma_10002_cte.composite_key_entity__company_name AS composite_key_entity__company_name
, sma_10002_cte.total_profit AS total_profit
FROM chukaiping.public.all_days time_spine_src_10000
INNER JOIN
sma_10002_cte sma_10002_cte
ON
time_spine_src_10000.date_day - MAKE_INTERVAL(years => 1) = sma_10002_cte.metric_time__day
) subq_10
WHERE metric_time__day >= '2025-01-01' AND metric_time__day <= '2025-01-31' and composite_key_entity__company_name='test'
GROUP BY
metric_time__day
, composite_key_entity__company_name
) subq_14
ON
(
subq_5.composite_key_entity__company_name = subq_14.composite_key_entity__company_name
) AND (
subq_5.metric_time__day = subq_14.metric_time__day
)
GROUP BY
COALESCE(subq_5.metric_time__day, subq_14.metric_time__day)
, COALESCE(subq_5.composite_key_entity__company_name, subq_14.composite_key_entity__company_name)
) subq_15

7.actually i expected group by without metric_time just like

how to change metrics defintion or something else to fullfit my query

Expected Behavior

expect group by by companyname ,none metric-time,because have filtered time range
expect sql just like
WITH sma_10002_cte AS (
SELECT
DATE_TRUNC('day', dtime) AS metric_time__day
, briefname AS composite_key_entity__company_name
, dp0073 AS total_profit
FROM "chukaiping"."public"."iv_station" iv_station_day_semantic_model_src_10000
)

SELECT

composite_key_entity__company_name AS composite_key_entity__company_name
, nz_profit - nz_profit_compare_lastyear AS nz_profit_compare_lastyear_simple
FROM (
SELECT

COALESCE(subq_5.composite_key_entity__company_name, subq_14.composite_key_entity__company_name) AS composite_key_entity__company_name
, MAX(subq_5.nz_profit) AS nz_profit
, MAX(subq_14.nz_profit_compare_lastyear) AS nz_profit_compare_lastyear

FROM (
SELECT
metric_time__day
, composite_key_entity__company_name
, SUM(total_profit) AS nz_profit
FROM (
SELECT
metric_time__day
, composite_key_entity__company_name
, total_profit
FROM sma_10002_cte sma_10002_cte
) subq_1
WHERE metric_time__day >= '2025-01-01' AND metric_time__day <= '2025-01-31' and composite_key_entity__company_name='test'
GROUP BY
metric_time__day
, composite_key_entity__company_name
) subq_5
FULL OUTER JOIN (
SELECT

   composite_key_entity__company_name
  , SUM(total_profit) AS nz_profit_compare_lastyear
FROM (
  SELECT
    time_spine_src_10000.date_day AS metric_time__day
    , sma_10002_cte.composite_key_entity__company_name AS composite_key_entity__company_name
    , sma_10002_cte.total_profit AS total_profit
  FROM chukaiping.public.all_days time_spine_src_10000
  INNER JOIN
    sma_10002_cte sma_10002_cte
  ON
    time_spine_src_10000.date_day - MAKE_INTERVAL(years => 1) = sma_10002_cte.metric_time__day
) subq_10
WHERE metric_time__day >= '2025-01-01' AND metric_time__day <= '2025-01-31'   and composite_key_entity__company_name='test'
GROUP BY
  metric_time__day
  , composite_key_entity__company_name

) subq_14
ON
(
subq_5.composite_key_entity__company_name = subq_14.composite_key_entity__company_name
)
GROUP BY
COALESCE(subq_5.composite_key_entity__company_name, subq_14.composite_key_entity__company_name)
) subq_15

Steps To Reproduce

1.with metricflow latest version
pip list
dbt-adapters 1.16.0
dbt-common 1.25.1
dbt-core 1.9.8
dbt-duckdb 1.9.4
dbt-extractor 0.6.0
dbt-metricflow 0.8.2
dbt-postgres 1.9.0
dbt-protos 1.0.335
dbt-semantic-interfaces 0.7.5
deepdiff 7.0.1

Relevant log output

Environment

- OS: window11
- Python 3.10.0
- dbt:1.9.8
- metricflow:0.207.3

Which database are you using?

postgres

Additional Context

No response