SingleStoreDB Cloud Release Notes

September 2023

• New Feature: SingleStore Spaces - Find a gallery of notebooks to learn about scenarios that SingleStoreDB covers at: https://www.singlestore.com/spaces/

• Enhancement: Notebooks have been improved with the addition of the following features:

  • SQL Notebooks

  • Hints to connect to external sources for the notebook firewall settings

  • Performance improvements around loading time

  • Jupyterlab 4.0

• New Feature: Datadog integration. Monitor the health and performance of your SingleStoreDB Cloud workspaces in Datadog.

• Enhancement: Enhanced the performance of DDL statements for role manipulation.

• Enhancement: Added two engine variables, jwks_username_field and jwks_require_audience to add flexibility and improve security.

• Enhancement: Added two engine variables: max_expression_query_limit which sets a limit on the number of expressions within an entire query and max_expression_item_limit which sets a limit on the number of expressions within a query item. Both can be set to a range between 100 and the maximum unsigned INT value. Setting these engine variables to the maximum unsigned INT value disables both features.

• Enhancement: Added support for materializing CTEs without recomputing them when the query contains UNION, UNION ALL, and other SET operations. To enable the feature, set the engine variableallow_materialize_cte_with_union to TRUE.

• Bugfix: Fixed several issues causing slow compilation for queries over wide tables.

• New Feature: Persistent Cache/Disk Monitoring - Monitoring dashboard to help explain "What's consuming Persistent Cache" as well as the Blob Cache downloaded/evicted rate.

• Bugfix: Fixed an issue where a crash occurs where the engine improperly rewrites queries with a UNION in an EXCEPT clause.

August 2023

• New Feature: Added support for the REGEXP_MATCH() function. This function returns a JSON array of matching substring(s) within the first match of a regular expression pattern to a string.

• Enhancement: Improved performance of multi-part GCS backups.

• Enhancement: Improved memory consumption in json decoding.

• Enhancement: Introduced a new global engine variable, json_document_max_leaves which limits the number of JSON key paths inferred within a segment. The default value is 10000.

• Enhancement: Introduced a new global variable, dr_min_connection_timeout_ms, which allows users to adjust the minimum timeout period in Disaster Recovery (DR) replication.

• Enhancement: Added support for multiple uncorrelated IN-subselects in more query shapes.

• Enhancement: SKIP PARSER ERRORS is now supported for Kafka. Additionally, a new related engine variable, pipelines_parse_errors_threshold, has been added.

• Enhancement: SingleStoreDB automatically rewrites A=B OR (A IS NULL AND B IS NULL) to null safe equal (A<=>B) to enable hash joins.

• Enhancement: Added support for hash joins on null-accepting expressions in the ON condition of outer joins.

• Bugfix: Fixed an issue with significant memory reduction from spilling for hash join queries with variable length strings (varchar, text, etc.) involved.

• Bugfix: Fixed an issue where BACKUP DATABASE WITH INIT would fail under out-of-memory conditions.

• Bugfix: Fixed a potential crash in NFS backup when encountering an IO error.

July 2023

• Enhancement: Added support for Control Group v2 (cgroup v2).

• Bugfix: Fixed an issue where a large spike in the query memory consumption on a any SingleStoreDB node could cause replicas on the same node to become unrecoverable.

• Bugfix: Fixed a pipeline wrong state issue caused by an error in a table with a computed column.

• Bugfix: Fixed a potential issue where REBALANCE PARTITION would not stabilize to a partition placement on a read replicas workspace.

• Bugfix: Fixed a potential crash in some edge cases when using Parquet pipelines.

• Enhancement: The SingleStore Python Client is now the standard for our notebooks. This upgrade supports the ingestion of dataframes with specialized data types, including geospatial or vector data, into the database. Additionally, it incorporates the Ibis component, enabling Python to directly interact with the database. This allows dataframes to be executed within the database itself, greatly enhancing performance.

• Enhancement: Default values for BLOB/TEXT and JSON columns are allowed, as well as NULL and empty strings.

• Bugfix: Fixed a crash that may occur when using SELECT … FOR UPDATE … LIMIT <n> in a multi-statement transaction when two_phase_commit is ON.

• Bugfix: Fixed a bug where the permissions do not clear for Data Manipulation Language (DML) queries and then leak to subsequent Data Definition Language (DDL) queries.

• Bugfix: Fixed a bug where the mv_disk_usage table would show incorrect results for the plancache directory.

