I'm not sure if this is a bug, so I'd like to show it here first. When running this query, the mere existence of a primary key on a table changes the results of the query. See post at
http://stackoverflow.com/questions/41369058/mysql-pivot-table-existance-of-primary-key-changes-query-results/41370216
##### Create tables
mysql> CREATE TABLE `table1` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> )
-> COLLATE='latin1_swedish_ci'
-> ENGINE=InnoDB
-> AUTO_INCREMENT=5
-> ;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE `table2` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> )
-> COLLATE='latin1_swedish_ci'
-> ENGINE=InnoDB
-> AUTO_INCREMENT=5
-> ;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE `table1_table2` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `table1_id` INT(10) UNSIGNED NULL DEFAULT NULL,
-> `table2_id` INT(10) UNSIGNED NULL DEFAULT NULL,
-> `created_at` TIMESTAMP NULL DEFAULT NULL,
-> `updated_at` TIMESTAMP NULL DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> INDEX `table1_id_table1id` (`table1_id`),
-> INDEX `table2_id_table2id` (`table2_id`),
-> CONSTRAINT `table1_id_table1id` FOREIGN KEY (`table1_id`) REFERENCES `table1` (`id`) ON DELETE CASCADE,
-> CONSTRAINT `table2_id_table2id` FOREIGN KEY (`table2_id`) REFERENCES `table2` (`id`) ON DELETE CASCADE
-> )
-> COLLATE='utf8_unicode_ci'
-> ENGINE=InnoDB
-> AUTO_INCREMENT=7
-> ;
Query OK, 0 rows affected (0.03 sec)
##### Populate tables
mysql> INSERT INTO `table1` (`id`) VALUES
-> (1),
-> (2),
-> (3),
-> (4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `table2` (`id`) VALUES
-> (1),
-> (2),
-> (3),
-> (4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `table1_table2` (`id`, `table1_id`, `table2_id`, `created_at`, `updated_at`) VALUES
-> (1, 1, 3, '2016-12-28 14:47:40', '2016-12-28 14:47:41'),
-> (2, 1, 1, '2016-12-28 14:47:37', '2016-12-28 14:47:39'),
-> (3, 2, 3, '2016-12-28 14:59:48', '2016-12-28 14:59:48'),
-> (4, 3, 1, '2016-12-28 14:51:38', '2016-12-28 14:51:38'),
-> (5, 3, 2, '2016-12-28 14:52:33', '2016-12-28 14:52:34'),
-> (6, 3, 3, '2016-12-28 14:55:05', '2016-12-28 14:55:05'),
-> (7, 3, 4, '2016-12-28 14:56:48', '2016-12-28 14:56:48');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
##### NO RESULTS
mysql> SELECT *
-> FROM `table1`
-> WHERE EXISTS (
-> SELECT *
-> FROM `table2`
-> INNER JOIN `table1_table2` ON `table2`.`id` = `table1_table2`.`table2_id`
-> WHERE `table1_table2`.`table1_id` = `table1`.`id`
-> AND `table2`.`id` = 3)
-> AND `table1`.`id` = 3;
Empty set (0.00 sec)
###### DROP PRIMARY KEY OF PIVOT TABLE
mysql> ALTER TABLE `table1_table2`
-> DROP COLUMN `id`;
Query OK, 7 rows affected (0.09 sec)
Records: 7 Duplicates: 0 Warnings: 0
###### CORRECT RESULTS
mysql> SELECT *
-> FROM `table1`
-> WHERE EXISTS (
-> SELECT *
-> FROM `table2`
-> INNER JOIN `table1_table2` ON `table2`.`id` = `table1_table2`.`table2_id`
-> WHERE `table1_table2`.`table1_id` = `table1`.`id`
-> AND `table2`.`id` = 3)
-> AND `table1`.`id` = 3;
+----+
| id |
+----+
| 3 |
+----+
1 row in set (0.00 sec)
mysql>