search

stoneatom / stonedb

StoneDB is an Open-Source MySQL HTAP and MySQL-Native DataBase for OLTP, Real-Time Analytics, a counterpart of MySQLHeatWave. (https://stonedb.io)
https://stonedb.io/
GNU General Public License v2.0
862 stars 139 forks source link

bug: SELECT from DATE type table with where query, return wrong result #1567

Open davidshiz opened 1 year ago

davidshiz commented 1 year ago

Have you read the Contributing Guidelines on issues?

Please confirm if bug report does NOT exists already ?

Describe the problem

mysql> CREATE TABLE t1 ( a DATE );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------------+
| a          |
+------------+
| 2009-09-22 |
| 2009-09-22 |
| 2009-09-22 |
| 2009-09-23 |
| 2009-09-23 |
| 2009-09-23 |
+------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE a >=  20090923;
+------------+
| a          |
+------------+
| 2009-09-22 |
| 2009-09-22 |
| 2009-09-22 |
| 2009-09-23 |
| 2009-09-23 |
| 2009-09-23 |
+------------+
6 rows in set (0.00 sec)

Expected behavior

mysql> SELECT * FROM t1 WHERE a >=  20090923;
+------------+
| a          |
+------------+
| 2009-09-23 |
| 2009-09-23 |
| 2009-09-23 |
+------------+
3 rows 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: d0665f9dc
        Last commit time: Date:   Wed Apr 12 10:52:39 2023 +0800
        Build time: Date: Wed Apr 12 19:37:30 CST 2023

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

hustjieke commented 1 year ago

Assigned me!

hustjieke commented 1 year ago

Simplify sql:

mysql> CREATE TABLE t1 ( a DATE );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES ('2009-09-22');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES ('2009-09-23');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE a >=  20090923;
+------------+
| a          |
+------------+
| 2009-09-22 |
| 2009-09-23 |
+------------+
2 rows in set (0.00 sec
hustjieke commented 1 year ago

I found the reason why this query return all tuples: left boundary: v1 = 0 (not correct) right boundary: v2 = 141413550567456768 dpn.min_i = 141413413128503296 dpn.max_i = 141413550567456768

After rough check, return common::RoughSetValue::RS_ALL, this is not correct. 

// 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));

  // before:
  // if(v1 == common::PLUS_INF_64 || v2 == common::MINUS_INF_64) --> RS_NONE
  // if(is_float && (v1 == *(int64_t *)&common::PLUS_INF_DBL || v2 == *(int64_t *)&common::MINUS_INF_DBL)) --> RS_NONE
  // actually the v1 or v2 equal to boundary is illegal and it will cause empty result when condition
  // like `where col = PLUS_INF_64`, `where col = PLUS_INF_DBL`, `where col = MINUS_INF_DBL`
  if (!is_float && (v1 > dpn.max_i || v2 < dpn.min_i)) {
    res = common::RoughSetValue::RS_NONE;
  } else if (is_float && (*(double *)&v1 > dpn.max_d || *(double *)&v2 < dpn.min_d)) {
    res = common::RoughSetValue::RS_NONE;
  } else if (!is_float && (v1 <= dpn.min_i && v2 >= dpn.max_i)) {
    res = common::RoughSetValue::RS_ALL;

We should find out why left boundary:v1 = 0(not correct)

hustjieke commented 1 year ago

extract v from exprWHERE a <= 20090923, but get v= 1838504387920527360

std::shared_ptr<ValueOrNull> MysqlExpression::ItemInt2ValueOrNull(Item *item) {
  auto val = std::make_shared<ValueOrNull>();
  int64_t v = item->val_int();
Tianmu::core::MysqlExpression::ItemInt2ValueOrNull(Item * item) (/github/stonedb/storage/tianmu/core/mysql_expression.cpp:559)
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:513)
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:243)
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/sql/ha_my_tianmu.cpp:95)
execute_sqlcom_select(THD * thd, TABLE_LIST * all_tables) (/github/stonedb/sql/sql_parse.cc:5204)
mysql_execute_command(THD * thd, bool first_level) (/github/stonedb/sql/sql_parse.cc:2847)
mysql_parse(THD * thd, Parser_state * parser_state) (/github/stonedb/sql/sql_parse.cc:5642)
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 commented 1 year ago

extract v from exprWHERE a <= 20090923, but get v= 1838504387920527360

std::shared_ptr<ValueOrNull> MysqlExpression::ItemInt2ValueOrNull(Item *item) {
  auto val = std::make_shared<ValueOrNull>();
  int64_t v = item->val_int();
Tianmu::core::MysqlExpression::ItemInt2ValueOrNull(Item * item) (/github/stonedb/storage/tianmu/core/mysql_expression.cpp:559)
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:513)
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:243)
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/sql/ha_my_tianmu.cpp:95)
execute_sqlcom_select(THD * thd, TABLE_LIST * all_tables) (/github/stonedb/sql/sql_parse.cc:5204)
mysql_execute_command(THD * thd, bool first_level) (/github/stonedb/sql/sql_parse.cc:2847)
mysql_parse(THD * thd, Parser_state * parser_state) (/github/stonedb/sql/sql_parse.cc:5642)
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)

1838504387920527360 get from this function in sql/item_cmpfunc.cc: image