5

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:

0

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?

Thanks

Alan

1 Answer 1

6

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.

2
  • Sounds plausible to me. +1 !!! Oops, 40 votes already ??? I'll upvote tomorrow. Commented Jun 17, 2014 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. Commented Jun 17, 2014 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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