[MDEV-22700] Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins Created: 2020-05-25  Updated: 2024-01-25

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-24374 Crash with order by expression contai... Closed
duplicates MDEV-24376 Server crashes after UPDATE with ORDE... Closed
Relates
relates to MDEV-22702 Assertion `!field->is_null()' failed ... Stalled

 Description   

Reproducible on 10.2-10.5, with MyIsam, not with InnoDB.
(test uses window functions, so applicable only from 10.2)

create table t1 ( a2 time not null, a1 varchar(1) not null) engine=myisam;
 
create table t2 ( i1 int not null, i2 int not null) engine=myisam;
 
insert into t2 values (0,0);
 
 
 
select 1 from t2 where (i1, i2) in
 
  (select count((a1 div '1')), bit_or(a2) over () from t1);

10.2 5530a93f47324b847c799d00a

 
#4  0x00007f725efa5428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
 
#5  0x00007f725efa702a in __GI_abort () at abort.c:89
 
#6  0x00007f725ef9dbd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x558fd86886e0 "subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE", file=file@entry=0x558fd8687f70 "/10.2/sql/opt_subselect.cc", line=line@entry=5602, function=function@entry=0x558fd86891a0 <setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**)::__PRETTY_FUNCTION__> "bool setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**)") at assert.c:92
 
#7  0x00007f725ef9dc82 in __GI___assert_fail (assertion=0x558fd86886e0 "subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE", file=0x558fd8687f70 "/10.2/sql/opt_subselect.cc", line=5602, function=0x558fd86891a0 <setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**)::__PRETTY_FUNCTION__> "bool setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**)") at assert.c:101
 
#8  0x0000558fd7d66428 in setup_jtbm_semi_joins (join=0x7f7200014f00, join_list=0x7f7200004fc8, join_where=0x7f7200015308) at /10.2/sql/opt_subselect.cc:5601
 
#9  0x0000558fd7bf84d6 in JOIN::optimize_inner (this=0x7f7200014f00) at /10.2/sql/sql_select.cc:1346
 
#10 0x0000558fd7bf77fd in JOIN::optimize (this=0x7f7200014f00) at /10.2/sql/sql_select.cc:1113
 
#11 0x0000558fd7c00d70 in mysql_select (thd=0x7f7200000af0, tables=0x7f7200012658, wild_num=0, fields=..., conds=0x7f7200014af8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f7200014ee0, unit=0x7f72000046e8, select_lex=0x7f7200004e28) at /10.2/sql/sql_select.cc:3819
 
#12 0x0000558fd7bf4faa in handle_select (thd=0x7f7200000af0, lex=0x7f7200004628, result=0x7f7200014ee0, setup_tables_done_option=0) at /10.2/sql/sql_select.cc:373
 
#13 0x0000558fd7bc0805 in execute_sqlcom_select (thd=0x7f7200000af0, all_tables=0x7f7200012658) at /10.2/sql/sql_parse.cc:6226
 
#14 0x0000558fd7bb7090 in mysql_execute_command (thd=0x7f7200000af0) at /10.2/sql/sql_parse.cc:3532
 
#15 0x0000558fd7bc4552 in mysql_parse (thd=0x7f7200000af0, rawbuf=0x7f7200012448 "select 1 from t2 where (i1, i2) in\n(select count((a1 div '1')), bit_or(a2) over () from t1)", length=91, parser_state=0x7f7258083490, is_com_multi=false, is_next_command=false) at /10.2/sql/sql_parse.cc:7741
 
#16 0x0000558fd7bb27fb in dispatch_command (command=COM_QUERY, thd=0x7f7200000af0, packet=0x7f7200095551 "select 1 from t2 where (i1, i2) in\n(select count((a1 div '1')), bit_or(a2) over () from t1)", packet_length=91, is_com_multi=false, is_next_command=false) at /10.2/sql/sql_parse.cc:1832
 
#17 0x0000558fd7bb1236 in do_command (thd=0x7f7200000af0) at /10.2/sql/sql_parse.cc:1386
 
#18 0x0000558fd7d071b6 in do_handle_one_connection (connect=0x558fdbd027a0) at /10.2/sql/sql_connect.cc:1336
 
#19 0x0000558fd7d06f21 in handle_one_connection (arg=0x558fdbd027a0) at /10.2/sql/sql_connect.cc:1241
 
#20 0x0000558fd8518fc0 in pfs_spawn_thread (arg=0x558fdbd0d590) at /10.2/storage/perfschema/pfs.cc:1869
 
#21 0x00007f725fbe26ba in start_thread (arg=0x7f7258084700) at pthread_create.c:333
 
#22 0x00007f725f07741d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

fails also on non-debug versions:

Version: '10.2.33-MariaDB-log' 
200525 21:12:32 [ERROR] mysqld got signal 11 ;
 
Server version: 10.2.33-MariaDB-log
 
 
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f1998e34390]
 
sql/opt_subselect.cc:5609(setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, Item**))[0x55962efcf092]
 
sql/sql_select.cc:1346(JOIN::optimize_inner())[0x55962ef1c367]
 
sql/sql_select.cc:1114(JOIN::optimize())[0x55962ef1f9aa]
 
sql/sql_select.cc:3819(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*))[0x55962ef20432]
 
sql/sql_select.cc:373(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55962ef2059e]
 
sql/sql_parse.cc:6228(execute_sqlcom_select(THD*, TABLE_LIST*) [clone .constprop.199])[0x55962ee126c1]
 
sql/sql_parse.cc:3532(mysql_execute_command(THD*))[0x55962eed3f25]
 
sql/sql_parse.cc:7741(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55962eed61cd]
 
sql/sql_parse.cc:1857(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55962eed79ec]
 
sql/sql_parse.cc:1388(do_command(THD*))[0x55962eed95f5]
 
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x55962ef9a2b4]
 
sql/sql_connect.cc:1243(handle_one_connection)[0x55962ef9a414]
 
perfschema/pfs.cc:1872(pfs_spawn_thread)[0x55962f4c7304]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f1998e2a6ba]
 
x86_64/clone.S:111(clone)[0x7f19982bf41d]
 
 
 
Query (0x7f198000f0a0): select 1 from t2 where (i1, i2) in (select count((a1 div '1')), bit_or(a2) over () from t1)



 Comments   
Comment by Varun Gupta (Inactive) [ 2020-07-01 ]

This looks another problem with a degenerate join, here the subquery is degenerate one. I think IN/ANY subqueries with window function should just throw an error that these are not implemented. This is because IN-EXIST transform cannot work with window functions (window functions cannot be pushed into HAVING or WHERE clause)

Comment by Varun Gupta (Inactive) [ 2020-07-01 ]

When i run this query in MYSQL then we get:

mysql> select 1 from t2 where (i1, i2) in   (select count((a1 div '1')), bit_or(a2) over () from t1);
 
ERROR 1235 (42000): This version of MySQL doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions'

Comment by Sergei Petrunia [ 2020-07-02 ]

Agree. Currently, the only option we have is to disallow window functions inside IN/ANY subqueries.

Comment by Alice Sherepa [ 2020-11-25 ]

adding just to make it searchable

mysqld: /10.5/sql/opt_subselect.cc:6093: bool execute_degenerate_jtbm_semi_join(THD*, TABLE_LIST*, Item_in_subselect*, List<Item>&): Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed.
 
201125 14:39:50 [ERROR] mysqld got signal 6 ;
 
 
 
sql/opt_subselect.cc:6095(execute_degenerate_jtbm_semi_join(THD*, TABLE_LIST*, Item_in_subselect*, List<Item>&))[0x563f17295b37]
 
sql/opt_subselect.cc:6289(setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, List<Item>&))[0x563f17296f08]
 
sql/sql_select.cc:2044(JOIN::optimize_inner())[0x563f16e0c85d]
 
sql/sql_select.cc:1627(JOIN::optimize())[0x563f16e07e36]
 
sql/sql_select.cc:4641(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x563f16e280e7]
 
sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x563f16dfa060]
 
sql/sql_parse.cc:6266(execute_sqlcom_select(THD*, TABLE_LIST*))[0x563f16d6369f]
 
sql/sql_parse.cc:3968(mysql_execute_command(THD*))[0x563f16d52601]
 
sql/sql_parse.cc:8044(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x563f16d6e950]
 
sql/sql_parse.cc:1875(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x563f16d44cc9]
 
sql/sql_parse.cc:1353(do_command(THD*))[0x563f16d415f2]
 
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x563f17182b1e]
 
sql/sql_connect.cc:1314(handle_one_connection)[0x563f17182482]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x563f17e8d141]
 
nptl/pthread_create.c:478(start_thread)[0x7f26d6df1609]
 
x86_64/clone.S:97(__GI___clone)[0x7f26d69c7293]

Comment by Sergei Petrunia [ 2020-12-22 ]

varun, where is the patch for this?

Comment by Varun Gupta (Inactive) [ 2020-12-22 ]

The patch is in the branch 10.2-wf

Comment by Sergei Petrunia [ 2020-12-22 ]

Looking at the patch

MariaDB [test]> select a from t1;
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
|    2 |
 
+------+


MariaDB [test]> select a from t1 where a in (select row_number() over (order by a) from t1);
 
ERROR 1235 (42000): This version of MariaDB doesn't yet support 'window functions inside IN/ALL/ANY/SOME subquery'

Good.

Let the subquery be a union of the same select;

MariaDB [test]> select a from t1 where a in (select row_number() over (order by a) from t1 union select row_number() over (order by a) from t1);
 
Empty set (0.001 sec)

The result is incorrect. As far as I understand, this should not be supported, either?

Comment by Sergei Petrunia [ 2020-12-22 ]

MySQL is interesting. It supports IN with window functions for the non-correlated subqueries:

mysql>  select a from t1 where a in (select row_number() over (order by a) from t1);
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
|    2 |
 
+------+
 
2 rows in set (0.00 sec)

but doesn't support it for correlated:

mysql>  select a from t1 where a in (select row_number() over (order by a) from t1 INNR where INNR.a<=t1.a);
 
ERROR 1235 (42000): This version of MySQL doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions'

and doesn't support for UNIONs:

mysql>  select a from t1 where a in (select row_number() over (order by a) from t1 INNR union select row_number() over (order by a) from t1 INNR) ;
 
ERROR 1235 (42000): This version of MySQL doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions'

Comment by Sergei Petrunia [ 2020-12-22 ]

Review input:

  • we shouldn't support UNIONs with correlated second branches.
  • Is it difficult to do something similar to what MySQL does - allow queries that can use Materialization (and force them to use it) ?
Comment by Varun Gupta (Inactive) [ 2020-12-30 ]

Updated patch
http://lists.askmonty.org/pipermail/commits/2020-December/014427.html

Comment by Varun Gupta (Inactive) [ 2020-12-31 ]

Tried to work on another approach, only disallowing window functions with IN/ALL/ANY/SOME query if the window function is present in the ORDER BY clause.
http://lists.askmonty.org/pipermail/commits/2020-December/014428.html

Comment by Alice Sherepa [ 2022-01-27 ]

reproducible with InnoDB/MyiSAM,10.2-10.8

--source include/have_innodb.inc
 
CREATE TABLE t1 (pk int, a1 int, PRIMARY KEY (pk)) engine=innodb;
 
INSERT INTO t1 VALUES (151,NULL),(152,NULL),(153,NULL);
 
 
 
CREATE TABLE t2 ( x int, y int) engine=innodb;
 
INSERT INTO t2 VALUES (0,0);
 
 
 
SELECT * FROM t2 WHERE (x,y) IN (SELECT bit_xor('a'),  bit_xor(a1) over () FROM t1 where pk=0);

10.4 e9aac091537a7aaf18543

 
mysqld: /10.4/src/sql/opt_subselect.cc:6076: bool execute_degenerate_jtbm_semi_join(THD*, TABLE_LIST*, Item_in_subselect*, List<Item>&): Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed.
 
220127 14:49:43 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.4.23-MariaDB-debug-log
 
 
 
sql/opt_subselect.cc:6078(execute_degenerate_jtbm_semi_join(THD*, TABLE_LIST*, Item_in_subselect*, List<Item>&))[0x55ebd8c5b8d1]
 
sql/opt_subselect.cc:6270(setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, List<Item>&))[0x55ebd8c5ccf1]
 
sql/sql_select.cc:2110(JOIN::optimize_inner())[0x55ebd882645e]
 
sql/sql_select.cc:1659(JOIN::optimize())[0x55ebd88213b2]
 
sql/sql_select.cc:4741(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*))[0x55ebd88420a9]
 
sql/sql_select.cc:436(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55ebd8813448]
 
sql/sql_parse.cc:6449(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55ebd8781a73]
 
sql/sql_parse.cc:3963(mysql_execute_command(THD*))[0x55ebd876f352]
 
sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55ebd878af61]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55ebd87616e8]
 
sql/sql_parse.cc:1373(do_command(THD*))[0x55ebd875e165]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55ebd8b59163]
 
sql/sql_connect.cc:1317(handle_one_connection)[0x55ebd8b588bc]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55ebd98589c9]
 
nptl/pthread_create.c:478(start_thread)[0x7fc30e436609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fc30e009293]
 
 
 
 
 
Query (0x62b0000a1290): SELECT * FROM t2 WHERE (x,y) IN (SELECT bit_xor('a'),  bit_xor(a1) over () FROM t1 where pk=NULL)

Generated at Thu Feb 08 09:16:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.