bug: If using semi join,maybe wrong result. #1559

haitaoguan · 2023-04-13T07:07:17Z

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

select B.e_id, count(A.id)
  from A
 inner join B
    ON B.user_id = A.user_id
 where B.code = 'c8de2be2f1ef4dcb8bd5539aee'
   and A.code = 'c8de2be2f1ef4dcb8bd5539aee'
   and A.user_id in
       (select id
          from C
         where C.code = 'c8de2be2f1ef4dcb8bd5539aee'
           and C.d_flag = '0'
           and C.l_flag = '0')
 group by B.e_id;
+----------------------------------+-----+
| c_0                              | c_3 |
+----------------------------------+-----+
| 3746cc9efe794214813a8295291b4c21 |   0 |
| 6298aa9a086c47f7b7d592b35abb3beb |   0 |
| 3ff056ba8b0a4bddb97aee151b54f52e |   0 |
| 4cb302194ce349fca29a52df74ade390 |   0 |
| 628a93cf59cb474693747f2e4a68c6c6 |   0 |
......

set optimizer_switch='semijoin=off';
+----------------------------------+-------+
| c_0                              | c_3   |
+----------------------------------+-------+
| 3746cc9efe794214813a8295291b4c21 |  8559 |
| 6298aa9a086c47f7b7d592b35abb3beb |  7797 |
| 3ff056ba8b0a4bddb97aee151b54f52e |   457 |
| 4cb302194ce349fca29a52df74ade390 |  4358 |
| 628a93cf59cb474693747f2e4a68c6c6 |  5709 |
......

Expected behavior

No response

How To Reproduce

No response

Environment

./mysqld Ver 5.7.36-StoneDB-v1.0.3 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: 758bce8
Last commit time: Date: Mon Apr 10 19:46:20 2023 +0800
Build time: Date: Tue Apr 11 11:34:37 CST 2023

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

Yes, I will!

The text was updated successfully, but these errors were encountered:

RingsC · 2023-04-24T07:51:11Z

The root cause maybe is not due to turn semijoin on. when it turns semijoin on, and run the query statement and it will lead to service coredump in debug mode. Therefore, if, the server runs in release mode, it's maybe not down, and returns wrong result.


#0  0x0000564b4d61758c in Tianmu::types::TianmuDataType::IsNull (this=0x0) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/types/tianmu_data_types.h:158
#1  0x0000564b4d614f2d in Tianmu::core::Engine::Convert (is_null=@0x7f7abe0f5bd4: 0, value=@0x7f7abe0f5be0: 140153439952744, tianmu_item=..., f_type=MYSQL_TYPE_LONGLONG, unsigned_flag=false) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_convert.cpp:366
#2  0x0000564b4d6207f1 in Tianmu::core::ResultSender::SendRecord (this=0x564b53dd15c0, r=std::vector of length 1, capacity 1 = {...}) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_results.cpp:342
#3  0x0000564b4d62015b in Tianmu::core::ResultSender::Send (this=0x564b53dd15c0, iter=...) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_results.cpp:239
#4  0x0000564b4d93041d in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7f7abe0f6500, gbw=..., limit=@0x7f7abe0f60c0: 358, offset=@0x7f7abe0f64d0: 0, sender=0x564b53dd15c0, limit_less_than_no_groups=false)
    at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/optimizer/aggregation_algorithm.cpp:413
#5  0x0000564b4d92f2b5 in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7f7abe0f6500, just_distinct=false, limit=@0x7f7abe0f64d8: -1, offset=@0x7f7abe0f64d0: 0, sender=0x564b53dd15c0)
    at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/optimizer/aggregation_algorithm.cpp:209

(gdb) p this
$1 = (const Tianmu::types::TianmuDataType * const) 0x0
(gdb) f 1
#1  0x000056031f8e0f2d in Tianmu::core::Engine::Convert (is_null=@0x7f311d42cbd4: 0, value=@0x7f311d42cbe0: 139835411030408, tianmu_item=..., f_type=MYSQL_TYPE_LONGLONG, unsigned_flag=false) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_convert.cpp:366
366	  if (tianmu_item.IsNull())
(gdb) p tianmu_item
$2 = (Tianmu::types::TianmuDataType &) <error reading variable>
(gdb)