• Bugfix: Fixed a crash that may occur when a function calls more than one external function.

  In the following example get_embedding() is an external function:

  SQL

  Copy

  SELECT DOT_PRODUCT(JSON_ARRAY_PACK(get_embedding('house')), 

• Enhancement: Added an opt-in optimization to Kerberos for HDFS pipelines to reduce the amount of requests.

• Bugfix: Fixed a JSON formatting issue for the PARTITION_UNRECOVERABLE event in MV_EVENTS details column.

• Bugfix: Fixed the dependency on PIPES_AS_CONCAT sql_mode inside user defined functions. The sql_mode state is now stored when a user defined function is created and used whenever the function is executed.

June 2023

• Enhancement: Introduced the 8.1 data_conversion_compatibility_level which provides additional out-of-range checks for the TIMESTAMP data type.

• Bugfix: Fixed a bug where a HashJoin on a materialized CTE gets executed as a NestedLoopJoin in some query shapes causing slow join performance.

• Bugfix: Fixed the SPLITstring function that was not detecting the correct argument type when used inside a table-valued function (TVF) or a user-defined scalar-valued function (UDF).

• Bugfix: Reduced memory usage in situations where tables are repeatedly created and dropped.

• Enhancement: Added mv_disk_usage and mv_data_disk_usage information schema tables that report the breakdown of disk utilization by SingleStoreDB Cloud.

• Enhancement: Added the max_execution_timeengine variable. It is unused and setting this variable has no effect. It exists so certain MySQL-compatible apps that have this variable set will not fail when connected to SingleStoreDB Cloud.

• Bugfix: Fixed an issue on sample tables that can be created with different collations on different partitions.

• Bugfix: Fixed an issue where JSON computed column matching incorrectly triggers a JSON function.

• Bugfix: Fixed a rare case where the query optimizer re-wrote a query into a non-equivalent query which produced more result rows.

• Bugfix: Fixed unlimited storage S3 multipart uploads to properly retry on 200 responses which contain an embedded "SlowDown" error, instead of failing immediately.

• Bugfix: Fixed an issue where memory accumulates over time if reshuffle or repartition operators are executed for many unique query shapes.

• Bugfix: Fixed an issue with crashes that occur when SSL is enabled on universal storage databases.

May 2023

SingleStore Kai (Preview)

SingleStore Kai allows you to run MongoDB queries natively in a SingleStoreDB Cloud workspace. This feature is currently available in these regions. You can enable this feature while creating a workspace. Each SingleStore Kai-enabled workspace has an additional mongodb:// endpoint, that can be used to connect from supported MongoDB tools/applications to run MongoDB queries.

For more information, refer to SingleStore Kai . Here's a few additional references:

• Migrate from MongoDB to SingleStoreDB

• MongoDB to SQL Mapping

• SingleStore Extension Commands

• Supported MongoDB Commands, Data Types, and Operators

New Feature: Introduced a columnstore row data structure (COLUMN GROUP) that will create a materialized copy of individual rows as a separate structure in a columnstore table. This index will speed up full row retrieval and updates.

New Feature: SingleStoreDB Cloud now supports creation of Wasm-based user-defined aggregate functions (UDAFs). Refer to CREATE AGGREGATE for more information.

Enhancement: Introduced the autostats_flush_interval_secs engine variable. It determines when autostats are flushed to disk if they are not used within the specified time. The default value is 600 seconds. If the engine variable is set to 0, autostats will always stay in memory.

Enhancement: Introduced two new Sync variables control the dynamic reuse of WM queues: workload_management_dy-namic_resource_allocation and workload_management_queue_size_allow_upgrade. The default value for workload_management_queue_size_allow_upgrade is 1. This means we can upgrade a medium queue to a large queue until the large queue becomes equal to 1.

Enhancement: Added two new monitoring dashboards: Pipeline Summary and Pipeline Performance. Currently, both dashboards are in preview mode.

Enhancement: Improved JSON encoding speed for sparse schemas (JSON schemas with a very large number of distinct property names across the full set of documents, with most properties missing in each document). This applies only to universal storage.

Enhancement: A disk manager now brokers the usage of disk space between competing uses like data, log, cache, and spilling.

Enhancement: Added capability to create Wasm functions in SingleStoreDB Cloud workspaces with heterogeneous hardware, e.g., with different levels of SIMD support.

New Feature: Added a METADATA_ONLYargument for shard keys which will prevent an index from being created on shard keys thereby saving memory. It can cause queries that would have used that index to run slower.

Enhancement: Reduced processing costs for queries containing an ORDER BY clause by minimizing the amount of data that needs to be processed. This is achieved by only projecting the primary key at the lower levels of the query plan and then joining the primary key with the original data to retrieve the remaining columns. This can significantly improve performance when dealing with large amounts of data and when only a small portion of the data needs to be retrieved.

New Feature: Added support for query profiling that is efficient enough to be enabled all the time by introducing the auto_profile_type engine variable with FULL and LITE options for automatic profiling. The enable_auto_profile engine variable must be set to ON for the FULL or LITE options to work.

Enhancement: The updated full-text search tokenizer in version 8.1 supports utf8mb4. The tokenizer properly tokeni- zes emojis and other extended 4-byte characters. In addition, certain emoji and glyph characters in utf8mb3 are also recognized as their own token rather than being treated as blanks.

Enhancement: Enhanced support for recursive common table expressions (CTEs) by expanding the range of query shapes allowed, improving column type checking and resolution between the base and recursive branches, and adding cross database support. Also, resolved the issue that caused the "ER_UN- SUPPORTED_RECURSIVE_CTE_SHAPE" error with the accompanying message about dependen- cy on a streaming result table outside the cycle.

New Feature: Added two Information Schema views for tracking connection attributes: LMV_CONNECTION_ATTRIBUTES and MV_CONNECTION_ATTRIBUTES.

Enhancement: Added support to optimize table scans with an Order-By-Limit by (a) do the order and limit first, with only the minimum needed columns, and then (b) using a self-join to retrieve the additional necessary columns for only the qualifying rows. This optimization can be enabled/disabled via the optimizer_enable_orderby_limit_self_join session variable, which is ON by default.

New Feature: Added ATTRIBUTE and COMMENT fields for users. These can be set via the CREATE USER and ALTER USER commands. The values are shown in the INFORMATION_SCHEMA.USERSview.

New Feature: Notebooks feature (Preview) is available to all customers through our Cloud service. It gives users the ability to marry SQL and Python interoperably and in a secure way. This is available in the Develop section within the Cloud Portal.

Bugfix: The sql_mode session variable now shows up in SHOW VARIABLES and the mv_global_variables view.

Bugfix: Fixed an issue that could cause an incorrect error message to be generated on an out of memory error.

Bugfix: Fixed an issue where a NULL could be pushed down to a union which could cause the type-cast/collation to be lost, resulting in an error: "Operation 'Scanning a hash join build' is not allowed".

Bugfix: Fixed an issue with DATETIME columns with ON UPDATE CURRENT_TIMESTAMP defaults that could cause incorrect results.

Enhancement: Removed EXECUTE privilege requirement for MongoDB API internal UDFs.

Enhancement: Changed system information built-ins to use utf8_general_ci collation for the collation_server.

Enhancement: Removed the restrictions on the names of JWT users. Customers may now create users with names that look like UUIDs and emails.

Enhancement: Improved the performance of a left join of a reference table with a sharded table by pushing the reference table clauses to the leaves. The engine variable disable_reference_table_leftjoin_where_pushdown must be set to "OFF" to enable this operation.

Bugfix: Fixed an issue with optimizing queries containing no-operation predicates such as 1=1.

Bugfix: Fixed rare issue with sync durability and unlimited storage that could cause log file corruption in the blob storage.

Enhancement: Added more information about blob files that need to be downloaded from unlimited storage to the blob cache when running PROFILE.

Bugfix: Fixed an issue where the query optimizer could choose incorrectly between using the HashGroupBy or the StreamingGroupBy operators.

Bugfix: Disabled semi-join operator reduction optimization.

April 2023

Bugfix: Fixed an issue where errors could occur during backup when large files are copied.

Bugfix: Fixed an issue where S3 pipelines could stop loading data from the bucket if files are deleted from the bucket while the pipeline is active.

Bugfix: Fixed an issue where, in rare cases, an UPDATE statement with a left join in the filter clauses could fail to execute.

Bugfix: Fixed an issue where replica blobs could be loaded into the blob cache with an incorrect LRU timestamp.

New Feature: Added support for LOAD DATA from S3 for Avro and Parquet data.

New Feature: Added  JSON_INCLUDE/EXCLUDE_MASK function. When applied to a JSON document it will return a subset of the document based on the mask.

Bugfix: Fixed a potential crash issue in UPDATE queries that involve joins and that have scalar subselects in SET clauses.

Bugfix: Fixed an issue where running DROP PLAN FROM PLANCACHE ... FOR QUERY on a query with invalid syntax could cause a crash.

Enhancement: Updated timezone metadata to include Mexico's latest timezone change.

Enhancement: Added a new information schema view internal_table_statistics which shows memory use of SingleStoreDB internal metadata tables. The columns displayed are the same as those shown for table_statistics.

Bugfix: Fixed a column name resolution issue for recursive CTEs when the column type is the same across the base branch and the recursive branch.

Bugfix: Fixed an issue where CLEAR ORPHAN DATABASE could cause data loss if run when the master aggregator has a database in an unrecoverable state.

Bugfix: Added the option to use HTTPS with memsql_exporter. To use HTTPS, enable the engine variables exporter_use_https, exporter_ssl_cert, and exporter_ssl_key.

Bugfix: Fixed an issue with missing row counts during optimization when sampling is disabled.

Bugfix: Fixed an issue where background mergers were not stopping quickly enough, resulting in delayed DDL operations or REBALANCE commands.

Bugfix: Fixed an issue in information schema view JSON_COLUMN_SCHEMA where incorrect details were being shown for the leaf columns.

Bugfix: Fixed an issue where the memory used by an external function was not being freed quickly enough.

Enhancement: The default_partitions_per_leaf global variable will no longer be user-settable in SingleStoreDB Cloud, ensuring that it's always defined for the most optimal performance, according to the current resource size.

Bugfix: Fixed an issue where filters using the range_stats column in the information_schema.optimizer_statistics table were not allowed.

Bugfix: Fixed an issue where incorrect results could be returned when segment-level flexible parallelism is used inside a subquery containing both a shard key filter and either an aggregate, a window function, or the limit clause.

Bugfix: Fixed an issue where an internal result table was not created for a recursive CTE involving data across databases.

New Feature: Added self-service historical monitoring that allows you to quickly and easily understand your application workloads and debug performance-related issues.

March 2023

Enhancement: Improved column type resolution for base and recursive branches in recursive common table expressions (CTEs).

Bugfix: Fixed an error that could occur when attaching databases with a snapshot file of greater than 5 GB.

Bugfix: Fixed a bug where too many rows are sampled from columnstore tables with more than 1 billion rows.

Bugfix: Fixed an issue with histogram estimation in columns with a negative value TIME type.

Bugfix: Fixed "table partition count mismatch errors" that occur due to the following conditions: the system variable enable_workspace_ha is set and there is an upgraded workspace with an attached read replica.

Bugfix: Fixed an issue with DDL endpoint queries using a lower-than-specified query parallelism setting when workspaces are enabled.

Bugfix: Fixed a bug that prevents GROUP BY Push-Down optimization if the join filter contains a mismatched column type.

Bugfix: Fixed a data conversion compatibility level 8.0 error that may occur when sampling columnstore tables.

Bugfix: Fixed a possible deadlock that may occur between the blob cache and the Rowstore Garbage Collection (GC) when the blob cache encounters an out-of-memory error.

Bugfix: Fixed an error caused by setting the collation_server global variable to a non-default value when performing a REBALANCE PARTITIONS resource availability check.

Enhancement: Improved the parsing performance of queries that contain several tables.

Enhancement: The  read_advanced_counters, snapshot_trigger_size, and  snapshot_to_keep  engine variables can now be set on SingleStoreDB Cloud.

New Feature: Added new session variable disable_remove_redundant_gby_rewrite to prevent the GROUP BY columns from being removed when used in an ORDER BY clause.

Enhancement: Introduced disk and memory availability checks that run before a database is allowed to be attached to a workspace.

Enhancement: Added the ability to cache histogram results during optimization to reduce the work performed by the histograms.

Enhancement: Improved the performance of S3 pipelines when Garbage Collection (GC) is enabled.

Enhancement: Added the ability to backup a database to an HTTPS S3 target with an unverified SSL certificate when using the option: verify_ssl: false.

New feature: ORDER BY SELF JOIN, it creates a self join on ORDER BY LIMIT queries to take advantage of differences in bandwidth.

Bugfix: Fixed an issue with column type checking on base and recursive case of a recursive common table expression.

Bugfix: Fixed an issue that may cause a "Table doesn't exist" error when a multi-insert contains expressions and the target table has a computed column as its shard key.

Enhancement: Expanded existing Unicode characters to support Private Use Area (PUA) code points. Including one in the Basic Multilingual Plane (U+E000–U+F8FF) and one in each plane 15 and 16 (U+F0000–U+FFFFD, U+100000–U+10FFFD).

Bugfix: Fixed a crash that could occur when a computed column definition refers to a table name.

New Feature: Added the ability to set the maximum_blob_cache_size_percent global variable for workspaces.

Bugfix: Fixed an issue with promote lock timeout errors that may occur during a rebalance due to a heavy ingest workload, which causes the merger to be slow to pause.

New Feature: Introduced a new global variable subprocess_max_retries, which is used for retrying on retry-able connection failures during select into/backup queries for S3 and GCS.

New feature: ORDER BY SELF JOIN, it creates a self join on ORDER BY LIMIT queries to take advantage of differences in bandwidth.

February 2023

Enhancement: Background snapshots are now allowed to run during BACKUP DATABASE commands. This prevents increased disk usage by logs during a long-running backup.

Bugfix: The CREATE_TIME, CREATE_USER, ALTER_TIME, and ALTER_USER columns in the information_schema.TABLESview are now properly set for views and TVFs (table-valued functions).

Bugfix: Fixed an issue that occurred when the MATCH_PARAM_<type> argument of the JSON_MATCH_ANYfunction was not in a predicate expression.

Enhancement: Improved the performance of the JSON_MATCH_ANY_EXISTSfunction over columnstore tables.

Bugfix: Fixed a profiling issue specific to non-collocated hash joins where the memory usage and disk spilling are missing under the join operators.

Enhancement: Background snapshots are now allowed to run during BACKUP commands. This prevents increased disk use by logs during a long running BACKUP.

Bugfix: Attaching a database that exceeds available workspace memory or persistent cache is now automatically blocked.

Enhancement: Workspace creation and resume times are reduced by running operations in parallel.

Bugfix: Fixed an issue where the database engine locks up on certain out-of-memory errors.

Bugfix: Fixed a parsing issue for queries containing multi-line comments near GROUP BY or ORDER BY clauses.

Enhancement: The num_background_merger_threadsengine variable is now settable on Cloud.

Enhancement: The ORDER BY ALL [DESC|ASC] (or ORDER BY *) syntax is now supported. 

Enhancement: The GROUP BY ALL [DESC|ASC] (or GROUP BY *) syntax is now supported. 

Enhancement: Improved the query execution performance of JSON columns under a higher level of parallelism.

Enhancement: Expanded support for encoded GROUP BY query shapes containing expressions in aggregates.

Bugfix: Fixed an issue where extra CPU was used when a read-only database is attached to a workspace without any writable mount for the read-only database.

Enhancement: Sampling will no longer be used for table size estimation when statistics are present.

Enhancement: Added the /api/v2/jwks_setup endpoint to Data API to allow users to enable JWT Auth in Data API on Cloud. See jwks_setup for more information.

Enhancement: Improved the code generation performance of tables with a large number of indexes.

Bugfix: Fixed an issue causing incorrect trace messages in master logs where clocks were incorrectly advancing from "0".

Enhancement: Added the DATETIME_PRECISION column to both PARAMETER and ROUTINESinformation_schema views. Also, the DATETIME_PRECISION column will include TIME and TIMESTAMP data types in the COLUMNSinformation_schema view.

Enhancement: Added the REVERSE() built-in string function that reverses the target string.

January 2023

Bugfix: Fixed some error handling issues with unlimited storage download and upload processes.

Enhancement: The SHOW TABLE STATUS command now displays the memory usage by GLOBAL TEMPORARY tables.

Bugfix: Fixed a crash when parsing certain Parquet data into a pipeline.

Enhancement: Added support for using a connection link for inserting data with the FORMAT PARQUET option.

Bugfix: Fixed an issue with aggregate functions using incorrect enum/set types that may result in inaccurate output in the operator tree.

Bugfix: Fixed an issue with a transaction leak on the master aggregator when running CREATE TABLE AS SELECT on a child aggregator using autocommit=0 on the connection.

Bugfix: Fixed a bug that may cause a query to hang when comparing an utf8 column with an utf8mb4 constant. This issue occurs when collation_server is set to one of the utf8mb4 collations.

Bugfix: Improved the accuracy of network time reporting in query profiles regarding the time spent sending the results back to the user connection.

Bugfix: Fixed an edge case issue causing a potential memory leak when running an UPSERT statement against a columnstore table.

Bugfix: Fixed an issue that could cause the engine to crash from info_schema query submissions.

Enhancement: Improved the performance of bushy join rewrites.

Bugfix: Fixed an edge case issue where the engine could crash when performing multi-inserts.

Bugfix: The avro schema registry URL portion of the CREATE PIPELINE syntax is now redacted in processlist.

Bugfix: Fixed an issue where the engine could crash during recursive set operations.

Bugfix: The information_schema.statistics "collation" column now correctly indicates whether an index is ascending ("A") or descending ("D").

Enhancement: Improved performance of comparing utf8mb4 strings.

Bugfix: Fixed an edge case issue which could cause the engine to hang during shutdown.

Enhancement: Added the skip_segelim_with_inlist_threshold engine variable, which will skip segment elimination with the IN list if its size is larger than threshold (default is 1000 elements).

Bugfix: information_schema.table_statistics now correctly shows information about global temporary tables.

December 2022

The following features may require you to enable them manually.

New Feature: Improved Seekability in Universal Storage Tables delivers 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.

New Feature: 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).

