Bug #105899 Result mismatch when derived_merge

• Submitted: 15 Dec 2021 3:23
• Modified: 15 Dec 2021 6:15
• Reporter: Ze Yang (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.27, 5.7
• OS: Any
• CPU Architecture: Any

[15 Dec 2021 3:23] Ze Yang

Description:
Result mismatch when derived_merge.
For query with left join derived table and depend subquery, result mismatch.

For below query, as ss.y is not null in subquery at where condition, the result is wrong.

```
SELECT * FROM   int8_tbl t1 LEFT JOIN   (SELECT q1 AS x, 42 AS y FROM int8_tbl t2) ss   ON t1.q2 = ss.x WHERE   1 = (SELECT 1 FROM int8_tbl t3 WHERE ss.y is not null limit 1) ORDER BY 1,2;
+----------------+----------------+------+------+
| q1             | q2             | x    | y    |
+----------------+----------------+------+------+
| -1204982102907 |           NULL | NULL | NULL |
|           -123 |           NULL | NULL | NULL |
|            123 |            456 | NULL | NULL |
|            123 |   456789456789 | NULL | NULL |
|    45678956789 |  -456789016789 | NULL | NULL |
|   456789056789 |            123 |  123 |   42 |
|   456789056789 |            123 |  123 |   42 |
|   456789056789 | 45678903456789 | NULL | NULL |
| 39082035958095 |           NULL | NULL | NULL |
+----------------+----------------+------+------+
```

When switch derived_merge to off, the result is right.
```
SELECT/*+no_merge(ss)*/ * FROM   int8_tbl t1 LEFT JOIN   (SELECT q1 AS x, 42 AS y FROM int8_tbl t2) ss   ON t1.q2 = ss.x WHERE   1 = (SELECT 1 FROM int8_tbl t3 WHERE ss.y is not null limit 1) ORDER BY 1,2;
+--------------+------+------+------+
| q1           | q2   | x    | y    |
+--------------+------+------+------+
| 456789056789 |  123 |  123 |   42 |
| 456789056789 |  123 |  123 |   42 |
+--------------+------+------+------+
```

How to repeat:
derived_merge_bug.test

```
▽
CREATE TABLE int8_tbl(q1 int8, q2 int8);

INSERT INTO int8_tbl VALUES('  123   ','  456');
INSERT INTO int8_tbl VALUES('123   ','456789456789');
INSERT INTO int8_tbl VALUES('456789056789','123');
INSERT INTO int8_tbl VALUES([phone],'45678903456789');
INSERT INTO int8_tbl VALUES('[phone]','-456789016789');

INSERT INTO int8_tbl(q1) VALUES ('39082035958095');
INSERT INTO int8_tbl(q1) VALUES ('-1204982102907');
INSERT INTO int8_tbl(q1) VALUES ('-123');
ANALYZE TABLE int8_tbl;

--echo
--echo Derived_merge
--echo
let $query=SELECT * FROM
  int8_tbl t1 LEFT JOIN
  (SELECT q1 AS x, 42 AS y FROM int8_tbl t2) ss
  ON t1.q2 = ss.x
WHERE
  1 = (SELECT 1 FROM int8_tbl t3 WHERE ss.y is not null limit 1)
ORDER BY 1,2;

--eval EXPLAIN $query
--eval $query

--echo
--echo Same query no merge
--echo
SET optimizer_switch='derived_merge=off';
--eval EXPLAIN $query
--eval $query

SET optimizer_switch='derived_merge=on';
let $query=SELECT/*+no_merge(ss)*/ * FROM
  int8_tbl t1 LEFT JOIN
  (SELECT q1 AS x, 42 AS y FROM int8_tbl t2) ss
  ON t1.q2 = ss.x
WHERE
  1 = (SELECT 1 FROM int8_tbl t3 WHERE ss.y is not null limit 1)
ORDER BY 1,2;

--eval EXPLAIN $query
--eval $query

DROP TABLE int8_tbl;
```

[15 Dec 2021 6:15] MySQL Verification Team

Hello Ze Yang,

Thank you for the report and feedback.

regards,
Umesh

[25 Jan 2022 15:40] Dag Wanvik

Posted by developer:
 
The query is re-written in this way:

SELECT * 
FROM int8_tbl t1
     LEFT JOIN
    ( SELECT q1 AS x, 
            42 AS y 
      FROM int8_tbl t2) ss
     ON t1.q2 = ss.x 
WHERE 1 = ( SELECT 1 
            FROM int8_tbl t3
            WHERE ss.y IS NOT NULL LIMIT 1)
ORDER BY 1,2;

->

/* select#1 */
select `t1`.`q1` AS `q1`,
       `t1`.`q2` AS `q2`,
       `t2`.`q1` AS `x`,
       42 AS `y` 
from `int8_tbl` `t1` 
     left join 
     (`int8_tbl` `t2`)
     on ((`t1`.`q2` = `t2`.`q1`))
where (1 = ( /* select#3 */ 
             select 1 from `int8_tbl` `t3` 
             where (42 is not null) limit 1))    # 42 is wrong here when right side joinee is NULL-completed
order by `t1`.`q1`,`t1`.`q2`

[25 Jan 2022 15:43] Dag Wanvik

Posted by developer:
 
A possible re-write which gives the right answer could be:

/* select#1 */ 
select `t1`.`q1` AS `q1`,
       `t1`.`q2` AS `q2`,
       `t2`.`q1` AS `x`,
       42 AS `y` 
from `int8_tbl` `t1` 
     left join 
     (`int8_tbl` `t2`) 
     on ((`t1`.`q2` = `t2`.`q1`))
where (1 = ( /* select#3 */ 
             select 1 
             from `int8_tbl` `t3`
              where (if(t2.q1 is null, null, 42) is not null) limit 1))

Note the if function test on right side join column