tianmu_item is not a right var, and corrupted. and in TianmuDataType, this is 0x0.

in Query::BuildCondsIfPossible, only three types join type supported: JO_INNER, JO_LEFT, JO_RIGHT, JO_FULL .

sql/sql_resolver.cc file converts in statement to semi-join in sql layer, but tianmu only supports three types, That means the semi-join cannot be recognized properly.

The requirement of semi-join:

  /*
    Check if we're in subquery that is a candidate for flattening into a
    semi-join (which is done in flatten_subqueries()). The requirements are:
      0. Semi-join is enabled (cf. hints)
      1. Subquery predicate is an IN/=ANY or EXISTS predicate
      2. Subquery is a single query block (not a UNION, EXCEPT or INTERSECT)
      3. Subquery is not grouped (explicitly or implicitly)
         3x: outer aggregated expression are not accepted
      4. Subquery does not use HAVING
      5. Subquery does not use windowing functions
      6. Subquery predicate is (a) in an ON/WHERE clause,
         and (b) at the AND-top-level of that clause. Note for 6a:
         Semijoin transformations of subqueries in ON cause the
         join nests to no longer be acceptable as a join tree, which
         disturbs the hypergraph optimizer, so we disable them
         for that case (6x). However, we enable them when secondary
         engine optimization is ON because it is easy to reject a
         possible wrong plan when its not supporting nested loop
         joins.
      7. Parent query block accepts semijoins (i.e we are not in a subquery of
      a single table UPDATE/DELETE (TODO: We should handle this at some
      point by switching to multi-table UPDATE/DELETE)
      8. We're not in a confluent table-less subquery, like "SELECT 1".
      9. No execution method was already chosen (by a prepared statement)
      10. Parent query block is not a confluent table-less query block.
      11. Neither parent nor child query block has straight join.
      12. Parent query block does not prohibit semi-join.
      13. LHS of IN predicate is deterministic
      14. The surrounding truth test, and the nullability of expressions,
      are compatible with the conversion.
      15. Antijoins are supported, or it's not an antijoin (it's a semijoin).
      16. OFFSET starts from the first row and LIMIT is not 0.
  */