Enhancement: 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.

Enhancement: Subselect 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."

Enhancement: 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.

Enhancement: Improved performance for user-defined functions (UDFs) and Stored Procedures that take JSON arguments, and the JSON_TO_ARRAY command.

Enhancement: 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>;

Enhancement: Setting Collation for String Literals

You can set the collation for string literals explicitly:

SELECT "My string" COLLATE utf8mb4_unicode_ci;

Enhancement: Created the ALTER USER permission. Users must have this permission or the GRANT permission to be able to execute the ALTER USER command.

Enhancement: Added ALTER USER ... ACCOUNT LOCK to manually lock accounts:

ALTER USER 'test'@'%' ACCOUNT LOCK;

ALTER USER 'test'@'%' ACCOUNT UNLOCK;

Enhancement: Added sampling (a small portion of the rows in the table are used for analysis) for Reference tables as part of query optimization.

Enhancement: Improved the performance of the PROFILE functionality such as lower memory overheads, lower performance impacts to OLAP queries, and better statistics collecting.

Enhancement: Added support for improved segment elimination in queries with WHERE clauses containing DATE and TIME functions. The functions that are supported for segment elimination are DATE, DATE_TRUNC, TIMESTAMP, UNIX_TIMESTAMP, and YEAR.

Enhancement: 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 new data_conversion_compatibility_level setting additionally flags invalid string-to-number conversion in INSERT statements.

