Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error message on insert/update/delete queries #7180

Open
jonahgreenthal opened this issue Sep 26, 2024 · 6 comments
Open

Error message on insert/update/delete queries #7180

jonahgreenthal opened this issue Sep 26, 2024 · 6 comments

Comments

@jonahgreenthal
Copy link

jonahgreenthal commented Sep 26, 2024

Issue Summary

When a insert, update, or delete query is run in Redash, the result is an error message:
image
The query does not actually run, and the error message is completely inscrutable.

If the query is followed by a select query, like this—

update Contact set id=1 where id=1;
select 1;

—then the second query runs and returns the expected value, but the first query still doesn't actually run.

Here are some better ways this could be handled, from best to worst:

  1. When an insert, update, or delete query is run, actually run it and provide feedback like "1 row affected". Also support insert…output (SQL Server) and analogous operations with other DBMSes (update...returning) in which data are returned from a mutation.
  2. When an insert, update, or delete query is run, actually run it and provide no feedback
  3. When an insert, update, or delete query is run, actually run it, but still provide an error message because no results are returned (Redash 8 with Postgres behaved this way. I don't like it, but it's still better to run the query than not.)
  4. When an insert, update, or delete query is run, provide an error message that tells the user what's going on.

This occurs in all environments I have tested, using Redash 10.1.0.b50633 via the Docker image with a Microsoft SQL Server data source.

Steps to Reproduce

  1. Run a query like update Contact set id=1 where id=1;.
  2. Observe the error.

Expected behavior: The query runs as instructed, and there is no error message.

Technical details:

  • Redash Version: 10.1.0.b50633 via the Docker image.
  • Browser/OS: Firefox 130.0.1 on Windows 10, but I don't think it matters
  • How did you install Redash: Docker image
@justinclift
Copy link
Member

Interesting. Redash is generally used as a reporting tool (ie SELECT queries), but I could see that being able to stuff that changes the database would indeed be useful.

This is probably an area of things that could be looked at and improved to support doing so more.

@jonahgreenthal
Copy link
Author

jonahgreenthal commented Sep 27, 2024

Yeah, I realize it's not the normal use case, but I think it's worth supporting. We use our Redash installation not just for reporting, but also as a way to grant certain people access to the database (preferably including insert, update, and delete) without having to deal with SSH keys, establishing a tunnel, etc.

@eradman
Copy link
Collaborator

eradman commented Oct 14, 2024

We often run UPDATE ... RETURNING in queries, so I would consider this to be a normal use case.
When using a PostgreSQL data source the following is returned if the query does not return a recordset:

Error running query: Query completed but it returned no data.

@jonahgreenthal what behavior are you looking for? I think expecting 0 or more rows in return is reasonable, but there is doubtless some better error handling that could be implemented for some data sources.

@jonahgreenthal
Copy link
Author

I listed my preferences in the numbered list in my original message. Talking about update...returning is a good point, and I incorporated that.

@eradman
Copy link
Collaborator

eradman commented Oct 14, 2024

Ah, I overlooked the numbered list you posted. So for (1) I think you're suggesting that if the result list contains an expected result, check cursor.rowcount for number of affected rows and display that instead.

@jonahgreenthal
Copy link
Author

I'm not sure how it would be implemented, but that certainly sounds plausible.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants