stonedb
stonedb copied to clipboard
stoneatom
feat: support SELECT ..where not like
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!
like or not like don't support int type, only support string VARCHAR BLOBtype
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.
}
It returns to MySQL, mysql should handle this case, and why stonedb issue this error message. @DandreChen
And in debug mode, StoneDB crashes, Pls find out which assert failed. @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)
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.
