Bug #116611 user defined histogram import error for bigint data

Submitted: 11 Nov 2024 11:20 Modified: 22 Apr 20:22
Reporter: tianfeng li (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.4.3, 8.0.40 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[11 Nov 2024 11:20] tianfeng li
Description:
The histogram generated by `analyze table update histogram on col`, should be accepted by `analyze table update histogram on col using data ""`.
But when a histogram built on a bigint column, which contains the number bigger than uint32, it can never be wrote back through user-defined histogram interface.

How to repeat:

```
CREATE TABLE tbl_bigint(col1 BIGINT);
INSERT INTO tbl_bigint VALUES (100000000000);
analyze table tbl_bigint udpate histogram on col1;
select * from information_schema.COLUMN_STATISTICS where table_name="tbl_bigint";
# update using data obtained above.
analyze table tbl_bigint update histogram on col1 using data '...';

```

For example,
```
CREATE TABLE tbl_bigint(col1 BIGINT);
INSERT INTO tbl_bigint VALUES (100000000000);
analyze table tbl_bigint udpate histogram on col1 using data '{"buckets": [[100000000000, 1.0]], "data-type": "int", "auto-update": false, "null-values": 0.0, "collation-id": 8, "last-updated": "2024-11-11 10:32:21.166646", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 100}';
```

An error occurs : `Out of range value for column at ..`
[11 Nov 2024 11:21] tianfeng li
Contributed by tencent: accept Json_uint for longlong type in extract_json_dom_value()

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-accept-Json_uint-for-longlong-type-in-extract_json_d.patch (application/octet-stream, text), 3.92 KiB.

[11 Nov 2024 11:53] MySQL Verification Team
Hello tianfeng li,

Thank you for the report and contribution.

regards,
Umesh
[22 Apr 20:22] Edward Gilmore
Posted by developer:
 
Added the following note to the MySQL Server 8.4.9 and 9.7.0 release notes:
		

A histogram generated by ANALYZE TABLE UPDATE HISTOGRAM ON col_name was
not be accepted by ANALYZE TABLE UPDATE HISTOGRAM ON col_name USING DATA... 
if the histogram was built on a bigint column with values outside the
uint32 range.
Our thanks to Tianfeng Li for the contribution.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.