bug: When subqueries are associated with input variables and compare with decimal, wrong result. - Githubissues

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

861 stars 140 forks source link

bug: When subqueries are associated with input variables and compare with decimal, wrong result. #1647

Closed haitaoguan closed 1 year ago

haitaoguan 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

SELECT IF(@rowno != a.tax_no, @rankno := 1, @rankno := @rankno + 1) AS "rowNo",
       @rowno := a.tax_no
  FROM (SELECT d.tax_no,
               'a' std_ba
          FROM acct a
          JOIN curr d
            ON a.curr_id = d.row_id
           AND d.deleted_flag = '0'
         WHERE a.deleted_flag = '0'
           AND a.acct_flag IN ('2', '4')) a,
       (SELECT @rowno := NULL, @rankno := 0) r;
+-------+----------------------+
| rowNo | @rowno := a.tax_no |
+-------+----------------------+
|     1 |                    1 |
|     1 |                    1 |
|     1 |                    1 |
......
|     1 |                    1 |
|     1 |                    1 |
+-------+----------------------+
1016 rows in set (0.02 sec)

Expected behavior

+-------+----------------------+
| rowNo | @rowno := a.tax_no |
+-------+----------------------+
|     1 |                    1 |
|     2 |                    1 |
|     3 |                    1 |
......
|  1015 |                    1 |
|  1016 |                    1 |
+-------+----------------------+
1016 rows in set (0.01 sec)

How To Reproduce

No response

Environment

./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: 4a35d96 Last commit time: Date: Fri Apr 28 16:50:27 2023 +0800 Build time: Date: Thu May 4 09:21:37 CST 2023

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

[X] Yes, I will!

adofsauron commented 1 year ago

ACK

adofsauron commented 1 year ago


