MySQL Bugs: #724: Cannot use the word hash in a query

Bug #724	Cannot use the word hash in a query
Submitted:	25 Jun 2003 16:39	Modified:	27 Aug 2003 0:45
Reporter:	Michael Benza	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1 alpha	OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
Any query that has the word 'hash' in it will fail on error 1064 (which is unknown to perror):

DELETE FROM session where hash='....' results in:
Error (1064): 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 'hash='....'' at line 1

SELECT sessionhash as hash FROM session
Same error, just with a different "near '...'"
(However, if you quote hash above, it will work.)

Finally:
INSERT INTO session (hash,userid,host,agent,lastactive) VALUES ...
fails:
Error (1064): 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 'hash,userid,host,.....' at line 1

Mike B

How to repeat:
Run any of the following queries, adapting them to fit your DB structure:
DELETE FROM session where hash='....';

SELECT sessionhash as hash FROM session;

INSERT INTO session (hash,userid,host,agent,lastactive) VALUES ('1','2','3.4.5.6','test',UNIX_TIMESTAMP());

Suggested fix:
Not a clue--sorry :-\

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

HASH is reserved word in MySQL.
Refer to: http://www.mysql.com/doc/en/Reserved_words.html

In order to be used as column, table, database name reserved words must be enclosed in backticks

... where `hash`='....'

As Peter Gulutzan said: 
"The SQL:2003 standard document has a list of reserved words 
and non-reserved (i.e. keywords but not reserved) words. 
 
The word HASH does not appear in either list." 
 
knowing mechanism of making word reserved I think HASH can be made reserved 
accidently. This issue need more investigation. (same for RTREE & BTREE)

what about ERRORS ?

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

I have now fixed that the following words are not reserved anymore in 4.1.1
HASH, BTREE, RTREE, ERRORS and WARNINGS

It would be really helpful...if it returned an error that said something to the affect ... 'hash is a reserved word, please rename your column', 'key is a reserved word, please rename your column' instead of the error about invalid syntax which really gives a person no idea of what the real problem is.