and if transform failed, then it will converted to derived table. [from MySQL]

    If semijoin failed, try a transformation to a derived table:
    FROM ot WHERE ot.x IN (SELECT y FROM it1, it2)
    =>
    FROM ot LEFT JOIN (SELECT DISTINCT y FROM it1, it2) AS derived
            ON ot.x=derived.y
    WHERE derived.y IS NOT NULL.

    Applicability constraints have numbers which are the same as in the list of
    the previous block. Reasons may be different though.
      1. Subquery predicate is an IN/=ANY or EXISTS predicate
      2. Subquery is a single query block (not a UNION, INTERSECT or EXCEPT);
         this is because a certain secondary engine has no support for setop
         DISTINCT
      3. If this is [NOT] EXISTS, there is no aggregation; see
      transform_table_subquery_to_join_with_derived()
      6. Subquery predicate is
        6a. in WHERE clause (we have not implemented the transformation for the
        ON clause)
        6b. linked to the root of that clause with ANDs or ORs.
      7. Parent query block accepts semijoins (i.e we are not in a subquery of
      a single table UPDATE/DELETE (TODO: We should handle this at some
      point by switching to multi-table UPDATE/DELETE)
      9. No execution method was already chosen (by a prepared statement)
      10. Parent select has tables, as we'll link to them with LEFT JOIN
      12. Parent query block does not prohibit semi-join.
      13. LHS of IN predicate is deterministic
      14. The surrounding truth test, and the nullability of expressions,
      are compatible with the conversion.
      16. The left argument isn't a row (multi-column) subquery; it would lead
      to creating conditions like WHERE (outer_subq) =
      ROW(derived.col1,derived.col2), which would complicate code.
      17. Certain other subquery transformations, incompatible with this one,
      have not been done.

│   1298        JoinType Query::GetJoinTypeAndCheckExpr(uint outer_join, Item *on_expr) {                                                                                                                                                                                        │
│   1299          if (outer_join)                                                                                                                                                                                                                                                │
│   1300            ASSERT(on_expr != 0, "on_expr shouldn't be null when outer_join != 0");                                                                                                                                                                                      │
│   1301                                                                                                                                                                                                                                                                         │
│   1302          JoinType join_type;                                                                                                                                                                                                                                            │
│   1303                                                                                                                                                                                                                                                                         │
│   1304          if ((outer_join & JOIN_TYPE_LEFT) && (outer_join & JOIN_TYPE_RIGHT))                                                                                                                                                                                           │
│   1305            join_type = JoinType::JO_FULL;                                                                                                                                                                                                                               │
│   1306          else if (outer_join & JOIN_TYPE_LEFT)                                                                                                                                                                                                                          │
│   1307            join_type = JoinType::JO_LEFT;                                                                                                                                                                                                                               │
│   1308          else if (outer_join & JOIN_TYPE_RIGHT)                                                                                                                                                                                                                         │
│   1309            join_type = JoinType::JO_RIGHT;                                                                                                                                                                                                                              │
│   1310          else                                                                                                                                                                                                                                                           │
│  >1311            join_type = JoinType::JO_INNER;                                                                                                                                                                                                                              │
│   1312                                                                                                                                                                                                                                                                         │
│   1313          return join_type;                                                                                                                                                                                                                                              │
│   1314        }

Tiamnu generates join clause according to which type we support. From the code, we only support FULL, LEFT, RIGHT, INNER.

RingsC · 2023-04-24T09:26:42Z

The MTR is not sufficient for high quality, In closed PR: https://github.com/stoneatom/stonedb/pull/1322/files. More complex MTRs are requested, but it does not added in merged PR.

such as [for example]: Complex query statement.

RingsC · 2023-04-25T08:12:12Z

   517         QueryRouteTo Query::AddSemiJoinFiled(List<Item> &fields, List<TABLE_LIST> &join, const TabID &tmp_table) {                                                                                                                                                       │
│   518           List_iterator_fast<Item> field_li(fields);                                                                                                                                                                                                                     │
│   519           Item *item;                                                                                                                                                                                                                                                    │
│   520           item = field_li++;                                                                                                                                                                                                                                             │
│   521           while (item) {                                                                                                                                                                                                                                                 │
│   522             WrapStatus ws;                                                                                                                                                                                                                                               │
│   523             common::ColOperation oper;                                                                                                                                                                                                                                   │
│   524             bool distinct;                                                                                                                                                                                                                                               │
│   525             if (QueryRouteTo::kToMySQL == OperationUnmysterify(item, oper, distinct, 0))                                                                                                                                                                                 │
│   526               return QueryRouteTo::kToMySQL;                                                                                                                                                                                                                             │
│   527             if (!IsFieldItem(item)) {                                                                                                                                                                                                                                    │
│   528               item = field_li++;                                                                                                                                                                                                                                         │
│   529               continue;                                                                                                                                                                                                                                                  │
│   530             }                                                                                                                                                                                                                                                            │
│   531             AddColumnForPhysColumn(item, tmp_table, TabID(), oper, distinct, false, item->item_name.ptr());                                                                                                                                                              │
│   532             item = field_li++;                                                                                                                                                                                                                                           │
│   533           }                                                                                                                                                                                                                                                              │
│   534                                                                                                                                                                                                                                                                          │
│   535           TABLE_LIST *join_ptr;                                                                                                                                                                                                                                          │
│   536           List_iterator<TABLE_LIST> li(join);                                                                                                                                                                                                                            │
│   537           std::vector<TABLE_LIST *> reversed;                                                                                                                                                                                                                            │
│   538           while ((join_ptr = li++) != nullptr) {                                                                                                                                                                                                                         │
│  >539             reversed.push_back(join_ptr);                                                                                                                                                                                                                                │
│   540           }                                                                                                                                                                                                                                                              │
│   541           size_t size = reversed.size();                                                                                                                                                                                                                                 │
│   542           for (unsigned int i = 0; i < size; i++) {                                                                                                                                                                                                                      │
│   543             join_ptr = reversed[size - i - 1];

and in query_compile.cpp, the logic of processing semijoin is NOT ok. the semijoin has nothing to do with group clause, but here, it does semi-join optimization according to has_sj_nests and group.

      bool aggr_used = false;
      if (sl->has_sj_nests && group != nullptr) {
        // handle semi-join fields (use on group by)
        if (QueryRouteTo::kToMySQL == AddSemiJoinFiled(*fields, *join_list, tmp_table))
          throw CompilationError();
      } else {
        // handle normal fields
        if (QueryRouteTo::kToMySQL ==
            AddFields(*fields, tmp_table, TabID(), group != nullptr, col_count, ignore_minmax, aggr_used))
          throw CompilationError();
        if (QueryRouteTo::kToMySQL == AddGroupByFields(group, tmp_table, TabID()))
          throw CompilationError();
        bool group_by_clause = group != nullptr || sl->join->select_distinct || aggr_used || sl->has_sj_nests;
        if (QueryRouteTo::kToMySQL == AddOrderByFields(order, tmp_table, TabID(), group_by_clause))
          throw CompilationError();
      }

Taking the sql statement above as an example, it will take A and B as semijoin. but, it's inner join.

From the analysis of above text, we know that stonedb try to convert to semijoin at sql layer, but tianmu engine does not support semijoin now very well.

RingsC · 2023-05-05T09:37:43Z

semijoin enable， miss c_3 column, leading to crash.

T:-1 = TABLE_ALIAS(T:2,"sys_user")
T:-2 = TMP_TABLE(T:-1,T:-3,T:4294967292)
T:-3 = TABLE_ALIAS(T:0,"study_user_course")
T:-4 = TABLE_ALIAS(T:1,"sys_enterprise_user_relation")
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-4,A:2))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"c_0","ALL")
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:5))
A:-2 = T:-2.ADD_COLUMN(VC:-2.1,LIST,"user_id","ALL")
VC:-2.2 = CREATE_VC(T:-2,PHYS_COL(T:-4,A:9))
VC:-2.3 = CREATE_VC(T:-2,EXPR("c8de2be2f1ef4dcb8bd5539aee22c716"))
C:0 = CREATE_CONDS(T:-2,VC:-2.2,=,VC:-2.3,<null>)
VC:-2.4 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:16))
C:0.AND(VC:-2.4,=,VC:-2.3,<null>)
VC:-2.5 = CREATE_VC(T:-2,EXPR("1"))
VC:-2.6 = CREATE_VC(T:-2,EXPR("0"))
C:0.AND(VC:-2.5,<>,VC:-2.6,<null>)
VC:-2.7 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:24))
C:0.AND(VC:-2.7,=,VC:-2.3,<null>)
VC:-2.8 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:19))
VC:-2.9 = CREATE_VC(T:-2,EXPR("0"))
C:0.AND(VC:-2.8,=,VC:-2.9,<null>)
VC:-2.10 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:18))
C:0.AND(VC:-2.10,=,VC:-2.9,<null>)
VC:-2.11 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
C:0.AND(VC:-2.1,=,VC:-2.11,<null>)
VC:-2.12 = CREATE_VC(T:-2,PHYS_COL(T:-4,A:1))
C:0.AND(VC:-2.12,=,VC:-2.1,<null>)
T:-2.ADD_CONDS(C:0,HAVING)
T:-2.APPLY_CONDS()
T:-2.MODE(DISTINCT,0,0)
T:-5 = TMP_TABLE(T:4294967294)
VC:-5.0 = CREATE_VC(T:-5,PHYS_COL(T:-2,A:-1))
A:-1 = T:-5.ADD_COLUMN(VC:-5.0,LIST,"c_0","ALL")
VC:-5.1 = CREATE_VC(T:-5,PHYS_COL(T:-2,A:-1))
A:-2 = T:-5.ADD_COLUMN(VC:-5.1,GROUP_BY,"null","ALL")
RESULT(T:-5)

