MySQL Bugs: #45361: parser behaves too primitive here!

Bug #45361	parser behaves too primitive here!
Submitted:	7 Jun 2009 21:58	Modified:	11 Jun 2009 19:19
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE `testrestore` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `function` VARCHAR(50) DEFAULT NULL,
  `group` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

-- fails
INSERT  INTO `testrestore` (id, FUNCTION ,GROUP) VALUES (1,'a','b');

-- works
INSERT  INTO `testrestore` (id, FUNCTION ,`GROUP`) VALUES (1,'a','b');
-- other keywords used as identifiers that needs to be `backquoted` are 'ORDER' and 'TABLE', but not 'ACTION' for instance

How to repeat:
See above.  Tried with 4.1.22, 5.0.82 and 5.1.34.

Any Basic interpreter from my (almost) childhood did this better (we are 30 years back in time)! Does the parser expect an ORDER BY in an INSERT statement?

Suggested fix:
It is always wise to `backquote` identifiers. But behaviour is inconsistent. And in this context there is no risk with not backquoting.

Thank you for the report.

Closed as "Not a Bug" because last (hidden) comment.

Behavior is consistent with other RDBMs as well.

For example, PostgreSQL behaves in same way:

postgres=# create table bug45361("id" int, "function" varchar(255), "group" varchar(255));
CREATE TABLE
postgres=# INSERT  INTO bug45361 (id, function, group) VALUES (1,'a','b');
ERROR:  syntax error at or near "group"
LINE 1: INSERT  INTO bug45361 (id, function, group) VALUES (1,'a','b...
                                             ^
postgres=# INSERT  INTO bug45361 (id, function, "group") VALUES (1,'a','b');
INSERT 0 1

"Closed as "Not a Bug" because last (hidden) comment."

won't even the reporter see a hidden comment?

I made a short private comment. Here I expand it.

GROUP is a reserved word, hence the error message.
ID and FUNCTION and ACTION are not reserved words,
they are only keywords, hence the error message is
not demonstrably inconsistent.

We could of course make an exception for this
case, if we assume it's an identifier
rather than a spelling error.

But there is a slight risk in saying that
group (without backticks) means the same thing
as `group`. Perhaps someday we'll allow, as the
standard requires, case sensitive delimited
identifiers. Then, if there's a column named group,
group (without backticks) does not identify it
but `group` does. That could break an application's
assumptions. It's valid to say that applications
will break anyway if we make such a change,
but we don't want to add new potential cases
just because we already will have many.

Thanks for the explanation.  It makes sense with distiguishing 'reserved word' versus 'keyword'.

Now .. bascially inside the columns-list of an INSERT statement it would in my understanding be safe to proces identifiers even named GROUP, ORDER etc. without backquotes). The parser has too little 'context-awareness'. That was my complaint. This may both be an issue with the MySQL parser itself as well as the libraries it is based on (yacc/bison I think).

I think this is an ongoing discussion - not at least with all the complaints about errors messages giving little (no!) hint about the 1064 error here and other places.  I stopped programming 20 years ago.  But for the 10-15 years I did I never saw a compiler or interpreter (even very simple Basic interpreters on CPM or DOS!) not able to provide better guidance to user as compared to the MySQL parser of today.