bug:The instance crashed because the subquery includes a SORT clause but it cannot be flattened. #906

haitaoguan · 2022-11-09T01:57:22Z

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

1）create table t_test1

CREATE TABLE t_test1(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(10) NOT NULL,
  last_name VARCHAR(10) NOT NULL,
  sex VARCHAR(5) NOT NULL,
  score INT NOT NULL,
  copy_id INT NOT NULL,
  PRIMARY KEY (`id`)
) engine=tianmu;

2）create PROCEDURE

DELIMITER //
    create PROCEDURE add_user1(in num INT)
    BEGIN
        DECLARE rowid INT DEFAULT 0;
        DECLARE firstname CHAR(1);
        DECLARE name1 CHAR(1);
        DECLARE name2 CHAR(1);
        DECLARE lastname VARCHAR(3) DEFAULT '';
        DECLARE sex CHAR(1);
        DECLARE score CHAR(2);
        WHILE rowid < num DO
        SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1); 
        SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
        SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
        SET sex=FLOOR(0 + (RAND() * 2));
        SET score= FLOOR(40 + (RAND() *60));
        SET rowid = rowid + 1;
        IF ROUND(RAND())=0 THEN 
        SET lastname =name1;
        END IF;
        IF ROUND(RAND())=1 THEN
        SET lastname = CONCAT(name1,name2);
        END IF;
        insert INTO t_test1(first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid);  
        END WHILE;
    END //
DELIMITER ;

3）insert data

call add_user1(1000000);

4）create table t_test2

CREATE TABLE t_test2(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(10) NOT NULL,
  last_name VARCHAR(10) NOT NULL,
  sex VARCHAR(5) NOT NULL,
  score INT NOT NULL,
  copy_id INT NOT NULL,
  PRIMARY KEY (`id`)
) engine=tianmu;

5）create PROCEDURE

DELIMITER //
    create PROCEDURE add_user2(in num INT)
    BEGIN
        DECLARE rowid INT DEFAULT 0;
        DECLARE firstname CHAR(1);
        DECLARE name1 CHAR(1);
        DECLARE name2 CHAR(1);
        DECLARE lastname VARCHAR(3) DEFAULT '';
        DECLARE sex CHAR(1);
        DECLARE score CHAR(2);
        WHILE rowid < num DO
        SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1); 
        SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
        SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
        SET sex=FLOOR(0 + (RAND() * 2));
        SET score= FLOOR(40 + (RAND() *60));
        SET rowid = rowid + 1;
        IF ROUND(RAND())=0 THEN 
        SET lastname =name1;
        END IF;
        IF ROUND(RAND())=1 THEN
        SET lastname = CONCAT(name1,name2);
        END IF;
        insert INTO t_test2(first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid);  
        END WHILE;
    END //
DELIMITER ;

6）insert data

call add_user2(10000000);

###crash SQL1

select *
  from t_test1
 where id in (select count(*) from t_test2 group by last_name);

###crash SQL2

select * from t_test1 where id in (
select id from t_test2 where last_name = '励君'
union
select id from t_test2 where last_name = '励君');

Expected behavior

No response

How To Reproduce

No response

Environment

./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: c91a29e
Last commit time: Date: Mon Oct 24 03:25:06 2022 +0000
Build time: Date: Mon 24 Oct 2022 11:09:00 AM UTC

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

Yes, I will!

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

adofsauron · 2022-11-14T05:46:15Z

ACK

adofsauron · 2022-11-14T08:11:17Z

