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 ,  where . = .key and .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 ,  where . = .key and .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.

StackExchange Database Administrators Q#68425, answer score: 6