Description:
Both innodb and myisam engines return content in the table room when it shouldnt
Query 1:
select ra.room_id, r.id, count(rmcount.account_id) from room_access ra left join room r on r.id=ra.room_id left join room_member rmcount on rmcount.room_id=ra.room_id
where ra.account_id in (0, 26412) and ra.domain_id in (0, 2602) and ra.group_id in (0) and ra.room_id=2;
OK, expected data in return:
+---------+------+---------------------------+
| room_id | id | count(rmcount.account_id) |
+---------+------+---------------------------+
| NULL | NULL | 0 |
+---------+------+---------------------------+
Query 2:
select ra.room_id, r.id, count(rmcount.account_id) from room_access ra left join room r on r.id=ra.room_id left join room_member rmcount on rmcount.room_id=ra.room_id
where ra.account_id in (0, 26412) and ra.domain_id in (0, 2602) and ra.group_id in (0) and ra.room_id=2 and ra.room_id=r.id;
Illegal room row data in return:
+---------+----+---------------------------+
| room_id | id | count(rmcount.account_id) |
+---------+----+---------------------------+
| NULL | 2 | 0 |
+---------+----+---------------------------+
How to repeat:
CREATE TABLE `room` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` bigint(20) unsigned NOT NULL,
`domain_id` bigint(20) unsigned NOT NULL,
`server_database_id` int(10) unsigned NOT NULL DEFAULT '3',
`name` varchar(64) NOT NULL,
`description` tinytext,
`last_message_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`moderated` tinyint(1) NOT NULL DEFAULT '0',
`securitylevel` tinyint(3) unsigned NOT NULL DEFAULT '0',
`enabled` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `domain_id` (`domain_id`,`name`),
KEY `account_id` (`account_id`),
KEY `account_id_2` (`account_id`,`domain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
CREATE TABLE `room_access` (
`room_id` bigint(20) unsigned NOT NULL,
`account_id` bigint(20) unsigned NOT NULL,
`domain_id` bigint(20) unsigned NOT NULL,
`group_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`is_admin` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`room_id`,`account_id`,`domain_id`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `room_member` (
`room_id` bigint(20) unsigned NOT NULL,
`account_id` bigint(20) unsigned NOT NULL,
`last_message_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`last_online` datetime NOT NULL,
`is_admin` tinyint(1) NOT NULL DEFAULT '0',
`is_moderator` tinyint(1) NOT NULL DEFAULT '0',
`notification_sound` tinyint(1) NOT NULL DEFAULT '0',
`notification_desktop` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`room_id`,`account_id`),
KEY `room_id` (`room_id`),
KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
select * from room where id=2;
+----+------------+-----------+--------------------+----------+-------------------------+-----------------+-----------+---------------+---------+
| id | account_id | domain_id | server_database_id | name | description | last_message_id | moderated | securitylevel | enabled |
+----+------------+-----------+--------------------+----------+-------------------------+-----------------+-----------+---------------+---------+
| 2 | 1 | 4 | 3 | Test-room | | 46 | 0 | 0 | 1 |
+----+------------+-----------+--------------------+----------+-------------------------+-----------------+-----------+---------------+---------+
select * from room_access where account_id in (0, 26412) and domain_id in (0, 2602) and group_id in (0) and room_id=2;
Empty set (0.00 sec)
select count(*) from room_member where room_id=2;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
Suggested fix:
Since no rows match room_access, the join on room should not return any rows (Second column id should be NULL).
However, it does, but only if ra.room_id=r.id is applied to the where-query.
Ra.room_id is null, so this doesnt make any sense..
