Automatic Casting to Float64 in Where clause Filters for Numeric Dimensions (Causing Clustering problems)

[HassanAtWecrunch]

Problem

Hi, is there any way to make sure that dimensions with type: number get converted to int instead of float or is there any way to disable automatic casting?

Related Cube.js schema

org_id: {
      sql: org_id,
      type: number,
    },


time_period_id: {
      sql: time_period_id,
      type: number,
    },

These dimensions generate SQL like this:

Related Cube.js generated SQL

AND (fact.org_id = CAST(4 AS FLOAT64)) AND (fact.time_period_id = CAST(108 AS FLOAT64))

Since my columns are in integer it breaks my clustering optimization when wrong casting is happened.

Thanks

[igorlukanin]

@HassanAtWecrunch Which data source type do you use?

it breaks my clustering optimization

Could you please elaborate here?

[HassanAtWecrunch]

I use Numeric data type for my numerical columns. BigQuery does not allow columns to cluster on if their data type is something other than

BIGNUMERIC, BOOL, DATE, DATETIME, GEOGRAPHY, INT64, NUMERIC, RANGE, STRING, and TIMESTAMP

What does cluster breaking mean:

BigQuery charges by billable bytes processed during query. Clustering helps to reduce that.

When i write

fact.org_id = 4 i can see my billable bytes being reduced.

If i write

fact.org_id = CAST(4 AS FLOAT64) the billable bytes DO NOT reduce.

[igorlukanin]

Thanks for explaining! I see that this behavior comes from this part of the code base but I'm not sure if removing the cast is the right thing to do (it might break something else):

cube/packages/cubejs-schema-compiler/src/adapter/BigqueryQuery.ts

Lines 24 to 32 in 6abbdb3

	public castParameter() {
	if (this.definition().type === 'boolean') {
	return 'CAST(? AS BOOL)';
	} else if (this.measure || this.definition().type === 'number') {
	// TODO here can be measure type of string actually
	return 'CAST(? AS FLOAT64)';
	}
	return '?';
	}

[HassanAtWecrunch]

Understood. Thanks for the help i@igorlukanin