Bug #2384 False SQL Syntax error report by MySQL Server when Using Date format Columns
Submitted: 14 Jan 2004 6:11 Modified: 14 Jan 2004 7:47
Reporter: Jattie van der Linde Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.17-nt OS:Windows (Win32)
Assigned to: Dean Ellis CPU Architecture:Any

[14 Jan 2004 6:11] Jattie van der Linde
Description:
I receive the following error message when I run a query on my MySQl database and get this error message even thou the syntax of the query is not faulty:

[jattie] 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 'index
FROM `inhouse_syssched`' at line 1

The Query looks like this:

SELECT index
FROM `inhouse_syssched`

Running the Query:

SELECT *
FROM `inhouse_syssched` 

yields perfect results with no error messages.

Running the same query on a diffirent table without Date field types work fine.It seems if this failure occurs when a columns is defined as a date field.

How to repeat:
Create a table:

CREATE TABLE `inhouse_syssched` (
  `Index` int(10) NOT NULL auto_increment,
  `System` varchar(50) NOT NULL default '',
  `Owner_Initials` varchar(10) NOT NULL default '',
  `Contact_Number` varchar(50) NOT NULL default '',
  `Group` varchar(100) default NULL,
  `Date_From` date NOT NULL default '0000-00-00',
  `Date_To` date NOT NULL default '0000-00-00',
  `General_Time` varchar(100) NOT NULL default '',
  `Project_Number` varchar(10) NOT NULL default '',
  `Test_Notes` longtext,
  PRIMARY KEY  (`Index`),
  KEY `Index` (`Index`)
) TYPE=MyISAM; 

Then run Query:
SELECT Index, System
FROM `inhouse_syssched`

and get a SQL Syntax error report

Create the same table without Date Fields:
CREATE TABLE `in_house_sys_sched` (
  `Index` int(11) NOT NULL auto_increment,
  `System` varchar(100) NOT NULL default '',
  `Owner_Initials` varchar(100) NOT NULL default '',
  `Contact_Number` varchar(100) NOT NULL default '',
  `Group` varchar(100) NOT NULL default '',
  `Date_From` varchar(100) NOT NULL default '',
  `Date_To` varchar(100) NOT NULL default '',
  `General_Time` varchar(100) NOT NULL default '',
  `Project_Number` varchar(100) NOT NULL default '',
  `Test_Notes` text NOT NULL,
  PRIMARY KEY  (`Idx`)
) TYPE=MyISAM; 

Then run Query:
SELECT Index, System
FROM `inhouse_syssched`

and get proper results without syntax error reports.
[14 Jan 2004 6:33] Jattie van der Linde
On Closer investigation it seems that the culprit field type is not the Date field but the "long text" field. The results are the same as described above however.
[14 Jan 2004 7:47] Dean Ellis
This is not a bug; INDEX is documented as a reserved word and must be escaped when used as an identifier.

I am unable to repeat the behavior of a query allowing INDEX to be used unescaped.
[15 Jan 2004 1:10] Jattie van der Linde
Thanks, and embaressing oversight. Should've thought about it!