Error when use word "term" in schema

[frimuchkov]

Hi!

In some cases, when we use field Report."adTerm" in reports, Cube say thant there is no Report."ad" (!) field.

Doesn't work:

AND (Report."adTerm" LIKE '%First%' OR Report."campaign" = 'main_campaign')

Works fine:

AND (Report."campaign" = 'main_campaign' OR Report."adTerm" LIKE '%First%')

Original query:

SELECT
    'none' as "first_interval_dimension",
    MEASURE(Report.cost) as "first_interval_cost",
FROM Report
WHERE Report."date" >= '2022-09-09' AND  Report."date" <= '2022-10-06'
    AND (adTerm LIKE '%First%' OR campaign = 'main_campaign')
ORDER BY first_interval_sessions DESC NULLS LAST LIMIT 25 OFFSET 0

Error:

Internal: Error during planning: No field named 'report.ad'.
Valid fields are 'report__ad_account', 'report__ad_content', 'report__ad_platform',
'report__boost', 'report__campaign', 'report__city', 'report__country',
'report__data_source_id', 'report__date', 'report__device',
'report__domain', 'report__medium', 'report__source',
'report__source_medium', 'report__date_day', 'report__clicks'

As you can see, there is no report__ad_term in error, but we have it in our Cube and if we use adTerm before campaign everething works fine.

[frimuchkov]

BTW, noticed, we don't use Report."adTerm" in in preaggregation

[gandronchik]

Hi @frimuchkov. I will investigate and come back soon. Thank you 🤝

[igorlukanin]

Hi @frimuchkov 👋

I've tried to reproduce this with the following data model but wasn't able to:

cubes:
  - name: meta_prefix
    sql: SELECT * FROM tbl WHERE (Report."adTerm" LIKE '%First%' OR Report."campaign" = 'main_campaign')

    measures:
      - name: count
        type: count

It works just fine:

Could you please help me reproduce this?

[igorlukanin]

It looks like this has been resolved. Closing for now, please feel free to reopen if needed.