This morning I received an update on a MySQL bug, someone added a comment on an issue I filed in November 2003, originally for MySQL 4.0.12 and 4.1.0. It’s MySQL bug#1956 (note the very low number!), “Parser allows naming of PRIMARY KEY”:
[25 Nov 2003 16:23] Arjen Lentz Description: When specifying a PRIMARY KEY, the parser still accepts a name for the index even though the MySQL server will always name it PRIMARY. So, the parser should NOT accept this, otherwise a user can get into a confusing situation as his input is ignored/changed. How to repeat: CREATE TABLE pk (i INT NOT NULL); ALTER TABLE pk ADD PRIMARY KEY bla (i); 'bla' after PRIMARY KEY should not be accepted. Suggested fix: Fix grammar in parser.
Most likely we found it during a MySQL training session, training days have always been a great bug catching source as students would be encouraged to try lots of quirky things and explore.
It’s not a critical issue, but one from the “era of sloppiness” as I think we may now call it, with the benefit of hindsight. At the time, it was just regarded as lenient: the parser would silently ignore things it couldn’t process in a particular context. Like creating a foreign key on a table using an engine that didn’t support foreign keys would see the foreign keys silently disappear, rather than reporting an error. Many if not most of those quirks have been cleaned up over the years. Some are very difficult to get rid of, as people use them in code and thus essentially we’re stuck with old bad behaviour as otherwise we’d break to many applications. I think that one neat example of that is auto-casting:
SELECT "123 apples" + 1; +------------------+ | "123 apples" + 1 | +------------------+ | 124 | +------------------+ 1 row in set, 1 warning (0.000 sec) SHOW WARNINGS; +---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '123 hello' | +---------+------+-----------------------------------------------+
At least that one chucks a warning now, which is good as it allows developers to catch mistakes and thus prevent trouble. A lenient parser (or grammar) can be convenient, but it tends to also enable application developers to be sloppy, which is not really a good thing. Something that creates a warning may be harmless, or an indication of a nasty mistake: the parser can’t tell. So it’s best to ensure that code is clean, free even of warnings.
Going back to the named PRIMARY KEY issue… effectively, a PRIMARY KEY has the name ‘PRIMARY’ internally. So it can’t really have another name, and the parser should not accept an attempt to name it. The name silently disappears so when you check back in SHOW CREATE TABLE or SHOW INDEXES, you won’t ever see it.
CREATE TABLE pkx (i INT PRIMARY KEY x) reports a syntax error. So far so good.
But, CREATE TABLE pkx (i INT, PRIMARY KEY x (i)) is accepted, as is ALTER TABLE pkx ADD PRIMARY KEY x (i).
MySQL 8.0 still allows these constructs, as does MariaDB 10.3.9 !
The bug is somewhere in the parser, so that’s sql/sql_yacc.yy. I’ve tweaked and added things in there before, but it’s been a while and particularly in parser grammar you have to be very careful that changes don’t have other side-effects. I do think it’s worthwhile fixing even these minor issues.