Release notes for Hologres functions

V4.2

Version	Function type	Function	Category	Description	Execution engine	Documentation
4.2.0	Aggregate functions	bit_and, bit_or, bool_and, every, json_agg, json_object_agg, jsonb_agg, jsonb_object_agg, regr_count, stddev, variance	Performance optimization	HQE supports 13 aggregate functions, including bitwise aggregation, boolean aggregation, JSON aggregation, and regression statistics.	HQE	General-purpose aggregate functions
4.2.0	JSON functions	json_array_element, json_array_element_text, json_build_array, json_build_object, json_object, json_object_field, json_object_field_text, json_object_keys, jsonb_object_keys, json_typeof, jsonb_array_length, jsonb_build_array, jsonb_build_object, jsonb_delete, jsonb_object, jsonb_strip_nulls, jsonb_typeof	Performance optimization	HQE supports 20 common JSON and JSONB functions for construction, field access, key extraction, type determination, and merge/delete operations.	HQE	JSON and JSONB
4.2.0	Math functions	acos, asin, atan, atan2, cos, cot, dcbrt, degrees, dexp, dlog1, dlog10, dsqrt, exp, ln, radians, sin, tan, width_bucket	Performance optimization	HQE supports 18 mathematical functions, including trigonometric, logarithmic, exponential, square root, angle conversion, and bucket functions.	HQE	Mathematical functions
4.2.0	Bitwise operation functions	bit_out, bitcmp, biteq, bitge, bitgt, bitle, bitlt, bitne, bitnot, bittypmodout, bitxor, varbitcmp, varbiteq, varbitge, varbitgt, varbitle, varbitlt, varbitne, varbittypmodout	Performance optimization	HQE supports 19 bitwise functions for bit and varbit type comparisons and logical operations.	HQE	—
4.2.0	Boolean functions	booleq, boolge, boolgt, boolin, boolle, boollt, boolne, boolout, boolsend	Performance optimization	HQE supports 9 boolean functions for comparison operations and I/O of the boolean type.	HQE	—
4.2.0	Numeric comparison and arithmetic functions	float48ge, float48gt, float48le, float48lt, float48ne, float4abs, float4div, float4mi, float4mul, float4um, float4up, float84eq, float84ge, float84gt, float84le, float84lt, float84ne, float8abs, float8div, float8mi, float8mul, float8um, float8up, int28eq, int28ge, int28gt, int28le, int28lt, int28ne, int2_avg_accum_inv, int2abs, int2eq, int2int4_sum, int2lt, int2not, int2shl, int2shr, int2um, int2up, int2xor, int48eq, int48ge, int48gt, int48le, int48lt, int48ne, int4_avg_accum_inv, int4abs, int4eq, int4inc, int4lt, int4not, int4shl, int4shr, int4um, int4up, int4xor, int82eq, int82ge, int82gt, int82le, int82lt, int82ne, int8abs, int8dec, int8not, int8shl, int8shr, int8um, int8up, int8xor, numeric_cmp, numeric_eq, numeric_ge, numeric_gt, numeric_le, numeric_lt, numeric_ne, numeric_out, numerictypmodout	Performance optimization	HQE supports 80 functions for int2, int4, int8, float4, float8, and numeric type comparisons, arithmetic operations, and cross-type comparisons.	HQE	—
4.2.0	Date and time functions	date_cmp, date_eq, date_ge, date_gt, date_le, date_lt, date_ne, date_mi_interval, date_mii, date_pl_interval, date_pli, datetimetz_pl, intervaltypmodout, isfinite, justify_interval, make_date, time, time_pl_interval, time_mi_interval, time_mi_time, timestamp, timestamp_cmp, timestamp_eq, timestamp_ge, timestamp_gt, timestamp_le, timestamp_lt, timestamp_ne, timestamp_mi, timestamp_pl_interval, timestamp_mi_interval, timestamptz_cmp, timestamptz_eq, timestamptz_ge, timestamptz_gt, timestamptz_le, timestamptz_lt, timestamptz_ne, timestamptz_pl_interval, timestamptz_mi_interval, timetz_cmp, timetz_eq, timetz_ge, timetz_gt, timetz_le, timetz_lt, timetz_ne etc.	Performance optimization	HQE supports 99 functions for date, time, timestamp, timestamptz, timetz, and interval type comparisons, arithmetic operations, and cross-type conversions.	HQE	Date and time functions
4.2.0	String functions	byteacat, byteasend, decode, encode, length, overlay, pg_encoding_max_length, position, regexp_replace, reverse, similar_escape, similar_to_escape, substr, substring, text_pattern_ge, text_pattern_gt, text_pattern_le, text_pattern_lt, textlen	Performance optimization	HQE supports 19 string functions for substring extraction, regex replacement, string reversal, encoding conversion, bit string operations, length calculation, and pattern matching.	HQE	String functions
4.2.0	Type conversion and I/O functions	bool::text, btfloat48cmp, btfloat4cmp, btfloat84cmp, btfloat8cmp, bttext_pattern_cmp, charin, charout, charsend, float4in, float8in, in_range, int2in, int2out, int4, int4in, int4out, int8, int8dec_any, int8in, int8out, textin, textout etc.	Performance optimization	HQE supports 34 functions for type I/O, type conversion, and B-tree comparisons across multiple data types.	HQE	—
4.2.0	Other functions	char, chareq, charge, chargt, charle, charlt, charne, interval, interval_div, interval_mi, interval_mul, interval_um, mul_d_interval, octet_length, overlaps, text, to_hex, to_json, to_jsonb	Performance optimization	HQE supports 23 functions for interval arithmetic, time range overlap detection, character type operations, and bytea operations.	HQE	—

