Different MYSQL Output with same Query

Question

I have three tables:

events:

id  |  name     
-------------------
1   |  Test
2   |  Another test

persons:

type            |  type_id  | name   |   user_id
------------------------------------------------
event_organizer |   318     |  NULL  |     22
event_owner     |   318     |  Rob   |     NULL 
event_owner     |   318     |  NULL  |     6

user:

id  |  forname  |  surname
--------------------------
6   |  Peter    |   Watson
7   |  Chris    |   Brown
22  |  Charlie  |   Teck

(Of course, the tables are much bigger than that, I just copied the relevant parts.)

This query:

SELECT event.*, 
  IF(persons.type='event_organizer', CONCAT_WS(', ', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), GROUP_CONCAT(persons.name SEPARATOR ', ')), NULL) AS organizer_names, 
  IF(persons.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND (persons.type = 'event_owner' OR persons.type = 'event_organizer') 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

should output me all event data and the names of the owner and the organizer. The output I get is:

Array ( [id] => 318 [name] => Test [organizer_names] => Peter Watson, Rob, Charlie Teck [owner_names] => ) 

I don't get why owner_names is always empty. If I remove the organizer_names part:

SELECT event.*, 
  IF(persons.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND persons.type = 'event_owner' 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

Than I get the right owner (Rob and Peter Watson). I can also change it to:

SELECT event.*, 
  IF(persons.type='event_organizer', CONCAT_WS(', ', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), GROUP_CONCAT(persons.name SEPARATOR ', ')), NULL) AS organizer_names, 
  IF(persons.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND persons.type = 'event_owner' 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

And this is working right too. So it seems that the second OR condition of the LEFT JOIN is destroying my owners :(

The reverse test (with same condition and without organizer_names):

SELECT event.*, 
  IF(persons.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND (persons.type = 'event_owner' OR persons.type = 'event_organizer') 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

brings also no owner_names output.

Where is my mistake?

Answer 1

Using GROUP BY aggregate functions without a GROUP BY clause is valid SQL. The standard says a single group is created from all the selected rows.

For this specific question, the appropriate GROUP BY clause would be GROUP BY event.id. However, it doesn't help because the WHERE clause already filters only the rows having a single value for event.id.

To debug the query remove all the aggregate functions in the SELECT clause and check that the correct rows are selected:

SELECT event.*, persons.*, users.*
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND (persons.type = 'event_owner' OR persons.type = 'event_organizer') 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

Conceptually, this is the first step executed by MySQL when it runs your original query. In practice, some shortcuts are taken and optimizations are done and the result set you get is not generated completely; but this is how it works in theory.

Most probably, the rows are correctly joined and filtered and the problem is in the way the values are aggregated.

Let's try to analyze how MySQL computes this expression:

IF(
    persons.type='event_owner', 
    GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '),
    NULL
) AS owner_names 

It uses all the rows selected by the query displayed above to compute a single value for the above expression. If you run the query you can see it produces rows that contain both 'event_owner' and 'event_organizer' in the persons.type column.

From the inside out, it uses all the values returned in the forname and surname columns to compute GROUP_CONCAT(forname, ' ', surname SEPARATOR ', ') then it uses one value from the persons.type column to check the IF() condition. But which one?

It is entitled to pick whatever value it wants for it from all the possible values of the persons.type column selected on the previous step. If, by chance, it uses 'event_owner' it returns the value you expect and you think the query is correct. When it uses 'event_organizer', well, you are puzzled and ask on StackOverflow ;)

The correct expression of owner_names is:

GROUP_CONCAT(
  IF(                      # for each row
    persons.type='event_owner', 
    CONCAT(forname, ' ', surname),       # full name for owner
    NULL                                 # nothing for organizer
  )                        # owner name or NULL
  SEPARATOR ', '           # concatenate all using separator
) as owner_names

In a similar fashion you can compute the organizer names.

Answer 2

I can't test this - SQLFiddle appears broken right now - but I think you need to explicitly join organizers and owners as separate joins. Something like:

SELECT event.*, 
organizers.*,  
IF(organizers.type='event_organizer', CONCAT_WS(', ', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), GROUP_CONCAT(persons.name SEPARATOR ', ')), NULL) AS organizer_names, 
owners.* ,
IF(owners.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons organizers ON organizers.type_id = event.id AND organizers.type = 'event_organizer'
left join persons owners on owners.type_id = event.id and owners.type = 'event_owner'
LEFT JOIN user ON user.id = owners.user_id 
left join user u2 on u2.id = organizers.user_id
WHERE event.id=?;