bug: select ... where a = -9223372036854775807, return -9223372036854775806

[hustjieke]

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> create table t(a bigint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(-9223372036854775806), (-9223372036854775806);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t;
+----------------------+
| a                    |
+----------------------+
| -9223372036854775806 |
| -9223372036854775806 |
+----------------------+
2 rows in set (0.01 sec)

mysql> select * from t where a < null;
Empty set (1 min 43.74 sec)

mysql> select * from t where a = 9223372036854775808;
Empty set (18.57 sec)

mysql> select * from t where a = -9223372036854775807;
+----------------------+
| a                    |
+----------------------+
| -9223372036854775806 |
| -9223372036854775806 |
+----------------------+
2 rows in set (10.29 sec)

Expected behavior

mysql> select * from t where a = -9223372036854775807;
Empty set (18.57 sec)

How To Reproduce

No response

Environment

version < v1.0.3-beta

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

• [X] Yes, I will!

[hustjieke]

Insert values in unifom mode, e.g.: insert values (1), (1), (1); dpn.max = dpn.min = -9223372036854775807.

[hustjieke]

Condition where a = -9223372036854775807 will be changed to where a between -9223372036854775806 and -9223372036854775806, sth. wrong happened when set condtion value from -9223372036854775807 to -9223372036854775806.

In function RoughCheckBetween(): v1=v2= -9223372036854775806, this make the rough check result = RS_ALL;

common::RoughSetValue TianmuAttr::RoughCheckBetween(int pack, int64_t v1, int64_t v2) {
  common::RoughSetValue res = common::RoughSetValue::RS_SOME;  // calculate as for common::Operator::O_BETWEEN
                                                               // and then consider negation
...
...
  } else if (!is_float && (v1 <= dpn.min_i && v2 >= dpn.max_i)) {
    res = common::RoughSetValue::RS_ALL;

[hustjieke]

root cause:

• when v = NULL_VALUE_64; v++

std::shared_ptr<ValueOrNull> MysqlExpression::ItemInt2ValueOrNull(Item *item) {
  auto val = std::make_shared<ValueOrNull>();
  int64_t v = item->val_int();
  if (v == common::NULL_VALUE_64)
    v++;

call stack:

Tianmu::core::ValueOrNull::SetFixed(Tianmu::core::ValueOrNull * const this, int64_t v) (/github/stonedb/storage/tianmu/core/value_or_null.h:63)
Tianmu::core::MysqlExpression::ItemInt2ValueOrNull(Item * item) (/github/stonedb/storage/tianmu/core/mysql_expression.cpp:562)
Tianmu::core::MysqlExpression::Evaluate(Tianmu::core::MysqlExpression * const this) (/github/stonedb/storage/tianmu/core/mysql_expression.cpp:459)
Tianmu::core::Query::CreateColumnFromExpression(Tianmu::core::Query * const this, const std::vector<Tianmu::core::MysqlExpression*, std::allocator<Tianmu::core::MysqlExpression*> > & exprs, Tianmu::core::TempTable * temp_table, int temp_table_alias, Tianmu::core::MultiIndex * mind) (/github/stonedb/storage/tianmu/core/query.cpp:474)
Tianmu::core::Query::Preexecute(Tianmu::core::Query * const this, Tianmu::core::CompiledQuery & qu, Tianmu::core::ResultSender * sender, bool display_now) (/github/stonedb/storage/tianmu/core/query.cpp:820)
Tianmu::core::Engine::Execute(Tianmu::core::Engine * const this, THD * thd, LEX * lex, Query_result * result_output, SELECT_LEX_UNIT * unit_for_union) (/github/stonedb/storage/tianmu/core/engine_execute.cpp:484)
Tianmu::core::Engine::HandleSelect(Tianmu::core::Engine * const this, THD * thd, LEX * lex, Query_result *& result, ulong setup_tables_done_option, int & res, int & is_optimize_after_tianmu, int & tianmu_free_join, int with_insert) (/github/stonedb/storage/tianmu/core/engine_execute.cpp:238)
Tianmu::DBHandler::ha_my_tianmu_query(THD * thd, LEX * lex, Query_result *& result_output, ulong setup_tables_done_option, int & res, int & is_optimize_after_tianmu, int & tianmu_free_join, int with_insert) (/github/stonedb/storage/tianmu/handler/ha_my_tianmu.cpp:89)
execute_sqlcom_select(THD * thd, TABLE_LIST * all_tables) (/github/stonedb/sql/sql_parse.cc:5208)
mysql_execute_command(THD * thd, bool first_level) (/github/stonedb/sql/sql_parse.cc:2851)
mysql_parse(THD * thd, Parser_state * parser_state) (/github/stonedb/sql/sql_parse.cc:5646)
dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (/github/stonedb/sql/sql_parse.cc:1495)
do_command(THD * thd) (/github/stonedb/sql/sql_parse.cc:1034)
handle_connection(void * arg) (/github/stonedb/sql/conn_handler/connection_handler_per_thread.cc:313)
pfs_spawn_thread(void * arg) (/github/stonedb/storage/perfschema/pfs.cc:2197)
libpthread.so.0!start_thread(void * arg) (/build/glibc-SzIz7B/glibc-2.31/nptl/pthread_create.c:477)
libc.so.6!clone() (/build/glibc-SzIz7B/glibc-2.31/sysdeps/unix/sysv/linux/x86_64/clone.S:95)

[hustjieke]

A special case, maybe fixed in the future. ping @davidshiz