摘要:

mysql列存储引擎-varchar(255) The result of adding data is not queried-问题记录

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

DDL:

表结构:

CREATE TABLE device_delta_collect
(
    reportTime varchar(255) DEFAULT NULL,
    deviceId   varchar(255) DEFAULT NULL,
    multiple   varchar(255) DEFAULT NULL,
    deltaJgs   varchar(255) DEFAULT NULL
) ENGINE = tianmu;

插入数据:

insert into device_delta_collect (reportTime, deviceId, deltaJgs, multiple) values ('2022-08-20 22:33:58', '1547034559760195585', '1', '1');

查询SQL:

select * from device_delta_collect a where a.deviceId = 1547034559760195587 limit 10;

问题定位:

对比INNODB的处理

调用堆栈:

(gdb) layout src
(gdb) bt
#0  Item_int::val_real (this=0x7f6bcc000fc8) at item.h:1211
#1  0x00000000004e5ac2 in Arg_comparator::compare_real (this=0x7f6bcc0010e0) at item_cmpfunc.cc:403
#2  0x00000000004e3cf0 in compare (this=<optimized out>) at item_cmpfunc.h:59
#3  Item_func_eq::val_int (this=<optimized out>) at item_cmpfunc.cc:719
#4  0x0000000000572365 in evaluate_join_record (join=join@entry=0x7f6bcc001258, join_tab=join_tab@entry=0x7f6bcc0146e0, error=<optimized out>, report_error=report_error@entry=0x27d82e0 "")
    at sql_select.cc:8588
#5  0x000000000057259c in sub_select (join=join@entry=0x7f6bcc001258, join_tab=0x7f6bcc0146e0, end_of_records=end_of_records@entry=false) at sql_select.cc:8545
#6  0x0000000000579b3d in do_select (join=join@entry=0x7f6bcc001258, fields=fields@entry=0x27d7790, table=table@entry=0x0, procedure=0x0) at sql_select.cc:8311
#7  0x0000000000587d1c in JOIN::exec (this=this@entry=0x7f6bcc001258) at sql_select.cc:1436
#8  0x000000000058349a in mysql_select (thd=thd@entry=0x27d72a0, rref_pointer_array=rref_pointer_array@entry=0x27d78c8, tables=0x7f6bcc000c20, wild_num=<optimized out>, fields=..., 
    conds=<optimized out>, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=select_options@entry=2156153344, result=result@entry=0x7f6bcc001238, unit=unit@entry=0x27d7320, 
    select_lex=select_lex@entry=0x27d76a0) at sql_select.cc:1591
#9  0x0000000000583674 in handle_select (thd=thd@entry=0x27d72a0, lex=lex@entry=0x27d7308, result=result@entry=0x7f6bcc001238, setup_tables_done_option=setup_tables_done_option@entry=0)
    at sql_select.cc:186
#10 0x000000000054269d in mysql_execute_command (thd=thd@entry=0x27d72a0) at sql_parse.cc:2298
#11 0x00000000005428d7 in mysql_parse (thd=thd@entry=0x27d72a0, inBuf=<optimized out>, length=<optimized out>) at sql_parse.cc:5101
#12 0x00000000005435dc in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x27d72a0, packet=packet@entry=0x27f3131 "", packet_length=packet_length@entry=85) at sql_parse.cc:1579
#13 0x00000000005446f8 in do_command (thd=0x27d72a0) at sql_parse.cc:1386
#14 0x00000000005451ea in handle_one_connection (arg=<optimized out>) at sql_parse.cc:1043
#15 0x00007f6be4b18ea5 in start_thread () from /lib64/libpthread.so.0
#16 0x00007f6be39ccb0d in clone () from /lib64/libc.so.6

(gdb) bt
#0  my_strntod_8bit (cs=<optimized out>, str=0x7f6bcc0132ea "1547034559760195585", ' ' <repeats 181 times>..., length=19, end=0x7f6be09b1dc8, err=0x7f6be09b1dc4) at ctype-simple.c:778
#1  0x000000000050c7ca in Field_varstring::val_real (this=<optimized out>) at field.cc:5793
#2  0x00000000004bbfa6 in Item_field::val_real (this=<optimized out>) at item.cc:1401
#3  0x00000000004e5aa4 in Arg_comparator::compare_real (this=0x7f6bcc0010e0) at item_cmpfunc.cc:400
#4  0x00000000004e3cf0 in compare (this=<optimized out>) at item_cmpfunc.h:59
#5  Item_func_eq::val_int (this=<optimized out>) at item_cmpfunc.cc:719
#6  0x0000000000572365 in evaluate_join_record (join=join@entry=0x7f6bcc001258, join_tab=join_tab@entry=0x7f6bcc0146e0, error=<optimized out>, report_error=report_error@entry=0x27d82e0 "")
    at sql_select.cc:8588
