feat: support SELECT ..where not like

[shangyanwen]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

mysql> SELECT * FROM t1 WHERE t1_int NOT LIKE 1;

ERROR 6 (HY000): The query includes syntax that is not supported by the storage engine. Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.

Expected behavior

mysql> SELECT * FROM t1 WHERE t1_int NOT LIKE 1;
+--------+---------+
| t1_int | t1_char |
+--------+---------+
|      2 | aaa     |
|      3 | bbb     |
|      4 | ccc     |
|      5 | ddd     |
+--------+---------+
4 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE t1(t1_int INT, t1_char CHAR(5))ENGINE=tianmu;
INSERT INTO t1 VALUES(1,'aaa'),(2,'aaa'),(3,'bbb'),(4,'ccc'),(5,'ddd');
SELECT * FROM t1 WHERE t1_int NOT LIKE 1;

Environment

Ver 14.14 Distrib 5.7.36-StoneDB, for Linux (x86_64) using  EditLine wrapper

【notes】The debug version will crash

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

• [ ] Yes, I will!

[DandreChen]

ACK

[DandreChen]

like or not like don't support int type, only support string VARCHAR BLOBtype

[DandreChen]

code :

      if ((op == common::Operator::O_LIKE || op == common::Operator::O_NOT_LIKE) &&
          !(an_arg->field_type() == MYSQL_TYPE_VARCHAR || an_arg->field_type() == MYSQL_TYPE_STRING ||
            an_arg->field_type() == MYSQL_TYPE_VAR_STRING || an_arg->field_type() == MYSQL_TYPE_BLOB)) {
        return CondID(-1);  // Argument of LIKE is not a string, return to MySQL.
      }

[RingsC]

It returns to MySQL, mysql should handle this case, and why stonedb issue this error message. @DandreChen

[RingsC]

And in debug mode, StoneDB crashes, Pls find out which assert failed. @DandreChen

[DandreChen]

OK，I will fix it.

[DandreChen]

using test case，the latest code will not crash in debug mode .

[DandreChen]

but when set tianmu_ini_allowmysqlquerypath=1, running test case lead to cash

st_select_lex::prepare(st_select_lex * const this, THD * thd) (\opt\github\stonedb57\sql\sql_resolver.cc:110)
handle_query(THD * thd, LEX * lex, Query_result * result, ulonglong added_options, ulonglong removed_options, int optimize_after_bh, int free_join_from_bh) (\opt\github\stonedb57\sql\sql_select.cc:139)
execute_sqlcom_select(THD * thd, TABLE_LIST * all_tables) (\opt\github\stonedb57\sql\sql_parse.cc:5218)
mysql_execute_command(THD * thd, bool first_level) (\opt\github\stonedb57\sql\sql_parse.cc:2856)
mysql_parse(THD * thd, Parser_state * parser_state) (\opt\github\stonedb57\sql\sql_parse.cc:5655)
dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (\opt\github\stonedb57\sql\sql_parse.cc:1495)
do_command(THD * thd) (\opt\github\stonedb57\sql\sql_parse.cc:1034)
handle_connection(void * arg) (\opt\github\stonedb57\sql\conn_handler\connection_handler_per_thread.cc:313)
pfs_spawn_thread(void * arg) (\opt\github\stonedb57\storage\perfschema\pfs.cc:2197)
libpthread.so.0!start_thread (未知源:0)
libc.so.6!clone (未知源:0)

[isredstar]

