| Bug #111852 | An incorrect number of records is returned | ||
|---|---|---|---|
| Submitted: | 23 Jul 2023 12:46 | Modified: | 24 Jul 2023 7:27 |
| Reporter: | Luuk V | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.30, 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[24 Jul 2023 7:27]
MySQL Verification Team
Hello Luuk V, Thank you for the report and test case. regards, Umesh
[27 Jul 2023 1:15]
huahua xu
Hi Luuk V,
You could avoid the issue by disabling the derived tables merging. Perhaps the issue is produced when the optimizer merges the derived tables and view references into the outer query block.
mysql> set optimizer_switch = 'derived_merge=on';
mysql> explain format=tree with ints as (
-> select 1 as i
-> union all select 2
-> union all select 3
-> union all select 4
-> union all select 5
-> union all select 6
-> union all select 7
-> union all select 8
-> union all select 9
-> union all select 10
-> ),cte as (
-> select x from (
-> select floor(rand()*10)+1 as x
-> from ints
-> ) y
-> -- limit 10
-> )
-> select
-> i.i, c1.x
-> from ints i
-> inner join cte c1 on c1.x=i.i
-> order by i.i;
+----------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: i.i
-> Stream results (cost=16.25 rows=100)
-> Filter: ((floor((rand() * 10)) + 1) = i.i) (cost=16.25 rows=100)
-> Inner hash join (no condition) (cost=16.25 rows=100)
-> Table scan on ints (cost=1.26..3.62 rows=10)
-> Materialize union CTE ints if needed (query plan printed elsewhere) (cost=1.00..1.00 rows=10)
-> Hash
-> Table scan on i (cost=1.26..3.62 rows=10)
-> Materialize union CTE ints if needed (cost=1.00..1.00 rows=10)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
|
+----------------------------------------------------------------------------------------------------------------------------------+
mysql> set optimizer_switch = 'derived_merge=off';
mysql> explain format=tree with ints as (
-> select 1 as i
-> union all select 2
-> union all select 3
-> union all select 4
-> union all select 5
-> union all select 6
-> union all select 7
-> union all select 8
-> union all select 9
-> union all select 10
-> ),cte as (
-> select x from (
-> select floor(rand()*10)+1 as x
-> from ints
-> ) y
-> -- limit 10
-> )
-> select
-> i.i, c1.x
-> from ints i
-> inner join cte c1 on c1.x=i.i
-> order by i.i;
+----------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=14.95 rows=20)
-> Sort: i.i (cost=7.95..7.95 rows=10)
-> Table scan on i (cost=1.26..3.62 rows=10)
-> Materialize union CTE ints if needed (cost=1.00..1.00 rows=10)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Rows fetched before execution (cost=0.00..0.00 rows=1)
-> Filter: (c1.x = i.i) (cost=4.38..0.52 rows=2)
-> Covering index lookup on c1 using <auto_key0> (x=i.i) (cost=8.51..8.77 rows=2)
-> Materialize CTE cte (cost=8.25..8.25 rows=10)
-> Table scan on y (cost=4.89..7.25 rows=10)
-> Materialize (cost=4.62..4.62 rows=10)
-> Table scan on ints (cost=1.26..3.62 rows=10)
-> Materialize union CTE ints if needed (query plan printed elsewhere) (cost=1.00..1.00 rows=10)
|
+----------------------------------------------------------------------------------------------------------------------------------+
Description: An incorrect number of records (not 10) is returned from the following query, I am expecting that 10 records are returned, but this will only happen when I uncomment the `limit 10` ``` with ints as ( select 1 as i union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 ),cte as ( select x from ( select floor(rand()*10)+1 as x from ints ) y -- limit 10 ) select i.i, c1.x from ints i inner join cte c1 on c1.x=i.i order by i.i; ``` How to repeat: Repeat the given statement, and it will return a random number of records. The given statement is available as DBFIDDLE here: https://dbfiddle.uk/MSMxnTv7 Suggested fix: Always return the, IMHO, correct number of 10 records.