and its query plan is as following:

+----+-------------+-------------------+------------+------+---------------------+------+---------+------+---------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table             | partitions | type | possible_keys       | key  | key_len | ref  | rows    | filtered | Extra                                                                                                                                                                                                                                                                      |
+----+-------------+-------------------+------------+------+---------------------+------+---------+------+---------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | t_user            | NULL       | ALL  | PRIMARY             | NULL | NULL    | NULL |   10105 |     0.10 | Using where with pushed condition ((`db`.`t_user`.`tenant_code` = 'c8de2be2f1ef4dcb8bd5539aee22c716') and (`db`.`t_user`.`del_flag` = '0') and (`db`.`t_user`.`lock_flag` = '0'))(t0) Pckrows: 1, susp. 1 (0 empty 0 full). Conditions: 3; Using temporary; Using filesort |
|  1 | SIMPLE      | t_user_course     | NULL       | ALL  | uidx_uct,idx_userid | NULL | NULL    | NULL | 1609819 |     0.00 | Using where with pushed condition (`db`.`t_user_course`.`tenant_code` = 'c8de2be2f1ef4dcb8bd5539aee22c716')(t0) Pckrows: 1, susp. 1 (0 empty 0 full). Conditions: 3; Using join buffer (Block Nested Loop)                                                                 |
|  1 | SIMPLE      | t_enterprise_user | NULL       | ALL  | idx_u_e             | NULL | NULL    | NULL |    7282 |     0.00 | Using where with pushed condition (`db`.`t_enterprise_user`.`tenant_code` = 'c8de2be2f1ef4dcb8bd5539aee22c716')(t0) Pckrows: 1, susp. 1 (0 empty 0 full). Conditions: 3; Using join buffer (Block Nested Loop)                                                             |
+----+-------------+-------------------+------------+------+---------------------+------+---------+------+---------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

