Bug #14018 SELECT with LIMIT clause generates inconvenient result, not an error

• Submitted: 13 Oct 2005 20:21
• Modified: 13 Oct 2005 20:58
• Reporter: Pio Stremel Neto
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.x / 5.x
• OS: Any (All)
• CPU Architecture: Any

[13 Oct 2005 20:21] Pio Stremel Neto

Description:
Any select command with LIMIT clause incorrect do not generate an error, but a wrong execution of a query. 

Ex.: 

Shows 10 records. This is correct.
SELECT * FROM table LIMIT 10   

Show all records. Note that would give an SINTAX ERROR.
SELECT * FROM table LIMIT10    

Note that would give an SINTAX ERROR !!!
SELECT * FROM table BLABLABLA 

The inconvenient is that when we write any wrong LIMIT clause, it will not generate an error:

Ex.:

SELECT ... LIMIT10
SELECT ... LIMIT
SELECT ... LIMMIT 
SELECT ... LUMIT10
SELECT ... LIMITE10 (Only portuguese speakers)

This can occur accidentally when we generate the SQL by application, seeing the data at any client, etc, and can cause wrong interpretation, inconsistent information and logical mistakes.

etc.

How to repeat:
Just tap:

SELECT ............. ANY_WRONG_LIMIT_CLAUSE. 

Any MySQL version, any OS, any client.

Suggested fix:
Generate a syntax error when this syntax error occurs.

[13 Oct 2005 20:58] Hartmut Holzgraefe

> Shows 10 records. This is correct.
> SELECT * FROM table LIMIT 10   

ok

> Show all records. Note that would give an SINTAX ERROR.
> SELECT * FROM table LIMIT10    

this is the same as 

SELECT * FROM table AS LIMIT10

so what happens here is that the LIMIT10 string is taken as a table alias name
as documented on 

http://dev.mysql.com/doc/refman/5.0/en/join.html

...
table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias]
...

> Note that would give an SINTAX ERROR !!!
> SELECT * FROM table BLABLABLA 

same here