提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

2022-11-23 mysql列存储引擎-ESCAPE处理错误-问题记录

mysql引擎存储 记录 2022 11 23 escape
2023-09-27 14:25:42 时间

摘要:

mysql列存储引擎-ESCAPE处理错误-问题记录

相关ISSUE:

https://github.com/stoneatom/stonedb/issues/271

https://github.com/stoneatom/stonedb/issues/272

https://github.com/stoneatom/stonedb/issues/273

DDL:

SET @OLD_SQL_MODE12595=@@SQL_MODE, @@SQL_MODE='';
SHOW LOCAL VARIABLES LIKE 'SQL_MODE';

CREATE TABLE BUG_12595(a varchar(100)) ENGINE = TIANMU;
INSERT INTO BUG_12595 VALUES ('hakan%'), ('hakank'), ("ha%an");

SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*';

问题分析:

mysql列存储引擎层对于like的谓词,

既没有调用Item_func_like::val_int使用my_wildcmp去比较,利用sql层处理escapse.

mysql列存储层自身也没有处理escapse.

mysql/innodb对于like的处理:
 

调用堆栈:

(gdb) bt
#0  my_wildcmp_8bit (cs=0xb1ca00 <my_charset_latin1>, str=0x7fb7e0006d7a "hakan%", ' ' <repeats 94 times>, str_end=0x7fb7e0006d80 ' ' <repeats 94 times>, wildstr=0x7fb7e0001818 "hakan*%", 
    wildend=0x7fb7e000181f "", escape=42, w_one=95, w_many=37) at ctype-simple.c:894
#1  0x00000000004e82ff in Item_func_like::val_int (this=0x7fb7e0001918) at item_cmpfunc.cc:2687
#2  0x0000000000572365 in evaluate_join_record (join=join@entry=0x7fb7e0001b60, join_tab=join_tab@entry=0x7fb7e0007878, error=<optimized out>, report_error=report_error@entry=0x334f2e0 "")
    at sql_select.cc:8588
#3  0x000000000057259c in sub_select (join=join@entry=0x7fb7e0001b60, join_tab=0x7fb7e0007878, end_of_records=end_of_records@entry=false) at sql_select.cc:8545
#4  0x0000000000579b3d in do_select (join=join@entry=0x7fb7e0001b60, fields=fields@entry=0x334e790, table=table@entry=0x0, procedure=0x0) at sql_select.cc:8311
#5  0x0000000000587d1c in JOIN::exec (this=this@entry=0x7fb7e0001b60) at sql_select.cc:1436
#6  0x000000000058349a in mysql_select (thd=thd@entry=0x334e2a0, rref_pointer_array=rref_pointer_array@entry=0x334e8c8, tables=0x7fb7e0001498, 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=0x7fb7e0001b40, unit=unit@entry=0x334e320, 
    select_lex=select_lex@entry=0x334e6a0) at sql_select.cc:1591
#7  0x0000000000583674 in handle_select (thd=thd@entry=0x334e2a0, lex=lex@entry=0x334e308, result=result@entry=0x7fb7e0001b40, setup_tables_done_option=setup_tables_done_option@entry=0)
    at sql_select.cc:186
#8  0x000000000054269d in mysql_execute_command (thd=thd@entry=0x334e2a0) at sql_parse.cc:2298
#9  0x00000000005428d7 in mysql_parse (thd=thd@entry=0x334e2a0, inBuf=<optimized out>, length=<optimized out>) at sql_parse.cc:5101
#10 0x00000000005435dc in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x334e2a0, packet=packet@entry=0x336a131 "", packet_length=packet_length@entry=58) at sql_parse.cc:1579
#11 0x00000000005446f8 in do_command (thd=0x334e2a0) at sql_parse.cc:1386
#12 0x00000000005451ea in handle_one_connection (arg=<optimized out>) at sql_parse.cc:1043
#13 0x00007fb7f008eea5 in start_thread () from /lib64/libpthread.so.0
#14 0x00007fb7eef42b0d in clone () from /lib64/libc.so.6

my_wildcmp_8bit实现:


