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

[sfirke]

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

1. Create an aggregate table chart with a datetime variable as a dimension
2. Select Time Grain = Month
3. 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]

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

[sfirke]

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

[rusackas]

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]

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:

superset/superset/common/query_context_factory.py

Line 157 in 5bebcf6

if post_processing.get("operation") == "pivot":

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

[nemosdo]

I have the same problem, please let me know if you have any progress.