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