Database error messages in general are very bad. Why?

Oracle Version 8 had lots of messages such as "Invalid column name" where they meant:

1. Column name not found in the table in question. The word "invalid" is the wrong word as it implies illegal characters or something like that.
2. Which column? Which table? The parser surely knows this at the time it generates the error message. But it helpfully chooses not to inform the user.

Thankfully Oracle 10 has improved its error messages a lot. They include the statement in question and the point in the statement producing the problem. And error messages contain which foreign key constraint has been violated, and so on.

But I have the following problem with MySQL. I try to create a table with InnoDB with a foreign key constraint and it says:

ERROR 1005 (HY000):
Can't create table './myschema/mytable.frm' (errno: 150)

What it means is: the statement has an error in it. But what is the error? In this case, there was a foreign key constraint and the column didn't exist in the referenced table. But why couldn't it tell me this?

Ah MySQL (at least MyISAM) so isn't a real database!

Firstly, when doing an insert, I did some arithmetic. The numeric column was of a certain width. If the result of the arithmetic is larger than the maximum allowed value my number was just getting turned into that maximum allowed value, without warning or error. A large number suddenly becoming some other large number may sound good in the philosophy of "errors are bad - we want to minimize errors!" but literally it's never what you want. Oracle gives an error if a number is too big to be stored in a column. Which is what you always want.

Secondly, due to above arithmetic overflow errors, my insert statement was failing (as multiple values that should have been distinct, but beyond the maximum, were then identical, equal to the maximum). I kept on doing it and it kept on failing. Then I looked at the table and each time I'd done such an unsuccessful insert (a single statement to insert maybe 10k rows) some rows (but not all - due to the error) were getting inserted. Having half a statement succeed is never what you want! Oracle sets an invisible checkpoint before each statement and if the statement fails, rolls the database back to that checkpoint. That's always what you want!

This just annoys me so much. The sum of an empty set of integers is zero, not undefined.

However neither Oracle nor MySQL understand this. I can only assume this variation from common sense and mathematics is considered the “best practices” definition of the SQL SUM function.

mysql> desc email_box;
| box_size_bytes | int(10)     |

mysql> select count(*) from email_box;
|        0 |

mysql> select sum(box_size_bytes) from email_box;
| NULL |