revision-id: 17e6e908cec7544878b78e23985564bd27453c89 (mariadb-10.6.1-99-g17e6e908cec) parent(s): 02ca278e695535fbd2a43e392276f030c3617207 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-08-29 14:37:45 +0300 message: Fix JSON parsing: future-proof data representation in JSON, code cleanup --- include/json_lib.h | 13 +- mysql-test/main/statistics_json.result | 2407 ++++++++++++++++---------------- mysql-test/main/statistics_json.test | 22 +- sql/share/errmsg-utf8.txt | 2 +- sql/sql_statistics.cc | 177 +-- sql/sql_statistics.h | 16 +- 6 files changed, 1349 insertions(+), 1288 deletions(-) diff --git a/include/json_lib.h b/include/json_lib.h index 2248b1a9388..e9f3deea415 100644 --- a/include/json_lib.h +++ b/include/json_lib.h @@ -283,12 +283,13 @@ int json_key_matches(json_engine_t *je, json_string_t *k); int json_read_value(json_engine_t *j); /* - * json_smart_read_value() reads parses a scalar value and value length from the json engine, - * and copies them into `value` and `value_length` respectively. - * It should only be called when the json_engine state is JST_VALUE. - * If it encounters a non-scalar value (say object or array) before getting to value_len, - * such value is also read and copied into value. - */ + json_smart_read_value() reads a JSON value. Pointer to value is stored in + *value and its length in *value_len. + + if the value is non a scalar, it returns pointers to its JSON + representation. + The function should only be called when je->state==JST_VALUE. +*/ enum json_types json_smart_read_value(json_engine_t *je, const char **value, int *value_len); /* diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result index 97931026690..0293030e6a2 100644 --- a/mysql-test/main/statistics_json.result +++ b/mysql-test/main/statistics_json.result @@ -232,12 +232,12 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t1 a 0 49 0.0000 1.0000 4 JSON_HB 5B0A20202239222C0A2020223139222C0A2020223331222C0A2020223430220A5D -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 4 JSON_HB 5B0A20202276767676767676767676767676222C0A20202277777777777777777777777777777777777777777777777777777777222C0A202022797979222C0A2020227A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A220A5D -test t1 c aaaa dddddddd 0.1250 7.0000 4 JSON_HB 5B0A20202261616161222C0A202022626262626262222C0A202022636363636363636363222C0A2020226464646464646464220A5D -test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 4 JSON_HB 5B0A202022313938392D30332D3132222C0A202022313939302D30352D3135222C0A202022313939302D30352D3135222C0A202022313939392D30372D3233220A5D -test t1 e 0.01 0.112 0.2250 6.2000 4 JSON_HB 5B0A202022302E3031222C0A202022302E303132222C0A202022302E3035222C0A202022302E31220A5D -test t1 f 1 5 0.2000 6.4000 4 JSON_HB 5B0A20202202222C0A20202203222C0A20202204222C0A20202204220A5D +test t1 a 0 49 0.0000 1.0000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2239222C20223139222C20223331222C20223430225D0A7D +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202276767676767676767676767676222C0A202020202277777777777777777777777777777777777777777777777777777777222C0A2020202022797979222C0A20202020227A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A220A20205D0A7D +test t1 c aaaa dddddddd 0.1250 7.0000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2261616161222C2022626262626262222C2022636363636363636363222C20226464646464646464225D0A7D +test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B22313938392D30332D3132222C2022313939302D30352D3135222C2022313939302D30352D3135222C2022313939392D30372D3233225D0A7D +test t1 e 0.01 0.112 0.2250 6.2000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B22302E3031222C2022302E303132222C2022302E3035222C2022302E31225D0A7D +test t1 f 1 5 0.2000 6.4000 4 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2202222C202203222C202204222C202204225D0A7D DELETE FROM mysql.column_stats; set histogram_size=8; set histogram_type=@DOUBLE_PREC_TYPE; @@ -251,12 +251,12 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t1 a 0 49 0.0000 1.0000 8 JSON_HB 5B0A20202234222C0A20202239222C0A2020223135222C0A2020223231222C0A2020223239222C0A2020223333222C0A2020223339222C0A2020223433220A5D -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 8 JSON_HB 5B0A20202276767676767676767676767676222C0A20202276767676767676767676767676222C0A20202277777777777777777777777777777777777777777777777777777777222C0A20202277777777777777777777777777777777777777777777777777777777222C0A2020227878787878787878787878787878787878787878787878787878222C0A202022797979222C0A202022797979222C0A2020227A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A220A5D -test t1 c aaaa dddddddd 0.1250 7.0000 8 JSON_HB 5B0A20202261616161222C0A20202261616161222C0A202022626262626262222C0A202022626262626262222C0A202022636363636363636363222C0A202022636363636363636363222C0A2020226464646464646464222C0A2020226464646464646464220A5D -test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 8 JSON_HB 5B0A202022313938392D30332D3132222C0A202022313938392D30332D3132222C0A202022313939302D30352D3135222C0A202022313939302D30352D3135222C0A202022313939302D30352D3135222C0A202022313939302D30352D3135222C0A202022313939392D30372D3233222C0A202022313939392D30372D3233220A5D -test t1 e 0.01 0.112 0.2250 6.2000 8 JSON_HB 5B0A202022302E3031222C0A202022302E3031222C0A202022302E3031222C0A202022302E303132222C0A202022302E3035222C0A202022302E31222C0A202022302E31222C0A202022302E313132220A5D -test t1 f 1 5 0.2000 6.4000 8 JSON_HB 5B0A20202201222C0A20202202222C0A20202202222C0A20202203222C0A20202203222C0A20202204222C0A20202204222C0A20202205220A5D +test t1 a 0 49 0.0000 1.0000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2234222C202239222C20223135222C20223231222C20223239222C20223333222C20223339222C20223433225D0A7D +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202276767676767676767676767676222C0A202020202276767676767676767676767676222C0A202020202277777777777777777777777777777777777777777777777777777777222C0A202020202277777777777777777777777777777777777777777777777777777777222C0A20202020227878787878787878787878787878787878787878787878787878222C0A2020202022797979222C0A2020202022797979222C0A20202020227A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A7A220A20205D0A7D +test t1 c aaaa dddddddd 0.1250 7.0000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202261616161222C0A202020202261616161222C0A2020202022626262626262222C0A2020202022626262626262222C0A2020202022636363636363636363222C0A2020202022636363636363636363222C0A20202020226464646464646464222C0A20202020226464646464646464220A20205D0A7D +test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022313938392D30332D3132222C0A2020202022313938392D30332D3132222C0A2020202022313939302D30352D3135222C0A2020202022313939302D30352D3135222C0A2020202022313939302D30352D3135222C0A2020202022313939302D30352D3135222C0A2020202022313939392D30372D3233222C0A2020202022313939392D30372D3233220A20205D0A7D +test t1 e 0.01 0.112 0.2250 6.2000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022302E3031222C0A2020202022302E3031222C0A2020202022302E3031222C0A2020202022302E303132222C0A2020202022302E3035222C0A2020202022302E31222C0A2020202022302E31222C0A2020202022302E313132220A20205D0A7D +test t1 f 1 5 0.2000 6.4000 8 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2201222C202202222C202202222C202203222C202203222C202204222C202204222C202205225D0A7D DELETE FROM mysql.column_stats; set histogram_size= 0; set histogram_type=@SINGLE_PREC_TYPE; @@ -1502,109 +1502,111 @@ avg_length 4.0000 avg_frequency 2.7640 hist_size 100 hist_type JSON_HB -hex(histogram) 5B0A202022302E30222C0A202022302E30222C0A202022302E30222C0A202022302E30222C0A202022302E30222C0A202022302E30222C0A202022302E31222C0A202022302E31222C0A202022302E32222C0A202022302E32222C0A202022302E33222C0A202022302E33222C0A202022302E34222C0A202022302E34222C0A202022302E34222C0A202022302E35222C0A202022302E35222C0A202022302E36222C0A202022302E36222C0A202022302E37222C0A202022302E37222C0A202022302E38222C0A202022302E39222C0A202022312E31222C0A202022312E32222C0A202022312E33222C0A202022312E34222C0A202022312E34222C0A202022312E36222C0A202022312E36222C0A202022312E37222C0A202022312E39222C0A202022322E30222C0A202022322E32222C0A202022322E32222C0A202022322E33222C0A202022322E35222C0A202022322E36222C0A202022322E38222C0A202022322E39222C0A202022332E31222C0A202022332E32222C0A202022332E34222C0A202022332E36222C0A202022332E38222C0A202022342E30222C0A202022342E33222C0A202022342E35222C0A202022342E38222C0A202022352E31222C0A202022352E34222C0A202022352E37222C0A202022352E38222C0A202022362E31222C0A202022 362E34222C0A202022362E38222C0A202022372E32222C0A202022372E35222C0A202022372E37222C0A202022382E31222C0A202022382E35222C0A202022382E38222C0A202022392E31222C0A202022392E35222C0A20202231302E31222C0A20202231302E38222C0A20202231312E33222C0A20202231322E30222C0A20202231322E36222C0A20202231332E35222C0A20202231342E32222C0A20202231362E31222C0A20202231362E38222C0A20202231382E31222C0A20202232302E30222C0A20202232312E36222C0A20202232332E34222C0A20202232382E33222C0A20202233312E37222C0A20202233342E31222C0A20202233372E35222C0A20202234312E39222C0A20202234362E34222C0A20202235302E37222C0A20202235352E31222C0A20202236302E34222C0A20202236352E36222C0A20202237322E36222C0A20202237372E31222C0A20202238312E32222C0A20202238352E36222C0A20202238372E37222C0A20202238392E39222C0A20202239322E31222C0A20202239342E32222C0A20202239352E39222C0A20202239372E33222C0A20202239382E31222C0A20202239392E30222C0A20202239392E39220A5D -decode_histogram(hist_type,histogram) [ - "0.0", - "0.0", - "0.0", - "0.0", - "0.0", - "0.0", - "0.1", - "0.1", - "0.2", - "0.2", - "0.3", - "0.3", - "0.4", - "0.4", - "0.4", - "0.5", - "0.5", - "0.6", - "0.6", - "0.7", - "0.7", - "0.8", - "0.9", - "1.1", - "1.2", - "1.3", - "1.4", - "1.4", - "1.6", - "1.6", - "1.7", - "1.9", - "2.0", - "2.2", - "2.2", - "2.3", - "2.5", - "2.6", - "2.8", - "2.9", - "3.1", - "3.2", - "3.4", - "3.6", - "3.8", - "4.0", - "4.3", - "4.5", - "4.8", - "5.1", - "5.4", - "5.7", - "5.8", - "6.1", - "6.4", - "6.8", - "7.2", - "7.5", - "7.7", - "8.1", - "8.5", - "8.8", - "9.1", - "9.5", - "10.1", - "10.8", - "11.3", - "12.0", - "12.6", - "13.5", - "14.2", - "16.1", - "16.8", - "18.1", - "20.0", - "21.6", - "23.4", - "28.3", - "31.7", - "34.1", - "37.5", - "41.9", - "46.4", - "50.7", - "55.1", - "60.4", - "65.6", - "72.6", - "77.1", - "81.2", - "85.6", - "87.7", - "89.9", - "92.1", - "94.2", - "95.9", - "97.3", - "98.1", - "99.0", - "99.9" -] +hex(histogram) 7B0A202022686973746F6772616D5F68625F7631223A205B0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E30222C0A2020202022302E31222C0A2020202022302E31222C0A2020202022302E32222C0A2020202022302E32222C0A2020202022302E33222C0A2020202022302E33222C0A2020202022302E34222C0A2020202022302E34222C0A2020202022302E34222C0A2020202022302E35222C0A2020202022302E35222C0A2020202022302E36222C0A2020202022302E36222C0A2020202022302E37222C0A2020202022302E37222C0A2020202022302E38222C0A2020202022302E39222C0A2020202022312E31222C0A2020202022312E32222C0A2020202022312E33222C0A2020202022312E34222C0A2020202022312E34222C0A2020202022312E36222C0A2020202022312E36222C0A2020202022312E37222C0A2020202022312E39222C0A2020202022322E30222C0A2020202022322E32222C0A2020202022322E32222C0A2020202022322E33222C0A2020202022322E35222C0A2020202022322E36222C0A2020202022322E38222C0A2020202022322E39222C0A2020202022332E31222C0A2020202022332E32222C0A20202020 22332E34222C0A2020202022332E36222C0A2020202022332E38222C0A2020202022342E30222C0A2020202022342E33222C0A2020202022342E35222C0A2020202022342E38222C0A2020202022352E31222C0A2020202022352E34222C0A2020202022352E37222C0A2020202022352E38222C0A2020202022362E31222C0A2020202022362E34222C0A2020202022362E38222C0A2020202022372E32222C0A2020202022372E35222C0A2020202022372E37222C0A2020202022382E31222C0A2020202022382E35222C0A2020202022382E38222C0A2020202022392E31222C0A2020202022392E35222C0A202020202231302E31222C0A202020202231302E38222C0A202020202231312E33222C0A202020202231322E30222C0A202020202231322E36222C0A202020202231332E35222C0A202020202231342E32222C0A202020202231362E31222C0A202020202231362E38222C0A202020202231382E31222C0A202020202232302E30222C0A202020202232312E36222C0A202020202232332E34222C0A202020202232382E33222C0A202020202233312E37222C0A202020202233342E31222C0A202020202233372E35222C0A202020202234312E39222C0A202020202234362E34222C0A202020202235302E37222C0A202020202235352E31222C0A202020202236302E3 4222C0A202020202236352E36222C0A202020202237322E36222C0A202020202237372E31222C0A202020202238312E32222C0A202020202238352E36222C0A202020202238372E37222C0A202020202238392E39222C0A202020202239322E31222C0A202020202239342E32222C0A202020202239352E39222C0A202020202239372E33222C0A202020202239382E31222C0A202020202239392E30222C0A202020202239392E39220A20205D0A7D +decode_histogram(hist_type,histogram) { + "histogram_hb_v1": [ + "0.0", + "0.0", + "0.0", + "0.0", + "0.0", + "0.0", + "0.1", + "0.1", + "0.2", + "0.2", + "0.3", + "0.3", + "0.4", + "0.4", + "0.4", + "0.5", + "0.5", + "0.6", + "0.6", + "0.7", + "0.7", + "0.8", + "0.9", + "1.1", + "1.2", + "1.3", + "1.4", + "1.4", + "1.6", + "1.6", + "1.7", + "1.9", + "2.0", + "2.2", + "2.2", + "2.3", + "2.5", + "2.6", + "2.8", + "2.9", + "3.1", + "3.2", + "3.4", + "3.6", + "3.8", + "4.0", + "4.3", + "4.5", + "4.8", + "5.1", + "5.4", + "5.7", + "5.8", + "6.1", + "6.4", + "6.8", + "7.2", + "7.5", + "7.7", + "8.1", + "8.5", + "8.8", + "9.1", + "9.5", + "10.1", + "10.8", + "11.3", + "12.0", + "12.6", + "13.5", + "14.2", + "16.1", + "16.8", + "18.1", + "20.0", + "21.6", + "23.4", + "28.3", + "31.7", + "34.1", + "37.5", + "41.9", + "46.4", + "50.7", + "55.1", + "60.4", + "65.6", + "72.6", + "77.1", + "81.2", + "85.6", + "87.7", + "89.9", + "92.1", + "94.2", + "95.9", + "97.3", + "98.1", + "99.0", + "99.9" + ] +} select UPPER(db_name),UPPER(table_name),UPPER(column_name),min_value,max_value,nulls_ratio,avg_length,avg_frequency,hist_size,hist_type,hex(histogram),decode_histogram(hist_type,histogram) from mysql.column_stats where UPPER(db_name)='WORLD' and UPPER(table_name)='CITY' and UPPER(column_name) = 'POPULATION';; UPPER(db_name) WORLD UPPER(table_name) CITY @@ -1616,263 +1618,265 @@ avg_length 4.0000 avg_frequency 1.0467 hist_size 254 hist_type JSON_HB -hex(histogram) 5B0A20202231343338222C0A20202235303535222C0A2020223133303030222C0A2020223235383838222C0A2020223530363939222C0A2020223839303533222C0A2020223839333838222C0A2020223839383831222C0A2020223930313131222C0A2020223930363031222C0A2020223930393531222C0A2020223931323030222C0A2020223931373737222C0A2020223932323234222C0A2020223932353833222C0A2020223932393838222C0A2020223933333030222C0A2020223933383138222C0A2020223934313030222C0A2020223934363030222C0A2020223934393334222C0A2020223935343030222C0A2020223935393333222C0A2020223936323931222C0A2020223936383030222C0A2020223937313030222C0A2020223937343531222C0A2020223938303830222C0A2020223938333432222C0A2020223938373831222C0A2020223939333637222C0A2020223939373939222C0A202022313030313138222C0A202022313030343738222C0A202022313030383837222C0A202022313031323035222C0A202022313031353734222C0A202022313031393834222C0A202022313032323934222C0A202022313032363831222C0A202022313033313731222C0A202022313033353434222C0A202022313033393834222C0A20202231303437 3030222C0A202022313035313139222C0A202022313035363930222C0A202022313036303031222C0A202022313036343134222C0A202022313037303030222C0A202022313037333239222C0A202022313037373631222C0A202022313038313030222C0A202022313038353734222C0A202022313039313231222C0A202022313039353030222C0A202022313039393635222C0A202022313130333838222C0A202022313131313030222C0A202022313131373532222C0A202022313132333735222C0A202022313133303830222C0A202022313133383030222C0A202022313134323333222C0A202022313134383736222C0A202022313135353332222C0A202022313136313738222C0A202022313136363935222C0A202022313137323237222C0A202022313137383635222C0A202022313138373138222C0A202022313139323833222C0A202022313139373936222C0A202022313230323635222C0A202022313231303030222C0A202022313231363030222C0A202022313231393534222C0A202022313232373035222C0A202022313233333539222C0A202022313233383635222C0A202022313234323037222C0A202022313234373335222C0A202022313235323535222C0A202022313235373636222C0A202022313236323832222C0A202022313236383230222C0A202 022313237323232222C0A202022313237383031222C0A202022313238333030222C0A202022313239333030222C0A202022313330303030222C0A202022313331303030222C0A202022313331373137222C0A202022313332343535222C0A202022313333313036222C0A202022313333363432222C0A202022313334303337222C0A202022313335303130222C0A202022313336323136222C0A202022313337303238222C0A202022313337373736222C0A202022313338343138222C0A202022313339323833222C0A202022313430303330222C0A202022313430383030222C0A202022313432303434222C0A202022313432363539222C0A202022313433373236222C0A202022313434353832222C0A202022313435383030222C0A202022313436343339222C0A202022313437353233222C0A202022313438313035222C0A202022313439313436222C0A202022313530313030222C0A202022313531303630222C0A202022313532343432222C0A202022313533333634222C0A202022313534393830222C0A202022313535383030222C0A202022313537333030222C0A202022313538333335222C0A202022313539363332222C0A202022313631313631222C0A202022313632333030222C0A202022313633383439222C0A202022313634373437222C0A2020223136363436 37222C0A202022313637343631222C0A202022313639333030222C0A202022313730343238222C0A202022313731353332222C0A202022313732373031222C0A202022313733383935222C0A202022313734393835222C0A202022313736353736222C0A202022313738313832222C0A202022313739323038222C0A202022313830323130222C0A202022313831383035222C0A202022313833313030222C0A202022313834303631222C0A202022313835343031222C0A202022313836393030222C0A202022313838333434222C0A202022313839353639222C0A202022313930393035222C0A202022313933303035222C0A202022313934333030222C0A202022313935353030222C0A202022313937323534222C0A202022313939303030222C0A202022323030393031222C0A202022323032313334222C0A202022323033373933222C0A202022323036313538222C0A202022323037353838222C0A202022323130303638222C0A202022323132393736222C0A202022323134393530222C0A202022323136373335222C0A202022323138343437222C0A202022323231303030222C0A202022323232353138222C0A202022323234383837222C0A202022323237363537222C0A202022323239343235222C0A202022323333303431222C0A202022323336303030222C0A20202 2323339313234222C0A202022323431363439222C0A202022323433373432222C0A202022323435373732222C0A202022323438323435222C0A202022323532333836222C0A202022323534383432222C0A202022323537383132222C0A202022323632303030222C0A202022323634303831222C0A202022323636323831222C0A202022323730323531222C0A202022323732393638222C0A202022323736343231222C0A202022323739333430222C0A202022323832393431222C0A202022323836393030222C0A202022323931303030222C0A202022323934303536222C0A202022323938393030222C0A202022333031323736222C0A202022333034343737222C0A202022333039373530222C0A202022333133353330222C0A202022333137363030222C0A202022333232323637222C0A202022333236333939222C0A202022333330323736222C0A202022333334353633222C0A202022333339313331222C0A202022333432373338222C0A202022333439323436222C0A202022333533363332222C0A202022333539313437222C0A202022333632343730222C0A202022333636353439222C0A202022333732383430222C0A202022333830373535222C0A202022333835323031222C0A202022333932383330222C0A202022343030393937222C0A202022343039313030 222C0A202022343136393838222C0A202022343231353839222C0A202022343238353232222C0A202022343335393634222C0A202022343433373237222C0A202022343532393736222C0A202022343631303030222C0A202022343639373335222C0A202022343736383030222C0A202022343833313535222C0A202022343933343039222C0A202022353038383939222C0A202022353139373933222C0A202022353239393030222C0A202022353430383238222C0A202022353633363632222C0A202022353830303030222C0A202022353934353031222C0A202022363136373030222C0A202022363336373635222C0A202022363536393235222C0A202022363830333332222C0A202022373033353932222C0A202022373335313637222C0A202022373634393032222C0A202022373937373335222C0A202022383330303030222C0A202022383737323339222C0A202022393430353839222C0A202022393933343030222C0A20202231303432373430222C0A20202231313030303030222C0A20202231313536313030222C0A20202231323137383138222C0A20202231333030393737222C0A20202231333932383630222C0A20202231353137353530222C0A20202231363832303030222C0A20202231393639383638222C0A20202232313534333736222C0A20202232353 935363734222C0A20202232393634363338222C0A20202234323536333030222C0A20202236373538383435220A5D -decode_histogram(hist_type,histogram) [ - "1438", - "5055", - "13000", - "25888", - "50699", - "89053", - "89388", - "89881", - "90111", - "90601", - "90951", - "91200", - "91777", - "92224", - "92583", - "92988", - "93300", - "93818", - "94100", - "94600", - "94934", - "95400", - "95933", - "96291", - "96800", - "97100", - "97451", - "98080", - "98342", - "98781", - "99367", - "99799", - "100118", - "100478", - "100887", - "101205", - "101574", - "101984", - "102294", - "102681", - "103171", - "103544", - "103984", - "104700", - "105119", - "105690", - "106001", - "106414", - "107000", - "107329", - "107761", - "108100", - "108574", - "109121", - "109500", - "109965", - "110388", - "111100", - "111752", - "112375", - "113080", - "113800", - "114233", - "114876", - "115532", - "116178", - "116695", - "117227", - "117865", - "118718", - "119283", - "119796", - "120265", - "121000", - "121600", - "121954", - "122705", - "123359", - "123865", - "124207", - "124735", - "125255", - "125766", - "126282", - "126820", - "127222", - "127801", - "128300", - "129300", - "130000", - "131000", - "131717", - "132455", - "133106", - "133642", - "134037", - "135010", - "136216", - "137028", - "137776", - "138418", - "139283", - "140030", - "140800", - "142044", - "142659", - "143726", - "144582", - "145800", - "146439", - "147523", - "148105", - "149146", - "150100", - "151060", - "152442", - "153364", - "154980", - "155800", - "157300", - "158335", - "159632", - "161161", - "162300", - "163849", - "164747", - "166467", - "167461", - "169300", - "170428", - "171532", - "172701", - "173895", - "174985", - "176576", - "178182", - "179208", - "180210", - "181805", - "183100", - "184061", - "185401", - "186900", - "188344", - "189569", - "190905", - "193005", - "194300", - "195500", - "197254", - "199000", - "200901", - "202134", - "203793", - "206158", - "207588", - "210068", - "212976", - "214950", - "216735", - "218447", - "221000", - "222518", - "224887", - "227657", - "229425", - "233041", - "236000", - "239124", - "241649", - "243742", - "245772", - "248245", - "252386", - "254842", - "257812", - "262000", - "264081", - "266281", - "270251", - "272968", - "276421", - "279340", - "282941", - "286900", - "291000", - "294056", - "298900", - "301276", - "304477", - "309750", - "313530", - "317600", - "322267", - "326399", - "330276", - "334563", - "339131", - "342738", - "349246", - "353632", - "359147", - "362470", - "366549", - "372840", - "380755", - "385201", - "392830", - "400997", - "409100", - "416988", - "421589", - "428522", - "435964", - "443727", - "452976", - "461000", - "469735", - "476800", - "483155", - "493409", - "508899", - "519793", - "529900", - "540828", - "563662", - "580000", - "594501", - "616700", - "636765", - "656925", - "680332", - "703592", - "735167", - "764902", - "797735", - "830000", - "877239", - "940589", - "993400", - "1042740", - "1100000", - "1156100", - "1217818", - "1300977", - "1392860", - "1517550", - "1682000", - "1969868", - "2154376", - "2595674", - "2964638", - "4256300", - "6758845" -] +hex(histogram) 7B0A202022686973746F6772616D5F68625F7631223A205B0A202020202231343338222C0A202020202235303535222C0A20202020223133303030222C0A20202020223235383838222C0A20202020223530363939222C0A20202020223839303533222C0A20202020223839333838222C0A20202020223839383831222C0A20202020223930313131222C0A20202020223930363031222C0A20202020223930393531222C0A20202020223931323030222C0A20202020223931373737222C0A20202020223932323234222C0A20202020223932353833222C0A20202020223932393838222C0A20202020223933333030222C0A20202020223933383138222C0A20202020223934313030222C0A20202020223934363030222C0A20202020223934393334222C0A20202020223935343030222C0A20202020223935393333222C0A20202020223936323931222C0A20202020223936383030222C0A20202020223937313030222C0A20202020223937343531222C0A20202020223938303830222C0A20202020223938333432222C0A20202020223938373831222C0A20202020223939333637222C0A20202020223939373939222C0A2020202022313030313138222C0A2020202022313030343738222C0A2020202022313030383837222C0A20202020223130313230 35222C0A2020202022313031353734222C0A2020202022313031393834222C0A2020202022313032323934222C0A2020202022313032363831222C0A2020202022313033313731222C0A2020202022313033353434222C0A2020202022313033393834222C0A2020202022313034373030222C0A2020202022313035313139222C0A2020202022313035363930222C0A2020202022313036303031222C0A2020202022313036343134222C0A2020202022313037303030222C0A2020202022313037333239222C0A2020202022313037373631222C0A2020202022313038313030222C0A2020202022313038353734222C0A2020202022313039313231222C0A2020202022313039353030222C0A2020202022313039393635222C0A2020202022313130333838222C0A2020202022313131313030222C0A2020202022313131373532222C0A2020202022313132333735222C0A2020202022313133303830222C0A2020202022313133383030222C0A2020202022313134323333222C0A2020202022313134383736222C0A2020202022313135353332222C0A2020202022313136313738222C0A2020202022313136363935222C0A2020202022313137323237222C0A2020202022313137383635222C0A2020202022313138373138222C0A2020202022313139323833222C0A202020202 2313139373936222C0A2020202022313230323635222C0A2020202022313231303030222C0A2020202022313231363030222C0A2020202022313231393534222C0A2020202022313232373035222C0A2020202022313233333539222C0A2020202022313233383635222C0A2020202022313234323037222C0A2020202022313234373335222C0A2020202022313235323535222C0A2020202022313235373636222C0A2020202022313236323832222C0A2020202022313236383230222C0A2020202022313237323232222C0A2020202022313237383031222C0A2020202022313238333030222C0A2020202022313239333030222C0A2020202022313330303030222C0A2020202022313331303030222C0A2020202022313331373137222C0A2020202022313332343535222C0A2020202022313333313036222C0A2020202022313333363432222C0A2020202022313334303337222C0A2020202022313335303130222C0A2020202022313336323136222C0A2020202022313337303238222C0A2020202022313337373736222C0A2020202022313338343138222C0A2020202022313339323833222C0A2020202022313430303330222C0A2020202022313430383030222C0A2020202022313432303434222C0A2020202022313432363539222C0A2020202022313433373236222C 0A2020202022313434353832222C0A2020202022313435383030222C0A2020202022313436343339222C0A2020202022313437353233222C0A2020202022313438313035222C0A2020202022313439313436222C0A2020202022313530313030222C0A2020202022313531303630222C0A2020202022313532343432222C0A2020202022313533333634222C0A2020202022313534393830222C0A2020202022313535383030222C0A2020202022313537333030222C0A2020202022313538333335222C0A2020202022313539363332222C0A2020202022313631313631222C0A2020202022313632333030222C0A2020202022313633383439222C0A2020202022313634373437222C0A2020202022313636343637222C0A2020202022313637343631222C0A2020202022313639333030222C0A2020202022313730343238222C0A2020202022313731353332222C0A2020202022313732373031222C0A2020202022313733383935222C0A2020202022313734393835222C0A2020202022313736353736222C0A2020202022313738313832222C0A2020202022313739323038222C0A2020202022313830323130222C0A2020202022313831383035222C0A2020202022313833313030222C0A2020202022313834303631222C0A2020202022313835343031222C0A202020202231383 6393030222C0A2020202022313838333434222C0A2020202022313839353639222C0A2020202022313930393035222C0A2020202022313933303035222C0A2020202022313934333030222C0A2020202022313935353030222C0A2020202022313937323534222C0A2020202022313939303030222C0A2020202022323030393031222C0A2020202022323032313334222C0A2020202022323033373933222C0A2020202022323036313538222C0A2020202022323037353838222C0A2020202022323130303638222C0A2020202022323132393736222C0A2020202022323134393530222C0A2020202022323136373335222C0A2020202022323138343437222C0A2020202022323231303030222C0A2020202022323232353138222C0A2020202022323234383837222C0A2020202022323237363537222C0A2020202022323239343235222C0A2020202022323333303431222C0A2020202022323336303030222C0A2020202022323339313234222C0A2020202022323431363439222C0A2020202022323433373432222C0A2020202022323435373732222C0A2020202022323438323435222C0A2020202022323532333836222C0A2020202022323534383432222C0A2020202022323537383132222C0A2020202022323632303030222C0A2020202022323634303831222C0A2020 202022323636323831222C0A2020202022323730323531222C0A2020202022323732393638222C0A2020202022323736343231222C0A2020202022323739333430222C0A2020202022323832393431222C0A2020202022323836393030222C0A2020202022323931303030222C0A2020202022323934303536222C0A2020202022323938393030222C0A2020202022333031323736222C0A2020202022333034343737222C0A2020202022333039373530222C0A2020202022333133353330222C0A2020202022333137363030222C0A2020202022333232323637222C0A2020202022333236333939222C0A2020202022333330323736222C0A2020202022333334353633222C0A2020202022333339313331222C0A2020202022333432373338222C0A2020202022333439323436222C0A2020202022333533363332222C0A2020202022333539313437222C0A2020202022333632343730222C0A2020202022333636353439222C0A2020202022333732383430222C0A2020202022333830373535222C0A2020202022333835323031222C0A2020202022333932383330222C0A2020202022343030393937222C0A2020202022343039313030222C0A2020202022343136393838222C0A2020202022343231353839222C0A2020202022343238353232222C0A202020202234333539363 4222C0A2020202022343433373237222C0A2020202022343532393736222C0A2020202022343631303030222C0A2020202022343639373335222C0A2020202022343736383030222C0A2020202022343833313535222C0A2020202022343933343039222C0A2020202022353038383939222C0A2020202022353139373933222C0A2020202022353239393030222C0A2020202022353430383238222C0A2020202022353633363632222C0A2020202022353830303030222C0A2020202022353934353031222C0A2020202022363136373030222C0A2020202022363336373635222C0A2020202022363536393235222C0A2020202022363830333332222C0A2020202022373033353932222C0A2020202022373335313637222C0A2020202022373634393032222C0A2020202022373937373335222C0A2020202022383330303030222C0A2020202022383737323339222C0A2020202022393430353839222C0A2020202022393933343030222C0A202020202231303432373430222C0A202020202231313030303030222C0A202020202231313536313030222C0A202020202231323137383138222C0A202020202231333030393737222C0A202020202231333932383630222C0A202020202231353137353530222C0A202020202231363832303030222C0A2020202022313936393836 38222C0A202020202232313534333736222C0A202020202232353935363734222C0A202020202232393634363338222C0A202020202234323536333030222C0A202020202236373538383435220A20205D0A7D +decode_histogram(hist_type,histogram) { + "histogram_hb_v1": [ + "1438", + "5055", + "13000", + "25888", + "50699", + "89053", + "89388", + "89881", + "90111", + "90601", + "90951", + "91200", + "91777", + "92224", + "92583", + "92988", + "93300", + "93818", + "94100", + "94600", + "94934", + "95400", + "95933", + "96291", + "96800", + "97100", + "97451", + "98080", + "98342", + "98781", + "99367", + "99799", + "100118", + "100478", + "100887", + "101205", + "101574", + "101984", + "102294", + "102681", + "103171", + "103544", + "103984", + "104700", + "105119", + "105690", + "106001", + "106414", + "107000", + "107329", + "107761", + "108100", + "108574", + "109121", + "109500", + "109965", + "110388", + "111100", + "111752", + "112375", + "113080", + "113800", + "114233", + "114876", + "115532", + "116178", + "116695", + "117227", + "117865", + "118718", + "119283", + "119796", + "120265", + "121000", + "121600", + "121954", + "122705", + "123359", + "123865", + "124207", + "124735", + "125255", + "125766", + "126282", + "126820", + "127222", + "127801", + "128300", + "129300", + "130000", + "131000", + "131717", + "132455", + "133106", + "133642", + "134037", + "135010", + "136216", + "137028", + "137776", + "138418", + "139283", + "140030", + "140800", + "142044", + "142659", + "143726", + "144582", + "145800", + "146439", + "147523", + "148105", + "149146", + "150100", + "151060", + "152442", + "153364", + "154980", + "155800", + "157300", + "158335", + "159632", + "161161", + "162300", + "163849", + "164747", + "166467", + "167461", + "169300", + "170428", + "171532", + "172701", + "173895", + "174985", + "176576", + "178182", + "179208", + "180210", + "181805", + "183100", + "184061", + "185401", + "186900", + "188344", + "189569", + "190905", + "193005", + "194300", + "195500", + "197254", + "199000", + "200901", + "202134", + "203793", + "206158", + "207588", + "210068", + "212976", + "214950", + "216735", + "218447", + "221000", + "222518", + "224887", + "227657", + "229425", + "233041", + "236000", + "239124", + "241649", + "243742", + "245772", + "248245", + "252386", + "254842", + "257812", + "262000", + "264081", + "266281", + "270251", + "272968", + "276421", + "279340", + "282941", + "286900", + "291000", + "294056", + "298900", + "301276", + "304477", + "309750", + "313530", + "317600", + "322267", + "326399", + "330276", + "334563", + "339131", + "342738", + "349246", + "353632", + "359147", + "362470", + "366549", + "372840", + "380755", + "385201", + "392830", + "400997", + "409100", + "416988", + "421589", + "428522", + "435964", + "443727", + "452976", + "461000", + "469735", + "476800", + "483155", + "493409", + "508899", + "519793", + "529900", + "540828", + "563662", + "580000", + "594501", + "616700", + "636765", + "656925", + "680332", + "703592", + "735167", + "764902", + "797735", + "830000", + "877239", + "940589", + "993400", + "1042740", + "1100000", + "1156100", + "1217818", + "1300977", + "1392860", + "1517550", + "1682000", + "1969868", + "2154376", + "2595674", + "2964638", + "4256300", + "6758845" + ] +} set histogram_type=@SINGLE_PREC_TYPE; set histogram_size=0; use test; @@ -1943,7 +1947,7 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t1 a 1 3 0.0000 1.0000 10 JSON_HB 5B0A20202231222C0A20202231222C0A20202231222C0A20202232222C0A20202232222C0A20202232222C0A20202232222C0A20202233222C0A20202233222C0A20202233220A5D +test t1 a 1 3 0.0000 1.0000 10 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2231222C202231222C202231222C202232222C202232222C202232222C202232222C202233222C202233222C202233225D0A7D set histogram_size=default; drop table t1; # @@ -1968,7 +1972,7 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t1 a 1 5 0.0000 1.0000 10 JSON_HB 5B0A20202231222C0A20202231222C0A20202232222C0A20202232222C0A20202233222C0A20202233222C0A20202234222C0A20202234222C0A20202235222C0A20202235220A5D +test t1 a 1 5 0.0000 1.0000 10 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B2231222C202231222C202232222C202232222C202233222C202233222C202234222C202234222C202235222C202235225D0A7D set histogram_size=0; set histogram_type=@SINGLE_PREC_TYPE; drop table t1; @@ -2009,7 +2013,7 @@ nulls_ratio, avg_frequency, hist_size, hist_type, HEX(histogram) FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t2 id 1 1024 0.0000 8.0000 63 JSON_HB 5B0A2020223137222C0A2020223333222C0A2020223439222C0A2020223635222C0A2020223831222C0A2020223937222C0A202022313133222C0A202022313239222C0A202022313435222C0A202022313631222C0A202022313737222C0A202022313933222C0A202022323039222C0A202022323235222C0A202022323431222C0A202022323537222C0A202022323733222C0A202022323839222C0A202022333035222C0A202022333231222C0A202022333337222C0A202022333533222C0A202022333639222C0A202022333835222C0A202022343031222C0A202022343137222C0A202022343333222C0A202022343439222C0A202022343635222C0A202022343831222C0A202022343937222C0A202022353133222C0A202022353239222C0A202022353435222C0A202022353631222C0A202022353737222C0A202022353933222C0A202022363039222C0A202022363235222C0A202022363431222C0A202022363537222C0A202022363733222C0A202022363839222C0A202022373035222C0A202022373231222C0A202022373337222C0A202022373533222C0A202022373639222C0A202022373835222C0A202022383031222C0A202022383137222C0A202022383333222C0A202022383439222C0A20202238 3635222C0A202022383831222C0A202022383937222C0A202022393133222C0A202022393239222C0A202022393435222C0A202022393631222C0A202022393737222C0A202022393933222C0A20202231303039220A5D +test t2 id 1 1024 0.0000 8.0000 63 JSON_HB 7B0A202022686973746F6772616D5F68625F7631223A205B0A20202020223137222C0A20202020223333222C0A20202020223439222C0A20202020223635222C0A20202020223831222C0A20202020223937222C0A2020202022313133222C0A2020202022313239222C0A2020202022313435222C0A2020202022313631222C0A2020202022313737222C0A2020202022313933222C0A2020202022323039222C0A2020202022323235222C0A2020202022323431222C0A2020202022323537222C0A2020202022323733222C0A2020202022323839222C0A2020202022333035222C0A2020202022333231222C0A2020202022333337222C0A2020202022333533222C0A2020202022333639222C0A2020202022333835222C0A2020202022343031222C0A2020202022343137222C0A2020202022343333222C0A2020202022343439222C0A2020202022343635222C0A2020202022343831222C0A2020202022343937222C0A2020202022353133222C0A2020202022353239222C0A2020202022353435222C0A2020202022353631222C0A2020202022353737222C0A2020202022353933222C0A2020202022363039222C0A2020202022363235222C0A2020202022363431222C0A2020202022363537222C0A20202020223637 33222C0A2020202022363839222C0A2020202022373035222C0A2020202022373231222C0A2020202022373337222C0A2020202022373533222C0A2020202022373639222C0A2020202022373835222C0A2020202022383031222C0A2020202022383137222C0A2020202022383333222C0A2020202022383439222C0A2020202022383635222C0A2020202022383831222C0A2020202022383937222C0A2020202022393133222C0A2020202022393239222C0A2020202022393435222C0A2020202022393631222C0A2020202022393737222C0A2020202022393933222C0A202020202231303039220A20205D0A7D set histogram_size=0; drop table t1, t2; set use_stat_tables=@save_use_stat_tables; @@ -2169,18 +2173,20 @@ select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, a DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) -t1 id 1 17384 0.0000 4.0000 14.0000 [ - "1490", - "2979", - "4469", - "5958", - "7448", - "9937", - "11427", - "12916", - "14406", - "15895" -] +t1 id 1 17384 0.0000 4.0000 14.0000 { + "histogram_hb_v1": [ + "1490", + "2979", + "4469", + "5958", + "7448", + "9937", + "11427", + "12916", + "14406", + "15895" + ] +} set analyze_sample_percentage=0.1; # # This query will show an innacurate avg_frequency value. @@ -2193,18 +2199,20 @@ select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, a DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) -t1 id 111 17026 0.0000 4.0000 10.4739 [ - "832", - "2446", - "3422", - "5411", - "6687", - "9390", - "10738", - "12738", - "14365", - "15411" -] +t1 id 111 17026 0.0000 4.0000 10.4739 { + "histogram_hb_v1": [ + "832", + "2446", + "3422", + "5411", + "6687", + "9390", + "10738", + "12738", + "14365", + "15411" + ] +} # # This query will show a better avg_frequency value. # @@ -2217,18 +2225,20 @@ select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, a DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) -t1 id 1 17384 0.0000 4.0000 14.0401 [ - "1478", - "2954", - "4441", - "5894", - "7397", - "9888", - "11391", - "12895", - "14370", - "15880" -] +t1 id 1 17384 0.0000 4.0000 14.0401 { + "histogram_hb_v1": [ + "1478", + "2954", + "4441", + "5894", + "7397", + "9888", + "11391", + "12895", + "14370", + "15880" + ] +} set analyze_sample_percentage=0; # # Test self adjusting sampling level. @@ -2241,18 +2251,20 @@ select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, a DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) -t1 id 1 17384 0.0000 4.0000 13.9812 [ - "1500", - "3009", - "4501", - "5997", - "7493", - "9981", - "11456", - "12932", - "14408", - "15903" -] +t1 id 1 17384 0.0000 4.0000 13.9812 { + "histogram_hb_v1": [ + "1500", + "3009", + "4501", + "5997", + "7493", + "9981", + "11456", + "12932", + "14408", + "15903" + ] +} # # Test record estimation is working properly. # @@ -2271,18 +2283,20 @@ select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, a DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) -t1 id 1 17384 0.0000 4.0000 14.0000 [ - "1490", - "2979", - "4469", - "5958", - "7448", - "9937", - "11427", - "12916", - "14406", - "15895" -] +t1 id 1 17384 0.0000 4.0000 14.0000 { + "histogram_hb_v1": [ + "1490", + "2979", + "4469", + "5958", + "7448", + "9937", + "11427", + "12916", + "14406", + "15895" + ] +} explain select * from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 229376 @@ -2324,108 +2338,110 @@ test.t1_json analyze status Engine-independent statistics collected test.t1_json analyze status OK select * from mysql.column_stats where table_name='t1_json'; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -test t1_json a a-0 a-9 0.0000 3.0000 1.0000 100 JSON_HB [ - "a-0", - "a-0", - "a-0", - "a-0", - "a-0", - "a-0", - "a-0", - "a-0", - "a-0", - "a-0", - "a-1", - "a-1", - "a-1", - "a-1", - "a-1", - "a-1", - "a-1", - "a-1", - "a-1", - "a-1", - "a-2", - "a-2", - "a-2", - "a-2", - "a-2", - "a-2", - "a-2", - "a-2", - "a-2", - "a-2", - "a-3", - "a-3", - "a-3", - "a-3", - "a-3", - "a-3", - "a-3", - "a-3", - "a-3", - "a-3", - "a-4", - "a-4", - "a-4", - "a-4", - "a-4", - "a-4", - "a-4", - "a-4", - "a-4", - "a-4", - "a-5", - "a-5", - "a-5", - "a-5", - "a-5", - "a-5", - "a-5", - "a-5", - "a-5", - "a-5", - "a-6", - "a-6", - "a-6", - "a-6", - "a-6", - "a-6", - "a-6", - "a-6", - "a-6", - "a-6", - "a-7", - "a-7", - "a-7", - "a-7", - "a-7", - "a-7", - "a-7", - "a-7", - "a-7", - "a-7", - "a-8", - "a-8", - "a-8", - "a-8", - "a-8", - "a-8", - "a-8", - "a-8", - "a-8", - "a-8", - "a-9", - "a-9", - "a-9", - "a-9", - "a-9", - "a-9", - "a-9", - "a-9", - "a-9", - "a-9" -] +test t1_json a a-0 a-9 0.0000 3.0000 1.0000 100 JSON_HB { + "histogram_hb_v1": [ + "a-0", + "a-0", + "a-0", + "a-0", + "a-0", + "a-0", + "a-0", + "a-0", + "a-0", + "a-0", + "a-1", + "a-1", + "a-1", + "a-1", + "a-1", + "a-1", + "a-1", + "a-1", + "a-1", + "a-1", + "a-2", + "a-2", + "a-2", + "a-2", + "a-2", + "a-2", + "a-2", + "a-2", + "a-2", + "a-2", + "a-3", + "a-3", + "a-3", + "a-3", + "a-3", + "a-3", + "a-3", + "a-3", + "a-3", + "a-3", + "a-4", + "a-4", + "a-4", + "a-4", + "a-4", + "a-4", + "a-4", + "a-4", + "a-4", + "a-4", + "a-5", + "a-5", + "a-5", + "a-5", + "a-5", + "a-5", + "a-5", + "a-5", + "a-5", + "a-5", + "a-6", + "a-6", + "a-6", + "a-6", + "a-6", + "a-6", + "a-6", + "a-6", + "a-6", + "a-6", + "a-7", + "a-7", + "a-7", + "a-7", + "a-7", + "a-7", + "a-7", + "a-7", + "a-7", + "a-7", + "a-8", + "a-8", + "a-8", + "a-8", + "a-8", + "a-8", + "a-8", + "a-8", + "a-8", + "a-8", + "a-9", + "a-9", + "a-9", + "a-9", + "a-9", + "a-9", + "a-9", + "a-9", + "a-9", + "a-9" + ] +} explain extended select * from t1_json where a between 'a-3a' and 'zzzzzzzzz'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 60.87 Using where @@ -2441,46 +2457,47 @@ Warnings: Note 1003 select `test`.`t1_json`.`a` AS `a` from `test`.`t1_json` where `test`.`t1_json`.`a` < 'b-1a' analyze select * from t1_json where a > 'zzzzzzzzz'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 10.00 0.00 0.00 Using where +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 10.00 10.00 0.00 Using where +drop table ten; UPDATE mysql.column_stats SET histogram='["a-1", "a-2", {"a": "b"}, "a-3"]' WHERE table_name='t1_json'; FLUSH TABLES; explain extended select * from t1_json where a between 'a-3a' and 'zzzzzzzzz'; -ERROR HY000: Failed to parse histogram, encountered JSON_TYPE '1'. -create table users ( +ERROR HY000: Failed to parse histogram: Root JSON element must be a JSON object at offset 1. +create table t2 ( city varchar(100) ); set histogram_size=50; -insert into users select 'Moscow' from seq_1_to_99; -insert into users select 'Helsinki' from seq_1_to_2; +insert into t2 select 'Moscow' from seq_1_to_99; +insert into t2 select 'Helsinki' from seq_1_to_2; set histogram_type=json_hb; -analyze table users persistent for all; +analyze table t2 persistent for all; Table Op Msg_type Msg_text -test.users analyze status Engine-independent statistics collected -test.users analyze status OK -explain extended select * from users where city = 'Moscow'; +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain extended select * from t2 where city = 'Moscow'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE users ALL NULL NULL NULL NULL 101 98.04 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 98.04 Using where Warnings: -Note 1003 select `test`.`users`.`city` AS `city` from `test`.`users` where `test`.`users`.`city` = 'Moscow' -analyze select * from users where city = 'Moscow'; +Note 1003 select `test`.`t2`.`city` AS `city` from `test`.`t2` where `test`.`t2`.`city` = 'Moscow' +analyze select * from t2 where city = 'Moscow'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE users ALL NULL NULL NULL NULL 101 101.00 98.04 98.02 Using where -explain extended select * from users where city = 'Helsinki'; +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 101.00 98.04 98.02 Using where +explain extended select * from t2 where city = 'Helsinki'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE users ALL NULL NULL NULL NULL 101 2.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 2.00 Using where Warnings: -Note 1003 select `test`.`users`.`city` AS `city` from `test`.`users` where `test`.`users`.`city` = 'Helsinki' -analyze select * from users where city = 'helsinki'; +Note 1003 select `test`.`t2`.`city` AS `city` from `test`.`t2` where `test`.`t2`.`city` = 'Helsinki' +analyze select * from t2 where city = 'helsinki'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE users ALL NULL NULL NULL NULL 101 101.00 2.00 1.98 Using where -explain extended select * from users where city < 'Lagos'; +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 101.00 2.00 1.98 Using where +explain extended select * from t2 where city < 'Lagos'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE users ALL NULL NULL NULL NULL 101 3.58 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 50.00 Using where Warnings: -Note 1003 select `test`.`users`.`city` AS `city` from `test`.`users` where `test`.`users`.`city` < 'Lagos' +Note 1003 select `test`.`t2`.`city` AS `city` from `test`.`t2` where `test`.`t2`.`city` < 'Lagos' drop table t1_bin; drop table t1_json; -drop table users; +drop table t2; DELETE FROM mysql.column_stats; create schema world; use world; @@ -2489,630 +2506,654 @@ set histogram_size=50; ANALYZE TABLE Country, City, CountryLanguage persistent for all; SELECT column_name, min_value, max_value, hist_size, hist_type, histogram FROM mysql.column_stats; column_name min_value max_value hist_size hist_type histogram -Code ABW ZWE 50 JSON_HB [ - "ALB", - "ARM", - "AUS", - "BEL", - "BHR", - "BMU", - "BRN", - "CAN", - "CIV", - "COK", - "CUB", - "DEU", - "DOM", - "ESH", - "FJI", - "FSM", - "GIB", - "GNQ", - "GUF", - "HMD", - "IDN", - "IRQ", - "JAM", - "KGZ", - "KWT", - "LBY", - "LTU", - "MCO", - "MEX", - "MMR", - "MSR", - "MYS", - "NFK", - "NOR", - "PAK", - "PHL", - "PRK", - "QAT", - "RWA", - "SGS", - "SLV", - "STP", - "SWZ", - "TGO", - "TKM", - "TUR", - "UKR", - "UZB", - "VIR", - "YEM" -] -Name Afghanistan Zimbabwe 50 JSON_HB [ - "Andorra", - "Argentina", - "Azerbaijan", - "Barbados", - "Bermuda", - "Bouvet Island", - "Bulgaria", - "Canada", - "Chile", - "Colombia", - "Costa Rica", - "Côte d’Ivoire", - "Dominican Republic", - "Equatorial Guinea", - "Faroe Islands", - "French Guiana", - "Georgia", - "Greenland", - "Guinea", - "Heard Island and McDonald Islands", - "Iceland", - "Ireland", - "Japan", - "Kuwait", - "Lesotho", - "Lithuania", - "Malawi", - "Marshall Islands", - "Mayotte", - "Mongolia", - "Namibia", - "Netherlands Antilles", - "Nigeria", - "Norway", - "Panama", - "Philippines", - "Qatar", - "Saint Helena", - "Saint Vincent and the Grenadines", - "Senegal", - "Slovenia", - "South Georgia and the South Sandwich Islands", - "Suriname", - "Syria", - "Thailand", - "Tunisia", - "Uganda", - "United States", - "Venezuela", - "Western Sahara" -] -SurfaceArea 0.40 17075400.00 50 JSON_HB [ - "14.00", - "36.00", - "78.00", - "151.00", - "200.00", - "264.00", - "344.00", - "430.00", - "464.00", - "622.00", - "751.00", - "1399.00", - "2510.00", - "4033.00", - "8875.00", - "11000.00", - "14874.00", - "20256.00", - "25713.00", - "28051.00", - "30518.00", - "41526.00", - "48511.00", - "56785.00", - "69700.00", - "78866.00", - "90000.00", - "103000.00", - "111369.00", - "120538.00", - "147181.00", - "181035.00", - "214969.00", - "242900.00", - "274000.00", - "309500.00", - "331689.00", - "390757.00", - "447400.00", - "505992.00", - "581730.00", - "637657.00", - "774815.00", - "912050.00", - "1098581.00", - "1246700.00", - "1648195.00", - "2149690.00", - "2724900.00", - "9363520.00" -] -Population 0 1277558000 50 JSON_HB [ - "0", - "1000", - "2500", - "8000", - "17000", - "27000", - "38000", - "68000", - "78000", - "99000", - "149000", - "190000", - "241000", - "307000", - "428000", - "456000", - "638000", - "885000", - "1295000", - "1726000", - "2153000", - "2662000", - "3101000", - "3520000", - "3850000", - "4023000", - "4629000", - "5074000", - "5398700", - "6188000", - "6782000", - "7733000", - "8329000", - "9586000", - "10239000", - "10730000", - "11385000", - "14786000", - "15942000", - "18886000", - "22244000", - "23115000", - "25662000", - "31471000", - "39441700", - "50456000", - "61399000", - "68470000", - "111506000", - "170115000" -] -Capital 1 4074 50 JSON_HB [ - "35", - "63", - "129", - "150", - "187", - "204", - "538", - "554", - "586", - "652", - "764", - "904", - "917", - "926", - "937", - "1365", - "1464", - "1532", - "1800", - "1864", - "2256", - "2317", - "2409", - "2434", - "2441", - "2454", - "2463", - "2507", - "2514", - "2696", - "2728", - "2754", - "2821", - "2884", - "2914", - "2973", - "3018", - "3064", - "3161", - "3172", - "3208", - "3225", - "3248", - "3315", - "3334", - "3358", - "3425", - "3499", - "3537", - "3791" -] -ID 1 4079 50 JSON_HB [ - "80", - "160", - "240", - "320", - "400", - "480", - "560", - "640", - "720", - "800", - "880", - "960", - "1040", - "1120", - "1200", - "1280", - "1360", - "1440", - "1520", - "1600", - "1680", - "1760", - "1840", - "1920", - "2000", - "2080", - "2160", - "2240", - "2320", - "2400", - "2480", - "2560", - "2640", - "2720", - "2800", - "2880", - "2960", - "3040", - "3120", - "3200", - "3280", - "3360", - "3440", - "3520", - "3600", - "3680", - "3760", - "3840", - "3920", - "4000" -] -Name A Coruña (La Coruña) Ürgenc 50 JSON_HB [ - "Allentown", - "Araguari", - "Bahtim", - "Batangas", - "Bialystok", - "Brampton", - "Calama", - "Changchun", - "Ciomas", - "Cuautla", - "Detroit", - "Effon-Alaiye", - "Firozabad", - "Gebze", - "Guangyuan", - "Hangzhou", - "Hradec Králové", - "Inazawa", - "Jalib al-Shuyukh", - "Jubayl", - "Kassel", - "Kitakyushu", - "Kunshan", - "Le Mans", - "Longueuil", - "Malang", - "Mati", - "Mishan", - "Muroran", - "Natal", - "North York", - "Omsk", - "Palu", - "Phoenix", - "Poznan", - "Quezon", - "Rishra", - "Salem", - "Sancti-Spíritus", - "Sekondi-Takoradi", - "Silao", - "Stoke-on-Trent", - "Taegu", - "Teheran", - "Tomsk", - "Târgu Mures", - "Varginha", - "Weifang", - "Yangjiang", - "Zhaodong" -] -Country ABW ZWE 50 JSON_HB [ - "ARM", - "BHS", - "BRA", - "BRA", - "BRA", - "CAN", - "CHN", - "CHN", - "CHN", - "CHN", - "CHN", - "COL", - "DEU", - "DZA", - "ESP", - "FRA", - "GBR", - "IDN", - "IDN", - "IND", - "IND", - "IND", - "IND", - "IRN", - "ITA", - "JPN", - "JPN", - "JPN", - "KOR", - "LKA", - "MEX", - "MEX", - "MMR", - "NGA", - "NZL", - "PER", - "PHL", - "POL", - "QAT", - "RUS", - "RUS", - "SAU", - "TCD", - "TUR", - "UKR", - "USA", - "USA", - "USA", - "USA", - "VNM" -] -Population 42 10500000 50 JSON_HB [ - "50699", - "90601", - "92583", - "94600", - "96800", - "98781", - "100887", - "102681", - "105119", - "107329", - "109500", - "112375", - "115532", - "118718", - "121600", - "124207", - "126820", - "130000", - "133642", - "137776", - "142044", - "146439", - "151060", - "157300", - "163849", - "170428", - "176576", - "183100", - "189569", - "197254", - "206158", - "216735", - "227657", - "241649", - "254842", - "270251", - "286900", - "304477", - "326399", - "349246", - "372840", - "409100", - "443727", - "483155", - "540828", - "636765", - "764902", - "993400", - "1300977", - "2154376" -] -Country ABW ZWE 50 JSON_HB [ - "ALB", - "ASM", - "AZE", - "BFA", - "BHS", - "BRA", - "CAF", - "CHE", - "CIV", - "COD", - "CPV", - "CZE", - "DOM", - "EST", - "FRA", - "GBR", - "GIN", - "GRC", - "HKG", - "IDN", - "IND", - "ISL", - "JPN", - "KGZ", - "LAO", - "LIE", - "LVA", - "MDA", - "MLI", - "MNG", - "MRT", - "MYS", - "NER", - "NIU", - "NRU", - "PAN", - "PLW", - "PYF", - "RUS", - "SDN", - "SLE", - "SVN", - "TCD", - "THA", - "TUR", - "TZA", - "UKR", - "UZB", - "VNM", - "ZAF" -] -Language Abhyasi [South]Mande 50 JSON_HB [ - "Amhara", - "Arabic", - "Araucan", - "Bakhtyari", - "Belorussian", - "Bubi", - "Cebuano", - "Chinese", - "Comorian", - "Creole French", - "Danish", - "Embera", - "English", - "English", - "English", - "French", - "French", - "Futuna", - "German", - "Greek", - "Hakka", - "Hui", - "Italian", - "Joruba", - "Kazakh", - "Kongo", - "Kurdish", - "Luchazi", - "Makua", - "Malinke", - "Marshallese", - "Mixed Languages", - "Nauru", - "Nung", - "Pangasinan", - "Polish", - "Portuguese", - "Romanian", - "Russian", - "Sara", - "Shona", - "Songhai", - "Spanish", - "Spanish", - "Tamashek", - "Thai", - "Tswana", - "Tuvalu", - "Urdu", - "Wolea" -] -Percentage 0.0 99.9 50 JSON_HB [ - "0.0", - "0.0", - "0.0", - "0.1", - "0.2", - "0.3", - "0.4", - "0.5", - "0.6", - "0.7", - "0.8", - "1.1", - "1.3", - "1.4", - "1.6", - "1.8", - "2.1", - "2.3", - "2.5", - "2.9", - "3.2", - "3.5", - "3.8", - "4.4", - "4.9", - "5.5", - "5.9", - "6.6", - "7.4", - "7.8", - "8.6", - "9.2", - "10.3", - "11.5", - "12.9", - "14.6", - "17.1", - "20.3", - "23.8", - "31.8", - "39.4", - "47.5", - "55.1", - "66.5", - "77.2", - "86.0", - "89.9", - "94.3", - "97.3", - "99.0" -] +Code ABW ZWE 50 JSON_HB { + "histogram_hb_v1": [ + "ALB", + "ARM", + "AUS", + "BEL", + "BHR", + "BMU", + "BRN", + "CAN", + "CIV", + "COK", + "CUB", + "DEU", + "DOM", + "ESH", + "FJI", + "FSM", + "GIB", + "GNQ", + "GUF", + "HMD", + "IDN", + "IRQ", + "JAM", + "KGZ", + "KWT", + "LBY", + "LTU", + "MCO", + "MEX", + "MMR", + "MSR", + "MYS", + "NFK", + "NOR", + "PAK", + "PHL", + "PRK", + "QAT", + "RWA", + "SGS", + "SLV", + "STP", + "SWZ", + "TGO", + "TKM", + "TUR", + "UKR", + "UZB", + "VIR", + "YEM" + ] +} +Name Afghanistan Zimbabwe 50 JSON_HB { + "histogram_hb_v1": [ + "Andorra", + "Argentina", + "Azerbaijan", + "Barbados", + "Bermuda", + "Bouvet Island", + "Bulgaria", + "Canada", + "Chile", + "Colombia", + "Costa Rica", + "Côte d’Ivoire", + "Dominican Republic", + "Equatorial Guinea", + "Faroe Islands", + "French Guiana", + "Georgia", + "Greenland", + "Guinea", + "Heard Island and McDonald Islands", + "Iceland", + "Ireland", + "Japan", + "Kuwait", + "Lesotho", + "Lithuania", + "Malawi", + "Marshall Islands", + "Mayotte", + "Mongolia", + "Namibia", + "Netherlands Antilles", + "Nigeria", + "Norway", + "Panama", + "Philippines", + "Qatar", + "Saint Helena", + "Saint Vincent and the Grenadines", + "Senegal", + "Slovenia", + "South Georgia and the South Sandwich Islands", + "Suriname", + "Syria", + "Thailand", + "Tunisia", + "Uganda", + "United States", + "Venezuela", + "Western Sahara" + ] +} +SurfaceArea 0.40 17075400.00 50 JSON_HB { + "histogram_hb_v1": [ + "14.00", + "36.00", + "78.00", + "151.00", + "200.00", + "264.00", + "344.00", + "430.00", + "464.00", + "622.00", + "751.00", + "1399.00", + "2510.00", + "4033.00", + "8875.00", + "11000.00", + "14874.00", + "20256.00", + "25713.00", + "28051.00", + "30518.00", + "41526.00", + "48511.00", + "56785.00", + "69700.00", + "78866.00", + "90000.00", + "103000.00", + "111369.00", + "120538.00", + "147181.00", + "181035.00", + "214969.00", + "242900.00", + "274000.00", + "309500.00", + "331689.00", + "390757.00", + "447400.00", + "505992.00", + "581730.00", + "637657.00", + "774815.00", + "912050.00", + "1098581.00", + "1246700.00", + "1648195.00", + "2149690.00", + "2724900.00", + "9363520.00" + ] +} +Population 0 1277558000 50 JSON_HB { + "histogram_hb_v1": [ + "0", + "1000", + "2500", + "8000", + "17000", + "27000", + "38000", + "68000", + "78000", + "99000", + "149000", + "190000", + "241000", + "307000", + "428000", + "456000", + "638000", + "885000", + "1295000", + "1726000", + "2153000", + "2662000", + "3101000", + "3520000", + "3850000", + "4023000", + "4629000", + "5074000", + "5398700", + "6188000", + "6782000", + "7733000", + "8329000", + "9586000", + "10239000", + "10730000", + "11385000", + "14786000", + "15942000", + "18886000", + "22244000", + "23115000", + "25662000", + "31471000", + "39441700", + "50456000", + "61399000", + "68470000", + "111506000", + "170115000" + ] +} +Capital 1 4074 50 JSON_HB { + "histogram_hb_v1": [ + "35", + "63", + "129", + "150", + "187", + "204", + "538", + "554", + "586", + "652", + "764", + "904", + "917", + "926", + "937", + "1365", + "1464", + "1532", + "1800", + "1864", + "2256", + "2317", + "2409", + "2434", + "2441", + "2454", + "2463", + "2507", + "2514", + "2696", + "2728", + "2754", + "2821", + "2884", + "2914", + "2973", + "3018", + "3064", + "3161", + "3172", + "3208", + "3225", + "3248", + "3315", + "3334", + "3358", + "3425", + "3499", + "3537", + "3791" + ] +} +ID 1 4079 50 JSON_HB { + "histogram_hb_v1": [ + "80", + "160", + "240", + "320", + "400", + "480", + "560", + "640", + "720", + "800", + "880", + "960", + "1040", + "1120", + "1200", + "1280", + "1360", + "1440", + "1520", + "1600", + "1680", + "1760", + "1840", + "1920", + "2000", + "2080", + "2160", + "2240", + "2320", + "2400", + "2480", + "2560", + "2640", + "2720", + "2800", + "2880", + "2960", + "3040", + "3120", + "3200", + "3280", + "3360", + "3440", + "3520", + "3600", + "3680", + "3760", + "3840", + "3920", + "4000" + ] +} +Name A Coruña (La Coruña) Ürgenc 50 JSON_HB { + "histogram_hb_v1": [ + "Allentown", + "Araguari", + "Bahtim", + "Batangas", + "Bialystok", + "Brampton", + "Calama", + "Changchun", + "Ciomas", + "Cuautla", + "Detroit", + "Effon-Alaiye", + "Firozabad", + "Gebze", + "Guangyuan", + "Hangzhou", + "Hradec Králové", + "Inazawa", + "Jalib al-Shuyukh", + "Jubayl", + "Kassel", + "Kitakyushu", + "Kunshan", + "Le Mans", + "Longueuil", + "Malang", + "Mati", + "Mishan", + "Muroran", + "Natal", + "North York", + "Omsk", + "Palu", + "Phoenix", + "Poznan", + "Quezon", + "Rishra", + "Salem", + "Sancti-Spíritus", + "Sekondi-Takoradi", + "Silao", + "Stoke-on-Trent", + "Taegu", + "Teheran", + "Tomsk", + "Târgu Mures", + "Varginha", + "Weifang", + "Yangjiang", + "Zhaodong" + ] +} +Country ABW ZWE 50 JSON_HB { + "histogram_hb_v1": [ + "ARM", + "BHS", + "BRA", + "BRA", + "BRA", + "CAN", + "CHN", + "CHN", + "CHN", + "CHN", + "CHN", + "COL", + "DEU", + "DZA", + "ESP", + "FRA", + "GBR", + "IDN", + "IDN", + "IND", + "IND", + "IND", + "IND", + "IRN", + "ITA", + "JPN", + "JPN", + "JPN", + "KOR", + "LKA", + "MEX", + "MEX", + "MMR", + "NGA", + "NZL", + "PER", + "PHL", + "POL", + "QAT", + "RUS", + "RUS", + "SAU", + "TCD", + "TUR", + "UKR", + "USA", + "USA", + "USA", + "USA", + "VNM" + ] +} +Population 42 10500000 50 JSON_HB { + "histogram_hb_v1": [ + "50699", + "90601", + "92583", + "94600", + "96800", + "98781", + "100887", + "102681", + "105119", + "107329", + "109500", + "112375", + "115532", + "118718", + "121600", + "124207", + "126820", + "130000", + "133642", + "137776", + "142044", + "146439", + "151060", + "157300", + "163849", + "170428", + "176576", + "183100", + "189569", + "197254", + "206158", + "216735", + "227657", + "241649", + "254842", + "270251", + "286900", + "304477", + "326399", + "349246", + "372840", + "409100", + "443727", + "483155", + "540828", + "636765", + "764902", + "993400", + "1300977", + "2154376" + ] +} +Country ABW ZWE 50 JSON_HB { + "histogram_hb_v1": [ + "ALB", + "ASM", + "AZE", + "BFA", + "BHS", + "BRA", + "CAF", + "CHE", + "CIV", + "COD", + "CPV", + "CZE", + "DOM", + "EST", + "FRA", + "GBR", + "GIN", + "GRC", + "HKG", + "IDN", + "IND", + "ISL", + "JPN", + "KGZ", + "LAO", + "LIE", + "LVA", + "MDA", + "MLI", + "MNG", + "MRT", + "MYS", + "NER", + "NIU", + "NRU", + "PAN", + "PLW", + "PYF", + "RUS", + "SDN", + "SLE", + "SVN", + "TCD", + "THA", + "TUR", + "TZA", + "UKR", + "UZB", + "VNM", + "ZAF" + ] +} +Language Abhyasi [South]Mande 50 JSON_HB { + "histogram_hb_v1": [ + "Amhara", + "Arabic", + "Araucan", + "Bakhtyari", + "Belorussian", + "Bubi", + "Cebuano", + "Chinese", + "Comorian", + "Creole French", + "Danish", + "Embera", + "English", + "English", + "English", + "French", + "French", + "Futuna", + "German", + "Greek", + "Hakka", + "Hui", + "Italian", + "Joruba", + "Kazakh", + "Kongo", + "Kurdish", + "Luchazi", + "Makua", + "Malinke", + "Marshallese", + "Mixed Languages", + "Nauru", + "Nung", + "Pangasinan", + "Polish", + "Portuguese", + "Romanian", + "Russian", + "Sara", + "Shona", + "Songhai", + "Spanish", + "Spanish", + "Tamashek", + "Thai", + "Tswana", + "Tuvalu", + "Urdu", + "Wolea" + ] +} +Percentage 0.0 99.9 50 JSON_HB { + "histogram_hb_v1": [ + "0.0", + "0.0", + "0.0", + "0.1", + "0.2", + "0.3", + "0.4", + "0.5", + "0.6", + "0.7", + "0.8", + "1.1", + "1.3", + "1.4", + "1.6", + "1.8", + "2.1", + "2.3", + "2.5", + "2.9", + "3.2", + "3.5", + "3.8", + "4.4", + "4.9", + "5.5", + "5.9", + "6.6", + "7.4", + "7.8", + "8.6", + "9.2", + "10.3", + "11.5", + "12.9", + "14.6", + "17.1", + "20.3", + "23.8", + "31.8", + "39.4", + "47.5", + "55.1", + "66.5", + "77.2", + "86.0", + "89.9", + "94.3", + "97.3", + "99.0" + ] +} explain extended select * from Country where 'Code' between 'BBC' and 'GGG'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 100.00 diff --git a/mysql-test/main/statistics_json.test b/mysql-test/main/statistics_json.test index 404d1e11650..99705aa38ae 100644 --- a/mysql-test/main/statistics_json.test +++ b/mysql-test/main/statistics_json.test @@ -37,6 +37,8 @@ analyze select * from t1_json where a between 'a-3a' and 'zzzzzzzzz'; explain extended select * from t1_json where a < 'b-1a'; analyze select * from t1_json where a > 'zzzzzzzzz'; +drop table ten; + # test different valid JSON strings that are invalid histograms. UPDATE mysql.column_stats SET histogram='["a-1", "a-2", {"a": "b"}, "a-3"]' WHERE table_name='t1_json'; FLUSH TABLES; @@ -45,23 +47,23 @@ explain extended select * from t1_json where a between 'a-3a' and 'zzzzzzzzz'; --source include/have_sequence.inc -create table users ( +create table t2 ( city varchar(100) ); set histogram_size=50; -insert into users select 'Moscow' from seq_1_to_99; -insert into users select 'Helsinki' from seq_1_to_2; +insert into t2 select 'Moscow' from seq_1_to_99; +insert into t2 select 'Helsinki' from seq_1_to_2; set histogram_type=json_hb; -analyze table users persistent for all; -explain extended select * from users where city = 'Moscow'; -analyze select * from users where city = 'Moscow'; -explain extended select * from users where city = 'Helsinki'; -analyze select * from users where city = 'helsinki'; -explain extended select * from users where city < 'Lagos'; +analyze table t2 persistent for all; +explain extended select * from t2 where city = 'Moscow'; +analyze select * from t2 where city = 'Moscow'; +explain extended select * from t2 where city = 'Helsinki'; +analyze select * from t2 where city = 'helsinki'; +explain extended select * from t2 where city < 'Lagos'; drop table t1_bin; drop table t1_json; -drop table users; +drop table t2; DELETE FROM mysql.column_stats; diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 3e03f587f84..47929c8707a 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7993,4 +7993,4 @@ ER_REMOVED_ORPHAN_TRIGGER ER_STORAGE_ENGINE_DISABLED eng "Storage engine %s is disabled" ER_JSON_HISTOGRAM_PARSE_FAILED - eng "Failed to parse histogram, encountered JSON_TYPE '%d'." + eng "Failed to parse histogram: %s at offset %d." diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index 4c300b1c04d..af884fdd6e8 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -1123,6 +1123,7 @@ class Column_stat: public Stat_table void get_stat_values() { table_field->read_stats->set_all_nulls(); + // default: hist_type=NULL means there's no histogram table_field->read_stats->histogram_type_on_disk= INVALID_HISTOGRAM; if (table_field->read_stats->min_value) @@ -1196,7 +1197,10 @@ class Column_stat: public Stat_table break; } case COLUMN_STAT_HISTOGRAM: - //TODO: if stat_field->length() == 0 then histogram_type_on_disk is set to INVALID_HISTOGRAM + /* + Do nothing here: we take the histogram length from the 'histogram' + column itself + */ break; } } @@ -1245,7 +1249,7 @@ class Column_stat: public Stat_table } if (!hist->parse(mem_root, table_field, table_field->read_stats->histogram_type_on_disk, - (const uchar*)val.ptr(), val.length())) + val.ptr(), val.length())) { table_field->read_stats->histogram_= hist; return hist; @@ -1255,19 +1259,19 @@ class Column_stat: public Stat_table } }; -bool Histogram_binary::parse(MEM_ROOT *mem_root, Field *, - Histogram_type type_arg, - const uchar *ptr_arg, uint size_arg) + +bool Histogram_binary::parse(MEM_ROOT *mem_root, Field*, + Histogram_type type_arg, const char *hist_data, + size_t hist_data_len) { - // Just copy the data - size = (uint8) size_arg; - type = type_arg; - if ((values = (uchar*)alloc_root(mem_root, size_arg))) - { - memcpy(values, ptr_arg, size_arg); - return false; - } - return true; + /* On-disk an in-memory formats are the same. Just copy the data. */ + type= type_arg; + size= (uint8) hist_data_len; // 'size' holds the size of histogram in bytes + if (!(values= (uchar*)alloc_root(mem_root, hist_data_len))) + return true; + + memcpy(values, hist_data, hist_data_len); + return false; } /* @@ -1307,39 +1311,81 @@ void Histogram_json::init_for_collection(MEM_ROOT *mem_root, */ bool Histogram_json::parse(MEM_ROOT *mem_root, Field *field, - Histogram_type type_arg, const uchar *ptr, - uint size_arg) + Histogram_type type_arg, const char *hist_data, + size_t hist_data_len) { DBUG_ENTER("Histogram_json::parse"); DBUG_ASSERT(type_arg == JSON_HB); - size = (uint8) size_arg; - const char *json = (char *)ptr; - int vt; - std::vector<std::string> hist_buckets_text; - bool result = json_get_array_items(json, json + strlen(json), &vt, hist_buckets_text); - if (!result) - { - my_error(ER_JSON_HISTOGRAM_PARSE_FAILED, MYF(0), vt); - DBUG_RETURN(true); + const char *err; + json_engine_t je; + json_string_t key_name; + + json_scan_start(&je, &my_charset_utf8mb4_bin, + (const uchar*)hist_data, + (const uchar*)hist_data+hist_data_len); + + if (json_read_value(&je) || je.value_type != JSON_VALUE_OBJECT) + { + err= "Root JSON element must be a JSON object"; + goto error; } - size= hist_buckets_text.size(); - /* - Convert the text based array into a data structure that allows lookups and - estimates - */ - for (auto &s : hist_buckets_text) + json_string_set_str(&key_name, (const uchar*)JSON_NAME, + (const uchar*)JSON_NAME + strlen(JSON_NAME)); + json_string_set_cs(&key_name, system_charset_info); + + if (json_scan_next(&je) || je.state != JST_KEY || + !json_key_matches(&je, &key_name)) { - field->store_text(s.data(), s.size(), &my_charset_bin); + err= "The first key in the object must be histogram_hb_v1"; + goto error; + } - // Get the value in "truncated key tuple format" here: - uchar buf[MAX_KEY_LENGTH]; - uint len_to_copy= field->key_length(); - uint bytes= field->get_key_image(buf, len_to_copy, Field::itRAW); - histogram_bounds.push_back(std::string((char*)buf, bytes)); + // The value must be a JSON array + if (json_read_value(&je) || (je.value_type != JSON_VALUE_ARRAY)) + { + err= "A JSON array expected"; + goto error; } + // Read the array + while (!json_scan_next(&je)) + { + switch(je.state) + { + case JST_VALUE: + { + const char *val; + int val_len; + json_smart_read_value(&je, &val, &val_len); + if (je.value_type != JSON_VALUE_STRING && + je.value_type != JSON_VALUE_NUMBER && + je.value_type != JSON_VALUE_TRUE && + je.value_type != JSON_VALUE_FALSE) + { + err= "Scalar value expected"; + goto error; + } + uchar buf[MAX_KEY_LENGTH]; + uint len_to_copy= field->key_length(); + field->store_text(val, val_len, &my_charset_bin); + uint bytes= field->get_key_image(buf, len_to_copy, Field::itRAW); + histogram_bounds.push_back(std::string((char*)buf, bytes)); + // TODO: Should we also compare this endpoint with the previous + // to verify that the ordering is right? + break; + } + case JST_ARRAY_END: + break; + } + } + size= histogram_bounds.size(); DBUG_RETURN(false); + +error: + my_error(ER_JSON_HISTOGRAM_PARSE_FAILED, MYF(0), err, + je.s.c_str - (const uchar*)hist_data); + DBUG_RETURN(true); } @@ -1347,7 +1393,7 @@ static void store_key_image_to_rec_no_null(Field *field, uchar *ptr) { MY_BITMAP *old_map= dbug_tmp_use_all_columns(field->table, &field->table->write_set); - field->set_key_image(ptr, field->key_length()); + field->set_key_image(ptr, field->key_length()); dbug_tmp_restore_column_map(&field->table->write_set, old_map); } @@ -1506,9 +1552,9 @@ double Histogram_json::point_selectivity(Field *field, key_range *endpoint, doub /* @param field The table field histogram is for. We don't care about the - field's current value, we only need its virtual functions to + field's current value, we only need its virtual functions to perform various operations - + @param min_endp, max_endp - this specifies the range. */ double Histogram_json::range_selectivity(Field *field, key_range *min_endp, @@ -1594,7 +1640,7 @@ double Histogram_json::range_selectivity(Field *field, key_range *min_endp, void Histogram_json::serialize(Field *field) { - field->store((char*)json_text, strlen((char*)json_text), &my_charset_bin); + field->store(json_text.data(), json_text.size(), &my_charset_bin); } @@ -2052,13 +2098,16 @@ class Histogram_builder_json : public Histogram_builder } void build_json_from_histogram() { - Json_writer *writer = new Json_writer(); - writer->start_array(); + Json_writer writer; + writer.start_object(); + writer.add_member(Histogram_json::JSON_NAME).start_array(); + for(auto& value: bucket_bounds) { - writer->add_str(value.c_str()); + writer.add_str(value.c_str()); } - writer->end_array(); - Binary_string *json_string = (Binary_string *) writer->output.get_string(); + writer.end_array(); + writer.end_object(); + Binary_string *json_string = (Binary_string *) writer.output.get_string(); Histogram_json *hist= (Histogram_json*)histogram; hist->set_json_text(bucket_bounds.size(), (uchar *) json_string->c_ptr()); } @@ -2080,42 +2129,6 @@ Histogram_base *create_histogram(Histogram_type hist_type) } -bool json_get_array_items(const char *json, const char *json_end, int *value_type, std::vector<std::string> &container) { - json_engine_t je; - int vl; - const char *v; - - json_scan_start(&je, &my_charset_utf8mb4_bin, (const uchar *)json, (const uchar *)json_end); - - if (json_read_value(&je) || (*value_type = je.value_type) != JSON_VALUE_ARRAY) - { - return false; - } - - std::string val; - while(!json_scan_next(&je)) - { - switch(je.state) - { - case JST_VALUE: - *value_type = json_smart_read_value(&je, &v, &vl); - if (je.value_type != JSON_VALUE_STRING && - je.value_type != JSON_VALUE_NUMBER && - je.value_type != JSON_VALUE_TRUE && - je.value_type != JSON_VALUE_FALSE) - { - return false; - } - val = std::string(v, vl); - container.emplace_back(val); - break; - case JST_ARRAY_END: - break; - } - } - return true; -} - C_MODE_START int histogram_build_walk(void *elem, element_count elem_cnt, void *arg) diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 48b9e24b8f0..eb982f9c4b3 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -152,7 +152,7 @@ class Histogram_base : public Sql_alloc { public: virtual bool parse(MEM_ROOT *mem_root, Field *field, Histogram_type type_arg, - const uchar *ptr, uint size)= 0; + const char *hist_data, size_t hist_data_len)= 0; virtual void serialize(Field *to_field)= 0; virtual Histogram_type get_type()=0; @@ -187,7 +187,7 @@ class Histogram_binary : public Histogram_base { public: bool parse(MEM_ROOT *mem_root, Field *, Histogram_type type_arg, - const uchar *ptr_arg, uint size_arg) override; + const char *hist_data, size_t hist_data_len) override; void serialize(Field *to_field) override; Histogram_type get_type() override { return type; } @@ -350,14 +350,16 @@ class Histogram_json : public Histogram_base uint8 size; /* Number of elements in the histogram */ /* Collection-time only: collected histogram in the JSON form. */ - uchar *json_text; + std::string json_text; // Array of histogram bucket endpoints in KeyTupleFormat. std::vector<std::string> histogram_bounds; public: + static constexpr const char* JSON_NAME="histogram_hb_v1"; + bool parse(MEM_ROOT *mem_root, Field *field, Histogram_type type_arg, - const uchar *ptr, uint size) override; + const char *hist_data, size_t hist_data_len) override; void serialize(Field *field) override; @@ -375,7 +377,8 @@ class Histogram_json : public Histogram_base void set_json_text(ulonglong sz, uchar *json_text_arg) { size = (uint8) sz; - json_text= json_text_arg; + json_text.assign((const char*)json_text_arg, + strlen((const char*)json_text_arg)); } uint get_size() override @@ -481,8 +484,9 @@ class Column_statistics ulonglong avg_frequency; public: - + /* Histogram type as specified in mysql.column_stats.hist_type */ Histogram_type histogram_type_on_disk; + Histogram_base *histogram_; uint32 no_values_provided_bitmap()