Bug #118585 system variable "range_alloc_block_size" arouse different execution plan
Submitted: 3 Jul 16:35 Modified: 4 Jul 6:36
Reporter: John Smith Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.39, 8.0.42, 8.4.5, 9.3.0 OS:Ubuntu (Ubuntu 20.04.2)
Assigned to: CPU Architecture:Any
Tags: execution plan switch, hint, system variable

[3 Jul 16:35] John Smith 
Description:
After I create the database as follows:

CREATE DATABASE database0;
USE database0;

CREATE TABLE t0(c0 VARCHAR(500), c1 INT, c2 DOUBLE) ;
CREATE TABLE IF NOT EXISTS t1(c0 DOUBLE, c1 DOUBLE, c9 DOUBLE UNIQUE) ;
CREATE TABLE IF NOT EXISTS t2 LIKE t1;

SET @@cte_max_recursion_depth=1000000000;
SET @num_rows = 1000000;   -- number of rows to generate

-- generate large number of  data 
INSERT IGNORE INTO t1(c0, c1, c9)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < @num_rows
)
SELECT 
  n,   -- c0
  n,   -- c1
  n    -- c9
FROM seq;

INSERT IGNORE INTO t2(c0, c1, c9)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < @num_rows
)
SELECT 
  n,   -- c0
  n,   -- c1
  n    -- c9
FROM seq;

I execute the SQL statement, it returns blank plan immediately(0 ms):

EXPLAIN ANALYZE SELECT DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
    BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
GROUP BY  t1.c1;

mysql> EXPLAIN ANALYZE SELECT DISTINCTROW t1.c1
    -> FROM t2 NATURAL JOIN t1
    -> WHERE (t2.c9)
    ->     BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
    -> GROUP BY  t1.c1;
+--------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| -> Zero rows (no matching row in const table)  (cost=0..0 rows=0) (actual time=60e-6..60e-6 rows=0 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

then I add hint /*+ SET_VAR(range_alloc_block_size=10000000)*/, it generate execution plan and execute for a long time(650 ms):

EXPLAIN ANALYZE SELECT/*+ SET_VAR(range_alloc_block_size=10000000)*/ DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
    BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
GROUP BY  t1.c1;

mysql> EXPLAIN ANALYZE SELECT/*+ SET_VAR(range_alloc_block_size=10000000)*/ DISTINCTROW t1.c1
    -> FROM t2 NATURAL JOIN t1
    -> WHERE (t2.c9)
    ->     BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
    -> GROUP BY  t1.c1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=161390..161462 rows=5540) (actual time=650..650 rows=0 loops=1)
    -> Temporary table with deduplication  (cost=161390..161390 rows=5540) (actual time=650..650 rows=0 loops=1)
        -> Nested loop inner join  (cost=160836 rows=5540) (actual time=650..650 rows=0 loops=1)
            -> Filter: ((t2.c9 between exists(select #2) and <cache>(-(1))) and (t2.c9 is not null))  (cost=100977 rows=110795) (actual time=650..650 rows=0 loops=1)
                -> Table scan on t2  (cost=100977 rows=997255) (actual time=0.0227..541 rows=1e+6 loops=1)
                -> Select #2 (subquery in condition; run only once)
                    -> Zero rows (Impossible WHERE)  (cost=0..0 rows=0) (actual time=60e-6..60e-6 rows=0 loops=1)
            -> Filter: ((t1.c1 = t2.c1) and (t1.c0 = t2.c0))  (cost=0.44 rows=0.05) (never executed)
                -> Single-row index lookup on t1 using c9 (c9=t2.c9)  (cost=0.44 rows=1) (never executed)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 3 warnings (0.65 sec)

How to repeat:
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;

CREATE TABLE t0(c0 VARCHAR(500), c1 INT, c2 DOUBLE) ;
CREATE TABLE IF NOT EXISTS t1(c0 DOUBLE, c1 DOUBLE, c9 DOUBLE UNIQUE) ;
CREATE TABLE IF NOT EXISTS t2 LIKE t1;

SET @@cte_max_recursion_depth=1000000000;
SET @num_rows = 1000000;   -- number of rows to generate

-- generate large number of  data 
INSERT IGNORE INTO t1(c0, c1, c9)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < @num_rows
)
SELECT 
  n,   -- c0
  n,   -- c1
  n    -- c9
FROM seq;

INSERT IGNORE INTO t2(c0, c1, c9)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < @num_rows
)
SELECT 
  n,   -- c0
  n,   -- c1
  n    -- c9
FROM seq;

EXPLAIN ANALYZE SELECT DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
    BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
GROUP BY  t1.c1;

EXPLAIN ANALYZE SELECT/*+ SET_VAR(range_alloc_block_size=10000000)*/ DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
    BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
GROUP BY  t1.c1;
[4 Jul 3:02] John Smith 
Also, I try to make negetive digit to positive:

EXPLAIN ANALYZE SELECT DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
    BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (1)
GROUP BY  t1.c1;

and I get execution plan output in SQL without hint.

Then I delete the "UNIQUE" in t1, and don't change "-1" to "1":

CREATE TABLE IF NOT EXISTS t1(c0 DOUBLE, c1 DOUBLE, c9 DOUBLE);

The SQL as follows also output the execution plan:

EXPLAIN ANALYZE SELECT DISTINCTROW t1.c1
FROM t2 NATURAL JOIN t1
WHERE (t2.c9)
    BETWEEN ( EXISTS (SELECT 1 WHERE FALSE)) AND (-1)
GROUP BY  t1.c1;
[4 Jul 6:36] MySQL Verification Team 
Hello John Smith,

Thank you for the report and feedback.

regards,
Umesh