Bug #90570 Functions and/or view(s) in sys schema to work with JSON in column_statistics
Submitted: 23 Apr 2018 11:42 Modified: 8 Jul 2018 21:42
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: column_statistics, histogram, information_schema, json, sys

[23 Apr 2018 11:42] Valeriy Kravchuk
Description:
According to the manual (https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html), information_schema.column_statistcis table has HISTOGRAM column of type JSON. The structure of the document is complex enough, so explicit use of JSON functions is needed to get the details, like in this example from the manual:

ysql> SELECT
         TABLE_NAME, COLUMN_NAME,
         HISTOGRAM->>'$."data-type"' AS 'data-type',
         JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'
       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;

I'd prefer to stay relational and know nothing about JSONB while requesting details about column-level statistics for the relational tables, so I suggest to create view(s) (and maybe extra stored functions) to be able to get all the detail about the histogram data as columns directly avaiable in SQL without anyt JSON functions. 

We may also need a separate view for representing the details about the individual buckets in the histogram data for a given column. They may be even base64-encoded for string columns etc.

How to repeat:
Try to get the number of buckets in the histogram data, or specific value in the column that is present in 25% of rows, or find out when the histogram data were collected, in pure SQL.

Suggested fix:
Provide a set of views and/or stored functions to get information about the histogram data for the columns, so that one does not need to know JSON or code anything at client side to get all the histogram data.
[23 Apr 2018 11:43] Valeriy Kravchuk
Corrected synopsis.
[23 Apr 2018 11:44] Valeriy Kravchuk
Corrected synopsis, added tags
[8 Jul 2018 21:42] Jesper wisborg Krogh
Thank you for a reasonable feature request.