int my_wildcmp_8bit(CHARSET_INFO *cs, const char *str, const char *str_end, const char *wildstr, const char *wildend,
                    int escape, int w_one, int w_many)
{
  int result = -1; /* Not found, using wildcards */

  while (wildstr != wildend)
  {
    while (*wildstr != w_many && *wildstr != w_one)
    {
      if (*wildstr == escape && wildstr + 1 != wildend)
        wildstr++;

      if (str == str_end || likeconv(cs, *wildstr++) != likeconv(cs, *str++))
        return (1); /* No match */
      if (wildstr == wildend)
        return (str != str_end); /* Match if both are at end */
      result = 1;                /* Found an anchor char     */
    }
    if (*wildstr == w_one)
    {
      do
      {
        if (str == str_end) /* Skip one char if possible */
          return (result);
        INC_PTR(cs, str, str_end);
      } while (++wildstr < wildend && *wildstr == w_one);
      if (wildstr == wildend)
        break;
    }
    if (*wildstr == w_many)
    { /* Found w_many */
      uchar cmp;

      wildstr++;
      /* Remove any '%' and '_' from the wild search string */
      for (; wildstr != wildend; wildstr++)
      {
        if (*wildstr == w_many)
          continue;
        if (*wildstr == w_one)
        {
          if (str == str_end)
            return (-1);
          INC_PTR(cs, str, str_end);
          continue;
        }
        break; /* Not a wild character */
      }
      if (wildstr == wildend)
        return (0); /* Ok if w_many is last */
      if (str == str_end)
        return (-1);

      if ((cmp = *wildstr) == escape && wildstr + 1 != wildend)
        cmp = *++wildstr;

      INC_PTR(cs, wildstr, wildend); /* This is compared trough cmp */
      cmp = likeconv(cs, cmp);
      do
      {
        while (str != str_end && (uchar)likeconv(cs, *str) != cmp) str++;
        if (str++ == str_end)
          return (-1);
        {
          int tmp = my_wildcmp_8bit(cs, str, str_end, wildstr, wildend, escape, w_one, w_many);
          if (tmp <= 0)
            return (tmp);
        }
      } while (str != str_end && wildstr[0] != w_many);
      return (-1);
    }
  }
  return (str != str_end ? 1 : 0);
}

mysql列存储引擎层对于like的处理

调用堆栈:

(gdb) bt
#0  Tianmu::core::TianmuAttr::EvaluatePack_Like_UTF (this=0x7fa1d88fcc80, mit=..., dim=0, d=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/tianmu_attr_exqp.cpp:486
#1  0x0000000002d35ebe in Tianmu::core::TianmuAttr::EvaluatePack (this=0x7fa1d88fcc80, mit=..., dim=0, d=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/tianmu_attr_exqp.cpp:73
#2  0x0000000002df3e1c in Tianmu::vcolumn::SingleColumn::EvaluatePackImpl (this=0x7fa1d88fe7e0, mit=..., desc=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/vc/single_column.cpp:172
#3  0x0000000002e073e3 in Tianmu::vcolumn::VirtualColumnBase::EvaluatePack (this=0x7fa1d88fe7e0, mit=..., desc=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/vc/virtual_column_base.h:353
#4  0x0000000002f9dc90 in Tianmu::core::Descriptor::EvaluatePackImpl (this=0x7fa1d88fb250, mit=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/descriptor.cpp:837
#5  0x0000000002f9e100 in Tianmu::core::Descriptor::EvaluatePack (this=0x7fa1d88fb250, mit=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/descriptor.cpp:906
#6  0x000000000301bda0 in Tianmu::core::ParameterizedFilter::ApplyDescriptor (this=0x7fa1d88fe3a0, desc_number=0, limit=-1)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/parameterized_filter.cpp:1466
#7  0x000000000301a2a6 in Tianmu::core::ParameterizedFilter::UpdateMultiIndex (this=0x7fa1d88fe3a0, count_only=false, limit=-1)
    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=0x7fa352f12810, qu=..., sender=0x7fa1d88fcf10, 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=0x50e27a0, thd=0x7fa1d8000e10, lex=0x7fa1d8003138, result_output=0x7fa1d80063f8, 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=0x50e27a0, thd=0x7fa1d8000e10, lex=0x7fa1d8003138, result=@0x7fa352f12dd8: 0x7fa1d80063f8, setup_tables_done_option=0, 
    res=@0x7fa352f12dd4: 0, optimize_after_tianmu=@0x7fa352f12dcc: 1, tianmu_free_join=@0x7fa352f12dd0: 1, with_insert=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/tianmu/core/engine_execute.cpp:238
#11 0x0000000002d6c24d in Tianmu::handler::ha_my_tianmu_query (thd=0x7fa1d8000e10, lex=0x7fa1d8003138, result_output=@0x7fa352f12dd8: 0x7fa1d80063f8, setup_tables_done_option=0, res=@0x7fa352f12dd4: 0, 
    optimize_after_tianmu=@0x7fa352f12dcc: 1, tianmu_free_join=@0x7fa352f12dd0: 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=0x7fa1d8000e10, all_tables=0x7fa1d8005c80) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:5184