semi-join disabled: in this way, in statement is processed as subquery. As the codem, VC:-2.6 = CREATE_VC(T:-2,SUBQUERY(T:-5)), shows.

T:-1 = TABLE_ALIAS(T:0,"study_user_course")
T:-2 = TMP_TABLE(T:-1,T:4294967293)
T:-3 = TABLE_ALIAS(T:1,"sys_enterprise_user_relation")
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:2))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,GROUP_BY,"c_0","ALL")
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
A:-2 = T:-2.ADD_COLUMN(VC:-2.1,COUNT,"c_3","ALL")
VC:-2.2 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:9))
VC:-2.3 = CREATE_VC(T:-2,EXPR("c8de2be2f1ef4dcb8bd5539aee22c716"))
C:0 = CREATE_CONDS(T:-2,VC:-2.2,=,VC:-2.3,<null>)
VC:-2.4 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:16))
C:0.AND(VC:-2.4,=,VC:-2.3,<null>)
VC:-2.5 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:5))
T:-4 = TABLE_ALIAS(T:2,"sys_user")
T:-5 = TMP_TABLE(T:4294967292)
VC:-5.0 = CREATE_VC(T:-5,PHYS_COL(T:-4,A:0))
A:-1 = T:-5.ADD_COLUMN(VC:-5.0,LIST,"id","ALL")
VC:-5.1 = CREATE_VC(T:-5,PHYS_COL(T:-4,A:24))
VC:-5.2 = CREATE_VC(T:-5,EXPR("c8de2be2f1ef4dcb8bd5539aee22c716"))
C:1 = CREATE_CONDS(T:-5,VC:-5.1,=,VC:-5.2,<null>)
VC:-5.3 = CREATE_VC(T:-5,PHYS_COL(T:-4,A:19))
VC:-5.4 = CREATE_VC(T:-5,EXPR("0"))
C:1.AND(VC:-5.3,=,VC:-5.4,<null>)
VC:-5.5 = CREATE_VC(T:-5,PHYS_COL(T:-4,A:18))
C:1.AND(VC:-5.5,=,VC:-5.4,<null>)
T:-5.ADD_CONDS(C:1,HAVING)
T:-5.APPLY_CONDS()
VC:-2.6 = CREATE_VC(T:-2,SUBQUERY(T:-5))
C:0.AND(VC:-2.5,IN,VC:-2.6,<null>)
VC:-2.7 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:1))
C:0.AND(VC:-2.7,=,VC:-2.5,<null>)
T:-2.ADD_CONDS(C:0,HAVING)
T:-2.APPLY_CONDS()
RESULT(T:-2)

and its query plan is listed below:

+----+-------------+-------------------+------------+------+---------------------+------+---------+------+---------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table             | partitions | type | possible_keys       | key  | key_len | ref  | rows    | filtered | Extra                                                                                                                                                                                                                                                                      |
+----+-------------+-------------------+------------+------+---------------------+------+---------+------+---------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | t_user            | NULL       | ALL  | PRIMARY             | NULL | NULL    | NULL |   10105 |     0.10 | Using where with pushed condition ((`db`.`t_user`.`tenant_code` = 'c8de2be2f1ef4dcb8bd5539aee22c716') and (`db`.`t_user`.`del_flag` = '0') and (`db`.`t_user`.`lock_flag` = '0'))(t0) Pckrows: 1, susp. 1 (0 empty 0 full). Conditions: 3; Using temporary; Using filesort |
|  1 | SIMPLE      | t_user_course     | NULL       | ALL  | uidx_uct,idx_userid | NULL | NULL    | NULL | 1609819 |     0.00 | Using where with pushed condition (`db`.`t_user_course`.`tenant_code` = 'c8de2be2f1ef4dcb8bd5539aee22c716')(t0) Pckrows: 1, susp. 1 (0 empty 0 full). Conditions: 3; Using join buffer (Block Nested Loop)                                                                 |
|  1 | SIMPLE      | t_enterprise_user | NULL       | ALL  | idx_u_e             | NULL | NULL    | NULL |    7282 |     0.00 | Using where with pushed condition (`db`.`t_enterprise_user`.`tenant_code` = 'c8de2be2f1ef4dcb8bd5539aee22c716')(t0) Pckrows: 1, susp. 1 (0 empty 0 full). Conditions: 3; Using join buffer (Block Nested Loop)                                                             |
+----+-------------+-------------------+------------+------+---------------------+------+---------+------+---------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

RingsC · 2023-05-06T12:47:01Z