Enhancement: 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.

Enhancement: 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 that ON. The default value for existing customers is ON.

Enhancement: 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.

Enhancement: Added support for encoded GROUP BY clauses in queries containing conditional and character expressions in aggregate functions.

Enhancement: 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.

Enhancement: Added support for ? and [ ] glob patterns to FS pipelines.

Enhancement: Added the optional parameter DEFINER for CREATE PROCEDURE, FUNCTION, and AGGREGATE.

Enhancement: Added ability for a JSON computed column to be returned in a query instead of the entire document.

Enhancement: Added ability to use use the ORDER BY clause with the JSON_AGG function.

Enhancement: 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.

Enhancement: For unlimited storage databases, SingleStoreDB caches data within the workspace. 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.

Enhancement: Added support for the AUTO option in the computed column definition clause of a CREATE TABLE statement to automatically infer the data type of a computed column expression. For more information, see CREATE TABLE.

Enhancement: Added two Workload Management engine variables: workload_management_queue_size_allow_upgrade and workload_management_dynamic_resource_allocation. These variables work together to dynamically move queries to another queue if the original queue is saturated.

Enhancement: Storage of CHAR(<length>) as VARCHAR(<length>): For a column defined as type CHAR of length len, SingleStoreDB will store the column as a VARCHAR of length len if len greater than or equal to the value of the new engine variable varchar_column_string_optimization_length. If the value of the variable is 0, the column is not stored as a VARCHAR.

