What's wrong with my SQL Fiddle query?

Question

Take a look at this simple query in SQL fiddle: http://sqlfiddle.com/#!2/c1eb6/1. This is a super-simple update query, why is it producing an error? I wonder if could it be a bug in that site?

Build Schema:

create table your_table (some_column varchar(50));

insert into your_table set some_column = '30# 11';
insert into your_table set some_column = '30# 12.00';
insert into your_table set some_column = '30# 13.5';
insert into your_table set some_column = 'abdfs';
insert into your_table set some_column = '0000000';

Query:

UPDATE your_table
SET some_column = 1;

Clepsydra

I would guess that it fails on EXPLAIN EXTENDED UPDATE..etc., because the left column is for building, the right seems to be SELECT only.... But as I said, a guess. – Cassell

yeah the update worked when using a different SQL server, for example: sqlfiddle.com/#!3/22362/7 – Clepsydra

If it works there it does indeed smell like it's a bug that only selects are available on the right. I'd say: the contact information to report this is in the about page – Cassell

@Clepsydra yes, emailing me (admin at sqlfiddle dot com) about this would probably have been a bit more direct. – Kailakaile

I will do that, thank you, just checking with the community in case I was missing something (as I usually am). – Clepsydra

-1; I appreciate bug reporting is an admirable civic duty, but notwithstanding, bugs for website X should be reported at website X. – Galinagalindo

@Galinagalindo - thanks very constructive. – Clepsydra

@Clepsydra - no offence is meant. It actually is intended to be constructive - if external site bugs are on-topic here, SO would have to define which sites are acceptable. A slippery slope! – Galinagalindo

I guess "Bug" in the title is misleading. I was trying to write "Problem" but SO doesn't allow that. I didn't know if it was a bug or not and wanted the community to confirm or tell me where I was wrong... and they did that splendidly. – Clepsydra

Alright, fair enough; question tweaked & undownvoted. – Galinagalindo

Can you please include the code in your question? Nobody knows where sqlFiddle is in a few years time, and we don't want the question to become useless. I won't do it for you because of possible copyright issues. – Changchun

Answer 1

A bit of background for those interested in some of the arcane issues I've been dealing with on SQL Fiddle:

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state (dba.se)

Essentially, I am trying to ensure that the fiddles always remain in a consistent state, even as people play with them. One thing I've worried about is people intentionally messing with the databases, breaking them for the other people that might be working with them (this has happened before, but not often fortunately).

I've found methods of keeping things clean for each of the database platforms, but interestingly each method is completely different for each platform. Unfortunately, for MySQL I had to resort to the worst option - only allowing SELECTs on the right-hand side. This is because there are too many ways to write queries that include implicit commits, and there is no way that I've found to prevent that from happening short of outright denial on the query side. This is quite unfortunate, I realize, but it seems to be required.

Anyhow, this particular bug was a result from a change I had recently made in the logic for MySQL. It is fixed now, and now reports the expected error message:

DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel.

Kailakaile

FWIW, the error message "DDL and DML statements are not allowed ..." meant nothing to me. DDL? DML? Googling, it seems those two between them cover EVERYTHING that SQL does (specifically, SELECT is part of DML). Fortunately, "only SELECT statements are allowed" is clear enough. Anyway, I was glad google brought me to this post, so I could understand better what was going on and why. – Keilakeily

Maybe simply "Any statement that would not work in readonly mode is not allowed ..."? (And I see that some people use DML to mean only "data-changing" statements, consist with the "M" in DML, separating SELECT into DQL.) – Keilakeily

Ugh can't call a stored procedure in query panel, for complex query output. I see why (procedure might make changes), but that is a major limitation... – Keilakeily

Make all of your change-related queries on the left, view all the results by querying on the right. – Kailakaile

Yes, I understand. Just pointing out that the error message is non-obvious. And how to use SQL fiddle when can't make a call (using MySQL) on the right hand side (to a stored procedure that constructs and executes a complex query, not a change-making action) is non-obvious. I realized eventually that I would have to put the call on the left, followed by statements that create a table to store that result, and put the result into that table - simply so I could query it on the right. That seems like quite a work-around to have to do. But I understand why. – Keilakeily

Answer 2

I got this error on SQL Fiddle because I was trying to use PostgresSql syntax, while the interpreter was set to MySql

Porterhouse