bug: ERROR 6 (HY000), The query syntax (WHERE NOT IN ) is not supported

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

mysql> select * from tt where 42 not in (select * from tt where val > 42);
ERROR 6 (HY000): The query includes syntax that is not supported by the storage engine. Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.

Expected behavior

mysql> select * from tt where 42 not in (select * from tt where val > 42);
+------+
| val  |
+------+
|   12 |
+------+
1 row in set (0.00 sec)

How To Reproduce

create table tt(val int) ENGINE=TIANMU;
insert into tt values (12);
select * from tt where 42 not in (select * from tt where val > 42);

Environment

ubuntu@ubuntu:~$ cat /etc/os-release
NAME="Ubuntu"
VERSION="20.04.4 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.4 LTS"
VERSION_ID="20.04"

ubuntu@ubuntu:~$ /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld Ver 5.7.36-StoneDB 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: 5d996f9
Last commit time: Date: Sat Oct 8 14:34:42 2022 +0800
Build time: Date: Fri 14 Oct 2022 12:28:00 PM UTC

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

• Yes, I will!

[lujiashun]

ACK

[lujiashun]

in query_compile.cpp:1048, throw this exception

1045：     if (left_expr_for_subselect)
1046：       if (!ClearSubselectTransformation(*oper_for_subselect, field_for_subselect, conds, having, cond_to_reinsert,
1047：                                         list_to_reinsert, left_expr_for_subselect))
1048:          throw CompilationError();

backtrace

(gdb) bt
#0  0x000056006b9c0f52 in __cxa_throw ()
#1  0x000056006b47835c in Tianmu::core::Query::Compile (this=0x7f0765d59fc0, compiled_query=0x7f0765d59ef0, selects_list=0x7f0288007700, last_distinct=0x0, res_tab=0x7f0765d591e8, ignore_limit=true, left_expr_for_subselect=0x7f028800fd60, oper_for_subselect=0x7f0765d59340, ignore_minmax=false, for_subq_in_where=true) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query_compile.cpp:1048
#2  0x000056006b452c5d in Tianmu::core::Query::Item2CQTerm (this=0x7f0765d59fc0, an_arg=0x7f0288006e10, term=..., tmp_table=..., filter_type=Tianmu::core::CondType::WHERE_COND, negative=true, left_expr_for_subselect=0x7f028800fd60, oper_for_subselect=0x7f0765d59340) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query.cpp:940
#3  0x000056006b454e96 in Tianmu::core::Query::ConditionNumberFromComparison (this=0x7f0765d59fc0, conds=0x7f028800fc78, tmp_table=..., filter_type=Tianmu::core::CondType::WHERE_COND, and_me_filter=0x0, is_or_subtree=false, negative=true) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query.cpp:1236
#4  0x000056006b454b39 in Tianmu::core::Query::ConditionNumberFromComparison (this=0x7f0765d59fc0, conds=0x7f028800f6c0, tmp_table=..., filter_type=Tianmu::core::CondType::WHERE_COND, and_me_filter=0x0, is_or_subtree=false, negative=false) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query.cpp:1210
#5  0x000056006b4577c2 in Tianmu::core::Query::ConditionNumber (this=0x7f0765d59fc0, conds=0x7f028800f6c0, tmp_table=..., filter_type=Tianmu::core::CondType::WHERE_COND, and_me_filter=0x0, is_or_subtree=false) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query.cpp:1560
#6  0x000056006b4582d7 in Tianmu::core::Query::BuildConditions (this=0x7f0765d59fc0, conds=0x7f028800f6c0, cond_id=..., cq=0x7f0765d59ef0, tmp_table=..., filter_type=Tianmu::core::CondType::WHERE_COND, is_zero_result=false, join_type=Tianmu::core::JoinType::JO_INNER) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query.cpp:1607
#7  0x000056006b478834 in Tianmu::core::Query::Compile (this=0x7f0765d59fc0, compiled_query=0x7f0765d59ef0, selects_list=0x7f0288005c20, last_distinct=0x0, res_tab=0x0, ignore_limit=false, left_expr_for_subselect=0x0, oper_for_subselect=0x0, ignore_minmax=false, for_subq_in_where=false) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query_compile.cpp:1087
#8  0x000056006b41f456 in Tianmu::core::Engine::Execute (this=0x56007078c3e0, thd=0x7f0288000e60, lex=0x7f0288003188, result_output=0x7f028800f840, unit_for_union=0x0) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/engine_execute.cpp:377
#9  0x000056006b41eade in Tianmu::core::Engine::HandleSelect (this=0x56007078c3e0, thd=0x7f0288000e60, lex=0x7f0288003188, result=@0x7f0765d5a458: 0x7f028800f840, setup_tables_done_option=0, res=@0x7f0765d5a44c: 32514, optimize_after_tianmu=@0x7f0765d5a454: 1, tianmu_free_join=@0x7f0765d5a450: 1, with_insert=0) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/engine_execute.cpp:238
#10 0x000056006b5199d8 in Tianmu::dbhandler::TIANMU_HandleSelect (thd=0x7f0288000e60, lex=0x7f0288003188, result=@0x7f0765d5a458: 0x7f028800f840, setup_tables_done_option=0, res=@0x7f0765d5a44c: 32514, optimize_after_tianmu=@0x7f0765d5a454: 1, tianmu_free_join=@0x7f0765d5a450: 1, with_insert=0) at /data/codebase/stonedb57/stonedb/storage/tianmu/handler/ha_rcengine.cpp:82
#11 0x000056006aabe668 in execute_sqlcom_select (thd=0x7f0288000e60, all_tables=0x7f0288007160) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:5182
#12 0x000056006aab76f7 in mysql_execute_command (thd=0x7f0288000e60, first_level=true) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:2831
#13 0x000056006aabf6f6 in mysql_parse (thd=0x7f0288000e60, parser_state=0x7f0765d5b5f0) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:5621
#14 0x000056006aab43b3 in dispatch_command (thd=0x7f0288000e60, com_data=0x7f0765d5bea0, command=COM_QUERY) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:1495
#15 0x000056006aab322a in do_command (thd=0x7f0288000e60) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:1034
#16 0x000056006abfb9c8 in handle_connection (arg=0x560073310190) at /data/codebase/stonedb57/stonedb/sql/conn_handler/connection_handler_per_thread.cc:313
#17 0x000056006b3459c7 in pfs_spawn_thread (arg=0x56007308f850) at /data/codebase/stonedb57/stonedb/storage/perfschema/pfs.cc:2197
#18 0x00007f07b234a609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x00007f07b211e133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

