-- Pure SQL script to show difference in output.  Works as expected in
-- version 5.1.41-3ubuntu12.3, but not in 5.1.49-1ubuntu8 and 5.1.51
-- (compiled from source).
-- 

USE mysql_demo;

DROP TABLE IF EXISTS activity_participant;
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS activity;

BEGIN; 
CREATE TABLE user (
	user_id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(50), 
	PRIMARY KEY (user_id)
)ENGINE=InnoDB;

CREATE TABLE activity (
	activity_id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(50), 
	PRIMARY KEY (activity_id)
)ENGINE=InnoDB;

CREATE TABLE activity_participant (
	activity_id INTEGER, 
	user_id INTEGER, 
	FOREIGN KEY(activity_id) REFERENCES activity (activity_id), 
	FOREIGN KEY(user_id) REFERENCES user (user_id)
)ENGINE=InnoDB;
COMMIT;

INSERT INTO activity (name) VALUES ('writing #0');
INSERT INTO activity (name) VALUES ('writing #1');
INSERT INTO activity (name) VALUES ('writing #2');
INSERT INTO activity (name) VALUES ('writing #3');
INSERT INTO user (name) VALUES ('Demo 1');
INSERT INTO user (name) VALUES ('Demo 2');

SELECT 'This works in all tested versions; 5.1.41-3ubuntu12.3, but not in 5.1.49-1ubuntu8 and 5.1.51:' as 'NOTE 1:';

INSERT INTO activity_participant (activity_id, user_id) VALUES (2, 1);
INSERT INTO activity_participant (activity_id, user_id) VALUES (4, 1);
COMMIT;
BEGIN;

SELECT 'EXPLAIN' AS '';
EXPLAIN SELECT activity.activity_id AS activity_activity_id, 
       activity.name AS activity_name 
FROM activity, activity_participant AS activity_participant_1 
WHERE activity.activity_id = 2 
      AND activity.activity_id = activity_participant_1.activity_id 
      AND 1 = activity_participant_1.user_id;

SELECT 'RESULT' AS '';
SELECT activity.activity_id AS activity_activity_id, 
       activity.name AS activity_name 
FROM activity, activity_participant AS activity_participant_1 
WHERE activity.activity_id = 2 
      AND activity.activity_id = activity_participant_1.activity_id 
      AND 1 = activity_participant_1.user_id;

-- Observed result (in all versions):
--   activity_activity_id	activity_name
--   2	  writing #1

-- Please note that we now delete all entries.
DELETE FROM activity_participant;

SELECT '';
SELECT 'This works in version 5.1.41-3ubuntu12.3, but does not work in versions 5.1.49-1ubuntu8 and 5.1.51 (from source):' as 'NOTE 2:';

INSERT INTO activity_participant (activity_id, user_id) VALUES (1, 2);
INSERT INTO activity_participant (activity_id, user_id) VALUES (2, 2);
INSERT INTO activity_participant (activity_id, user_id) VALUES (2, 1);
INSERT INTO activity_participant (activity_id, user_id) VALUES (4, 1);
COMMIT;
BEGIN;

SELECT 'EXPLAIN' AS '';
EXPLAIN SELECT activity.activity_id AS activity_activity_id, 
       activity.name AS activity_name 
FROM activity, activity_participant AS activity_participant_1 
WHERE activity.activity_id = 2 
      AND activity.activity_id = activity_participant_1.activity_id 
      AND 1 = activity_participant_1.user_id;

SELECT 'RESULT' AS '';
SELECT activity.activity_id AS activity_activity_id, 
       activity.name AS activity_name 
FROM activity, activity_participant AS activity_participant_1 
WHERE activity.activity_id = 2 
      AND activity.activity_id = activity_participant_1.activity_id 
      AND 1 = activity_participant_1.user_id;

-- Observed result (in version 5.1.41-3ubuntu12.3):
--   activity_activity_id	activity_name
--   2	  writing #1

-- In versions 5.1.49-1ubuntu8 and 5.1.51 the resultset is empty.