SQL with LIMIT1 returns all records

Question

I made a mistake and went in:

SELECT * FROM table LIMIT1

instead

SELECT * FROM table LIMIT 1 (note the space between LIMIT and 1 )

in the MySQL CLI. I expected to get some parsing error, but I was surprised because the query returned all the records in the table. My first thought was “stupid MySQL, I'm sure it will return an error in PostgreSQL”, but PostgreSQL also returned all the records. Then we tested it using SQLite - with the same result.

After some digging, I realized that it doesn’t matter what I write down after the table. There are no WHERE/ORDER/GROUP offers:

 SELECT * FROM table SOMETHING -- works and returns all records in table SELECT * FROM table WHERE true SOMETHING -- doesn't work - returns parse error 

I assume this is standardized behavior, but I could not find an explanation of why. Any ideas?

Answer 1

Your first query is equivalent to this query using a table alias:

 SELECT * FROM yourtable AS LIMIT1 

The keyword AS is optional. The table alias allows you to reference the columns of this table using the alias LIMIT1.foo , rather than the name of the source table. It is useful to use aliases if you want to give the tables a shorter or more descriptive alias in the query. You must use aliases if you join the table for yourself.

From the SQL lite documentation :

Answer 2

That's why I want the DB engine to force the use of the AS keyword for the aliases http://beyondrelational.com/modules/2/blogs/70/posts/10814/should-alias-names-be-preceded-by-as.aspx

Answer 3

 SELECT * FROM table LIMIT1; 

LIMIT1 This is taken as an SQL alias because LIMIT1 not a reserved SQL literal. Something after the table name and this is not a reserved keyword, which is always used as an alias of the SQL table.

  SELECT * FROM table LIMIT 1; 

When you used LIMIT immediately after the table name, SQL detected this as a reserved keyword and worked on it according to the behavior. If you want to use reserved keywords in your query, you can do this by placing the reserved literals in quotation marks. as..

 SELECT * FROM table `LIMIT`; 

OR

 SELECT * FROM table `LIMIT 1`; 

Now all words related to quotes will be treated as custom. Usually we were mistaken with keywords of date, time, restriction, etc. using them as column names.