Bug description

When using an INTERVAL type in postgres as a metric, both bar charts and pie charts fail to render with no obvious errors.

For a pie chart, the legend is rendered but nothing else. For a bar chart, the axes and X-axis labels are rendered, but the bars are not drawn.

No errors are reported, either via toast, in the browser console, or in the application logs.

How to reproduce the bug

  1. Write a query against postgres with an INTERVAL column in the response
  2. Create a bar chart or pie chart from that query
  3. Use the INTERVAL as a metric, e.g. with MAX aggregation
  4. Press the button to render the chart

Screenshots/recordings

screenshot_2024-08-29_14-00-05_07894c8a screenshot_2024-08-29_14-00-49_34811ac2

Superset version

4.0.2

Python version

3.9

Node version

16

Browser

Firefox

Additional context

Rendering the exact same chart but modifying the query to convert the INTERVAL into an integer number of milliseconds worked fine.

Note I also couldn't work around it in this way from the chart itself, as I'd need my metric to be EXTRACT('epoch' FROM total_playtime) which the chart disallowed as it contains a subquery; I had to modify the virtual dataset to work around this.

Checklist

  • [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
  • [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
  • [X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

Comment From: giftig

Note that I've also noticed the INTERVAL type doesn't get sorted correctly in query results in SQL Lab; results just end up sorted alphabetically according to the rendered text. It seems there's just no support for INTERVAL in the engine spec.

Comment From: rusackas

This has been silent for quite a while, but looking at the db engine spec, I'm assuming this is still an issue. I don't have a Postgres connection at the moment to test with, but I'll CC @mistercrunch @betodealmeida @sadpandajoe in case they do.

Comment From: giftig

Yeah I don't think anything's been fixed here. I've just tried verifying it by upgrading to 5.0.0-rc2 but had some issues with requirements failing to install when I tried swapping the image tag, so wasn't able to quickly try it again. But I can try to reproduce with a fresh db / install when I have some spare time.

I suspect using postgres INTERVAL is a less common usecase since more people are using "big data" engines like trino with superset and only some use cases would use INTERVAL even among postgres users, so it's a fairly niche bug but a bug nonetheless.

I'd take a look at fixing this one but lately I've only been using superset for a hobby project and I worked around this bug by converting INTERVALs to integers for that, so not sure I'll get the bandwidth very soon.

Comment From: rusackas

Opened a PR (via AI): https://github.com/apache/superset/pull/34513 - let's see how that plays out.