Details
-
Bug
-
Status: In Review (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
-
None
-
None
Description
Test case:
CREATE TABLE t1(a INT) ENGINE=MYISAM;
|
INSERT INTO t1(a) VALUES (1);
|
|
|
SELECT a FROM t1 WHERE (SELECT a,a) IN (SELECT a,a);
|
SELECT a FROM t1 WHERE (SELECT a,a) IN (SELECT 1,1);
|
SELECT a FROM t1 WHERE (SELECT a,a UNION SELECT 1,a FROM t1 ) IN (SELECT 1,1);
|
SELECT a FROM t1 WHERE (SELECT a,a UNION SELECT 1,a FROM t1 ) IN (SELECT a,a);
|
Note that these queries do not fail if the table engine is InnoDB. In order these query to fail with a debug assertion the optimizer should apply single row substitution which is done for MyISAM tables, but not for InnoDB tables. For InnoDB tables we can achieve the same failure using single row substitution for primary keys.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- In Review
-
Activity
MDEV-22375 seems to be the same problem
mysqld: /10.4/src/sql/item_subselect.cc:1883: virtual bool Item_in_subselect::val_bool(): Assertion `(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference' failed.
|
231212 15:17:22 [ERROR] mysqld got signal 6 ;
|
|
|
Server version: 10.4.33-MariaDB-debug-log source revision: 9f5078a1d79031c4a781d378af18df9c8c9d2321
|
|
|
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f1c7b10ffd6]
|
sql/item_subselect.cc:1885(Item_in_subselect::val_bool())[0x55fb10f13f10]
|
sql/item.h:1562(Item::val_bool_result())[0x55fb10246962]
|
sql/item_cmpfunc.cc:1673(Item_in_optimizer::val_int())[0x55fb10daa74e]
|
sql/sql_select.cc:4520(JOIN::exec_inner())[0x55fb105e509a]
|
sql/sql_select.cc:4408(JOIN::exec())[0x55fb105e37a6]
|
sql/sql_select.cc:4848(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55fb105e7982]
|
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55fb105b81f8]
|
sql/sql_parse.cc:6523(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55fb1051f1fd]
|
sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x55fb1050c81d]
|
sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55fb10528779]
|
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55fb104fe925]
|
sql/sql_parse.cc:1378(do_command(THD*))[0x55fb104fb450]
|
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55fb109104c6]
|
sql/sql_connect.cc:1324(handle_one_connection)[0x55fb1090fd6a]
|
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55fb115ade4c]
|
nptl/pthread_create.c:478(start_thread)[0x7f1c7b62a609]
|
|
|
Query (0x62b0000a1290): SELECT a FROM t1 WHERE (SELECT a,a) IN (SELECT a,a)
|
Examining the statement
SELECT a FROM t1 WHERE (SELECT a,a) IN (SELECT a,a);
|
The debug assertion triggers at Item_in_subselect::val_bool() due to condition engine->is_executed() (and the engine is subselect_single_select_engine is this case). So, the engine tries to execute once again despite having executed previously, and this triggers the assertion.
The first execution is made at the line marked with (
) at the optimization stage:
|
make_join_select() |
if (const_cond)
|
{
|
...
|
if (const_cond->is_expensive())
|
{
|
...
|
}
|
else
|
{
|
bool const_cond_result;
|
{
|
...
|
(*) const_cond_result= const_cond->val_int() != 0;
|
}
|
if (!const_cond_result)
|
{
|
...
|
join->exec_const_cond= NULL;
|
DBUG_RETURN(1);
|
}
|
}
|
(**) join->exec_const_cond= const_cond;
|
}
|
Despite having been executed, const_cond is saved to join->exec_const_cond for later evaluation during the execution phase (line marked with (**)).
/*
|
Constant codition computed during optimization, but evaluated during
|
join execution. Typically expensive conditions that should not be
|
evaluated at optimization time.
|
*/
|
Item *exec_const_cond;
|
The second execution is performed at JOIN::exec_inner() at the line marked with (***) and triggers the assertion:
|
JOIN::exec_inner() |
/*
|
Evaluate expensive constant conditions that were not evaluated during
|
optimization. Do not evaluate them for EXPLAIN statements as these
|
condtions may be arbitrarily costly, and because the optimize phase
|
might not have produced a complete executable plan for EXPLAINs.
|
*/
|
if (!zero_result_cause &&
|
exec_const_cond && !(select_options & SELECT_DESCRIBE) &&
|
(***) !exec_const_cond->val_int())
|
zero_result_cause= "Impossible WHERE noticed after reading const tables";
|
oleg.smirnov: if we want to cache the result of a subquery we use the function make_const() to set the flag forced_const to true. If this flag is set to true bring_value() just returns without doing anything. With your suggested patch forced_const is not set to true for SELECT(a,1) at the first call of bring_value().
The idea of cached expression is that they are cached only once and then they can be evaluated with val_* methods many times. The subquery that you cache can be just a part of a comparison expression, like:
<constant subquery> <cmp op> <non-constant expression>
|
.
You can't execute this expression without calling val_* method for the left part.
igor, I agree. See my next commit to the same branch:
commit 92090fc11ba7cedab75fdf7a0970934341e61257 (HEAD -> bb-10.5-MDEV-32866, origin/bb-10.5-MDEV-32866)
|
Author: Oleg Smirnov <olernov@gmail.com>
|
Date: Tue Dec 17 13:47:22 2024 +0700
|
|
|
MDEV-35654 Re-evaluation of constant subquery triggers debug assertion
|
|
Constant subqueries, once evaluated during optimization, should not
|
be re-evaluated neither during optimization, nor during execution.
|
Nevertheless, at some scenarios re-evaluation was
|
possible, since previously evaluated constant IN-predicates were
|
not properly marked. This commit implements proper marking of subqueries
|
within constant conditions, preventing their re-evaluation
|
This issue was discovered while working on
MDEV-29070.