V4.0

Version	Function type	Function	Category	Description	Execution engine	References
4.0.1	Time and date conversion	Multiple functions	New	Supports multiple time and date truncation functions to facilitate migration from ClickHouse and Apache Doris.	HQE	Date and time functions

V3.2

Version	Function type	Function	Category	Description	Execution engine	References
3.2.1	Array function	hg_array_map hg_array_fill hg_array_filter hg_array_sort hg_array_first_index	New	Supports Lambda expressions and higher-order array functions containing Lambda expressions.	HQE	LAMBDA expressions and functions

V3.1

Version	Function type	Function	Category	Description	Execution engine	References
3.1.1	Roaring bitmap functions	Multiple roaring bitmap functions support 64-bit.	Enhanced feature	Multiple roaring bitmap functions support 64-bit.	HQE	roaringbitmap
3.1.1	Presto-compatible functions	Multiple Presto-compatible functions are supported.	New function	Multiple Presto-compatible functions are supported.	HQE	Presto functions
3.1.1	Spark-compatible functions	Multiple Spark-compatible functions are supported.	New function	Multiple Spark-compatible functions are supported.	HQE	Spark functions

V3.0

Version

Function type

Function

Category

Description

Execution engine

References

3.0.1

Data type conversion functions

TRY_CAST

Enhanced feature

The following data types are supported by the TRY_CAST function:

DATE
TIMESTAMP
TIMESTAMP_TZ

HQE

TRY_CAST

3.0.1

Aggregate functions

STRING_AGG
ARRAY_AGG

Performance improvement

The following SQL functions that contain the deduplication and sorting clauses are supported by HQE:

STRING_AGG(distinct|order by col)
ARRAY_AGG(distinct|order by col)

Note

To use the functions, you must run the set hg_experimental_enable_distinct_or_orderby_agg_in_hqe=true; command to set the GUC parameter to true.

HQE

V2.2

Version	Function type	Function	Category	Description	Execution engine	References
2.2.7	Data type conversion functions	text::roaringbitmap	Performance improvement	Conversion between the TEXT and roaring bitmap data types is supported by HQE.	HQE	roaringbitmap
2.2.1	Data type conversion functions	TRY_CAST	New function	The data type conversion function TRY_CAST is supported by Hologres.	HQE	TRY_CAST
2.2.0	Traffic analysis functions	Path analysis functions	New function	Path analysis functions are supported to facilitate business traffic analysis in an efficient manner.	HQE	Path analysis functions
2.2.0	General-purpose aggregate functions	CORR COVAR_POP COVAR_SAMP REGR_AVGX REGR_AVGY REGR_INTERCEPT REGR_R2 REGR_SLOPE REGR_SXX REGR_SXY REGR_SYY	Performance improvement	Multiple general-purpose aggregate functions are supported by HQE.	HQE	General-purpose aggregate functions

V2.1

Version	Function type	Function	Category	Description	Execution engine	References
2.1.33	Data type conversion functions	text::roaringbitmap	Performance improvement	Conversion between the TEXT and roaring bitmap data types is supported by HQE.	HQE	roaringbitmap
2.1.33	Roaring bitmap functions	ROARINGBITMAP_IN	New function	This function is used to convert data of the TEXT type into the roaring bitmap type.	HQE	roaringbitmap
2.1.0	Funnel analysis functions	RANGE_FUNNEL RANGE_FUNNEL_TIME RANGE_FUNNEL_LEVEL	New function	The RANGE_FUNNEL, RANGE_FUNNEL_TIME, and RANGE_FUNNEL_LEVEL functions are supported by Hologres. RANGE_FUNNEL: Calculates grouping statistics and aggregates the calculation results of a time window. RANGE_FUNNEL_TIME: Decodes the event time in the result returned by the RANGE_FUNNEL function. RANGE_FUNNEL_LEVEL: Decodes the event level in the result returned by the RANGE_FUNNEL function.	HQE	Funnel functions
2.1.0	Bit-sliced Index (BSI) functions	BSI constructor function BSI expansion function BSI query function BSI aggregate and analytic function	New function	BSI functions are supported to improve user profile analysis capabilities.	PQE	BSI functions
2.1.0	Supported MySQL functions	IF	New function	The IF function is supported by Hologres.	HQE	IF
2.1.0	Array functions	ARRAY[] ARRAY_APPEND	Performance improvement	The ARRAY[] function that is used to obtain an array subscript and the ARRAY_APPEND function that is used to append elements to an array are supported by HQE.	HQE	Array functions
2.1.0	String functions	REGEXP_SPLIT_TO_TABLE	Performance improvement	The REGEXP_SPLIT_TO_TABLE function that is used to split strings is supported by HQE.	HQE	String functions
2.1.0	Set returning functions	GENERATE_SERIES	Performance improvement	The GENERATE_SERIES function that is used to generate a sequence is supported by HQE. The data types can be INT, BIGINT, or NUMERIC.	HQE	Set returning functions

