SELECT * vs SELECT *

Question

Yesterday a colleague showed me the following postgres request. We were both surprised that this worked:

SELECT* FROM mytable; 

Since I recently encoded a parser for another language, I am trying to understand in more detail why this query is “compiled” and returns the same results as SELECT * FROM mytable; .

Presumably, this is recognized as a valid request, because during lexical analysis, postgres reads SELECT from the input as a token, and then looks for the next token that it finds as * , and so on - is it more or less what happens here?

Also, is Postgres lexer / parser enough to be strong enough to understand this query, or do other databases understand a similar SELECT* query?

Answer 1

Typically, lexers will add characters to the current token until they find a character that cannot belong to the current token, then it terminates and starts from the point where it could not continue.

So what is happening here is that the lexer takes away the SELECT and sees that the next character is * , which, as it collects the word, cannot belong to SELECT . So it stops, parses the SELECT , which turns out to be a keyword, and starts with * , which it recognizes, and so on. This is the same reason you get 4 from 2*2 and 2 * 2 in other programming languages.

As for whether it will work in other databases, it all depends on the details of the lexical analyzer and the rules of grammar.

Answer 2

Apparently, the tokenizer symbolizes the space and special characters used in arithmetic.

Here are the BNF SELECT statements: h2database.com :

 SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,...] FROM tableExpression [,...] [ WHERE expression ] [ GROUP BY expression [,...] ] [ HAVING expression ] [ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT } select ] [ ORDER BY order [,...] ] [ LIMIT expression [ OFFSET expression ] [ SAMPLE_SIZE rowCountInt ] ] [ FOR UPDATE ] 

Answer 3

As far as I know, SQL skips parsing white space, so you can do SELECT*FROM or SELECT * FROM basically the same.

He also uses ` and ' to understand what's what. Thus, SELECT * FROM myTable WHERE id = my string will be an invalid query because the "string" in is not understood.