| Bug #110961 | Subquery to derived optimizer assertion error | ||
|---|---|---|---|
| Submitted: | 9 May 2023 12:29 | Modified: | 18 Apr 3:22 |
| Reporter: | Pedro Ferreira | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S6 (Debug Builds) |
| Version: | 8.0.33 | OS: | Ubuntu (22.04) |
| Assigned to: | CPU Architecture: | x86 (x86_64) | |
| Tags: | insert, Optimizer | ||
[9 May 2023 12:36]
MySQL Verification Team
Hello Pedro Ferreira, Thank you for the report and feedback. Observed that 8.0.33 debug build is affected. regards, Umesh
[2 Jun 2023 8:13]
Pedro Ferreira
Reproduced again today with: CREATE TABLE t0 (c0 INT); CREATE TABLE t1 (c0 INT); INSERT INTO t0(c0) VALUES (1),(2),(3); SET @@SESSION.OPTIMIZER_SWITCH = 'subquery_to_derived=on'; INSERT IGNORE INTO t1 (c0) (SELECT 1 FROM (SELECT 1) x(x) STRAIGHT_JOIN (SELECT 1) y(y) ON b'00' = (SELECT b'01' FROM t0));
[8 Mar 10:25]
Dag Wanvik
Posted by developer:
This happens because the transform will raise an error if there is more than one row in the subquery, but in another place
than the untransformed query. When INSERT has IGNORE as here, we install a non-strict error handler, which downgrades the
error we give: R_SUBQUERY_NO_1_ROW to a warning.
Later, in HashJoinIterator::Init(), we get an error return from MaterializeIterator<Profiler>::Init()
which checks for more than one row here:
if (m_reject_multiple_rows && stored_rows > 1) {
my_error(ER_SUBQUERY_NO_1_ROW, MYF(0));
return true;
but then HashJoinIterator::Init() seeing the error return asserts that the current THD::is_error is true, which it does not due to
the ignore downgrade.
[8 Mar 13:12]
Dag Wanvik
Posted by developer: In production (non-debug) builds, there is no crash, but the results differ: without the transform, two NULL values are inserted. With the transform, no rows are inserted and no warning is given.
[18 Apr 3:22]
Jon Stephens
Documented fix as follows in the MySQL 9.0.0 changelog:
ER_SUBQUERY_NO_1_ROW has been removed from the list of errors
which are ignored by statements which include the IGNORE
keyword. This has been done for the following reasons:
·Ignoring such errors sometimes led to insertion of NULL into
non-nullable columns (for untransformed subqueries), or of
no row at all (subqueries using subquery_to_derived).
·When subqueries were transformed to join with derived
tables, the behavior differed from that of untransformed
queries.
Following an upgrade to this release, this change can make an
UPDATE, DELETE, or INSERT statement which includes the IGNORE
keyword raise errors if it contains a SELECT statement with a
scalar subquery that produces more than one row.
For more information, see "The Effect of IGNORE on Statement
Execution".
Also removed ER_SUBQUERY_NO_1_ROW from the list in the indicated section of the 9.0 Manual.
Closed.
Description: Run these queries: CREATE TABLE t1 (c0 INT); INSERT INTO t1(c0) VALUES (1), (2); SET @@SESSION.OPTIMIZER_SWITCH = 'subquery_to_derived=on'; INSERT IGNORE INTO t1 (c0) (SELECT (SELECT t1.c0 FROM t1) FROM t1); The insert will trigger an assertion error at sql/iterators/hash_join_iterator.cc:183: assert(thd()->is_error() || thd()->killed); The compilation parameters are the same as issue 108148: -DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77 How to repeat: Run the queries above.