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 <TABLE1>, <TABLE2> where <TABLE1>.<FKEY> = <TABLE2>.key and <TABLE1>.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:


If I change the query to be:

select distinct(concat(devicemode, ' - ', model,' - ', version) as a from <TABLE1>, <TABLE2> where <TABLE1>.<FKEY> = <TABLE2>.key and <TABLE1>.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?




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.

  • Sounds plausible to me. +1 !!! Oops, 40 votes already ??? I'll upvote tomorrow. – RolandoMySQLDBA Jun 17 '14 at 18:31
  • @RolandoMySQLDBA haha, thnx. You do some serious voting, don't you? I don't think I've reached the 40 limit, ever. – ypercubeᵀᴹ Jun 17 '14 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.