摘要：

mysql列存储引擎-GroupBy查询出错-记录

关联ISSUE: https://github.com/stoneatom/stonedb/issues/1107

DDL:

表结构:

create table A(id int,UnitSN varchar(10),CTIME datetime) engine=tianmu;

create table B(id int,AID int,Name varchar(10),value varchar(10)) engine=tianmu;

插入数据:

insert into A values(1,'A1','2022-12-09 01:00:00');
insert into A values(2,'A2','2022-12-09 02:00:00');
insert into A values(3,'A3','2022-12-09 03:00:00');
insert into A values(4,'A4','2022-12-09 04:00:00');
insert into A values(5,'A5','2022-12-09 05:00:00');

insert into B values(1,1,'V1','XXXXXXX1');
insert into B values(2,1,'V2','XXXXXXX2');
insert into B values(3,1,'V3','XXXXXXX3');
insert into B values(4,1,'V4','XXXXXXX4');
insert into B values(5,1,'V5','XXXXXXX5');

查询SQL:

select a.ID,
       a.UNITSN,
       case upper(b.NAME) when 'V1' then b.VALUE else null end as V1,
       case upper(b.NAME) when 'V2' then b.VALUE else null end as V2,
       case upper(b.NAME) when 'V3' then b.VALUE else null end as V3,
       case upper(b.NAME) when 'V4' then b.VALUE else null end as V4,
       case upper(b.NAME) when 'V5' then b.VALUE else null end as V5
  from A a
  left join B b
    on a.ID = b.AID
 group by a.ID, a.UNITSN;

追踪:

获取计算的值堆栈:

(gdb) bt
#0  Tianmu::vcolumn::ExpressionColumn::GetValueStringImpl (this=0x7fed60997c80, s=..., mit=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/vc/expr_column.cpp:141
#1  0x0000000002ceea21 in Tianmu::vcolumn::VirtualColumnBase::GetValueString (this=0x7fed60997c80, s=..., mit=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/vc/virtual_column_base.h:125
#2  0x0000000002fcca8b in Tianmu::core::GroupTable::PutAggregatedValue (this=0x7fef5cb422a8, col=1, row=0, mit=..., factor=1, as_string=true)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/group_table.cpp:574
#3  0x0000000002fd96b5 in Tianmu::core::GroupByWrapper::PutAggregatedValue (this=0x7fef5cb421e0, gr_a=1, pos=0, mit=..., factor=1)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/groupby_wrapper.cpp:520
#4  0x0000000002f73c04 in Tianmu::core::AggregationAlgorithm::AggregatePackrow (this=0x7fef5cb42570, gbw=..., mit=0x7fef5cb41e70, cur_tuple=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/aggregation_algorithm.cpp:615
#5  0x0000000002f72311 in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7fef5cb42570, gbw=..., limit=@0x7fef5cb421c8: 5, offset=@0x7fef5cb425f8: 0, sender=0x7fed6001d3c0, 
    limit_less_than_no_groups=false, force_parall=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/aggregation_algorithm.cpp:303
#6  0x0000000002f71b04 in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7fef5cb42570, just_distinct=false, limit=@0x7fef5cb425f0: -1, offset=@0x7fef5cb425f8: 0, sender=0x7fed6001d3c0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/aggregation_algorithm.cpp:204
#7  0x0000000002ceb258 in Tianmu::core::TempTable::Materialize (this=0x7fed609980c0, in_subq=false, sender=0x7fed6001d3c0, lazy=false)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/temp_table.cpp:2060
#8  0x0000000002c76a4d in Tianmu::core::Engine::Execute (this=0x4f517a0, thd=0x7fed600128c0, lex=0x7fed60014be8, result_output=0x7fed6099f458, unit_for_union=0x0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/engine_execute.cpp:482
#9  0x0000000002c757b8 in Tianmu::core::Engine::HandleSelect (this=0x4f517a0, thd=0x7fed600128c0, lex=0x7fed60014be8, result=@0x7fef5cb42dc8: 0x7fed6099f458, setup_tables_done_option=0, 
    res=@0x7fef5cb42dc4: 0, optimize_after_tianmu=@0x7fef5cb42dbc: 1, tianmu_free_join=@0x7fef5cb42dc0: 1, with_insert=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/engine_execute.cpp:238
