bug: SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; The query result is empty

[shangyanwen]

Describe the problem

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

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

SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*';
##test result
Empty set (0.00 sec)

Expected behavior

##reference ：innodb test result
mysql> SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*';
+--------+
| a      |
+--------+
| hakan% |
+--------+
1 row in set (0.00 sec)

How To Reproduce

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

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

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

Environment

1. StoneDB for mysql5.7 (release)
2. Ubuntu 20.04.4

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

• Yes, I will!

[adofsauron]

ACK

[adofsauron]

innodb does not make an error because it matches Item_func_like::val_int. tianmu does not make an error because it neither calls the match to Item_func_like::val_int nor handles escape itself

[adofsauron]

(gdb) bt
#0  my_wildcmp_8bit (cs=0xb1ca00 <my_charset_latin1>, str=0x7fb7e0006d80 ' ' <repeats 94 times>, str_end=0x7fb7e0006d80 ' ' <repeats 94 times>, wildstr=0x7fb7e000181f "", wildend=0x7fb7e000181f "", 
    escape=42, w_one=95, w_many=37) at ctype-simple.c:906
#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

[adofsauron]

tianmu neither calls a match to Item_func_like::val_int nor handles escape itself

(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

(gdb) p pattern
$16 = {
  <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
}

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());
}