Drill-to-detail on a table chart with time grain = month only returns records from the first day of month

sfirke commented 1 year ago

Drill to detail on a month should return all records from that month. On a table chart, it returns only records from the first day of the month.

How to reproduce the bug

Create an aggregate table chart with a datetime variable as a dimension
Select Time Grain = Month
On dashboard, Drill to Detail by Month.

Expected results

See all results for that month (or month & other variables, if drilling by all).

Actual results

Only records matching the first day of the month are returned. I ran a trace on my data warehouse and can see the SQL query is only asking for the first day of the month:

SELECT TOP 50000 COUNT(*) AS [COUNT(*)]
FROM dbo.[myDataTable]
WHERE date_completed >= CONVERT(DATE, '2023-01-01', 23)
  AND date_completed < CONVERT(DATE, '2023-04-27', 23)
  AND date_completed = N'2023-03-01T00:00:00.000Z'

(The first two date_completed lines come from a dashboard native filter time range).

Screenshots

How I'm drilling:

Chart setup:

Environment

(please complete the following information):

browser type and version: Firefox
superset version: 4.0.2 (first noticed this in 2.1.0)
Data warehouse: Microsoft SQL Server

rusackas commented 6 months ago

Well, you know the "we don't support 2.x" party line I'm using ;) Is this still a thing in 3.x?

sfirke commented 6 months ago

@rusackas Yep I just updated the original post to indicate I've validated it in 3.1.1.

rusackas commented 3 weeks ago

I assume this one is still happening in 4.x, but it's getting a little stale. Seems not many people are running into this.

sfirke commented 2 weeks ago

Yes, I confirmed that this is still happening in 4.0.2. I updated the issue title to be more specific that this only affects table charts.

I tried to figure out where in the codebase changes would need to be made, I wonder if it's here since it only affects table charts and mine would have a pivot operation: https://github.com/apache/superset/blob/5bebcf61fefe2af7ed32f822ad1d86589ff4c9d2/superset/common/query_context_factory.py#L157

Also kind of wonder if it could be SQL Server-specific, maybe someone using a different data warehouse could try to validate.

apache / superset