Additional Features and Improvements in this Release

Storage

  • Partition splitting, implemented as an option to the BACKUP command, allows you double the number of partitions in a database easily. You may wish to increase the number of partitions to accommodate more leaves, or to enable more parallelism.

  • Added support for full backups to Google Cloud Storage (GCS). Also added support for incremental backups to GCS, S3, and Azure. For more information, see the Backing Up and Restoring Data topic.

  • Added global temporary tables, which are similar temporary tables, except that they exist beyond the duration of a client session.

  • When creating a table, you can now specify that a DATETIME or DATETIME(6) column should be populated with the current timestamp when inserting or updating a row in the table.

  • Highly selective joins on columnstores are now more efficient. A highly selective join has a very selective filter on one table which produces a few rows and these rows are joined with another table.

Query Optimization

  • Enhanced cardinality estimation for joins using histograms.

  • Updated the behavior of the DROP ALL FROM PLANCACHE command; it now purges all query plans from memory and disk.

  • Added more support for queries that use scalar nested subselects.

  • Added the NOPARAM() function that disables the parameterization of constants when a query is compiled.

  • Improved optimization of hash joins involving expressions.

Usability and Programmability

  • You can now access record variable fields inside of a SQL statement in procedural SQL.

  • Added the information_schema.MV_BLOCKED_QUERIES view, which provides a summary of activities that are currently waiting.

  • Added the TO_NUMBER() function, which converts a CHAR or a VARCHAR value to a DECIMAL value.

  • Added the TABLE() function, which converts a procedural SQL array to a set of rows.

  • Added the TRUNC(date) and TRUNC(number) functions. TRUNC(date) lets you truncate a date to a specified granularity. TRUNC(number) lets you truncate a given number to a specified number of decimal places.

  • Added the INET6_ATON() function, which converts an IPv6 or IPv4 internet address from its text representation to its binary representation.

  • Added the [INET6_NTOA() function, which converts an IPv6 or IPv4 internet address from its binary representation to its text representation.

  • Added the REGEXP_SUBSTR() function, which searches for a regular expression pattern and returns the matching substring.

  • Stored procedures now support the CREATE VIEW and DROP VIEW commands.

  • ECHO SELECT now supports ECHO SELECT <expr> where <expr> is a variable or record field. Previously, an alias was required as in ECHO SELECT <expr> as <alias>.

Data Ingest

  • Google Cloud Storage (GCS) Pipelines

Security

  • Added configurable password complexity rules. Now you can specify criteria for passwords, including criteria such as minimum length or the number of uppercase, lowercase, numeric, and special characters.

  • Added a configurable account lockout feature. You can specify the number of times a user can enter an incorrect password before they are locked out of the system. When a user reaches this limit, their account is locked for the specified number of seconds. For more information on the failed login attempt lockout feature, see Securing SingleStore.

  • OPTIMIZE TABLE now requires the INSERT permission.

Other

  • Fixed a race condition where plan eviction during a distributed join would cause the error MemSQL code generation has failed: Cannot compile from background threads.

  • Fixed a bug where decimals lose precision when scientific notation is parsed.

Last modified: July 11, 2023

Was this article helpful?