Export to pivoted .csv: internal server error 500 (Apache Superset version 3.0.1)

frlm commented 5 months ago

Bug description

Using a Pivot Chart, when I try to export to pivoted .CSV by clicking on the 3 dots --> Download --> Export to pivoted .CSV, a new window with the message "Internal Server Error 500" showes up.

Error in Superset webapp when I try to export .csv from chart:

2024-03-13T14:50:33.672078854Z [2024-03-13 14:50:33 +0000] [11] [ERROR] Error handling request
2024-03-13T15:47:40.180413775Z 2024-03-13 15:47:40,179:ERROR:superset.views.base:'SUM(TempoLavoro)'
2024-03-13T15:48:03.641397046Z 2024-03-13 15:48:03,638:ERROR:superset.views.base:'SUM(TempoLavoro)'
2024-03-13T16:01:15.674399501Z 2024-03-13 16:01:15,673:ERROR:superset.views.base:'SUM(TempoLavoro)'
2024-03-13T16:01:25.121376982Z [PARSE_SYNTAX_ERROR] Syntax error at or near 'ore_pianificate': extra input 'ore_pianificate'(line 4, pos 29)
2024-03-13T16:01:25.121514682Z [PARSE_SYNTAX_ERROR] Syntax error at or near 'ore_pianificate': extra input 'ore_pianificate'(line 4, pos 29)

Error in Superset Worker log when I schedule Export::

2024-03-13T13:05:09.063164041Z [2024-03-13 13:05:09,062: ERROR/ForkPoolWorker-1] A downstream exception occurred while generating a report: 78165834-e919-47a3-a279-02d5d6819c23. Failed generating csv HTTP Error 500: INTERNAL SERVER ERROR
2024-03-13T13:05:09.063213941Z urllib.error.HTTPError: HTTP Error 500: INTERNAL SERVER ERROR
2024-03-13T13:05:09.063283841Z superset.reports.commands.exceptions.ReportScheduleCsvFailedError: Failed generating csv HTTP Error 500: INTERNAL SERVER ERROR

It's strange that pivot table gets generated correctly but .csv export fails

How to reproduce the bug

-

Screenshots/recordings

No response

Superset version

3.0.4

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

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.

rusackas commented 2 months ago

Oof, this is indeed very real, even in current versions. @Vitor-Avila have we had anyone run into this?

Vitor-Avila commented 2 months ago

Oof, this is indeed very real, even in current versions. @Vitor-Avila have we had anyone run into this?

@rusackas no reports on Preset so far. I also just tested with a very simple pivot and it worked.

sfirke commented 2 months ago

I wonder if related to https://github.com/apache/superset/discussions/28595 where .csv generation fails in a scheduled report?

yousoph commented 2 months ago

I think there's an issue with the null values/cells in the pivot. My pivoted CSV exports fine if there are no blank cells, but as soon as there is at least one blank cell in the chart, the export fails.

andy-clapson commented 3 weeks ago

Seeing the same here - any NULLs resulting from a pivot render the whole thing un-exportable.

EDIT: I just pulled down 4.0.2 (same version we are running) and ran it locally and I was having a hard time reproducing exactly what I'm seeing in our Superset instance. I will try to recreate it, it may need a certain dataset or mix of types in the Pivottable chart, I'm not sure yet.

sfirke commented 3 weeks ago

I haven't dug deep but would this pending PR address this issue: https://github.com/apache/superset/pull/29898 ? They are both about pivoting and NULLs.

andy-clapson commented 3 weeks ago

I haven't dug deep but would this pending PR address this issue: #29898 ? They are both about pivoting and NULLs.

on the nose. that's the one. Timely fix.

apache / superset