*************** Case 1 *************** drop database if exists merge_db; { "drop_database_arg":"tenant_id", "database_name":"merge_db", "if_exist":true, "to_recyclebin":false } *************** Case 2 *************** create database merge_db; { "create_database_arg":"database_schema", "database_id":-1, "schema_version":1, "database_name":"merge_db", "replica_num":0, "zone_list": [ ], "primary_zone":"", "charset_type":2, "collation_type":"utf8mb4_general_ci", "name_case_mode":-1, "comment":"", "read_only":false, "default_tablegroup_id":-1, "default_tablegroup_name":"", "in_recyclebin":false, "primary_zone_array": [ ] } *************** Case 3 *************** use merge_db; { "stmt_type":97 } *************** Case 4 *************** create table sourceTable(id int primary key, sales int); {"ObTableStmt":{"partition_fun_expr":[], "range_values_exprs":[], "list_values_exprs":[], "index_partition_resolve_results":[], "part_type":8}, "stmt_type":20, "create_table_arg":{"if_not_exist":false, "schema":{"simple_table_schema":{"tenant_id":1, "database_id":18446744073709551615, "tablegroup_id":1099511627777, "table_id":18446744073709551615, "table_name":"sourceTable", "replica_num":0, "previous_locality_str":"", "min_partition_id":0, "session_id":0, "index_type":0, "table_type":3, "data_table_id":0, "name_casemode":-1, "schema_version":0, "part_level":0, "part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":0}, "sub_part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":-1}, "locality_str":"", "zone_list":[], "primary_zone":"", "part_num":0, "subpart_num":0, "partition_num":1, "subpartition_num":0, "partition_array":[{"tenant_id":18446744073709551615, "table_id":18446744073709551615, "part_id":0, "name":"p0", "high_bound_val":, "list_row_values":[], "part_idx":0}], "subpartition_array":null, "index_status":1}, "max_used_column_id":17, "max_used_constraint_id":0, "sess_active_time":0, "rowkey_column_num":1, "index_column_num":0, "rowkey_split_pos":0, "block_size":16384, "is_use_bloomfilter":false, "progressive_merge_num":0, "tablet_size":134217728, "pctfree":10, "load_type":0, "index_using_type":0, "def_type":1, "charset_type":2, "collation_type":45, "create_mem_version":0, "index_status":1, "partition_status":0, "code_version":1, "last_modified_frozen_version":0, "comment":"", "pk_comment":"", "create_host":"", "zone_replica_attr_array":[], "region_replica_num_array":[], "tablegroup_name":"", "compress_func_name":"zstd_1.0", "expire_info":"", "view_schema":{"view_definition":"", "check_option":"none", "is_updatable":"false", "is_materialized":"false"}, "autoinc_column_id":0, "auto_increment":1, "read_only":false, "primary_zone_array":[], "index_tid_array":[], "mv_tid_array":[], "base_table_ids":[], "rowkey_info":{"columns":[{"length":0, "column_id":16, "type":{"type":"INT", "collation":"binary", "coercibility":"NUMERIC"}, "order":0, "fulltext_flag":false}], "capacity":1}, "partition_key_info":{"columns":null, "capacity":0}}, "index_arg_list":[], "constraint_list":[], "db_name":"merge_db", "last_replay_log_id":0, "foreign_key_arg_list":[]}, "index_arg_list":[]} *************** Case 5 *************** create table sourceTable_2(id2 int primary key, sales2 int); {"ObTableStmt":{"partition_fun_expr":[], "range_values_exprs":[], "list_values_exprs":[], "index_partition_resolve_results":[], "part_type":8}, "stmt_type":20, "create_table_arg":{"if_not_exist":false, "schema":{"simple_table_schema":{"tenant_id":1, "database_id":18446744073709551615, "tablegroup_id":1099511627777, "table_id":18446744073709551615, "table_name":"sourceTable_2", "replica_num":0, "previous_locality_str":"", "min_partition_id":0, "session_id":0, "index_type":0, "table_type":3, "data_table_id":0, "name_casemode":-1, "schema_version":0, "part_level":0, "part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":0}, "sub_part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":-1}, "locality_str":"", "zone_list":[], "primary_zone":"", "part_num":0, "subpart_num":0, "partition_num":1, "subpartition_num":0, "partition_array":[{"tenant_id":18446744073709551615, "table_id":18446744073709551615, "part_id":0, "name":"p0", "high_bound_val":, "list_row_values":[], "part_idx":0}], "subpartition_array":null, "index_status":1}, "max_used_column_id":17, "max_used_constraint_id":0, "sess_active_time":0, "rowkey_column_num":1, "index_column_num":0, "rowkey_split_pos":0, "block_size":16384, "is_use_bloomfilter":false, "progressive_merge_num":0, "tablet_size":134217728, "pctfree":10, "load_type":0, "index_using_type":0, "def_type":1, "charset_type":2, "collation_type":45, "create_mem_version":0, "index_status":1, "partition_status":0, "code_version":1, "last_modified_frozen_version":0, "comment":"", "pk_comment":"", "create_host":"", "zone_replica_attr_array":[], "region_replica_num_array":[], "tablegroup_name":"", "compress_func_name":"zstd_1.0", "expire_info":"", "view_schema":{"view_definition":"", "check_option":"none", "is_updatable":"false", "is_materialized":"false"}, "autoinc_column_id":0, "auto_increment":1, "read_only":false, "primary_zone_array":[], "index_tid_array":[], "mv_tid_array":[], "base_table_ids":[], "rowkey_info":{"columns":[{"length":0, "column_id":16, "type":{"type":"INT", "collation":"binary", "coercibility":"NUMERIC"}, "order":0, "fulltext_flag":false}], "capacity":1}, "partition_key_info":{"columns":null, "capacity":0}}, "index_arg_list":[], "constraint_list":[], "db_name":"merge_db", "last_replay_log_id":0, "foreign_key_arg_list":[]}, "index_arg_list":[]} *************** Case 6 *************** create table targetTable(id int primary key, sales int); {"ObTableStmt":{"partition_fun_expr":[], "range_values_exprs":[], "list_values_exprs":[], "index_partition_resolve_results":[], "part_type":8}, "stmt_type":20, "create_table_arg":{"if_not_exist":false, "schema":{"simple_table_schema":{"tenant_id":1, "database_id":18446744073709551615, "tablegroup_id":1099511627777, "table_id":18446744073709551615, "table_name":"targetTable", "replica_num":0, "previous_locality_str":"", "min_partition_id":0, "session_id":0, "index_type":0, "table_type":3, "data_table_id":0, "name_casemode":-1, "schema_version":0, "part_level":0, "part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":0}, "sub_part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":-1}, "locality_str":"", "zone_list":[], "primary_zone":"", "part_num":0, "subpart_num":0, "partition_num":1, "subpartition_num":0, "partition_array":[{"tenant_id":18446744073709551615, "table_id":18446744073709551615, "part_id":0, "name":"p0", "high_bound_val":, "list_row_values":[], "part_idx":0}], "subpartition_array":null, "index_status":1}, "max_used_column_id":17, "max_used_constraint_id":0, "sess_active_time":0, "rowkey_column_num":1, "index_column_num":0, "rowkey_split_pos":0, "block_size":16384, "is_use_bloomfilter":false, "progressive_merge_num":0, "tablet_size":134217728, "pctfree":10, "load_type":0, "index_using_type":0, "def_type":1, "charset_type":2, "collation_type":45, "create_mem_version":0, "index_status":1, "partition_status":0, "code_version":1, "last_modified_frozen_version":0, "comment":"", "pk_comment":"", "create_host":"", "zone_replica_attr_array":[], "region_replica_num_array":[], "tablegroup_name":"", "compress_func_name":"zstd_1.0", "expire_info":"", "view_schema":{"view_definition":"", "check_option":"none", "is_updatable":"false", "is_materialized":"false"}, "autoinc_column_id":0, "auto_increment":1, "read_only":false, "primary_zone_array":[], "index_tid_array":[], "mv_tid_array":[], "base_table_ids":[], "rowkey_info":{"columns":[{"length":0, "column_id":16, "type":{"type":"INT", "collation":"binary", "coercibility":"NUMERIC"}, "order":0, "fulltext_flag":false}], "capacity":1}, "partition_key_info":{"columns":null, "capacity":0}}, "index_arg_list":[], "constraint_list":[], "db_name":"merge_db", "last_replay_log_id":0, "foreign_key_arg_list":[]}, "index_arg_list":[]} *************** Case 7 *************** merge into targetTable using sourceTable on (targetTable.id = sourceTable.id) when matched then update set targetTable.sales = sourceTable.sales; { "stmt_type":7, "table": [ { "table_id":1099511677879, "table_name":"targetTable", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":1099511677877, "table_name":"sourceTable", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":16, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":1099511677877, "column_id":16, "database_name":"merge_db", "table_name":"sourceTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":16, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":1099511677879, "table_name":"targetTable", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":17, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":1099511677877, "column_id":17, "database_name":"merge_db", "table_name":"sourceTable", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 8 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id) when matched then update set t1.sales = t2.sales; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 9 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id and t1.id != t2.sales) when matched then update set t1.sales = t2.sales; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] }, { "item_type":"T_OP_NE", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 10 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id or t1.id != abs(t2.sales)) when matched then update set t1.sales = t2.sales; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_OR", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_OR", "CNT_COLUMN", "CNT_FUNC", "CNT_OR" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] }, { "item_type":"T_OP_NE", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_OP_ABS", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"abs", "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } ] } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 11 *************** merge into targetTable using sourceTable_2 on (id = id2 or id != abs(sales2)) when matched then update set sales = sales2; { "stmt_type":7, "table": [ { "table_id":1099511677879, "table_name":"targetTable", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":1099511677878, "table_name":"sourceTable_2", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677878, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_OR", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_OR", "CNT_COLUMN", "CNT_FUNC", "CNT_OR" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":16, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":1099511677878, "column_id":16, "database_name":"merge_db", "table_name":"sourceTable_2", "synonym_name":"", "column_name":"id2", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] }, { "item_type":"T_OP_NE", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":16, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_OP_ABS", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"abs", "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":1099511677878, "column_id":17, "database_name":"merge_db", "table_name":"sourceTable_2", "synonym_name":"", "column_name":"sales2", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } ] } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":16, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":1099511677879, "table_name":"targetTable", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":17, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":1099511677878, "column_id":17, "database_name":"merge_db", "table_name":"sourceTable_2", "synonym_name":"", "column_name":"sales2", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677878, "target_table_id":1099511677879 } *************** Case 12 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id and t1.id != t2.sales) when matched then update set t1.sales = t2.sales; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] }, { "item_type":"T_OP_NE", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 13 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id and t1.id != t2.sales) when matched then update set t1.sales = t2.sales where t1.sales > 1; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] }, { "item_type":"T_OP_NE", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ { "item_type":"T_OP_GT", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_RANGE_COND" ], "rel_id": [ 1 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":1 } } ] } ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 14 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id and t1.id != t2.sales) when matched then update set t1.sales = t2.sales where t1.sales > 88 delete where t1.id < 99; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] }, { "item_type":"T_OP_NE", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ { "item_type":"T_OP_GT", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_RANGE_COND" ], "rel_id": [ 1 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":2, "precision":2, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":88 } } ] } ], "insert condition": [ ], "delete condition": [ { "item_type":"T_OP_LT", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_RANGE_COND" ], "rel_id": [ 1 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":2, "precision":2, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":99 } } ] } ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 15 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id) when not matched then insert(t1.id, t1.sales) values(t2.id, t2.sales) where t2.id > 0; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ { "item_type":"T_OP_GT", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_RANGE_COND" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":0 } } ] } ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "column_conv": [ { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":0 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] }, { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } ], "assign": [ ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 16 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id) when not matched then insert(t1.id, t1.sales) values(t2.id, t2.sales); { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "column_conv": [ { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":0 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] }, { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } ], "assign": [ ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 17 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id) when matched then update set t1.sales = t2.sales where t1.id = 999 delete where t1.sales =888 when not matched then insert(t1.id, t1.sales) values(t2.id, t2.sales) where t2.id = 777; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_SIMPLE_COND" ], "rel_id": [ 1 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":3, "precision":3, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":999 } } ] } ], "insert condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_SIMPLE_COND" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":3, "precision":3, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":777 } } ] } ], "delete condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_SIMPLE_COND" ], "rel_id": [ 1 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":3, "precision":3, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":888 } } ] } ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "column_conv": [ { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":0 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] }, { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } ], "assign": [ { "table": { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 18 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id) when not matched then insert(t1.id, t1.sales) values(99, 88); { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":2, "precision":2, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":99 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":2, "precision":2, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":88 } } ], "column_conv": [ { "item_type":"T_QUESTIONMARK", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM", "IS_DYNAMIC_PARAM" ], "rel_id": [ ], "value": { "UNKNOWN":0 } }, { "item_type":"T_QUESTIONMARK", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM", "IS_DYNAMIC_PARAM" ], "rel_id": [ ], "value": { "UNKNOWN":1 } } ], "assign": [ ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 19 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id and t1.id != t2.sales) when matched then update set t1.sales = 100; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] }, { "item_type":"T_OP_NE", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_QUESTIONMARK", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM", "IS_DYNAMIC_PARAM" ], "rel_id": [ ], "value": { "UNKNOWN":0 } } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 20 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id) when not matched then insert(t1.id) values(99); { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":2, "precision":2, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":99 } } ], "column_conv": [ { "item_type":"T_QUESTIONMARK", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM", "IS_DYNAMIC_PARAM" ], "rel_id": [ ], "value": { "UNKNOWN":0 } }, { "item_type":"T_QUESTIONMARK", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM", "IS_DYNAMIC_PARAM" ], "rel_id": [ ], "value": { "UNKNOWN":1 } } ], "assign": [ ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 21 *************** merge into targetTable t1 using sourceTable t2 on (t1.id = t2.id) when not matched then insert (t1.id, t1.sales) values(99, 88); { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"sourceTable", "alias_name":"t2", "synonym_name":"", "table_type":1, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"merge_db", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":2, "precision":2, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":99 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":2, "precision":2, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":88 } } ], "column_conv": [ { "item_type":"T_QUESTIONMARK", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM", "IS_DYNAMIC_PARAM" ], "rel_id": [ ], "value": { "UNKNOWN":0 } }, { "item_type":"T_QUESTIONMARK", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM", "IS_DYNAMIC_PARAM" ], "rel_id": [ ], "value": { "UNKNOWN":1 } } ], "assign": [ ], "source_table_id":1099511677877, "target_table_id":1099511677879 } *************** Case 22 *************** create table target1(id int, c2 int, c3 int, primary key(id, c2)); {"ObTableStmt":{"partition_fun_expr":[], "range_values_exprs":[], "list_values_exprs":[], "index_partition_resolve_results":[], "part_type":8}, "stmt_type":20, "create_table_arg":{"if_not_exist":false, "schema":{"simple_table_schema":{"tenant_id":1, "database_id":18446744073709551615, "tablegroup_id":1099511627777, "table_id":18446744073709551615, "table_name":"target1", "replica_num":0, "previous_locality_str":"", "min_partition_id":0, "session_id":0, "index_type":0, "table_type":3, "data_table_id":0, "name_casemode":-1, "schema_version":0, "part_level":0, "part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":0}, "sub_part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":-1}, "locality_str":"", "zone_list":[], "primary_zone":"", "part_num":0, "subpart_num":0, "partition_num":1, "subpartition_num":0, "partition_array":[{"tenant_id":18446744073709551615, "table_id":18446744073709551615, "part_id":0, "name":"p0", "high_bound_val":, "list_row_values":[], "part_idx":0}], "subpartition_array":null, "index_status":1}, "max_used_column_id":18, "max_used_constraint_id":0, "sess_active_time":0, "rowkey_column_num":2, "index_column_num":0, "rowkey_split_pos":0, "block_size":16384, "is_use_bloomfilter":false, "progressive_merge_num":0, "tablet_size":134217728, "pctfree":10, "load_type":0, "index_using_type":0, "def_type":1, "charset_type":2, "collation_type":45, "create_mem_version":0, "index_status":1, "partition_status":0, "code_version":1, "last_modified_frozen_version":0, "comment":"", "pk_comment":"", "create_host":"", "zone_replica_attr_array":[], "region_replica_num_array":[], "tablegroup_name":"", "compress_func_name":"zstd_1.0", "expire_info":"", "view_schema":{"view_definition":"", "check_option":"none", "is_updatable":"false", "is_materialized":"false"}, "autoinc_column_id":0, "auto_increment":1, "read_only":false, "primary_zone_array":[], "index_tid_array":[], "mv_tid_array":[], "base_table_ids":[], "rowkey_info":{"columns":[{"length":0, "column_id":16, "type":{"type":"INT", "collation":"binary", "coercibility":"NUMERIC"}, "order":0, "fulltext_flag":false}, {"length":0, "column_id":17, "type":{"type":"INT", "collation":"binary", "coercibility":"NUMERIC"}, "order":0, "fulltext_flag":false}], "capacity":2}, "partition_key_info":{"columns":null, "capacity":0}}, "index_arg_list":[], "constraint_list":[], "db_name":"merge_db", "last_replay_log_id":0, "foreign_key_arg_list":[]}, "index_arg_list":[]} *************** Case 23 *************** create table target2(id int, c2 int); {"ObTableStmt":{"partition_fun_expr":[], "range_values_exprs":[], "list_values_exprs":[], "index_partition_resolve_results":[], "part_type":8}, "stmt_type":20, "create_table_arg":{"if_not_exist":false, "schema":{"simple_table_schema":{"tenant_id":1, "database_id":18446744073709551615, "tablegroup_id":1099511627777, "table_id":18446744073709551615, "table_name":"target2", "replica_num":0, "previous_locality_str":"", "min_partition_id":0, "session_id":0, "index_type":0, "table_type":3, "data_table_id":0, "name_casemode":-1, "schema_version":0, "part_level":0, "part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":0}, "sub_part_option":{"part_func_type":0, "part_func_expr":"", "part_num":1, "partition_cnt_within_partition_table":-1, "max_used_part_id":-1}, "locality_str":"", "zone_list":[], "primary_zone":"", "part_num":0, "subpart_num":0, "partition_num":1, "subpartition_num":0, "partition_array":[{"tenant_id":18446744073709551615, "table_id":18446744073709551615, "part_id":0, "name":"p0", "high_bound_val":, "list_row_values":[], "part_idx":0}], "subpartition_array":null, "index_status":1}, "max_used_column_id":17, "max_used_constraint_id":0, "sess_active_time":0, "rowkey_column_num":3, "index_column_num":0, "rowkey_split_pos":0, "block_size":16384, "is_use_bloomfilter":false, "progressive_merge_num":0, "tablet_size":134217728, "pctfree":10, "load_type":0, "index_using_type":0, "def_type":1, "charset_type":2, "collation_type":45, "create_mem_version":0, "index_status":1, "partition_status":0, "code_version":1, "last_modified_frozen_version":0, "comment":"", "pk_comment":"", "create_host":"", "zone_replica_attr_array":[], "region_replica_num_array":[], "tablegroup_name":"", "compress_func_name":"zstd_1.0", "expire_info":"", "view_schema":{"view_definition":"", "check_option":"none", "is_updatable":"false", "is_materialized":"false"}, "autoinc_column_id":0, "auto_increment":1, "read_only":false, "primary_zone_array":[], "index_tid_array":[], "mv_tid_array":[], "base_table_ids":[], "rowkey_info":{"columns":[{"length":0, "column_id":1, "type":{"type":"BIGINT UNSIGNED", "collation":"binary", "coercibility":"NUMERIC"}, "order":0, "fulltext_flag":false}, {"length":0, "column_id":4, "type":{"type":"BIGINT UNSIGNED", "collation":"binary", "coercibility":"NUMERIC"}, "order":0, "fulltext_flag":false}, {"length":0, "column_id":5, "type":{"type":"BIGINT UNSIGNED", "collation":"binary", "coercibility":"NUMERIC"}, "order":0, "fulltext_flag":false}], "capacity":3}, "partition_key_info":{"columns":null, "capacity":0}}, "index_arg_list":[], "constraint_list":[], "db_name":"merge_db", "last_replay_log_id":0, "foreign_key_arg_list":[]}, "index_arg_list":[]} *************** Case 24 *************** merge into target1 using sourceTable on (target1.id = sourceTable.id) when matched then update set target1.c2 = sourceTable.sales; { "stmt_type":7, "table": [ { "table_id":1099511677880, "table_name":"target1", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677880, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":1099511677877, "table_name":"sourceTable", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677880, "column_id":16, "database_name":"merge_db", "table_name":"target1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":1099511677877, "column_id":16, "database_name":"merge_db", "table_name":"sourceTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677880, "column_id":16, "database_name":"merge_db", "table_name":"target1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677880, "column_id":17, "database_name":"merge_db", "table_name":"target1", "synonym_name":"", "column_name":"c2", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":1099511677880, "table_name":"target1", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677880, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677880, "column_id":17, "database_name":"merge_db", "table_name":"target1", "synonym_name":"", "column_name":"c2", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":0 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":1099511677877, "column_id":17, "database_name":"merge_db", "table_name":"sourceTable", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677880 } *************** Case 25 *************** merge into target2 using sourceTable on (target2.id = sourceTable.id) when matched then update set target2.c2 = sourceTable.sales; { "stmt_type":7, "table": [ { "table_id":1099511677881, "table_name":"target2", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677881, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":1099511677877, "table_name":"sourceTable", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677877, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677881, "column_id":16, "database_name":"merge_db", "table_name":"target2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":1099511677877, "column_id":16, "database_name":"merge_db", "table_name":"sourceTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"BIGINT UNSIGNED", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":16899, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677881, "column_id":1, "database_name":"merge_db", "table_name":"target2", "synonym_name":"", "column_name":"__pk_increment", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"BIGINT UNSIGNED", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677881, "column_id":4, "database_name":"merge_db", "table_name":"target2", "synonym_name":"", "column_name":"__pk_cluster_id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"BIGINT UNSIGNED", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677881, "column_id":5, "database_name":"merge_db", "table_name":"target2", "synonym_name":"", "column_name":"__pk_partition_id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":1099511677881, "table_name":"target2", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677881, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677881, "column_id":17, "database_name":"merge_db", "table_name":"target2", "synonym_name":"", "column_name":"c2", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":1099511677877, "column_id":17, "database_name":"merge_db", "table_name":"sourceTable", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":1099511677877, "target_table_id":1099511677881 } *************** Case 26 *************** merge into targetTable using (select * from target1 ) sourceTable on (targetTable.id = sourceTable.id) when matched then update set targetTable.sales = sourceTable.c3; { "stmt_type":7, "table": [ { "table_id":1099511677879, "table_name":"targetTable", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-2, "table_name":"sourceTable", "alias_name":"sourceTable", "synonym_name":"", "table_type":2, "ref_id":-1, "database_name":"", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":16, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-2, "column_id":16, "database_name":"", "table_name":"sourceTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ ], "insert condition": [ ], "delete condition": [ ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":16, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ ], "column_conv": [ ], "assign": [ { "table": { "table_id":1099511677879, "table_name":"targetTable", "alias_name":"", "synonym_name":"", "table_type":0, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":1099511677879, "column_id":17, "database_name":"merge_db", "table_name":"targetTable", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-2, "column_id":18, "database_name":"", "table_name":"sourceTable", "synonym_name":"", "column_name":"c3", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":1, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":-1, "target_table_id":1099511677879 } *************** Case 27 *************** merge into targetTable t1 using (select * from sourceTable) t2 on (t1.id = t2.id) when matched then update set t1.sales = t2.sales where t1.id = 999 delete where t1.sales =888 when not matched then insert(t1.id, t1.sales) values(t2.id, t2.sales) where t2.id = 777; { "stmt_type":7, "table": [ { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, { "table_id":-3, "table_name":"t2", "alias_name":"t2", "synonym_name":"", "table_type":2, "ref_id":-1, "database_name":"", "for_update":false, "wait":-1, "mock_id":-1 } ], "match condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_COLUMN", "IS_JOIN_COND" ], "rel_id": [ 1, 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false } ] } ], "update condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_SIMPLE_COND" ], "rel_id": [ 1 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":3, "precision":3, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":999 } } ] } ], "insert condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":1, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_SIMPLE_COND" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":3, "precision":3, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":777 } } ] } ], "delete condition": [ { "item_type":"T_OP_EQ", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":1, "scale":0 }, "flag":0, "calc_type": { "type":"DECIMAL", "collation":"binary", "coercibility":"NUMERIC" } }, "expr_info": [ "CNT_CONST", "CNT_COLUMN", "IS_SIMPLE_COND" ], "rel_id": [ 1 ], "expr_levels": [ 0 ], "children": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":3, "precision":3, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":888 } } ] } ], "row key": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":16, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false } ], "value": [ { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "column_conv": [ { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":0 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":16387, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":16, "database_name":"", "table_name":"t2", "synonym_name":"", "column_name":"id", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] }, { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } ], "assign": [ { "table": { "table_id":-2, "table_name":"targetTable", "alias_name":"t1", "synonym_name":"", "table_type":1, "ref_id":1099511677879, "database_name":"merge_db", "for_update":false, "wait":-1, "mock_id":-1 }, "assign": [ { "column": { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 1 ], "table_id":-2, "column_id":17, "database_name":"merge_db", "table_name":"t1", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":3, "enum_set_values": [ ], "is_lob_column":false }, "expression": { "item_type":"T_FUN_COLUMN_CONV", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"IMPLICIT" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_FUNC", "CNT_CONST", "CNT_COLUMN", "CNT_FUNC" ], "rel_id": [ 2 ], "expr_levels": [ 0 ], "func":"column_conv", "children": [ { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":4 } }, { "item_type":"T_INT32", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "INT":63 } }, { "item_type":"T_INT", "result_type": { "meta": { "type":"BIGINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "BIGINT":51539607551 } }, { "item_type":"T_TINYINT", "result_type": { "meta": { "type":"TINYINT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":-1, "scale":-1 }, "flag":1, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_CONST", "CNT_CONST" ], "rel_id": [ ], "value": { "TINYINT":1 } }, { "item_type":"T_REF_COLUMN", "result_type": { "meta": { "type":"INT", "collation":"binary", "coercibility":"NUMERIC" }, "accuracy": { "length":-1, "precision":11, "scale":0 }, "flag":0, "calc_type": { "type":"NULL", "collation":"invalid_type", "coercibility":"INVALID" } }, "expr_info": [ "IS_COLUMN", "CNT_COLUMN" ], "rel_id": [ 2 ], "table_id":-3, "column_id":17, "database_name":"", "table_name":"t2", "synonym_name":"", "column_name":"sales", "expr_level":0, "expr_levels": [ 0 ], "column_flags":0, "is_explicited_referece":true, "explicited_ref_count":2, "enum_set_values": [ ], "is_lob_column":false } ], "enum_set_values": [ ] } } ], "is_update_part_key":false, "subquery_expr":null } ], "source_table_id":-1, "target_table_id":1099511677879 }