8.0 Release Notes
Release Highlights
Note
For a list of all new features and bug fixes, see the maintenance release changelog.
Code Engine - Powered by Wasm
The Code Engine feature allows you to create UDFs/TVFs using code compiled to WebAssembly (Wasm). This feature supports any language that can compile to the Wasm core specification.
For more information, see Code Engine - Powered by Wasm.
Improved Seekability in Universal Storage Tables
These enhancements will deliver large performance gains for transactional workloads on universal storage tables.
Added support for fast seeking into JSON columns in a universal storage table using subsegment access.
Improved seek performance for string data types for universal storage for LZ4 and run-length encoded (RLE) data.
Recursive Common Table Expressions
Recursive common table expressions (CTE) are now supported by SingleStoreDB. Previously, complex operations including temporary tables within a stored procedure would be needed to perform the actions that a simple recursive CTE query can handle.
For more information, see WITH (Common Table Expressions).
Initial IPv6 Support
Preview feature: Added initial support for IPv6 to the SingleStoreDB engine (memsqld
) and SingleStoreDB Toolbox. Refer to the allow_ipv6
and bind_address
engine variables for more information.
Other Improvements and Fixes
New Information Schema Views
Added the MV_RECOVERY_STATUS view which includes information about the status of the current recovery process.
Added several Replication Management views.
Subselect related lockdown messages are now more informative and they indicate the line number and character offset of the subselect that caused the error. In addition, up to 100 bytes of text from the beginning of the referred subselect is also displayed.
SELECT (SELECT DISTINCT t1.a FROM t ORDER BY a) FROM t t1; Old output: "Feature 'subselect containing dependent field inside group by' is not supported by SingleStore." New output: "Feature 'subselect containing dependent field inside group by' is not supported by SingleStore. Near '(SELECT DISTINCT t1.a FROM t ORDER BY a) FROM t t1' at line 1, character 7."
Decreased the memory overhead for columnstore cardinality statistics by 25% as the first phase of an overall project to improve memory for auto-stats in general.
Improved performance for user-defined functions (UDFs) and Stored Procedures that take JSON arguments, and the
JSON_TO_ARRAY
command.Updated the supported syntax for DROP … FROM PLANCACHE so plans on a specified node and plans from all aggregators based on the query text can be dropped.
DROP plan_id FROM PLANCACHE ON NODE node_id; DROP PLAN FROM PLANCACHE [ON AGGREGATORS] FOR QUERY <query_text>;
Setting Collation for String Literals
You can set the collation for string literals explicitly:
SELECT "My string" COLLATE utf8mb4_unicode_ci;
Created the
ALTER USER
permission. Users must have this permission or theGRANT
permission to be able to execute theALTER USER
command.Added
ALTER USER ... ACCOUNT LOCK
to manually lock accounts:ALTER USER 'test'@'%' ACCOUNT LOCK; ALTER USER 'test'@'%' ACCOUNT UNLOCK;
Added sampling (a small portion of the rows in the table are used for analysis) for Reference tables as part of query optimization.
Improved the performance of the
PROFILE
functionality such as lower memory overheads, lower performance impacts to OLAP queries, and better statistics collecting.Added support for improved segment elimination in queries with
WHERE
clauses containingDATE
andTIME
functions. The functions that are supported for segment elimination are DATE, DATE_TRUNC, TIMESTAMP, UNIX_TIMESTAMP, and YEAR. See each topic for specific examples.The data_conversion_compatibility_level engine variable can now be set to
'8.0'
for stricter data type conversions. This will now be the default value. This newdata_conversion_compatibility_level
setting additionally flags invalid string-to-number conversion inINSERT
statements.The sync_permissions engine variable default value is now
ON
.The default value only impacts newly installed clusters. Existing clusters must be manually updated to the variable.The enable_auto_profile engine variable now has a third value:
LITE
.LITE
is the new default value for new customers. It has a lower memory overhead thatON
. The default value for existing customers isON
.The columnstore_small_blob_combination_threshold engine variable default value has been changed to 5242880 bytes. Prior to the 8.0 release, the default value was 33554432 bytes.
Added support for encoded
GROUP BY
clauses in queries containing conditional and character expressions in aggregate functions.Expanded the type of query execution operations (hash joins, window functions, and sort operations) to offload memory to disk using spilling to allow a large memory footprint query to succeed at the cost of query execution times in a memory constraint environment.
Added support for ? and [ ] glob patterns to FS pipelines.
Added the optional parameter
DEFINER
for CREATE PROCEDURE, FUNCTION, and AGGREGATE.Added ability for a JSON computed column to be returned in a query instead of the entire document.
Added ability to use use the
ORDER BY
clause with theJSON_AGG
function.Expressions can be assigned to system variables. System variables, literals, or any combination of these can be referenced using built-ins like
CONCAT
as a variant of complex expressions.For unlimited storage databases, SingleStoreDB caches data from remote storage on local disks or SSDs. It uses a modified least-recently-used (LRU(2)) replacement policy. Information is retained to indicate if objects are frequently-accessed. This reduces the chance that a single large query will flush frequently-accessed data from the cache.
Added support for the
AUTO
option in the computed column definition clause of aCREATE TABLE
statement to automatically infer the data type of a computed column expression. For more information, see CREATE TABLE.Added two Workload Management engine variables:
workload_management_queue_size_allow_upgrade
andworkload_management_dynamic_resource_allocation
. These variables work together to dynamically move queries to another queue if the original queue is saturated.Storage of CHAR(<length>) as VARCHAR(<length>): For a column defined as type
CHAR
of lengthlen
, SingleStoreDB will store the column as aVARCHAR
of lengthlen
iflen
greater than or equal to the value of the new engine variablevarchar_column_string_optimization_length
. If the value of the variable is0
, the column is not stored as aVARCHAR
.After adding a new leaf node to a cluster and rebalancing partitions, the blob cache on the new leaf is warmed with copies of blobs from the node originally holding data that has been moved to the new leaf. This is done before the new leaf begins handling queries. It is fully automatic.
The
BACKUP
command no longer blocks theALTER TABLE
and several other commands for the duration of the backup. This allows you to run commands likeTRUNCATE
on your tables even during the backup of a very large deployment. For a complete list of commands no longer blocked during backup refer to Commands Not Blocked by the BACKUP Process.Added the ability to use
JSON_MATCH_<ANY>
. Returns true if, in the JSON, there is a value at the specified filter path which evaluates the optional filter predicate as true. If no filter predicate is provided, will return true if the filter path exists.Made the following Selectivity Estimation improvements:
Enabled sampling for reference tables.
Improved date/time histogram estimates by utilizing a heuristic when the current date/time is outside of the histogram range.
Added selectivity estimation for filters containing uncorrelated scalar subselects. This behavior can be controlled by the engine variable
exclude_scalar_subselects_from_filters
. This change has the side-effect of enabling bloom filters more often.Changed the estimation source to heuristics when sampling is turned on but the total sampled rows are zero.
Added ability to use histogram estimation for filtering predicates that use a stored procedure parameter.
Increased the default value for engine variable
optimizer_cross_join_cost
to reduce the chance of Cartesian Joins being included when there are incorrect estimations.Improved the
GROUP BY
cardinality estimates for predicates usingOR
expressions.Enabled ability to combine histogram and sampling selectivity estimates by default.
Made the following Query Optimization enhancements:
Moved sub-queries for some outer joins from the
ON
clause to aWHERE
clause to enable subselects to be rewritten as joins.Enabled repartition on expressions.
Added ability to use
GROUP BY
push down for outer joins.Enhanced column pruning by eliminating derived duplicate columns.
Removed redundant
GROUP BY
clauses that are implied by equi-joins.