5

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

SELECT* FROM mytable;

Since I recently coded a parser for another language, I am trying to understand in more depth why this query "compiles" and returns the same results as SELECT * FROM mytable;.

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

Also, does the postgres lexer/parser just happen to be robust enough to understand this query, or would other databases understand a similar SELECT* query?

Justin Ethier
  • 131,333
  • 52
  • 229
  • 284
  • 1
    works on MySQL too, not on Oracle APEX. – Jacob Aug 06 '11 at 16:22
  • Not sure what APEX has to do with it. – Steve Aug 07 '11 at 06:55
  • Are you confused by the fact that the DBs accept "SELECT *" regardless of spacing, or are you surprised that "SELEcT *" is accepted regardless of spacing? The latter is a standard in programming languages in general; whitespace is generally optional. I don't know how common "SELECT *" is among database vendors, but what is says is clear enough. – Ira Baxter Aug 11 '11 at 04:53
  • Capitalization was never a concern or part of this question. Having worked in the industry for many years it quickly becomes obvious that SQL is not case sensitive - however in many general purpose programming languages that is not the case. – Justin Ethier Aug 11 '11 at 11:47

3 Answers3

5

Usually lexers will add characters to the current token until it finds a character that can't belong to the current token, then it quits and starts over from where it couldn't go on before.

So what's going on here is that the lexer gobbles up SELECT and sees that the next character is a * which, because it's collecting a word, can't belong with SELECT. So it stops, analyzes the SELECT which turns out to be a keyword, and starts over with the *, which it recognizes, and so on. It's the same reason why you get 4 from both 2*2 and 2 * 2 in other programming languages.

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

Seth Carnegie
  • 73,875
  • 22
  • 181
  • 249
3

Apparently, the tokenizer tokenizes on white space and special characters used in arithmetic.

Here's the BNF of 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 ]
nes1983
  • 15,209
  • 4
  • 44
  • 64
2

As far as i know SQL skips parsing white-space so you can do SELECT*FROM or SELECT * FROM it's basically the same.

It also uses ` and ' to understand what is what. So SELECT * FROM myTable WHERE id = my string would be a invalid query because the "string" at the and is not understood.

Abel
  • 56,041
  • 24
  • 146
  • 247
blejzz
  • 3,349
  • 4
  • 39
  • 60