Description:
Since "FALSE and any_expr" is FALSE. MySQL can apply short-circuit evaluation to it. When the false expression is "0", "false", or "2<1", etc., the optimizer can rewrite it to "FALSE" directly. In another scenario, the executor can short-circuit when determining that one expression is false after scanning one table, and skip scanning another table.
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();
-- The optimizer can rewrite such a SQL statement to "FALSE" directly
SELECT (SELECT MAX(t2.c0) FROM t2)<1 AND FALSE;
+-----------------------------------------+
| (SELECT MAX(t2.c0) FROM t2)<1 AND FALSE |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set (2.991 sec)
explain SELECT (SELECT MAX(t2.c0) FROM t2)<1 AND FALSE;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 2 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 9738355 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
2 rows in set, 1 warning (0.001 sec)
-- The executor can short-circuit when determining that one expression is false after scanning one table, and skip scanning another table.
SELECT (SELECT MAX(t2.c0) FROM t2)<1 AND (SELECT MAX(t1.c0) FROM t1)<1;
+-----------------------------------------------------------------+
| (SELECT MAX(t2.c0) FROM t2)<1 AND (SELECT MAX(t1.c0) FROM t1)<1 |
+-----------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------+
1 row in set (3.151 sec)
explain SELECT (SELECT MAX(t2.c0) FROM t2)<1 AND (SELECT MAX(t1.c0) FROM t1)<1;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 3 | SUBQUERY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 9738355 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
3 rows in set, 1 warning (0.001 sec)
Description: Since "FALSE and any_expr" is FALSE. MySQL can apply short-circuit evaluation to it. When the false expression is "0", "false", or "2<1", etc., the optimizer can rewrite it to "FALSE" directly. In another scenario, the executor can short-circuit when determining that one expression is false after scanning one table, and skip scanning another table. 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(); -- The optimizer can rewrite such a SQL statement to "FALSE" directly SELECT (SELECT MAX(t2.c0) FROM t2)<1 AND FALSE; +-----------------------------------------+ | (SELECT MAX(t2.c0) FROM t2)<1 AND FALSE | +-----------------------------------------+ | 0 | +-----------------------------------------+ 1 row in set (2.991 sec) explain SELECT (SELECT MAX(t2.c0) FROM t2)<1 AND FALSE; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 9738355 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ 2 rows in set, 1 warning (0.001 sec) -- The executor can short-circuit when determining that one expression is false after scanning one table, and skip scanning another table. SELECT (SELECT MAX(t2.c0) FROM t2)<1 AND (SELECT MAX(t1.c0) FROM t1)<1; +-----------------------------------------------------------------+ | (SELECT MAX(t2.c0) FROM t2)<1 AND (SELECT MAX(t1.c0) FROM t1)<1 | +-----------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------+ 1 row in set (3.151 sec) explain SELECT (SELECT MAX(t2.c0) FROM t2)<1 AND (SELECT MAX(t1.c0) FROM t1)<1; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 3 | SUBQUERY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 9738355 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ 3 rows in set, 1 warning (0.001 sec)