{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10.50"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "r",
          "access_type": "system",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.20",
            "prefix_cost": "0.00",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "@rowno := NULL",
            "@rankno := 0"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 3,
              "message": "No tables used"
            }
          }
        }
      },
      {
        "table": {
          "table_name": "a",
          "access_type": "ALL",
          "rows_examined_per_scan": 2,
          "rows_produced_per_join": 2,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "10.10",
            "eval_cost": "0.40",
            "prefix_cost": "10.50",
            "data_read_per_join": "48"
          },
          "used_columns": [
            "taxis_no",
            "std_balance"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "288.38"
              },
              "nested_loop": [
                {
                  "table": {
                    "table_name": "a",
                    "access_type": "ALL",
                    "rows_examined_per_scan": 1016,
                    "rows_produced_per_join": 20,
                    "filtered": "2.00",
                    "pushed_condition": "((`mbs`.`a`.`DELETED_FLAG` = '0') and (`mbs`.`a`.`ACCT_FLAG` in ('2','4')))(t0) Pckrows: 1, susp. 1 (0 empty 0 full). Conditions: 2",
                    "cost_info": {
                      "read_cost": "259.94",
                      "eval_cost": "4.06",
                      "prefix_cost": "264.00",
                      "data_read_per_join": "314K"
                    },
                    "used_columns": [
                      "CURRENCY_ID",
                      "ACCT_FLAG",
                      "DELETED_FLAG"
                    ]
                  }
                },
                {
                  "table": {
                    "table_name": "d",
                    "access_type": "ALL",
                    "possible_keys": [
                      "PRIMARY"
                    ],
                    "rows_examined_per_scan": 33,
                    "rows_produced_per_join": 2,
                    "filtered": "0.30",
                    "pushed_condition": "(`mbs`.`d`.`DELETED_FLAG` = '0')(t0) Pckrows: 1, susp. 1 (0 empty 0 full). Conditions: 2",
                    "using_join_buffer": "Block Nested Loop",
                    "cost_info": {
                      "read_cost": "20.32",
                      "eval_cost": "0.41",
                      "prefix_cost": "288.38",
                      "data_read_per_join": "6K"
                    },
                    "used_columns": [
                      "ROW_ID",
                      "TAXIS_NO",
                      "DELETED_FLAG"
                    ]
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}

adofsauron commented 1 year ago

[2023-05-05 09:58:03.758524] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: T:-1 = TABLE_ALIAS(T:0,"c1md_bank_acct") [2023-05-05 09:58:03.758640] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: T:-2 = TMP_TABLE(T:-1,T:4294967293) [2023-05-05 09:58:03.758691] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: T:-3 = TABLE_ALIAS(T:1,"c1cd_currency") [2023-05-05 09:58:03.758736] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:4)) [2023-05-05 09:58:03.758793] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"taxis_no","ALL") [2023-05-05 09:58:03.758861] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.1 = CREATE_VC(T:-2,EXPR("a")) [2023-05-05 09:58:03.758913] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: A:-2 = T:-2.ADD_COLUMN(VC:-2.1,LIST,"std_balance","ALL") [2023-05-05 09:58:03.758959] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.2 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:44)) [2023-05-05 09:58:03.759023] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.3 = CREATE_VC(T:-2,EXPR("0")) [2023-05-05 09:58:03.759075] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: C:0 = CREATE_CONDS(T:-2,VC:-2.2,=,VC:-2.3,) [2023-05-05 09:58:03.759120] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.4 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:26)) [2023-05-05 09:58:03.759177] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.5 = CREATE_VC(T:-2,EXPR("2")) [2023-05-05 09:58:03.759230] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.6 = CREATE_VC(T:-2,EXPR("4")) [2023-05-05 09:58:03.759277] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.7 = CREATE_VC(T:-2,VIRT_COLS([5,..,6](2 items))) [2023-05-05 09:58:03.759324] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: C:0.AND(VC:-2.4,IN,VC:-2.7,) [2023-05-05 09:58:03.759371] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.8 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:9)) [2023-05-05 09:58:03.759416] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: C:0.AND(VC:-2.8,=,VC:-2.3,) [2023-05-05 09:58:03.759457] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.9 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:19)) [2023-05-05 09:58:03.759595] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.10 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:0)) [2023-05-05 09:58:03.759656] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: C:1 = CREATE_CONDS(T:-2,VC:-2.10,=,VC:-2.9,) [2023-05-05 09:58:03.759698] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: C:0.AND(C:1) [2023-05-05 09:58:03.759756] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: T:-2.ADD_CONDS(C:0,HAVING) [2023-05-05 09:58:03.759799] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: T:-2.APPLY_CONDS() [2023-05-05 09:58:03.759849] [1129776] [DEBUG] [compiled_query.cpp:425] MSG: RESULT(T:-2)

adofsauron commented 1 year ago

[2023-05-05 11:18:38.029112] [1129783] [DEBUG] [compiled_query.cpp:425] MSG: T:-1 = TABLE_ALIAS(T:0,"d") [2023-05-05 11:18:38.029226] [1129783] [DEBUG] [compiled_query.cpp:425] MSG: T:-2 = TMP_TABLE(T:4294967295) [2023-05-05 11:18:38.029281] [1129783] [DEBUG] [compiled_query.cpp:425] MSG: VC:-2.0 = CREATE_VC(T:-2,EXPR("if")) [2023-05-05 11:18:38.029391] [1129783] [DEBUG] [compiled_query.cpp:425] MSG: A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"IF(@rowno != d.taxis_no, @rankno := 1, @rankno := @rankno + 1)","ALL") [2023-05-05 11:18:38.029470] [1129783] [DEBUG] [compiled_query.cpp:425] MSG: T:-2.APPLY_CONDS() [2023-05-05 11:18:38.029570] [1129783] [DEBUG] [compiled_query.cpp:425] MSG: RESULT(T:-2) [2023-05-05 11:18:38.031230] [1129783] [DEBUG] [engine_results.cpp:396] MSG: Result: 3 Costtime(ms): 5

