MySQL result set changes based on select list

Question

So this is a bit weird as I've never had to deal with this issue before. I have a dataset with 4 basic joins to find the user who edited, created the record set.

SELECT `tasks`.`id`, `tasks`.`title`, `tasks`.`description`, `tasks`.`created_ts`, `tasks`.`modified_ts`, 
assigned_user_id,
`u1`.`id` AS `assigned_to_id`,
`u1`.`firstname` AS `assigned_to_firstname`, 
`u1`.`surname` AS `assigned_to_surname`,
`u2`.`id` AS `author_id`,
`u2`.`firstname` AS `author_firstname`,
`u2`.`surname` AS `author_surname`,
`u3`.`id` AS `modified_by_id`,
`u3`.`firstname` AS `modified_by_firstname`,
`u3`.`surname` AS `modified_by_surname`,
`t1`.`id`AS `priority_id`,
`t1`.`priority` AS `priority_priority`,
author_user_id,
modified_by_user_id,
type_priority_id
FROM tasks
LEFT OUTER JOIN `users` u1 ON `u1`.`id` = `tasks`.`assigned_user_id`
LEFT JOIN `users` u2 ON  `u2`.`id` = `tasks`.`author_user_id` 
LEFT JOIN `users` u3 ON `u3`.`id` = `tasks`.`modified_by_user_id` 
LEFT JOIN `type_priority` t1 ON `tasks`.`type_priority_id` = `t1`.`id`
LIMIT 100

when I run this query, I only get the results with a particular user (ID:1). It just skips all other records that have joins with different users. What's funny is, when I don't select the fields from user tables (u1, u2 and u3), I get all the results that I expect.

Answer 1

You aren't specifying an order, so the LIMIT picks the first 100 rows in whatever order the query read them.

That order depends on which index was chosen by the optimizer, and the table order. The optimizer tries to choose these based on what it thinks will be the most efficient given the columns and conditions in your query.

So it's possible that omitting some columns from the select-list will result in a different index, and therefore a different set of rows read first.

You should use EXPLAIN to analyze the table order and index choices for your two query forms.