bug: When using where to filter hexadecimal formate data, return incorrect

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

mysql> CREATE TABLE t_latin1(
    ->   a CHAR(20) CHARACTER SET latin1,
    ->   b VARCHAR(20) CHARACTER SET latin1,
    ->   c TEXT(20) CHARACTER SET latin1
    -> )engine=tianmu;
Query OK, 0 rows affected (0.02 sec)

mysql> SET SQL_MODE="STRICT_TRANS_TABLES";
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> INSERT INTO t_latin1 values(x'f242', x'f242', x'f242');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t_latin1 where a=x'f242';
Empty set (0.01 sec)

Expected behavior

mysql> select * from t_latin1 where a=x'f242';
+------+------+------+
| a    | b    | c    |
+------+------+------+
| òB   | òB   | òB   |
+------+------+------+
1 row in set (0.00 sec)

How To Reproduce

No response

Environment

root@ub01:/stonedb57/install/bin# ./mysqld --version
./mysqld  Ver 5.7.36-StoneDB-v1.0.3 for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 31919be01
        Last commit time: Date:   Thu Apr 20 10:19:54 2023 +0800
        Build time: Date: Mon Apr 24 11:43:10 CST 2023

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

• Yes, I will!

[RingsC]

relevant issue #1362.

[chenshengjiang]

When insert value (x'f242', x'f242', x'f242') into table, it will be treated as òB, òB, òB, so the variable maxlen in dpn is 2.
x'f242' in where conditon will be handled as string 62018 with len 5, as 5 > 2 then in RoughCheck it will think there is no values int the pack meet the condition.

[chenshengjiang]

This bug roots in storage/tianmu/core/query.cpp:1105

if (an_arg->type() == Item::VARBIN_ITEM) {
      String str;
      an_arg->val_str(&str);  // sets null_value
      if (!an_arg->null_value) {
        if (an_arg->max_length <= 8) {
          Item *int_item = new Item_int((ulonglong)an_arg->val_int());
          MysqlExpression *mysql_expression = nullptr;
          MysqlExpression::Item2VarID item2varid;
          gc_expressions.push_back(mysql_expression = new MysqlExpression(int_item, item2varid));
          vc.n = VirtualColumnAlreadyExists(tmp_table, mysql_expression);
          if (vc.n == common::NULL_VALUE_32) {
            cq->CreateVirtualColumn(vc, tmp_table, mysql_expression);
            tab_id2expression.insert(std::make_pair(tmp_table, std::make_pair(vc.n, mysql_expression)));
          }
        } else
          return QueryRouteTo::kToMySQL;  // too large binary to be treated
                                          // as BIGINT
      } else {
        return QueryRouteTo::kToMySQL;
      }
}

Item_hex_string will be transformed into Item_int here eventually leading to bugs.
Actually, this if block is unnecessary, the transformation of Mysql Item will be handled by function WrapMysqlExpression.