superset: refactor(pinot) regression on area chart (DB engine error)

We are using a pinot table as a dataset, with a temporal column startDate in epoch_ms. Before the 3.0.0.rc4 version, we were using it to create the followin area chart :

areachart

Since the release of the 3.0.0.rc4 version, our chart is not loading :

DB engine Erreur : could not convert string to Timestamp

Before 3.0.0.rc4, the query generated by the simple area chart was :

SELECT DATETIMECONVERT(startDate, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS'), count(*) FROM "dataset" WHERE startDate >= 1694908800000 AND startDate < 1697500800000 GROUP BY DATETIMECONVERT(startDate, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS') ORDER BY count(*) DESC LIMIT 10000;

(1694908800000 and 1697500800000 are just examples of our time filter)

After the 3.0.0.rc4, the query that causes the error is :

SELECT CAST(DATE_TRUNC('day', CAST(DATETIMECONVERT((startDate/1000), '1:SECONDS:EPOCH', '1:SECONDS:EPOCH', '1:SECONDS') AS TIMESTAMP)) AS TIMESTAMP), count(*) FROM "dataset" WHERE startDate >= 1694908800000 AND startDate < 1697500800000 GROUP BY CAST(DATE_TRUNC('day', CAST(DATETIMECONVERT((startDate/1000), '1:SECONDS:EPOCH', '1:SECONDS:EPOCH', '1:SECONDS') AS TIMESTAMP)) AS TIMESTAMP) ORDER BY count(*) DESC LIMIT 10000;

The part :

DATETIMECONVERT((startDate/1000), '1:SECONDS:EPOCH', '1:SECONDS:EPOCH', '1:SECONDS')

seems odd because it literally said “convert seconds in seconds”, and we think it might causes the crash.

We think the problem is caused by : https://github.com/apache/superset/pull/24942

Thx for your help

superset version: 3.0.0.rc4

About this issue

Original URL
State: closed
Created 8 months ago
Reactions: 1
Comments: 41 (41 by maintainers)

Most upvoted comments

we changed all of our temporal field in our pinot table, so we skipped the problem completely. the thread can stay closed, thx

martin-raymond on Apr 9, 2024

i confirm all our charts are now ok. thx very much @zhaoyongjie

martin-raymond on Nov 13, 2023

@zhaoyongjie well, it seems to have solved the issue (but we have to use a calculated column as you adviced). we have a lots of charts to verify now, but it seems to work fine on the first

thank you very much @zhaoyongjie for your help. i will keep you informed here if it works on every charts, then i will close my issue.

martin-raymond on Nov 8, 2023

The query :

SELECT CAST(DATE_TRUNC('day', CAST(cast(dateHeureDebut as timestamp) AS TIMESTAMP)) AS TIMESTAMP),
       COUNT(*)
FROM "knada-indicateur-visite-v2"."knada-indicateur-visite-v2"
GROUP BY CAST(DATE_TRUNC('day', CAST(cast(dateHeureDebut as timestamp) AS TIMESTAMP)) AS TIMESTAMP)
ORDER BY COUNT(*) DESC
LIMIT 10000;

which works fine in pinot

@zhaoyongjie that is what you wanted ? and thank you very much for your help by the way !

martin-raymond on Oct 27, 2023