(gdb) bt
#0  0x0000000002dd94b4 in Tianmu::vcolumn::VirtualColumnBase::IsFullConst (this=0x0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/vc/virtual_column_base.h:456
#1  0x0000000002dd53fa in Tianmu::vcolumn::SubSelectColumn::ContainsImpl (this=0x7f45b090e350, mit=..., v=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/vc/subselect_column.cpp:174
#2  0x0000000002dd63a6 in Tianmu::vcolumn::SubSelectColumn::Contains64Impl (this=0x7f45b090e350, mit=..., val=1)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/vc/subselect_column.cpp:287
#3  0x0000000002d10409 in Tianmu::vcolumn::MultiValColumn::Contains64 (this=0x7f45b090e350, mit=..., val=1)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/vc/multi_value_column.h:143
#4  0x0000000002f88d66 in Tianmu::core::Descriptor::CheckSetCondition (this=0x7f45b0913b30, mit=..., op=Tianmu::common::O_IN)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/descriptor.cpp:1427
#5  0x0000000002f861ad in Tianmu::core::Descriptor::CheckCondition (this=0x7f45b0913b30, mit=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/descriptor.cpp:1121
#6  0x000000000307616c in Tianmu::core::JoinerGeneral::ExecuteInnerJoinLoopSingleThread (this=0x7f45b0913cf0, mit=..., cond=..., new_mind=..., all_dims=..., 
    pack_desc_locked=std::vector<bool> of length 1, capacity 64 = {...}, tuples_in_output=@0x7f69090294c8: 0, limit=-1, count_only=false)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/joiner_general.cpp:244
#7  0x0000000003075486 in Tianmu::core::JoinerGeneral::ExecuteJoinConditions (this=0x7f45b0913cf0, cond=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/joiner_general.cpp:83
#8  0x0000000002fe1eff in Tianmu::core::ParameterizedFilter::UpdateJoinCondition (this=0x7f45b09070d0, cond=..., tips=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/parameterized_filter.cpp:647
#9  0x0000000002fe5cc6 in Tianmu::core::ParameterizedFilter::UpdateMultiIndex (this=0x7f45b09070d0, count_only=false, limit=-1)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/parameterized_filter.cpp:1298
#10 0x0000000002ca54e8 in Tianmu::core::Query::Preexecute (this=0x7f690902a780, qu=..., sender=0x7f45b0901e70, display_now=true)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/query.cpp:793
#11 0x0000000002c76d7f in Tianmu::core::Engine::Execute (this=0x5b89720, thd=0x7f45b0002b60, lex=0x7f45b0004e88, result_output=0x7f45b0011520, unit_for_union=0x0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:477
#12 0x0000000002c75c16 in Tianmu::core::Engine::HandleSelect (this=0x5b89720, thd=0x7f45b0002b60, lex=0x7f45b0004e88, result=@0x7f690902acd8: 0x7f45b0011520, setup_tables_done_option=0, 
    res=@0x7f690902acd4: 0, optimize_after_tianmu=@0x7f690902accc: 1, tianmu_free_join=@0x7f690902acd0: 1, with_insert=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:238
#13 0x0000000002d5f63b in Tianmu::handler::ha_my_tianmu_query (thd=0x7f45b0002b60, lex=0x7f45b0004e88, result_output=@0x7f690902acd8: 0x7f45b0011520, setup_tables_done_option=0, res=@0x7f690902acd4: 0, 
    optimize_after_tianmu=@0x7f690902accc: 1, tianmu_free_join=@0x7f690902acd0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/handler/ha_rcengine.cpp:82
#14 0x000000000239ee34 in execute_sqlcom_select (thd=0x7f45b0002b60, all_tables=0x7f45b000fe70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:4850
#15 0x00000000023981b8 in mysql_execute_command (thd=0x7f45b0002b60, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:2655
#16 0x000000000239fdfd in mysql_parse (thd=0x7f45b0002b60, parser_state=0x7f690902be70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5254
#17 0x0000000002395095 in dispatch_command (thd=0x7f45b0002b60, com_data=0x7f690902c610, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1399
#18 0x0000000002393fc1 in do_command (thd=0x7f45b0002b60) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:976
#19 0x00000000024c6bc1 in handle_connection (arg=0xbe50c80) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/conn_handler/connection_handler_per_thread.cc:313
#20 0x0000000002bac33c in pfs_spawn_thread (arg=0x18077ef0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/perfschema/pfs.cc:2197
#21 0x00007f6913cf2ea5 in start_thread () from /lib64/libpthread.so.0
#22 0x00007f6910f6fb0d in clone () from /lib64/libc.so.6

