Bug in sqlfiddle OR I don't understand INSERT ... ON DUPLICATE KEY UPDATE

Question

This sqlfiddle link seems to retain state across invocations: http://sqlfiddle.com/#!2/125bc/1

It contains this schema for mysql:

CREATE TABLE if not exists  `standings` (
  `teamid` int(11) NOT NULL,
  `win` decimal(23,0) default NULL,
  `tie` decimal(23,0) default NULL,
  `lose` decimal(23,0) default NULL,
  PRIMARY KEY  (`teamid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `standings` (`teamid`, `win`, `tie`, `lose`) VALUES
(1, 3, 0, 0),
(2, 1, 1, 1),
(3, 1, 1, 1),
(4, 0, 0, 3);


CREATE TABLE if not exists `newscores` (
  `recordid` int(11) NOT NULL auto_increment,
  `teamid` int(11) NOT NULL,
  `gameid` int(11) NOT NULL,
  `totalstrokes` int(11) NOT NULL,
  primary key (recordid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;



INSERT INTO `newscores` (`recordid`, `teamid`, `gameid`, `totalstrokes`) VALUES
(5, 1, 1, 145),
(6, 2, 1, 105),
(7, 5, 1, 145),
(8, 6, 1, 155);

and these two queries:

Insert into  standings (teamid, win, tie, lose) (SELECT g1.teamid, SUM( IF( g1.totalstrokes < g2.totalstrokes, 1, 0 ) ) AS win, SUM( IF( g1.totalstrokes = g2.totalstrokes, 1, 0 ) ) AS tie, SUM( IF( g1.totalstrokes > g2.totalstrokes, 1, 0 ) ) AS lose
FROM newscores g1
JOIN newscores g2 ON g1.gameid = g2.gameid
AND g1.teamid != g2.teamid
GROUP BY g1.teamid) 
on duplicate key 
update win = win + values(win), 
lose = lose + values(lose), 
tie = tie + values(tie);

select * from standings;

It should result in this standings table:

teamid   win tie lose
  1       4   1   1
  2       4   1   1
  3       1   1   1
  4       0   0   3
  5       1   1   1
  6       0   0   3

and it does, the first time it's run. The second time it's run (by pasting the url into a new window), some of the values for teamids 1,2,5,6 are incremented.

I can reset it by editing the schema to change "create table" to "create if not exists table" and vice versa, or including "drop table" statements. But this seems very odd.

PS I'd be grateful if someone more pointful than I added a sqlfiddle tag to this question.

Answer 1

I'm the author of SQL Fiddle. There is a very simple explanation for this behavior. Essentially, all of your queries that you run on the right-side panel (the "Run Query" side) are executed in a transaction block, which is immediately rolled back to preserve the state of the schema (as defined by the left-side panel). Most of the time, everything is returned to exactly where things started, because most everything that was executed in a transaction can be rolled back. However, as I discovered early on with SQL Fiddle, one of the few things that doesn't get rolled back is auto incrementing ID values. This is an intentional design decision made by all of the vendors, and it is meant to ensure that your primary keys will never collide from reuse.

Since auto ids are supposed to be meaningless, it never really struck me as a problem for SQL Fiddle that this one thing changes (particularly since most people run their inserts on the left-side anyway).

You can see this behavior without having to open multiple windows, BTW - just hit "Run Query" multiple times and you'll see it change. You can also see it with my sample fiddle for SQL Server: http://sqlfiddle.com/#!3/1fa93/1 Just run that query multiple times and you'll see the id change each time. (It will reset if the fiddle hasn't been used in a while, since I drop the running DB to free up memory after a particular fiddle goes unused).

Edit

Doh! Sorry, was not reading your query closely enough. I now see what's going on. You are using MyISAM tables instead of InnoDB. I really need to disable those as an option for MySQL. You see, they don't support transactions at all. So when I attempt to roll back your updates to those tables, well, the updates simply don't get rolled back. You keep incrementing them. I saw this when I first rolled out MySQL as an option, since by default MySQL uses MyISAM. I had to change the default to InnoDB to get transactions working.

Here you can see it working fine when I simply removed your explicit use of MyISAM: http://sqlfiddle.com/#!2/cec6e/1

Update

Here is the version that I've re-arranged slightly, due to some changed requirements for MySQL: http://sqlfiddle.com/#!2/a9cc3/1