Enhancement: After scaling a workspace, the persistent cache on the workspace is warmed with copies of blobs before new resources begin handling queries. It is fully automatic.

Enhancement: The BACKUP command no longer blocks the ALTER TABLE and several other commands for the duration of the backup. This allows you to run commands like TRUNCATE on your tables even during the backup of a very large deployment.

Enhancement: 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.

Enhancement: 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 using OR expressions.

• Enabled ability to combine histogram and sampling selectivity estimates by default.

Enhancement: Made the following Query Optimization enhancements:

• Moved sub-queries for some outer joins from the ON clause to a WHERE 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.

Bugfix: Fixed an issue where REGEXP and RLIKE were case-insensitive. They are now case-sensitive.

June 2022

New Feature: Management API now supports Workspaces. For more information see, Management API Reference.

The following features may require you to enable them manually.

April 2022

The following features may require you to enable them manually.

New Feature: Flexible Parallelism allows multiple cores on the same node to access the same database partition.

With Flexible Parallelism, as database partitions are created they are divided into sub-partitions. As a query runs on a leaf node, multiple cores working on behalf of the query can process different sub-partitions of a partition in parallel. As an example, if you are currently at one partition per core with Flexible Parallelism, doubling the size of your workspace and then rebalancing will result in two cores for each partition. As a result, a simple query that scans and aggregates all the data in a single partition will now execute more quickly than it did before.

