SELECT 'foo' IN ('foo'
Be careful out there.
Yeah, there is no comma between the foo and bar.
can someone explain why this is a valid statement? Shouldn't the missing comma throw an error?
It's because a newline between two single-quoted literals concatenates the two. Without the newline, this would not be valid SQL.
Yeah, the quirk of SQL string lexing. The standard is really weird on this point.
@Marko Tiikkaja: even without the newline postgresql doesn't throw any error: http://sqlfiddle.com/#!15/d41d8/1812/0
@Anonymous: That's a single string literal with an escaped single quote in the middle. See e.g. the result of SELECT 'foo''bar';
the correct syntax is:SELECT 'foo' IN ('foo', 'bar');
The point is that "SELECT 'foo' IN ('foo' 'bar')" will result an error, but the additional newline causes the strings to be concatenated, which will cause some head-scratching if you accidentally missed the comma.
Hmm, guess which other open-source database treats all whitespace (not just new lines) between string literals as an opportunity to concatenate them, no questions asked.
SQL Server 2012 reports an error: Incorrect syntax near the keyword 'in'.: SELECT 'foo' in ('foo' 'bar')
Did you try it with our without a newline? The SQL standard requires a newline between the literals for this to "work".
yes, I did and still got the same error. but maybe this is just sqlfiddle?
@Anonymous: That's interesting. Looks like the SQL server doesn't follow the standard here. For example:SELECT 'foo''bar';appears to be equivalent to SELECT 'foo' "bar"; i.e. a single column with the value of 'foo' called "bar", which obviously won't work in an IN list.
indeed strange. and oracle in every case expects "FROM" ORA-00923: FROM keyword not found where expected : SELECT 'foo' "bar"; You have touched interesting case ;)
MySQL (MariaDB) is strange too:MariaDB [(none)]> SELECT 'foo',('foo' 'bar');+-----+--------+| foo | foo |+-----+--------+| foo | foobar |+-----+--------+I concatenates the two strings.