void ResultSender::SendRecord(const std::vector<std::unique_ptr<types::TianmuDataType>> &r) {
  Item *item;
  Field *f;
  Item_field *ifield;
  Item_ref *iref;
  Item_sum *is;
  types::ItemSumInTianmuBase *isum_int_rcbase;
  types::ItemSumSumTianmuBase *isum_sum_rcbase;
  Item_sum::Sumfunctype sum_type;

  uint col_id = 0;
  int64_t value;
  core::Engine *eng = reinterpret_cast<core::Engine *>(tianmu_hton->data);
  assert(eng);

  List_iterator_fast<Item> li(fields);
  li.rewind();

  while ((item = li++)) {
    int is_null = 0;
    types::TianmuDataType &tianmu_dt(*r[col_id]);

The code above has implicit bug, may lead to wrong error. the vector of data type is not same as fields. Here, we found that
the fields are larger than vecotor of data type, for example, fileds has 2 fileds, but only has ONLY one data type element in vector of r. Hence, this line, types::TianmuDataType &tianmu_dt(*r[col_id]); , can not get the data type from vector of r. Therefore, it can not be converted to mysql data type.

(gdb) n
293	    types::TianmuDataType &tianmu_dt(*r[col_id]);
(gdb) 
294	    switch (item->type()) {
(gdb) p tianmu_dt
$22 = (Tianmu::types::TianmuDataType &) <error reading variable>
(gdb) p col_id
$23 = 1
(gdb) p r
$24 = std::vector of length 1, capacity 1 = {std::unique_ptr<Tianmu::types::TianmuDataType> = {get() = 0x7f7e09c2dd70}}
(gdb) where

This maybe introduced by semi-join=on option. we disable semi-join option, it works well. But, when this option is enable, server crashes.

And when executing the statement, the displable two attrs, the first is right one, but the second is wrong.

(gdb) p *this->attrs[0]
$49 = (Tianmu::core::TempTable::Attr) {
  <Tianmu::core::PhysicalColumn> = {
    <Tianmu::core::Column> = {
      ct = {
        type = Tianmu::common::ColumnType::VARCHAR,
        unsigned_flag_ = false,
        precision = 256,
        scale = 0,
        internal_size = 256,
        display_size = 64,
        collation = {
          collation = 0x556ee604d5c0 <my_charset_utf8mb4_general_ci>,
          derivation = DERIVATION_IMPLICIT,
          repertoire = 3
        },
        fmt = Tianmu::common::PackFmt::DEFAULT,
        flag = std::bitset
      }
    }, 
    members of Tianmu::core::PhysicalColumn:
    _vptr.PhysicalColumn = 0x556ee5f76168 <vtable for Tianmu::core::TempTable::Attr+16>,
    is_unique = false,
    is_unique_updated = false
  }, 
  members of Tianmu::core::TempTable::Attr:
  si = {
    separator = "",
    order = st_order::ORDER_NOT_RELEVANT
  },
  buffer = 0x0,
  no_obj = 0,
  no_power = 16,
  no_materialized = 0,
  page_size = 1369568,
  alias = 0x7f68c01a5580 "c_0",
  mode = Tianmu::common::ColOperation::LISTING,
  distinct = false,
  term = {
    type = Tianmu::common::ColumnType::UNK,
    vc = 0x7f68c0999f10,
    cond_value = std::vector of length 0, capacity 0,
    cond_numvalue = std::shared_ptr<Tianmu::utils::Hash64> (empty) = {
      get() = 0x0
    },

the second attrs:

(gdb) p *this->attrs[1]
$50 = (Tianmu::core::TempTable::Attr) {
  <Tianmu::core::PhysicalColumn> = {
    <Tianmu::core::Column> = {
      ct = {
        type = Tianmu::common::ColumnType::VARCHAR,
        unsigned_flag_ = false,
        precision = 128,
        scale = 0,
        internal_size = 128,
        display_size = 32,
        collation = {
          collation = 0x556ee604d5c0 <my_charset_utf8mb4_general_ci>,
          derivation = DERIVATION_IMPLICIT,
          repertoire = 3
        },
        fmt = Tianmu::common::PackFmt::DEFAULT,
        flag = std::bitset = {
          [0] = 1
        }
      }
    }, 
    members of Tianmu::core::PhysicalColumn:
    _vptr.PhysicalColumn = 0x556ee5f76168 <vtable for Tianmu::core::TempTable::Attr+16>,
    is_unique = false,
    is_unique_updated = false
  }, 
  members of Tianmu::core::TempTable::Attr:
  si = {
    separator = "",
    order = st_order::ORDER_NOT_RELEVANT
  },
  buffer = 0x0,
  no_obj = 0,
  no_power = 16,
  no_materialized = 0,
  page_size = 1369568,
  alias = 0x7f68c01a55c0 "user_id",
  mode = Tianmu::common::ColOperation::LISTING,
  distinct = false,
  term = {
    type = Tianmu::common::ColumnType::UNK,
    vc = 0x7f68c07b7f10,
    cond_value = std::vector of length 0, capacity 0,
    cond_numvalue = std::shared_ptr<Tianmu::utils::Hash64> (empty) = {
      get() = 0x0
    },
    vc_id = 1,

The first one ins c_0 and the second one is user_id. Obviously, the second one is wrong.

RingsC · 2023-05-08T08:18:57Z

In fact, is in aggregation, we add an assertion here, and failed. It seems that aggregation_algorithm.xxx has some bugs not founded now.

#1  0x00007f6be527d859 in __GI_abort () at abort.c:79
#2  0x00007f6be527d729 in __assert_fail_base (fmt=0x7f6be5413588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x556ee54f0a26 "col_id < r.size()", 
    file=0x556ee54f09d8 "/home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_results.cpp", line=294, function=<optimized out>) at assert.c:92
#3  0x00007f6be528efd6 in __GI___assert_fail (assertion=0x556ee54f0a26 "col_id < r.size()", file=0x556ee54f09d8 "/home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_results.cpp", line=294, 
    function=0x556ee54f0958 "virtual void Tianmu::core::ResultSender::SendRecord(const std::vector<std::unique_ptr<Tianmu::types::TianmuDataType> >&)") at assert.c:101
    function=0x556ee54f0958 "virtual void Tianmu::core::ResultSender::SendRecord(const std::vector<std::unique_ptr<Tianmu::types::TianmuDataType> >&)") at assert.c:101
#4  0x0000556ee4935738 in Tianmu::core::ResultSender::SendRecord (this=0x556eeacbf8e0, r=std::vector of length 1, capacity 1 = {...})
    at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_results.cpp:294
#5  0x0000556ee4935435 in Tianmu::core::ResultSender::Send (this=0x556eeacbf8e0, iter=...) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/engine_results.cpp:239
#6  0x0000556ee4c458eb in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7f6b99b374e0, gbw=..., limit=@0x7f6b99b370a0: 358, offset=@0x7f6b99b374b0: 0, sender=0x556eeacbf8e0, 
    limit_less_than_no_groups=false) at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/optimizer/aggregation_algorithm.cpp:413
#7  0x0000556ee4c44783 in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7f6b99b374e0, just_distinct=false, limit=@0x7f6b99b374b8: -1, offset=@0x7f6b99b374b0: 0, sender=0x556eeacbf8e0)
    at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/optimizer/aggregation_algorithm.cpp:209
#8  0x0000556ee498a0ea in Tianmu::core::TempTable::Materialize (this=0x7f68c0857bd0, in_subq=false, sender=0x556eeacbf8e0, lazy=false)
    at /home/xxx/workshop/stonedb-ver-1/storage/tianmu/core/temp_table.cpp:2101
#9  0x0000556ee492e913 in Tianmu::core::Engine::Execute (this=0x556ee9990a80, thd=0x7f68c0003060, lex=0x7f68c0005388, result_output=0x7f68c0513918, unit_for_union=0x0)

adofsauron · 2023-06-13T13:57:29Z

通过屏蔽主键和索引屏蔽掉semi join, 保证查询结果正确, 查询出错已解决，如果此后需要开发semi join，重新提交feature，此处处理与当前版本的处理保持一致 https://stoneatom.yuque.com/staff-ft8n1u/lsztbl/rxlhws22n0f1otxn/edit#AqyB

haitaoguan added A-bug Something isn't working prio: high High priority labels Apr 13, 2023

haitaoguan changed the title ~~bug: If using semi join in the Centos environment, may be wrong result.~~ bug: If using semi join,maybe wrong result. Apr 23, 2023

RingsC self-assigned this Apr 24, 2023

RingsC added this to StoneDB for MySQL 5.7 May 5, 2023

hustjieke moved this to Todo in StoneDB for MySQL 5.7 May 29, 2023

hustjieke added this to the StoneDB_5.7_v1.0.4 milestone May 29, 2023

adofsauron closed this as completed Jun 13, 2023

github-project-automation bot moved this from Todo to Done in StoneDB for MySQL 5.7 Jun 13, 2023

haitaoguan reopened this Jun 14, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

bug: If using semi join,maybe wrong result. #1559

bug: If using semi join,maybe wrong result. #1559

haitaoguan commented Apr 13, 2023 •

edited

Loading

RingsC commented Apr 24, 2023 •

edited

Loading

RingsC commented Apr 24, 2023 •

edited by haitaoguan

Loading

RingsC commented Apr 25, 2023 •

edited

Loading

RingsC commented May 5, 2023 •

edited

Loading

RingsC commented May 6, 2023 •

edited

Loading

RingsC commented May 8, 2023 •

edited

Loading

adofsauron commented Jun 13, 2023

bug: If using semi join,maybe wrong result. #1559

bug: If using semi join,maybe wrong result. #1559

Comments

haitaoguan commented Apr 13, 2023 • edited Loading

Have you read the Contributing Guidelines on issues?

Please confirm if bug report does NOT exists already ?

Describe the problem

Expected behavior

How To Reproduce

Environment

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

RingsC commented Apr 24, 2023 • edited Loading

RingsC commented Apr 24, 2023 • edited by haitaoguan Loading

RingsC commented Apr 25, 2023 • edited Loading

RingsC commented May 5, 2023 • edited Loading

RingsC commented May 6, 2023 • edited Loading

RingsC commented May 8, 2023 • edited Loading

adofsauron commented Jun 13, 2023

haitaoguan commented Apr 13, 2023 •

edited

Loading

RingsC commented Apr 24, 2023 •

edited

Loading

RingsC commented Apr 24, 2023 •

edited by haitaoguan

Loading

RingsC commented Apr 25, 2023 •

edited

Loading

RingsC commented May 5, 2023 •

edited

Loading

RingsC commented May 6, 2023 •

edited

Loading

RingsC commented May 8, 2023 •

edited

Loading