#10 0x0000000002d78703 in Tianmu::handler::ha_my_tianmu_query (thd=0x7fed600128c0, lex=0x7fed60014be8, result_output=@0x7fef5cb42dc8: 0x7fed6099f458, setup_tables_done_option=0, res=@0x7fef5cb42dc4: 0, 
    optimize_after_tianmu=@0x7fef5cb42dbc: 1, tianmu_free_join=@0x7fef5cb42dc0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/handler/ha_my_tianmu.cpp:88
#11 0x00000000023b96cc in execute_sqlcom_select (thd=0x7fed600128c0, all_tables=0x7fed6099e888) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:5217
#12 0x00000000023b29fe in mysql_execute_command (thd=0x7fed600128c0, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:2856
#13 0x00000000023ba731 in mysql_parse (thd=0x7fed600128c0, parser_state=0x7fef5cb43f90) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:5655
#14 0x00000000023af6c8 in dispatch_command (thd=0x7fed600128c0, com_data=0x7fef5cb44730, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:1495
#15 0x00000000023ae509 in do_command (thd=0x7fed600128c0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:1034
#16 0x00000000024dfd27 in handle_connection (arg=0x8336bf0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/conn_handler/connection_handler_per_thread.cc:313
#17 0x0000000002bad070 in pfs_spawn_thread (arg=0x7dcde30) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/perfschema/pfs.cc:2197
#18 0x00007fef671fb1ca in start_thread () from /lib64/libpthread.so.0
#19 0x00007fef6454de73 in clone () from /lib64/libc.so.6

处理函数:

std::shared_ptr<ValueOrNull> MysqlExpression::ItemString2ValueOrNull(Item *item, int maxstrlen,
                                                                     common::ColumnType a_type) {
  auto val = std::make_shared<ValueOrNull>();
  String string_result;
  String *ret = item->val_str(&string_result);
  if (ret != nullptr) {
    char *p = ret->c_ptr_safe();
    if (ATI::IsDateTimeType(a_type)) {
      types::TianmuDateTime tianmu_dt;
      types::BString rbs(p);
      if (rbs.IsNull())
        return val = std::make_shared<ValueOrNull>(common::NULL_VALUE_64);
      else {
        common::ErrorCode rc = types::ValueParserForText::ParseDateTime(rbs, tianmu_dt, a_type);
        if (common::IsError(rc)) {
          return val = std::make_shared<ValueOrNull>(common::NULL_VALUE_64);
        }
        val->SetFixed(tianmu_dt.GetInt64());
      }
    } else {
      uint len = ret->length();
      DEBUG_ASSERT(p[len] == 0);
      if (maxstrlen >= 0 && len > uint(maxstrlen)) {
        len = maxstrlen;
        p[len] = 0;
      }
      val->SetString(p, len);
      val->MakeStringOwner();
    }
  }
  if (item->null_value)
    return std::make_shared<ValueOrNull>();
  return val;
}

填充聚合数据

(gdb) bt
#0  Tianmu::core::AggregatorListT::PutAggregatedValue (this=0x7f2088907c90, buf=0x7f20b8c31010 "\b", v=..., factor=1) at /root/work/stonedb-dev-20221213/storage/tianmu/core/aggregator_basic.cpp:545
#1  0x0000000002fd0945 in Tianmu::core::GroupTable::PutAggregatedValue (this=0x7f227552e2a8, col=2, row=0, mit=..., factor=1, as_string=true)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/group_table.cpp:577
#2  0x0000000002fdd493 in Tianmu::core::GroupByWrapper::PutAggregatedValue (this=0x7f227552e1e0, gr_a=2, pos=0, mit=..., factor=1)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/groupby_wrapper.cpp:520
#3  0x0000000002f77a46 in Tianmu::core::AggregationAlgorithm::AggregatePackrow (this=0x7f227552e570, gbw=..., mit=0x7f227552de70, cur_tuple=0)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/aggregation_algorithm.cpp:615
#4  0x0000000002f76153 in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7f227552e570, gbw=..., limit=@0x7f227552e1c8: 9, offset=@0x7f227552e5f8: 0, sender=0x7f20888fc890, 
    limit_less_than_no_groups=false, force_parall=false) at /root/work/stonedb-dev-20221213/storage/tianmu/core/aggregation_algorithm.cpp:303