Added new engine variables used for enabling and configuring Flexible Parallelism: sub_to_physical_partition_ratio, query_parallelism_per_leaf_core, and expected_leaf_core_count. The existing engine variable node_degree_of_parallelism is deprecated. For more information, see Flexible Parallelism.

New Function: Added the ISNUMERIC function, used to determine whether the provided expression is a valid numeric type.

New Function: Added the SESSION_USER function, used to return the user name you specified when connecting to the server, and the client host from which you connected.

New Function: Added the SET function, used to initialize a user-defined session variable.

New Function: Added new vector functions, namely VECTOR_ELEMENTS_SUM, VECTOR_KTH_ELEMENT,VECTOR_NUM_ELEMENTS, VECTOR_SORT, and VECTOR_SUBVECTOR.

Enhancement: Added support for TRIM string function. TRIM is now multi-byte safe which means the result of an operation using TRIM is either a valid string or an unmodified string.

Enhancement: Unlimited storage databases now support the BACKUP … WITH SPLIT PARTITIONS command.

Enhancement: The DROP MILESTONE command is used to delete a milestone of a currently attached database.

Enhancement: Improved performance for columnstore seeks into string columns – now it is no longer necessary to scan an entire segment to look up the data for a string value for a row when seeking to find that one row.

New Function: A new clause "AS new_db_name" has been added to the RESTORE DATABASE command which allows the use of the full original backup path if trying to restore to a new database name.

