BigQuery: no matching signature error for calculated measures on `count_distinct_approx` measures

[prratek]

Describe the bug
When defining a calculated measure in Cube that divides two measures of type count_distinct_approx, Cube generates invalid SQL. It attempts to divide two HLL sketches, which are of type BYTES, which results in this error:

No matching signature for operator / for argument types: BYTES, BYTES

To Reproduce
See the minimally reproducible schema below. When Cube attempts to generate a pre-aggregation you should see the error above.

Expected behavior
Current Query:

SELECT
  `my_cube`.my_dimension `my_cube__my_dimension`, 
  HLL_COUNT.INIT(CASE WHEN (`my_cube`.viewed) THEN `my_cube`.user_id END) / HLL_COUNT.INIT(`my_cube`.user_id) `my_cube__ctr`
FROM
    (
  SELECT * 
  FROM my_table
)  
GROUP BY 1, 2

Expected Query:

SELECT
  `my_cube`.my_dimension `my_cube__my_dimension`, 
  HLL_COUNT.EXTRACT(HLL_COUNT.INIT(CASE WHEN (`my_cube`.viewed) THEN `my_cube`.user_id END)) /
    HLL_COUNT.EXTRACT(HLL_COUNT.INIT(`my_cube`.user_id) ) `my_cube__ctr`
FROM
    (
  SELECT * 
  FROM my_table
)  
GROUP BY 1, 2

Screenshots
If applicable, add screenshots to help explain your problem.

Minimally reproducible Cube Schema
In case your bug report is data modeling related please put your minimally reproducible Cube Schema here.
You can use selects without tables in order to achieve that as follows.

cube(`my_cube`, {
  sql: `
  select * from my_table
  `,
  measures: {
    clicks: {
      sql: `user_id`,
      type: `count_distinct_approx`,
      filters: [{ sql: `${CUBE.clicked}` }]
    },
    views: {
      sql: `user_id`,
      type: `count_distinct_approx`,
      filters: [{ sql: `${CUBE.viewed}` }]
    },
    ctr: {
      sql: `${clicks} / ${views}`,
      type: `number`
    },
  },
  dimensions: {
    my_dimension: {
      sql: `my_dimension`,
      type: `string`,
    },
  },
});

Version:
0.33.28

Additional context
Add any other context about the problem here.

[igorlukanin]

Hi @prratek 👋

I think this is not actually a bug, it's a misconfiguration. Long story short, ctr should not be used in a pre-aggregation. Instead, clicks and views should. This is because ctr is. calculated measure. Cube Store can calculate it on-the-fly based on clicks and views and this is why they should be added in a pre-aggregation instead.

Please take a look at the following data model:

cube(`my_cube`, {
  sql: `SELECT 123 AS user_id`,

  measures: {
    clicks: {
      sql: `user_id`,
      type: `count_distinct_approx`
    },

    views: {
      sql: `user_id`,
      type: `count_distinct_approx`
    },
    
    ctr: {
      sql: `${clicks} / ${views}`,
      type: `number`
    },

    // This measure is added to work around this bug only:
    // https://github.com/cube-js/cube/issues/8916$0
    count: {
      type: `count`
    }
  },

  pre_aggregations: {
    main: {
      measures: [
        clicks,
        views,
        count,
        // ctr
      ]
    }
  }
})

With this data model, if you run a query with ctr, it will work flawlessly:

Correct SQL is generated as well:

However, if you remove the comment on ctr in the pre-aggregation definition, you will get the error is shown above.

I hope this helps!