Can't create chart if write incorrect SQL request in virtual dataset

[PavelOreshkin]

Bug description

Can't create chart if write incorrect SQL request in virtual dataset

Steps:

1. Create dataset
2. Go to dataset list
3. Click on Edit icon on this new dataset
4. Click on icon Lock with text "Click the lock to make changes."
5. Choose Virtual
6. In SQL textarea write "SELECT 1"
7. Click on Save
8. Enter on this dataset
9. Choose any column in "Dimensions" selector
10. Click "Create chart"

Actual results: Import error is shown in the "Connect a database" dialog

Expected behavior: May be validate dataset SQL before starting work with chart or let the user select only those columns that he can select

Screenshots/recordings

Superset version

master / latest-dev

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.

[dosubot[bot]]

Hey @PavelOreshkin! I'm here to help you with any bugs, questions, or contributions you have for Apache Superset. Let's tackle this issue together!

I found a similar closed issue regarding filtering based on virtual columns. The solution suggested was to add the column to the dataset via the dataset editor, which resolves the issue. However, this might not be practical for many columns, and ideally, the query should work without manually adding each column ^[1].

For your specific issue, it seems like validating the SQL in the virtual dataset before proceeding with chart creation could prevent such errors. Ensuring that the SQL query is correct and that all necessary columns are present in the dataset might help avoid import errors during chart creation.

To continue talking to Dosu, mention @dosu.

[rusackas]

If you edit the virtual query, you should probably hit the synchronize columns button to update the available columns in the chart builder tool. It's (probably?) not Superset's responsibility to pre-flight your query (which means running it, and could incur costs of any scale). While that's an interesting feature request, I'm not sure we'd consider that a bug. Curious what @betodealmeida or @villebro think. Maybe we can move this to an Ideas Discussion of we want to keep the idea rolling.

[rusackas]

This has remained silent for nearly 6 months, so I'll close it as not planned. If anyone is still seeing this as a bug in current versions, we can revisit/reopen as needed.