New Function: SECRET - Added the ability to hide credentials from queries. Passing credentials in queries can leave them exposed in plain text during parameterization and they can be seen in logs and the process list. To counter this, you can use the SECRET function (similar in function to NOPARAM). SECRET takes a string (such as a password or other sensitive information) and replaces it with the literal string "<password>" during parameterization. The string is unchanged for the query however.

CALL db.do_something_useful('root', SECRET('super-secret-password'));

See SECRET for more information.

Enhancement: Added per privilege transferability from one user to another via the new TRANSFERABLE clause and SYSTEM_VARIABLES_ADMIN grant in the REVOKE security management command. A new engine variable, privilege_transfer_mode, must be set to per_privilege for this functionality to work as expected. Also, this new functionality will affect the results of the SHOW GRANTS command. If the privilege_transfer_mode engine variable remains on the default value of grant_option, then the output is one row and can include the WITH GRANT OPTION privilege. If the value of privilege_transfer_mode is per_privilege, then the output can be two rows. The first row will display the non-transferable privileges. The second row will display the transferable privileges.

Enhancement: Added new EXPLAIN and PROFILE reproduction clause syntax. EXPLAIN REPRO outputs the explain information in JSON format and provides important debugging information. EXPLAIN REPRO will work for SELECT queries only. The PROFILE REPRO syntax will replace the need to set the engine variable set_profile_for_debug to on. The engine variable will continue to be supported for backward compatibility.

Enhancement: Added ability to match a computed column expression and the same expression appearing in a query, to improve query performance, especially for indexed computed JSON fields. The enhancement promotes data independence between the physical and application layer.

Enhancement: Spilling for GROUP BY statements is enabled by default starting in engine version 7.8. Added an additional engine variable, spilling_minimal_disk_space. If a node has less disk space than spilling_minimal_disk_space (default is 500MB), queries on that node that require spilling will fail instead of spilling to disk.

Enhancement: Materialized CTEs are now on by default and no longer considered a preview feature.Reduced the memory usage of approx_count_distinct by using a more compact representation.

Enhancement: Existing queries are no longer recompiled on minor upgrades (from 7.8.x to 7.8.y for example).