enum class ColOperation {
  DELAYED,
  LISTING,
  COUNT,
  SUM,
  MIN,
  MAX,
  AVG,
  GROUP_BY,
  STD_POP,
  STD_SAMP,
  VAR_POP,
  VAR_SAMP,
  BIT_AND,
  BIT_OR,
  BIT_XOR,
  GROUP_CONCAT
};

(gdb) p tmp_tab_subq_ptr_->GetAttrP(col_idx_)[0]
$10 = (Tianmu::core::TempTable::Attr) {
  <Tianmu::core::PhysicalColumn> = {
    <Tianmu::core::Column> = {
      ct = {
        type = Tianmu::common::NUM, 
        precision = 18, 
        scale = 0, 
        internal_size = 8, 
        display_size = 19, 
        collation = {
          collation = 0x437a9e0 <my_charset_bin>, 
          derivation = DERIVATION_NONE, 
          repertoire = 3
        }, 
        fmt = Tianmu::common::DEFAULT, 
        flag = std::bitset = {
          [0] = 1
        }
      }
    }, 
    members of Tianmu::core::PhysicalColumn: 
    _vptr.PhysicalColumn = 0x431bdd8 <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 = 0x7f45b09161b0, 
  no_obj = 474, 
  no_power = 16, 
  no_materialized = 474, 
  page_size = 474, 
  alias = 0x7f45b0909280 "count(*)", 
  mode = Tianmu::common::COUNT, 
  distinct = false, 
  term = {
    type = Tianmu::common::UNK, 
    vc = 0x0, 
    cond_value = std::vector of length 0, capacity 0, 
    cond_numvalue = std::shared_ptr (empty) 0x0, 
    vc_id = -2147483648, 
    is_vc_owner = false
---Type <return> to continue, or q <return> to quit---
  }, 
  dim = -2, 
  orig_precision = 18, 
  not_complete = false
}

Analysis of Problems:

The subquery generates a temp table, but the count(*) column of the subquery is not a physical column, so the interface is incompatible

T:-1 = TABLE_ALIAS(T:0,"t_test1")
T:-2 = TMP_TABLE(T:4294967295)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"id","ALL")
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:1))
A:-2 = T:-2.ADD_COLUMN(VC:-2.1,LIST,"first_name","ALL")
VC:-2.2 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:2))
A:-3 = T:-2.ADD_COLUMN(VC:-2.2,LIST,"last_name","ALL")
VC:-2.3 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:3))
A:-4 = T:-2.ADD_COLUMN(VC:-2.3,LIST,"sex","ALL")
VC:-2.4 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:4))
A:-5 = T:-2.ADD_COLUMN(VC:-2.4,LIST,"score","ALL")
VC:-2.5 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:5))
A:-6 = T:-2.ADD_COLUMN(VC:-2.5,LIST,"copy_id","ALL")
 
 
T:-3 = TABLE_ALIAS(T:1,"t_test2")
T:-4 = TMP_TABLE(T:4294967293)
A:-1 = T:-4.ADD_COLUMN(<null>,COUNT,"count(*)","ALL")
VC:-4.0 = CREATE_VC(T:-4,PHYS_COL(T:-3,A:2))
A:-2 = T:-4.ADD_COLUMN(VC:-4.0,GROUP_BY,"null","ALL")
T:-4.APPLY_CONDS()
 
VC:-2.6 = CREATE_VC(T:-2,SUBQUERY(T:-4))
 
 
C:0 = CREATE_CONDS(T:-2,VC:-2.0,IN,VC:-2.6,<null>)
T:-2.ADD_CONDS(C:0,WHERE)
T:-2.APPLY_CONDS()
RESULT(T:-2)

