Error while query using pre-aggregation on multi-tenant setup

[alokthakur142]

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?

[igorlukanin]

Hi @alokthakur142 👋

The source of the issue is that the SECURITY_CONTEXT variable is used in your data model. Note that its use is deprecated: https://cube.dev/docs/reference/data-model/context-variables#security_context

Instead, please use COMPILE_CONTEXT.

Also, please don't forget to define context_to_app_id and scheduled_refresh_contexts configuration parameters in your configuration file. It would help.

[alokthakur142]

This seems to be working. Thank you @igorlukanin