From 7832d043a4d95569a4bef084aedfd7bb80e936f6 Mon Sep 17 00:00:00 2001 From: casazhang Date: Tue, 21 Sep 2021 08:51:16 +0800 Subject: [PATCH] Bug#104040 ANALYZE TABLE UPDATE HISTOGRAM USING DATA Previously histograms are gathered by ANALYZE TABLE UPDATE HISTOGRAM (WL#8943). It samples user data, build histogram, then saves to persistent storage (WL#8706) in one command. However, sampling user data and building histogram are heavy operations which might press the server and impact user queries. Even with block sampling provided by WL#8777, such situation is not alleviated too much. As a workaround, gathering tasks might be properly scheduled. This patch goes one step further to avoid heavy operations totally on the current server, by providing a new syntax to directly write histogram data to the persistent storage: ANALYZE TABLE t UPDATE HISTOGRAM on c USING DATA 'json'; Then the histogram data could be obtained by heavy operations a separated server with the same copy of data, which is usually a replica. In this way, the gather command is disassembled for great flexibility. It also enables migrating histograms between servers. Note that histogram data is standard JSON while the persisted copy is MySQL binary JSON (WL#8132). Direct-writing is achieved by extracting and validating histogram data smartly then reusing the same storage code. In this process, real user data is not accessed, only the column definition is checked to get the value domain of endpoints. In other words, the histogram might be deviated from real data to any extent. The user is supposed to provide up-to-date histogram data. Since DD loading is required to be successful, this patch provides an internal mode to bypass histogram validation for DD loading path. Anyway, certain fixes are expected: Bug#97172 UNABLE TO OPEN TABLE WITH INCOMPATIBLE HISTOGRAM Bug#104108 CUMULATIVE FREQUENCY MAY GROW OVER 1.0 DUE TO FLOAT ERRORS --- mysql-test/include/store_histogram_and_check.inc | 9 + mysql-test/r/histograms.result | 11 + mysql-test/r/histograms_update_using_data.result | 832 +++++++++++++++++++++++ mysql-test/suite/innodb/r/histogram-debug.result | 10 +- mysql-test/t/histograms.test | 10 + mysql-test/t/histograms_update_using_data.test | 633 +++++++++++++++++ sql/dd/impl/types/column_statistics_impl.cc | 4 +- sql/histograms/equi_height.cc | 135 +++- sql/histograms/equi_height.h | 13 +- sql/histograms/histogram.cc | 555 +++++++++++++-- sql/histograms/histogram.h | 167 ++++- sql/histograms/singleton.cc | 113 ++- sql/histograms/singleton.h | 6 +- sql/json_dom.cc | 2 +- sql/json_dom.h | 2 +- sql/parse_tree_nodes.cc | 2 +- sql/parse_tree_nodes.h | 7 +- sql/parser_yystype.h | 6 +- sql/sql_admin.cc | 154 ++++- sql/sql_admin.h | 8 +- sql/sql_partition_admin.h | 2 +- sql/sql_yacc.yy | 40 +- 22 files changed, 2577 insertions(+), 144 deletions(-) create mode 100644 mysql-test/include/store_histogram_and_check.inc create mode 100644 mysql-test/r/histograms_update_using_data.result create mode 100644 mysql-test/t/histograms_update_using_data.test diff --git a/mysql-test/include/store_histogram_and_check.inc b/mysql-test/include/store_histogram_and_check.inc new file mode 100644 index 0000000..845631a --- /dev/null +++ b/mysql-test/include/store_histogram_and_check.inc @@ -0,0 +1,9 @@ +eval ANALYZE TABLE $tbl_name UPDATE HISTOGRAM ON $col_name WITH $buckets BUCKETS; +let $result1= `SELECT JSON_REMOVE(HISTOGRAM, '$."last-updated"') AS HISTOGRAM FROM information_schema.COLUMN_STATISTICS WHERE TABLE_NAME="$tbl_name" AND COLUMN_NAME="$col_name";`; +eval ANALYZE TABLE $tbl_name UPDATE HISTOGRAM ON $col_name USING DATA '$json_data'; +let $result2= `SELECT JSON_REMOVE(HISTOGRAM, '$."last-updated"') AS HISTOGRAM FROM information_schema.COLUMN_STATISTICS WHERE TABLE_NAME="$tbl_name" AND COLUMN_NAME="$col_name";`; +#--echo $json_data +#--echo $result1 +#--echo $result2 +assert($json_data == $result1); +assert($json_data == $result2); diff --git a/mysql-test/r/histograms.result b/mysql-test/r/histograms.result index 96de5f6..633f151 100644 --- a/mysql-test/r/histograms.result +++ b/mysql-test/r/histograms.result @@ -2043,6 +2043,17 @@ AND OUTR . col_varchar_key IS NULL HAVING x <= 3 ORDER BY OUTR . pk , OUTR . pk; x +# +# Bug#104108 CUMULATIVE FREQUENCY MAY GROW OVER 1.0 DUE TO FLOAT ERRORS +# +ANALYZE TABLE t2 UPDATE HISTOGRAM ON col_datetime WITH 100 BUCKETS; +Table Op Msg_type Msg_text +test.t2 histogram status Histogram statistics created for column 'col_datetime'. +SELECT schema_name, table_name, column_name, +JSON_REMOVE(histogram, '$."last-updated"') +FROM information_schema.COLUMN_STATISTICS WHERE table_name = 't2' and column_name = 'col_datetime'; +SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') +test t2 col_datetime {"buckets": [["1900-01-01 00:00:00.000000", 0.3], ["2001-01-20 12:47:23.000000", 0.35], ["2001-07-25 08:40:24.000000", 0.4], ["2001-10-22 11:13:24.000000", 0.45], ["2002-08-25 20:35:06.000000", 0.5], ["2002-12-08 11:34:58.000000", 0.55], ["2003-03-12 02:00:34.000000", 0.6], ["2003-12-04 11:14:26.000000", 0.65], ["2005-08-15 00:00:00.000000", 0.7], ["2006-09-11 18:25:21.000000", 0.75], ["2007-04-10 12:16:04.000000", 0.8], ["2008-05-16 08:09:06.000000", 0.85], ["2008-07-02 00:00:00.000000", 0.9], ["2009-06-07 13:48:58.000000", 0.95], ["2009-11-07 00:00:00.000000", 1.0]], "data-type": "datetime", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 100} DROP TABLE t1, t2; SET @@SESSION.sql_mode=DEFAULT; # diff --git a/mysql-test/r/histograms_update_using_data.result b/mysql-test/r/histograms_update_using_data.result new file mode 100644 index 0000000..8b7bf04 --- /dev/null +++ b/mysql-test/r/histograms_update_using_data.result @@ -0,0 +1,832 @@ +# +# singleton histogram +# +CREATE TABLE tbl_int(col1 INT); +INSERT INTO tbl_int VALUES (4), (52), (12), (12), (4), (23), (NULL), (NULL); +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 WITH 4 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_int histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram status Histogram statistics created for column 'col1'. +# +# check json format +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75], "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error JSON format error. +# +# check if data is a json object +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA ''; +Table Op Msg_type Msg_text +test.tbl_int histogram Error JSON format error. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA 'a'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error JSON format error. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '1'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error JSON data is not an object +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA 'null'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error JSON data is not an object +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '[]'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error JSON data is not an object +# +# check attribute data-type +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Missing attribute at '$."data-type"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": 1, "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$."data-type"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "blob", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Unsupported data type at '$."data-type"'. +# +# check attribute null-values [0.0, 1.0] +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Missing attribute at '$."null-values"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": "0.25", "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$."null-values"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": -0.01, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The frequency must be greater than or equal to 0 and less than or equal to 1 at '$."null-values"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 1.01, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The frequency must be greater than or equal to 0 and less than or equal to 1 at '$."null-values"'. +# +# check attribute collation-id +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Missing attribute at '$."collation-id"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": "utf8", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$."collation-id"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 2048, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The charest id does not exists at '$."collation-id"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": -1, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The charest id does not exists at '$."collation-id"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 9300000000000000000, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The charest id does not exists at '$."collation-id"'. +# +# check attribute sampling-rate [0.0, 1.0] +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Missing attribute at '$."sampling-rate"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": "1.0", "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$."sampling-rate"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": -0.1, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The sampling rate must be greater than or equal to 0 and less than or equal to 1 at '$."sampling-rate"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.1, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The sampling rate must be greater than or equal to 0 and less than or equal to 1 at '$."sampling-rate"'. +# +# check attribute histogram-type +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Missing attribute at '$."histogram-type"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": 1, "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$."histogram-type"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "single", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Unsupported histogram type at '$."histogram-type"'. +# +# check attribute number-of-buckets-specified [1, 1024] +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton"}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Missing attribute at '$."number-of-buckets-specified"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": "4"}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$."number-of-buckets-specified"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": -1}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The value of attribute number-of-buckets-specified must be an integer in the range from 1 to 1024 at '$."number-of-buckets-specified"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 1025}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The value of attribute number-of-buckets-specified must be an integer in the range from 1 to 1024 at '$."number-of-buckets-specified"'. +# +# check attribute buckets array +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Missing attribute at '$.buckets'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": "[[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]]", "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": null, "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets'. +# +# check bucket layout [v, cf] or none +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [4, 0.25], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets[0]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Two elements required for bucket at '$.buckets[0]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The sum of the null values fraction and the cumulative frequency of the last bucket should be 1 or 0.' +# +# check if real number of buckets is less than specified +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The number of real buckets must be less than or equal to the number specified by attribute number-of-buckets-specified. +# +# check attribute cumulative frequency [0.0, 1.0] +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, "0.5"], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets[1][1]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, -0.01], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The frequency must be greater than or equal to 0 and less than or equal to 1 at '$.buckets[0][1]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 1.1]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The frequency must be greater than or equal to 0 and less than or equal to 1 at '$.buckets[3][1]'. +# +# check if cumulative frequency sequence is in strict asc order +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.7], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The cumulative frequency must be greater than that of previous bucket at '$.buckets[2][1]'. +# +# check if total frequency is 1 or 0 +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.26, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The sum of the null values fraction and the cumulative frequency of the last bucket should be 1 or 0.' +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.24, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The sum of the null values fraction and the cumulative frequency of the last bucket should be 1 or 0.' +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram status Histogram statistics created for column 'col1'. +# +# singleton histogram, INT column +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23.0, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets[2][0]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [9300000000000000000, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Out of range value for column at '$.buckets[3][0]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [2200000000, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Out of range value for column at '$.buckets[3][0]'. +# +# check if value sequence is in strict asc order +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [32, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The value must be greater than that of previous bucket at '$.buckets[2][0]'. +DROP TABLE tbl_int; +# +# equal-height histogram +# +CREATE TABLE tbl_int(col1 INT); +INSERT INTO tbl_int VALUES (4), (52), (12), (12), (4), (23); +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 WITH 3 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_int histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram status Histogram statistics created for column 'col1'. +# +# check attribute data-type +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Missing attribute at '$."data-type"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": 3, "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$."data-type"'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "text", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Unsupported data type at '$."data-type"'. +# +# check attribute buckets array +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Missing attribute at '$.buckets'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": "[[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]]", "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": null, "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets'. +# +# check bucket layout [l, u, cf, d] or none +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [4, 4, 0.3333333333333333, 1], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets[0]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Four elements required for bucket at '$.buckets[0]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Four elements required for bucket at '$.buckets[1]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.4, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Equi-height histogram must have some buckets +# +# check if real number of buckets is less than specified +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 2}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The number of real buckets must be less than or equal to the number specified by attribute number-of-buckets-specified. +# +# check attribute cumulative frequency [0.0, 1.0] +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, "0.6666666666666666", 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets[1][2]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, -0.01, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.01, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The frequency must be greater than or equal to 0 and less than or equal to 1 at '$.buckets[0][2]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.01, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The frequency must be greater than or equal to 0 and less than or equal to 1 at '$.buckets[2][2]'. +# +# check if cumulative frequency sequence is in strict asc order +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.7333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The cumulative frequency must be greater than that of previous bucket at '$.buckets[1]'. +# +# check if total frequency is 1 +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.01, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The sum of the null values fraction and the cumulative frequency of the last bucket should be 1 or 0.' +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.01, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Equi-height histogram must have some buckets +# +# check if equi-height has some buckets +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Equi-height histogram must have some buckets +# +# check attribute num distinct +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1.0], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets[1][3]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 0], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The number of distinct value must be a positive integer at '$.buckets[1][3]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, -1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The number of distinct value must be a positive integer at '$.buckets[1][3]'. +# +# equal-height histogram, INT column +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, "12", 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Wrong attribute type at '$.buckets[1][1]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[9300000000000000000, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 9300000000000000000, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Out of range value for column at '$.buckets[2][1]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 2200000000, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Out of range value for column at '$.buckets[0][1]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 2200000000, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error Out of range value for column at '$.buckets[2][1]'. +# +# check if value sequence is in strict asc order +# +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [13, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The lower inclusive value of a bucket must be less than or equal to upper inclusive value at '$.buckets[1]'. +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 42, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_int histogram Error The lower inclusive value of a bucket must be greater than the upper inclusive value of previous bucket at '$.buckets[2]'. +DROP TABLE tbl_int; +# +# singleton histogram, BIGINT UNSIGNED column +# +CREATE TABLE tbl_uint(col1 BIGINT UNSIGNED); +INSERT INTO tbl_uint VALUES (4), (52), (12), (12), (4), (23), (NULL), (NULL); +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 WITH 4 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_uint histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23.0, 0.625], [52, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error Wrong attribute type at '$.buckets[2][0]'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [20000000000000000000, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error Wrong attribute type at '$.buckets[3][0]'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [-1, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error Out of range value for column at '$.buckets[3][0]'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [32, 0.5], [23, 0.625], [52, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error The value must be greater than that of previous bucket at '$.buckets[2][0]'. +DROP TABLE tbl_uint; +# +# equal-height histogram, BIGINT UNSIGNED column +# +CREATE TABLE tbl_uint(col1 BIGINT UNSIGNED); +INSERT INTO tbl_uint VALUES (4), (52), (12), (12), (4), (23), (NULL), (NULL); +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 WITH 3 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_uint histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [12, 12, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [12.0, 12, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error Wrong attribute type at '$.buckets[1][0]'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [12, "12", 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error Wrong attribute type at '$.buckets[1][1]'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[-1, 4, 0.25, 1], [12, 12, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [12, 12, 0.5, 1], [23, 20000000000000000000, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error Wrong attribute type at '$.buckets[2][1]'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [13, 12, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error The lower inclusive value of a bucket must be less than or equal to upper inclusive value at '$.buckets[1]'. +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [12, 42, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_uint histogram Error The lower inclusive value of a bucket must be greater than the upper inclusive value of previous bucket at '$.buckets[2]'. +DROP TABLE tbl_uint; +# +# singleton histogram, DOUBLE column +# +CREATE TABLE tbl_double(col1 DOUBLE); +INSERT INTO tbl_double VALUES (4.0), (52.0), (12.0), (12.0), (4.0), (23.0), (NULL), (NULL); +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 WITH 4 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_double histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 0.25], [12.0, 0.5], [23.0, 0.625], [52.0, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_double histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 0.25], [12.0, 0.5], ["23.0", 0.625], [52.0, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_double histogram Error Wrong attribute type at '$.buckets[2][0]'. +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[-1e330, 0.25], [12.0, 0.5], [23.0, 0.625], [52.0, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_double histogram Error JSON format error. +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 0.25], [12.0, 0.5], [23.0, 0.625], [1E330, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_double histogram Error JSON format error. +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 0.25], [32.0, 0.5], [23.0, 0.625], [52.0, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_double histogram Error The value must be greater than that of previous bucket at '$.buckets[2][0]'. +DROP TABLE tbl_double; +# +# equal-height histogram, DOUBLE column +# +CREATE TABLE tbl_double(col1 DOUBLE); +INSERT INTO tbl_double VALUES (4.0), (52.0), (12.0), (12.0), (4.0), (23.0), (NULL), (NULL); +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 WITH 3 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_double histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 4.0, 0.25, 1], [12.0, 12.0, 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_double histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4.0, 0.25, 1], [12.0, 12.0, 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_double histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 4.0, 0.25, 1], [12.0, "12.0", 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_double histogram Error Wrong attribute type at '$.buckets[1][1]'. +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 4.0, 0.25, 1], [13.0, 12.0, 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_double histogram Error The lower inclusive value of a bucket must be less than or equal to upper inclusive value at '$.buckets[1]'. +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 4.0, 0.25, 1], [12.0, 32.0, 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_double histogram Error The lower inclusive value of a bucket must be greater than the upper inclusive value of previous bucket at '$.buckets[2]'. +DROP TABLE tbl_double; +# +# singleton histogram, VARCHAR(8) column +# +CREATE TABLE tbl_string(col1 VARCHAR(8)); +INSERT INTO tbl_string VALUES ("Charles"), ("Mark"), ("Bill"), ("Bill"), ("Charles"), ("Vincent"), (NULL), (NULL); +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 WITH 4 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_string histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_string histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[12345, 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["QmlsbA==", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsTWFyVmluY2VuQ2hhcmxlbA==", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:Q2hhcmxlcw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error The value must be greater than that of previous bucket at '$.buckets[2][0]'. +DROP TABLE tbl_string; +# +# equal-height histogram, VARCHAR(8) column +# +CREATE TABLE tbl_string(col1 VARCHAR(8)); +INSERT INTO tbl_string VALUES ("Charles"), ("Mark"), ("Bill"), ("Bill"), ("Charles"), ("Vincent"), (NULL), (NULL); +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 WITH 3 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_string histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[12345, "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", 12345, 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Wrong attribute type at '$.buckets[0][1]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["QmlsbA==", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Value format error at '$.buckets[0][1]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "base64:type254:QmlsbA", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Value format error at '$.buckets[0][1]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsTWFyVmluY2VuQ2hhcmxlbA==", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "base64:type254:QmlsTWFyVmluY2VuQ2hhcmxlbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error Out of range value for column at '$.buckets[0][1]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:VmluY2VudA==", "base64:type254:TWFyaw==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error The lower inclusive value of a bucket must be less than or equal to upper inclusive value at '$.buckets[2]'. +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "base64:type254:TWFyaw==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_string histogram Error The lower inclusive value of a bucket must be greater than the upper inclusive value of previous bucket at '$.buckets[1]'. +DROP TABLE tbl_string; +# +# singleton histogram, DATE column +# +CREATE TABLE tbl_date(col1 DATE); +INSERT INTO tbl_date VALUES ("2018-03-21"), ("2017-02-06"), ("2017-02-10"), ("2017-02-10"), ("2018-03-21"), ("2018-02-12"), (NULL), (NULL); +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 WITH 4 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_date histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_date histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["0000-00-00", 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[20170206, 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 14:48:11", 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["14:48:11", 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", 0.125], ["2017-02-10", 0.375], ["2018-04-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error The value must be greater than that of previous bucket at '$.buckets[3][0]'. +DROP TABLE tbl_date; +# +# equal-height histogram, DATE column +# +CREATE TABLE tbl_date(col1 DATE); +INSERT INTO tbl_date VALUES ("2018-03-21"), ("2017-02-06"), ("2017-02-10"), ("2017-02-10"), ("2018-03-21"), ("2018-02-12"), (NULL), (NULL); +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 WITH 3 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_date histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["0000-00-00", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "0000-00-00", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Out of range value for column at '$.buckets[0][1]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[20170206, "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", 20170210, 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Wrong attribute type at '$.buckets[0][1]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 14:48:11", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12 14:48:11", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Out of range value for column at '$.buckets[1][1]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["14:48:11", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "2017-02-10", 0.375, 2], ["2018-02-12", "14:48:11", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error Value format error at '$.buckets[1][1]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-12", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error The lower inclusive value of a bucket must be less than or equal to upper inclusive value at '$.buckets[0]'. +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "2019-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_date histogram Error The lower inclusive value of a bucket must be greater than the upper inclusive value of previous bucket at '$.buckets[1]'. +DROP TABLE tbl_date; +# +# singleton histogram, TIME column +# +CREATE TABLE tbl_time(col1 TIME); +INSERT INTO tbl_time VALUES ("21:12:42"), ("16:22:23"), ("08:15:18"), ("08:15:18"), ("21:12:42"), ("07:04:18"), (NULL), (NULL); +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 WITH 4 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_time histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_time histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[170418, 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-10", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-10 07:04:18.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["-839:00:00.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["839:00:00.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Value format error at '$.buckets[3][0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["09:04:18.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error The value must be greater than that of previous bucket at '$.buckets[1][0]'. +DROP TABLE tbl_time; +# +# equal-height histogram, TIME column +# +CREATE TABLE tbl_time(col1 TIME); +INSERT INTO tbl_time VALUES ("21:12:42"), ("16:22:23"), ("08:15:18"), ("08:15:18"), ("21:12:42"), ("07:04:18"), (NULL), (NULL); +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 WITH 3 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_time histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[170418, "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", 181518, 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Wrong attribute type at '$.buckets[0][1]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", "2017-02-10", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Value format error at '$.buckets[0][1]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-10 07:04:18.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Out of range value for column at '$.buckets[0][1]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["-839:00:00.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "839:00:00.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error Value format error at '$.buckets[2][1]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["09:04:18.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error The lower inclusive value of a bucket must be less than or equal to upper inclusive value at '$.buckets[0]'. +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", "17:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_time histogram Error The lower inclusive value of a bucket must be greater than the upper inclusive value of previous bucket at '$.buckets[1]'. +DROP TABLE tbl_time; +# +# singleton histogram, DATETIME column +# +CREATE TABLE tbl_datetime(col1 DATETIME); +INSERT INTO tbl_datetime VALUES ("2018-03-21 21:12:42"), ("2017-02-06 16:22:23"), ("2017-02-10 08:15:18"), ("2017-02-10 08:15:18"), ("2018-03-21 21:12:42"), ("2018-02-12 07:04:18"), (NULL), (NULL); +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 WITH 4 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_datetime histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[20170216162223, 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["16:22:23.000000", 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["0000-00-00 00:00:00.000000", 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-16 16:22:23.000000", 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error The value must be greater than that of previous bucket at '$.buckets[1][0]'. +DROP TABLE tbl_datetime; +# +# equal-height histogram, DATETIME column +# +CREATE TABLE tbl_datetime(col1 DATETIME); +INSERT INTO tbl_datetime VALUES ("2018-03-21 21:12:42"), ("2017-02-06 16:22:23"), ("2017-02-10 08:15:18"), ("2017-02-10 08:15:18"), ("2018-03-21 21:12:42"), ("2018-02-12 07:04:18"), (NULL), (NULL); +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 WITH 3 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_datetime histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[20170206162223, "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", 20170210081518, 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error Wrong attribute type at '$.buckets[0][1]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["16:22:23.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", "08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error Value format error at '$.buckets[0][1]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["0000-00-00 00:00:00.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", "0000-00-00 00:00:00.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error Out of range value for column at '$.buckets[0][1]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-16 16:22:23.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error The lower inclusive value of a bucket must be less than or equal to upper inclusive value at '$.buckets[0]'. +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", "2019-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_datetime histogram Error The lower inclusive value of a bucket must be greater than the upper inclusive value of previous bucket at '$.buckets[1]'. +DROP TABLE tbl_datetime; +# +# singleton histogram, DECIMAL(5,2) column +# +CREATE TABLE tbl_decimal(col1 DECIMAL(5,2)); +INSERT INTO tbl_decimal VALUES (4.12), (52.12), (12.12), (12.12), (4.12), (23.12), (NULL), (NULL); +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 WITH 4 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_decimal histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.12, 0.25], [12.12, 0.5], [23.12, 0.625], [52.12, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], ["12.00", 0.5], [23.00, 0.625], [52.00, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Wrong attribute type at '$.buckets[1][0]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], [12.00, 0.5], [23.00, 0.625], [9300000000000000000, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Out of range value for column at '$.buckets[3][0]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], [12.00, 0.5], [23.00, 0.625], [1e100, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Value format error at '$.buckets[3][0]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], [12.00, 0.5], [23.00, 0.625], [5555.00, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Out of range value for column at '$.buckets[3][0]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], [32.00, 0.5], [23.00, 0.625], [52.00, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error The value must be greater than that of previous bucket at '$.buckets[2][0]'. +DROP TABLE tbl_decimal; +# +# equal-height histogram, DECIMAL(5,2) column +# +CREATE TABLE tbl_decimal(col1 DECIMAL(5,2)); +INSERT INTO tbl_decimal VALUES (4.12), (52.12), (12.12), (12.12), (4.12), (23.12), (NULL), (NULL); +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 WITH 3 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_decimal histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.12, 4.12, 0.25, 1], [12.12, 12.12, 0.5, 1], [23.12, 52.12, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram status Histogram statistics created for column 'col1'. + +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["4.00", 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Wrong attribute type at '$.buckets[0][0]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [12.00, "12.00", 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Wrong attribute type at '$.buckets[1][1]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[-1e100, 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Value format error at '$.buckets[0][0]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 1e100, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Value format error at '$.buckets[2][1]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[-1000.00, 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Out of range value for column at '$.buckets[0][0]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 1000.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error Out of range value for column at '$.buckets[2][1]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [13.00, 12.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error The lower inclusive value of a bucket must be less than or equal to upper inclusive value at '$.buckets[1]'. +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [12.00, 32.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +Table Op Msg_type Msg_text +test.tbl_decimal histogram Error The lower inclusive value of a bucket must be greater than the upper inclusive value of previous bucket at '$.buckets[2]'. +DROP TABLE tbl_decimal; +# ENUM and SET columns are same as INT in storage, so here just for coverage. +# +# Singleton histogram, ENUM column +# +CREATE TABLE tbl_enum (col1 ENUM('red', 'black', 'blue', 'green')); +INSERT INTO tbl_enum VALUES ('red'), ('red'), ('black'), ('blue'), ('green'), +('green'), (NULL), (NULL), (NULL), (NULL); +ANALYZE TABLE tbl_enum UPDATE HISTOGRAM ON col1 WITH 10 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_enum histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_enum UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[1, 0.2], [2, 0.3], [3, 0.4], [4, 0.6]], "data-type": "enum", "null-values": 0.4, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 10}'; +Table Op Msg_type Msg_text +test.tbl_enum histogram status Histogram statistics created for column 'col1'. +# +# Equi-height histogram, ENUM column +# +ANALYZE TABLE tbl_enum UPDATE HISTOGRAM ON col1 WITH 2 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_enum histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_enum UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[1, 2, 0.3, 2], [3, 4, 0.6, 2]], "data-type": "enum", "null-values": 0.4, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 2}'; +Table Op Msg_type Msg_text +test.tbl_enum histogram status Histogram statistics created for column 'col1'. +DROP TABLE tbl_enum; +# +# Singleton histogram, SET column +# +CREATE TABLE tbl_set (col1 SET('red', 'black', 'blue', 'green')); +INSERT INTO tbl_set VALUES ('red'), ('red,black'), ('black,green,blue'), +('black,green,blue'), ('black,green,blue'), +('green'), ('green,red'), ('red,green'), +(NULL), (NULL); +ANALYZE TABLE tbl_set UPDATE HISTOGRAM ON col1 WITH 10 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_set histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_set UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[1, 0.1], [3, 0.2], [8, 0.3], [9, 0.5], [14, 0.8]], "data-type": "set", "null-values": 0.2, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 10}'; +Table Op Msg_type Msg_text +test.tbl_set histogram status Histogram statistics created for column 'col1'. +# +# Equi-height histogram, SET column +# +ANALYZE TABLE tbl_set UPDATE HISTOGRAM ON col1 WITH 2 BUCKETS; +Table Op Msg_type Msg_text +test.tbl_set histogram status Histogram statistics created for column 'col1'. +ANALYZE TABLE tbl_set UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[1, 9, 0.5, 4], [14, 14, 0.8, 1]], "data-type": "set", "null-values": 0.2, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 2}'; +Table Op Msg_type Msg_text +test.tbl_set histogram status Histogram statistics created for column 'col1'. +DROP TABLE tbl_set; diff --git a/mysql-test/suite/innodb/r/histogram-debug.result b/mysql-test/suite/innodb/r/histogram-debug.result index 4515dc2..4bee143 100644 --- a/mysql-test/suite/innodb/r/histogram-debug.result +++ b/mysql-test/suite/innodb/r/histogram-debug.result @@ -32,7 +32,7 @@ Table Op Msg_type Msg_text test.t1 histogram status Histogram statistics created for column 'a'. SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') -test t1 a {"buckets": [[10, 0.1], [20, 0.2], [30, 0.30000000000000004], [40, 0.4], [50, 0.5], [60, 0.6], [70, 0.7], [80, 0.7999999999999999], [90, 0.8999999999999999], [100, 0.9999999999999999]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} +test t1 a {"buckets": [[10, 0.1], [20, 0.2], [30, 0.3], [40, 0.4], [50, 0.5], [60, 0.6], [70, 0.7], [80, 0.8], [90, 0.9], [100, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} DROP TABLE t1; SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; NAME COUNT @@ -97,7 +97,7 @@ Table Op Msg_type Msg_text test.t1 histogram status Histogram statistics created for column 'a'. SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') -test t1 a {"buckets": [[10, 0.2], [20, 0.4], [30, 0.6000000000000001], [40, 0.8], [50, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 50} +test t1 a {"buckets": [[10, 0.2], [20, 0.4], [30, 0.6], [40, 0.8], [50, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 50} set global innodb_limit_optimistic_insert_debug = 0; DROP TABLE t1; SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; @@ -167,7 +167,7 @@ Table Op Msg_type Msg_text test.t1 histogram status Histogram statistics created for column 'a'. SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') -test t1 a {"buckets": [[40, 0.2], [50, 0.4], [80, 0.6000000000000001], [90, 0.8], [100, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} +test t1 a {"buckets": [[40, 0.2], [50, 0.4], [80, 0.6], [90, 0.8], [100, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; NAME COUNT sampled_pages_read 3 @@ -183,7 +183,7 @@ Table Op Msg_type Msg_text test.t1 histogram status Histogram statistics created for column 'a'. SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') -test t1 a {"buckets": [[40, 0.2], [50, 0.4], [80, 0.6000000000000001], [90, 0.8], [100, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} +test t1 a {"buckets": [[40, 0.2], [50, 0.4], [80, 0.6], [90, 0.8], [100, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} DROP TABLE t1; set global innodb_limit_optimistic_insert_debug = 0; SET DEBUG='-d,histogram_force_sampling'; @@ -273,7 +273,7 @@ Table Op Msg_type Msg_text test.t1 histogram status Histogram statistics created for column 'id'. SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'id'; SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') -test t1 id {"buckets": [[1, 0.2], [2, 0.4], [3, 0.6000000000000001], [4, 0.8], [5, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 50} +test t1 id {"buckets": [[1, 0.2], [2, 0.4], [3, 0.6], [4, 0.8], [5, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 50} SET DEBUG = "-d, sample_read_sample_half"; DROP TABLE t1; # diff --git a/mysql-test/t/histograms.test b/mysql-test/t/histograms.test index d07ab8d..04ab532 100644 --- a/mysql-test/t/histograms.test +++ b/mysql-test/t/histograms.test @@ -1626,6 +1626,16 @@ AND OUTR . col_varchar_key IS NULL HAVING x <= 3 ORDER BY OUTR . pk , OUTR . pk; + +--echo # +--echo # Bug#104108 CUMULATIVE FREQUENCY MAY GROW OVER 1.0 DUE TO FLOAT ERRORS +--echo # + +ANALYZE TABLE t2 UPDATE HISTOGRAM ON col_datetime WITH 100 BUCKETS; +SELECT schema_name, table_name, column_name, + JSON_REMOVE(histogram, '$."last-updated"') +FROM information_schema.COLUMN_STATISTICS WHERE table_name = 't2' and column_name = 'col_datetime'; + DROP TABLE t1, t2; SET @@SESSION.sql_mode=DEFAULT; diff --git a/mysql-test/t/histograms_update_using_data.test b/mysql-test/t/histograms_update_using_data.test new file mode 100644 index 0000000..67144af --- /dev/null +++ b/mysql-test/t/histograms_update_using_data.test @@ -0,0 +1,633 @@ +--echo # +--echo # singleton histogram +--echo # + +--let $tbl_name= tbl_int +--let $col_name= col1 +--let $buckets= 4 + +CREATE TABLE tbl_int(col1 INT); +INSERT INTO tbl_int VALUES (4), (52), (12), (12), (4), (23), (NULL), (NULL); +let $json_data= {"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}; +--source include/store_histogram_and_check.inc + +--echo # +--echo # check json format +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75], "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check if data is a json object +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA ''; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA 'a'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '1'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA 'null'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '[]'; + +# Test case arrangements: +# +# Common cases +# +# - common single attribute: missing, dom type, value domain +# +# Each histogram type +# +# - data type attribute +# - buckets: missing, dom type +# - number of buckets +# - buckets: bucket layout +# +# - bucket frequency: dom type, value domain +# - frequency sequence and total frequency +# +# Each value type +# +# - bucket endpoint: dom type, value domain +# - endpoint sequence + +--echo # +--echo # check attribute data-type +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": 1, "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "blob", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check attribute null-values [0.0, 1.0] +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": "0.25", "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": -0.01, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 1.01, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check attribute collation-id +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": "utf8", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 2048, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": -1, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 9300000000000000000, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check attribute sampling-rate [0.0, 1.0] +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": "1.0", "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": -0.1, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.1, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check attribute histogram-type +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": 1, "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "single", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check attribute number-of-buckets-specified [1, 1024] +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton"}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": "4"}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": -1}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 1025}'; + +--echo # +--echo # check attribute buckets array +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": "[[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]]", "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": null, "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check bucket layout [v, cf] or none +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [4, 0.25], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check if real number of buckets is less than specified +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 3}'; + +--echo # +--echo # check attribute cumulative frequency [0.0, 1.0] +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, "0.5"], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, -0.01], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 1.1]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check if cumulative frequency sequence is in strict asc order +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.7], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check if total frequency is 1 or 0 +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.26, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.24, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 3}'; + + +--echo # +--echo # singleton histogram, INT column +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23.0, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [9300000000000000000, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [2200000000, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check if value sequence is in strict asc order +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [32, 0.5], [23, 0.625], [52, 0.75]], "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +DROP TABLE tbl_int; + +--echo # +--echo # equal-height histogram +--echo # + +--let $tbl_name= tbl_int +--let $col_name= col1 +--let $buckets= 3 + +CREATE TABLE tbl_int(col1 INT); +INSERT INTO tbl_int VALUES (4), (52), (12), (12), (4), (23); +let $json_data= {"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}; +--source include/store_histogram_and_check.inc + +--echo # +--echo # check attribute data-type +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": 3, "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "text", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +--echo # +--echo # check attribute buckets array +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": "[[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]]", "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": null, "data-type": "int", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +--echo # +--echo # check bucket layout [l, u, cf, d] or none +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [4, 4, 0.3333333333333333, 1], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.4, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +--echo # +--echo # check if real number of buckets is less than specified +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 2}'; + +--echo # +--echo # check attribute cumulative frequency [0.0, 1.0] +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, "0.6666666666666666", 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, -0.01, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.01, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.01, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +--echo # +--echo # check if cumulative frequency sequence is in strict asc order +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.7333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +--echo # +--echo # check if total frequency is 1 +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.01, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.01, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +--echo # +--echo # check if equi-height has some buckets +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +--echo # +--echo # check attribute num distinct +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1.0], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 0], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, -1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +--echo # +--echo # equal-height histogram, INT column +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, "12", 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[9300000000000000000, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 9300000000000000000, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 2200000000, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 12, 0.6666666666666666, 1], [23, 2200000000, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +--echo # +--echo # check if value sequence is in strict asc order +--echo # +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [13, 12, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_int UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.3333333333333333, 1], [12, 42, 0.6666666666666666, 1], [23, 52, 1.0, 2]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +DROP TABLE tbl_int; + +--echo # +--echo # singleton histogram, BIGINT UNSIGNED column +--echo # + +--let $tbl_name= tbl_uint +--let $col_name= col1 +--let $buckets= 4 + +CREATE TABLE tbl_uint(col1 BIGINT UNSIGNED); +INSERT INTO tbl_uint VALUES (4), (52), (12), (12), (4), (23), (NULL), (NULL); +let $json_data= {"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [52, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23.0, 0.625], [52, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [20000000000000000000, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [12, 0.5], [23, 0.625], [-1, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 0.25], [32, 0.5], [23, 0.625], [52, 0.75]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +DROP TABLE tbl_uint; + +--echo # +--echo # equal-height histogram, BIGINT UNSIGNED column +--echo # + +--let $tbl_name= tbl_uint +--let $col_name= col1 +--let $buckets= 3 + +CREATE TABLE tbl_uint(col1 BIGINT UNSIGNED); +INSERT INTO tbl_uint VALUES (4), (52), (12), (12), (4), (23), (NULL), (NULL); +let $json_data= {"buckets": [[4, 4, 0.25, 1], [12, 12, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [12.0, 12, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [12, "12", 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[-1, 4, 0.25, 1], [12, 12, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [12, 12, 0.5, 1], [23, 20000000000000000000, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [13, 12, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_uint UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4, 0.25, 1], [12, 42, 0.5, 1], [23, 52, 0.75, 2]], "data-type": "uint", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +DROP TABLE tbl_uint; + +--echo # +--echo # singleton histogram, DOUBLE column +--echo # + +--let $tbl_name= tbl_double +--let $col_name= col1 +--let $buckets= 4 + +CREATE TABLE tbl_double(col1 DOUBLE); +INSERT INTO tbl_double VALUES (4.0), (52.0), (12.0), (12.0), (4.0), (23.0), (NULL), (NULL); +let $json_data= {"buckets": [[4.0, 0.25], [12.0, 0.5], [23.0, 0.625], [52.0, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 0.25], [12.0, 0.5], ["23.0", 0.625], [52.0, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[-1e330, 0.25], [12.0, 0.5], [23.0, 0.625], [52.0, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 0.25], [12.0, 0.5], [23.0, 0.625], [1E330, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 0.25], [32.0, 0.5], [23.0, 0.625], [52.0, 0.75]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +DROP TABLE tbl_double; + +--echo # +--echo # equal-height histogram, DOUBLE column +--echo # + +--let $tbl_name= tbl_double +--let $col_name= col1 +--let $buckets= 3 + +CREATE TABLE tbl_double(col1 DOUBLE); +INSERT INTO tbl_double VALUES (4.0), (52.0), (12.0), (12.0), (4.0), (23.0), (NULL), (NULL); +let $json_data= {"buckets": [[4.0, 4.0, 0.25, 1], [12.0, 12.0, 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4, 4.0, 0.25, 1], [12.0, 12.0, 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 4.0, 0.25, 1], [12.0, "12.0", 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 4.0, 0.25, 1], [13.0, 12.0, 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_double UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.0, 4.0, 0.25, 1], [12.0, 32.0, 0.5, 1], [23.0, 52.0, 0.75, 2]], "data-type": "double", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +DROP TABLE tbl_double; + +--echo # +--echo # singleton histogram, VARCHAR(8) column +--echo # + +--let $tbl_name= tbl_string +--let $col_name= col1 +--let $buckets= 4 + +CREATE TABLE tbl_string(col1 VARCHAR(8)); +INSERT INTO tbl_string VALUES ("Charles"), ("Mark"), ("Bill"), ("Bill"), ("Charles"), ("Vincent"), (NULL), (NULL); +let $json_data= {"buckets": [["base64:type254:QmlsbA==", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[12345, 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["QmlsbA==", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsTWFyVmluY2VuQ2hhcmxlbA==", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:TWFyaw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", 0.25], ["base64:type254:Q2hhcmxlcw==", 0.5], ["base64:type254:Q2hhcmxlcw==", 0.625], ["base64:type254:VmluY2VudA==", 0.75]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +DROP TABLE tbl_string; + +--echo # +--echo # equal-height histogram, VARCHAR(8) column +--echo # + +--let $tbl_name= tbl_string +--let $col_name= col1 +--let $buckets= 3 + +CREATE TABLE tbl_string(col1 VARCHAR(8)); +INSERT INTO tbl_string VALUES ("Charles"), ("Mark"), ("Bill"), ("Bill"), ("Charles"), ("Vincent"), (NULL), (NULL); +let $json_data= {"buckets": [["base64:type254:QmlsbA==", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}; +--source include/store_histogram_and_check.inc + +--echo +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[12345, "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", 12345, 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["QmlsbA==", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "base64:type254:QmlsbA", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsTWFyVmluY2VuQ2hhcmxlbA==", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "base64:type254:QmlsTWFyVmluY2VuQ2hhcmxlbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "base64:type254:QmlsbA==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:VmluY2VudA==", "base64:type254:TWFyaw==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_string UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["base64:type254:QmlsbA==", "base64:type254:TWFyaw==", 0.25, 1], ["base64:type254:Q2hhcmxlcw==", "base64:type254:Q2hhcmxlcw==", 0.5, 1], ["base64:type254:TWFyaw==", "base64:type254:VmluY2VudA==", 0.75, 2]], "data-type": "string", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +DROP TABLE tbl_string; + +--echo # +--echo # singleton histogram, DATE column +--echo # + +--let $tbl_name= tbl_date +--let $col_name= col1 +--let $buckets= 4 + +CREATE TABLE tbl_date(col1 DATE); +INSERT INTO tbl_date VALUES ("2018-03-21"), ("2017-02-06"), ("2017-02-10"), ("2017-02-10"), ("2018-03-21"), ("2018-02-12"), (NULL), (NULL); +let $json_data= {"buckets": [["2017-02-06", 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["0000-00-00", 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[20170206, 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 14:48:11", 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["14:48:11", 0.125], ["2017-02-10", 0.375], ["2018-02-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", 0.125], ["2017-02-10", 0.375], ["2018-04-12", 0.5], ["2018-03-21", 0.75]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +DROP TABLE tbl_date; + +--echo # +--echo # equal-height histogram, DATE column +--echo # + +--let $tbl_name= tbl_date +--let $col_name= col1 +--let $buckets= 3 + +CREATE TABLE tbl_date(col1 DATE); +INSERT INTO tbl_date VALUES ("2018-03-21"), ("2017-02-06"), ("2017-02-10"), ("2017-02-10"), ("2018-03-21"), ("2018-02-12"), (NULL), (NULL); +let $json_data= {"buckets": [["2017-02-06", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["0000-00-00", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "0000-00-00", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[20170206, "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", 20170210, 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 14:48:11", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12 14:48:11", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["14:48:11", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "2017-02-10", 0.375, 2], ["2018-02-12", "14:48:11", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-12", "2017-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_date UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "2019-02-10", 0.375, 2], ["2018-02-12", "2018-02-12", 0.5, 1], ["2018-03-21", "2018-03-21", 0.75, 1]], "data-type": "date", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +DROP TABLE tbl_date; + +--echo # +--echo # singleton histogram, TIME column +--echo # + +--let $tbl_name= tbl_time +--let $col_name= col1 +--let $buckets= 4 + +CREATE TABLE tbl_time(col1 TIME); +INSERT INTO tbl_time VALUES ("21:12:42"), ("16:22:23"), ("08:15:18"), ("08:15:18"), ("21:12:42"), ("07:04:18"), (NULL), (NULL); +let $json_data= {"buckets": [["07:04:18.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[170418, 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-10", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-10 07:04:18.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["-839:00:00.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["839:00:00.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["09:04:18.000000", 0.125], ["08:15:18.000000", 0.375], ["16:22:23.000000", 0.5], ["21:12:42.000000", 0.75]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +DROP TABLE tbl_time; + +--echo # +--echo # equal-height histogram, TIME column +--echo # + +--let $tbl_name= tbl_time +--let $col_name= col1 +--let $buckets= 3 + +CREATE TABLE tbl_time(col1 TIME); +INSERT INTO tbl_time VALUES ("21:12:42"), ("16:22:23"), ("08:15:18"), ("08:15:18"), ("21:12:42"), ("07:04:18"), (NULL), (NULL); +let $json_data= {"buckets": [["07:04:18.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[170418, "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", 181518, 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", "2017-02-10", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-10 07:04:18.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["-839:00:00.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "839:00:00.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["09:04:18.000000", "08:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_time UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["07:04:18.000000", "17:15:18.000000", 0.375, 2], ["16:22:23.000000", "16:22:23.000000", 0.5, 1], ["21:12:42.000000", "21:12:42.000000", 0.75, 1]], "data-type": "time", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +DROP TABLE tbl_time; + +--echo # +--echo # singleton histogram, DATETIME column +--echo # + +--let $tbl_name= tbl_datetime +--let $col_name= col1 +--let $buckets= 4 + +CREATE TABLE tbl_datetime(col1 DATETIME); +INSERT INTO tbl_datetime VALUES ("2018-03-21 21:12:42"), ("2017-02-06 16:22:23"), ("2017-02-10 08:15:18"), ("2017-02-10 08:15:18"), ("2018-03-21 21:12:42"), ("2018-02-12 07:04:18"), (NULL), (NULL); +let $json_data= {"buckets": [["2017-02-06 16:22:23.000000", 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[20170216162223, 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["16:22:23.000000", 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["0000-00-00 00:00:00.000000", 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-16 16:22:23.000000", 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +DROP TABLE tbl_datetime; + +--echo # +--echo # equal-height histogram, DATETIME column +--echo # + +--let $tbl_name= tbl_datetime +--let $col_name= col1 +--let $buckets= 3 + +CREATE TABLE tbl_datetime(col1 DATETIME); +INSERT INTO tbl_datetime VALUES ("2018-03-21 21:12:42"), ("2017-02-06 16:22:23"), ("2017-02-10 08:15:18"), ("2017-02-10 08:15:18"), ("2018-03-21 21:12:42"), ("2018-02-12 07:04:18"), (NULL), (NULL); +let $json_data= {"buckets": [["2017-02-06 16:22:23.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[20170206162223, "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", 20170210081518, 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["16:22:23.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", "08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["0000-00-00 00:00:00.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", "0000-00-00 00:00:00.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-16 16:22:23.000000", "2017-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["2017-02-06 16:22:23.000000", "2019-02-10 08:15:18.000000", 0.375, 2], ["2018-02-12 07:04:18.000000", "2018-02-12 07:04:18.000000", 0.5, 1], ["2018-03-21 21:12:42.000000", "2018-03-21 21:12:42.000000", 0.75, 1]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +DROP TABLE tbl_datetime; + +--echo # +--echo # singleton histogram, DECIMAL(5,2) column +--echo # + +--let $tbl_name= tbl_decimal +--let $col_name= col1 +--let $buckets= 4 + +# Note: This numbers are crafted carefully to avoid kind of 4.00, +# the direct-write way does not preserve trailing zeros. + +CREATE TABLE tbl_decimal(col1 DECIMAL(5,2)); +INSERT INTO tbl_decimal VALUES (4.12), (52.12), (12.12), (12.12), (4.12), (23.12), (NULL), (NULL); +let $json_data= {"buckets": [[4.12, 0.25], [12.12, 0.5], [23.12, 0.625], [52.12, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], ["12.00", 0.5], [23.00, 0.625], [52.00, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], [12.00, 0.5], [23.00, 0.625], [9300000000000000000, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], [12.00, 0.5], [23.00, 0.625], [1e100, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], [12.00, 0.5], [23.00, 0.625], [5555.00, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 0.25], [32.00, 0.5], [23.00, 0.625], [52.00, 0.75]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}'; + +DROP TABLE tbl_decimal; + +--echo # +--echo # equal-height histogram, DECIMAL(5,2) column +--echo # + +--let $tbl_name= tbl_decimal +--let $col_name= col1 +--let $buckets=3 + +CREATE TABLE tbl_decimal(col1 DECIMAL(5,2)); +INSERT INTO tbl_decimal VALUES (4.12), (52.12), (12.12), (12.12), (4.12), (23.12), (NULL), (NULL); +let $json_data= {"buckets": [[4.12, 4.12, 0.25, 1], [12.12, 12.12, 0.5, 1], [23.12, 52.12, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}; +--source include/store_histogram_and_check.inc + +--echo + +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [["4.00", 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [12.00, "12.00", 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[-1e100, 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 1e100, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[-1000.00, 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [12.00, 12.00, 0.5, 1], [23.00, 1000.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [13.00, 12.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; +ANALYZE TABLE tbl_decimal UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[4.00, 4.00, 0.25, 1], [12.00, 32.00, 0.5, 1], [23.00, 52.00, 0.75, 2]], "data-type": "decimal", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 3}'; + +DROP TABLE tbl_decimal; + +--echo # ENUM and SET columns are same as INT in storage, so here just for coverage. + +--echo # +--echo # Singleton histogram, ENUM column +--echo # + +--let $tbl_name= tbl_enum +--let $col_name= col1 +--let $buckets= 10 + +CREATE TABLE tbl_enum (col1 ENUM('red', 'black', 'blue', 'green')); +INSERT INTO tbl_enum VALUES ('red'), ('red'), ('black'), ('blue'), ('green'), + ('green'), (NULL), (NULL), (NULL), (NULL); +let $json_data= {"buckets": [[1, 0.2], [2, 0.3], [3, 0.4], [4, 0.6]], "data-type": "enum", "null-values": 0.4, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 10}; +--source include/store_histogram_and_check.inc + +--echo # +--echo # Equi-height histogram, ENUM column +--echo # + +--let $buckets= 2 + +let $json_data= {"buckets": [[1, 2, 0.3, 2], [3, 4, 0.6, 2]], "data-type": "enum", "null-values": 0.4, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 2}; +--source include/store_histogram_and_check.inc + +DROP TABLE tbl_enum; + +--echo # +--echo # Singleton histogram, SET column +--echo # + +--let $tbl_name= tbl_set +--let $col_name= col1 +--let $buckets= 10 + +CREATE TABLE tbl_set (col1 SET('red', 'black', 'blue', 'green')); +INSERT INTO tbl_set VALUES ('red'), ('red,black'), ('black,green,blue'), + ('black,green,blue'), ('black,green,blue'), + ('green'), ('green,red'), ('red,green'), + (NULL), (NULL); +let $json_data= {"buckets": [[1, 0.1], [3, 0.2], [8, 0.3], [9, 0.5], [14, 0.8]], "data-type": "set", "null-values": 0.2, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 10}; +--source include/store_histogram_and_check.inc + +--echo # +--echo # Equi-height histogram, SET column +--echo # + +--let $buckets= 2 + +let $json_data= {"buckets": [[1, 9, 0.5, 4], [14, 14, 0.8, 1]], "data-type": "set", "null-values": 0.2, "collation-id": 255, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 2}; +--source include/store_histogram_and_check.inc + +DROP TABLE tbl_set; diff --git a/sql/dd/impl/types/column_statistics_impl.cc b/sql/dd/impl/types/column_statistics_impl.cc index 8d0afcd..af4582c 100644 --- a/sql/dd/impl/types/column_statistics_impl.cc +++ b/sql/dd/impl/types/column_statistics_impl.cc @@ -43,6 +43,7 @@ #include "sql/dd/impl/tables/column_statistics.h" // Column_statistics #include "sql/dd/impl/transaction_impl.h" // Open_dictionary_tables_ctx #include "sql/histograms/histogram.h" // histograms::Histogram + // histograms::Error_context #include "sql/json_dom.h" // Json_* #include "template_utils.h" @@ -115,10 +116,11 @@ bool Column_statistics_impl::restore_attributes(const Raw_record &r) { return true; /* purecov: deadcode */ const Json_object *json_object = down_cast(json_dom); + histograms::Error_context context; m_histogram = histograms::Histogram::json_to_histogram( &m_mem_root, {m_schema_name.data(), m_schema_name.size()}, {m_table_name.data(), m_table_name.size()}, - {m_column_name.data(), m_column_name.size()}, *json_object); + {m_column_name.data(), m_column_name.size()}, *json_object, &context); if (m_histogram == nullptr) return true; /* purecov: deadcode */ return false; } diff --git a/sql/histograms/equi_height.cc b/sql/histograms/equi_height.cc index c2dee54..08c0f32 100644 --- a/sql/histograms/equi_height.cc +++ b/sql/histograms/equi_height.cc @@ -307,54 +307,147 @@ std::string Equi_height::histogram_type_to_str() const { } template -bool Equi_height::json_to_histogram(const Json_object &json_object) { - if (Histogram::json_to_histogram(json_object)) - return true; /* purecov: deadcode */ +bool Equi_height::json_to_histogram(const Json_object &json_object, + Error_context *context) { + if (Histogram::json_to_histogram(json_object, context)) return true; const Json_dom *buckets_dom = json_object.get(buckets_str()); - assert(buckets_dom->json_type() == enum_json_type::J_ARRAY); + if (buckets_dom == nullptr) { + assert(!context->binary()); + context->report_missing_attribute(buckets_str()); + return true; + } + if (buckets_dom->json_type() != enum_json_type::J_ARRAY) { + context->report_node(buckets_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } const Json_array *buckets = down_cast(buckets_dom); for (size_t i = 0; i < buckets->size(); ++i) { const Json_dom *bucket_dom = (*buckets)[i]; - assert(bucket_dom->json_type() == enum_json_type::J_ARRAY); + if (bucket_dom->json_type() != enum_json_type::J_ARRAY) { + assert(!context->binary()); + context->report_node(bucket_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } const Json_array *bucket = down_cast(bucket_dom); - assert(bucket->size() == 4); + assert(!context->binary() || bucket->size() == 4); + // Only the first four items are defined, others are simply ignored. + if (bucket->size() < 4) { + context->report_node(bucket_dom, Message::JSON_WRONG_BUCKET_TYPE_4); + return true; + } - if (add_bucket_from_json(bucket)) return true; /* purecov: deadcode */ + if (add_bucket_from_json(bucket, context)) return true; } + + // Global post-check +#ifdef DBUG_OFF + if (!context->internal()) +#endif + { + if (m_buckets.empty()) { + context->report_global(Message::JSON_IMPOSSIBLE_EMPTY_EQUI_HEIGHT); + return true; + } else if (std::abs(m_buckets.rbegin()->get_cumulative_frequency() + + get_null_values_fraction() - 1.0) > FREQUENCY_EPSILON) { + context->report_global(Message::JSON_INVALID_TOTAL_FREQUENCY); + return true; + } + } + return false; } template -bool Equi_height::add_bucket_from_json(const Json_array *json_bucket) { +bool Equi_height::add_bucket_from_json(const Json_array *json_bucket, + Error_context *context) { const Json_dom *cumulative_frequency_dom = (*json_bucket)[2]; - if (cumulative_frequency_dom->json_type() != enum_json_type::J_DOUBLE) - return true; /* purecov: deadcode */ - - const Json_dom *num_distinct_dom = (*json_bucket)[3]; - if (num_distinct_dom->json_type() != enum_json_type::J_UINT) - return true; /* purecov: deadcode */ - + if (cumulative_frequency_dom->json_type() != enum_json_type::J_DOUBLE) { + context->report_node(cumulative_frequency_dom, + Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } const Json_double *cumulative_frequency = down_cast(cumulative_frequency_dom); - const Json_uint *num_distinct = - down_cast(num_distinct_dom); + const Json_dom *num_distinct_dom = (*json_bucket)[3]; + ulonglong num_distinct_v = 0; + if (num_distinct_dom->json_type() == enum_json_type::J_UINT) { + num_distinct_v = down_cast(num_distinct_dom)->value(); + } else if (!context->binary() && + num_distinct_dom->json_type() == enum_json_type::J_INT) { + const Json_int *num_distinct = + down_cast(num_distinct_dom); + num_distinct_v = (num_distinct->value() > 0) ? num_distinct->value() : 0; + } else { + context->report_node(num_distinct_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } const Json_dom *lower_inclusive_dom = (*json_bucket)[0]; const Json_dom *upper_inclusive_dom = (*json_bucket)[1]; T upper_value; T lower_value; - if (extract_json_dom_value(upper_inclusive_dom, &upper_value) || - extract_json_dom_value(lower_inclusive_dom, &lower_value)) - return true; /* purecov: deadcode */ + if (extract_json_dom_value(upper_inclusive_dom, &upper_value, context)) + return true; + if (extract_json_dom_value(lower_inclusive_dom, &lower_value, context)) + return true; + + // Bucket-extraction post-check +#ifdef DBUG_OFF + if (!context->internal()) +#endif + { + // Check items in the bucket + if (cumulative_frequency->value() < 0.0 || + cumulative_frequency->value() > 1.0) { + context->report_node(cumulative_frequency_dom, + Message::JSON_INVALID_FREQUENCY); + return true; + } + if (num_distinct_v < 1) { + context->report_node(num_distinct_dom, + Message::JSON_INVALID_NUM_DISTINCT); + return true; + } + if (context->check_value(&upper_value)) { + context->report_node(upper_inclusive_dom, + Message::JSON_VALUE_OUT_OF_RANGE); + return true; + } + if (context->check_value(&lower_value)) { + context->report_node(lower_inclusive_dom, + Message::JSON_VALUE_OUT_OF_RANGE); + return true; + } + + // Check endpoint sequence and frequency sequence. + if (histograms::Histogram_comparator()(upper_value, lower_value)) { + context->report_node(json_bucket, + Message::JSON_VALUE_DESCENDING_IN_BUCKET); + return true; + } + if (!m_buckets.empty()) { + if (!histograms::Histogram_comparator()(*m_buckets.rbegin(), + lower_value)) { + context->report_node(json_bucket, Message::JSON_VALUE_NOT_ASCENDING_2); + return true; + } + if (m_buckets.rbegin()->get_cumulative_frequency() >= + cumulative_frequency->value()) { + context->report_node(json_bucket, + Message::JSON_CUMULATIVE_FREQUENCY_NOT_ASCENDING); + return true; + } + } + } try { m_buckets.emplace(lower_value, upper_value, cumulative_frequency->value(), - num_distinct->value()); + num_distinct_v); } catch (const std::bad_alloc &) { return true; /* purecov: deadcode */ } diff --git a/sql/histograms/equi_height.h b/sql/histograms/equi_height.h index a5cd69a..e20cec3 100644 --- a/sql/histograms/equi_height.h +++ b/sql/histograms/equi_height.h @@ -116,10 +116,13 @@ class Equi_height : public Histogram { the provided JSON array. Contents are allocated as needed on the current histograms MEM_ROOT. - @param json_bucket a JSON array containing the histogram buckets + @param json_bucket a JSON array containing the histogram buckets + @param context error context for validation + @return true on error, false otherwise */ - bool add_bucket_from_json(const Json_array *json_bucket); + bool add_bucket_from_json(const Json_array *json_bucket, + Error_context *context); /** Find the fraction of values that is less than or equal to 'value'. @@ -137,11 +140,13 @@ class Equi_height : public Histogram { /** Populate this histogram with contents from a JSON object. - @param json_object a JSON object that represents an Equi-height histogram + @param json_object a JSON object that represents an Equi-height histogram + @param context error context for validation @return true on error, false otherwise. */ - bool json_to_histogram(const Json_object &json_object) override; + bool json_to_histogram(const Json_object &json_object, + Error_context *context) override; public: /** diff --git a/sql/histograms/histogram.cc b/sql/histograms/histogram.cc index 925549a..23013cb 100644 --- a/sql/histograms/histogram.cc +++ b/sql/histograms/histogram.cc @@ -36,6 +36,8 @@ #include #include +#include "base64.h" // base64_* +#include "decimal.h" // *2decimal #include "field_types.h" // enum_field_types #include "lex_string.h" #include "m_ctype.h" @@ -58,12 +60,14 @@ #include "sql/dd/types/column_statistics.h" #include "sql/dd/types/table.h" // dd::Table #include "sql/debug_sync.h" +#include "sql/error_handler.h" // Internal_error_handler #include "sql/field.h" // Field #include "sql/handler.h" #include "sql/histograms/equi_height.h" // Equi_height #include "sql/histograms/singleton.h" // Singleton #include "sql/histograms/value_map.h" // Value_map #include "sql/item.h" +#include "sql/item_json_func.h" // parse_json #include "sql/json_dom.h" // Json_* #include "sql/key.h" #include "sql/mdl.h" // MDL_request @@ -74,6 +78,7 @@ // close_thread_tables #include "sql/sql_class.h" // make_lex_string_root #include "sql/sql_const.h" +#include "sql/sql_time.h" // str_to_time #include "sql/strfunc.h" // find_type2, find_set #include "sql/system_variables.h" #include "sql/table.h" @@ -87,6 +92,9 @@ struct TYPELIB; namespace histograms { +// Same as MAX_NUMBER_OF_HISTOGRAM_BUCKETS defined in sql_yacc.yy +static constexpr int MAX_NUMBER_OF_HISTOGRAM_BUCKETS = 1024; + /* This type represents a instrumented map of value maps, indexed by field number. @@ -193,6 +201,134 @@ static Value_map_type field_type_to_value_map_type(const Field *field) { return field_type_to_value_map_type(field->real_type(), is_unsigned); } +void Error_context::report_global(Message err_code) { + assert(err_code == Message::JSON_NUM_BUCKETS_MORE_THAN_SPECIFIED || + err_code == Message::JSON_IMPOSSIBLE_EMPTY_EQUI_HEIGHT || + err_code == Message::JSON_INVALID_TOTAL_FREQUENCY); + if (m_results) { + Json_path path; + + String str; + path.to_string(&str); + m_results->emplace(to_string(str), err_code); + } +} + +void Error_context::report_missing_attribute(const std::string &name) { + // In histogram json, attributes are always top-level. + if (m_results) { + Json_path path; + Json_path_leg leg(name); + path.append(leg); + + String str; + path.to_string(&str); + m_results->emplace(to_string(str), Message::JSON_MISSING_ATTRIBUTE); + } +} + +void Error_context::report_node(const Json_dom *dom, Message err_code) { + assert(!(err_code == Message::JSON_INVALID_TOTAL_FREQUENCY || + err_code == Message::JSON_NUM_BUCKETS_MORE_THAN_SPECIFIED || + err_code == Message::JSON_IMPOSSIBLE_EMPTY_EQUI_HEIGHT || + err_code == Message::JSON_MISSING_ATTRIBUTE)); + if (m_results) { + String str; + dom->get_location().to_string(&str); + m_results->emplace(to_string(str), err_code); + } +} + +/// RAII class to trap lower-level errors. +class Histogram_error_handler : public Internal_error_handler { + public: + Histogram_error_handler(THD *thd) + : Internal_error_handler{}, m_thd(thd), m_has_error{false} { + m_thd->push_internal_handler(this); + } + ~Histogram_error_handler() { + m_thd->pop_internal_handler(); + } + + /// @return true if the condition is handled + bool handle_condition(THD *, uint, const char *, + Sql_condition::enum_severity_level *, + const char *) override { + m_has_error = true; + return true; + } + + bool has_error() const { return m_has_error; } + + private: + THD *m_thd; + bool m_has_error; +}; + + +/** + Helper template function for check_value(). + + It uses Field::store() or some variant to test if the value is in the field + definition domain, which is kind of best effort without touching real data. + + Note that the value parameter is pointer instead of const reference. + The function uses Field::store() and variants to test against value domain, + which do not agree the same const style. +*/ +template +type_conversion_status check_value_aux(Field *f, T *v); + +template <> +type_conversion_status check_value_aux(Field *field, double *nr) { + return field->store(*nr); +} + +template <> +type_conversion_status check_value_aux(Field *field, String *str) { + return field->store(str->ptr(), str->length(), str->charset()); +} + +template <> +type_conversion_status check_value_aux(Field *field, longlong *nr) { + return field->store(*nr, false); +} + +template <> +type_conversion_status check_value_aux(Field *field, ulonglong *nr) { + return field->store(*nr, true); +} + +template <> +type_conversion_status check_value_aux(Field *field, MYSQL_TIME *ltime) { + return field->store_time(ltime); +} + +template <> +type_conversion_status check_value_aux(Field *field, my_decimal *mdec) { + return field->store_decimal(mdec); +} + +template +bool Error_context::check_value(T *v) { + if (m_thd) { + Histogram_error_handler error_handler(m_thd); + return (m_field && + check_value_aux(m_field, v) != type_conversion_status::TYPE_OK) || + error_handler.has_error(); + } + return false; +} + +// Explicit template instantiations. +template bool Error_context::check_value(double *); +template bool Error_context::check_value(String *); +template bool Error_context::check_value(longlong *); +template bool Error_context::check_value(ulonglong *); +template bool Error_context::check_value(MYSQL_TIME *); +template bool Error_context::check_value(my_decimal *); + + /** Lock a column statistic MDL key for writing (exclusive lock). @@ -361,20 +497,29 @@ Histogram *Histogram::json_to_histogram(MEM_ROOT *mem_root, const std::string &schema_name, const std::string &table_name, const std::string &column_name, - const Json_object &json_object) { + const Json_object &json_object, + Error_context *context) { // Histogram type (equi-height or singleton). const Json_dom *histogram_type_dom = json_object.get(Histogram::histogram_type_str()); - if (histogram_type_dom == nullptr || - histogram_type_dom->json_type() != enum_json_type::J_STRING) { - return nullptr; /* purecov: deadcode */ + if (histogram_type_dom == nullptr) { + context->report_missing_attribute(Histogram::histogram_type_str()); + return nullptr; + } + if (histogram_type_dom->json_type() != enum_json_type::J_STRING) { + context->report_node(histogram_type_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return nullptr; } // Histogram data type const Json_dom *data_type_dom = json_object.get(Histogram::data_type_str()); - if (data_type_dom == nullptr || - data_type_dom->json_type() != enum_json_type::J_STRING) { - return nullptr; /* purecov: deadcode */ + if (data_type_dom == nullptr) { + context->report_missing_attribute(Histogram::data_type_str()); + return nullptr; + } + if (data_type_dom->json_type() != enum_json_type::J_STRING) { + context->report_node(data_type_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return nullptr; } const Json_string *histogram_type = @@ -419,7 +564,8 @@ Histogram *Histogram::json_to_histogram(MEM_ROOT *mem_root, Equi_height(mem_root, schema_name, table_name, column_name, Value_map_type::DECIMAL); } else { - return nullptr; /* purecov: deadcode */ + context->report_node(data_type_dom, Message::JSON_UNSUPPORTED_DATA_TYPE); + return nullptr; } } else if (histogram_type->value() == Histogram::singleton_str()) { // Singleton histogram @@ -458,15 +604,29 @@ Histogram *Histogram::json_to_histogram(MEM_ROOT *mem_root, Singleton(mem_root, schema_name, table_name, column_name, Value_map_type::DECIMAL); } else { - return nullptr; /* purecov: deadcode */ + context->report_node(data_type_dom, Message::JSON_UNSUPPORTED_DATA_TYPE); + return nullptr; } } else { // Unsupported histogram type. - return nullptr; /* purecov: deadcode */ + context->report_node(histogram_type_dom, + Message::JSON_UNSUPPORTED_HISTOGRAM_TYPE); + return nullptr; } - if (histogram != nullptr && histogram->json_to_histogram(json_object)) - return nullptr; /* purecov: deadcode */ + if (histogram != nullptr && + histogram->json_to_histogram(json_object, context)) + return nullptr; + + // Global post-check + if ( +#ifdef DBUG_OFF + !context->internal() && +#endif + histogram->get_num_buckets_specified() < histogram->get_num_buckets()) { + context->report_global(Message::JSON_NUM_BUCKETS_MORE_THAN_SPECIFIED); + return nullptr; + } return histogram; } @@ -474,12 +634,17 @@ Histogram *Histogram::json_to_histogram(MEM_ROOT *mem_root, All subclasses should also call this function in order to populate fields that are shared among all histogram types (character set, null values fraction). */ -bool Histogram::json_to_histogram(const Json_object &json_object) { +bool Histogram::json_to_histogram(const Json_object &json_object, + Error_context *context) { // The sampling rate that was used to create the histogram. const Json_dom *sampling_rate_dom = json_object.get(sampling_rate_str()); - if (sampling_rate_dom == nullptr || - sampling_rate_dom->json_type() != enum_json_type::J_DOUBLE) { - return true; /* purecov: deadcode */ + if (sampling_rate_dom == nullptr) { + context->report_missing_attribute(Histogram::sampling_rate_str()); + return true; + } + if (sampling_rate_dom->json_type() != enum_json_type::J_DOUBLE) { + context->report_node(sampling_rate_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; } const Json_double *sampling_rate = down_cast(sampling_rate_dom); @@ -488,9 +653,15 @@ bool Histogram::json_to_histogram(const Json_object &json_object) { // The number of buckets originally specified by the user. const Json_dom *num_buckets_specified_dom = json_object.get(numer_of_buckets_specified_str()); - if (num_buckets_specified_dom == nullptr || - num_buckets_specified_dom->json_type() != enum_json_type::J_INT) { - return true; /* purecov: deadcode */ + if (num_buckets_specified_dom == nullptr) { + context->report_missing_attribute( + Histogram::numer_of_buckets_specified_str()); + return true; + } + if (num_buckets_specified_dom->json_type() != enum_json_type::J_INT) { + context->report_node(num_buckets_specified_dom, + Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; } const Json_int *num_buckets_specified = down_cast(num_buckets_specified_dom); @@ -498,9 +669,13 @@ bool Histogram::json_to_histogram(const Json_object &json_object) { // Fraction of SQL null-values in the original data set. const Json_dom *null_values_dom = json_object.get(null_values_str()); - if (null_values_dom == nullptr || - null_values_dom->json_type() != enum_json_type::J_DOUBLE) { - return true; /* purecov: deadcode */ + if (null_values_dom == nullptr) { + context->report_missing_attribute(Histogram::null_values_str()); + return true; + } + if (null_values_dom->json_type() != enum_json_type::J_DOUBLE) { + context->report_node(null_values_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; } const Json_double *null_values = down_cast(null_values_dom); @@ -508,14 +683,53 @@ bool Histogram::json_to_histogram(const Json_object &json_object) { // Character set ID const Json_dom *charset_id_dom = json_object.get(collation_id_str()); - if (charset_id_dom == nullptr || - charset_id_dom->json_type() != enum_json_type::J_UINT) { - return true; /* purecov: deadcode */ + if (charset_id_dom == nullptr) { + context->report_missing_attribute(Histogram::collation_id_str()); + return true; } - const Json_uint *charset_id = down_cast(charset_id_dom); - // Get the charset (my_sys.h) - m_charset = get_charset(static_cast(charset_id->value()), MYF(0)); + /* + In the JSON object of the histogram, charset_id is defined as an unsigned + integer, but it may become a signed integer when re-parsed into a JSON + object. + */ + if (charset_id_dom->json_type() == enum_json_type::J_UINT) { + const Json_uint *charset_id = down_cast(charset_id_dom); + m_charset = get_charset(static_cast(charset_id->value()), MYF(0)); + } else if (!context->binary() && + charset_id_dom->json_type() == enum_json_type::J_INT) { + const Json_int *charset_id = down_cast(charset_id_dom); + m_charset = get_charset(static_cast(charset_id->value()), MYF(0)); + } else { + context->report_node(charset_id_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } + + // Common attributes post-check +#ifdef DBUG_OFF + if (!context->internal()) +#endif + { + if (sampling_rate->value() < 0.0 || sampling_rate->value() > 1.0) { + context->report_node(sampling_rate_dom, + Message::JSON_INVALID_SAMPLING_RATE); + return true; + } + if (num_buckets_specified->value() < 1 || + num_buckets_specified->value() > MAX_NUMBER_OF_HISTOGRAM_BUCKETS) { + context->report_node(num_buckets_specified_dom, + Message::JSON_INVALID_NUM_BUCKETS_SPECIFIED); + return true; + } + if (null_values->value() < 0.0 || null_values->value() > 1.0) { + context->report_node(null_values_dom, Message::JSON_INVALID_FREQUENCY); + return true; + } + if (m_charset == nullptr) { + context->report_node(charset_id_dom, Message::JSON_UNSUPPORTED_CHARSET); + return true; + } + } return false; } @@ -534,72 +748,225 @@ bool Histogram::histogram_data_type_to_json(Json_object *json_object) const { } template <> -bool Histogram::extract_json_dom_value(const Json_dom *json_dom, double *out) { - if (json_dom->json_type() != enum_json_type::J_DOUBLE) - return true; /* purecov: deadcode */ +bool Histogram::extract_json_dom_value(const Json_dom *json_dom, double *out, + Error_context *context) { + if (json_dom->json_type() != enum_json_type::J_DOUBLE) { + context->report_node(json_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } + *out = down_cast(json_dom)->value(); + return false; } template <> -bool Histogram::extract_json_dom_value(const Json_dom *json_dom, String *out) { +bool Histogram::extract_json_dom_value(const Json_dom *json_dom, String *out, + Error_context *context) { assert(get_character_set() != nullptr); - if (json_dom->json_type() != enum_json_type::J_OPAQUE) - return true; /* purecov: deadcode */ - const Json_opaque *json_opaque = down_cast(json_dom); - String value(json_opaque->value(), json_opaque->size(), get_character_set()); + char *value_dup_data = nullptr; + size_t value_dup_length = 0; - /* - Make a copy of the data, since the JSON opaque will free it before we need - it. - */ - char *value_dup_data = value.dup(get_mem_root()); - if (value_dup_data == nullptr) { - assert(false); /* purecov: deadcode */ - return true; // OOM + if (json_dom->json_type() == enum_json_type::J_OPAQUE) { + assert(context->binary()); + const Json_opaque *json_opaque = down_cast(json_dom); + + String value(json_opaque->value(), json_opaque->size(), + get_character_set()); + value_dup_length = value.length(); + + /* + Make a copy of the data, since the JSON opaque will free it before we need + it. + */ + value_dup_data = value.dup(get_mem_root()); + if (value_dup_data == nullptr) { + assert(0); /* purecov: deadcode */ + return true; // OOM + } + } else if (!context->binary() && + json_dom->json_type() == enum_json_type::J_STRING) { + /* + When a histogram is converted to binary json by histogram_to_json() + to be persisted, a String-typed value is converted to Json_opaque with + field type enum_field_types::MYSQL_TYPE_STRING. + + The opaque data is base64-encoded by Json_wrapper::to_string() before it + goes to the outside as standard json. + + So when the outside data comes back and is processed by parse_json(), it + is J_STRING and needs to be decoded here. The fact that it is always + prefixed "base64:type254:" could be also explored. + */ + const Json_string *json_string = down_cast(json_dom); + const std::string &str = json_string->value(); + + static_assert( + static_cast(enum_field_types::MYSQL_TYPE_STRING) == 254, ""); + constexpr const char *prefix = "base64:type254:"; + constexpr int prefix_length = strlen(prefix); + + size_t pos = str.find(prefix, 0, prefix_length); + if (pos == str.npos) { + context->report_node(json_dom, Message::JSON_VALUE_FORMAT_ERROR); + return true; + } + + const char *substr = str.c_str() + prefix_length; + const size_t slen = str.size() - prefix_length; + if (slen > base64_decode_max_arg_length()) return true; + const size_t needed = + static_cast(base64_needed_decoded_length(slen)); + + String base64_buffer; + if (base64_buffer.reserve(needed)) { + return true; + } + + const char *end_ptr = nullptr; + const int64 rlen = + base64_decode(substr, slen, &base64_buffer[0], &end_ptr, 0); + if (rlen < 0 || + (end_ptr && (static_cast(end_ptr - substr) != slen))) { + context->report_node(json_dom, Message::JSON_VALUE_FORMAT_ERROR); + return true; + } + base64_buffer.length(rlen); + + value_dup_data = base64_buffer.dup(get_mem_root()); + if (value_dup_data == nullptr) { + assert(0); /* purecov: deadcode */ + return true; // OOM + } + value_dup_length = rlen; + } else { + context->report_node(json_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; } - out->set(value_dup_data, value.length(), value.charset()); + out->set(value_dup_data, value_dup_length, get_character_set()); return false; } template <> -bool Histogram::extract_json_dom_value(const Json_dom *json_dom, - ulonglong *out) { - if (json_dom->json_type() != enum_json_type::J_UINT) - return true; /* purecov: deadcode */ - *out = down_cast(json_dom)->value(); +bool Histogram::extract_json_dom_value(const Json_dom *json_dom, ulonglong *out, + Error_context *context) { + if (json_dom->json_type() == enum_json_type::J_UINT) + *out = down_cast(json_dom)->value(); + else if (!context->binary() && + json_dom->json_type() == enum_json_type::J_INT) { + longlong val = down_cast(json_dom)->value(); + if (val < 0) { + context->report_node(json_dom, Message::JSON_VALUE_OUT_OF_RANGE); + return true; + } + *out = static_cast(val); + } else { + context->report_node(json_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } + return false; } template <> -bool Histogram::extract_json_dom_value(const Json_dom *json_dom, - longlong *out) { - if (json_dom->json_type() != enum_json_type::J_INT) - return true; /* purecov: deadcode */ +bool Histogram::extract_json_dom_value(const Json_dom *json_dom, longlong *out, + Error_context *context) { + if (json_dom->json_type() != enum_json_type::J_INT) { + if (json_dom->json_type() == enum_json_type::J_UINT) + context->report_node(json_dom, Message::JSON_VALUE_OUT_OF_RANGE); + else + context->report_node(json_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + + return true; + } + *out = down_cast(json_dom)->value(); return false; } template <> bool Histogram::extract_json_dom_value(const Json_dom *json_dom, - MYSQL_TIME *out) { - if (json_dom->json_type() != enum_json_type::J_DATE && - json_dom->json_type() != enum_json_type::J_TIME && - json_dom->json_type() != enum_json_type::J_DATETIME && - json_dom->json_type() != enum_json_type::J_TIMESTAMP) - return true; /* purecov: deadcode */ - *out = *down_cast(json_dom)->value(); + MYSQL_TIME *out, + Error_context *context) { + if (json_dom->json_type() == enum_json_type::J_DATE || + json_dom->json_type() == enum_json_type::J_TIME || + json_dom->json_type() == enum_json_type::J_DATETIME || + json_dom->json_type() == enum_json_type::J_TIMESTAMP) { + assert(context->binary()); + *out = *down_cast(json_dom)->value(); + } else if (!context->binary() && + json_dom->json_type() == enum_json_type::J_STRING) { + const Json_string *json_string = down_cast(json_dom); + String str{json_string->value().c_str(), json_string->value().size(), + &my_charset_utf8mb4_bin}; + MYSQL_TIME_STATUS status; + + if (get_data_type() == Value_map_type::TIME) { + if (str_to_time(&str, out, 0, &status) || status.warnings) { + context->report_node(json_dom, Message::JSON_VALUE_FORMAT_ERROR); + return true; + } + out->time_type = enum_mysql_timestamp_type::MYSQL_TIMESTAMP_TIME; + } else if (get_data_type() == Value_map_type::DATE) { + if (str_to_datetime(&str, out, 0, &status) || status.warnings) { + context->report_node(json_dom, Message::JSON_VALUE_FORMAT_ERROR); + return true; + } + out->time_type = enum_mysql_timestamp_type::MYSQL_TIMESTAMP_DATE; + } else if (get_data_type() == Value_map_type::DATETIME) { + if (str_to_datetime(&str, out, 0, &status) || status.warnings) { + context->report_node(json_dom, Message::JSON_VALUE_FORMAT_ERROR); + return true; + } + out->time_type = enum_mysql_timestamp_type::MYSQL_TIMESTAMP_DATETIME; + } else { + assert(0); /* purecov: deadcode */ + return true; + } + } else { + context->report_node(json_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } + return false; } template <> bool Histogram::extract_json_dom_value(const Json_dom *json_dom, - my_decimal *out) { - if (json_dom->json_type() != enum_json_type::J_DECIMAL) - return true; /* purecov: deadcode */ - *out = *down_cast(json_dom)->value(); + my_decimal *out, + Error_context *context) { + if (json_dom->json_type() == enum_json_type::J_DECIMAL) + *out = *down_cast(json_dom)->value(); + else if (!context->binary()) { + if (json_dom->json_type() == enum_json_type::J_INT) { + if (longlong2decimal(down_cast(json_dom)->value(), + out)) { + context->report_node(json_dom, Message::JSON_VALUE_FORMAT_ERROR); + return true; + } + } else if (json_dom->json_type() == enum_json_type::J_UINT) { + if (ulonglong2decimal( + down_cast(json_dom)->value(), out)) { + context->report_node(json_dom, Message::JSON_VALUE_FORMAT_ERROR); + return true; + } + } else if (json_dom->json_type() == enum_json_type::J_DOUBLE) { + if (double2decimal(down_cast(json_dom)->value(), + out)) { + context->report_node(json_dom, Message::JSON_VALUE_FORMAT_ERROR); + return true; + } + } else { + context->report_node(json_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } + } else { + assert(0); /* purecov: deadcode */ + return true; + } + return false; } @@ -870,7 +1237,7 @@ static bool fill_value_maps( } bool update_histogram(THD *thd, TABLE_LIST *table, const columns_set &columns, - int num_buckets, results_map &results) { + int num_buckets, LEX_STRING data, results_map &results) { dd::cache::Dictionary_client::Auto_releaser auto_releaser(thd->dd_client()); // Read only should have been stopped at an earlier stage. @@ -984,6 +1351,62 @@ bool update_histogram(THD *thd, TABLE_LIST *table, const columns_set &columns, */ if (resolved_fields.empty()) return true; + if (data.str != nullptr) { + assert(!resolved_fields.empty()); + if (resolved_fields.size() > 1) { + results.emplace("", Message::MULTIPLE_COLUMNS_SPECIFIED); + return true; + } + + Field *field = resolved_fields.front(); + + /* + The column needs to be in the write set because Field::store() or some + variant is used to test value domain. + */ + bitmap_set_bit(tbl->write_set, field->field_index()); + + // Parse the literal for a standard JSON object. + String str{data.str, static_cast(data.length), + &my_charset_utf8mb4_bin}; + Json_dom_ptr dom; + bool parse_error = false; + { + Histogram_error_handler error_handler(thd); + if (parse_json(str, 0, __func__, &dom, true, &parse_error) || + error_handler.has_error()) { + results.emplace("", Message::JSON_FORMAT_ERROR); + return true; + } + if (dom->json_type() != enum_json_type::J_OBJECT) { + results.emplace("", Message::JSON_NOT_AN_OBJECT); + return true; + } + } + + MEM_ROOT local_mem_root; + init_alloc_root(key_memory_histograms, &local_mem_root, 256, 0); + + // Create a histogram for the json object. + Error_context context(thd, field, &results); + std::string col_name(field->field_name); + histograms::Histogram *histogram = Histogram::json_to_histogram( + &local_mem_root, std::string(table->db, table->db_length), + std::string(table->table_name, table->table_name_length), col_name, + *down_cast(dom.get()), &context); + + // Store it to persistent storage. + if (histogram == nullptr || histogram->store_histogram(thd)) return true; + + results.emplace(col_name, Message::HISTOGRAM_CREATED); + + bool ret = trans_commit_stmt(thd) || trans_commit(thd); + close_thread_tables(thd); + tables_guard.commit(); + + return ret; + } + /* Prepare one Value_map for each field we are creating histogram statistics for. Also, estimate how many bytes one row will consume so that we can diff --git a/sql/histograms/histogram.h b/sql/histograms/histogram.h index 6219ebd..52ebfd3 100644 --- a/sql/histograms/histogram.h +++ b/sql/histograms/histogram.h @@ -58,6 +58,7 @@ class Json_dom; class Json_object; class THD; struct TYPELIB; +class Field; namespace dd { class Table; @@ -70,6 +71,7 @@ class Value_map; struct CHARSET_INFO; struct MEM_ROOT; struct TABLE_LIST; +class Json_dom; namespace histograms { @@ -84,10 +86,35 @@ enum class Message { VIEW, HISTOGRAM_CREATED, MULTIPLE_TABLES_SPECIFIED, + MULTIPLE_COLUMNS_SPECIFIED, COVERED_BY_SINGLE_PART_UNIQUE_INDEX, NO_HISTOGRAM_FOUND, HISTOGRAM_DELETED, - SERVER_READ_ONLY + SERVER_READ_ONLY, + + // JSON validation errors. See Error_context. + JSON_FORMAT_ERROR, + JSON_NOT_AN_OBJECT, + JSON_MISSING_ATTRIBUTE, + JSON_WRONG_ATTRIBUTE_TYPE, + JSON_WRONG_BUCKET_TYPE_2, + JSON_WRONG_BUCKET_TYPE_4, + JSON_UNSUPPORTED_DATA_TYPE, + JSON_UNSUPPORTED_HISTOGRAM_TYPE, + JSON_UNSUPPORTED_CHARSET, + JSON_INVALID_SAMPLING_RATE, + JSON_INVALID_NUM_BUCKETS_SPECIFIED, + JSON_INVALID_FREQUENCY, + JSON_INVALID_NUM_DISTINCT, + JSON_VALUE_FORMAT_ERROR, + JSON_VALUE_OUT_OF_RANGE, + JSON_VALUE_NOT_ASCENDING_1, + JSON_VALUE_NOT_ASCENDING_2, + JSON_VALUE_DESCENDING_IN_BUCKET, + JSON_CUMULATIVE_FREQUENCY_NOT_ASCENDING, + JSON_INVALID_TOTAL_FREQUENCY, + JSON_NUM_BUCKETS_MORE_THAN_SPECIFIED, + JSON_IMPOSSIBLE_EMPTY_EQUI_HEIGHT, }; struct Histogram_psi_key_alloc { @@ -131,6 +158,116 @@ enum class enum_operator { }; /** + Error context to validate given JSON object which represents a histogram. + + A validation error consists of two pieces of information: + + 1) error code - what kind of error it is + 2) JSON path - where the error occurs + + Errors are classified into a few conceptual categories, namely + + 1) absence of required attributes + 2) unexpected JSON type of attributes + 3) value encoding corruption + 4) value out of domain + 5) breaking bucket sequence semantics + 6) breaking certain constraint between pieces of information + + @see Message +*/ +class Error_context { + public: + /// Default constructor. The context will discard any error. + Error_context() + : m_thd{nullptr}, + m_field{nullptr}, + m_results{nullptr}, + m_internal{true}, + m_binary{true} {} + + /** + Constructor. The context will save errors to the given results store. + + @param thd Thread context + @param field The field for values on which the histogram is built + @param results Where reported errors are stored + */ + Error_context(THD *thd, Field *field, results_map *results) + : m_thd{thd}, m_field{field}, m_results{results}, m_internal{false}, + m_binary{false} {} + + /** + Report a global error to this context. + + @param err_code The global error code + */ + void report_global(Message err_code); + + /** + Report to this context that a required attribute is missing. + + @param name Name of the missing attribute + */ + void report_missing_attribute(const std::string &name); + + /** + Report to this context that an error occurs on the given dom node. + + @param dom The given dom node + @param err_code The error code + */ + void report_node(const Json_dom *dom, Message err_code); + + /** + Check if the value is in the field definition domain. + + @param v Pointer to the value. + + @return true on error, false otherwise + */ + template + bool check_value(T *v); + + /** + Internal mode to bypass post checks for loading Column_statistics. + + Note that DD assumes valid Column_statistics objects. Any json validation + error would break that assumption and have bad consequence. The following + patch provided some defense: + + Bug#97172 UNABLE TO OPEN TABLE WITH INCOMPATIBLE HISTOGRAM (issue#142) + + Anyway, the internal mode is still provided to get rid of checks totally. + + @return true for internal mode, false otherwise + */ + bool internal() const { return m_internal; } + + /** + Tell whether the input json is binary (WL#8132) or standard. + + Types are exact in binary format, while being loose in standard format. + As a result, value extraction for standard format needs more adpation. + + @return true for binary JSON, false otherwise + */ + bool binary() const { return m_binary; } + + private: + /// Thread context for error handlers + THD *m_thd; + /// The field for checking endpoint values + Field *m_field; + /// Where reported errors are stored + results_map *m_results; + /// Internal mode to bypass post checks + bool m_internal; + /// Whether or not the JSON object to process is in binary format + bool m_binary; +}; + +/** Histogram base class. */ class Histogram { @@ -182,6 +319,15 @@ class Histogram { } /** + Epsilon to defend error in adding float values in build_histogram(). + + See BUG#104108 cumulative frequency may grow over 1.0 due to float errors. + There might be persisted histogram data with such float errors, so + json_to_histogram() needs to be loose with this check. + */ + static constexpr double FREQUENCY_EPSILON = 1e-7; + + /** Write the data type of this histogram into a JSON object. @param json_object the JSON object where we will write the histogram @@ -200,12 +346,14 @@ class Histogram { histogram object. @param json_dom the JSON DOM object to extract the value from - @param out the value from the JSON DOM object + @param out the value from the JSON DOM object + @param context error context for validation @return true on error, false otherwise */ template - bool extract_json_dom_value(const Json_dom *json_dom, T *out); + bool extract_json_dom_value(const Json_dom *json_dom, T *out, + Error_context *context); /** Populate the histogram with data from the provided JSON object. The base @@ -213,11 +361,13 @@ class Histogram { to populate fields that are shared among all histogram types (character set, null values fraction). - @param json_object the JSON object to read the histogram data from + @param json_object the JSON object to read the histogram data from + @param context error context for validation @return true on error, false otherwise */ - virtual bool json_to_histogram(const Json_object &json_object) = 0; + virtual bool json_to_histogram(const Json_object &json_object, + Error_context *context) = 0; private: /// The MEM_ROOT where the histogram contents will be allocated. @@ -406,6 +556,7 @@ class Histogram { @param table_name the table name @param column_name the column name @param json_object output where the histogram is stored + @param context error context for validation @return nullptr on error. Otherwise a histogram allocated on the provided MEM_ROOT. @@ -414,7 +565,8 @@ class Histogram { const std::string &schema_name, const std::string &table_name, const std::string &column_name, - const Json_object &json_object); + const Json_object &json_object, + Error_context *context); /** Make a clone of the current histogram @@ -516,12 +668,13 @@ Histogram *build_histogram(MEM_ROOT *mem_root, const Value_map &value_map, @param columns Columns specified by the user. @param num_buckets The maximum number of buckets to create in each histogram. + @param data The histogram json literal for update @param results A map where the result of each operation is stored. @return false on success, true on error. */ bool update_histogram(THD *thd, TABLE_LIST *table, const columns_set &columns, - int num_buckets, results_map &results); + int num_buckets, LEX_STRING data, results_map &results); /** Drop histograms for all columns in a given table. diff --git a/sql/histograms/singleton.cc b/sql/histograms/singleton.cc index 7b89be6..bd34d68 100644 --- a/sql/histograms/singleton.cc +++ b/sql/histograms/singleton.cc @@ -117,7 +117,7 @@ bool Singleton::build_histogram(const Value_map &value_map, value_map.get_num_null_values() / static_cast(total_count); // Create buckets with relative frequency, and not absolute frequency. - double cumulative_frequency = 0.0; + ha_rows cumulative_sum = 0; /* Since we are using a std::map with Mem_root_allocator, we are forced to wrap @@ -126,8 +126,9 @@ bool Singleton::build_histogram(const Value_map &value_map, */ try { for (const auto &node : value_map) { - const double frequency = node.second / static_cast(total_count); - cumulative_frequency += frequency; + cumulative_sum += node.second; + const double cumulative_frequency = + cumulative_sum / static_cast(total_count); m_buckets.emplace(node.first, cumulative_frequency); } @@ -260,40 +261,120 @@ std::string Singleton::histogram_type_to_str() const { } template -bool Singleton::json_to_histogram(const Json_object &json_object) { - if (Histogram::json_to_histogram(json_object)) - return true; /* purecov: deadcode */ +bool Singleton::json_to_histogram(const Json_object &json_object, + Error_context *context) { + if (Histogram::json_to_histogram(json_object, context)) return true; const Json_dom *buckets_dom = json_object.get(buckets_str()); - if (buckets_dom == nullptr || - buckets_dom->json_type() != enum_json_type::J_ARRAY) - return true; /* purecov: deadcode */ + if (buckets_dom == nullptr) { + context->report_missing_attribute(Histogram::buckets_str()); + return true; + } + if (buckets_dom->json_type() != enum_json_type::J_ARRAY) { + context->report_node(buckets_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } const Json_array *buckets = down_cast(buckets_dom); for (size_t i = 0; i < buckets->size(); ++i) { const Json_dom *bucket_dom = (*buckets)[i]; if (bucket_dom == nullptr || - bucket_dom->json_type() != enum_json_type::J_ARRAY) - return true; /* purecov: deadcode */ + bucket_dom->json_type() != enum_json_type::J_ARRAY) { + context->report_node(bucket_dom, Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } const Json_array *bucket = down_cast(bucket_dom); - if (bucket->size() != 2) return true; /* purecov: deadcode */ + assert(!context->binary() || bucket->size() == 2); + // Only the first two items are defined, others are simply ignored. + if (bucket->size() < 2) { + context->report_node(bucket_dom, Message::JSON_WRONG_BUCKET_TYPE_2); + return true; + } // First item is the value, second is the cumulative frequency const Json_dom *cumulative_frequency_dom = (*bucket)[1]; - if (cumulative_frequency_dom->json_type() != enum_json_type::J_DOUBLE) - return true; /* purecov: deadcode */ + if (cumulative_frequency_dom->json_type() != enum_json_type::J_DOUBLE) { + context->report_node(cumulative_frequency_dom, + Message::JSON_WRONG_ATTRIBUTE_TYPE); + return true; + } const Json_double *cumulative_frequency = down_cast(cumulative_frequency_dom); const Json_dom *value_dom = (*bucket)[0]; T value; - if (extract_json_dom_value(value_dom, &value)) - return true; /* purecov: deadcode */ + if (extract_json_dom_value(value_dom, &value, context)) return true; + + // Bucket extraction post-check +#ifdef DBUG_OFF + if (!context->internal()) +#endif + { + /* + Check items in the bucket. + + Using json data built from a server without fix to BUG#104108, would + break the strict check 'cumulative_frequency->value() > 1.0'. To help + migration of histograms across servers, the strict check should be + loosened. + + Without touching real data, endpoint values can only be checked against + the field definition. + */ + if (cumulative_frequency->value() < 0.0 || + cumulative_frequency->value() > 1.0) { + context->report_node(cumulative_frequency_dom, + Message::JSON_INVALID_FREQUENCY); + return true; + } + if (context->check_value(&value)) { + context->report_node(value_dom, Message::JSON_VALUE_OUT_OF_RANGE); + return true; + } + + // Check endpoint sequence and frequency sequence. + if (!m_buckets.empty()) { + if (!histograms::Histogram_comparator()(m_buckets.rbegin()->first, + value)) { + context->report_node(value_dom, Message::JSON_VALUE_NOT_ASCENDING_1); + return true; + } + if (m_buckets.rbegin()->second >= cumulative_frequency->value()) { + context->report_node( + cumulative_frequency_dom, + Message::JSON_CUMULATIVE_FREQUENCY_NOT_ASCENDING); + return true; + } + } + } m_buckets.emplace(value, cumulative_frequency->value()); } + + // Global post-check +#ifdef DBUG_OFF + if (!context->internal()) +#endif + { + /* + Note that Singleton may be built on an empty table or an all-NULL + column. In this case the buckets array is empty. + */ + if (m_buckets.empty()) { + if (get_null_values_fraction() != 1.0 && + get_null_values_fraction() != 0.0) { + context->report_global(Message::JSON_INVALID_TOTAL_FREQUENCY); + return true; + } + } else if (std::abs(m_buckets.rbegin()->second + + get_null_values_fraction() - 1.0) > FREQUENCY_EPSILON) { + context->report_global(Message::JSON_INVALID_TOTAL_FREQUENCY); + return true; + } + } + return false; } diff --git a/sql/histograms/singleton.h b/sql/histograms/singleton.h index 31c8bcf..5bc67b0 100644 --- a/sql/histograms/singleton.h +++ b/sql/histograms/singleton.h @@ -254,11 +254,13 @@ class Singleton : public Histogram { /** Populate this histogram with contents from a JSON object. - @param json_object a JSON object that represents an Singleton histogram + @param json_object a JSON object that represents an Singleton histogram + @param context error context for validation @return true on error, false otherwise. */ - bool json_to_histogram(const Json_object &json_object) override; + bool json_to_histogram(const Json_object &json_object, + Error_context *context) override; }; } // namespace histograms diff --git a/sql/json_dom.cc b/sql/json_dom.cc index f027ed0..60024e0 100644 --- a/sql/json_dom.cc +++ b/sql/json_dom.cc @@ -1961,7 +1961,7 @@ bool Json_wrapper::get_boolean() const { } #ifdef MYSQL_SERVER -Json_path Json_dom::get_location() { +Json_path Json_dom::get_location() const { if (m_parent == nullptr) { Json_path result; return result; diff --git a/sql/json_dom.h b/sql/json_dom.h index b2bcdc3..6e65677 100644 --- a/sql/json_dom.h +++ b/sql/json_dom.h @@ -292,7 +292,7 @@ class Json_dom { Get the path location of this dom, measured from the outermost document it nests inside. */ - Json_path get_location(); + Json_path get_location() const; /** Finds all of the json sub-documents which match the path expression. diff --git a/sql/parse_tree_nodes.cc b/sql/parse_tree_nodes.cc index d25cd95..6a09ee4 100644 --- a/sql/parse_tree_nodes.cc +++ b/sql/parse_tree_nodes.cc @@ -2791,7 +2791,7 @@ Sql_cmd *PT_analyze_table_stmt::make_cmd(THD *thd) { thd->lex->alter_info = &m_alter_info; auto cmd = new (thd->mem_root) - Sql_cmd_analyze_table(thd, &m_alter_info, m_command, m_num_buckets); + Sql_cmd_analyze_table(thd, &m_alter_info, m_command, m_num_buckets, m_data); if (cmd == nullptr) return nullptr; if (m_command != Sql_cmd_analyze_table::Histogram_command::NONE) { if (cmd->set_histogram_fields(m_columns)) return nullptr; diff --git a/sql/parse_tree_nodes.h b/sql/parse_tree_nodes.h index 09a05a7..a64fbce 100644 --- a/sql/parse_tree_nodes.h +++ b/sql/parse_tree_nodes.h @@ -4653,13 +4653,15 @@ class PT_analyze_table_stmt final : public PT_table_ddl_stmt_base { PT_analyze_table_stmt(MEM_ROOT *mem_root, bool no_write_to_binlog, Mem_root_array *table_list, Sql_cmd_analyze_table::Histogram_command command, - int num_buckets, List *columns) + int num_buckets, List *columns, + LEX_STRING data) : PT_table_ddl_stmt_base(mem_root), m_no_write_to_binlog(no_write_to_binlog), m_table_list(table_list), m_command(command), m_num_buckets(num_buckets), - m_columns(columns) {} + m_columns(columns), + m_data{data} {} Sql_cmd *make_cmd(THD *thd) override; @@ -4669,6 +4671,7 @@ class PT_analyze_table_stmt final : public PT_table_ddl_stmt_base { const Sql_cmd_analyze_table::Histogram_command m_command; const int m_num_buckets; List *m_columns; + const LEX_STRING m_data; }; class PT_check_table_stmt final : public PT_table_ddl_stmt_base { diff --git a/sql/parser_yystype.h b/sql/parser_yystype.h index 2c810b7..f75c41d 100644 --- a/sql/parser_yystype.h +++ b/sql/parser_yystype.h @@ -600,10 +600,14 @@ union YYSTYPE { Item *where; } wild_or_where; Show_cmd_type show_cmd_type; + struct Histogram_param { + int num_buckets; + LEX_STRING data; + } histogram_param; struct { Sql_cmd_analyze_table::Histogram_command command; List *columns; - int num_buckets; + Histogram_param *param; } histogram; Acl_type acl_type; Mem_root_array *lex_cstring_list; diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index 2e6c988..30173cf 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -290,12 +290,13 @@ static inline bool table_not_corrupt_error(uint sql_errno) { Sql_cmd_analyze_table::Sql_cmd_analyze_table( THD *thd, Alter_info *alter_info, Histogram_command histogram_command, - int histogram_buckets) + int histogram_buckets, LEX_STRING data) : Sql_cmd_ddl_table(alter_info), m_histogram_command(histogram_command), m_histogram_fields(Column_name_comparator(), Mem_root_allocator(thd->mem_root)), - m_histogram_buckets(histogram_buckets) {} + m_histogram_buckets(histogram_buckets), + m_data{data} {} bool Sql_cmd_analyze_table::drop_histogram(THD *thd, TABLE_LIST *table, histograms::results_map &results) { @@ -423,6 +424,12 @@ bool Sql_cmd_analyze_table::send_histogram_results( "statistics."); table_name = ""; break; + case histograms::Message::MULTIPLE_COLUMNS_SPECIFIED: + message_type.assign("Error"); + message.assign( + "Only one column can be specified while modifying histogram " + "statistics with JSON data."); + break; case histograms::Message::COVERED_BY_SINGLE_PART_UNIQUE_INDEX: message_type.assign("Error"); message.assign("The column '"); @@ -440,6 +447,146 @@ bool Sql_cmd_analyze_table::send_histogram_results( message.assign("The server is in read-only mode."); table_name = ""; break; + case histograms::Message::JSON_FORMAT_ERROR: + message_type.assign("Error"); + message.assign("JSON format error."); + break; + case histograms::Message::JSON_NOT_AN_OBJECT: + message_type.assign("Error"); + message.assign("JSON data is not an object"); + break; + case histograms::Message::JSON_MISSING_ATTRIBUTE: + message_type.assign("Error"); + message.assign("Missing attribute at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_WRONG_ATTRIBUTE_TYPE: + message_type.assign("Error"); + message.assign("Wrong attribute type at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_WRONG_BUCKET_TYPE_2: + message_type.assign("Error"); + message.assign("Two elements required for bucket at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_WRONG_BUCKET_TYPE_4: + message_type.assign("Error"); + message.assign("Four elements required for bucket at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_UNSUPPORTED_DATA_TYPE: + message_type.assign("Error"); + message.assign("Unsupported data type at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_UNSUPPORTED_HISTOGRAM_TYPE: + message_type.assign("Error"); + message.assign("Unsupported histogram type at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_UNSUPPORTED_CHARSET: + message_type.assign("Error"); + message.assign("The charest id does not exists at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_INVALID_SAMPLING_RATE: + message_type.assign("Error"); + message.assign( + "The sampling rate must be greater than or equal to 0 and " + "less than or equal to 1 at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_INVALID_NUM_BUCKETS_SPECIFIED: + message_type.assign("Error"); + message.assign( + "The value of attribute number-of-buckets-specified must be an " + "integer in the range from 1 to 1024 at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_INVALID_FREQUENCY: + message_type.assign("Error"); + message.assign( + "The frequency must be greater than or equal to 0 and " + "less than or equal to 1 at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_INVALID_NUM_DISTINCT: + message_type.assign("Error"); + message.assign( + "The number of distinct value must be a positive integer at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_VALUE_FORMAT_ERROR: + message_type.assign("Error"); + message.assign("Value format error at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_VALUE_OUT_OF_RANGE: + message_type.assign("Error"); + message.assign("Out of range value for column at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_VALUE_NOT_ASCENDING_1: + message_type.assign("Error"); + message.assign( + "The value must be greater than that of previous bucket at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_VALUE_NOT_ASCENDING_2: + message_type.assign("Error"); + message.assign( + "The lower inclusive value of a bucket must be greater than the " + "upper inclusive value of previous bucket at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_VALUE_DESCENDING_IN_BUCKET: + message_type.assign("Error"); + message.assign( + "The lower inclusive value of a bucket must be less than or equal " + "to upper inclusive value at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_CUMULATIVE_FREQUENCY_NOT_ASCENDING: + message_type.assign("Error"); + message.assign( + "The cumulative frequency must be greater than that of previous " + "bucket at '"); + message.append(pair.first); + message.append("'."); + break; + case histograms::Message::JSON_INVALID_TOTAL_FREQUENCY: + message_type.assign("Error"); + message.assign( + "The sum of the null values fraction and the cumulative frequency " + "of the last bucket should be 1 or 0.'"); + break; + case histograms::Message::JSON_NUM_BUCKETS_MORE_THAN_SPECIFIED: + message_type.assign("Error"); + message.assign( + "The number of real buckets must be less than or equal to the " + "number specified by attribute number-of-buckets-specified."); + break; + case histograms::Message::JSON_IMPOSSIBLE_EMPTY_EQUI_HEIGHT: + message_type.assign("Error"); + message.assign( "Equi-height histogram must have some buckets"); + break; } protocol->start_row(); @@ -466,7 +613,8 @@ bool Sql_cmd_analyze_table::update_histogram(THD *thd, TABLE_LIST *table, fields.emplace(column->ptr(), column->length()); return histograms::update_histogram(thd, table, fields, - get_histogram_buckets(), results); + get_histogram_buckets(), + get_histogram_data_string(), results); } using Check_result = std::pair; diff --git a/sql/sql_admin.h b/sql/sql_admin.h index 30571dc..650a4d9 100644 --- a/sql/sql_admin.h +++ b/sql/sql_admin.h @@ -78,7 +78,7 @@ class Sql_cmd_analyze_table : public Sql_cmd_ddl_table { */ Sql_cmd_analyze_table(THD *thd, Alter_info *alter_info, Histogram_command histogram_command, - int histogram_buckets); + int histogram_buckets, LEX_STRING data); bool execute(THD *thd) override; @@ -103,6 +103,9 @@ class Sql_cmd_analyze_table : public Sql_cmd_ddl_table { /// The number of buckets specified by the user in UPDATE HISTOGRAM int m_histogram_buckets; + /// The histogram json literal for update + const LEX_STRING m_data; + /// @return The histogram command specified, if any. Histogram_command get_histogram_command() const { return m_histogram_command; @@ -111,6 +114,9 @@ class Sql_cmd_analyze_table : public Sql_cmd_ddl_table { /// @return The number of buckets specified in UPDATE HISTOGRAM. int get_histogram_buckets() const { return m_histogram_buckets; } + /// @return The histogram json literal specified in UPDATE HISTOGRAM. + LEX_STRING get_histogram_data_string() const { return m_data; } + /// @return The fields specified in UPDATE/DROP HISTOGRAM const columns_set &get_histogram_fields() const { return m_histogram_fields; } diff --git a/sql/sql_partition_admin.h b/sql/sql_partition_admin.h index ed24470..4f947b8 100644 --- a/sql/sql_partition_admin.h +++ b/sql/sql_partition_admin.h @@ -55,7 +55,7 @@ class Sql_cmd_alter_table_analyze_partition final Constructor, used to represent a ALTER TABLE ANALYZE PARTITION statement. */ Sql_cmd_alter_table_analyze_partition(THD *thd, Alter_info *alter_info) - : Sql_cmd_analyze_table(thd, alter_info, Histogram_command::NONE, 0) {} + : Sql_cmd_analyze_table(thd, alter_info, Histogram_command::NONE, 0, {nullptr, 0}) {} ~Sql_cmd_alter_table_analyze_partition() override = default; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index fc0a57f..2d055c8 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1463,7 +1463,6 @@ void warn_about_deprecated_binary(THD *thd) trg_action_time trg_event view_check_option signed_num - opt_num_buckets %type @@ -2052,6 +2051,7 @@ void warn_about_deprecated_binary(THD *thd) %type jt_column_type %type opt_acl_type +%type opt_histogram_update_param %type opt_histogram %type column_list opt_column_list @@ -9453,14 +9453,24 @@ analyze_table_stmt: ANALYZE_SYM opt_no_write_to_binlog table_or_tables table_list opt_histogram { - $$= NEW_PTN PT_analyze_table_stmt(YYMEM_ROOT, $2, $4, - $5.command, $5.num_buckets, - $5.columns); + if ($5.param) { + $$= NEW_PTN PT_analyze_table_stmt(YYMEM_ROOT, $2, $4, + $5.command, $5.param->num_buckets, + $5.columns, $5.param->data); + } else { + $$= NEW_PTN PT_analyze_table_stmt(YYMEM_ROOT, $2, $4, + $5.command, 0, + $5.columns, {nullptr, 0}); + } } ; -opt_num_buckets: - /* empty */ { $$= DEFAULT_NUMBER_OF_HISTOGRAM_BUCKETS; } +opt_histogram_update_param: + /* empty */ + { + $$.num_buckets= DEFAULT_NUMBER_OF_HISTOGRAM_BUCKETS; + $$.data= { nullptr, 0 }; + } | WITH NUM BUCKETS_SYM { int error; @@ -9474,7 +9484,13 @@ opt_num_buckets: MYSQL_YYABORT; } - $$= num; + $$.num_buckets= num; + $$.data= { nullptr, 0 }; + } + | USING DATA_SYM TEXT_STRING_literal + { + $$.num_buckets= 0; + $$.data= $3; } ; @@ -9483,21 +9499,23 @@ opt_histogram: { $$.command= Sql_cmd_analyze_table::Histogram_command::NONE; $$.columns= nullptr; - $$.num_buckets= 0; + $$.param= nullptr; } - | UPDATE_SYM HISTOGRAM_SYM ON_SYM ident_string_list opt_num_buckets + | UPDATE_SYM HISTOGRAM_SYM ON_SYM ident_string_list opt_histogram_update_param { $$.command= Sql_cmd_analyze_table::Histogram_command::UPDATE_HISTOGRAM; $$.columns= $4; - $$.num_buckets= $5; + $$.param= NEW_PTN YYSTYPE::Histogram_param($5); + if ($$.param == nullptr) + MYSQL_YYABORT; // OOM } | DROP HISTOGRAM_SYM ON_SYM ident_string_list { $$.command= Sql_cmd_analyze_table::Histogram_command::DROP_HISTOGRAM; $$.columns= $4; - $$.num_buckets= 0; + $$.param = nullptr; } ; -- 1.8.3.1