| Bug #111418 | Subquery with ROLLUP gives wrong result | ||
|---|---|---|---|
| Submitted: | 14 Jun 2023 11:43 | Modified: | 15 Jun 2023 13:45 |
| Reporter: | Erik Frøseth (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[14 Jun 2023 13:19]
MySQL Verification Team
Hi Mr. Froseth, Thank you for your bug report. However, it is not a bug. We tested with 8.0.33 and got the following result: ERROR 1242 (21000) at line 5: Subquery returns more than 1 row Not a bug.
[14 Jun 2023 18:51]
Erik Frøseth
Hello. That is not what I see. This is on commit ea7087d885006918ad54458e7aad215b1650312c, debug build. Do we have the same version and EXPLAIN output? +--------------+ | VERSION() | +--------------+ | 8.0.33-debug | +--------------+ 1 row in set (0,03 sec) mysql> CREATE TABLE t0 (c0 INTEGER); Query OK, 0 rows affected (0,04 sec) mysql> INSERT INTO t0 VALUES (1); Query OK, 1 row affected (0,02 sec) mysql> SELECT (SELECT t0.c0 GROUP BY t0.c0 WITH ROLLUP) FROM t0; +-------------------------------------------+ | (SELECT t0.c0 GROUP BY t0.c0 WITH ROLLUP) | +-------------------------------------------+ | 1 | +-------------------------------------------+ 1 row in set (0,00 sec) mysql> EXPLAIN FORMAT=tree SELECT (SELECT t0.c0 GROUP BY t0.c0 WITH ROLLUP) FROM t0; +------------------------------------------+ | EXPLAIN | +------------------------------------------+ | -> Table scan on t0 (cost=0.35 rows=1) | +------------------------------------------+ 1 row in set, 3 warnings (0,00 sec)
[15 Jun 2023 12:33]
MySQL Verification Team
Hi, Yes, we still get the correct result. We also accept only reports with our official binaries.
[15 Jun 2023 12:45]
Erik Frøseth
Allright, here is from your official APT repository: mysql> CREATE SCHEMA bug; Query OK, 1 row affected (0.02 sec) mysql> USE bug; Database changed mysql> CREATE TABLE t0 (c0 INTEGER); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO t0 VALUES (1); Query OK, 1 row affected (0.01 sec) mysql> SELECT (SELECT t0.c0 GROUP BY t0.c0 WITH ROLLUP) FROM t0; +-------------------------------------------+ | (SELECT t0.c0 GROUP BY t0.c0 WITH ROLLUP) | +-------------------------------------------+ | 1 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN FORMAT=tree SELECT (SELECT t0.c0 GROUP BY t0.c0 WITH ROLLUP) FROM t0; +------------------------------------------+ | EXPLAIN | +------------------------------------------+ | -> Table scan on t0 (cost=0.35 rows=1) | +------------------------------------------+ 1 row in set, 3 warnings (0.00 sec) mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.33 | +-----------+ 1 row in set (0.00 sec)
[15 Jun 2023 13:12]
MySQL Verification Team
Hi Mr. Froseth, The error is in the nested query, where it misses FROM clause. Now , that is the true root from the bug. Because, if you add FROM clause, you get the expected error. If you wish we can verify that bug, but it is about missing FROM clause and not about multiple rows.
[15 Jun 2023 13:45]
MySQL Verification Team
HI Mr. Froseth, We concluded that this is a bug. It has two components. First, the missing FROM clause in the nested query and (when that is not fixed) there is no error message for the number of rows, which is more then 1 (one). This is a low severity bug, but still a bug. Verified .........
Description: The following query returns one row, while it should given an error saying "Subquery returns more than one row": CREATE TABLE t0 (c0 INTEGER); INSERT INTO t0 VALUES (1); SELECT (SELECT t0.c0 GROUP BY t0.c0 WITH ROLLUP) FROM t0; What happens here is that the inner query satisfied a set of conditions so that the column is pulled out to the outer query (happens in Item_singlerow_subselect::select_transformer). Without the ROLLUP, this simplification would have been valid. With ROLLUP, this simplification is not valid anymore as the inner query now can return more than one row. How to repeat: CREATE TABLE t0 (c0 INTEGER); INSERT INTO t0 VALUES (1); SELECT (SELECT t0.c0 GROUP BY t0.c0 WITH ROLLUP) FROM t0; The above line should return an error, and not one row as it currently does. Suggested fix: diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index b27476a5311..6fd3c9f9311 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -74,6 +74,7 @@ #include "sql/opt_trace.h" // OPT_TRACE_TRANSFORM #include "sql/opt_trace_context.h" #include "sql/parse_tree_nodes.h" // PT_subquery +#include "sql/parser_yystype.h" #include "sql/query_options.h" #include "sql/query_result.h" #include "sql/sql_class.h" // THD @@ -1136,7 +1137,8 @@ bool Item_singlerow_subselect::select_transformer(THD *thd, if (!unit->is_set_operation() && !select->has_tables() && single_field != nullptr && !single_field->has_aggregation() && !single_field->has_wf() && select->where_cond() == nullptr && - select->having_cond() == nullptr) { + select->having_cond() == nullptr && + select->olap == olap_type::UNSPECIFIED_OLAP_TYPE) { have_to_be_excluded = true; if (thd->lex->is_explain()) { char warn_buff[MYSQL_ERRMSG_SIZE];