New Function: Added ALTER_TIME, ALTER_USER, and CREATE_USER to the information_schema.TABLES table, to show the time of the latest update the table, the user who made the change, and the user who created the table. For existing tables, the ALTER_TIME value will be NULL until the table is altered. For new tables, ALTER_TIME will be the same as CREATE_TIME.

New Function: Added FLAGS column to the information_schema.VIEWS table, to indicate whether a view is a Table Valued Function (TVF). A value of IS_TABLE_VALUED_FUNCTION indicates a TVF.

Enhancement: An internal component, the LLVM code generation framework, was updated to version 10 from version 3.8. This improves performance of query compilation for DELETES on tables with a very large number of columns.

November 2021

New Feature: Point-in-time recovery (PITR) has moved from preview to production status, and is now supported for production use cases.

Enhancement: By default, all backups are now lock-free. Distributed write transactions no longer have to wait when a backup starts.

New feature: Introduced row-level decompression for the string data type which will increase performance on reads against columnstore tables.  Before this improvement, decompression occurred on the order of blocks of data which consists of 4096 rows.

New Feature: Added support for cross-database INSERT...SELECT into columnstore temporary tables.

Enhancement: Allow spilling hash GROUP BY operator.

New Feature: Added support for SELECT ... INTO AZURE. This command supports the WITH COMPRESSION option, which is described in the next release note.

New Feature: Added the WITH COMPRESSION option to SELECT … INTO FS, SELECT … INTO GCS, and SELECT … INTO S3. WITH COMPRESSION writes the SELECT query results, in compressed .gzip files, to an object store.

New Feature: Added support for new vector functions, namely JSON_ARRAY_UNPACK, SCALAR_VECTOR_MUL, VECTOR_ADD, and VECTOR_MUL.

New Feature: Added support for the current user security model in stored procedures. In this model, when the current user executes a stored procedure, the stored procedure is executed using the security permissions of that user.

New Feature: Added support for external functions, as a preview feature. An external function calls code that is executed outside of a SingleStore database. For more information, see CREATE [OR REPLACE] EXTERNAL FUNCTION.

Enhancement: Improved full-text filter performance when used with other secondary hash index filters. For highly selective full-text filters, the improvement in execution speed can be 10 times faster.

Enhancement: Introduced new logic to determine when to evict a compiled image of a query plan. The logic will sort on the oldest number of plans while considering the explicitly set memory limit usage of each plan. The feature is disabled by default. To enable the logic, the engine variable enable_compiled_images_eviction must be set to ON. The engine variable compiled_images_eviction_memory_limit_mb  is used to set the memory limit.

New Feature: Added a new function, JSON_KEYS, which returns the top-level keys of a JSON object in the form of a JSON array. Optionally, if a keypath is defined, returns the top-level keys from the keypath.

Enhancement: Added support for more query shapes with FULL JOIN or correlated subselects when reference tables are involved. Prior to this release, these query shapes would hit a lockdown error.

New Feature: Added support for query shapes that include repartitioned subqueries containing SELECT statements with aggregated column(s) without a GROUP BY clause. Prior to this release, these query shapes would hit a lockdown error.

New Feature: Added support for LEFT JOIN when the left table is a reference table without a primary key. Prior to this release, this query shape would hit a lockdown error.

Enhancement: Improved query execution for repartition DELETE FROM ...LIMIT and broadcast LEFT JOIN.

Enhancement: Improved selectivity estimate for RIGHT JOIN query shapes when doing BloomFilter decision. See Query Plan Operations for a detailed explanation of BloomFilter and other filtering methods.

Enhancement: Improved query performance using SORT KEY() and KEY () WITH CLUSTERED COLUMNSTORE columns with integer data types.

Enhancement: Added password_expiration column to the information_schema.USERS table. If the password_expiration_seconds engine variable is not enabled, the password_expiration column will be NULL. If the password_expiration_seconds engine variable is enabled, the password_expiration column will display the number of seconds remaining for the password to expire.

Enhancement: Improved the performance of selective filters using string columns in columnstore tables.

New Feature: Added support for UNION between reference and sharded tables.  Prior to this, this query shape would hit a lockdown error.

Enhancement: Improved EXPLAIN output to clarify a result table for a broadcast LEFT JOIN or for a MATERIALIZE_CTE as they can have the same result table name. For broadcast LEFT JOIN, a branch operator is added so that the branching operation on the shared result table is reflected. See Query Plan Operations for a detailed explanation of broadcasts and other distributed data movement.

New Feature: Ingest, Added support for transactions in Kafka pipelines.