Concatenation behaves differently on two servers I have two servers (not master/slaves of each other), each running MySQL 5.1.71-log. 

One server 1 (which happens to be in the US), the following query returns good results:

select distinct(devicemode || ' - ' || model || ' - ' || version) as a from &lt;TABLE1&gt;, &lt;TABLE2&gt; where &lt;TABLE1&gt;.&lt;FKEY&gt; = &lt;TABLE2&gt;.key and &lt;TABLE1&gt;.key = 1234 order by devicemode ,model,version;


The results look like:

US - MicroMaxx - 30.80.306.024


When the same query is run on server 2 (which happens to be in the UK), I get different results:

0


If I change the query to be:

select distinct(concat(devicemode, ' - ', model,' - ', version) as a from &lt;TABLE1&gt;, &lt;TABLE2&gt; where &lt;TABLE1&gt;.&lt;FKEY&gt; = &lt;TABLE2&gt;.key and &lt;TABLE1&gt;.key = 1234 order by devicemode ,model,version;


the same results are returned on both servers:

US - MicroMaxx - 30.80.306.024


Any idea why there would be a difference?

Thanks

Alan
Most probably the two servers have different SQL_MODE settings.

One has PIPES_AS_CONCAT defined and the other not. The server that hasn't this setting, treats || ("pipes") as a synonym to OR and not as the string concatenation operator.