Bug #119621 query on partitioned table report "the JSON binary value contains invalid data"

• Submitted: 5 Jan 11:11
• Modified: 24 Feb 12:06
• Reporter: Zhang zhang
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S2 (Serious)
• Version: 8.0.43 / 8.4.7
• OS: Any
• CPU Architecture: Any

[5 Jan 11:11] Zhang zhang

Description:
After upgrading from 8.0.32 to 8.0.43, a SQL query that was running normally before started throwing an error:

The JSON binary value contains invalid data

How to repeat:
create table t1 (
  id int not null,
  json_col json,
  KEY `i1` (`id`,(cast(json_extract(`json_col`,_utf8mb4'$[*]') as char(100) array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PARTITION BY KEY (id) PARTITIONS 4;

insert into t1 values (1, '["a1"]'), (2, '["a2"]'), (2, '["a3"]');

select * from t1 where id = 2 and ((json_contains(json_col -> '$[*]',  ('"a2"'))));

Suggested fix:
The stacktrace of the error:

```
Field_json::val_json(const Field_json * const this, Json_wrapper * wr) (/data00/home/zhangpengcheng.1/mysql80/sql/field.cc:8346)
Field_typed_array::key_cmp(const Field_typed_array * const this, const uchar * key_ptr, uint key_length) (/data00/home/zhangpengcheng.1/mysql80/sql/field.cc:10571)
key_cmp(KEY_PART_INFO * key_part, const uchar * key, uint key_length, bool is_reverse_multi_valued_index_scan) (/data00/home/zhangpengcheng.1/mysql80/sql/key.cc:478)
handler::compare_key(handler * const this, key_range * range) (/data00/home/zhangpengcheng.1/mysql80/sql/handler.cc:8101)
ha_innopart::read_range_first_in_part(ha_innopart * const this, uint part, uchar * record) (/data00/home/zhangpengcheng.1/mysql80/storage/innobase/handler/ha_innopart.cc:1981)
Partition_helper::handle_unordered_scan_next_partition(Partition_helper * const this, uchar * buf) (/data00/home/zhangpengcheng.1/mysql80/sql/partitioning/partition_handler.cc:2391)
Partition_helper::common_index_read(Partition_helper * const this, uchar * buf, bool have_start_key) (/data00/home/zhangpengcheng.1/mysql80/sql/partitioning/partition_handler.cc:1915)
Partition_helper::ph_read_range_first(Partition_helper * const this, const key_range * start_key, const key_range * end_key, bool eq_range_arg, bool sorted) (/data00/home/zhangpengcheng.1/mysql80/sql/partitioning/partition_handler.cc:2213)
ha_innopart::read_range_first(ha_innopart * const this, const key_range * start_key, const key_range * end_key, bool eq_range_arg, bool sorted) (/data00/home/zhangpengcheng.1/mysql80/storage/innobase/handler/ha_innopart.h:482)
handler::multi_range_read_next(handler * const this, char ** range_info) (/data00/home/zhangpengcheng.1/mysql80/sql/handler.cc:7091)
DsMrr_impl::dsmrr_next(DsMrr_impl * const this, char ** range_info) (/data00/home/zhangpengcheng.1/mysql80/sql/handler.cc:7442)
ha_innobase::multi_range_read_next(ha_innobase * const this, char ** range_info) (/data00/home/zhangpengcheng.1/mysql80/storage/innobase/handler/ha_innodb.cc:25110)
handler::ha_multi_range_read_next(handler * const this, char ** range_info) (/data00/home/zhangpengcheng.1/mysql80/sql/handler.cc:7029)
IndexRangeScanIterator::Read(IndexRangeScanIterator * const this) (/data00/home/zhangpengcheng.1/mysql80/sql/range_optimizer/index_range_scan.cc:373)
FilterIterator::Read(FilterIterator * const this) (/data00/home/zhangpengcheng.1/mysql80/sql/iterators/composite_iterators.cc:79)
Query_expression::ExecuteIteratorQuery(Query_expression * const this, THD * thd) (/data00/home/zhangpengcheng.1/mysql80/sql/sql_union.cc:1784)
Query_expression::execute(Query_expression * const this, THD * thd) (/data00/home/zhangpengcheng.1/mysql80/sql/sql_union.cc:1837)
Sql_cmd_dml::execute_inner(Sql_cmd_dml * const this, THD * thd) (/data00/home/zhangpengcheng.1/mysql80/sql/sql_select.cc:1055)
Sql_cmd_dml::execute(Sql_cmd_dml * const this, THD * thd) (/data00/home/zhangpengcheng.1/mysql80/sql/sql_select.cc:802)
mysql_execute_command(THD * thd, bool first_level) (/data00/home/zhangpengcheng.1/mysql80/sql/sql_parse.cc:5260)
```

In our debugging environment, we found in json_binary.cc:
Value parse_binary(const char *data, size_t len)
we got the value of data as "a2", len=2.

data[0] is used json type, which is 'a'=97, which is incorrect.

[5 Jan 15:05] Roy Lyseng

Thank you for the bug report.
Verified as described.

[24 Feb 11:47] Zhang zhang

The issue can be reproduced in version 8.4.7

[24 Feb 12:06] Zhang zhang

We tried a non-partitioned table, and the data/queries above worked well:

```
create table t2 (
  id int not null,
  json_col json,
  KEY `i1` (`id`,(cast(json_extract(`json_col`,_utf8mb4'$[*]') as char(100) array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t2 values (1, '["a1"]'), (2, '["a2"]'), (2, '["a3"]');

select * from t2 where id = 2 and ((json_contains(json_col -> '$[*]',  ('"a2"'))));
```

Comparing the code for partitioned and non-partitioned tables, the non-partitioned table code path contained these additional processing steps:

```
int handler::ha_index_read_map(uchar *buf, const uchar *key,
                               key_part_map keypart_map,
                               enum ha_rkey_function find_flag) {
  ...

  // Set status for the need to update generated fields
  m_update_generated_read_fields = table->has_gcol();

  MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, {
    result = index_read_map(buf, key, keypart_map, find_flag);
  })
  if (!result && m_update_generated_read_fields) {
    result = update_generated_read_fields(buf, table, active_index);
    m_update_generated_read_fields = false;
  }

  ...
}
```

If the `update_generated_read_fields()` processing is added to the `ha_innopart::read_range_first_in_part()` code, the query for the partitioned table above also returns normal results.