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: Apache Superset Drill-to-detail on a table chart with time grain = month only returns records from the first day of month

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

Environment

(please complete the following information):

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

Comment From: rusackas

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

Comment From: sfirke

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

Comment From: 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.

Comment From: 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: 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.

Comment From: nemosdo

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

Comment From: rusackas

I'm assuming this is still happening in 4.1.2? This has gone silent for a long time, and is a bit at-risk of being closed as inactive, but it sounds like a legit issue worth paying attention to.

Comment From: sfirke

I just validated that it persists in 4.1.2, I'll update the top post.

@nemosdo what is your data warehouse? I'd like to know if this is specific to SQL Server or affects all kinds of DBs.

Comment From: sfirke

The query for the table start begins with:

SELECT TOP 10000 DATEADD(MONTH, DATEDIFF(MONTH, 0, date_completed), 0) AS date_completed, full_name AS full_name, sum(credit_hours) AS [Sum of Credit Hours]

I'm guessing it's that DATEADD bit - it does the grouping for the table aggregation, then unfortunately also gets passed to the drilling query. Might require a rewrite to address.

Comment From: Samuelinto

Good afternoon, My group and I are students in the Software Engineering (CSCD01) course at the University of Toronto. We were wondering if this issue is still open, and if we could be assigned to fix it. We were able to replicate the issue, and have looked into ways we can fix it.

Thanks. @Laksh03 @nafizul02 @aidand9