V2.0

Version	Function type	Function	Category	Description	Execution engine	References
2.0.31	Date and time conversion functions	DATEADD DATEDIFF LAST_DAY	New function	The DATEADD, DATEDIFF, and LAST_DAY functions are supported by Hologres.	HQE	Date and time functions
2.0.24	JSONB functions	TRY_CAST_TO_JSONB	New function	The TRY_CAST_TO_JSONB function is supported by Hologres.	HQE	JSON and JSONB
2.0.8	Geographic information analysis functions	ST_DISTANCE_SPHERE_S2 ST_DWITHIN_S2	New function	The ST_DISTANCE_SPHERE_S2 function that is used to calculate the spherical distance and the ST_DWITHIN_S2 function that is used to check the distance relationship are supported by Hologres.	HQE	PostGIS spatial functions
2.0.1	Supported ClickHouse functions	HG_SIP_HASH_64(TEXT)	New function	The ClickHouse built-in function HG_SIP_HASH_64(TEXT) is added.	HQE	ClickHouse functions
2.0.1	Data type conversion functions	TEXT::TIMESTAMP CAST(TEXT AS TIMESTAMP) TIMESTAMP::TEXT CAST(TIMESTAMP AS TEXT)	Performance improvement	Conversion between the TEXT and TIMESTAMP data types is supported by HQE.	HQE	Optimize internal table query performance
2.0.1	String functions	LEFT RIGHT	Performance improvement	The string truncation functions LEFT and RIGHT are supported by HQE.	HQE	String functions

V1.3

Version	Function type	Function	Category	Description	Execution engine	References
1.3.39	Date and time functions	EXTRACT DATE_PART DATE_TRUNC	Performance improvement	The following date and time truncation functions are supported by HQE: EXTRACT DATE_PART DATE_TRUNC	HQE	Date and time functions
1.3.39	Roaring bitmap functions	RB_BUILD	Performance improvement	The RB_BUILD function allows you to use columns of the ARRAY type as input parameters.	HQE	roaringbitmap
1.3.37	Array operators	ARRAY && ARRAY	Performance improvement	The && operator allows you to use columns of the ARRAY type as input parameters.	HQE	Array functions
1.3.36	Supported ClickHouse functions	TOSTRING TOINT64 TOINT32 TODATE TOFLOAT64	New function	Some ClickHouse type conversion functions are supported.	HQE	ClickHouse functions
1.3.36	Aggregate functions	MAX_BY MIN_BY	New function	The MAX_BY and MIN_BY functions are supported.	HQE	MAX_BY and MIN_BY
1.3.19	Array functions	ARRAY_MAX ARRAY_MIN ARRAY_CONTAINS ARRAY_EXCEPT ARRAY_DISTINCT ARRAY_UNION	New function	Some array functions are supported.	HQE	Array functions
1.3.18	Array functions	ARRAY_SORT	Performance improvement	The ARRAY_SORT function supports more types of arrays.	HQE	Array functions
1.3.16	Roaring bitmap operators	\| << >> -	New function	The roaring bitmap operators are supported in specific scenarios.	HQE	roaringbitmap
1.3.16	Roaring bitmap functions	RB_INDEX RB_FILL RB_CLEAR RB_RANGE RB_RANGE_CARDINALITY	New function	Some roaring bitmap functions are supported.	HQE	roaringbitmap
1.3.12	JSON functions	IS_VALID_JSON	New function	The IS_VALID_JSON function is supported to validate JSON strings.	HQE	JSON and JSONB
1.3.1	Aggregate functions	UNIQ	New function	The UNIQ function is supported for precise deduplication.	HQE	UNIQ
1.3.1	String functions Array functions	ARRAY_AGG STRING_AGG	Performance improvement	Filter conditions are supported in expressions.	HQE	String functions Array functions
1.3.1	JSON functions	ROW_TO_JSON	New function	A JSON function is supported.	PQE	ROW_TO_JSON
1.3.1	String functions	ROW()	New function	Some string functions are supported.	HQE	String functions
1.3.1	PostGIS spatial functions	Geometry Constructors Geometry Accessors Geometry Editors Spatial Reference System Geometry Input Geometry Output Spatial Relationships Measurement Overlay Geometry Processing Bounding Box Linear Referencing	Performance improvement	HQE supports most spatial functions and supports PostGIS at the production level.	HQE	PostGIS spatial functions
1.3.1	Supported Oracle functions	Other functions such as DATE functions and STRING functions	New function	The orafce extension is introduced to support Oracle functions.	HQE	Supported Oracle functions