Invalid code has nonsense error line - it doesn't actually run changed code in build schema

[doug65536]

Delimiter set to GO:

CREATE TABLE ids (
    doc INT NOT NULL AUTO_INCREMENT,
    rev INT NOT NULL,
    PRIMARY KEY (doc)
) GO

CREATE FUNCTION test(a INT, b INT) RETURNS INT BEGIN
  RETURN a + b;
END GO

CREATE FUNCTION test2(docId INT) RETURNS INT BEGIN
  DECLARE newId INT DEFAULT 0;

  UPDATE ids set rev=(newId = rev+1) WHERE doc = docId;
  IF ROW_COUNT() <> 1 THEN BEGIN
    SET newId = 1;
    INSERT INTO ids set doc=docId,rev=revId;
  END;

  RETURN newId;
END GO

Try building that schema. Try changing it (perhaps adding 10 or 20 blank lines after the create table, and building schema again. The error message will refer to a line that is now whitespace.

I know that the THEN BEGIN and END is wrong. The problem is, it is almost impossible to find errors in sqlfiddle when it gives you nonsense error lines.

[bladeoflight16]

You didn't indicate what kind of database you're using. The use of GO suggests SQL Server, but MySQL is the only one for which AUTO_INCREMENT is valid, so far as I know.

Why not link to the actual Fiddle that shows your problem?

[doug65536]

Can I get a link when there is an error? On Jun 3, 2014 5:27 PM, "bladeoflight16" [email protected] wrote:

You didn't indicate what kind of database you're using. The use of GO suggests SQL Server, but MySQL is the only one for which AUTO_INCREMENT is valid, so far as I know.

Why not link to the actual Fiddle that shows your problem?

— Reply to this email directly or view it on GitHub https://github.com/jakefeasel/sqlfiddle/issues/67#issuecomment-45022942.

[bladeoflight16]

Hm. Well, I guess not if the error is in the schema. You can if it's in the query, though. Anyway, you still haven't said what database this is.

[doug65536]

Reviewing my old bugs. Sorry I missed your request for information.

The bug persists (I just tried it today). This is what it says at the bottom:

You have an error in your SQL syntax; check the manual that
 corresponds to your MySQL server version for the right syntax to use near 'GO

CREATE FUNCTION test(a INT, b INT) RETURNS INT BEGIN
  RETURN a + b' at line 5

...which I know is not valid. Here's the important bit: after causing that error, edit the sql and add a bunch of empty lines before line 5. Then try to build the schema again. The error message will not update, it will still say the error is on line 5 (the message will not change).