INSTR() and GROUP_CONCAT() returning varying results between servers

Question

I'm having an issue running the same query on a development and production server. My application stores conference speakers and sessions. The development server, running MySQL 5.1, properly returns only the correlating speakers for an event session, but the production server, running MySQL 5.0.9, returns multiple speakers not necessarily associated with that session. (Note: I cannot bring production server up to 5.1 at this time.)

Speakers table:

+------------+--------------+
| speaker_id |     name     |
+------------+--------------+
|        100 | John Doe     |
|        200 | Steve Rogers |
|        300 | Tony Stark   |
+------------+--------------+

Sessions table:

 +------------+------------------+
| session_id | session_speakers |
+------------+------------------+
|          1 | [100]            |
|          2 | [100,200]        |
|          3 | [300]            |
+------------+------------------+

SELECT 
s.session_id, 
GROUP_CONCAT(DISTINCT sp.name SEPARATOR ' & '), 
FROM 
sessions s
LEFT JOIN 
speakers sp ON INSTR(s.session_speakers, sp.speaker_id)
GROUP BY 
s.session_id

Development server results for session_id = 1

'John Doe'

Typical production server results for session_id = 1

'John Doe & Steve Rogers & &'

Answer 1

You should normalize your table layout:

speakers
+------------+--------------+
| speaker_id |     name     |
+------------+--------------+
|        100 | John Doe     |
|        200 | Steve Rogers |
|        300 | Tony Stark   |
+------------+--------------+

and

sessions_to_speakers
 +------------+------------------+
| session_id | session_speakers |
+------------+------------------+
|          1 | 100            |
|          2 | 100            |
|          2 | 200            |
|          3 | 300            |
+------------+------------------+

and

sessions
+------------+--------------+
| session_id |     name     |
+------------+--------------+
|          1 | Session 1    |
|          2 | Session 2    |
|          3 | Session 3    |
+------------+--------------+

Then you could use a simple "join" to get all the Speakers:

SELECT * FROM speakers INNER JOIN sessions_to_speakers ON 
sessions_to_speakers.session_speakers = speakers.speaker_id
WHERE sessions_to_speakers.session_id = 1;

Suggestion: Fix your column names. don't repeat table names if its not a foreign key. i.e. the id of the speaker table should be called id (not speaker_id). ONLY when referenced from the session_to_speaker table, it should be called speaker_id (same for session and the reference)