Bug #112037 Contribute by tencent: explain analyze show never executed for materialize

• Submitted: 11 Aug 2023 3:45
• Modified: 11 Aug 2023 10:37
• Reporter: tianfeng li (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.32, 8.0.34
• OS: Any
• CPU Architecture: Any

[11 Aug 2023 3:45] tianfeng li

Description:
Explain analyze may show never executed for materializeIterator.

How to repeat:
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
ANALYZE TABLE t1;
SET optimizer_switch='subquery_to_derived=on';
EXPLAIN analyze SELECT a + (SELECT SUM(a) FROM (SELECT a FROM t1 GROUP BY a) t2 ) FROM t1;

The result:
-> Left hash join (no condition)  (cost=12.48 rows=4) (actual time=0.139..0.330 rows=4 loops=1)
    -> Table scan on t1  (cost=0.65 rows=4) (actual time=0.076..0.260 rows=4 loops=1)
    -> Hash
        -> Table scan on derived_1_2  (cost=2.51..2.51 rows=1) (actual time=0.003..0.004 rows=1 loops=1)
            -> Materialize  (cost=9.56..9.56 rows=1) (never executed)
                -> Aggregate: sum(t2.a)  (cost=6.95..6.95 rows=1) (never executed)
                    -> Table scan on t2  (cost=0.64..2.55 rows=4) (never executed)
                        -> Materialize  (cost=4.64..6.55 rows=4) (never executed)
                            -> Table scan on <temporary>  (cost=0.64..2.55 rows=4) (never executed)
                                -> Temporary table with deduplication  (cost=1.69..3.60 rows=4) (never executed)
                                    -> Table scan on t1  (cost=0.65 rows=4) (never executed)

Suggested fix:
query_block.subquery_iterator is different from subquery_path.iterator

[11 Aug 2023 10:37] MySQL Verification Team

Hello tianfeng li,

Thank you for the report and test case.

regards,
Umesh