#5  0x0000000002f75946 in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7f227552e570, just_distinct=false, limit=@0x7f227552e5f0: -1, offset=@0x7f227552e5f8: 0, sender=0x7f20888fc890)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/aggregation_algorithm.cpp:204
#6  0x0000000002cef030 in Tianmu::core::TempTable::Materialize (this=0x7f208890b3c0, in_subq=false, sender=0x7f20888fc890, lazy=false)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/temp_table.cpp:2060
#7  0x0000000002c7a825 in Tianmu::core::Engine::Execute (this=0x602a7a0, thd=0x7f2088000e10, lex=0x7f2088003138, result_output=0x7f2088903900, unit_for_union=0x0)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/engine_execute.cpp:482
#8  0x0000000002c79590 in Tianmu::core::Engine::HandleSelect (this=0x602a7a0, thd=0x7f2088000e10, lex=0x7f2088003138, result=@0x7f227552edc8: 0x7f2088903900, setup_tables_done_option=0, 
    res=@0x7f227552edc4: 0, optimize_after_tianmu=@0x7f227552edbc: 1, tianmu_free_join=@0x7f227552edc0: 1, with_insert=0) at /root/work/stonedb-dev-20221213/storage/tianmu/core/engine_execute.cpp:238
#9  0x0000000002d7c4db in Tianmu::handler::ha_my_tianmu_query (thd=0x7f2088000e10, lex=0x7f2088003138, result_output=@0x7f227552edc8: 0x7f2088903900, setup_tables_done_option=0, res=@0x7f227552edc4: 0, 
    optimize_after_tianmu=@0x7f227552edbc: 1, tianmu_free_join=@0x7f227552edc0: 1, with_insert=0) at /root/work/stonedb-dev-20221213/storage/tianmu/handler/ha_my_tianmu.cpp:88
#10 0x00000000023baa0c in execute_sqlcom_select (thd=0x7f2088000e10, all_tables=0x7f2088901f88) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:5216
#11 0x00000000023b3d3e in mysql_execute_command (thd=0x7f2088000e10, first_level=true) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:2855
#12 0x00000000023bba71 in mysql_parse (thd=0x7f2088000e10, parser_state=0x7f227552ff90) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:5654
#13 0x00000000023b0a18 in dispatch_command (thd=0x7f2088000e10, com_data=0x7f2275530730, command=COM_QUERY) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:1495
#14 0x00000000023af859 in do_command (thd=0x7f2088000e10) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:1034
#15 0x00000000024e1067 in handle_connection (arg=0x93e2e30) at /root/work/stonedb-dev-20221213/sql/conn_handler/connection_handler_per_thread.cc:313
#16 0x0000000002bb0e48 in pfs_spawn_thread (arg=0x8e47450) at /root/work/stonedb-dev-20221213/storage/perfschema/pfs.cc:2197
#17 0x00007f228065a1ca in start_thread () from /lib64/libpthread.so.0
#18 0x00007f227d7a9e73 in clone () from /lib64/libc.so.6

void AggregatorListT::PutAggregatedValue(unsigned char *buf, const types::BString &v, [[maybe_unused]] int64_t factor) {
  DEBUG_ASSERT((uint)val_len >= v.len_);
  if (*((unsigned short *)buf) == 0 && buf[2] == 0) {  // still null
    stats_updated = false;
    *((unsigned short *)buf) = v.len_;
    if (v.len_ > 0)
      std::memcpy(buf + 2, v.val_, v.len_);
    else
      buf[2] = 1;  // empty string indicator (non-null)
    value_set = true;
  }
}

1.  注意如果是空值则填充

获取空字符串:

