Description:
MySQL has applied short-circuit evaluation for expressions like TRUE OR any_expr (evaluating to TRUE) in the WHERE clause. However, this optimization is not applied to FALSE AND any_expr expressions (which evaluate to FALSE).
How to repeat:
-- Create table t1 with 1 row and t2 with 10,000,000 rows
CREATE TABLE t1(c0 INT8);
CREATE TABLE t2(c0 INT8);
INSERT INTO t1 VALUES(1);
DELIMITER //
CREATE PROCEDURE batch_insert_numbers()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE batch_size INT DEFAULT 1000;
WHILE i <= 10000000 DO
START TRANSACTION;
WHILE i <= 10000000 AND batch_size > 0 DO
INSERT INTO t2(c0) VALUES (i);
SET i = i + 1;
SET batch_size = batch_size - 1;
END WHILE;
COMMIT;
SET batch_size = 1000;
END WHILE;
END //
DELIMITER ;
CALL batch_insert_numbers();
-- positive case
SELECT * FROM t1 WHERE t1.c0 IN (SELECT * FROM t2 WHERE t2.c0=t1.c0) OR t1.c0>0;
+------+
| c0 |
+------+
| 1 |
+------+
1 row in set (0.001 sec)
-- negative case
SELECT * FROM t1 WHERE t1.c0<0 AND t1.c0 IN (SELECT * FROM t2 WHERE t2.c0=t1.c0);
Empty set (4.866 sec)
SELECT * FROM t1 WHERE t1.c0<0;
Empty set (0.001 sec)
SELECT * FROM t1 WHERE t1.c0 IN (SELECT * FROM t2 WHERE t2.c0=t1.c0);
+------+
| c0 |
+------+
| 1 |
+------+
1 row in set (4.888 sec)
Description: MySQL has applied short-circuit evaluation for expressions like TRUE OR any_expr (evaluating to TRUE) in the WHERE clause. However, this optimization is not applied to FALSE AND any_expr expressions (which evaluate to FALSE). How to repeat: -- Create table t1 with 1 row and t2 with 10,000,000 rows CREATE TABLE t1(c0 INT8); CREATE TABLE t2(c0 INT8); INSERT INTO t1 VALUES(1); DELIMITER // CREATE PROCEDURE batch_insert_numbers() BEGIN DECLARE i INT DEFAULT 1; DECLARE batch_size INT DEFAULT 1000; WHILE i <= 10000000 DO START TRANSACTION; WHILE i <= 10000000 AND batch_size > 0 DO INSERT INTO t2(c0) VALUES (i); SET i = i + 1; SET batch_size = batch_size - 1; END WHILE; COMMIT; SET batch_size = 1000; END WHILE; END // DELIMITER ; CALL batch_insert_numbers(); -- positive case SELECT * FROM t1 WHERE t1.c0 IN (SELECT * FROM t2 WHERE t2.c0=t1.c0) OR t1.c0>0; +------+ | c0 | +------+ | 1 | +------+ 1 row in set (0.001 sec) -- negative case SELECT * FROM t1 WHERE t1.c0<0 AND t1.c0 IN (SELECT * FROM t2 WHERE t2.c0=t1.c0); Empty set (4.866 sec) SELECT * FROM t1 WHERE t1.c0<0; Empty set (0.001 sec) SELECT * FROM t1 WHERE t1.c0 IN (SELECT * FROM t2 WHERE t2.c0=t1.c0); +------+ | c0 | +------+ | 1 | +------+ 1 row in set (4.888 sec)