Description:
Create tables:
CREATE TABLE `t1` (
`id` INT NOT NULL AUTO_INCREMENT,
`a_id` INT NOT NULL,
`b_id` INT NOT NULL,
`v_id` INT NOT NULL,
`p_id` INT NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `t1_idx1` (`a_id`, `b_id`, `p_id`, `v_id`, `id`)
);
CREATE TABLE `t2` (
`id` INT NOT NULL AUTO_INCREMENT,
`a_id` INT NOT NULL,
`b_id` INT NOT NULL,
`v_id` INT NOT NULL,
`p_id` INT NOT NULL,
`j_id` INT NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_t1_t2` FOREIGN KEY (`a_id`, `b_id`, `p_id`, `v_id`, `j_id`) REFERENCES `t1` (`a_id`, `b_id`, `p_id`, `v_id`, `id`)
);
Execute attached testdata.sql.
Query #1:
SELECT COUNT(*) FROM t1 WHERE (EXISTS (SELECT * FROM t2 WHERE t2.j_id=t1.id AND t2.a_id=1));
Executes super fast.
Query #2:
SELECT COUNT(*) FROM t1 WHERE (EXISTS (SELECT * FROM t2 WHERE t2.j_id=t1.id AND t2.a_id=1)) = 1;
Executes much much slower.
Expected results.
Query #2 runs as fast as query #1.
How to repeat:
See description.
Suggested fix:
Comparing the result of EXISTS() with any constant should not affect query execution performance.
I.e., the condition of the form "(EXISTS (subquery)) = 1" should be optimized into "(EXISTS (subquery))" and run as fast.
Analogous, the condition of the form "(EXISTS (subquery)) = 0" should be optimized into "(NOT EXISTS (subquery))" and run as fast.
