bug: Integer Type is filtered by where return incorrect result

davidshiz commented 1 year ago

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_int(id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t_int VALUES (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_int;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT * from t_int WHERE id = 1.1;
Empty set (0.00 sec)

mysql> SELECT * from t_int WHERE id = '1.1';
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Expected behavior

mysql> SELECT * from t_int WHERE id = 1.1;
Empty set (0.00 sec)

mysql> SELECT * from t_int WHERE id = '1.1';
Empty set (0.00 sec)

How To Reproduce

CREATE TABLE t_int(id INT);
INSERT INTO t_int VALUES (1), (2);
SELECT * from t_int WHERE id = 1.1;
SELECT * from t_int WHERE id = '1.1';

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 commented 1 year ago

Catch infos in funciton void ConditionEncoder::TransformOtherThanINsOnNumerics() {: Normally, 1.1 should be ret 1 after 1-level encode using EncodeValue64() and v1_rounded will be set to true.

    v1 = attr->EncodeValue64(desc->val1.vc->GetValue(mit), v1_rounded,
                             &tianmu_err_code);  // 1-level values

then the operator will transform to Operator::0_FALSE and return empty result:

  if (v1_rounded) {
    if (ISTypeOfEqualOperator(desc->op)) {
      desc->op = common::Operator::O_FALSE;
      return;
    }

But if condition like where a ="1.1", we get interger value 1 and v1_rounded will not be set to true, in this way we get data equal to 1.

hustjieke commented 1 year ago

root cause: In function ValueParserForText::ParseNum(), when do cast string to tianmu_num, if string has dot, it will be truncated.

        if (has_dot) {
          no_digs_after_dot--;
          if (*val_ptr != '0')
            tianmuret = common::ErrorCode::VALUE_TRUNCATED;

call stack:

Tianmu::types::ValueParserForText::ParseNum(const Tianmu::types::BString & tianmu_s, Tianmu::types::TianmuNum & tianmu_n, short scale) (/github/stonedb/storage/tianmu/types/value_parser4txt.cpp:136)
Tianmu::types::TianmuNum::ParseNum(const Tianmu::types::BString & tianmu_s, Tianmu::types::TianmuNum & tianmu_n, short scale) (/github/stonedb/storage/tianmu/types/tianmu_num.cpp:101)
Tianmu::vcolumn::String2NumCastColumn::String2NumCastColumn(Tianmu::vcolumn::String2NumCastColumn * const this, Tianmu::vcolumn::VirtualColumn * from, const Tianmu::core::ColumnType & to) (/github/stonedb/storage/tianmu/vc/type_cast_column.cpp:46)
Tianmu::core::Descriptor::CoerceColumnType(Tianmu::core::Descriptor * const this, Tianmu::vcolumn::VirtualColumn *& for_typecast) (/github/stonedb/storage/tianmu/core/descriptor.cpp:1696)
Tianmu::core::Descriptor::CoerceColumnTypes(Tianmu::core::Descriptor * const this) (/github/stonedb/storage/tianmu/core/descriptor.cpp:1746)
Tianmu::core::ParameterizedFilter::SyntacticalDescriptorListPreprocessing(Tianmu::core::ParameterizedFilter * const this, bool for_rough_query) (/github/stonedb/storage/tianmu/core/parameterized_filter.cpp:970)
Tianmu::core::ParameterizedFilter::UpdateMultiIndex(Tianmu::core::ParameterizedFilter * const this, bool count_only, int64_t limit) (/github/stonedb/storage/tianmu/core/parameterized_filter.cpp:1160)
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:797)
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:477)
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)

stoneatom / stonedb