cubestore preAggregations Unsupported CAST from Utf8 to Timestamp(Nanosecond, None)

[rongfengliang]

Describe the bug

Unsupported CAST from Utf8 to Timestamp(Nanosecond, None)

Screenshots

Query: 'SELECT
      `hr_sea_approve_statistics`.approverid `hr_sea_approve_statistics__approverid`, `hr_sea_approve_statistics`.avg_order_id `hr_sea_approve_statistics__avg_order_id`, `hr_sea_approve_statistics`.max_instance_time `hr_sea_approve_statistics__max_instance_time`, count(*) `hr_sea_approve_statistics__count`, sum(`hr_sea_approve_statistics`.instance_count) `hr_sea_approve_statistics__instance_count`
    FROM
      dev_pre_aggregations.hr_sea_approve_statistics_main_zkuw4aso_xaclbdwo_1g8rvge AS `hr_sea_approve_statistics`  WHERE (`hr_sea_approve_statistics`.start_date >= to_timestamp('2021-05-01T00:00:00.000') AND `hr_sea_approve_statistics`.start_date <= to_timestamp('2021-05-01T23:59:59.999')) GROUP BY 1, 2, 3 ORDER BY 4 DESC LIMIT 10000'
2021-05-02 01:19:27,290 TRACE [cubestore::queryplanner] Logical Plan: Limit: 10000
  Sort: #hr_sea_approve_statistics__count DESC NULLS FIRST
    Projection: #hr_sea_approve_statistics__approverid, #hr_sea_approve_statistics__avg_order_id, #hr_sea_approve_statistics__max_instance_time, #COUNT(UInt8(1)) AS hr_sea_approve_statistics__count, #SUM(instance_count) AS hr_sea_approve_statistics__instance_count
      Aggregate: groupBy=[[#hr_sea_approve_statistics.approverid AS hr_sea_approve_statistics__approverid, #hr_sea_approve_statistics.avg_order_id AS hr_sea_approve_statistics__avg_order_id, #hr_sea_approve_statistics.max_instance_time AS hr_sea_approve_statistics__max_instance_time]], aggr=[[COUNT(UInt8(1)), SUM(#hr_sea_approve_statistics.instance_count)]]
        Filter: #hr_sea_approve_statistics.start_date GtEq totimestamp(Utf8("2021-05-01T00:00:00.000")) And #hr_sea_approve_statistics.start_date LtEq totimestamp(Utf8("2021-05-01T23:59:59.999"))
          TableScan: dev_pre_aggregations.hr_sea_approve_statistics_main_zkuw4aso_xaclbdwo_1g8rvge projection=Some([0, 2, 3, 5, 8]), filters=[#start_date GtEq totimestamp(Utf8("2021-05-01T00:00:00.000")), #start_date LtEq totimestamp(Utf8("2021-05-01T23:59:59.999"))]
2021-05-02 01:19:27,291 TRACE [cubestore::queryplanner::planning] Extracted partition filter is PartitionFilter { min_max: [] }
2021-05-02 01:19:27,291 TRACE [cubestore::queryplanner::planning] Pruned 0 of 1 partitions
2021-05-02 01:19:27,292 TRACE [cubestore::http] Sending web socket response
2021-05-02 01:19:27,603 TRACE [cubestore::http] Received web socket message
2021-05-02 01:19:27,604 TRACE [cubestore::sql] Query: 'SELECT
      `hr_sea_approve_statistics`.approverid `hr_sea_approve_statistics__approverid`, `hr_sea_approve_statistics`.avg_order_id `hr_sea_approve_statistics__avg_order_id`, `hr_sea_approve_statistics`.max_instance_time `hr_sea_approve_statistics__max_instance_time`, count(*) `hr_sea_approve_statistics__count`, sum(`hr_sea_approve_statistics`.instance_count) `hr_sea_approve_statistics__instance_count`
    FROM
      dev_pre_aggregations.hr_sea_approve_statistics_main_zkuw4aso_xaclbdwo_1g8rvge AS `hr_sea_approve_statistics`  WHERE (`hr_sea_approve_statistics`.start_date >= to_timestamp('2021-05-01T00:00:00.000') AND `hr_sea_approve_statistics`.start_date <= to_timestamp('2021-05-01T23:59:59.999')) GROUP BY 1, 2, 3 ORDER BY 4 DESC LIMIT 10000'
2021-05-02 01:19:27,605 TRACE [cubestore::queryplanner] Logical Plan: Limit: 10000
  Sort: #hr_sea_approve_statistics__count DESC NULLS FIRST
    Projection: #hr_sea_approve_statistics__approverid, #hr_sea_approve_statistics__avg_order_id, #hr_sea_approve_statistics__max_instance_time, #COUNT(UInt8(1)) AS hr_sea_approve_statistics__count, #SUM(instance_count) AS hr_sea_approve_statistics__instance_count
      Aggregate: groupBy=[[#hr_sea_approve_statistics.approverid AS hr_sea_approve_statistics__approverid, #hr_sea_approve_statistics.avg_order_id AS hr_sea_approve_statistics__avg_order_id, #hr_sea_approve_statistics.max_instance_time AS hr_sea_approve_statistics__max_instance_time]], aggr=[[COUNT(UInt8(1)), SUM(#hr_sea_approve_statistics.instance_count)]]
        Filter: #hr_sea_approve_statistics.start_date GtEq totimestamp(Utf8("2021-05-01T00:00:00.000")) And #hr_sea_approve_statistics.start_date LtEq totimestamp(Utf8("2021-05-01T23:59:59.999"))
          TableScan: dev_pre_aggregations.hr_sea_approve_statistics_main_zkuw4aso_xaclbdwo_1g8rvge projection=Some([0, 2, 3, 5, 8]), filters=[#start_date GtEq totimestamp(Utf8("2021-05-01T00:00:00.000")), #start_date LtEq totimestamp(Utf8("2021-05-01T23:59:59.999"))]
2021-05-02 01:19:27,605 TRACE [cubestore::queryplanner::planning] Extracted partition filter is PartitionFilter { min_max: [] }

Version:
[e.g. 0.27.4 ]

some links apache/arrow#9449

[paveltiunov]

Hey @rongfengliang ! We indeed need a better way of handling it. However, the issue origin is the lack of timestamp cast during rollup preparation. We actually want to avoid any implicit UTF8 to timestamp casts during query time as those are really slow. So you just need to add one in your time dimension sql in cube.js schema.

[rongfengliang]

@paveltiunov thanks , i have add time dimension sql in cube.js schema . if i add time filter will cause this problem

[paveltiunov]

@rongfengliang Could you please share your cube.js schema?

[rongfengliang]

@i found this maybe caused by data type mapper (create table in cubestore)

i use postgres

this is my schema

cube(`Demoapp`, {
  sql: `SELECT * FROM public.demoapp`,
  
  joins: {
    
  },
  preAggregations: {
    main: {
      type: `originalSql`,
      external: true
    },
  },
  measures: {
    count: {
      type: `count`,
      drillMembers: [id, name, startdate]
    }
  },
  
  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },
    
    name: {
      sql: `name`,
      type: `string`
    },
    
    startdate: {
      sql: `startdate`,
      type: `time`
    },
    
    insertdata: {
      sql: `insertdata`,
      type: `time`
    },
    
    myinfo: {
      sql: `myinfo`,
      type: `time`
    }
  },
  
  dataSource: `default`
});

my tabel define

CREATE TABLE demoapp (
    id integer,
    name character varying,
    startdate date,
    insertdata time without time zone,
    myinfo timestamp without time zone
);

if i filter with myinfo is ok, startdate && insertdata will cause convert exception

cubestore table info
startdate as string ,the same as insertdata

[paveltiunov]

@rongfengliang Gotcha. Generally speaking, Cube Store is designed to store rollups and not originalSql pre-aggregations. We're still going to support originalSql as intermediate step during rollup preparation but storing originalSql in Cube Store is out of our roadmap scope.

[rongfengliang]

@paveltiunov thanks !!!