bug: bigint boundary value problem

[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 t1 (
    ->  value64  bigint  not null,
    ->  value32  integer          not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(9223372036854775806, 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(9223372036854775807, 2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---------------------+---------+
| value64             | value32 |
+---------------------+---------+
| 9223372036854775806 |       1 |
| 9223372036854775807 |       2 |
+---------------------+---------+
2 rows in set (0.00 sec)

mysql> select * from t1 where value64= 9223372036854775806;
+---------------------+---------+
| value64             | value32 |
+---------------------+---------+
| 9223372036854775806 |       1 |
+---------------------+---------+
1 row in set (0.00 sec)

mysql> select * from t1 where value64= 9223372036854775807;
Empty set (0.00 sec)

Expected behavior

mysql> select * from t1 where value64= 9223372036854775807;
+---------------------+---------+
| value64             | value32 |
+---------------------+---------+
| 9223372036854775807 |       2 |
+---------------------+---------+
1 row in set (0.00 sec)

How To Reproduce

create table t1 (
 value64  bigint   not null,
 value32  integer          not null
);
insert into t1 values(9223372036854775806, 1);
insert into t1 values(9223372036854775807, 2);
select * from t1 where value64= 9223372036854775807;

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB-v1.0.2 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: 0f4250589
        Last commit time: Date:   Tue Feb 21 06:09:37 2023 +0000
        Build time: Date: Wed Feb 22 17:50:51 CST 2023
root@ub01:~# cat /etc/issue
Ubuntu 20.04.5 LTS \n \l

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

• Yes, I will!

[hustjieke]

ACK.

[davidshiz]

unsigned bigint also have this problem

[hustjieke]

In function UpdateGlobalRoughFilter, var loc_rs should return Tianmu::common::RoughSetValue::RS_SOME , actualy we get Tianmu::common::RoughSetValue::RS_NONE and caused the return value any_nonempty=false:

bool RoughMultiIndex::UpdateGlobalRoughFilter(int dim, int desc_num) {
  bool any_nonempty = false;
  common::RoughSetValue *loc_rs = GetLocalDescFilter(dim, desc_num, true);
  if (loc_rs == nullptr)
    return true;
  for (int p = 0; p < no_packs[dim]; p++) {
    if (rf[dim][p] == common::RoughSetValue::RS_UNKNOWN) {  // not known yet - get the first
                                                            // information available
      rf[dim][p] = loc_rs[p];
      if (rf[dim][p] != common::RoughSetValue::RS_NONE)
        any_nonempty = true;
    } else if (loc_rs[p] == common::RoughSetValue::RS_NONE)
      rf[dim][p] = common::RoughSetValue::RS_NONE;
    else if (rf[dim][p] != common::RoughSetValue::RS_NONE) {  // else no change
      any_nonempty = true;
      if (loc_rs[p] != common::RoughSetValue::RS_ALL ||
          rf[dim][p] != common::RoughSetValue::RS_ALL)  // else rf[..] remains common::RoughSetValue::RS_ALL
        rf[dim][p] = common::RoughSetValue::RS_SOME;
    }
  }
  return any_nonempty;
}

This caused multi index set to be empty and rough_mind_ cleared in function ParameterizedFilter::UpdateMultiIndex().

  nonempty = RoughUpdateMultiIndex();  // calculate all rough conditions,

  if ((!nonempty && empty_cannot_grow) || mind_->m_conn->Explain()) {
    mind_->Empty();  // nonempty==false if the whole result is empty (outer joins considered)
    rough_mind_->ClearLocalDescFilters();
    return;
  }

[hustjieke]

In function UpdateGlobalRoughFilter, var loc_rs should return Tianmu::common::RoughSetValue::RS_SOME , actualy we get Tianmu::common::RoughSetValue::RS_NONE and caused the return value any_nonempty=false:

bool RoughMultiIndex::UpdateGlobalRoughFilter(int dim, int desc_num) {
  bool any_nonempty = false;
  common::RoughSetValue *loc_rs = GetLocalDescFilter(dim, desc_num, true);
  if (loc_rs == nullptr)
    return true;
  for (int p = 0; p < no_packs[dim]; p++) {
    if (rf[dim][p] == common::RoughSetValue::RS_UNKNOWN) {  // not known yet - get the first
                                                            // information available
      rf[dim][p] = loc_rs[p];
      if (rf[dim][p] != common::RoughSetValue::RS_NONE)
        any_nonempty = true;
    } else if (loc_rs[p] == common::RoughSetValue::RS_NONE)
      rf[dim][p] = common::RoughSetValue::RS_NONE;
    else if (rf[dim][p] != common::RoughSetValue::RS_NONE) {  // else no change
      any_nonempty = true;
      if (loc_rs[p] != common::RoughSetValue::RS_ALL ||
          rf[dim][p] != common::RoughSetValue::RS_ALL)  // else rf[..] remains common::RoughSetValue::RS_ALL
        rf[dim][p] = common::RoughSetValue::RS_SOME;
    }
  }
  return any_nonempty;
}

This caused multi index set to be empty and rough_mind_ cleared in function ParameterizedFilter::UpdateMultiIndex().

  nonempty = RoughUpdateMultiIndex();  // calculate all rough conditions,

  if ((!nonempty && empty_cannot_grow) || mind_->m_conn->Explain()) {
    mind_->Empty();  // nonempty==false if the whole result is empty (outer joins considered)
    rough_mind_->ClearLocalDescFilters();
    return;
  }

root cause:
When do rough check on tianmu attr, condition where value64= 9223372036854775807 will be transformed
between(9223372036854775807, 9223372036854775807) in tianmu.

v1 = 9223372036854775807
v2 = 9223372036854775807

In this case v1 == common::PLUS_INF_64 in if is ture and set the res =common::RoughSetValue::RS_NONE

// check whether any value from the pack may meet the condition "... BETWEEN min
// AND max"
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
  bool is_float = Type().IsFloat();
  auto const &dpn(get_dpn(pack));
  if (!is_float && (v1 == common::PLUS_INF_64 || v2 == common::MINUS_INF_64)) {
    res = common::RoughSetValue::RS_NONE;

[hustjieke]

call stack:

Tianmu::core::TianmuAttr::RoughCheckBetween(Tianmu::core::TianmuAttr * const this, int pack, int64_t v1, int64_t v2) (/github/stonedb/storage/tianmu/core/tianmu_attr_exeq_rs.cpp:566)
Tianmu::core::TianmuAttr::RoughCheck(Tianmu::core::TianmuAttr * const this, int pack, Tianmu::core::Descriptor & d, bool additional_nulls_possible) (/github/stonedb/storage/tianmu/core/tianmu_attr_exeq_rs.cpp:382)
Tianmu::vcolumn::SingleColumn::RoughCheckImpl(Tianmu::vcolumn::SingleColumn * const this, const Tianmu::core::MIIterator & mit, Tianmu::core::Descriptor & d) (/github/stonedb/storage/tianmu/vc/single_column.cpp:195)
Tianmu::vcolumn::VirtualColumnBase::RoughCheck(Tianmu::vcolumn::VirtualColumnBase * const this, const Tianmu::core::MIIterator & it, Tianmu::core::Descriptor & d) (/github/stonedb/storage/tianmu/vc/virtual_column_base.h:359)
Tianmu::core::Descriptor::EvaluateRoughlyPack(Tianmu::core::Descriptor * const this, const Tianmu::core::MIIterator & mit) (/github/stonedb/storage/tianmu/core/descriptor.cpp:447)
Tianmu::core::ParameterizedFilter::RoughUpdateMultiIndex(Tianmu::core::ParameterizedFilter * const this) (/github/stonedb/storage/tianmu/core/parameterized_filter.cpp:383)
Tianmu::core::ParameterizedFilter::UpdateMultiIndex(Tianmu::core::ParameterizedFilter * const this, bool count_only, int64_t limit) (/github/stonedb/storage/tianmu/core/parameterized_filter.cpp:1232)
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:799)
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:499)
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:253)
Tianmu::handler::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:90)
execute_sqlcom_select(THD * thd, TABLE_LIST * all_tables) (/github/stonedb/sql/sql_parse.cc:5209)
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:5647)
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]

double boundary has the same problem:

create table txxx(a double);
Query OK, 1 row affected (0.00 sec)

insert into txxx values(1.79769313486231570814527423731704357e+308);
Query OK, 1 row affected (0.00 sec)

insert into txxx values(-1.79769313486231570814527423731704357e+308);
Query OK, 1 row affected (0.00 sec)

mysql> select * from txxx;
+-------------------------+
| a                       |
+-------------------------+
|  1.7976931348623157e308 |
| -1.7976931348623157e308 |
+-------------------------+
2 rows in set (0.00 sec)

mysql> select * from txxx where a = -1.7976931348623157e308;
Empty set (0.01 sec)

mysql> select * from txxx where a = 1.7976931348623157e308;;
Empty set (0.01 sec)