bug: debug version, IN subquery causes mysqld to crash

[davidshiz]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

Thread 152 "mysqld" received signal SIGABRT, Aborted.
[Switching to Thread 0x7f494ce6d700 (LWP 81221)]
__GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
50      ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
(gdb) bt
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007f4953715859 in __GI_abort () at abort.c:79
#2  0x00007f4953715729 in __assert_fail_base (fmt=0x7f49538ab588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55fe619ea77a "cleaned < UC_CLEAN",
    file=0x55fe619ea15a "/stonedb/sql/sql_union.cc", line=959, function=<optimized out>) at assert.c:92
#3  0x00007f4953726fd6 in __GI___assert_fail (assertion=0x55fe619ea77a "cleaned < UC_CLEAN", file=0x55fe619ea15a "/stonedb/sql/sql_union.cc", line=959,
    function=0x55fe619ea748 "void st_select_lex_unit::assert_not_fully_clean()") at assert.c:101
#4  0x000055fe607a60a0 in st_select_lex_unit::assert_not_fully_clean (this=0x7f38a48fb400) at /stonedb/sql/sql_union.cc:959
#5  0x000055fe607a60f0 in st_select_lex_unit::assert_not_fully_clean (this=0x7f38a48fa2f8) at /stonedb/sql/sql_union.cc:971
#6  0x000055fe607a60f0 in st_select_lex_unit::assert_not_fully_clean (this=0x7f38a4005ec8) at /stonedb/sql/sql_union.cc:971
#7  0x000055fe606ea06b in mysql_execute_command (thd=0x7f38a4000e10, first_level=true) at /stonedb/sql/sql_parse.cc:5011
#8  0x000055fe606eba40 in mysql_parse (thd=0x7f38a4000e10, parser_state=0x7f494ce6a630) at /stonedb/sql/sql_parse.cc:5642
#9  0x000055fe606e05e1 in dispatch_command (thd=0x7f38a4000e10, com_data=0x7f494ce6aee0, command=COM_QUERY) at /stonedb/sql/sql_parse.cc:1495
#10 0x000055fe606df458 in do_command (thd=0x7f38a4000e10) at /stonedb/sql/sql_parse.cc:1034
#11 0x000055fe60828b1e in handle_connection (arg=0x55fe6d8d2b60) at /stonedb/sql/conn_handler/connection_handler_per_thread.cc:313
#12 0x000055fe60f749cf in pfs_spawn_thread (arg=0x55fe6d9e7510) at /stonedb/storage/perfschema/pfs.cc:2197
#13 0x00007f4953a3e609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#14 0x00007f4953812133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Expected behavior

No response

How To Reproduce

create table t1 (a int);
insert into t1 values (1), (2), (3), (4), (null);
create table t2 (b int, c int);
insert into t2 values (1, 100), (1, 101), (2, 200),
(3, 300), (3, 301), (3, 302), (null, null);
select * from t1 where a in (select b from t2 where b in (select a from t1));

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB-v1.0.3.05db04de4 for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 05db04de4
        Last commit time: Date:   Wed Mar 22 21:09:55 2023 +0800
        Build time: Date: Mon Mar 27 14:06:28 CST 2023

Are you interested in submitting a PR to solve the problem?

• [ ] Yes, I will!

[davidshiz]

NOT IN also has problem SELECT * FROM t1 WHERE a NOT IN ( SELECT b FROM t2 WHERE b NOT IN ( SELECT a FROM t1));

[RingsC]

Perhaps, it's similar with converting exists/in statement to semijoin.