3 ms·
I had hoped that the SQL improvements would fix this annoyance, but according to the documentation they did not: create table fib ( a integer, b integer );
by tzs 9y ago
I had hoped that the SQL improvements would fix this annoyance, but according to the documentation they did not:
create table fib ( a integer, b integer );
insert into fib (a, b) values (1, 0);
update fib set a=a+b, b=a;
update fib set a=a+b, b=a;
update fib set a=a+b, b=a;
update fib set a=a+b, b=a;
update fib set a=a+b, b=a;
select a, b from fib;
The correct result according to the SQL standard is a == 8 and b == 5. In MySQL the result is a == 16 and b == 16. They do document this in the manual, saying UPDATE evaluates the assignments left to write, and note that this deviates from the standard.
MariaDB is the same prior to 10.3.5. Starting with 10.3.5 it defaults to left to write evaluation, but you can set the SIMULTANEOUS_ASSIGNMENT mode flag to make UPDATE evaluate the assignments simultaneously instead of left to right.
I believe that all other common SQL databases (Sqlite, PostgreSQL, Oracle, Microsoft SQL Server) follow the standard.
- kbsletten 9y agoI have to say, I wouldn't get your hopes up. If I were MySQL, I wouldn't touch this with a 10 ft pole because it's not likely to really affect somebody's opinion of MySQL enough to switch, but there's going to be at least one person whose code you'll break. Before you can consider SQL compatibility, you have to keep most-popular-version-of-mysql compatibility.
- iteriteratedone 9y agoNo .... this is perfect for a config flag ... you can make it table specific. Then 4 versions later ... this flag is default 1 more version ... we are now standard compliant
- kbenson 9y agoI agree. If it's desired, use a config setting to make it the default. It was already standard practice in any MySQL database I rolled out to set InnoDB as the default storage engine in the config until it became the shipped default. Like transaction isolation levels, this is something you either set as an administrator for your need, or you let the application setup guide steer you, or you let the application queries specifically set it, if the application needs it. Otherwise,you risk causing real data corruption and loss to existing customers purely for the benefit of being more compliant in your default configuration. A global and per-session setting is sufficient and more beneficial, IMO, so I think MariaDB took the right approach. If MySQL still doesn't have that setting, well, that's troubling, but I can't say I'm entirely surprised.
- morgo 9y agoMariaDB and MySQL have different goals here - MariaDB wants to offer drop-in compatibility for Oracle. MySQL has a core-audience of OLTP apps, that are mostly developed for it specifically. When designing features, I think it's important to make sure they can benefit the highest number of users possible. For this one, I don't think it can ever be the default because of the burden of backward compatibility. So it is difficult to justify developing it.
- morgo 9y agoHi! Former MySQL Product Manager here. I'm not sure I ever looked at this issue specifically, but you are correct: the burden of breaking backwards compatibility should be high, and supported by major use cases. To give a similar example, that I did look at: MySQL can enable more standards equivalent identifier quoting (via an SQL mode): https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi_quotes https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmod... To enable that by default would break a lot of applications.
- karmakaze 9y agoI don't see the problem here. Make the default preserve backward compatibility, but offer an option like 'SIMULTANEOUS_ASSIGNMENT' to be standards-compliant.
- userbinator 9y agoSimpler example: https://bugs.mysql.com/bug.php?id=13943 https://bugs.mysql.com/bug.php?id=13943 Reported over 12 years ago...