[lujiashun]

in query.cpp:1726, return false;

(gdb) call left_ref->type()
$5 = Item::FIELD_ITEM
(gdb) call ((Item_ref *)left_ref)->ref_type()
$6 = Item_ref::REF
(gdb) call ((Item_ref *)left_ref)->real_item()
$7 = (Item *) 0x7f028800faa0
(gdb) p left_expr_for_subselect
$8 = (Item *) 0x7f028800fd60
(gdb) bt 1
#0  Tianmu::core::Query::ClearSubselectTransformation (oper_for_subselect=@0x7f0765d59340: Tianmu::common::Operator::O_IN, field_for_subselect=@0x7f0765d59038: 0x7f028800fe28, conds=@0x7f0765d59028: 0x7f02880103b8, having=@0x7f0765d59030: 0x0, cond_to_reinsert=@0x7f0765d59040: 0x7f02880102a0, list_to_reinsert=@0x7f0765d59048: 0x7f0288010490, left_expr_for_subselect=0x7f028800fd60) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query.cpp:1726
(More stack frames follow...)
(gdb) bt 2
#0  Tianmu::core::Query::ClearSubselectTransformation (oper_for_subselect=@0x7f0765d59340: Tianmu::common::Operator::O_IN, field_for_subselect=@0x7f0765d59038: 0x7f028800fe28, conds=@0x7f0765d59028: 0x7f02880103b8, having=@0x7f0765d59030: 0x0, cond_to_reinsert=@0x7f0765d59040: 0x7f02880102a0, list_to_reinsert=@0x7f0765d59048: 0x7f0288010490, left_expr_for_subselect=0x7f028800fd60) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query.cpp:1726
#1  0x000056006b47832d in Tianmu::core::Query::Compile (this=0x7f0765d59fc0, compiled_query=0x7f0765d59ef0, selects_list=0x7f0288007700, last_distinct=0x0, res_tab=0x7f0765d591e8, ignore_limit=true, left_expr_for_subselect=0x7f028800fd60, oper_for_subselect=0x7f0765d59340, ignore_minmax=false, for_subq_in_where=true) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/query_compile.cpp:1046
(More stack frames follow...)
(gdb) list
1721      Item *left_ref = ((Item_func *)cond_removed)->arguments()[0];
1722      if (dynamic_cast<Item_int_with_ref *>(left_ref) != nullptr)
1723        left_ref = ((Item_int_with_ref *)left_ref)->real_item();
1724      if (left_ref->type() != Item::REF_ITEM || ((Item_ref *)left_ref)->ref_type() != Item_ref::DIRECT_REF ||
1725          ((Item_ref *)left_ref)->real_item() != left_expr_for_subselect)
1726        return false;
1727      // set the operation type
1728      switch (((Item_func *)cond_removed)->functype()) {
1729        case Item_func::EQ_FUNC:
1730          oper_for_subselect = common::Operator::O_IN; /*common::Operator::common::Operator::O_IN;*/

[lujiashun]

mysql> explain select * from tt where 42 not in (select * from tt where val > 42);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                                                                             |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------+
|  1 | PRIMARY     | tt    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                                                                                                              |
|  2 | SUBQUERY    | tt    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where with pushed condition ((`test`.`tt`.`val` > 42) and ((`test`.`tt`.`val` = <cache>(42)) or isnull(`test`.`tt`.`val`))) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)