adofsauron commented 1 year ago


$7 = {
  <Parse_tree_node> = {
    _vptr.Parse_tree_node = 0x4353e40 <vtable for Item_func_if+16>, 
    contextualized = true, 
    transitional = false
  }, 
  members of Item: 
  is_expensive_cache = -1 '\377', 
  rsize = 0, 
  str_value = {
    m_ptr = 0x0, 
    m_length = 0, 
    m_charset = 0x44644e0 <my_charset_bin>, 
    m_alloced_length = 0, 
    m_is_alloced = false
  }, 
  item_name = {
    <Name_string> = {
      <Simple_cstring> = {
        m_str = 0x7fecf01bb6f0 "IF(@rowno != d.taxis_no, @rankno := 1, @rankno := @rankno + 1)", 
        m_length = 62
      }, <No data fields>}, 
    members of Item_name_string: 
    m_is_autogenerated = true
  }, 
  orig_name = {
    <Name_string> = {
      <Simple_cstring> = {
        m_str = 0x0, 
        m_length = 0
      }, <No data fields>}, 
    members of Item_name_string: 
    m_is_autogenerated = true
  }, 
  next = 0x7fecf01bb178, 
  max_length = 63, 
  marker = 0, 
  decimals = 0 '\000', 
  maybe_null = 1 '\001', 
  null_value = 0 '\000', 
  unsigned_flag = 0 '\000', 
  with_sum_func = 0 '\000', 
  fixed = 1 '\001', 
  collation = {
--Type <RET> for more, q to quit, c to continue without paging--
    collation = 0x446cae0 <my_charset_latin1>, 
    derivation = DERIVATION_NUMERIC, 
    repertoire = 1
  }, 
  cmp_context = 4294967295, 
  runtime_item = false, 
  derived_used = false, 
  with_subselect = 0 '\000', 
  with_stored_program = 0 '\000', 
  tables_locked_cache = false, 
  is_parser_item = true
}

adofsauron commented 1 year ago


$9 = std::map with 1 element = {
  [{
    tab = -1, 
    col = 0
  }] = {
    valtype = Tianmu::core::DataType::ValueType::VT_FIXED, 
    attrtype = Tianmu::common::ColumnType::INT, 
    fixscale = 0, 
    fixmax = 2147483647, 
    collation = {
      collation = 0x446cae0 <my_charset_latin1>, 
      derivation = DERIVATION_NUMERIC, 
      repertoire = 3
    }, 
    precision = 11, 
    unsigned_flag_ = false
  }
}

adofsauron commented 1 year ago


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++;
  val->SetFixed(v);
  if (item->null_value)
    return std::make_shared<ValueOrNull>();
  return val;
}

adofsauron commented 1 year ago


