Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Time dimension on a runningTotal measure on Snowflake mixes quoted and unquoted columns #3452

Open
leogodin217 opened this issue Sep 17, 2021 · 3 comments
Open

Time dimension on a runningTotal measure on Snowflake mixes quoted and unquoted columns #3452

leogodin217 opened this issue Sep 17, 2021 · 3 comments
Labels
bug good first issue help wanted

Comments

@leogodin217
Copy link

@leogodin217 leogodin217 commented Sep 17, 2021

Describe the bug
Using a time dimension on a runningTotal measure on Snowflake mixes quoted and unquoted columns in the query. This fails the query, because Snowflake has specific rules about quoted columns. Specifically:

  • All unquoted column names are treated as upper case
  • Quoted column names are case sensitive.

So "date_from" <> date_from

To Reproduce
Steps to reproduce the behavior:

  1. Create a runningTotal measure in a cube with Snowflake as the database
  2. In the sandbox add that measure and a time dimension
  3. Run the chart and see the error
  4. Update start and end dates in the query (these are parameterized near the bottom of the query)
  5. Run the query on Snowflake
  6. See the error: SQL compilation error: error line 52 at position 115 invalid identifier '"OFFERINGS.firstTradeDate_series"."date_to"'

Expected behavior
It should not create an error. I would suggest adding the column aliases to "OFFERINGS.firstTradeDate_series" and quote date_to and date_from. When I do that, the query runs fine.

Screenshots
image

Version:
28.32 according to running /usr/local/bin/cubejs --version on the Latest Docker version

Additional context

This is the generated query. Notice that line 2 quotes "date_from", but but when the aliases are created, they are not quoted. In snowflake, this means they are not the same column.

Also,
Snowflake requires the column aliases for "OFFERINGS.firstTradeDate_series". I tested this and was surprised. When we add the column type in the select query, the parent subquery does not inherit the column name. I pasted a simpler query below to show what Snowflake expects.

SELECT
  "OFFERINGS.firstTradeDate_series"."date_from" "o_f_f_e_r_i_n_g_s__first_trade_date_month",
  sum("o_f_f_e_r_i_n_g_s__cumulative_num_offerings") "o_f_f_e_r_i_n_g_s__cumulative_num_offerings"
FROM
  (
    SELECT
      date_from :: timestamp,
      date_to :: timestamp
    FROM
      (
        VALUES
          (
            '2021-07-01T00:00:00.000',
            '2021-07-31T23:59:59.999'
          ),
          (
            '2021-08-01T00:00:00.000',
            '2021-08-31T23:59:59.999'
          ),
          (
            '2021-09-01T00:00:00.000',
            '2021-09-30T23:59:59.999'
          )
      ) AS dates (date_from, date_to)
  ) AS "OFFERINGS.firstTradeDate_series"
  LEFT JOIN (
    SELECT
      date_trunc(
        'MONTH',
        CONVERT_TIMEZONE(
          'UTC',
          "o_f_f_e_r_i_n_g_s_cumulative_num_offerings_cumulative___o_f_f_e_r_i_n_g_s"."FIRST_TRADE_DATE" :: timestamp_tz
        ) :: timestamp_ntz
      ) "o_f_f_e_r_i_n_g_s__first_trade_date_month",
      sum(count(*)) "o_f_f_e_r_i_n_g_s__cumulative_num_offerings"
    FROM
      "DATALAB_STG"."OFFERINGS" AS "o_f_f_e_r_i_n_g_s_cumulative_num_offerings_cumulative___o_f_f_e_r_i_n_g_s"
    WHERE
      (
        CONVERT_TIMEZONE(
          'UTC',
          "o_f_f_e_r_i_n_g_s_cumulative_num_offerings_cumulative___o_f_f_e_r_i_n_g_s"."FIRST_TRADE_DATE" :: timestamp_tz
        ) :: timestamp_ntz >= CONVERT_TIMEZONE('UTC', ? :: timestamp_tz :: timestamp_tz) :: timestamp_ntz
        AND CONVERT_TIMEZONE(
          'UTC',
          "o_f_f_e_r_i_n_g_s_cumulative_num_offerings_cumulative___o_f_f_e_r_i_n_g_s"."FIRST_TRADE_DATE" :: timestamp_tz
        ) :: timestamp_ntz <= ? :: timestamp
      )
    GROUP BY
      1
  ) AS "o_f_f_e_r_i_n_g_s_cumulative_num_offerings_cumulative__base" ON "o_f_f_e_r_i_n_g_s_cumulative_num_offerings_cumulative__base"."o_f_f_e_r_i_n_g_s__first_trade_date_month" >= '2021-07-01T00:00:00.000'
  AND "o_f_f_e_r_i_n_g_s_cumulative_num_offerings_cumulative__base"."o_f_f_e_r_i_n_g_s__first_trade_date_month" <= "OFFERINGS.firstTradeDate_series"."date_to"
GROUP BY
  1
LIMIT
  10000

select
    letter,
    number
from (
    select
        letter :: varchar,
        number :: int
    from (
        values
            ('a', '1'),
            ('b', '2')
    ) as data(letter, number)
) as data2 (letter, number) -- When column types are specified for letter and number, this is needed
@paveltiunov paveltiunov added the bug label Sep 20, 2021
@paveltiunov
Copy link
Member

@paveltiunov paveltiunov commented Sep 20, 2021

@leogodin217 Hey Leo! Thanks for posting it! As a workaround, you can use rolling window rollup.

@paveltiunov paveltiunov added help wanted good first issue labels Sep 20, 2021
@github-actions
Copy link

@github-actions github-actions bot commented Sep 20, 2021

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you.
If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines.
You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

@leogodin217
Copy link
Author

@leogodin217 leogodin217 commented Sep 20, 2021

Rolling window is a good workaround. Unfortunately, I'm more of a Python programmer, so I will not be able to fix this myself.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug good first issue help wanted
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@leogodin217 @paveltiunov