#7  0x000000000057259c in sub_select (join=join@entry=0x7f6bcc001258, join_tab=0x7f6bcc0146e0, end_of_records=end_of_records@entry=false) at sql_select.cc:8545
#8  0x0000000000579b3d in do_select (join=join@entry=0x7f6bcc001258, fields=fields@entry=0x27d7790, table=table@entry=0x0, procedure=0x0) at sql_select.cc:8311
#9  0x0000000000587d1c in JOIN::exec (this=this@entry=0x7f6bcc001258) at sql_select.cc:1436
#10 0x000000000058349a in mysql_select (thd=thd@entry=0x27d72a0, rref_pointer_array=rref_pointer_array@entry=0x27d78c8, tables=0x7f6bcc000c20, wild_num=<optimized out>, fields=..., 
    conds=<optimized out>, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=select_options@entry=2156153344, result=result@entry=0x7f6bcc001238, unit=unit@entry=0x27d7320, 
    select_lex=select_lex@entry=0x27d76a0) at sql_select.cc:1591
#11 0x0000000000583674 in handle_select (thd=thd@entry=0x27d72a0, lex=lex@entry=0x27d7308, result=result@entry=0x7f6bcc001238, setup_tables_done_option=setup_tables_done_option@entry=0)
    at sql_select.cc:186
#12 0x000000000054269d in mysql_execute_command (thd=thd@entry=0x27d72a0) at sql_parse.cc:2298
#13 0x00000000005428d7 in mysql_parse (thd=thd@entry=0x27d72a0, inBuf=<optimized out>, length=<optimized out>) at sql_parse.cc:5101
#14 0x00000000005435dc in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x27d72a0, packet=packet@entry=0x27f3131 "", packet_length=packet_length@entry=85) at sql_parse.cc:1579
#15 0x00000000005446f8 in do_command (thd=0x27d72a0) at sql_parse.cc:1386
#16 0x00000000005451ea in handle_one_connection (arg=<optimized out>) at sql_parse.cc:1043
#17 0x00007f6be4b18ea5 in start_thread () from /lib64/libpthread.so.0
#18 0x00007f6be39ccb0d in clone () from /lib64/libc.so.6

对值的处理

double Field_varstring::val_real(void)
{
  int not_used;
  char *end_not_used;
  uint length = length_bytes == 1 ? (uint)(uchar)*ptr : uint2korr(ptr);
  return my_strntod(field_charset, ptr + length_bytes, length, &end_not_used, &not_used);
}

(gdb) p value
$10 = 1547034559760195587
(gdb) p	(double)value
$11 = 1.5470345597601956e+18

mysql列存储引擎的处理:

调用堆栈:

(gdb) bt
#0  Tianmu::core::TianmuAttr::EvaluatePack_BetweenString_UTF (this=0x7fa2ac8fae80, mit=..., dim=0, d=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/tianmu_attr_exqp.cpp:803
#1  0x0000000002d36099 in Tianmu::core::TianmuAttr::EvaluatePack (this=0x7fa2ac8fae80, mit=..., dim=0, d=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/tianmu_attr_exqp.cpp:68
#2  0x0000000002df4088 in Tianmu::vcolumn::SingleColumn::EvaluatePackImpl (this=0x7fa2ac902040, mit=..., desc=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/vc/single_column.cpp:172
#3  0x0000000002e0764f in Tianmu::vcolumn::VirtualColumnBase::EvaluatePack (this=0x7fa2ac902040, mit=..., desc=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/vc/virtual_column_base.h:353
#4  0x0000000002f9defc in Tianmu::core::Descriptor::EvaluatePackImpl (this=0x7fa2ac016c40, mit=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/descriptor.cpp:837
#5  0x0000000002f9e36c in Tianmu::core::Descriptor::EvaluatePack (this=0x7fa2ac016c40, mit=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/descriptor.cpp:906
#6  0x000000000301c00c in Tianmu::core::ParameterizedFilter::ApplyDescriptor (this=0x7fa2ac901900, desc_number=0, limit=10)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/parameterized_filter.cpp:1466
#7  0x000000000301a512 in Tianmu::core::ParameterizedFilter::UpdateMultiIndex (this=0x7fa2ac901900, count_only=false, limit=10)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/parameterized_filter.cpp:1167
#8  0x0000000002c99282 in Tianmu::core::Query::Preexecute (this=0x7fa465b37810, qu=..., sender=0x7fa2ac8fb410, display_now=true)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/query.cpp:793
#9  0x0000000002c6b373 in Tianmu::core::Engine::Execute (this=0x4a4f7a0, thd=0x7fa2ac000e10, lex=0x7fa2ac003138, result_output=0x7fa2ac006428, unit_for_union=0x0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/engine_execute.cpp:477
#10 0x0000000002c6a214 in Tianmu::core::Engine::HandleSelect (this=0x4a4f7a0, thd=0x7fa2ac000e10, lex=0x7fa2ac003138, result=@0x7fa465b37dd8: 0x7fa2ac006428, setup_tables_done_option=0, 
    res=@0x7fa465b37dd4: 0, optimize_after_tianmu=@0x7fa465b37dcc: 1, tianmu_free_join=@0x7fa465b37dd0: 1, with_insert=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/engine_execute.cpp:238
