Error while query using pre-aggregation on multi-tenant setup #9024
Description
Describe the bug
I have two cube schema, Order and Customer which is created for multi-tenant structure. I have added company_id check in both of the schemas. After adding FILTER_PARAMS on order_date, the pre-aggregation plan is taking order_date parameter from second schema (Which is 3). This works fine if I remove pre_aggregation from Order schema.
To Reproduce
cube(`Order`, {
title: "Order",
sql: `SELECT * FROM public.order WHERE ${SECURITY_CONTEXT.companyId.filter('company_id')} AND ${FILTER_PARAMS.Order.order_date.filter('order_date')}`,
pre_aggregations: {
main: {
type: `original_sql`,
external: false,
time_dimension: CUBE.order_date,
partition_granularity: `month`,
allow_non_strict_date_range_match: true,
refreshKey: {
sql: `SELECT MAX(updated_date) FROM public.order`
},
build_range_start: {
sql: `SELECT date_trunc('month', NOW()) - interval '2 year'`
},
build_range_end: {
sql: `SELECT date_trunc('month', NOW()) + interval '1 month'`
}
}
},
joins: {
Customer: {
sql: `${CUBE}.customer = ${Customer}.id`,
relationship: `belongsTo`
}
},
});
cube(`Customer`, {
sql: `select * from public.customer where ${SECURITY_CONTEXT.companyId.filter('company_id')}`,
pre_aggregations: {
main: {
type: `original_sql`,
external: false,
refresh_key: {
every: `1 hour`,
sql: `SELECT MAX(updated_date) FROM public.customer where ${SECURITY_CONTEXT.companyId.filter('company_id')}`
},
}
},
});
REST API:
{"query":{"limit":10,"offset":0,"order":{"Order.order_date":"desc"},"timeDimensions":[{"dimension":"Order.order_date","granularity":"day","dateRange":"This Year"}],"dimensions":["ViewCustomer.id"],"measures":["Order.count","Order.value","Order.discount"],"filters":[]},"queryType":"multi"}
Error:
{error: "Error: invalid input syntax for type timestamp with time zone: "3"",…}Version:
Checked on all versions after 1.0.0
Additional context
Detailed Error Log:
Error while querying: {"queueId":18,"processingId":18,"queueSize":2,"duration":21,"queryKey":[["CREATE TABLE pre_3.order_main20241001 AS SELECT id, ref_id, beatplan, customer_id, order_date **** FROM public.order WHERE company_id = $1 AND (("order_date" >= $2::timestamptz AND "order_date" <= $3::timestamptz))",[3,3,"2023-01-01T00:00:00.000"],{}],[[{"refresh_key":"173348762"}]]],"queuePrefix":"SQL_PRE_AGGREGATIONS_CUBEJS_APP_3_default","requestId":"90de698b-0544-4efc-a1b1-b780c4b0e2ae-span-1","timeInQueue":229,"preAggregationId":"Order.main","newVersionEntry":{"table_name":"pre_3.order_main20241001","structure_version":"pnrp0c33","content_version":"pxy4kxp1","last_updated_at":1733487624925,"naming_version":2},"preAggregation":{"preAggregationId":"Order.main","timezone":"UTC","timestampFormat":"YYYY-MM-DD[T]HH:mm:ss.SSS[Z]","timestampPrecision":3,"tableName":"pre_3.order_main20241001","invalidateKeyQueries":[["SELECT FLOOR((UNIX_TIMESTAMP()) / 10) as refresh_key",[],{"external":true,"renewalThreshold":10}]],"type":"originalSql","external":true,"previewSql":["SELECT * FROM pre_3.order_main20241001 LIMIT 1000",[],{}],"preAggregationsSchema":"pre_3","loadSql":["CREATE TABLE pre_3.order_main20241001 AS SELECT **** WHERE company_id = $1 AND (("order_date" >= $2::timestamptz AND "order_date" <= $3::timestamptz))",[3,3,"2023-01-01T00:00:00.000"],{}],"sql":["SELECT * FROM public.order WHERE company_id = $1 AND (("order_date" >= $2::timestamptz AND "order_date" <= $3::timestamptz))",[3,3,"2023-01-01T00:00:00.000"],{}],"outputColumnTypes":null,"uniqueKeyColumns":null,"aggregationsColumns":[],"dataSource":"default","partitionGranularity":"month","preAggregationStartEndQueries":[["SELECT date_trunc('month', NOW()) - interval '2 year'",[]],["SELECT date_trunc('month', NOW()) + interval '1 month'",[]]],"matchedTimeDimensionDateRange":["2023-01-01T00:00:00.000","2023-12-31T23:59:59.999"],"readOnly":false,"structureVersionLoadSql":["CREATE TABLE pre_3.order_main20241001 AS SELECT * FROM public.order WHERE company_id = $1 AND (("dateTime" >= $2::timestamptz AND "dateTime" <= $3::timestamptz))",[3,3,"2023-01-01T00:00:00.000"],{}],"buildRangeStart":"2023-10-01T00:00:00.000","buildRangeEnd":"2023-10-31T23:59:59.999","sealAt":"2023-10-31T23:59:59.999Z"},"addedToQueueTime":1733487624934,"error":"error: invalid input syntax for type timestamp with time zone: "3"\n at Parser.parseErrorMessage (/cube/node_modules/pg-protocol/src/parser.ts:369:69)\n at Parser.handlePacket (/cube/node_modules/pg-protocol/src/parser.ts:188:21)\n at Parser.parse (/cube/node_modules/pg-protocol/src/parser.ts:103:30)\n at TLSSocket. (/cube/node_modules/pg-protocol/src/index.ts:7:48)\n at TLSSocket.emit (node:events:519:28)\n at addChunk (node:internal/streams/readable:559:12)\n at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)\n at TLSSocket.Readable.push (node:internal/streams/readable:390:5)\n at TLSWrap.onStreamRead (node:internal/stream_base_commons:191:23)"}
This has added second parameter from second cube schema which is 3. How to avoid this?