#13 0x00000000023a7f99 in mysql_execute_command (thd=0x7fa1d8000e10, 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=0x7fa1d8000e10, parser_state=0x7fa352f13f90) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:5622
#15 0x00000000023a4d74 in dispatch_command (thd=0x7fa1d8000e10, com_data=0x7fa352f14730, 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=0x7fa1d8000e10) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/sql/sql_parse.cc:1034
#17 0x00000000024d5249 in handle_connection (arg=0x81395a0) 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=0x7b16c20) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-133/storage/perfschema/pfs.cc:2197
#19 0x00007fa35d8c41ca in start_thread () from /lib64/libpthread.so.0
#20 0x00007fa35ae2ee73 in clone () from /lib64/libc.so.6

TianmuAttr::EvaluatePack_Like_UTF实现:


void TianmuAttr::EvaluatePack_Like_UTF(MIUpdatingIterator &mit, int dim, Descriptor &d) {
  MEASURE_FET("TianmuAttr::EvaluatePack_Like_UTF(...)");
  int pack = mit.GetCurPackrow(dim);
  if (pack == -1) {
    mit.ResetCurrentPack();
    mit.NextPackrow();
    return;
  }
  auto p = get_packS(pack);
  if (p == nullptr) {  // => nulls only
    mit.ResetCurrentPack();
    mit.NextPackrow();
    return;
  }
  types::BString pattern;
  d.val1.vc->GetValueString(pattern, mit);
  size_t min_len = 0;  // the number of fixed characters
  for (uint i = 0; i < pattern.len_; i++)
    if (pattern[i] != '%')
      min_len++;
  std::unordered_set<uint16_t> possible_ids;
  bool use_trie = false;
  bool pure_prefix = false;
  if (!pattern.IsNullOrEmpty() && !IsSpecialChar(pattern[0], d) && p->IsTrie()) {
    auto first_wildcard = pattern.begin();
    std::size_t prefixlen = 0;
    while (first_wildcard != pattern.end() && !IsSpecialChar(*first_wildcard, d)) {
      first_wildcard++;
      prefixlen++;
    }
    use_trie = p->LikePrefix(pattern, prefixlen, possible_ids);
    if (possible_ids.empty()) {
      mit.ResetCurrentPack();
      mit.NextPackrow();
      return;
    }
    if (first_wildcard == pattern.end() || (*first_wildcard == '%' && (++first_wildcard) == pattern.end()))
      pure_prefix = true;
  }
  do {
    int inpack = mit.GetCurInpack(dim);
    if (mit[dim] == common::NULL_VALUE_64 || p->IsNull(inpack)) {
      mit.ResetCurrent();
    } else if (use_trie && p->IsNotMatched(inpack, possible_ids)) {
      mit.ResetCurrent();
    } else if (pure_prefix) {
      // The query is something like 'Pattern%' or 'Pattern', so
      // if p->IsNotMatched() == false, then this is a match and
      // there is no need to check it here.
    } else {
      types::BString v(p->GetValueBinary(inpack));
      auto len = v.size();
      bool res;
      if (len < min_len)
        res = false;
      else {
        v.MakePersistent();
        int x = common::wildcmp(d.GetCollation(), v.val_, v.val_ + v.len_, pattern.val_, pattern.val_ + pattern.len_,
                                '\\', '_', '%');
        res = (x == 0 ? true : false);
      }
      if (d.op == common::Operator::O_NOT_LIKE)
        res = !res;
      if (!res)
        mit.ResetCurrent();
    }
    ++mit;
  } while (mit.IsValid() && !mit.PackrowStarted());
}


(gdb) p pattern
$17 = {
  <Tianmu::types::ValueBasic<Tianmu::types::BString>> = {
    <Tianmu::types::TianmuDataType> = {
      _vptr.TianmuDataType = 0x4231a50 <vtable for Tianmu::types::BString+16>, 
      null_ = false
    }, 
    members of Tianmu::types::ValueBasic<Tianmu::types::BString>: 
    static null_value_ = {
      <Tianmu::types::ValueBasic<Tianmu::types::BString>> = {
        <Tianmu::types::TianmuDataType> = {
          _vptr.TianmuDataType = 0x4231a50 <vtable for Tianmu::types::BString+16>, 
          null_ = true
        }, 
        members of Tianmu::types::ValueBasic<Tianmu::types::BString>: 
        static null_value_ = <same as static member of an already seen type>
      }, 
      members of Tianmu::types::BString: 
      val_ = 0x0, 
      len_ = 0, 
      pos_ = 0, 
      persistent_ = false, 
      static value_type_ = Tianmu::types::ValueTypeEnum::STRING_TYPE
    }
  }, 
  members of Tianmu::types::BString: 
  val_ = 0x7fa1d88ff600 "hakan*%", 
  len_ = 7, 
  pos_ = 0, 
  persistent_ = true, 
  static value_type_ = Tianmu::types::ValueTypeEnum::STRING_TYPE
}