Column properties of the temporary table of the subquery

(gdb) p  tmp_tab_subq_ptr_.get()[0].attrs[0][0].term
$20 = {
  type = Tianmu::common::UNK, 
  vc = 0x0, 
  cond_value = std::vector of length 0, capacity 0, 
  cond_numvalue = std::shared_ptr (empty) 0x0, 
  vc_id = -2147483648, 
  is_vc_owner = false
}

(gdb) p  tmp_tab_subq_ptr_.get()[0].attrs[0][0].alias
$21 = 0x7f338c911e10 "count(*)"

adofsauron · 2022-11-14T08:13:31Z

Problem solving:

If the virtual column does not point to the physical column, the check is not performed

 
common::Tribool SubSelectColumn::ContainsImpl(core::MIIterator const &mit, types::RCDataType const &v) {
  // If the sub-select is something like 'select null from xxx' then there
  // is no need to execute the sub-select, just return common::TRIBOOL_UNKNOWN.
 
	Tianmu::core::TempTable::Attr *attr = tmp_tab_subq_ptr_->GetAttrP(col_idx_);
  if (!attr->term.vc) {
    return ContainsWithVirtualColumn(mit, v);
	}

common::Tribool SubSelectColumn::ContainsWithVirtualColumn(core::MIIterator const &mit, types::RCDataType const &v) {
  common::Tribool res = false;
  bool added_new_value = false;
  if (types::RequiresUTFConversions(GetCollation()) && Type().IsString()) {
    for (int64_t i = no_cached_values_; i < tmp_tab_subq_ptr_->NumOfObj(); i++) {
      no_cached_values_++;
      added_new_value = true;
      if (tmp_tab_subq_ptr_->IsNull(i, col_idx_)) {
        res = common::TRIBOOL_UNKNOWN;
      } else {
        types::BString val;
        tmp_tab_subq_ptr_->GetTableString(val, i, col_idx_);
        val.MakePersistent();
        if (CollationStrCmp(GetCollation(), val, v.ToBString()) == 0) {
          res = true;
          break;
        }
      }
    }
  } else {
    for (int64_t i = no_cached_values_; i < tmp_tab_subq_ptr_->NumOfObj(); i++) {
      no_cached_values_++;
      added_new_value = true;
      if (tmp_tab_subq_ptr_->IsNull(i, col_idx_)) {
        res = common::TRIBOOL_UNKNOWN;
      } else {
        types::RCValueObject value;
        if (Type().IsString()) {
          types::BString s;
          tmp_tab_subq_ptr_->GetTableString(s, i, col_idx_);
          value = s;
          static_cast<types::BString *>(value.Get())->MakePersistent();
        } else
          value = tmp_tab_subq_ptr_->GetValueObject(i, col_idx_);
        if (value == v) {
          res = true;
          break;
        }
      }
    }
  }
 
	return res;
}

Result verification:

mysql> select *
    ->   from t_test1
    ->  where id in (select count(*) from t_test2 group by last_name);
+----+------------+-----------+-----+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+-----+-------+---------+
|  1 | 郑         | 丁天      | 0   |    77 |       1 |
|  2 | 杨         | 景        | 1   |    57 |       2 |
|  3 | 刘         | 景        | 1   |    95 |       3 |
|  4 | 刘         | 闵立      | 1   |    83 |       4 |
|  5 | 王         | 六军      | 0   |    62 |       5 |
|  6 | 孙         | 君闵      | 1   |    94 |       6 |
|  7 | 徐         | 京黎      | 1   |    54 |       7 |
|  8 | 彭         | 京黎      | 0   |    91 |       8 |
|  9 | 孙         | 民        | 1   |    64 |       9 |
| 10 | 吴         | 立俊      | 0   |    44 |      10 |
| 11 | 阮         | 八        | 0   |    97 |      11 |
| 12 | 高         | 黎        | 1   |    95 |      12 |
| 13 | 柳         | 天        | 1   |    71 |      13 |
| 14 | 彭         | 五骏      | 1   |    75 |      14 |
| 18 | 高         | 乙民      | 0   |    79 |      18 |
+----+------------+-----------+-----+-------+---------+
15 rows in set (0.05 sec)

adofsauron · 2022-11-14T09:17:36Z

Code changes for the final solution:

1. Add a function to the TempTable class that identifies the Field

temp_table.cpp

class TempTable : public JustATable {
 public:
  class Attr final : public PhysicalColumn {
   public:

    bool IsField() const { return mode == common::ColOperation::LISTING && term.vc; }
    bool ShouldOutput() const { return IsField() && alias; }

2. The function SubSelectColumn: : ContainsImpl do feature recognition

common::Tribool SubSelectColumn::ContainsImpl(core::MIIterator const &mit, types::RCDataType const &v) {
  // If the sub-select is something like 'select null from xxx' then there
  // is no need to execute the sub-select, just return common::TRIBOOL_UNKNOWN.
  Tianmu::core::TempTable::Attr *attr = tmp_tab_subq_ptr_->GetAttrP(col_idx_);
  if (attr && attr->IsField()) {
    VirtualColumn *vc = attr->term.vc;
    if (vc->IsFullConst() && vc->IsNull(core::MIIterator(nullptr, multi_index_->ValueOfPower())))
      return common::TRIBOOL_UNKNOWN;
    }

) the reason is The subquery generates a temp table, but the count(*) column of the subquery is not a physical column, so the interface is incompatible. so if the virtual column does not point to the physical column, the check is not performed

haitaoguan added the A-bug Something isn't working label Nov 9, 2022

RingsC assigned adofsauron Nov 14, 2022

RingsC added the prio: high High priority label Nov 14, 2022

This was referenced Nov 14, 2022

fix(tianmu): fix crashed when the subquery includes a SORT clause (#906) #930

Closed

fix(tianmu): fix crashed when the subquery includes a SORT clause (#906) #931

Merged

mergify bot closed this as completed in #931 Nov 16, 2022

haitaoguan changed the title ~~The instance crashed because the subquery includes a SORT clause but it cannot be flattened.~~ bug:The instance crashed because the subquery includes a SORT clause but it cannot be flattened. Nov 21, 2022

This was referenced May 6, 2023

backport: crashed when the subquery includes a SORT clause #1678

Open

backport: fix LEFT JOIN query result err with WHERE IS NULL clause #1680

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

bug:The instance crashed because the subquery includes a SORT clause but it cannot be flattened. #906

bug:The instance crashed because the subquery includes a SORT clause but it cannot be flattened. #906

haitaoguan commented Nov 9, 2022 •

edited

Loading

adofsauron commented Nov 14, 2022

adofsauron commented Nov 14, 2022 •

edited

Loading

adofsauron commented Nov 14, 2022 •

edited

Loading

adofsauron commented Nov 14, 2022 •

edited

Loading

bug:The instance crashed because the subquery includes a SORT clause but it cannot be flattened. #906

bug:The instance crashed because the subquery includes a SORT clause but it cannot be flattened. #906

Comments

haitaoguan commented Nov 9, 2022 • 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?

adofsauron commented Nov 14, 2022

adofsauron commented Nov 14, 2022 • edited Loading

Analysis of Problems:

Column properties of the temporary table of the subquery

adofsauron commented Nov 14, 2022 • edited Loading

Problem solving:

Result verification:

adofsauron commented Nov 14, 2022 • edited Loading

Code changes for the final solution:

1. Add a function to the TempTable class that identifies the Field

2. The function SubSelectColumn: : ContainsImpl do feature recognition

haitaoguan commented Nov 9, 2022 •

edited

Loading

adofsauron commented Nov 14, 2022 •

edited

Loading

adofsauron commented Nov 14, 2022 •

edited

Loading

adofsauron commented Nov 14, 2022 •

edited

Loading