You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "Zakyrel (via GitHub)" <gi...@apache.org> on 2024/02/06 11:49:48 UTC
[I] Error when running an sql query having an empty result (Drill error - Issue 1002) [superset]
Zakyrel opened a new issue, #27028:
URL: https://github.com/apache/superset/issues/27028
### Bug description
Hello,
First of all, Superset is working. I can use it normally and I already created SQL queries working within the SQL Lab and saved them as dataset, then used it for Charts.
**ISSUE**
My issue is when using the SQL Lab to create datasets when the result of a query is an empty result.
For instance here is a simple query fetching data from a postegreSQL database's table:
SELECT * from base_migration.test;
Case 1 : If the table "test" has got at least one row in it, the query will work normally.
Case 2 : If that table doesn't have any row in it, I get the following error:
Note : I deleted the only row from the table "test" before running this same query again.
This behavior from Superset is the same on my local computer (I'm using Docker Desktop 4.26.1 for Drill and Superset), and on our integration server.
But if I run the same query directly in Apache Drill, it works and display an empty result.
Can you help in this issue please ?
----------
**CONTEXT**
I'm creating a dashboard to display the result of a data migration between csv files into a postgreSQL database. I want to use the Table type charts. For each Table chart, it would list rows that were in a given csv file, but not in the target table the data were migrated into. To say it differently, the goal is to display rejects that occured during the data loading. Sometime a table chart would display rows, meaning that there was a reject. But most of the time, there will be no result, meaning that the data loading from this csv file to that table succeed in full.
A first data migration already take place. We have two cases: tables with all rows from their source csv file, and tables missing some rows from their source csv file (aka: rejects). These Table charts would be used for several upcoming data migration rehearsals. This means that the goal is to have no reject on any table, so no row to display in all Table charts.
### How to reproduce the bug
1. Within a database management tool (e.g. pgAdmin 4), create a table "test" in a database you have the Storage Plugin setup for, with these columns:
- id (integer)
- name (text)
2. Insert a row:
INSERT INTO database.test(
id, name)
VALUES ('1', 'abc');
3. In Superset, SQL Lab, use the following query:
SELECT * from database.test;
You should have that one row as the result of this query.
4. Within the database management tool, delete the row from that table:
DELETE FROM database.test
WHERE id = '1';
5. In Superset, SQL Lab, use the query again:
SELECT * from database.test;
The error message comes.
[Optional] 6. In Superset, SQL Lab, use the query:
SELECT * from database.test WHERE id = 2;
The error message comes.
### Screenshots/recordings
_No response_
### Superset version
3.1.0
### Python version
I don't know
### Node version
I don't know
### Browser
Not applicable
### 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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when running an sql query having an empty result (Drill error - Issue 1002) [superset]
Posted by "sfirke (via GitHub)" <gi...@apache.org>.
sfirke commented on issue #27028:
URL: https://github.com/apache/superset/issues/27028#issuecomment-1930158658
I wonder if this is specific to Apache Drill. I tried creating a table in MS SQL Server, dropping the only record, and running SELECT * in SQL Lab. I get a message, "The query returned no data." And I have charts on dashboards that function just as you describe: most of the time the underlying SQL View/Table has no data, and when it does, an alert triggers and sends that table to a user.
Maybe ask in Slack to see if another Drill user can at least replicate this, and ideally help investigate it? Unfortunately no community member has self-identified as an Apache Drill expert on our [database user rolodex](https://docs.google.com/spreadsheets/d/1U1qxiLvOX0kBTUGME1AHHi6Ywel6ECF8xk_Qy-V9R8c/edit#gid=0).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when running a SQL query on an Apache Drill DB that returns an empty result (error - Issue 1002) [superset]
Posted by "Zakyrel (via GitHub)" <gi...@apache.org>.
Zakyrel commented on issue #27028:
URL: https://github.com/apache/superset/issues/27028#issuecomment-1934451088
Hello. I posted on Slack > Apache Drill.
"cgivre", a user, responded about it:
Oh... it looks like the issue is actually in the SQLAlchemy Driver for Drill
What's happening is that Drill is returning an empty result set, but Superset isn't liking how that is reported back to Drill
The issue is here: https://github.com/JohnOmernik/sqlalchemy-drill
What we have to do is figure out what Superset wants to see when a query returns zero results and fix the SQLAlchemy dialect
I don't think it would be difficult, but I'm not sure when I'd have time to get to it
The issue is probably somewhere here: https://github.com/JohnOmernik/sqlalchemy-drill/blob/3d5abc7587941523e472e3430b391bf207ed77a7/sqlalchemy_drill/drilldbapi/_drilldbapi.py#L176
I'm not a developper so I can't help myself.
Maybe these information would help Superset devs ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when running a SQL query on an Apache Drill DB that returns an empty result (error - Issue 1002) [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #27028:
URL: https://github.com/apache/superset/issues/27028#issuecomment-1932376942
Here's the link to join [Superset Slack](http://bit.ly/join-superset-slack)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when running a SQL query on an Apache Drill DB that returns an empty result (error - Issue 1002) [superset]
Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #27028:
URL: https://github.com/apache/superset/issues/27028#issuecomment-1934544673
I think Superset expects an empty iterable when no rows are returned, and Drill is probably returning a `None`. We should follow [Postel's Law](https://en.wikipedia.org/wiki/Robustness_principle):
> Be conservative in what you do, be liberal in what you accept from others.
And make sure Superset can handle this case gracefully, as I'm sure this will happen again in the future with some other database driver.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when running a SQL query on an Apache Drill DB that returns an empty result (error - Issue 1002) [superset]
Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida closed issue #27028: Error when running a SQL query on an Apache Drill DB that returns an empty result (error - Issue 1002)
URL: https://github.com/apache/superset/issues/27028
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when running a SQL query on an Apache Drill DB that returns an empty result (error - Issue 1002) [superset]
Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #27028:
URL: https://github.com/apache/superset/issues/27028#issuecomment-1936598960
I have a fix in https://github.com/apache/superset/pull/27073.
Unfortunately it's Drill-specific, since there was no generic way of knowing if no rows were returned. But it solves the problem.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when running a SQL query on an Apache Drill DB that returns an empty result (error - Issue 1002) [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #27028:
URL: https://github.com/apache/superset/issues/27028#issuecomment-1934481945
Not sure who would be the right person to make updates to the Drill SQLA dialect... I don't know any Drill users to be opinionated or able to test (though maybe I should ramp on it, it seems really cool). CC @john-bodley @betodealmeida in case they know anyone interested/qualified to investigate.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Error when running a SQL query on an Apache Drill DB that returns an empty result (error - Issue 1002) [superset]
Posted by "Zakyrel (via GitHub)" <gi...@apache.org>.
Zakyrel commented on issue #27028:
URL: https://github.com/apache/superset/issues/27028#issuecomment-1932206239
Hello. Thank you for your answer. I thougth it was Dril lspecific too.
I didn't know about what is Slack. I will try to get answer there. I will let you know about.
Regards.
Anthony
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org