$35 = (Item_func_ne) {
  <Item_bool_rowready_func2> = {
    <Item_bool_func2> = {
      <Item_bool_func> = {
        <Item_int_func> = {
          <Item_func> = {
            <Item_result_field> = {
              <Item> = {
                <Parse_tree_node> = {
                  _vptr.Parse_tree_node = 0x4357200 <vtable for Item_func_ne+16>, 
                  contextualized = true, 
                  transitional = false
                }, 
                members of Item: 
                is_expensive_cache = -1 '\377', 
                rsize = 0, 
                str_value = {
                  m_ptr = 0x0, 
                  m_length = 0, 
                  m_charset = 0x44644e0 <my_charset_bin>, 
                  m_alloced_length = 0, 
                  m_is_alloced = false
                }, 
                item_name = {
                  <Name_string> = {
                    <Simple_cstring> = {
                      m_str = 0x0, 
                      m_length = 0
                    }, <No data fields>}, 
                  members of Item_name_string: 
                  m_is_autogenerated = true
                }, 
                orig_name = {
                  <Name_string> = {
                    <Simple_cstring> = {
                      m_str = 0x0, 
                      m_length = 0
                    }, <No data fields>}, 
                  members of Item_name_string: 
                  m_is_autogenerated = true
                }, 
                next = 0x7fecf01bb410, 
                max_length = 1, 
                marker = 0, 
--Type <RET> for more, q to quit, c to continue without paging--
                decimals = 0 '\000', 
                maybe_null = 1 '\001', 
                null_value = 0 '\000', 
                unsigned_flag = 0 '\000', 
                with_sum_func = 0 '\000', 
                fixed = 1 '\001', 
                collation = {
                  collation = 0x446cae0 <my_charset_latin1>, 
                  derivation = DERIVATION_NUMERIC, 
                  repertoire = 1
                }, 
                cmp_context = 4294967295, 
                runtime_item = false, 
                derived_used = false, 
                with_subselect = 0 '\000', 
                with_stored_program = 0 '\000', 
                tables_locked_cache = false, 
                is_parser_item = false
              }, 
              members of Item_result_field: 
              result_field = 0x0
            }, 
            members of Item_func: 
            args = 0x7fecf01bb5d8, 
            tmp_arg = {0x7fecf01ba7d8, 0x7fecf0005040}, 
            const_item_cache = false, 
            allowed_arg_cols = 1, 
            used_tables_cache = 1, 
            not_null_tables_cache = 1, 
            arg_count = 2
          }, <No data fields>}, 
        members of Item_bool_func: 
        m_created_by_in2exists = false
      }, 
      members of Item_bool_func2: 
      cmp = {
        <Sql_alloc> = {<No data fields>}, 
        members of Arg_comparator: 
        a = 0x7fecf01bb5d8, 
        b = 0x7fecf01bb5e0, 
        func = (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfd4d6 <Arg_comparator::compare_decimal()>, 
        owner = 0x7fecf01bb530, 
        comparators = 0x0, 
        comparator_count = 0, 
--Type <RET> for more, q to quit, c to continue without paging--
        precision = -9.9261575707946013e-234, 
        a_cache = 0x0, 
        b_cache = 0x0, 
        is_nulls_eq = 143, 
        set_null = true, 
        get_value_a_func = 0x0, 
        get_value_b_func = 0x0, 
        json_scalar = 0x0, 
        cmp_collation = {
          collation = 0x44644e0 <my_charset_bin>, 
          derivation = DERIVATION_NONE, 
          repertoire = 3
        }, 
        value1 = {
          m_ptr = 0x0, 
          m_length = 0, 
          m_charset = 0x44644e0 <my_charset_bin>, 
          m_alloced_length = 0, 
          m_is_alloced = false
        }, 
        value2 = {
          m_ptr = 0x0, 
          m_length = 0, 
          m_charset = 0x44644e0 <my_charset_bin>, 
          m_alloced_length = 0, 
          m_is_alloced = false
        }, 

        static comparator_matrix = {{(int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfcfee <Arg_comparator::compare_string()>, 
            (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfd22c <Arg_comparator::compare_e_string()>}, {
            (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfd3c0 <Arg_comparator::compare_real()>, 
            (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfd66a <Arg_comparator::compare_e_real()>}, {
            (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfdb24 <Arg_comparator::compare_int_signed()>, 
            (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfe090 <Arg_comparator::compare_e_int()>}, {
            (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfe23c <Arg_comparator::compare_row()>, 
            (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfe420 <Arg_comparator::compare_e_row()>}, {
            (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfd4d6 <Arg_comparator::compare_decimal()>, 
            (int (Arg_comparator::*)(Arg_comparator * const)) 0x1dfd754 <Arg_comparator::compare_e_decimal()>}}
      }, 
      abort_on_null = true
    }, <No data fields>}, <No data fields>}

adofsauron commented 1 year ago

a4f99fc3739ff3b47b7b43e8960fda74

adofsauron commented 1 year ago

2eed53e35ad249b5908695a42fa2c017