MySQL Bugs: #10198: Reserved words as column names don't work anymore in 4.1

Bug #10198	Reserved words as column names don't work anymore in 4.1
Submitted:	27 Apr 2005 9:03	Modified:	28 Apr 2005 15:42
Reporter:	Igor D'Astolfo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Linux (Linux & Windows)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
I've just upgraded my development server from 4.0.x to 4.1.11.

Before the upgrade the following query worked:

select CODDIST, MOD, MATR,MODIFY,STATUS  from main where MOD like '%sometext%'

now, mysql server responds that the query is broken. The query works again if I quote the column name or if I add the table name before the column name.

I think that the problem is that MOD is a reserved word, but I can't understand why MySQL doesn't act as specified as reported in chapter 9.6 (Treatment of Reserved Words in MySQL). My server works in standard sql_mode, that (I think) permits the use of reserved words as column names. It would complain about it only if the server works with sql_mode IGNORE_SPACE set.

How to repeat:
A generic query with a reserved word in column names:

select CODDIST, MOD, MATR,MODIFY,STATUS  from main where MOD like '%sometext%'

Hi,

Thanks for the report.
Fact that "mod" is failing as column name is expected behaviour. Manual is correct, and no sql mode will let you use it unless you qoute it.

Though, I found something interesting with the MOD(n,m) function. It is behaving differently than other functions when there is a space before the (. This is probably because MOD can be used as a "MOD(n,m)" and "n MOD m".

Mind space infront of ( in following examples:

mysql-4.1.11-standard-log> select abs (4);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(4)' at line 1
mysql-4.1.11-standard-log> select mod (4,2);
+-----------+
| mod (4,2) |
+-----------+
|         0 |
+-----------+

mysql-5.0.4-beta-standard-log> select abs (4);
ERROR 1305 (42000): FUNCTION .abs does not exist
mysql-5.0.4-beta-standard-log> select mod (4,2);
+-----------+
| mod (4,2) |
+-----------+
|         0 |
+-----------+

Regards,

Geert

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

As specified in the chapter  9.6 (Treatment of Reserved Words in MySQL)

 "A reserved word can be used as an identifier if you quote it."

and

"A word that follows a period in a qualified name must be an identifier, so it is not necessary to quote it, even if it is a reserved word."

That's why you need to quote MOD or to qualify it with a table name, if you want to use it as an identifier.

Hi, 
   reviewing my post I saw I made a mistake, I wrote incorrectly that I had IGNORE_SPACE set, instead I was working in default sql_mode (IGNORE_SPACE unset)

I think you should add a comment to MOD function either to Chapter 9.6 (that I read carefully before posting this issue).

As stated in MOD documentation, the syntax "N MOD M" has been introducted in 4.1, and this syntax is incompatible with the IGNORE_SPACE unset sql_mode . Moreover, any reserved word that has this form of syntax can't be used without quote.

Before 4.1 I used the MOD reserved word as unquoted column name without problem (the IGNORE_SPACE was unset)