else if ((d.op == common::Operator::O_LIKE || d.op == common::Operator::O_NOT_LIKE) &&
               (GetPackType() == common::PackType::STR )) {
      DEBUG_ASSERT(vc1->IsConst());
      types::BString pat;
      vc1->GetValueString(pat, mit);
      common::RoughSetValue res = common::RoughSetValue::RS_SOME;
      // here: check min, max
      uint pattern_prefix = 0;        // e.g. "ab_cd_e%f"  -> 7
      uint pattern_fixed_prefix = 0;  // e.g. "ab_cd_e%f"  -> 2
      uint pack_prefix;
      if (types::RequiresUTFConversions(d.GetCollation())) {
        my_match_t mm;
        if (d.GetCollation().collation->coll->instr(d.GetCollation().collation, pat.val_, pat.len_, "%", 1, &mm, 1) ==
            2)
          pattern_prefix = pattern_fixed_prefix = mm.end;

        if (d.GetCollation().collation->coll->instr(d.GetCollation().collation, pat.val_, pat.len_, "_", 1, &mm, 1) ==
            2)
          if (mm.end < pattern_fixed_prefix)
            pattern_fixed_prefix = mm.end;

        if ((pattern_fixed_prefix > 0) &&
            types::BString(pat.val_, pattern_fixed_prefix).LessEqThanMaxUTF(dpn.max_s, Type().GetCollation()) == false)
          res = common::RoughSetValue::RS_NONE;

        if (pattern_fixed_prefix > GetActualSize(pack))
          res = common::RoughSetValue::RS_NONE;
        pack_prefix = GetPrefixLength(pack);
        if (res == common::RoughSetValue::RS_SOME && pack_prefix > 0 &&
            pattern_fixed_prefix <= pack_prefix  // special case: "xyz%" and the
                                                 // pack prefix is at least 3
            && pattern_fixed_prefix + 1 == pat.len_ && pat[pattern_fixed_prefix] == '%') {
          if (d.GetCollation().collation->coll->strnncoll(d.GetCollation().collation, (const uchar *)pat.val_,
                                                          pattern_fixed_prefix, (const uchar *)dpn.min_s,
                                                          pattern_fixed_prefix, 0) == 0)
            res = common::RoughSetValue::RS_ALL;
          else
            res = common::RoughSetValue::RS_NONE;  // prefix and pattern are different
        }

      } else {
        while (pattern_prefix < pat.len_ && pat[pattern_prefix] != '%') pattern_prefix++;
        while (pattern_fixed_prefix < pat.len_ && pat[pattern_fixed_prefix] != '%' && pat[pattern_fixed_prefix] != '_')
          pattern_fixed_prefix++;

        if ((pattern_fixed_prefix > 0) && types::BString(pat.val_, pattern_fixed_prefix).LessEqThanMax(dpn.max_s) ==
                                              false)  // val_t==nullptr means +/-infty
          res = common::RoughSetValue::RS_NONE;
        if (pattern_fixed_prefix > GetActualSize(pack))
          res = common::RoughSetValue::RS_NONE;
        pack_prefix = GetPrefixLength(pack);
        if (res == common::RoughSetValue::RS_SOME && pack_prefix > 0 &&
            pattern_fixed_prefix <= pack_prefix  // special case: "xyz%" and the
                                                 // pack prefix is at least 3
            && pattern_fixed_prefix + 1 == pat.len_ && pat[pattern_fixed_prefix] == '%') {
          if (std::memcmp(pat.val_, dpn.min_s, pattern_fixed_prefix) == 0)  // pattern is equal to the prefix
            res = common::RoughSetValue::RS_ALL;
          else
            res = common::RoughSetValue::RS_NONE;  // prefix and pattern are different
        }
      }

      if (res == common::RoughSetValue::RS_SOME && std::min(pattern_prefix, pack_prefix) < pat.len_ &&
          !types::RequiresUTFConversions(d.GetCollation())) {
        types::BString pattern_for_cmap;  // note that cmap is shifted by a common prefix!
        if (pattern_prefix > pack_prefix)
          pattern_for_cmap = types::BString(pat.val_ + pack_prefix,
                                            pat.len_ - pack_prefix);  // "xyz%abc" -> "z%abc"
        else
          pattern_for_cmap = types::BString(pat.val_ + pattern_prefix,
                                            pat.len_ - pattern_prefix);  // "xyz%abc" -> "%abc"

        if (!(pattern_for_cmap.len_ == 1 && pattern_for_cmap[0] == '%')) {  // i.e. "%" => all is matching
          if (auto sp = GetFilter_CMap())
            res = sp->IsLike(pattern_for_cmap, pack, d.like_esc);
        } else
          res = common::RoughSetValue::RS_ALL;
      }
      if (d.op == common::Operator::O_NOT_LIKE) {
        if (res == common::RoughSetValue::RS_ALL)
          res = common::RoughSetValue::RS_NONE;
        else if (res == common::RoughSetValue::RS_NONE)
          res = common::RoughSetValue::RS_ALL;
      }
      if ((dpn.numOfNulls != 0 || additional_nulls_possible) && res == common::RoughSetValue::RS_ALL)
        res = common::RoughSetValue::RS_SOME;
      return res;
    } 

The handling of INT type about like and not like doesn't exist.