MySQL Bugs: #2001: Problems with table names

Bug #2001	Problems with table names
Submitted:	3 Dec 2003 3:23	Modified:	3 Dec 2003 6:32
Reporter:	Rafal Kedziorski	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
This is syntax for SELECT from MySQL documentation

6.4.1 SELECT Syntax

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
        [WITH ROLLUP]]
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] row_count | row_count OFFSET offset]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]

It's allowed to have table names like:
- user
- limit

but we have problems. this query dosn't work:

select
    m.mandant_id,
    m.name as 'mandant_name',
    l.name as 'limit_name',
    u.name as 'unit_name',
    sg.name as 'system_group_name',
    l2sg.value as 'limit_value'
from
    limit l,
    system_group sg,
    limit_2_system_group l2sg,
    mandant m,
    unit u
where
    m.mandant_id = 2
and
    sg.mandant_id = m.mandant_id
and
    sg.system_group_id = 3
and
    l2sg.system_group_id = sg.system_group_id
and
    l.limit_id = l2sg.limit_id
and
    u.unit_id = l.unit_id
order by l.name

MySQL told:

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 'limit l, system_group sg, limit_2_system_group l2sg, mandant m,

But after FROM caould folow an LIMIT. The same we have with tablenames like user, while user() is an function.

Regards,
Rafal

How to repeat:
Create user and limit table and make some selects on this tables.

LIMIT is a reserved word and must be escaped.  Please read:

http://www.mysql.com/doc/en/Reserved_words.html

I cannot duplicate any problems with a table name of user, however, in the current releases.

Thank you.

We have the same problem with Field Names ... And using recordset Objects (ADO) to Update or Add a new record in a table returns a similair Error ... 

I strongly beleive that this is a Bug since Field names cannot be escaped within a record Object. The following Example returns an error and we don't know of a way to escape it except by changing to SQL execute statements or modifying field names which is a major change and prawn to many errors for medium to large projects.

RS("Limit")=10
RS.Update

Also the idea of introducing new reserved words that might coincide with field/table names one might be using makes developers think twice before migrating an application or upgrading to a newer version.

We hope that the part responsible for the generation of this error checks a little further within the syntax of the statement to intelligently distinguish between reserved words and table/field names