Query issues migrating from 4.1.22 to 5.0.67
Posted by: Matt Miller
Date: June 17, 2009 05:11PM

Hello,

I'm in the process of a server move and the new server runs MySQL 5.0.67 whereas our old server runs 4.1.22.

Most of the sql queries were fine. There were a few that had this bug:
http://www.delphifaq.com/faq/databases/mysql/f1110.shtml
so I had to remove the commas and put in JOIN statements. No big deal.

However, there is one mysql statement that doesn't return the desired results. It doesn't throw any errors, but it doesn't perform the same way it did in version 4.1.22.

The query is actually a large UNION of 3 queries. I'll post one part of the UNION here because the other two are basically the same.

SELECT 1, `is_leaf`, `score`, `creation`, `objects`.`object_name`, `objects`.`object_id`, `types`.`type_name`, `objects`.`media_file`, `users`.`username`, `users`.`user_id`
FROM `users`, `types`, `object_scores`
LEFT JOIN `object_map` ON `object_map`.`parent_id`='171197'
RIGHT JOIN `objects` ON `object_map`.`object_id` = `objects`.`object_id`
WHERE `types`.`type_id` = `objects`.`type_id`
AND `objects`.`type_id`>0
AND `objects`.`object_id`!='171197'
AND `objects`.`detached`=0
AND `object_scores`.`object_id`=`objects`.`object_id`
AND `users`.`user_id`=`objects`.`created_user_id`
AND `object_map`.`parent_id` IS NULL
AND `objects`.`type_id`!=3
AND MATCH (`object_name`, `object_body_nft`) AGAINST ('+longs' IN BOOLEAN MODE);

I'm not sure what the problem is exactly, but I'm fairly confident that it has something to do with the LEFT and/or RIGHT JOIN statements.

If I reduce the statement to this:
SELECT 1, `is_leaf`, `score`, `creation`, `objects`.`object_name`, `objects`.`object_id`, `types`.`type_name`, `objects`.`media_file`, `users`.`username`, `users`.`user_id`
FROM `users`, `types`, `object_scores`, objects
WHERE `types`.`type_id` = `objects`.`type_id`
AND `objects`.`type_id`>0
AND `objects`.`object_id`!='171197'
AND `objects`.`detached`=0
AND `object_scores`.`object_id`=`objects`.`object_id`
AND `users`.`user_id`=`objects`.`created_user_id`
AND `objects`.`type_id`!=3
AND MATCH (`object_name`, `object_body_nft`) AGAINST ('+longs' IN BOOLEAN MODE);

I get results, but obviously it's missing part of the logic.

Is there a known bug for RIGHT JOIN or LEFT JOIN statements used in conjuction with RIGHT JOINs when migrating from 4.1.22 to 5.0?

Thanks in advance for any help.
Matt

Options: ReplyQuote


Subject
Written By
Posted
Query issues migrating from 4.1.22 to 5.0.67
June 17, 2009 05:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.