三. ERROR 1210 (HY000): Incorrect arguments to ESCAPE

设置为NO_BACKSLASH_ESCAPES模式

SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
SHOW LOCAL VARIABLES LIKE 'SQL_MODE';

返回错误的堆栈:

(gdb) bt
#0  Item_func_like::eval_escape_clause (this=0x7f6df0005948, thd=0x7f6df0000e10) at /root/work/stonedb-dev-20221123/sql/item_cmpfunc.cc:6615
#1  0x0000000001d93a66 in Item_func_like::fix_fields (this=0x7f6df0005948, thd=0x7f6df0000e10, ref=0x7f6df0004c58) at /root/work/stonedb-dev-20221123/sql/item_cmpfunc.cc:6533
#2  0x00000000023eebe6 in st_select_lex::setup_conds (this=0x7f6df0004b80, thd=0x7f6df0000e10) at /root/work/stonedb-dev-20221123/sql/sql_resolver.cc:1191
#3  0x00000000023ec263 in st_select_lex::prepare (this=0x7f6df0004b80, thd=0x7f6df0000e10) at /root/work/stonedb-dev-20221123/sql/sql_resolver.cc:212
#4  0x0000000002c6a776 in Tianmu::core::optimize_select (thd=0x7f6df0000e10, select_options=2147748608, result=0x7f6df00063f8, select_lex=0x7f6df0004b80, optimize_after_tianmu=@0x7f6fe968fdcc: 0, 
    free_join=@0x7f6fe968fdd0: 1) at /root/work/stonedb-dev-20221123/storage/tianmu/core/engine_execute.cpp:336
#5  0x0000000002c6a130 in Tianmu::core::Engine::HandleSelect (this=0x50607a0, thd=0x7f6df0000e10, lex=0x7f6df0003138, result=@0x7f6fe968fdd8: 0x7f6df00063f8, setup_tables_done_option=0, 
    res=@0x7f6fe968fdd4: 0, optimize_after_tianmu=@0x7f6fe968fdcc: 0, tianmu_free_join=@0x7f6fe968fdd0: 1, with_insert=0) at /root/work/stonedb-dev-20221123/storage/tianmu/core/engine_execute.cpp:231
#6  0x0000000002d6c1bf in Tianmu::handler::ha_my_tianmu_query (thd=0x7f6df0000e10, lex=0x7f6df0003138, result_output=@0x7f6fe968fdd8: 0x7f6df00063f8, setup_tables_done_option=0, res=@0x7f6fe968fdd4: 0, 
    optimize_after_tianmu=@0x7f6fe968fdcc: 0, tianmu_free_join=@0x7f6fe968fdd0: 1, with_insert=0) at /root/work/stonedb-dev-20221123/storage/tianmu/handler/ha_my_tianmu.cpp:88
#7  0x00000000023aeb83 in execute_sqlcom_select (thd=0x7f6df0000e10, all_tables=0x7f6df0005c80) at /root/work/stonedb-dev-20221123/sql/sql_parse.cc:5184
#8  0x00000000023a7f19 in mysql_execute_command (thd=0x7f6df0000e10, first_level=true) at /root/work/stonedb-dev-20221123/sql/sql_parse.cc:2831
#9  0x00000000023afbe9 in mysql_parse (thd=0x7f6df0000e10, parser_state=0x7f6fe9690f90) at /root/work/stonedb-dev-20221123/sql/sql_parse.cc:5622
#10 0x00000000023a4cf4 in dispatch_command (thd=0x7f6df0000e10, com_data=0x7f6fe9691730, command=COM_QUERY) at /root/work/stonedb-dev-20221123/sql/sql_parse.cc:1495
#11 0x00000000023a3b35 in do_command (thd=0x7f6df0000e10) at /root/work/stonedb-dev-20221123/sql/sql_parse.cc:1034
#12 0x00000000024d51c9 in handle_connection (arg=0x8437ac0) at /root/work/stonedb-dev-20221123/sql/conn_handler/connection_handler_per_thread.cc:313
#13 0x0000000002ba24e8 in pfs_spawn_thread (arg=0x7ea2980) at /root/work/stonedb-dev-20221123/storage/perfschema/pfs.cc:2197
#14 0x00007f6ff3d481ca in start_thread () from /lib64/libpthread.so.0
#15 0x00007f6ff12b2e73 in clone () from /lib64/libc.so.6

相关文章