#11 0x0000000002d6c4b9 in Tianmu::handler::ha_my_tianmu_query (thd=0x7fa2ac000e10, lex=0x7fa2ac003138, result_output=@0x7fa465b37dd8: 0x7fa2ac006428, setup_tables_done_option=0, res=@0x7fa465b37dd4: 0, 
    optimize_after_tianmu=@0x7fa465b37dcc: 1, tianmu_free_join=@0x7fa465b37dd0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/handler/ha_my_tianmu.cpp:88
#12 0x00000000023aec03 in execute_sqlcom_select (thd=0x7fa2ac000e10, all_tables=0x7fa2ac005bc8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:5184
#13 0x00000000023a7f99 in mysql_execute_command (thd=0x7fa2ac000e10, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:2831
#14 0x00000000023afc69 in mysql_parse (thd=0x7fa2ac000e10, parser_state=0x7fa465b38f90) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:5622
#15 0x00000000023a4d74 in dispatch_command (thd=0x7fa2ac000e10, com_data=0x7fa465b39730, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:1495
#16 0x00000000023a3bb5 in do_command (thd=0x7fa2ac000e10) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:1034
#17 0x00000000024d5249 in handle_connection (arg=0x7e26240) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/conn_handler/connection_handler_per_thread.cc:313
#18 0x0000000002ba2592 in pfs_spawn_thread (arg=0x77fabd0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/perfschema/pfs.cc:2197
#19 0x00007fa4b5ea31ca in start_thread () from /lib64/libpthread.so.0
#20 0x00007fa4b340de73 in clone () from /lib64/libc.so.6

TianmuAttr::EvaluatePack_BetweenString_UTF

void TianmuAttr::EvaluatePack_BetweenString_UTF(MIUpdatingIterator &mit, int dim, Descriptor &d) {
  MEASURE_FET("TianmuAttr::EvaluatePack_BetweenString_UTF(...)");
  int pack = mit.GetCurPackrow(dim);
  if (pack == -1) {
    mit.ResetCurrentPack();
    mit.NextPackrow();
    return;
  }
  // added trivial case due to OR tree
  if (get_dpn(pack).NullOnly()) {
    mit.ResetCurrentPack();
    mit.NextPackrow();
    return;
  }

  auto p = get_packS(pack);
  if (p == nullptr) {  // => nulls only
    mit.ResetCurrentPack();
    mit.NextPackrow();
    return;
  }
  types::BString v1, v2;
  DTCollation coll = d.GetCollation();
  d.val1.vc->GetValueString(v1, mit);
  d.val2.vc->GetValueString(v2, mit);
  bool use_trie = false;
  uint16_t trie_id;
  if (v1 == v2 && p->IsTrie()) {
    use_trie = p->Lookup(v1, trie_id);
    if (!use_trie) {
      mit.ResetCurrentPack();
      mit.NextPackrow();
      return;
    }
  }
  do {
    int inpack = mit.GetCurInpack(dim);  // row number inside the pack
    if (mit[dim] == common::NULL_VALUE_64 || p->IsNull(inpack)) {
      mit.ResetCurrent();
    } else if (use_trie) {
      if (p->IsNotMatched(inpack, trie_id))
        mit.ResetCurrent();
    } else {
      types::BString v(p->GetValueBinary(inpack));  // change to materialized in case
                                                    // of problems, but the pack should
                                                    // be locked and unchanged here
      // IsNull() below means +/-inf
      bool res =
          (d.sharp &&
           ((v1.IsNull() || CollationStrCmp(coll, v, v1) > 0) && (v2.IsNull() || CollationStrCmp(coll, v, v2) < 0))) ||
          (!d.sharp &&
           ((v1.IsNull() || CollationStrCmp(coll, v, v1) >= 0) && (v2.IsNull() || CollationStrCmp(coll, v, v2) <= 0)));
      if (d.op == common::Operator::O_NOT_BETWEEN)
        res = !res;
      if (!res)
        mit.ResetCurrent();
    }
    ++mit;
  } while (mit.IsValid() && !mit.PackrowStarted());
}