(gdb) bt
#0  Tianmu::core::AggregatorListT::GetValueT (this=0x7f2088907c30, buf=0x7f20b8c3103c "") at /root/work/stonedb-dev-20221213/storage/tianmu/core/aggregator_basic.cpp:565
#1  0x0000000002fcfe4e in Tianmu::core::GroupTable::GetValueT (this=0x7f227552e2a8, col=3, row=0) at /root/work/stonedb-dev-20221213/storage/tianmu/core/group_table.cpp:468
#2  0x0000000002fdd550 in Tianmu::core::GroupByWrapper::GetValueT (this=0x7f227552e1e0, col=3, row=0) at /root/work/stonedb-dev-20221213/storage/tianmu/core/groupby_wrapper.cpp:528
#3  0x0000000002f77c8e in Tianmu::core::AggregationAlgorithm::AggregateFillOutput (this=0x7f227552e570, gbw=..., gt_pos=0, omit_by_offset=@0x7f227552e5f8: 0)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/aggregation_algorithm.cpp:654
#4  0x0000000002f76735 in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7f227552e570, gbw=..., limit=@0x7f227552e1c8: 9, offset=@0x7f227552e5f8: 0, sender=0x7f20888fc890, 
    limit_less_than_no_groups=false, force_parall=false) at /root/work/stonedb-dev-20221213/storage/tianmu/core/aggregation_algorithm.cpp:363
#5  0x0000000002f75946 in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7f227552e570, just_distinct=false, limit=@0x7f227552e5f0: -1, offset=@0x7f227552e5f8: 0, sender=0x7f20888fc890)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/aggregation_algorithm.cpp:204
#6  0x0000000002cef030 in Tianmu::core::TempTable::Materialize (this=0x7f208890b3c0, in_subq=false, sender=0x7f20888fc890, lazy=false)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/temp_table.cpp:2060
#7  0x0000000002c7a825 in Tianmu::core::Engine::Execute (this=0x602a7a0, thd=0x7f2088000e10, lex=0x7f2088003138, result_output=0x7f2088903900, unit_for_union=0x0)
    at /root/work/stonedb-dev-20221213/storage/tianmu/core/engine_execute.cpp:482
#8  0x0000000002c79590 in Tianmu::core::Engine::HandleSelect (this=0x602a7a0, thd=0x7f2088000e10, lex=0x7f2088003138, result=@0x7f227552edc8: 0x7f2088903900, setup_tables_done_option=0, 
    res=@0x7f227552edc4: 0, optimize_after_tianmu=@0x7f227552edbc: 1, tianmu_free_join=@0x7f227552edc0: 1, with_insert=0) at /root/work/stonedb-dev-20221213/storage/tianmu/core/engine_execute.cpp:238
#9  0x0000000002d7c4db in Tianmu::handler::ha_my_tianmu_query (thd=0x7f2088000e10, lex=0x7f2088003138, result_output=@0x7f227552edc8: 0x7f2088903900, setup_tables_done_option=0, res=@0x7f227552edc4: 0, 
    optimize_after_tianmu=@0x7f227552edbc: 1, tianmu_free_join=@0x7f227552edc0: 1, with_insert=0) at /root/work/stonedb-dev-20221213/storage/tianmu/handler/ha_my_tianmu.cpp:88
#10 0x00000000023baa0c in execute_sqlcom_select (thd=0x7f2088000e10, all_tables=0x7f2088901f88) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:5216
#11 0x00000000023b3d3e in mysql_execute_command (thd=0x7f2088000e10, first_level=true) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:2855
#12 0x00000000023bba71 in mysql_parse (thd=0x7f2088000e10, parser_state=0x7f227552ff90) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:5654
#13 0x00000000023b0a18 in dispatch_command (thd=0x7f2088000e10, com_data=0x7f2275530730, command=COM_QUERY) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:1495
#14 0x00000000023af859 in do_command (thd=0x7f2088000e10) at /root/work/stonedb-dev-20221213/sql/sql_parse.cc:1034
#15 0x00000000024e1067 in handle_connection (arg=0x93e2e30) at /root/work/stonedb-dev-20221213/sql/conn_handler/connection_handler_per_thread.cc:313
#16 0x0000000002bb0e48 in pfs_spawn_thread (arg=0x8e47450) at /root/work/stonedb-dev-20221213/storage/perfschema/pfs.cc:2197
#17 0x00007f228065a1ca in start_thread () from /lib64/libpthread.so.0
#18 0x00007f227d7a9e73 in clone () from /lib64/libc.so.6

types::BString AggregatorListT::GetValueT(unsigned char *buf) {
  int len = *((unsigned short *)(buf));
  char *p = (char *)(buf + 2);
  if (len == 0) {
    if (*p != 0)                           // empty string indicator: len==0 and nontrivial character
      return types::BString("", 0, true);  // empty string
    return types::BString();               // null value
  }
  types::BString res(p, len);
  return res;
}