Superset | filter by last * results in wrong timestamp.

[wierdvanderhaar]

The issue is that when creating a chart in SuperSet using a filter (simple) on timestamp (ts) with either range (day/week/month) results in a query like this:

SELECT AVG(global_active_power) AS "AVG(global_active_power)"
FROM doc.power_consumption
WHERE ts >= 1707177600000.0
  AND ts < 1709683200000.0
ORDER BY "AVG(global_active_power)" DESC
LIMIT 1000;

Instead of

SELECT AVG(global_active_power) AS "AVG(global_active_power)"
FROM doc.power_consumption
WHERE ts >= 1707177600000
  AND ts < 1709683200000
ORDER BY "AVG(global_active_power)" DESC
LIMIT 1000;

The timestamp used has an extra (.0), which results in a timestamp in 56068 BC.

[amotl]

Thanks for reporting. It would be interesting if this can also be reproduced on behalf of the SQLAlchemy dialect in crate-python, where a corresponding flaw would "just need a fix", when applicable.

[wierdvanderhaar]

Created the table:

CREATE TABLE IF NOT EXISTS "doc"."power_consumption" (
   "ts" TIMESTAMP WITH TIME ZONE,
   "global_active_power" REAL
);

Inserted dummy rows using cr8

cr8 insert-fake-data --hosts https://USER:[email protected]:4200 --table "doc.power_consumption" --num-records 10000

Imported the table into SuperSet:

Created a Chart:

[hlcianfagna]

It would be interesting if this can also be reproduced on behalf of the SQLAlchemy dialect

Not sure if this is what you mean, but if I try the following the datetime renders as a string, not a number.

Base = declarative_base()
class Table1(Base):
    __tablename__ = 'table1'
    _id = sa.Column(sa.String, primary_key=True)
    field1 = Column(String)
    ts1 = Column(DateTime)
session=sessionmaker(bind=sa.create_engine("crate://localhost:4200", echo=True))()
Query=session.query(Table1.field1).filter(Table1.ts1 > datetime.datetime.now())
print (Query.statement.compile(dialect=session.bind.dialect, compile_kwargs={"literal_binds": True}))

[amotl]

Yeah something like that, we wanted to evaluate. However, we haven't exactly checked how query generation works in Superset, specifically on the spot in question.

In the following snippet, the datetime renders as a string, not a number.

Would it actually be correct in case of CrateDB? So, no bug in the dialect per se, because it does not matter if we send it over as a string or a number?

[hlcianfagna]

https://preset.atlassian.net/browse/PPR-714

[hlcianfagna]

apache/superset#27382 (comment)

[hlcianfagna]

This issue has been addressed with apache/superset#27567

[amotl]

Thanks a stack!