sql-info.de
April 27, 2014

MySQL Gotchas | Newlines and implicit string constant concatenation

Marko Tiikkaja points out this interesting SQL gotcha, where (in PostgreSQL) two string constants separated by a newline character are implicitly concatenated - whereas separated by any other whitespace, an error is raised:

postgres=# SELECT 'foo' IN ('foo','bar');
 ?column? 
----------
 t
(1 row)

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

postgres=# SELECT 'foo' IN ('foo' 'bar');
ERROR:  syntax error at or near "'bar'"
LINE 1: SELECT 'foo' IN ('foo' 'bar');

According to the PostgreSQL documentation, this slightly bizarre behavior is specified by SQL.

How does MySQL stack up in this admittedly obscure piece of SQL quirkiness?

It appears to deal with the newline in the expected way (expected if you're aware of the syntax, that is):

MariaDB [(none)]> SELECT 'foo' IN ('foo','bar');
+------------------------+
| 'foo' IN ('foo','bar') |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 'foo' IN ('foo'
    ->   'bar');
+--------------------------+
| 'foo' IN ('foo'
  'bar') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

But wait, what's this?

MariaDB [(none)]> SELECT 'foo' IN ('foo' 'bar');
+------------------------+
| 'foo' IN ('foo' 'bar') |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

It seems MySQL will concatenate any string constants separated by any white space:

Quoted strings placed next to each other are concatenated to a single string.

source

The server is running in this sql_mode:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

I haven't been able to find any mode settings which might modify the default behaviour.

Posted at 8:23 AM