Saturday, April 26, 2014

SQL gotcha of the week

SELECT 'foo' IN ('foo'
                 'bar');
 ?column? 
----------
 f
(1 row)

Be careful out there.

16 comments:

  1. Yeah, there is no comma between the foo and bar.

    ReplyDelete
  2. can someone explain why this is a valid statement? Shouldn't the missing comma throw an error?

    ReplyDelete
    Replies
    1. It's because a newline between two single-quoted literals concatenates the two. Without the newline, this would not be valid SQL.

      Delete
    2. Yeah, the quirk of SQL string lexing. The standard is really weird on this point.

      Delete
    3. @Marko Tiikkaja: even without the newline postgresql doesn't throw any error: http://sqlfiddle.com/#!15/d41d8/1812/0

      Delete
    4. @Anonymous: That's a single string literal with an escaped single quote in the middle. See e.g. the result of SELECT 'foo''bar';

      Delete
  3. the correct syntax is:
    SELECT 'foo' IN ('foo', 'bar');

    ReplyDelete
    Replies
    1. 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.

      Delete
  4. 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.

    ReplyDelete
  5. SQL Server 2012 reports an error: Incorrect syntax near the keyword 'in'.: SELECT 'foo' in ('foo' 'bar')

    ReplyDelete
    Replies
    1. Did you try it with our without a newline? The SQL standard requires a newline between the literals for this to "work".

      Delete
    2. yes, I did and still got the same error. but maybe this is just sqlfiddle?

      Delete
    3. @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.

      Delete
    4. 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 ;)

      Delete
  6. MySQL (MariaDB) is strange too:
    MariaDB [(none)]> SELECT 'foo',('foo' 'bar');
    +-----+--------+
    | foo | foo |
    +-----+--------+
    | foo | foobar |
    +-----+--------+

    I concatenates the two strings.

    ReplyDelete