# Release Notes ## SingleStore Helios Release Notes New features added to SingleStore Helios listed by month. ## May 2026 ## Enhancements * Improved query plan printing efficiency, lowering query compilation overhead. * Log detailed, step-by-step timings for each operation within `BATCH_PARTITION_TIME`. This behavior is enabled with the `pipelines_extractor_debug_logging` engine variable. * Update Golang to version 1.26.2. ## Bug Fixes * Fixed a bug that may lead to a crash or undefined behavior during query execution. * Fixed a bug that may cause asynchronous LLVM compilation to fail. * Fixed a bug that prevented the table creator from querying it. * Fixed an overflow issue in materialized views that occured when computing `AVG` aggregates on `DECIMAL` values. * Fixed a bug in materialized views that caused incorrect aggregation of `FLOAT` and `DOUBLE` type columns. * Fixed a bug in materialized views with the `DIV` operator in the view definition. * Fixed a bug that potentially caused queries using the `BM25_GLOBAL()` function Global Full-Text statistics. * Updated to Temurin OpenJDK 11 JRE 11.0.31+11 to fix CVEs in OpenJDK. * Prevent messages with multiple nested "Leaf Error"s. Added the `allow_nested_leaf_errors` engine variable which disables the new behavior and reverts to prior behavior allowing nested "Leaf Error" messages. * Fix CVE vulnerabilities in Java third-party packages. ## April 2026 ## Enhancements * Added proper refresh handling for STS tokens during long-running operations. * Allowed Out-of-Memory (OOM) threads to exit early when waiting for the garbage collection mechanism to provide memory buffers. * Added the ability for an optimizer rewrite to merge a derived table with subselects; unwrapping a derived table with subselects is now configurable. * Upgraded librdkafka to version 2.13.2. * Improved join selectivity estimation for predicates of the form `col1 = IFNULL(col2, col3)` by leveraging column cardinality statistics. * Added the ability to avoid group by placement rewrites when grouped tables have no join predicates, improving query plan stability for multi-table aggregates. ## Bug Fixes * Fixed a bug where an optimization on `GROUP BY` fields was incorrectly applied when grouping by a typecast expression. * Improved optimizer handling of Out-of-Memory (OOM) conditions. * Reverted a change to the `JSON_EXTRACT_BIGINT` function that could cause incorrect results or system crashes.  * Allowed Out-of-Memory (OOM) threads to exit early when waiting for the garbage collection mechanism to provide memory buffers. * Fixed a filesystem (FS) pipeline error that occurred when files were deleted while the pipeline was still running. * Fixed an issue where query-time variables (QTVs) referenced in common table expressions (CTEs) caused a "no such table" error. * Fixed a nullability issue in result table columns of an outer join that may lead to a leaf crash. * Fixed an issue where the `PIPELINES_ICEBERG_FILES` view crashed if pre-upgrade partitions were present in the metadata. ## March 2026 ## Enhancements * Added support for `DROP MATERIALIZED VIEW`. * Added support for the `IF EXISTS` option in the `DROP PROJECTION` command. * Extended the range of values for the `row_count` hint to allow values up to the maximum float value. * Added engine variable `skip_sharding_warmup_query`. This variable disables sending single-partition queries to all leaves to warmup. * Added `SNAPSHOT_ID` and `SNAPSHOT_TIMESTAMP` to `PIPELINES_ICEBERG_FILES` information schema view. * Updated the `MV_COLUMNAR_SEGMENT_INDEX` information schema view to return the correct `INDEX_NAME` for multi-value hash indexes on JSON/BSON columns enabling accurate per-index size reporting. * Added column `ACTIVITY_TRACKING_ID` in the `MV_ACTIVITIES`, `MV_ACTIVITIES_CUMULATIVE`, `MV_ACTIVITIES_EXTENDED`, and `MV_ACTIVITIES_EXTENDED_CUMULATIVE` information schema views. * Updated the `KILLED_QUERIES` counter in Resource Governor pools to include queries terminated due to exceeding `QUERY_MEMORY_PERCENTAGE`. * Improved performance of queries on `information_schema.MV_CLUSTER_STATUS` through RPC parallelization. * Improved `JSON_EXTRACT_BIGINT` performance by performing encoding-level extraction instead of full JSON decoding. * Updated materialized views to use `GROUP BY` columns as the default shard key when no shard key is specified. * Improved CDC pipeline error reporting. Multiple parsing errors now display detailed messages, including the associated DDL statement. ## Bug Fixes * Fixed a correctness bug in Materialized Views with time-related types. * Fixed an issue that caused duplicate repartition on join between derived table and reference table. * Fixed an issue where `SHOW GRANTS` no longer displayed `REQUIRE SSL` status after upgrade. * Fixed an issue where the `ADD AGGREGATOR` command failed for node IDs greater than 16384. * Fixed vulnerabilities in aircompressor, jackson-core, and avro. * Fixed an issue where the system allowed creation of semantically identical multi-value indexes on the same column or JSON path. * Fixed an issue where `json_collation` was not synchronized with `character_set_server` after upgrading from version 8.5 or earlier to version 8.7 or later. * Removed password validation for `ALTER USER` statements that do not modify the password. * Fixed an issue where security built-in functions like `CURRENT_SECURITY_ROLES()` and `CURRENT_SECURITY_GROUPS()` returned incorrect results when used within a `CREATE TABLE AS SELECT` (CTAS) statement . * Fixed missing value for `Type` column in `information_schema.USERS` view. * Fixed a crash where one process queried the `MV_PROCESSLIST` view while another updated the status being collected. ## February 2026 ## Enhancements * Result tables now explicitly set table-level collation. ## Bug Fixes * Fixed a crash that occurred when using `PERCENTILE_CONT()` with a constant `ORDER BY` clause. ## January 2026 ## Enhancements * Backfilled index type and index size of vector index metadata during information schema queries. * Introduced background IO submission thread for sync durability and batching multiple IO requests. * Disabled prewritten ingest transformations by default. * Added support for an alter node port. * Extended the `TABLE()` function to have an ordinality column when used with `WITH ORDINALITY` clause. * Added new column `INDEX_NAME` to the `VECTOR_INDEX` information schema view. * Added the `FUNCTION_NAME` column to the `MV_PROCESSLIST` information schema view. When a query is invoked from a stored procedure, this column will display the stored procedure name. * Added support for vended credentials in Iceberg pipelines with S3 storage. * Added display of original line number when re-raising exceptions with `RAISE` statement in stored procedures. * Engine now displays NULL for vector index size and type for vector indexes created in versions older than 9.0. ## Bug Fixes * Fixed a crash when pipelines insert data into writable views. * Fixed a bug that caused stack overflow errors when executing complex queries with multiple joins in `compile_lite` and `compile_lite_first` interpreter mode. * Fixed an internal query conversion issue that caused a mismatch in vector join decisions between the aggregator and leaf nodes. * Updated Golang dependencies to address known Common Vulnerabilities and Exposures (CVEs) in container images and related components. * Fixed double accounting of memory via buffer allocators (`Alloc_spill`, `Alloc_arena`). Now, if memory is used from cache memory, total server memory remains unused. * Fixed a bug that caused a crash when a built-in Table-Valued Function (TVF) table is joined with a table with a join condition involving `JSON_ARRAY_CONTAINS_`. * Fixed a bug in parsing when `LATERAL JOIN` is used with a Table-Valued Function (TVF) with a recursive Common Table Expression (CTE). * Fixed an issue in stored procedures where the callstack is incomplete for nested `RAISE` statements and only displays the last frame instead of all the `RAISE` statement frames. * Fixed an issue promoting demoted voting members when a database does not have a R/W attachment. * Fixed a Common Table Expression (CTE) preprocessing bug involving nested CTEs within `INTERSECT` and `EXCEPT` queries. * Fixed an issue in which a scale operation on a workspace with a R/O attachment of a database could result in temporary tables being dropped from the R/W attachment of the same database in another workspace. * Fixed the affected row count for `SELECT .. FROM .. into FS/S3 ...` queries when row count is greater than 32-bit integer limits. * Fixed a stack corruption bug exposed in very large queries. * Fixed an issue where memory allocated during the spilling phase was not following resource governor memory limits. ## SingleStore Helios Release Notes New features added to SingleStore Helios listed by month. ## December 2025 ## Highlights * Added the ability to skip heuristic checks when using projection for JSON documents for `ORDER BY` self join rewrites. * Added support for increasing the length of variable-length string type columns using `ALTER TABLE MODIFY COLUMN` on columnstore tables. * Allowed users that do not require X509 (mTLS) to connect regardless of the `ssl_ca_for_client_cert` value and presence of the file it points to (if set). * Updated vector index and search to use internal memory allocator instead of malloc. * Added an optimizer rewrite to push `LIMIT`s below external User Defined Functions (UDFs). This optimization can be turned off with the engine variable: `optimizer_disable_limit_pushdown_below_external_udf`. * Added a check to `CREATE` or `ALTER USER` that verifies that the Subject in the client certificate provided during a mutual Transport Layer Security (mTLS) connection matches the subject specified in the `REQUIRE SUBJECT` clause. * Added support for `COUNT()` in aggregated views. * Enabled the use of column names from the base table for materialized view column names. * Limited the vector index cache size to keep it from getting to close to the maximum server memory. * Added the TLS cipher and application name to the auditlog. * Improved phonehome security by adding remote endpoint verification. * Upgraded the Almalinux version to Almalinux 10. * Added `ssl_system_capath` engine variable that defines the system path to the certificate directory. * Added collection of enhanced query stats from kill query Recursive Common Table Expressions (RCTE) writer statistics and sorted iterator error cases. * Added Unix timestamp versions of `start_time` and `event_time` fields in event trace samples exposed by monitoring exporter. * Added option to merge credentials with external secret storage. * Made Resource Governance aware of cgroup limits in Kubernetes clusters. The minimum of host cores and cgroup cores is now used as the CPU limit for governing resource pools. * Modified ingestion to skip `NULL` records while using `SKIP ALL ERRORS` and `SKIP PARSER ERRORS` in kafka pipelines. * Added a new `BLOB_COMPUTE_ID` column for `COLUMNAR_SEGMENTS` and `MV_COLUMSTORE_FILES` information schema views. * Added a new column `MEMORY_USAGE_BYTES` to `LMV_RESOURCE_POOL_STATUS` and `MV_RESOURCE_POOL_STATUS` information schema views. * *Bug Fixes* - Resolved a crash that could occur when reading the `VECTOR_INDEX` information schema view.- Fixed an issue with lateral join. - Fixed vulnerabilities in Netty and OpenJDK CVEs. - Fixed charset UI to show `*_bin` as default collation rather than `*_general_ci`. - Fixed an issue in `ORDER BY` self-join rewrite.- Fixed Transport Layer Security (TLS) connectivity issue when using .NET connection pool. - Fixed an issue with JSON attribute validation for mutual Transport Layer Security (mTLS) in `ALTER USER`.- Disabled the optimize JSON computed column feature. - Fixed an aggregated view crash caused by aliases. - Fixed an issue where unsuccessful distributed transactions could lead to increased memory usage on aggregators. - Fixed `RESTORE DATABASE` in highly-available master aggregator deployments. - Fixed an issue where `information_schema.MV_CLUSTER_STATUS` queries would be recursively forwarded to the master aggregator. - Updated the zookeeper version. - Resolved a crash that could occur when a `TABLE()` function appeared inside a subquery that was part of an `OR` or `AND` condition (for example nested `IN` and `EXISTS` clauses).* Fixed incorrect aggregation of leaf query stats in Query\_completion tracing. * Fixed results for `NOT IN` subselects when the left input table has NULL values. * Fixed a bug with some `NOT IN` subselect query shapes. * Fixed logic for `NOT IN` subselects when nested under `OR`s. * Fixed an issue that caused a stalled cluster in the presence of many concurrent `DROP TEMPORARY TABLE` commands. * Fixed memory tracking for some queries which were not being tracked properly, result tables to be specific. * Fix correctness when using `NOT IN` for some query shapes. ## October 2025 ## Features Feature: **Sequences** - The addition of a `SEQUENCE` modifier for `AUTO_INCREMENT` ensures that a unique sequence of numbers is generated, while greatly reducing the difference between the generated numbers. Additionally, the `SEQUENCE` modifier allows you to set the column type for `AUTO_INCREMENT` columns to other integer types and specify custom starting values. Refer to [AUTO\_INCREMENT AS SEQUENCE](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-table/#section-idm234966854099799.md) for more information. Feature: **Automatic Query Reoptimization with Feedback** - This feature, also referred to as [Feedback Reoptimization](https://docs.singlestore.com/cloud/query-data/query-tuning/feedback-reoptimization.md) (FR), uses statistics from previous query runs to improve query plans and performance. FR reoptimizes queries using statistics collected during query execution so the query optimizer can generate plans based on actual runtime data instead of estimates. Feature: **Multi-value Index for JSON** - The new [multi-value hash index](https://docs.singlestore.com/cloud/create-a-database/multi-value-hash-index-json.md) enables 100x faster analytics performance on JSON arrays. By indexing each value in arrays in JSON documents, the new index avoids scanning large JSON collections and improves query performance for `JSON_MATCH_ANY` queries. Feature: **Load Data Wizard** - The new wizard for loading data simplifies the data ingestion process and allows users to import data from AWS S3, local CSVs, MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and Snowflake without code. These wizards offer an intuitive, step-by-step interface that guides users through the loading process, minimizing complexity and reducing setup time. Feature: **Pipeline Monitoring** - The [Pipeline Dashboards](https://docs.singlestore.com/cloud/user-and-workspace-administration/workspace-health-and-performance/historical-monitoring/#section-idm4579546351372833664771243305.md) enhance user experience by providing real-time insights into data ingestion processes and resources consumed by pipelines. Users can easily access metrics related to pipeline performance, including data speed, row counts, and resource consumption to quickly diagnose issues and optimize their workflows. Feature: **Shared Edition Workspaces** - This feature provides low-cost development workspaces for non-production workloads. Providing core engine features, these shared workspaces offer a cost-effective alternative for developing new applications and allowing easy migration to production when ready. Feature: **AWS On-demand Billing** - Customers can now conveniently pay for SingleStore on-demand directly through AWS, simplifying the billing process and integrating seamlessly into their existing cloud frameworks. This feature provides customers unified cost management and flexible usage. Feature: **Job Scheduling with Cron**: This feature allows users to schedule notebook code as jobs using Linux’s cron syntax. Jobs can be scheduled at specific times and on specific schedules and provides reliable control over data workflows. Feature: **Notebook Versioning** - [This feature](https://docs.singlestore.com/cloud/container-services/notebooks/notebook-version-history.md) allows users to easily track and manage the evolution of their notebooks through a Git-like interface. This functionality mitigates the risk of data loss and removes the hassle of manual version management. Enhancement: **Database Branching on GCP** - Database branching offers Git-style agility for your data. You can branch, experiment, and roll back data changes in moments, not hours. You can also create an instant, independent, and updatable clone of your primary database to safely and easily develop prototypes, run performance tests, and conduct feature tests. Database Branching was already available on AWS and is now available on GCP. Enhancement: **Enhanced Query History** - The updated [Query History](https://docs.singlestore.com/cloud/query-data/query-tuning/query-history.md) feature provides users with a powerful tool for monitoring and optimizing SQL query performance. This feature enables high-frequency event tracing, allowing users to identify which queries consume substantial resources or fail during execution and then trace and debug those query executions. Enhanced metrics like CPU time, disk time, network time, various lock times, and more are now included. Preview Feature: **Flow on SingleStore Helios** - [Flow](https://docs.singlestore.com/cloud/load-data/load-data-with-singlestore-flow-on-helios.md) is a no-code data transfer and CDC tool tailored for ingesting data into SingleStore from databases including Oracle, SQL Server, MySQL, PostgreSQL, and Snowflake. Preview Feature: **Distributed Plancache (DPC)**  - The [Distributed Plancache](https://docs.singlestore.com/cloud/user-and-workspace-administration/workspace-health-and-performance/distributed-plancache.md) adds a third layer to the plancache, supplementing the existing in-memory plancache and on-disk persistent plancache (PPC). The DPC allows nodes to share plans. Nodes newly added to the cluster will start with a warm plancache instead of building one from scratch. The DPC improves query performance and reduces CPU usage after operations like scaling or rebalance. Plans compiled on one aggregator will be asynchronously synced to other aggregators so that plans do not need to be compiled separately on each aggregator. * Enabled synchronous Distributed Plancache lookup during query execution. * Distributed Plancache pool size adjustment fails gracefully. * Prevented the drop of Distributed Plancache files on regular Persistent Plancache GC. Preview Feature: **GPU-powered Notebooks** - This feature allows you to run complex data processing tasks in Python that take advantage of higher CPU and GPU compute for both interactive and job workloads. Preview Feature: **Cloud Functions** - This feature offers Lambda-style serverless functions. [Cloud Functions](https://docs.singlestore.com/cloud/container-services/cloud-functions.md) enable the deployment of serverless applications alongside database services, allowing developers to run custom code with the same performance and security as their database transactions. Cloud Functions simplifies application development by eliminating the overhead of traditional infrastructure management. Preview Feature: **Container App RBAC** - Aura Container Apps now support role-based access control (RBAC) for security and access management, which allows you to define access controls for deployed container apps. Preview Feature: **Continuous Backup and Online PITR on GCP** - [Continuous backups](https://docs.singlestore.com/cloud/manage-data/continuous-backups-and-restore.md) and online [Point-in-Time-Recovery (PITR)](https://docs.singlestore.com/cloud/manage-data/database-storage/online-point-in-time-recovery-pitr.md) allow you to back up and recover instantly, without disrupting live workloads. PITR provides self-service and instant recovery to any point in time within the retention period. Preview Feature: **Smart DR on GCP** - The [Smart DR](https://docs.singlestore.com/cloud/manage-data/smart-disaster-recovery-dr-smartdr.md) feature in Helios is an advanced disaster recovery (DR) solution that supports flexible and predictable cross-region disaster recovery for effortless failover and failback. Smart DR is also cost effective as no idle compute is required. Smart DR is Public Preview on AWS and is now Public Preview on GCP. Preview Feature: **Projects** - Projects allow organizations to deploy multiple product editions—such as Shared, Standard and Enterprise—simultaneously from a single account, across different cloud service providers and regions. This enables flexible, hierarchical management and centralized billing, allowing teams to seamlessly scale deployments across departments and workloads without the limitations of earlier edition choices. ## Changes in Default Behavior ## Collation Change The default collation for new (9.0+) clusters has been changed to `utf8mb4_bin` from `utf8mb4_general_ci` to improve default performance of string comparisons, which can substantially improve query speed. The `utf8mb4_bin` collation is not case sensitive and provides performance advantages when case sensitivity is not required. * For existing clusters that are upgraded to 9.0+, the collation is not changed. * For new (9.0+) clusters, the default collation will be `utf8mb4_bin`. * The default collation for the `utf8` and `utf8mb4` charsets has been changed. If a charset, e.g. `utf8mb4` is used in new (9.0+) clusters without an explicit collation, the default collation will be `utf8mb4_bin` instead of `utf8mb4_general_ci`. * Refer to [Specifying Character Set and Collation for Workspaces](https://docs.singlestore.com/cloud/reference/sql-reference/character-encoding/specifying-character-set-and-collation-for-workspaces.md) for additional information. ## Enhancements ## Full-text Search * Added full-text pushdown optimization that works with any filter in the `WHERE` clause. * Added a new metadata table to track full-text search indexes eligible for merging. * Added support for custom stopword lists for [full-text search version 2](https://docs.singlestore.com/cloud/developer-resources/functional-extensions/working-with-full-text-search.md). * Added new nori (Korean) analyzer customizations for full-text search version 2. ## Iceberg * Implemented support for automatically generated column mappings (for primitive data types and structures) as part of Iceberg schema inference. * Added support for EKS IRSA with assumeRole for Iceberg pipeline authorization. * Added support for file filtering based on the WHERE clause in the pipeline declaration. Refer to [Iceberg Ingest](https://docs.singlestore.com/cloud/load-data/data-sources/iceberg-ingest.md) for more information. ## JSON * Introduced [Multi-Value Hash Index](https://docs.singlestore.com/cloud/create-a-database/multi-value-hash-index-json.md) for JSON columns. This index optimizes key-path and array-value lookups in JSON columns. * Extended a rewrite to allow merging derived tables with JSON functions. * Added a new [JSON\_BUILD\_ARRAY](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-build-array.md) function. * Added support for JSON arrays in pipelines loading JSON files. Each JSON record in the array is loaded as a separate row. * Enabled the use of the `MATCH_ANY` tokenizer option with underscore in multi-value index settings. * Added charset validation for JSON type values in external functions. * Added support for the JSON data type in external functions. ## Observability * Improved performance of profiled query plans by introducing a new profile codegen mode. * Included aggregator activity statistics in Query\_completion event traces. * Added `activity_tracking_id` field in activity structure to track the resource consumption by pipelines. * Updated the default values of `enable_auto_profile` and `auto_profile_type` engine variable to `ON` and `SMART`, respectively. * Added the `SHOW BOTTOMLESS_PLANCACHE STATUS` command that returns observability metrics for the distributed plancache. * Added estimates for query memory usage based on static row count and row size estimates to improve Workload Manager (WM) memory management. * Added an internal allocator `Alloc_connection_context` which tracks certain per-connection allocations that were previously tracked using the standard allocator. * Added an option to run queries that failed asynchronous compilation, allowing users to introspect the compilation results. * Added logging for LRU compiled unit eviction. ## Pipelines * Added support for the `PIPELINE ` clause to the [SHOW PROFILE](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/profile.md) command which allows you to view profile information for the specified pipeline. * Added support for `SHOW PROFILE PIPELINE` on Helios. * Added the `CREATE_USER` and `ALTER_USER` fields in the `PIPELINES` information schema view. For more information, refer to [PIPELINES](https://docs.singlestore.com/cloud/reference/information-schema-reference/data-ingest/pipelines.md). * Added a `HEADER DETECTION` `ON`/`OFF` clause to control inference from CSV files. For more information, refer to [Schema and Pipeline Inference](https://docs.singlestore.com/cloud/load-data/about-singlestore-pipelines/pipeline-concepts/schema-and-pipeline-inference.md). * Added support for inferring Parquet files with the `INFER PIPELINE` command. For more information, refer to [Schema and Pipeline Inference](https://docs.singlestore.com/cloud/load-data/about-singlestore-pipelines/pipeline-concepts/schema-and-pipeline-inference.md). * Added support for inferring Iceberg schemas automatically on pipeline creation with the `INFER PIPELINE` command. For more information, refer to [Schema and Pipeline Inference](https://docs.singlestore.com/cloud/load-data/about-singlestore-pipelines/pipeline-concepts/schema-and-pipeline-inference.md). * Added `SHOW CREATE LINK` command that allows you to view the `CREATE` statement for the specified link. * Added support for `TIME(0)`, `TIME(6)`, `DATETIME(0)`, and `DATETIME(6)` types for the `CAST` command for use with computed columns and the `SET` clause for use with pipelines and `LOAD DATA`. * Added support to infer CSV files with a single column when the file contains no field terminators in any record. * Implemented exponential backoff sleep between batch retries. * Added support for metadata garbage collection for Azure pipelines. * Enhanced [Pipeline Dashboards](https://docs.singlestore.com/cloud/user-and-workspace-administration/workspace-health-and-performance/historical-monitoring/#section-idm4579546351372833664771243305.md) to include items such as speed of execution, number of batches processed, data transferred, and lag over time. ## Query Execution * Added Join Memory Reduction Optimization. This optimization is a new query optimization feature designed to help prevent memory overflow (spilling) during hash joins on large datasets. When enabled, it automatically rewrites qualifying joins into smaller, manageable pieces—reducing memory pressure and the risk of out-of-memory (OOM) errors. This feature is especially useful when joining large tables on high-cardinality columns, where traditional hash joins could exceed memory limits. Added engine variables `cardinality_threshold_for_join_split_rewrite` and `target_group_size_for_join_split_rewrite` to control the behavior of this optimization. Refer to [Join Memory Reduction Optimization](https://docs.singlestore.com/cloud/query-data/query-tuning/join-memory-reduction-optimization.md) for details.  * Added segment metadata that allows the engine to determine if columns are fully enclosed in (or pass) a filter and allows the engine to do segment elimination on queries with an `IS NULL` filter. * Added the `CHARACTER SET ` and `COLLATE ` clauses to the `ALTER TABLE` command that allows converting the character set and collation of a table, respectively. * Added support for parameterized `IN`-lists in BSON multi-value index optimization enabling improved performance for queries with same-type constant literals and single-argument built-in expressions. Previously limited to hex literals. * Disabled the pushdown of `INSERT SELECT` queries to leaf nodes for shard key affecting columns. * Enabled expression pushdown from join into hash builds on materialized CTEs. * Enabled the rewriting of correlated subselects that depend on more than one outer table with correlation conditions other than `=` and `AND`. * Extended predicate pushdowns to identify more cases. * Extended rewrite for `EXISTS` subselects to handle arbitrarily nested `EXISTS` subselects under `AND` or `OR`. * Added the ability to ignore join selectivity estimates of zero. ## Security * Enabled mutual TLS authentication between the client and server. * Added support for manual password activation and expiration to the `ALTER USER` command. * Added the `password_expiration_mode` engine variable. When set to `LIMITED_ACCESS`, users are allowed to log in even after password expiration and run password update commands, such as `ALTER USER` or `SET PASSWORD`. * Internal roles and groups cannot be deleted anymore. * Passwords are now masked in extractor subprocess logs. * A warning is now raised for every query if the user's password is expiring soon. ## SQL Editor * Autocomplete provides real-time, context-aware query completion as the SQL query is written. This functionality helps users quickly identify relevant tables, views, and columns, and also helps reduce syntax errors. * Multi-tab provides multiple tabs in the SQL Editor so you can better organize your SQL scripts and queries. ## Vector Search * Added the `VECTOR_INDEX` information schema view which shows the amount of memory and disk used by each vector index. * Added a fallback mechanism so that when the engine detects that an indexed vector search followed by filters yields fewer than `LIMIT` rows, the engine falls back to a full table scan to ensure sufficient rows are produced. The fallback mechanism can be disabled by setting the engine variable `vector_index_fallback_non_index_scan` to FALSE. * Extended the `VECTOR_SUM` aggregate function to support the `VECTOR` data type. * Added a vector index cache to limit the amount of memory used by vector indexes. Refer to [Vector Indexing](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/vector-indexing.md) for details. ## General Updates * Added events to indicate the start and end of asynchronous upgrades. * Added the `ENSURE_ONLINE_NODES` clause to the `REBALANCE ALL DATABASES` and `REBALANCE PARTITIONS` commands which allows rebalancing to run only if all the leaf nodes are online. * Allowed background merger to run while `OPTIMIZE TABLE` is running. * Connected the distributed plancache to the plancache manager. * Disabled unnecessary collection of block statistics. * All distributed plancache operations are now asynchronous. * Added support for reusing autostats file on recovery. * Added support for spilling if input data has NULL rows. * Aggregators now periodically synchronize query plans across the deployment. * `DROP RESOURCE POOL` command cannot delete the current resource pool anymore. * The engine now waits until asynchronous upgrade steps complete before taking a snapshot. * Added support for NULL type in Wasm UDF return types and parameters. * Added support for expressions, builtin functions, and user-defined variables in addition to hex literals in Multi-Value index for BSON. Refer to [Multi-Value Hash Index (BSON)](https://docs.singlestore.com/cloud/reference/sql-reference/bson-functions/multi-value-hash-index-bson.md) for more information. * Extended redundant `GROUP BY` detection to outer join predicates. * Users with database visibility can now query the `*_BOTTOMLESS_*` information schema views in the respective database. `CLUSTER` permissions are no longer enforced for querying these views. * Columns with extended types now use the correct default table collation. * Enabled correlating fields in subselects to reference outer selects more than one level up. * Enabled skipping of non-alterable tables from asynchronous DDL queries. * Updated to use a consistent snapshot while performing a shallow table copy. * Disabled segment elimination for `IN` clauses for which the left-hand expression is not a table column. * Improved the error message returned when invalid key is specified in the CONFIG/CREDENTIALS JSON. * Improved the error message for parameter count mismatch during query parsing. * Users with `SHOW METADATA` privilege can now access the `LOAD_DATA_ERRORS` information schema view. * Added support for delimited batch sets in external functions. * Added support for the `LIMIT` clause in prepared statements. * Computed column definitions now support the `SPLIT` function. * The `DROP` and `ALTER TABLE` commands no longer have to wait for the plan garbage collector. * Improved the garbage collection behavior for plancache. * Introduced support for placeholders for partition ID and timestamp in the `SELECT INTO ... filename` command. * Added Lucene logs to the cluster report. * Added statistics related to AWS S3 lookup latency. * Added knob to disable cardinality estimates on JSON/BSON columns during analyze. ## Engine Variables This update adds the following new engine variables: * `disk_plan_gc_pause_seconds_on_startup`: Disables disk plan garbage collection on startup. This variable prevents hot plans from being unintentionally disk garbage collected. * `disk_plan_gc_pause_minutes_for_detached_tables`: Specifies the number of seconds to cache detached table modules before eviction from disk. * `enable_gc_events`: Enables reporting of garbage collection events. Gates the `MV_GC_EVENTS` information schema view that provides visibility into garbage collection passes. * `info_schema_show_null_dbs`: Allows non-privileged users to view the rows in `*_BOTTOMLESS_*` information schema views where associated database name is NULL. * `managed_functions_batch_size`: Controls the batch size for managed external functions including Python UDFs and AI/ML Functions. * `optimizer_disable_analyze_cardinality_on_json`: Controls if the cardinality of JSON columns is collected by autostats in `PERIODIC` mode. * `optimizer_max_table_row_count_as_default_cardinality`: Controls the maximum estimated table row count to be used as the per column cardinality when no column-level statistics exists. * `password_expiration_mode`: When set to `LIMITED_ACCESS`, users are allowed to log in even after password expiration and run password update commands, such as `ALTER USER` or `SET PASSWORD`. * `regexp_error_handling_level`: Controls the behavior of error handling for regexp operations. * `scheduler_slow_loop_seconds`: Specifies the threshold for triggering the verbose logging of scheduler thread timing. * `ssl_ca_for_client_cert`: Support for client certification. * `suppress_dml_warnings`: Suppresses all DML query warnings. * `vector_index_fallback_non_index_scan`: Controls a fallback mechanism so that when the engine detects that an indexed vector search followed by filters yields fewer than `LIMIT` rows, the engine falls back to a full table scan to ensure sufficient rows are produced.  * [Distributed Plan Cache](https://docs.singlestore.com/cloud/user-and-workspace-administration/workspace-health-and-performance/distributed-plancache.md): Added the following engine variables to control this feature: `enable_distributed_plancache`, `distributed_plancache_worker_threads`, `distributed_plancache_max_download_plans`, `distributed_plancache_agg_sync_s`, `enable_periodic_distributed_plancache_agg_sync`, `enable_synchronous_dpc_lookup`. * [Join Memory Reduction Optimization](https://docs.singlestore.com/cloud/query-data/query-tuning/join-memory-reduction-optimization.md) : Added `cardinality_threshold_for_join_split_rewrite` and `target_group_size_for_join_split_rewrite` to control the behavior of this optimization. ## Information Schema This update adds the following information schema views and columns: * `MV_ACTIVE_METADATA_VERSIONS`. Provides visibility into active metadata transaction read versions. * `MV_COLUMNSTORE_ACTIVE_MERGE_PLANS` and `LMV_COLUMNSTORE_ACTIVE_MERGE_PLANS`. Show internal details on currently running merge plans. * `MV_GC_EVENTS`. Provides visibility into garbage collection passes. This view is gated behind the preview feature and engine variable `enable_gc_events`. * `MV_TABLE_COUNTERS` and `LMV_TABLE_COUNTERS`. Show cumulative per-table metrics, i.e., the amount of work performed by the background merger. * `VECTOR_INDEX`. Shows the amount of memory and cache used by each vector index. * Added the `UPLOAD_BLOB_LEAD_PAGES` column to `information_schema.MV_BOTTOMLESS_SUMMARY`. Ensures that uploading log chunks to the remote storage does not keep falling behind. * Added more metrics to `information_schema.MV_CLOUD_PER_COMPUTE_REMOTE_STATS` and `information_schema.MV_CLOUD_PER_STORAGE_REMOTE_STATS`. Tracks retention log chunks and snapshots. * Added the `ATTACH DATABASE`, `DETACH DATABASE`, `CREATE DATABASE`, and `DROP DATABASE` columns to `information_schema.MV_EVENTS`. * Added the `CREATE_USER` and `ALTER_USER` fields to `information_schema.PIPELINES`. * `source_type` is now included as part of the `config_json` in `information_schema.pipelines`. * *Bug Fixes* * Fixed a case where long running background work was blocking in-memory garbage collection of database metadata. * Fixed a memory allocation bug during query compilation. * Limited the number of retries on plan compilation errors; this fix limits resource usage. * Fixed a deadlock in result table creation during reshuffle with the always-false filter. * Fixed a crash when a `BM25` function in a subselect referenced a table in the outer select. * Fixed a crash during replay when a table uses an FTS index with JSON options. * Fixed a potential replay failure on a table that uses full-text search version 2. * Fixed how `JSON_PRETTY` builtin function escapes characters. * Fixed a bug that caused `JSON_TO_ARRAY` join queries to return wrong results where the quotes on strings were removed incorrectly. * Fixed using delimited batch sets with JSON format. * Fixed performance regression for queries involving JSON string extraction (e.g. `JSON_EXTRACT_STRING` and `JSON_ARRAY_CONTAINS_STRING`) in situations where there are a large number of JSON values. * Fixed incorrect aggregation of leaf query stats in Query\_completion tracing. * Fixed an issue that caused an error if column names had trailing spaces in `CREATE PIPELINE` statements. * Fixed an issue where monitoring pipelines would sometimes fail to produce data for large source clusters. * Fixed a wrong result in `ORDER BY LIMIT` query that skipped rows with NULL values in the sort key. * Added support for the `IF NOT EXISTS` clause to the `CREATE VIEW` command. * Added support for table-level collations. * Improved optimization speed of parameterized `IN`-lists by limiting traversal depth. * `OPTIMIZE TABLE ... INDEX` is now more responsive to `KILL QUERY` statements. * Enabled using the estimated table row count as default column cardinality for better join estimation. * Added support for PSQL ARRAY type arguments to the `TO_JSON` built-in function. * Removed the `CAST` operator if a redundant cast is performed. * Improved error handling for regular expressions: errors are now properly raised instead of returning SQL NULL. To avoid breaking existing apps the `regexp_error_handling_level` engine variable was added. This variable controls the behavior of error handling for regexp operations. * Fixed an issue where concurrent DDL and `PROMOTE AGGREGATOR TO MASTER` statements were not forwarded to the Master Aggregator. * Fixed the projection DDL output in the debug profile. * Fixed an error named `ER_BAD_TABLE_ERROR` on `UPDATE` or `DELETE` queries with a specific shape. * Fixed an issue in CTE query rewrites caused when a CTE referenced a recursive CTE. * Fixed the predicate pushdown logic for materialized common table expressions. * Fixed query performance issue caused by heuristic estimate selectivity being overwritten. * Fixed row count estimation for union subquery containing Materialized Common Table Expression (MCTE)s. * Resolved an issue where the regular expression engine would return SQL NULL when hitting the memory limit during execution. It now raises an appropriate error instead. * Fixed resource pool and workload management CPU limiting. * Fixed a locking bug related to table module eviction during recovery. * Fixed a deadlock in a rare scenario related to low memory conditions. * Fixed a rare race condition which caused a node to get stuck in offline mode after recovery. * Fixed an issue that led to undefined behavior if a node failed concurrently with the computation of the replication distribution tree. * Fixed a race condition that caused autostats file leak while reprovisioning. * Fixed incorrect autostats in a rare failover case during asynchronous replication. * Fixed a race condition in autostats between table refresh and table deletion. * Fixed a race condition that could cause shutdown to hang. * Fixed a rare crash in synchronized variables initialization. * Fixed a bug in the deadlock avoidance algorithm that could lead to deadlocks. * Fixed a bug that caused nodes to crash when `ALTER TABLE` queries ignored foreign keys. * Fixed a rare deadlock related to temporary tables. * Fixed a crash in `SHOW PROCESSLIST` in a rare race condition scenario. * Fixed a bug in CTE rewrites. * Fixed a leak in a rare scenario. * Fixed a bug in parser. * Fixed a crash in a shallow copy of a table with a pending ALTER operation. * Fixed an issue where IN-list factorization did not work with newline characters around the IN-list. * Fixed an issue where periodic autostats was disabled when it should not have been. * Fixed a crash in lockfree hashtable when the engine is under high memory pressure. Reports an "out of memory" error instead. * Fixed a bug in lateral join. * Updated third party libraries to fix security vulnerabilities. * Increased the \_bt timeout from 60 seconds to 180 seconds. * Implemented shallow copy of a table with a dropped index, the shallow copy now includes the in-memory segment. * Fixed incorrect aggregation of leaf query stats in Query\_completion tracing. * Fixed an issue where certain commands (e.g. DROP TABLE) could hang due to aborted queries caused by stuck collocated services triggered by external functions. * Fixed a bug in the detection of shard key matches in the window function partition by list. * Fixed an error in backup subprocess and enhanced subprocess tracing to use structured logging. ## September 2025 ## Highlights * **Enhancement**: Added the ability to ignore join selectivity estimates of zero. * **Enhancement**: Enabled using the estimated table row count as default column cardinality for better join estimation. Added session variable `optimizer_max_table_row_count_as_default_cardinality` which controls the maximum estimated table row count to be used as the per column cardinality. * *Bugfixes* - **Bugfix**: Fixed a case where long running background work was blocking in-memory garbage collection of database metadata. - **Bugfix**: Fixed a memory allocation bug during query compilation. - **Bugfix**: Fixed a bug where large queries can crash the engine in `compile_lite` and `compile_lite_first` interpreter modes. - **Bugfix**: Fixed stack overflows in `compile_lite` and `compile_lite_first` interpreter mode. - **Bugfix**: Fixed a bug with some `NOT IN` subselect query shapes. - **Bugfix**: Limited the number of retries on plan compilation errors; this fix limits resource usage. - **Bugfix**: Fixed a deadlock in result table creation during reshuffle with the always-false filter.* **Bugfix**: Fixed an issue with Java 11 path (`java_pipelines_java11_path` engine variable) configuration while deploying SingleStore. * **Bugfix**: Fixed a bug in the detection of shard key matches in the window function partition by list. * **Bugfix**: Fixed using delimited batch sets with JSON format. * **Bugfix**: Fixed an issue where the `ALTER TABLE` command could hang and become unkillable due to a type conversion error occurring during the alter operation. ## August 2025 ## Highlights * **Enhancement**: Removed the `CAST` operator if a redundant cast is performed. * **Enhancement**: Changed the approach for delimited batch sets. Delimited batch sets now return and accept original rowIDs. * **Enhancement**: Added a `suppress_dml_warnings` global engine variable to suppress all DML query warnings. * **Enhancement**: Added `ATTACH DATABASE`, `DETACH DATABASE`, `CREATE DATABASE`, and `DROP DATABASE` to `information_schema.MV_EVENTS`. * **Enhancement**: Added support for metadata garbage collection for Azure pipelines. * **Enhancement**: Added knob to disable cardinality estimates on JSON/BSON columns during analyze. * **Enhancement**: Added the `managed_functions_max_connections` engine variable which specifies the maximum number of HTTP connections per node for managed external functions, such as Python User Defined Function (UDF)s. * *Bugfixes* * **Bugfix**: Fixed an error in backup subprocess and enhanced subprocess tracing to use structured logging. * **Bugfix**: Fixed a crash when BM25 in a subselect references a table from the outer select. * **Bugfix**: Fixed an issue where certain commands (e.g. `DROP TABLE`) could hang due to aborted queries caused by stuck Collocated Services triggered by External Functions. * **Bugfix**: Fixed performance regression for queries involving JSON (like `JSON_EXTRACT_` builtins). * **Bugfix**: Fixed query performance issue caused by heuristic estimate selectivity being overwritten. * **Bugfix**: Updated third party libraries to fix security vulnerabilities. * **Bugfix**: Fixed an issue where monitoring pipelines would sometimes fail to produce data for large source clusters. * **Bugfix**: Fixed row count estimation for union subquery containing Materialized Common Table Expression(MCTE)s. ## July 2025 ## Highlights * **Enhancement**: Added support for fully enclosed optimization on nullable columns and segment elimination on `IS NULL` filter. * **Enhancement**: Updated the bundled OpenSSL version to 3.1.2. * *Bugfixes* * **Bugfix**: Fixed results for `NOT IN` subselects when the left input table has `NULL` values. * **Bugfix**: Fixed incorrect results in an `ORDER BY LIMIT` query, caused by an issue where rows with null values in the sort key may be skipped. * **Bugfix**: Bumped the \_bt timeout from 60 seconds to 180 seconds. * **Bugfix**: Fixed logic for `NOT IN` subselects when nested under `OR`s. * **Bugfix**: Fix correctness when using `NOT IN` for some query shapes. * **Bugfix**: Fixed incorrect aggregation of leaf query stats in Query\_completion tracing. ## June 2025 ## Highlights * **Enhancement**: Added Lucene logs to the cluster report. * **Enhancement**: Users with database visibility can now query the `*_BOTTOMLESS_*` information schema views in the respective database. `CLUSTER` permissions are no longer enforced for querying these views. * **Enhancement**: Improved the error message for parameter count mismatch during query parsing. * **Enhancement**: Disabled segment elimination for `IN` clauses for which the left-hand expression is not a table column. * *Bugfixes* * **Bugfix**: Fixed a bug in lateral join. * **Bugfix**: Fixed the predicate pushdown logic for materialized common table expressions. * **Bugfix**: Fixed an issue where periodic autostats was disabled when it should not have been. * **Bugfix:** Fixed crash in lockfree hashtable when engine is under high memory pressure. Report out of memory error instead. * **Bugfix**: Fixed a leak in a rare scenario. * **Bugfix**: Fixed the `ER_BAD_TABLE_ERROR` error on `UPDATE` or `DELETE` queries with a specific shape. * **Bugfix**: Fixed a crash in a shallow copy of a table with a pending `ALTER` operation. * **Bugfix**: Fixed a bug in CTE query rewrites. * **Bugfix**: Fixed a potential replay failure on a table that uses full-text version 2. * **Bugfix**: Fixed an issue where `IN`-list factorization did not work with newline characters around the `IN`-list. ## May 2025 ## Highlights * **Enhancement**: Extended a rewrite to allow merging derived tables with json functions. * **Enhancement**: Changed the default blob cache size to 75% of the total disk for small disk config. * **Enhancement**: Enabled rewriting correlated subselects that depend on more than one outer table with conditions other than equality. * **Enhancement**: Prevented the destination table from being replaced by projection on update or delete. * **Enhancement**: Renamed GIN index to Multi-Value Hash Index. GIN keyword deprecated. * **Enhancement**: Enable the use of the `MATCH_ANY` tokenizer option with underscores in GIN index settings. * **Enhancement**: Added support for parameterized  `IN` -lists in BSON GIN index optimization enabling improved performance for queries with same-type constant literals and one-argument built-in expressions. Previously limited to hex literals. * **Enhancement** :New variables (Note, these should only be set with guidance from SingleStore. * `repl_page_desc_limit_before_stall`: limits the number of page descriptors in the page map that a 'ReplLog' can use before stalling new txs. See 'ReplLog::StallForPageMap'. The default 256K page desciptor limit means the hash table will not have load greater than 4. * `repl_io_buffer_limit`: limits the number of IO buffers that a 'ReplLog' can use before stalling new txs. See 'ReplLog::StallForPageMap'. 128 IO buffers is 16MB since each IO buffer is 128K. * `[dr_]repl_network_buffer_size`: replication tries to set both send and receive buffer sizes to reduce waits on TCP ack packets. 8MB is a sane battle-tested value. Can be set for both DR and non-DR replicas. * `[dr_]repl_max_chunk_size`: when sending logs or snapshots to a replica limits chunk size to this value. Too long of a chunk increases the chance of timeout waiting for the replica's response packet (since the replica must process the entire chunk before acking). This defaults to 8MB - a sane limit chunk size to this value. Maximum value is 1GB and it must be divisible by 128kb so it is a multiple of io buffer size. Can be set for both dr and non-DR replicas. * **Enhancement**: Added more metrics to the views,  `MV_CLOUD_PER_COMPUTE_REMOTE_STATS`  and  `MV_CLOUD_REMOTE_STATS` , for tracking of retention log chunks and snapshots. * **Enhancement**: Added support for stopwords `token_filter` for custom analyzers for FTS VERSION 2. * *Bugfixes* - **Bugfix**: Fixed a bug that caused filter-aware optimizations to not be applied above MCTE when involved in hash joins. - **Bugfix**: Fixed a bug that occurred when CTEs are rewritten. - **BugFix**: Fixed resource pool and workload management CPU limiting. - **BugFix**: Fixed an isue to use full table scan instead of vector index scan if enough rows are not produced. - **BugFix**: Fixed a wrong results bug in  `JSON_TO_ARRAY ()` join queries where the quotes on strings were incorrectly removed. - **BugFix**: Fixed a rare deadlock involving temp tables.* **BugFix**: Fixed invalid optree from rewrite on union queries matching a very specific shape. ## April 2025 ## Highlights * **Enhancement**: Added support to the GIN Index for BSON for expressions, built-in functions, user-defined variables (UDVs), and typecasting. User-defined functions (UDFs) and non-deterministic built-in functions, such as rand(), remain unsupported. * **Enhancement**: Added estimates for query memory usage based on static row count and row size estimates to improve Workload Manager (WM) memory management. * **Enhancement**: Added a `KILL` option for the `DETACH DATABASE` command. * **Enhancement**: General improvements and optimizations for `CHECK BOTTOMLESS CHECKSUM` command. * **Enhancement**: Added support for JSON arrays in pipelines loading JSON files. Each JSON record in the array is loaded as a separate row. * **Enhancement**: Improved optimization speed of parameterized `IN`-lists by limiting traversal depth. * **Enhancement**: Added support for delimited batch sets in external functions. * **Enhancement**: High Availability for the Master Aggregator (HA for MA) is now enabled exclusively for Enterprise edition. * **Enhancement**: `OPTIMIZE TABLE ... INDEX` is now more responsive to `KILL QUERY` statements. * *Bugfixes* - **Bugfix**: Fixed an issue where a node that had not been upgraded could trigger undefined behavior if the node's `processlist` was queried while the node was processing an internal RPC. - **Bugfix**: Fixed a `NULL` value inline bug by adding typecast operations above the referencing field. - **Bugfix**: Fixed an issue that could lead to undefined behavior when a node failed concurrently with the computation of the replication distribution tree. - **Bugfix**: Fixed a bug that caused `DROP DATABASE` to hang in a rare condition. - **Bugfix**: Fixed an issue that returned wrong results in pipelines in a rare race condition. - **Bugfix**: Fixed an issue that caused a crash when a query referenced too many tables. - **Bugfix**: Changed options for loading libpam.so. - **Bugfix**: Fixed a race condition that caused `DETACH DATABASE` to hang. - **Bugfix**: Fixed a buffer overflow issue that occurred while de-parameterizing a multi-column `IN`-list filter. - **Bugfix**: Fixed an issue to prevent LRU eviction from stalling when several table modules are stale. - **Bugfix**: Fixed a bug in `JSON_TO_ARRAY()` join pushdown optimization that occurred when the JSON value being extracted from `table_col` is a JSON array.* **Bugfix**: Fixed `ALTER RESOURCE POOL` to respect queue depth constraints. * **Bugfix**: Fixed a bug that produced incorrect results when performing a hash join with a condition that compares mismatched types where one of the types is BSON. * **Bugfix**: Fixed a crash caused by libgcc btree bug. * **Bugfix**: Reduced contention on a global lock controlling modules, affecting code loading and unwinding of exceptions. * **Bugfix**: Removed garbage collection from cleaning up arrangements when such garbage collection is already in process. * **Bugfix**: Disabled vector range search filter during `JOIN`s to avoid an engine crash. ## March 2025 ## Highlights * **Enhancement**: Added an engine variable `optimizer_disable_subselect_to_join_cte_preprocess` which can be used to disable the subselect to join rewrite for CTE preprocessing before inlining or materialization. * **Enhancement**: Added an engine variable `optimizer_enable_merge_unioned_queries_rewrite` which can be used to enable or disable the rewrite that merges union queries. This variable is `ON` by default. * **Enhancement**: Added aggregator activity stats in `Query_completion` event tracing. Refer to [Query History](https://docs.singlestore.com/cloud/query-data/query-tuning/query-history.md) for details. * **Enhancement**: Added the ability for a user to change the login password when the variable `password_expiration_mode` is set to LIMITED\_ACCESS. Now the user is allowed to login even after the password expires but can only execute commands that update the password such as `ALTER USER` or `SET PASSWORD`. * **Enhancement**: Added a password expiry warning message that is raised each time the user executes a query. This warning starts appearing 14 days before the password actually expires. * **Enhancement**: Added auditlogging mode for only the root user. It is a startup-only variable similar to other audit logging configuration variables and should be set along with the `auditlog_level`. * **Enhancement**: Added support for the `LIMIT` clause in prepared statements. * **Enhancement**: Added an option to run queries that failed asynchronous compilation, allowing users to introspect the compilation results. * *Bugfixes* - **Bugfix**: Fixed an issue impacting creating or altering a resource pool with a CPU percentage limitation after a MA failover.- **Bugfix**: Fixed CTE subselect to join preprocessing in the `INTERSECT`/`EXCEPT` CTE case. - **Bugfix**: Reserved memory space for `segment_id` in the pseudo column's MemoryFile. - **Bugfix**: Fixed a bug that occurred when a query used `I0` as a column name. - **Bugfix**: Fixed a crash that was due to an issue with the rewrite that merges union queries.* **Bugfix**: Fixed a bug that caused incorrect results when running `INFER PIPELINE CSV`. * **Bugfix**: Fixed a rare deadlock occurring when DETACH DATABASE is run. * **Bugfix**: Fixed a bug that caused a node to crash when the variable `ignore_foreign_keys` is ON and the `ALTER TABLE` command is used to add a foreign key. * **Bugfix**: Changed the output projection DDL to `CREATE PROJECTION` instead of `CREATE TABLE _$_table_name_$` in debug profile. * **Bugfix**: Fixed an issue where DDL statements that were concurrent with PROMOTE AGGREGATOR TO MASTER did not get forwarded to the new Master Aggregator. * **Bugfix**: Fixed an issue to prevent a crash when `JSON_TO_ARRAY` optimization is run against non-nullable JSON columns. * **Bugfix**: Fixed a bug to prevent a crash when using FTS v2 with CTE. ## February 2025 * **Enhancement**: Added an internal allocator `Alloc_connection_context` which tracks certain per-connection allocations that were previously tracked using the standard allocator. * **Enhancement**: Added a vector index cache to limit the amount of memory used by vector indexes. Refer to [Vector Indexing](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/vector-indexing.md) for details. * **Enhancement**: Extended the [VECTOR\_SUM](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/vector-sum.md) aggregate function to support the `VECTOR` data type. * **Enhancement**: Computed column definitions now support the `SPLIT` function. * **Enhancement**: The `DROP` and `ALTER TABLE` commands no longer have to wait for the plan garbage collector. * *New or Modified Engine Variables* Refer to [List of Engine Variables](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables.md) for information on each of the following engine variables.- **Enhancement**: Added an engine variable `disk_plan_gc_pause_seconds_on_startup` that disables disk plan garbage collection on startup. This variable prevents hot plans from being unintentionally disk garbage collected. * *Bugfixes* * **Bugfix**: Fixed the `userDictionary` parameter for the korean full-text tokenizer. * **Bugfix**: Fixed a crash that occurred when aggregator functions are used with `VECTOR` type data inside other built-ins. * **Bugfix**: Fixed a crash occurring when a table-valued function (TVF) column is used in a `WHERE` clause without a wrapping `JSON_EXTRACT` function in a `TABLE(JSON_TO_ARRAY())` join. * **Bugfix**: Fixed a bug that caused CDC-in pipelines to fail while inferring the table schema, with the "Failed to allocate slot in the extractor pool" error. * **Bugfix**: Fixed a deadlock between `ALTER` and failover in a rare race condition. * **Bugfix**: Fixed a critical issue where clusters were failing to connect to AWS remote storage due to CURL request timeouts. * **Bugfix**: Fixed a bug where a CDC pipeline gets stuck while waiting for data. * **Bugfix**: Fixed a bug that prevented proper error handling on socket timeout. * **Bugfix**: Fixed a bug in `JSON_ARRAY_CONTAINS_STRING` query rewrite for query shapes with the `LIMIT` clause. * **Bugfix**: Fixed support for plan pinning in `IN` list factorization. ## January 2025 ## Highlights ## Full-Text Search - Analyzers and Tokenizers Full-text search using SingleStore's VERSION 2 full-text index has been enhanced with support for custom analyzers and tokenizers. With this enhancement, SingleStore full-text indexes can be created to support languages other than English, for text that contains emails and URLs, with custom whitespace processing, and more. The n\_gram tokenizer, which breaks words into [n-grams](https://en.wikipedia.org/wiki/N-gram), sequences of n adjacent symbols is included. The full set of Apache Lucene analyzers and tokenizers is supported. Refer to [Full Text VERSION 2 Custom Analyzers](https://docs.singlestore.com/cloud/developer-resources/functional-extensions/full-text-version-2-custom-analyzers.md) for more information. ## Full-Text Search - Enhanced BM25 Scoring Full-text search using SingleStore's VERSION 2 full-text index has updated BM25 scoring functionality.  The `BM25` function has been enhanced with support for boolean and boost queries, phrase and proximity search queries, and queries over multiple columns. Refer to [BM25](https://docs.singlestore.com/cloud/reference/sql-reference/full-text-search-functions/bm-25.md) for more information and examples. A new function, `BM25_GLOBAL`, has been added to provide BM25 scoring across all partitions. With this new function, all rows in a table are scored together; collection and term statistics are calculated for a table, ensuring accurate scores relative to all rows in a table. The `BM25_GLOBAL` function augments the existing `BM25` and `MATCH` functions, and it is more accurate and more expensive than both of these functions. Refer to [BM25](https://docs.singlestore.com/cloud/reference/sql-reference/full-text-search-functions/bm-25.md) for more information on the `BM25_GLOBAL` function. ## Iceberg Continuous Ingest Added support for continuous ingest of data from Iceberg tables. Upsert and append-only workloads are supported. In addition, manual upserts with the `CREATE OR REPLACE` command are supported. Refer to [Iceberg Ingest](https://docs.singlestore.com/cloud/load-data/data-sources/iceberg-ingest.md) for more information. ## Iceberg - New Catalogs Added support for Snowflake, REST, JDBC, Hive, and Polaris Catalogs for Iceberg Ingest using pipelines. Refer to [Iceberg Ingest](https://docs.singlestore.com/cloud/load-data/data-sources/iceberg-ingest.md) for more information. ## Enhanced Disk Spilling Added disk spilling for `RIGHT` and `FULL OUTER JOIN`. ## Writable Views Writable views allow users to run `UPDATE`, `INSERT`, and `DELETE` queries on views. To enable writable views, set the `enable_writable_views` global variable to `1`. Query the `information_schema.VIEWS` view to inspect if a view can be updated. Refer to [CREATE VIEW](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-view/#section-idm234617498191001.md) for more information. ## Other Improvements and Fixes ## Vector Index on Nullable Column Vector Indexes can be created on columns that are nullable. Prior to this improvement, vector indexes could only be created on columns that were declared `NOT NULL`. With this improvement, a user can insert a row containing text and a `NULL` vector value into a table with a vector index. The user can subsequently obtain a vector embedding for the text and update the row with that vector value. The updated value will be added to the vector index. ## Vector Index Memory Tracking The memory used by vector indexes can be tracked using the `alloc_vector_index` metric which is now available in [SHOW STATUS EXTENDED](https://docs.singlestore.com/cloud/reference/sql-reference/show-commands/show-status-extended.md). Refer to [Vector Indexing](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/vector-indexing.md) and [Tuning Vector Indexes and Queries](https://docs.singlestore.com/cloud/developer-resources/functional-extensions/tuning-vector-indexes-and-queries.md) for more information. ## Vector Index Merger Added the vector index merger which combines per-segment vector indexes to create a cross-segment vector index and improve the performance of vector search queries. Refer to [Vector Indexing](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/vector-indexing.md) for more information. * *Other Performance Enhancements* * **Enhancement**: Added sub-segment elimination for flexible parallelism. Refer to [Flexible Parallelism](https://docs.singlestore.com/cloud/query-data/advanced-query-topics/flexible-parallelism.md) for more information. * **Enhancement**: Improved the performance of `LOAD DATA` queries that include the `CHARACTER SET` clause. * **Enhancement**: Optimized full-text queries with `ORDER BY ... LIMIT` on a full-text score and that optionally filter on the same full-text clause. * **Enhancement**: Significantly improved the performance (\~20x) of certain JSON-based SQL queries and queries run using the Kai endpoint, when JSON objects contain arrays of sub-objects. This optimization reduces the need to normalize the data into multiple tables to achieve high analytics performance. Queries that expand JSON arrays (with or without any aggregations) and/or perform the following operations benefit from this optimization: * Group by a field outside the array (in the `GROUP BY` clause) * Filter on the fields in the array * *New Information Schema Views and Columns* * **Enhancement**: Added the `TABLE_NAME` column to the `LOAD_DATA_ERRORS` information schema view. `TABLE_NAME` is the name of the table associated with the error. * **Enhancement**: Added the `NODE_ID` column to the `MV_RECOVERY_STATUS` information schema view that specifies the ID of the node from which the database is being recovered. * **Enhancement**: Added the `MV_BOTTOMLESS_API_EVENTS_SUMMARY` information schema view that contains a summary of remote API calls made from the engine. Refer to [MV\_BOTTOMLESS\_API\_EVENTS\_SUMMARY](https://docs.singlestore.com/cloud/reference/information-schema-reference/unlimited-storage/mv-bottomless-api-events-summary.md) for more information. (8.7.20) * **Enhancement**: Added the following columns (metrics) to the `MV_BOTTOMLESS_STATUS_EXTENDED` and `MV_BOTTOMLESS_SUMMARY` information schema views: * `IS_BOTTLE_SERVICE_UP`: Indicates whether the bottle service is up. * `BOTTLE_SERVICE_UPTIME_DOWNTIME_SECS`: Specifies the minimum uptime when the bottle service is up and the maximum downtime when the bottle service is down. * *New Commands and Functions* * **New feature**: Added the following [Identifier Generation Functions](https://docs.singlestore.com/cloud/reference/sql-reference/identifier-generation-functions.md): * `UUID_TO_BIN` * `BIN_TO_UUID` * `IS_UUID` * **New feature**: Added a `SHOW FULLTEXT SERVICE METRICS` command that displays the diagnostic metrics for the JLucene full-text search in JSON format. Refer to [SHOW FULLTEXT SERVICE METRICS](https://docs.singlestore.com/cloud/reference/sql-reference/show-commands/show-fulltext-service-metrics.md) for more information. * **New feature**: Added a `SHOW CDC EXTRACTOR POOL` command that displays information about the CDC-in pipelines. Refer to [SHOW CDC EXTRACTOR POOL](https://docs.singlestore.com/cloud/reference/sql-reference/show-commands/show-cdc-extractor-pool.md) for more information. (8.7.20) * **New feature**: Added a new `JSON_MERGE_PATCH` function that merges two JSON objects into a single JSON object. Refer to [JSON\_MERGE\_PATCH](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-merge-patch.md) for more information. * **New feature**: Added support for Lateral Join. Lateral join allows a subquery in the `FROM` clause of a SQL query to reference another table in that same `FROM` clause, which can simplify query syntax. Refer to [Lateral Join](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/select/#lateral-join.md) for more information. * *New or Modified Engine Variables* Refer to [List of Engine Variables](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables.md) for information on each of the following engine variables.- **Enhancement**: Added a new engine variable `scheduler_slow_loop_seconds` that specifies the threshold for triggering the verbose logging of scheduler thread timing. - **Enhancement**: Added a new engine variable `scheduler_slow_ready_queue_seconds` that specifies the threshold for triggering logging of slow ready queue draining. - **Enhancement**: Introduced global engine variable `pipelines_iceberg_data_workers_heap_size` to control memory consumption on leaves. - **Enhancement**: Added a new engine variable `enable_writable_views` that enables creation of writable views. Refer to [CREATE VIEW](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-view/#section-idm234617498191001.md) for more information. - **Enhancement**: Updated the minimum value of `json_document_max_children` engine variable to `1` (from `128` previously). - **Enhancement**: Added a new engine variable `enable_block_level_stats_collection` that controls the collection of block-level statistics for sub-segment elimination for flexible parallelism. - **Enhancement**: Added a new engine variable `enable_block_stats_use_in_query` that controls whether the block-level statistics are read and used during scan as part of sub-segment elimination for flexible parallelism. - **Enhancement**: Added a new engine variable `pipelines_iceberg_heap_size` to control heap size specifically for Iceberg pipelines. - **Enhancement**: Added `json_collation` global variable to control collation of JSON. The value of `json_collation` can be either `utf8_bin` or `utf8mb4_bin`. - **Bugfix**: During the upgrade to SingleStore 8.9, if the value of `fts2_max_connections` is equal to `100000`, it is set to `32`. * *Miscellaneous* - **Enhancement**: Improved pipeline error clearing. Additional errors are cleared when `CLEAR PIPELINES ERRORS` is run or when the `pipelines_errors_retention_minutes` limit is reached. - **Enhancement**: Improved performance of `OBSERVE` queries on columnstore tables. - **Enhancement**: Improved performance of `OBSERVE` queries by only decoding primary key or internal ID columns for columnstore segment deletes. - **Enhancement**: `OBSERVE` queries no longer block DDL operations while reading columnstore segments. - **Enhancement**: `OBSERVE` queries no longer require timeouts to prevent blocking database drop operations. - **Bugfix**: Fixed a bug in lateral join to preserve projection field aliases for lateral join subselects. - **Bugfix**: Fixed an issue that occurred when `OBSERVE` query was run on columnstore tables after `ALTER` operations.- **Enhancement**: Improve behavior of garbage collection for plancache. - **Bugfix**: Fixed a bug caused by using a (cross-segment) vector index ([Vector Indexing](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/vector-indexing/#section-idm234571614690074.md)) with null vectors.- **Bugfix**: Fixes a data-dependent crash condition occurring in certain `TABLE(JSON_TO_ARRAY(...` joins. - **Enhancement**: Added new nori (Korean) analyzer customizations for Full-Text Search V2. - **Enhancement**: Added logging for LRU compiled unit eviction. - **Enhancement**: Fixed out of memory (OOM) errors and extra memory usage in Iceberg ingest. - **Enhancement**: Introduced new [JSON\_BUILD\_ARRAY](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-build-array.md) function. - **Enhancement**: Introduced support for placeholders for partition ID and timestamp in the `SELECT INTO ... file name` command. - **Bugfix**: Fixed a bug causing accumulation of .rem files on disk. - **Bugfix**: Fixed a small memory bug in columnstore scans that use the `JSON_TO_ARRAY` join optimization.- **Enhancement**: Added Korean language analyzer for full-text search V2. - **Enhancement**: Added support to infer CSV files with a single column when the file contains no field terminators in any record. - **Enhancement**: Relaxed dependency on partition count of leaf nodes for leaf plans.- **Bugfix**: Fixed out-of-memory errors and extra memory usage in Iceberg ingest.- **Bugfix**: Fixed a crash in `JSON_EXTRACT_STRING`. - **Bugfix**: Fixed a bug with updates and asserts using `JSON_ARRAY_CONTAINS_` predicates. - **Bugfix**: Fixed bottomless upload throttling criteria. - **Bugfix**: Blocked creation of temporary table as a shallow copy. - **Bugfix**: Resolved an issue where an in-development subsystem can leak files on disk- **Bugfix**: Fixed handling of heartbeat messages in the MongoDB® extractor in debug mode. - **Bugfix**: Fixed display of default BSON and string values with null-terminators in the information schema. - **Bugfix**: Removed trailing dot for decimal column types that have scale equal to 0. - **Bugfix**: Fixed `IN`-list index matching for columnstore tables when a query has multiple `IN`-list predicates. - **Bugfix**: Fixed a bug that caused an invalid optree error after the `JsonArrayContainsToTableBuiltin` rewrite.- **Bugfix**: Fixed a crash that occurred during spilling when executing a query with a large number of `GROUP BY` columns. - **Bugfix**: Fixed performance regression in Vector Search when using `DOT_PRODUCT` metric. - **Bugfix**: Added an optional 'swap\_time' argument to `UUID_TO_BIN` and `BIN_TO_UUID` functions. - **Bugfix**: Allowed cached table memory to be freed for empty tables in replica databases. - **Bugfix**: Fixed a lateral join parsing bug.* **Enhancement**: Added the `DETERMINISTIC` clause to the `CREATE FUNCTION` (UDF) command that instructs the query optimizer to assume that the created function is deterministic. Refer to [CREATE FUNCTION (UDF)](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/create-function-udf.md) for more information. * **Enhancement**: Added support for the `IGNORE LINES` clause to the `INFER PIPELINE` command for CSV files. Refer to [Schema and Pipeline Inference](https://docs.singlestore.com/cloud/load-data/about-singlestore-pipelines/pipeline-concepts/schema-and-pipeline-inference.md) for more information. * **Enhancement**: Added support for using the `LOAD DATA` command to load files from a Stage without using pipelines. Refer to [Stage](https://docs.singlestore.com/cloud/load-data/load-data-from-files/stage.md) for more information. * **Enhancement**: Added support for using the `SKIP ALL ERRORS` clause during creation of Kafka pipelines for ingesting JSON formatted data. * **Enhancement**: Added support for `SKIP ALL ERRORS` and `SKIP PARSER ERRORS` clauses during creation of Kafka pipelines for ingesting Avro formatted data. * **Enhancement**: Added the ability to load Kafka properties and headers with the `get_kafka_pipeline_prop("")` function. * **Enhancement**: Added the ability to override the `pipelines_max_offsets_per_batch_partition` global variable for each Kafka pipeline using the `MAX_OFFSETS_PER_BATCH_PARTITION` pipeline variable in `CREATE PIPELINE` and `ALTER PIPELINE` commands. * **New feature**: Added support for the following parameters in the `CONFIG` clause of `CREATE PIPELINE AS ... LOAD DATA S3` statement: * `file_compression`: Decompresses files with the specified extensions. * `file_time_threshold`: Only ingest files modified after the specified timestamp. Refer to [S3 Configurations](https://docs.singlestore.com/cloud/load-data/data-sources/configuration-options-for-different-sources/#section-idm234488319463038.md) for more information. * **Enhancement**: Added the ability to use connection links to load Avro and Parquet formatted data stored in an AWS S3 bucket. * **Enhancement**: Enabled auto [PROFILE](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/profile.md) for `INSERT...SELECT` and `REPLACE...SELECT` query shapes. * **New feature**: Added the `ENABLE_OVERWRITE` clause in the `SELECT ... INTO S3` and `SELECT ... INTO LINK` statements that enables the overwriting of existing files. Refer to [SELECT … INTO S3](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/select/#select-into-s-3.md) for more information. * **Enhancement**: Updated [PROFILE](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/profile.md) to show `number_of_blocks_tested_for_block_elim` and `number_of_blocks_eliminated_for_block_elim` for sub-segment elimination for flexible parallelism. * **Enhancement**: Added the ability to load CSV and JSON files from an Amazon S3 bucket using a `LOAD DATA` query. * **Enhancement**: Enhanced the `TABLE()` function to support `DISTINCT`. * **Enhancement**: Improved recovery time for tables with incremental autostats by recovering statistics from disk instead of rebuilding them from scratch. * **Bugfix**: Fixed an issue where attaching a leaf node to the Master Aggregator (MA) failed if the MA was still starting up. * **Enhancement**: Improved columnstore hash index performance on low cardinality column. * **Enhancement**: Added row count estimate for joins in the output of `EXPLAIN` and `PROFILE` queries. * **Enhancement**: Added support for `LIMIT` and `OFFSET` clauses in non-equality `WHERE` conditions in subselects. * **Enhancements**: Added support for `LATERAL` joins for table-valued functions (TVFs). * **Enhancement**: `SYNC DURABILITY` is always enabled for reference databases. Reference tables in user databases that use async durability may notice a decrease in performance for DDL and DML statements. * **Enhancement**: Added support for `HIGHLIGHT ... AGAINST` as a computed column expression when creating a new table. * **Enhancement**: Added support for sub-select to join rewrites for correlated subselects and nested scalar subselects. * **Enhancement**: Added support for null-accepting projections in scalar subselect queries. * **Enhancement**: Added support for `LATERAL` join subselects to reference any level of outer tables. * **Enhancement**: Changed the default collation to `utf8mb4_general_ci` and default character set to `utf8mb4` for `TEXT` and `ENUM` type columns for `CSV`, `JSON`, `AVRO`, and parquet formats in `INFER PIPELINE AS LOAD DATA` statements. * **Bugfix**: Fixed an issue that caused dangling compute sessions after a failed `ALTER DATABASE` command. (8.7.20) * **Bugfix**: Fixed an issue where concurrent `ALTER DATABASE` and `REBALANCE` operations create unlimited storage partitions with wrong compute ID. * **Bugfix**: Fixed a race condition between the transaction log garbage collection and database transition-to-master that could result in unrecoverable partitions. * **Enhancement**: Added support for multi-column `IN` list predicate in the `WHERE` clause of a query. * **Enhancement**: Function mapped `IN`-lists now share the same signature in the plancache for the same set of built-in functions. * **Bugfix**: Removed soft lock on the `CHARACTER SET` clause and added a warning to indicate invalid character set value in the `LOAD DATA` statement. * **Bugfix**: Database names can no longer end with big numbers, such as `db_`, to avoid conflicts with internal databases used in replication. * **Enhancement**: Each node in the cluster now validates the availability of bottle service every minute and records any consecutive failures in the `LMV_EVENTS` information schema view. * **Enhancement**: Added more information to the out-of-memory (OOM) errors. * **Bugfix**: Fixed an issue that occurred while parsing manifest files (associated with backup or restore operations) having more than 4096 characters. * **Bugfix**: Fixed an issue that caused duplication of storage blobs that have not been repaired yet for ongoing repair operations. * **Bugfix**: Fixed an issue in MySQL CDC-in pipelines where some MySQL tables with names containing `_` were not being replicated. * **Bugfix**: Fixed a race condition that caused shutdown to wait on idle async compile manager thread. * **Bugfix**: Fixed an issue where repair operation gets stuck while converting milestones. (8.7.20) * **Bugfix**: Fixed a synchronization issue where the database was not immediately available after some clustering operations. * **Enhancement**: Added the estimated number of partitions returned by the query optimizer for cost-based join order optimization to the debug profile. * **Enhancement**: Added support for `VECTOR` type in `REDUCE` built-in function. * **Enhancement**: Improved the lockdown message when changing collation or character set related engine variables within `utf8mb4` character set. * **Bugfix**: Fixed an issue with `PROMOTE AGGREGATOR ... MASTER` command where a restart at the end of the command, followed by manually finishing the promote operation, caused reprovisioning of the old Master Aggregator. * **Bugfix**: Fixed an issue that caused an empty network prefetch queue. * **Bugfix**: Fixed a distributed deadlock caused by a query blocking reference database reprovisioning in one part of the deadlock cycle. * **Enhancement**: Added the following headers to Data API responses: * `Cache-control: no-store` * `Strict-Transport-Security: max-age=31536000` * `X-Content-Type-Options: nosniff` * **Bugfix**: Fixed an issue with counting common table expressions (CTEs) references in a query. * **Bugfix**: Fixed a memory corruption issue caused by a rare race condition involving `MV_ACTIVE_TRANSACTIONS` information schema view. * **Enhancement**: Sharding planner now recognizes non-union style single partitioned derived table. * **Enhancement**: Disabled pipeline batches sample by default in `memsql_exporter`. * **Enhancement**: Improved message for an error where a global variable cannot be set because of a table sharded on a computed column. * **Enhancement**: Improved connection stability for MongoDB® CDC-in pipelines. * **Enhancement**: Upgraded the [librdkafka](https://github.com/confluentinc/librdkafka) library to version 2.4.0-3. * **Bugfix**: Fixed an issue where the Master Aggregator (MA) temporarily stopped behaving as the MA after being restarted. * **Enhancement**: Improved the performance of cluster operations through distributed plancache. * **Bugfix**: Fixed a bug with incorrect privilege checks. * **Enhancement**: Optimized row locking for internal transactions. * **Enhancement**: Improved performance of `REVOKE` in case of errors. * **Bugfix**: Fixed an engine crash caused when the Master Aggregator received a `REMOVE AGGREGATOR` query with its own `:`. * **Enhancement**: Reduced the chances of reference database reprovisioning in universal storage if a snapshot is taken concurrently with Master Aggregator shutdown. * **Enhancement**: `TO_JSON()` and `JSON_BUILT_OBJECT()` built-in functions now convert `VECTOR` type arguments to JSON array instead of a JSON string. * **Enhancement**: Added support for ZSTD compressed Kafka topics to Kafka pipelines. * **Bugfix**: Fixed an issue where `ClampTimestamp` spammed the tracelog. * **Enhancement**: Limit DR connection attempts to the primary node when it is failing connections, avoiding a substantial increase in `TIME_WAIT` sockets. * **Enhancement**: Improved error messages related to reprovisioning. * **Enhancement**: Implemented rotation and deletion of webproxy socket logs. * **Enhancement**: Ingest Kafka headers into the SingleStore table if they are included in the Kafka message. * **Enhancement**: Lockdown hints for common table expressions (CTEs). * **Enhancement**: Locked select and row count hints for multi-table views. * **Bugfix**: Fixed an issue where slow snapshots blocked clustering operations. * **Bugfix**: Fixed an issue where unrecoverable reference databases did not auto-heal for a prolonged period of time and get blocked, for example, by alter operations. * **Enhancement**: Improved the error message for `Correlated subselect that cannot be transformed and does not match on shard keys` errors. * **Bugfix**: Fixed data loss in sync durability in a rare race condition. * **Enhancement**: Improved processing of queries with redundant (superfluous) `EXISTS` subselects. * **Bugfix**: Fixed the reference count of committed blobs on upgrade. * **Enhancement**: Improved enforcement of `internal_columnstore_max_uncompressed_blob_size` engine variable. * **Enhancement**: Information schema queries are no longer case-insensitive with respect to database names. * **Bugfix**: Fixed a snapshot not found error. * **Bugfix**: Fixed an issue where the result of a seek operation was not handled correctly. * **Enhancement**: Detect scalar subselect requirement at runtime rather than rewrite time for certain cases. * **Bugfix**: Fixed a bug in full-text search query compilation. * **Enhancement**: Enabled `LOAD DATA` queries to use the specified `CHARACTER SET` to ingest [string type](https://docs.singlestore.com/cloud/reference/sql-reference/data-types/string-types.md) fields. * **Bugfix**: Fixed an issue in the `OBSERVE` query that corrupted the blob cache when changes were captured from an unlimited storage database. * **Enhancement**: Improved performance of queries that use the `RAND()` builtin or constant expressions involving `PARTITION_ID`. * **Enhancement**: Added support for a query rewrite when `UNION` is used to merge queries that reference the same tables and have mutually exclusive filters. * **Enhancement**: Improved message for an error that occurs when `OBSERVE` queries are run without selecting a database. * **Bugfix**: Fixed a rare issue where `ALTER` operation and two-phase commit (2PC) caused index corruption. * **Bugfix**: Fixed an issue that caused a crash when the global collation or character was set to a value with the prefix "utf8mb3". * **Bugfix**: Fixed an issue that returned incorrect results when `JSON_MATCH_ANY()` and `BSON_MATCH_ANY()` queries contained nested `JSON_EXTRACT_()`, `JSON_MATCH_ANY()`, `BSON_EXTRACT_`, or `BSON_MATCH_ANY()` functions. * **Bugfix**: Fixed a rare undefined behavior issue caused by `SHOW CLUSTER STATUS` command. * **Bugfix**: Fixed an issue caused when multi-part names were used in common table expressions (CTEs) within UNION. * **Bugfix**: Fixed an issue that caused `DROP DATABASE` and some other cluster management commands to wait indefinitely for resources. * **Bugfix**: Fixed a rare issue that caused autostats file leak. * **Bugfix**: Fixed a rare issue that blocked kill query operations. * **Bugfix**: Fixed CVE-2024-45772 and BDSA-2024-0720 security vulnerabilities. * **Bugfix**: Fixed an error in `BSON_MATCH_ANY()` pushdown. * **Bugfix**: Fixed a bug that caused `OBSERVE` queries to reuse bad plans across database partitions. * **Enhancement**: Improved error message returned when an `OBSERVE` query is run while `enable_observe_queries` engine variable is disabled. * **Bugfix**: `OBSERVE` queries now return correct data when the `vector_type_project_format` engine variable is set as `JSON`. * **Bugfix**: Fixed the result of `ORDER BY LIMIT` query on tables with a sort key column defined in the descending order. * **Bugfix**: Fixed an issue that caused uneven time distribution in the CDC extractor pool. * **Bugfix**: Fixed an issue that delayed restart of nodes with multiple reference databases. * **Bugfix**: Fixed an issue where some point-in-time-recovery (PITR) operations failed when started from a restored snapshot. * **Enhancement**: Upgraded Apache Lucene search engine library to 10.0.0. * **Bugfix**: Fixed a bug that caused a query to fail in a specific situation when using `SEARCH OPTIONS` with vector index search. * **Bugfix**: Fixed an edge case; common table expressions (CTEs) are now recognized in subselect to join rewrite. * **Bugfix**: Fixed a bug that led to a master aggregator crash when using dynamic resource pools. * **Enhancement**: Added support for Iceberg v2 merge-on-read ingest mode. * **Bugfix**: Fixed ingest streaming logic for large Iceberg v2 tables. ## November 2024 * **Enhancement**: Improved out of memory (OOM) error message to make the message more accurate. * *Bugfixes* * **Bugfix**: Fixed a crash that could occur when columns were dropped with `ALTER TABLE`. * **Bugfix**: Fixed an issue where left join sometimes returned an incorrect result when spilling occurred. * **Bugfix**: Fixed an issue that could lead to new connections hanging. ## October 2024 * **Enhancement:** Improved the performance of regular expression (regex) operations. * **Enhancement:** Added a new filter derivation rewrite controlled by the `optimizer_not_null_filter_derivation` engine variable. * **Enhancement**: Improved the performance of `REPLACE` query into a columnstore table when table-level locking is triggered. * **Enhancement:** Added a variable, `max_autostats_update_workers`, to tune the maximum number of background autostats update workers. * **Enhancement**: Improved the Debezium DDL statements parser. * *Bugfixes* * **Bugfix**: Additional binlog position updates for CDC-in pipelines in the extractor pool queue. * **Bugfix**: Disable `JSON_ARRAY_CONTAINS_` rewrite in common table expressions (CTEs). * **Bugfix:** Fixed an edge case certificate refresh issue for connections to remote storage in long running clusters. * **Bugfix**: Improved the error message for CDC-in pipelines for insufficient permissions errors. * **Bugfix**: Fixed deadlock in database recovery due to evicting table module holding an open LOCK. * **Bugfix**: Fixed an issue where the garbage collection of a partition database's 2PC transaction table could unnecessarily delay the reprovisioning of the reference database. * **Bugfix**: Fixed an issue with alter repair where the repair job would be created before alter commits. * **Bugfix**: Fixed a race condition where node shutdown could get stuck if it happens concurrently with a partition failover. * **Bugfix**: Fixed a rare bug where `STOP PIPELINE` could write an old extractor cookie into pipeline metadata. ## September 2024 * **Enhancement**: Added single-parameter `JSON_EXTRACT` builtins. * **Bugfix**: Fixed a crash when using a unique key to upsert into a table when JSON column optimization is enabled. * **Bugfix**: Fixed a certificate refresh issue for connections to remote storage in long-running clusters. * **Enhancement**: Both `END AT TAIL` and the DDL gracefully end `OBSERVE` queries instead of producing an error. * **Enhancement**: Added a new `observe_agg_timeout_secs` global variable that sets the number of seconds an `OBSERVE` query can block on sending data before giving the consumer a network error. * **Bugfix**: Fixed an undefined behavior in the failure path of an `OBSERVE` query. * **Enhancement**: Where appropriate, the `DELETE` records for `OBSERVE` queries now populate the primary key for tables instead of the internal ID. * **Enhancement**: Removed the `TxPartitions` column and added a new `TxTimestamp` column for `OBSERVE`. * **Enhancement**: The internal IDs for `OBSERVE` are now unique across partitions. * **Enhancement**: `OBSERVE` now immediately flushes the result-set metadata to the client, eliminating the need to wait for rows to be returned to receive result metadata. * **Bugfix**: Fixed the failure to reset the socket-write timeout after an `OBSERVE` query has completed. * **Enhancement**: Reading segments in `OBSERVE` now respects `KILL QUERY`. * **Bugfix**: Fixed misleading `KILL QUERY` tracelog messages in `OBSERVE`. * **Bugfix**: Fixed connection reset errors when the value in the `observe_leaf_timeout_secs` engine variable was not used correctly with `OBSERVE`. * **Bugfix**: Fixed `BEGIN AT` in `OBSERVE` when the offset is in the middle of a snapshot on an unlimited storage database. * **Enhancement**: Added an `information_schema.OBSERVE_DATABASE_OFFSETS` view for obtaining various `OBSERVE` offsets. * **Enhancement**: Added support for resumable offsets into columnar segments for `OBSERVE`. The `OBSERVE` query now returns 28-byte offsets. While 24-byte offsets are still valid with `BEGIN AT`, the "strictly increasing" guarantee does not hold when comparing 24-byte and 28-byte offsets. * **Bugfix**: Fixed `OBSERVE` returning the wrong transaction ID (txId) when resuming from some transactions. * **Bugfix**: Fixed an issue where the output of `OBSERVE` would be incorrect when performing a delete on a large columnstore segment. * **Bugfix**: Fixed some cases of an incorrect transaction ID (txId) for snapshot records. `BeginSnapshot/CommitSnapshot` can now report nested `BeginTransaction/CommitTransaction` when there are unresolved distributed transactions while the snapshot is taken. * **Enhancement**: Added metrics to track the availability of unlimited storage and the bottle service. * **Enhancement**: Added support for query rewrite that enables hash joins when two tables are joined together using the `JSON_ARRAY_CONTAINS_` function. * **Enhancement**: Added bottle service reliability metrics to `MV_BOTTOMLESS_STATUS_EXTENDED`. * **Enhancement**: Fixed the `KILLED_QUERIES` counter in `MV_RESOURCE_POOL_STATUS`. * **Enhancement:** Added support for query rewrite for certain query patterns with `EXISTS` and subselects. * **Enhancement**: Added support for BM25 over multiple columns. * **Enhancement**: Added support for BM25 partition-scoped scoring for Boolean and boost queries. * **Bugfix**: Fixed a rare issue where a membership-related clustering operation could delay the re-provision or deletion of a cluster database. * **Enhancement**: Added the `json_collation` engine variable to control JSON collation, the value of which can either be `utf8_bin` or `utf8mb4_bin`. * **Enhancement**: Improved the logic for selecting vectors close to the distance threshold during vector range search. * **Bugfix**: Fixed an issue where `JSON_MATCH_ANY()` queries with `MATCH_ELEMENTS` evaluated incorrectly on non-array values. * **Enhancement**: Updated `JSON_MATCH_ANY()` simplified syntax to allow specifying `MATCH_ELEMENTS` by appending a '`*`' to the end of the keypath. * **Enhancement**: Added support for BM25 partition-scoped scoring for phrase and proximity search queries. * **Bugfix**: Fixed sporadic reading failures from `MV_SYSINFO_CPU`. * **Bugfix**: Clarified the error message produced in some cases where a snapshot gets aborted. * **Bugfix**: Fixed schema history in incremental snapshot mode for MySQL CDC. ## August 2024 * **New Feature**: Added the new `FULLTEXT SERVICE STOP` command. This command stops the full-text V2 service running on any node connected to the aggregator on which the command is run. Syntax: ```sql FULLTEXT SERVICE STOP ``` `FULLTEXT SERVICE STOP` requires the `PROCESS` permission. This command is useful when making configuration changes to the full-text service via engine variables that only take effect when the service (re)starts (`fts2_max_connections`, for example). Also can be used to force a reclaim of resources the service might be using before the idle-out time is reached. The service run state can be checked using `SHOW FULLTEXT SERVICE STATUS`. (8.7.12) * **Enhancement**: Significant overall performance improvements for the full-text V2 service. * **Bugfix**: Fixed an intermittent hang under load issue with the full-text V2 service. * **Bugfix**: Reduce `server_id` collision probability for MySQL CDC. * **Enhancement**: When `parametrize_json_keys` is enabled, numeric keys will be parametrized as well. Also fixed a crash in queries with json extracts within JSON MATCH ANY (when `parametrize_json_keys` is switched on). * **Enhancement**: Update to certificate loading behavior. Prior to this change, only the first certificate was loaded from a server's .pem file specified via the server configuration. Now all certificates are loaded, so if a .pem file contains a certificates chain, then intermediate CA certificates will be returned by the server. * **Enhancement**: Updated the distributed OpenSSL license file to to 1.0.2zj * **Bugfix**: Fixed an issue that caused INFER of CDC-in pipelines to fail with "Failed to allocate slot in the extractor pool" error after increasing the limit. * **Bugfix**: Fixed an issue where concurrent `DROP TABLE`s could block a snapshot from running indefinitely. * **Bugfix**: Address an issue with rewriting of scalar subqueries with null-accepting projections. * **Enhancement**: Added `X-Content-Type-Options: nosniff`, `Strict-Transport-Security: max-age=31536000`, `Cache-control: no-store` headers to Data API responses. * **Enhancement**: Added new engine variables for full-text search, used to fine-tune Lucene service connections: `fts2_socket_timeout_ms` and `fts2_socket_ttl_ms`. These variables control the service's socket timeout and time-to-live. * **Enhancement**: The default value for the `fts2_max_connections` engine variable has been increased to 100000. * **Enhancement**: Improved throughput for full-text v2 queries. * **Enhancement**: Optimized full-text queries that contain an `ORDER BY ... LIMIT ...` over a full-text score and optionally filter on the same full-text clause. * **Bugfix**: Fixed an issue in vector range search with `DOT PRODUCT` as the distance metric. * **Bugfix**: Fixed an issue with lateral join queries with identical aliases. * **Bugfix**: Fixed a possible race condition with DR connections that could cause repeated connection failures when attempting to replicate from the primary. * **Bugfix**: Fixed a rare race condition where PITR would incorrectly operate on an unrecoverable database. ## July 2024 * **Bugfix**: Fixed a password validation error on `ADD AGGREGATOR`. * **Enhancement**: Added a new engine variable: `assume_udfs_deterministic`. This variable controls behavior where SingleStore will do extra work to avoid issues caused by UDFs that return different values when repeatedly invoked (e.g., are non-deterministic). This variable defaults to false, preserving the existing behavior (since SingleStore version 8.0), where SingleStore will ensure these computations take place once on an aggregator node. When set to true, SingleStore will avoid this extra layer of coordination (and bottleneck) allowing leaf nodes to independently coordinate. * **Enhancement**: Added a new engine variable: `external_functions_service_buffer_mb`. Sets the maximum size (in mb) of the memory-mapped region used to communicate between the engine and collocated services. * **Bugfix**: Fixed an issue that could occur when replacing JSON expressions with computed columns in `GROUP BY` lists. * **Bugfix**: Fixed potential deadlock in Lucene service under high load. * **Bugfix**: Fixed an issue where in a VM environment `SHOW LEAVES` could show corrupted latency values due to incorrect values from the system clock if the VM is stopped or migrated. * **Bugfix**: Fixed a vector index issue in unlimited storage that could make a vector index blob file in accessible. * **Enhancement**: Added a new engine variable, `sampling_estimate_approach`, used to configure sampling in columnstore tables. See [Statistics and Sampling Concepts](https://docs.singlestore.com/cloud/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-concepts/#section-idm4556665136652832788440675389.md) for more information. * **Bugfix**: Fixed an issue where indexes were not case-sensitive when performing full-text search against JSON keys. * **Bugfix**: Fixed an issue where `ALTER TABLE` could throw errors related to VECTOR-type columns, even if those columns were not part of the `ALTER`. * **Bugfix**: Fixed an issue in CDC-in pipelines causing delays between batches. * **Enhancement**: Added Feedback Reoptimization via the `REOPTIMIZE` SQL command. The command is used to reoptimize a query based on previous executions of it. You can reoptimize a query multiple times, pick execution plans from any iteration, and then choose the best iteration's plan to commit to use. Syntax: ```sql REOPTIMIZE [[EXPLAIN] | [COMMIT]] [] ``` For more information, see `REOPTIMIZE`. (8.7.7) * **Enhancement**: Added websocket\_proxy.log to the files managed via Trace Log Rotation. * **Enhancement**: Added support for numeric range queries when doing full-text search against JSON fields. Refer to [numeric range queries](https://docs.singlestore.com/cloud/developer-resources/functional-extensions/working-with-full-text-search.md) for more information. * **Enhancement**: Enabled lateral join subselects to reference fields in grandparent `SELECT`. * **Bugfix**: Fixed an issue that could occur when replacing JSON expressions with computed columns in `GROUP BY` lists. * **Enhancement**: Enabled `log_file_size_partitions` and `log_file_size_ref_dbs` engine variables on SingleStore Helios. * **Bugfix**: Fixed display of microseconds for default TIMESTAMP(6) column values in system information commands (such as `DESCRIBE TABLE` or `INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT`). * **Bugfix**: Fixed a potential false positive output for OBSERVE queries during the first delete of large columnstore segments. * **Enhancement**: Allow leading 0x prefix for `OBSERVE` offsets. * **Bugfix**: Fixed an issue where the output of the `OBSERVE` query for the first delete on a columnstore segment was the inverse of expected. * **Bugfix**: Fixed an issue where user defined variables could read collation-related variables incorrectly during a `SET @var = ...` command. * **Bugfix**: Fixed a table eviction issue that can crash a node when replaying straight-to-disk inserts on unique key tables during database recovery. * **Bugfix**: Fixed an issue in MongoDB® CDC-in pipelines which blocks data ingestion after `ALTER PIPELINE`. * **Enhancement**: Allow NULL values in JSON columns when used with Full-Text search. * **Bugfix**: Fixed several system warnings that occur when running a `SELECT` from the `MV_VERSION_HISTORY` information schema view. * **Enhancement**: Improved performance of VECTOR-type User-Defined Variables(UDVs). No longer require using extra typecast to BLOB datatype. * **Bugfix**: Fixed an issue to now avoid prematurely assigning column reference to namespace. * **Enhancement**: Added support for `INNER`/`LEFT` lateral joins. * **Bugfix**: Fixed issue that caused protocol errors in the pipelines. * **Bugfix**: Temporarily disallow shallow table copy for tables created on version 8.0 or before. * **Enhancement**: Enhance table pushdown to union rewrite to enable derived table pushdown based on row count estimation. * **Enhancement**: Added support for `ON NODE` clause for `SHOW PROFILE PROCESS|PLAN`, which forwards the command to another aggregator. * **Enhancement**: Improved performance of the `CREATE PROJECTION` command. * **Bugfix**: Unneeded sort fields will no longer be projected to the aggregator. * **Enhancement**: Improved the performance of `CREATE TABLES AS INFER PIPELINE`. * **Bugfix**: Fixed an issue that could occur where a join with `TABLE(JSON_TO_ARRAY(JSON_EXTRACT_JSON()))` is performed with numeric keys in the `JSON_EXTRACT_JSON()` statement could possibly lead to incorrect output or crashes. * **Bugfix**: Identified an upgrade scenario that causes a blob leak and added a command to delete the leaked blobs. As a result, temporarily disallowing shallow table copy for tables created on version 8.0 or before. * **Enhancement**: Set collation utf8\_bin for the `JSON_TO_ARRAY` builtin in cases where the input has utf8 charset, and set collation utf8mb4\_bin in cases where the input has utf8mb4 charset. * **Bugfix**: An error is reported when inserting incorrect data into vector column for any `data_conversion_compatibility_level`. * **Bugfix**: Fixed a possible connectivity issue and eliminated delay when starting the Lucene service for the first time. * **Enhancement**: Updated the `MV_BOTTOMLESS_STATUS_EXTENDED` information schema table: * Removed the `LATEST_API_FAILURE_RATE` and `AVERAGE_API_LATENCY_US` columns. * Added the following columns: `SUCCESS_API_PER_SECOND` - Number of successful remote storage api calls per second over the last 30 seconds. `FAILED_API_PER_SECOND` - Number of failed remote storage api calls per second over the last 30 seconds. `AVERAGE_API_EVENT_LATENCY_US` - Average latency to complete a remote storage api event over the past 30 seconds. Includes time taken on retries or waiting for rate limiting. `AVERAGE_API_RESPONSE_LATENCY_US` - Average latency of receiving a response for a remote storage api request. Excludes waiting for rate limiting or retries. NOTE: This is relevant only for S3. otherwise this field is identical to `AVERAGE_API_EVENT_LATENCY_US`. ## June 2024 The following updates have been made this month: * **Enhancement**: Added the `enable_use_of_stale_incremental_stats` engine variable which allows the optimizer to use stale stats instead of querying for stats during optimization. * **Enhancement**: Added the `throttle_replay_behind_tail_mb` engine variable which controls how far the system allows a child aggregator to lag behind replicating the transaction log of its master aggregator before throttling is applied to the master aggregator. * **Bugfix**: Fixed issues with the `TO_CHAR()` function where results were truncated or reading communication packet errors were generated. * **Bugfix**: Fixed an integer overflow issue in unique integer columns by changing the storage type to a 32-bit unsigned integer. * **Bugfix**: Fixed incorrect results in an uncommon scenario where hash join operators with an optimization path need to spill. * **Enhancement**: The `memsql_exporter` now collects additional fields from the `mv_activities_extended_cumulative` information schema view. * **Enhancement**: Updated the default `client.id` for the Kafka configuration to the SingleStore partner ID. * **Enhancement**: Added the following monitoring metrics to the `information_schema.mv_bottomless_status_extended` view: `NUM_SUCCESS_API_REQUESTS`, `NUM_FAILED_API_REQUESTS`, `LATEST_API_FAILURE_RATE`, `AVERAGE_API_LATENCY_US`, `IS_GC_RUNNING`, `NUM_REMOTE_FILES_DELETED`, and `NUM_SLOW_DOWNS`. * **Bugfix**: Fixed a crash that occurred when spilling a hash join operation with at least 3 tables. * **Bugfix**: Fixed a crash that occurred when running the `SHOW CREATE PROJECTIONS` command when using the database name in the syntax (`SHOW CREATE PROJECTIONS database_name.projection_name...`). * **Bugfix**: Fixed a bug where the full-text service controller would periodically restart the process when the started process hadn't fully completed. * **Bugfix**: Fixed an issue where `INSERT ... SELECT` into `VECTOR` type columns returned an error. * **Enhancement**: Added support for Iceberg ingest via Snowflake, REST, and JDBC. * **Enhancement**: Added support for BM25 full-text search (FTS) as a built-in feature. * **Bugfix**: Fixed an edge case regression with SingleStore Kai and information schema queries when using multiple workspaces and databases. * **Bugfix**: Fixed a crash that occurred when dropping a database with tables containing very high and very low column IDs. ## May 2024 * **Bugfix**: Addressed a crash issue with `REGEX_REPLACE`. * **Bugfix**: Fixed an issue in `ALTER TABLE` when adding a computed column containing `JSON_BUILD_OBJECT`. * **Bugfix**: Reduced stack usage during compilation for some `CASE` expressions with many `WHEN-THEN` statements. * **Bugfix**: Fixed a potential deadlock issue between background garbage collector threads and foreground query execution threads. * **Bugfix**: Fixed a collation issue in the `JSON_COLUMN_SCHEMA` information schema table. * **Enhancement**: SingleStore now supports module eviction of idle tables with an `ALTER` history. This means that, prior to the 8.5.20 release, tables that had been altered could not be evicted. Altered here includes `CREATE INDEX`, `ALTER TABLE ADD COLUMN`, and some others. * **Bugfix**: Fixed a bug with Wasm UDFs where `REPLACE FUNCTION` did not correctly evict from the plancache, causing continued usage of the old UDF after replacement. * **Bugfix**: Fixed an upgrade issue present in version 8.5.17. * **Bugfix**: Fixed a BSON issue that could occur when large documents are read and mistakenly reported as corrupted. * **Enhancement**: Upgraded OpenSSL 1.0.2u to 1.0.2zj * **Bugfix**: Fixed a crash issue that could occur under a rare race condition in some distributed join queries. * **Bugfix**: Fixed statement atomicity in transactions writing to tables that have projections on them. * **Enhancement**: Allow Kerberos users to skip password validation for users who are authenticated with plugins. ## April 2024 * **Enhancement**: Added support for loading data from compressed Avro datafiles. * **Bugfix**: Fixed an issue that occurred when primary key is NULLABLE when copying a table using "Create table as select" statement. * **Bugfix**: Addressed a crash issue occurring while executing queries with vector builtins on clusters running via Docker containers on Mac machines equipped with Apple silicon processors (M1). * **Bugfix**: No longer show information\_schema pipeline metadata of databases that are not attached to the workspace. * **Enhancement**: Use SeekableString encoding by default for storing VECTOR datatype columns in columnstore tables. * **Bugfix**: Fixed an issue that caused rare "Arrangement already exists in namespace" errors in recursive CTE queries. * **Bugfix**: Improved the hashGroupBy spilling condition to trigger (spill) when appropriate. * **Enhancement**: Added a new engine variable `costing_max_djo_tables` which sets the maximum amount of tables we allow in full DQO estimate search when filling table row estimates for distributed join hints. * **Enhancement**: Made the following engine variables configurable on SingleStore Helios: `multi_insert_tuple_count, columnstore_row_value_table_lock_threshold, internal_columnstore_max_uncompressed_blob_size, internal_columnstore_max_uncompressed_rowindex_blob_size, columnstore_validate_blob_before_merge, default_max_histogram_buckets, enable_varbuffer_dictionary_compression, enable_alias_space_trim, skip_segelim_with_inlist_threshold, default_autostats_histogram_mode, default_autostats_columnstore_cardinality_mode, default_autostats_rowstore_cardinality_mode, default_autostats_columnstore_sampling, experimental_disable_multipartition_parallel_read, internal_enable_parallel_query_throttling, enable_spilling, spilling_node_memory_threshold_ratio, spilling_query_operator_memory_threshold, regexp_compile_mem_mb, selectivity_estimation_min_sample_size, repartition_minimum_buffer_size, use_seekable_json, json_document_max_children, json_document_max_leaves, json_document_sparse_children_check_threshold, json_document_sparse_children_check_ratio, json_document_absolute_sparse_key_check_ratio, json_document_page_data_soft_limit, ignore_insert_into_computed_column, max_subselect_aggregator_rowcount, leaf_pushdown_enable_rowcount, reshuffle_group_by_base_cost, max_broadcast_tree_rowcount, enable_broadcast_left_join, display_full_estimation_stats, force_bushy_joins, force_heuristic_rewrites, force_table_pushdown, force_bloom_filters, explain_joinplan_costs, statistics_warnings, optimizer_warnings, optimize_constants, optimize_huge_expressions, optimize_expressions_larger_than, optimize_stmt_threshold, quadratic_rewrite_size_limit, batch_external_functions, batch_external_functions_size, optimizer_enable_json_text_matching, optimizer_enable_orderby_limit_self_join, distributed_optimizer_broadcast_mult, distributed_optimizer_old_selectivity_table_threshold, distributed_optimizer_selectivity_fallback_threshold, distributed_optimizer_verbose, old_local_join_optimizer, optimizer_hash_join_cost, optimizer_merge_join_cost, optimizer_nested_join_cost, optimizer_disable_right_join, interpreter_mode_sampling_threshold, hash_groupby_segment_distinct_values_threshold, sampling_estimates_for_complex_filters, enable_histogram_to_unwrap_literals, estimate_zero_rows_when_sampling_data_is_missing, exclude_scalar_subselects_from_filters, leaf_pushdown_default, distributed_optimizer_nodes, optimizer_num_partitions, enable_binary_protocol, enable_multipartition_queries, enable_local_shuffle_group_by, enable_skiplist_sampling_for_selectivity, columnstore_sample_per_partition_limit, disable_sampling_estimation, disable_histogram_estimation, inlist_precision_limit, allow_materialize_cte_with_union, optimizer_cross_join_cost, distributed_optimizer_run_legacy_heuristic, distributed_optimizer_min_join_size_run_initial_heuristics, distributed_optimizer_unrestricted_search_threshold, singlebox_optimizer_cost_based_threshold, distributed_optimizer_estimated_restricted_search_cost_bound, disable_reference_table_leftjoin_where_pushdown, disable_sampling_estimation_with_histograms, disable_subquery_merge_with_straight_joins, default_columnstore_table_lock_threshold, default_spill_dependent_outputters, query_rewrite_loop_iterations, verify_fields_in_transitivity, optimizer_min_reference_tables_for_gathered_join, optimizer_min_reference_rows_for_gathered_join, max_expression_query_limit, max_expression_item_limit, optimizer_empty_tables_limit, optimizer_beam_width, optimizer_disable_subselect_to_join, disable_remove_redundant_gby_rewrite, subquery_merge_with_outer_joins, optimizer_disable_operator_tree_nullability_check, clamp_histogram_date_estimates, varchar_column_string_optimization_length, histogram_column_correlation, use_column_correlation, consider_secondary_projection, optimizer_disable_semi_join_reduction, optimizer_gb_placement_table_limit, optimizer_disable_transitive_predicates` * **Enhancement**: Added more config information about pipelines to memsql\_exporter. * **Enhancement**: Improved error handling for VECTOR type. * **Enhancement**: Added support for aggregate functions (`FIRST`, `LAST`) with VECTOR datatype. * **Enhancement**: Added support for Kafka key (producer) in the [SELECT ... INTO KAFKA](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/select/#select-into-kafka.md) command. * **Enhancement**: Improved Kafka error messages to make them more specific. * **Bugfix**: Fixed a case where a killed query could incorrectly return a JSON error rather than query interrupted message. * **Bugfix**: `MV_DISK_USAGE` queries can no longer block garbage collection. * **Bugfix**: Fixed a crash-on-startup issue on Centos9. * **Bugfix**: Fixed potential deadlock between table module eviction and GC manager. * **Bugfix**: Fixed a crash that could occur when a scalar subselect is passed as an argument to an external TVF. * **Bugfix**: Fixed an issue with restoring from public backups. * **Bugfix**: Backups now throttle after snapshot. * **New Feature**: Added ability to [create a Projection](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-projection.md) which is a copy of some or all columns in a table, and may be sharded and sorted differently than the primary table. Projections can be used to speed up several types of queries including range filters, `ORDER BY`/`LIMIT`, `GROUP BY`, `count(DISTINCT...)`, `DISTINCT`, joins, and row lookups on secondary keys. Projections depend on the table and are updated in real time when you update the table. Related information schema table: [PROJECTIONS](https://docs.singlestore.com/cloud/reference/information-schema-reference/workspace-component/projections.md). * **Bugfix**: Removed an unnecessary metadata copy to improve JSON performance. * **Enhancement**: Multi-insert queries now respect lower `columnstore_segment_rows` settings. * **Enhancement**: Subprocess and S3 API tracing are now also enabled via the `enable_subprocess_tracing` engine variable. * **Enhancement**: Added the optional `IF NOT EXISTS` clause to the `CREATE TABLE ... AS INFER PIPELINE` statement. Refer to [Replicate MongoDB® Collections using SQL](https://docs.singlestore.com/cloud/reference/singlestore-kai/replicate-mongodb-collections-to-singlestore/replicate-mongodb-collections-using-sql.md) for more information. ## March 2024 * **Bugfix**: `LAST_INSERT_ID` will now correctly retrieve the last inserted ID for a forwarded `INSERT` query. * **Bugfix**: The rule based optimization warning message will only be shown when cost based optimization is meaningful. * **Bugfix**: Fixed a minor performance issue where a table module could be evicted prematurely during database recovery. * **Bugfix**: Fixed a very rare deadlock between `ALTER` of a table and a `DROP` of a database. * **Bugfix**: Fixed a JSON insertion failure to old utf8 sample tables that contain utf8mb4 characters by encoding it with base64 format beforehand for backward compatibility. * **Enhancement**: Disk Spilling now takes `QUERY_MEMORY_PERCENTAGE` resource pool setting into consideration. * **Bugfix**: Fixed an issue with a blob file leak in a rare crash scenario. * **Bugfix**: Fixed a potential deadlock in a scenario involving DDL, a 2PC transaction, and a concurrent failover. * **Bugfix**: Fixed a deadlock involving `BACKUP` and `ALTER` in a rare case. * **Enhancement**: Added support for utf8mb4 symbols in column comments, table comments, and user comments. * **Enhancement**: Enabled additional query hint for non-distributed queries so that first run of those queries can be queued. * **Enhancement**: Updated timezone data, now using IANA's tzdata version 2024a; new timezones Kyiv, Ciudad\_Juarez, and Kanton supported. * **Enhancement**: Idle Table Eviction improvement. In the 8.5 GA version, an idle table's objects cannot be evicted from memory if the table has one or more UNIQUE key columns (both PRIMARY KEYs and any other keys with a UNIQUE constraint). In the 8.5.11 patch, idle tables' code objects can be evicted from memory if the table has one or more UNIQUE key columns. * **Bugfix**: Fixed an issue in background merger that allowed concurrent deletes of empty segments. * **Bugfix**: Fixed an issue to ensure preserving a field of a table correctly. * **Enhancement**: Added `numInferredPaths` and `schemaSize` as new columns to `JSON_COLUMN_SCHEMA` table. `numInferredPaths` is the number of key paths inferred for the segment. `schemaSize` is the size of `schema_json` in bytes. * **Bugfix**: Added safety checks around dropping databases without exclusive access to storage. * **Bugfix**: Fixed an issue where explicitly defining a JSON type in the RETURNS of a Wasm TVF can cause an error when it is run. * **Bugfix**: Fixed a rare crash scenario. * **Bugfix**: Fixed an issue that could lead to the risk of undefined behavior when running `DROP EXTENSION IF EXISTS`. * **Enhancement**: `IN`-lists will now use hashmap optimization for matching parameters in `*_MATCH_ANY` statements. * **Enhancement**: Made `default_distributed_ddl_timeout` a sync variable on all nodes. * **Enhancement**: Modified the conversion logic for converting from VECTOR to BSON types. Now, when casting VECTOR(F32), it will generate a BSON array of doubles, rather than a combination of numeric types. * **Bugfix**: Addressed a family of issues relating to optimal execution of JSON extracts on schema'd data. ## February 2024 * **Bugfix**: Fixed an issue with regression in replay performance for databases with many tables. * **Enhancement**: Added support for usage of vector built-ins with string/JSON arguments without requiring an explicit typecast to VECTOR (e.g., 'SELECT @vec<\*> '\[1,2,3]'). * **Enhancement**: Added spilling metrics for TopSort (`ORDER BY` with `LIMIT`). * **Enhancement**: Added support to the VECTOR built-ins for all VECTOR elements types (e.g., F32, F64, I8, I16, I32, and I64). * **Enhancement**: Added support for creating numeric histograms based on JSON values. (Feature flag gated, contact your SingleStore representative) * **Enhancement**: Added new metrics to `memsql_exporter` based on the `information_schema.mv_sysinfo_disk` columns. * **Enhancement**: Creation of computed columns with VECTOR data type is now allowed. * **Enhancement**: Added ability for `information_schema.optimizer_statistics` to display JSON keys. * **Enhancement**: Added ability to skip eviction when log replaying hits the blob cache space limit, providing a greater chance to succeed. * **Enhancement**: Improved JSON histograms to support numeric histograms, analyze command, `info_schema.optimizer_statistics`, and displays JSON histograms. * **Enhancement**: Added support for warming blob cache with table's column data. Syntax is: `OPTIMIZE TABLE WARM BLOB CACHE FOR COLUMN `: ```sql OPTIMIZE TABLE t WARM BLOB CACHE FOR COLUMN c1, c2; OPTIMIZE TABLE t WARM BLOB CACHE FOR COLUMN *; ``` * **Enhancement**: Disabled the default semi join reduction rewrite. * **Enhancement**: Now recognize more `EXTRACT`/`MATCH` functions as candidates for pushdown/computed column matching. * **Enhancement**: Replaced expression pushdown (EPD) approach with more complete version that does not over project rows. * **Bugfix**: Fixed an issue where the existing websocket connection would close when variables are updated in global scope. * **Bugfix**: Removed the hard-coded 'collation\_server' from `constant.StrictModeParams`. * **Bugfix**: Fixed an issue with a sensitive information leak inside of out-of-memory reports. * **Bugfix**: Fixed the result collation from `VECTOR` built-ins to be `utf8mb4_bin` instead of binary. * **Bugfix**: Fixed an issue with nested extracts in the `JSON_MATCH_ANY()` statement. * **Bugfix**: Fixed an issue with backup restore when the backup has a corrupted GV timestamp in the about snapshot record. * **Enhancement**: The new [engine variable](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists.md) `parametrize_json_keys` allows JSON keys to be parametrized and plans can be reused. * **Bugfix**: Fixed an issue that causes `CREATE TABLES AS INFER PIPELINE` to fail with an `Out Of Memory` error. * **Bugfix**: Fixed an issue with exporter not working with Public-Key Cryptography Standards #8(PKCS#8) pem format. * **Bugfix**: Fixed an issue with potential crashes when specifying a vector index in index hint. * **Bugfix**: Fixed a crash that occurred when trying to grant access to the table with no context database in per-privilege mode. * **Bugfix**: Fixed a memory issue, when calling a Table-Valued Function (TVF) that has arguments with default values. * **Bugfix**: Fixed an issue with the RG pool selector function return type check. * **Enhancement**: Added ability for `information_schema.mv_connection_attributes` to show `tls_version` and `tls_cipher` for connections where `SSL` is enabled. * **Enhancement**: Added ability for predicate pushdown with `NOW()` and user defined functions (UDF). * **Enhancement**.: Reduced amount of memory used by unlimited storage file listing, including point-in-time recovery (PITR). * **Enhancement**. Added an engine variable, `innodb_lock_wait_timeout.` This variable exists for backwards compatibility with MySQL and is non-operational in SingleStore Helios. * **Enhancement**. Optimized performance when using `VECTOR` built-ins with the `VECTOR` data type. * **Bugfix**: Fixed an issue where a backup would inadvertently carry metadata about the retention period of the backed-up unlimited storage database. * **Bugfix**: Fixed the change to aggregator activity queries in `memsql_exporter` not being applied properly. * **Bugfix**: Fixed an issue with `START PIPELINE FOREGROUND` skipping files in some circumstances. * **Enhancement**: Added ability to `memsql_exporter` to always return all sub-activities of an aggregator activity. * **Enhancement**: Improved ability to configure Kafka extract/egress by allowing additional options. * **Enhancement**: Improved performance for certain query shapes used by Kai. * **Bugfix**: Fixed the query-events endpoint link on the `memsql_exporter` landing page. * **Bugfix**: Fixed an issue where a segmentation fault (crash) appeared after terminating a query on the remote node. * **Bugfix**: Fixed an issue with crashes occurring when using certain `JSON_EXTRACT`s in the predicate expression of `JSON_MATCH_ANY()`. * **Bugfix**: Fixed an issue where the number of rows affected with `SELECT INTO` object storage was erroneously reported as 0 rows. ## January 2024 **Smart DR** Smart DR creates and manages a continuous replication of data to a geographically separate secondary region thereby allowing you to failover to the secondary region with minimal downtime. For more information, refer to [Smart Disaster Recovery (DR): SmartDR](https://docs.singlestore.com/cloud/manage-data/smart-disaster-recovery-dr-smartdr.md) **Database Branching** Database branching enables you to quickly create private, independent copies of your database for development, testing and other scenarios. For more information, refer to [Database Branching](https://docs.singlestore.com/cloud/developer-resources/database-branching.md) **Extensions** Extensions in SingleStore allow you to combine user-defined objects, such as UDFs or UDAFs, into a packaged archive (the extension) and then create, manage, and deploy these objects and other resources using a single command. Extensions support both Wasm-based and PSQL functions. For more information, refer to [Extensions](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/extensions.md). **Trace Events and Query History** Added the ability to trace query completions as events, which is the initial installment of the larger event tracing framework. The Query History feature relies on query event tracing, and can be used to display query trace events over time. The Query History feature can therefore be used to troubleshoot and optimize query performance, including, but not limited to, tracing and recording expensive queries, resolving unexpected slowdowns, and viewing and optimizing workloads in real time. Refer to [Query History](https://docs.singlestore.com/cloud/query-data/query-tuning/query-history.md) for more information. **Improved Memory Management for Resource Pools** Added the `QUERY_MEMORY_PERCENTAGE` option for resource pools, which restricts memory usage in the pool on a per-individual query basis. This in contrast to `MEMORY_PERCENTAGE` which restricts usage based on total memory used within the current pool. For example, when creating or altering a resource pool, setting `MEMORY_PERCENTAGE` to `60%` and `QUERY_MEMORY_PERCENTAGE` to `50%` would configure the system so that all queries running within the specified resource pool should together use a maximum of 60% of system memory, and any single query running within the pool should use, at most, 50% of system memory. Example syntax: ```sql CREATE RESOURCE POOL rpoolmain WITH MEMORY_PERCENTAGE = 60, QUERY_MEMORY_PERCENTAGE = 50, SOFT_CPU_LIMIT_PERCENTAGE = 65, MAX_CONCURRENCY = 40; ``` **Load Data Updates** SingleStore now supports loading data using the Change Data Capture (CDC) pipelines from the following data sources: MongoDB® and MySQL. Refer to [Replicate MongoDB® Collections to SingleStore](https://docs.singlestore.com/cloud/reference/singlestore-kai/replicate-mongodb-collections-to-singlestore.md) or [Replicate Data from MySQL](https://docs.singlestore.com/cloud/load-data/data-sources/replicate-data-from-mysql.md) for information on loading data from the respective data source. **Enhancement**: Other Performance Enhancements * SingleStore now supports creating shallow copies of tables. The `WITH SHALLOW COPY` [feature](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-table.md) copies an existing table and creates a new table that will have the same structure as the original table. The data is not physically copied to the new table, but referenced against the original table. * SingleStore now supports sorted scan query plan operators for queries containing `ORDER BY/LIMIT` clauses when utilizing flexible parallelism. Before this enhancement, there could be performance regressions for this query shape using flexible parallelism. * Improved performance when completing large sets of security operations (creating a lot of groups/users/roles, etc.). * Added the ability to use named argument notation when calling a PSQL SP or function. Can reduce total lines of code and make code more readable. * Added reduction of memory pre-allocation during columnstore JSON reads. * Added ability to check if all leaf node partitions are available, before processing new batches. * Addressed a table resolution issue for embedded recursive Common Table Expressions (CTEs). * SingleStore now natively supports the BSON data type. **Enhancement**: Query Optimization Enhancements * Added support for Row Count and Selectivity hints in views. * Added new join logic to recognize when a non-reference table is being joined exclusively to reference tables and then gather the non-reference table to avoid duplicating work across every partition. * Remove redundant aggregation functions and `GROUP BY` statements. * JSON expressions are properly pushed down. * Allow columnstore optimization for `JSON_MATCH_ANY` with `JSON_EXTRACT` in predicate. * Added support for hash joins on null-accepting expressions in the `ON` condition of outer joins. * Automatically rewrite `A=B OR (A IS NULL AND B IS NULL)` to null safe equal `(A<=>B)` so that many important optimizations (e.g. shard key joins, hash joins) will work. * Perform a subselect to join rewrite in an `UPDATE` statement when there are multiple columns in the subselect. * Removed some query shape lockdowns. * Added support for flipping join order for full outer join. * Improved performance by not executing query optimization procedures for read queries during the process of persistent plan cache lookup. This optimization strategy has resulted in improved lookup performance, leading to faster data retrieval operations. * Added support for statistics on correlations between columns in cases where highly correlated filters are used. * The Data API now supports HTTPS for connections where `ssl_key` is encrypted with `ssl_key_passphrase`. * Removed parametrization of `LIMIT 0` and `LIMIT 1` to unlock more rewrites, especially for subselects. * Modified computed column matching to accurately evaluate JSON expressions containing equality and non-safe equality comparisons. * Fixed an issue where filtering with a `JSON_EXTRACT_` function performs inconsistently. * The query optimizer now considers more `LEFT JOIN` elimination cases. **Enhancement**: New Information Schema Views and Columns * Added a new view, `correlated_column_statistics`, to provide [metadata on correlated columns](https://docs.singlestore.com/cloud/reference/information-schema-reference/query-performance-workload-management-and-statistics/correlated-column-statistics.md). * Added a new view, `RESOURCE_POOL_PRIVILEGES`, to provide [information](https://docs.singlestore.com/cloud/reference/information-schema-reference/resource-and-user-management/resource-pool-privileges.md) about resource pool grants and privileges. * Added the following new columns to `MV_BACKUP_HISTORY`: * `error_code`: Error code for failed backups. * `error_message`: Error message for failed backups. * Added the following new columns to `MV_SYSINFO_DISK`: * `read_operations_cumulative_per_device`: Number of read operations performed by the device since start up. * `write_operations_cumulative_per_device`: Number of write operations performed by the device since start up. * `device_name`: Name of the device to which the values in `read_operations_cumulative_per_device` and `write_operations_cumulative_per_device` are associated. * Added the following to support trace events that are used by the [Query History](https://docs.singlestore.com/cloud/query-data/query-tuning/query-history.md) feature: * `MV_TRACE_EVENTS`: A snapshot of all trace events, the size of which is dictated by the `trace_events_queue_size` variable * `MV_TRACE_EVENTS_STATUS`: A view that reflects the status of current trace events * `LMV_TRACE_EVENTS`: A snapshot of each node's trace events * Added the following new column to `ADVANCED_HISTOGRAMS`, `L/MV_QUERY_PROSPECTIVE_HISTOGRAMS`, and `L/MV_PROSPECTIVE_HISTOGRAMS`: * `JSON_KEY`: an entry for each (column, json\_key) pair. For non-json columns `JSON_KEY` is NULL. * Added the `blob_cache_miss_b` and `blob_cache_wait_time_ms` columns to the following information schema views: `information_schema.plancache`, `information_schema.MV_ACTIVITIES`, `information_schema.MV_ACTIVITIES_CUMULATIVE`, `information_schema.MV_TASKS`, `information_schema.MV_FINISHED_TASKS`, and `information_schema.MV_QUERY_ACTIVITIES_EXTENDED_CUMULATIVE`. * **Bugfix**: Updated the `information_schema.USERS` view to reflect the account status for locked users. **Enhancement**: New Commands and Functions * 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. * Added support for `CUBE` and `ROLLUP` grouping operations to Wasm-based user-defined aggregate functions (UDAFs) in SingleStore. For more information, refer to [CREATE AGGREGATE](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/create-aggregate.md). * Added the following to support trace events that are used by the [Query History](https://docs.singlestore.com/cloud/query-data/query-tuning/query-history.md) feature: * `CREATE EVENT TRACE` to create a trace event * `DROP EVENT` to drop a trace event * Added ability to use `DELETE` on identical keys with the `ON DUPLICATE KEY` clause. This is in addition to existing "upsert" support with `ON DUPLICATE KEY UPDATE`. This allows new scenarios such as the ability to manage streaming aggregation with `INSERT … ON DUPLICATE KEY UPDATE … ELSE DELETE ….` * `INFER PIPELINE` for MongoDB® CDC-in now generates tables with BSON column types. **Enhancement**: New or Modified Engine Variables * Added `enable_idle_table_optimizations` and `enable_idle_table_eviction`, which are used to reduce table memory overhead for idle tables on a cluster. The feature is enabled by default on all new *and existing* clusters. The variable `enable_idle_table_optimizations` needs to be set at runtime and requires a restart for changes to take effect. It can be set to OFF or ON (default). The other related variable, `enable_idle_table_eviction`, can be set during a session (though a very small amount of overhead will remain until the server is restarted) and can be set to Full, SkipListsOnly, and Off. It defaults to SkipListsOnly, which means SingleStore will only evict skiplist indexes for idle tables on the cluster. Full means it will evict skiplists and table modules, and Off means no eviction. * Added `privilege_caches_update_mode`, which can be used to address some performance issues that occur when performing large sets of security operations (creating a lot of groups/users/roles, etc.). * The `query_parallelism` engine variable (which was deprecated in 8.1) now is non-functional. To modify Flexible Parallelism settings, use `query_parallelism_per_leaf_core` instead. * Added the `use_user_provided_index_types_in_show` engine variable which controls what will be displayed via the `DESCRIBE ` or `SHOW COLUMNS` syntaxes for backward compatibility. * Added the `throttle_replay_behind_tail_mb` engine variable which controls how far the system allows a child aggregator to lag behind replicating the transaction log of it's master aggregator before throttling is applied to the master aggregator. * Added the ability to use the `ANALYZE` command with JSON keys to create histograms when the new engine variable `enable_json_statistics` is enabled. The engine variable `enable_json_statistics` is disabled by default. * Added `trace_events_queue_size` to capture trace events, the first of which is query event tracing. This engine variable is enabled by default (set to a value of 16 MB, where the value must be provided in bytes). Refer to [Query History](https://docs.singlestore.com/cloud/query-data/query-tuning/query-history.md) for more information. * Added the `optimizer_disable_transitive_predicates` engine variable which disables predicate transitivity on query rewrites if set to `TRUE`. This engine variable defaults to `FALSE`. * Added a new engine variable `pipelines_cdc_java_heap_size` to specify the JVM heap size limit for CDC-in pipelines. * Added the `bottomless_experimental_blobstore_mode` engine variable, which when enabled, completes additional verification of persisted files immediately after upload. This mode is experimental and may reduce upload speed. Please use with caution. * The new [engine variable](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists-2.md) `max_table_memory_room_mb` sets the maximum amount of memory required when creating or attaching a database. Configuring this engine variable allows more control over whether a detached database can be reattached. * The [engine variable](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists-3.md) `backup_multipart_upload_concurrency` maximum value has been increased to 15. **Miscellaneous Enhancements and Bugfixes**: * **Enhancement**: Introduced a new `VECTOR` data type that is recommended over the `BLOB` data type for vector operations and use with ANN indexes. The `VECTOR` type improves code readability, error checking, and reduces total lines of code. * **Enhancement**: Added support for DDL forwarding for `CLEAR BACKUP_HISTORY`.`CLEAR BACKUP_HISTORY` works on the DML endpoint now. * **Enhancement**: Improved retry logic for `connection reset: write` errors. * **Enhancement**: SingleStore now natively supports the BSON data type. * **Enhancement**: Added support for `collection.exclude.list`, `database.include.list`, and `database.exclude.list` parameters to the `CONFIG`/`CREDENTIAL` clause of the `CREATE AGGREGATOR PIPELINE ... AS LOAD DATA MONGODB` statement. Refer to [Replicate MongoDB® Collections to SingleStore](https://docs.singlestore.com/cloud/reference/singlestore-kai/replicate-mongodb-collections-to-singlestore.md) for more information. * **Enhancement**: Added support for Approximate Nearest Neighbor (ANN) vector search using inverted file (IVF) and hierarchical navigable small world (HNSW) indexes, and variants of them based on product quantization (PQ). Enables support of larger-scale semantic search and generative AI applications. * **Enhancement**: Increased name length limit to 256 characters for tables, views, table columns, view columns, procedures, functions, and aliases. Database names are still limited to 64 characters. Note: Column aliases are truncated during projection (`SELECT`) to 256 bytes (not characters) which impacts names that use multi-byte characters. The full names are stored in the database and can be queried. Information schema tables can be used to view the full names. * **Enhancement**: Added the ability to truncate plancache file names if they exceed the operating system's specified limit (255 bytes). * **Bugfix**: Both `SHOW PIPELINES` and `SELECT * FROM information_schema.pipelines` now show consistent pipeline state information across master and child aggregators. * **Bugfix**: Specific pipeline built-ins like `pipeline_source_file()` and `pipeline_batch_Id()` should not be used in `UPSERT` clause when creating a pipeline. * **Bugfix**: Improved the ability to terminate expressions containing JSON built-ins. * **Enhancement**: Added error-handling details for pipelines, including state, error, and performance-related metrics through monitoring solutions. * **Enhancement**: MemSQL Procedural SQL (MPSQL) has been renamed to simply Procedural SQL (PSQL). The name change will only show in some `SHOW` command output and information schema views. For example, `SHOW FUNCTIONS` output changed. * **Enhancement**: Added support for `%ROWTYPE` and `%TYPE` for use in declaring scalar type variables and parameters. Employing these abbreviations in Procedural SQL (PSQL) can lead to a reduction in the required lines of code. * **Enhancement**: Introduced blob cache profiling metrics for columnstore tables on unlimited storage databases. Refer to the [PROFILE](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/profile.md) page for more information on what blob cache metrics are available. * **Enhancement**: Added infix operators for dot\_product (<\*>) and euclidean\_distance (<->). * **Enhancement**: Added ability to delay retry attempts for pipeline retries. * **Bugfix**: Updated the output for the `Key_name` and `Index_type` columns in the `SHOW INDEX`, `SHOW INDEXES`, and `SHOW KEYS` commands for primary keys on columnstore tables. Refer to the [SHOW INDEX, SHOW INDEXES, SHOW KEYS](https://docs.singlestore.com/cloud/reference/sql-reference/show-commands/show-index-show-indexes-show-keys.md) page for more information. * **Bugfix**: Improved the error message displayed when trying to create a primary key on an existing table. * **Bugfix**: Improved the error message displayed when a `GRANT` command fails due to missing permissions. The error message will now show the missing permissions: ```sql GRANT SELECT, UPDATE, DELETE, EXECUTE ON *.* TO test2; ``` ```output ERROR 1045 (28000): Current user is missing UPDATE, DELETE permission(s) for this GRANT ``` * **Bugfix**: Fixed a case when `UNIX_TIMESTAMP()` was incorrectly returning 999999999.999999 for `DATETIME` data types with precision. * **Bugfix**: Fixed an issue where a user could be erroneously marked as deleted. * **Bugfix**: Fixed an issue where a child aggregator could crash if it ran out of memory during query forwarding. * **Bugfix**: Fixed an issue with blob cache LRU2 eviction that could occur when a query fetches a blob, evict it, and fetches it again. * **Bugfix**: Fixed an issue that could cause information for a blob to be missing from an Information Schema table. * **Bugfix**: Disk Spilling now takes the resource pool settings into consideration. * **Enhancement**: Auto user creation is deprecated and the `NO_AUTO_CREATE_USER` variable is enabled by default. * **Bugfix**: Fixed an erroneous access denied issue to views selecting from shard tables with computed columns. * **Bugfix**: Fixed a rare issue where the incorrect timezone could be used in logging. * **Bugfix**: Fixed an issue where using user-defined variables inside `ORDER/GROUP BY` statements could cause a crash. * **Enhancement**: Added column name to error messages when invalid date/time is inserted into a column. * **Enhancement**: Added BSON columnstore functionality over Parquet storage. * **Enhancement**: Added support for `SELECT ... INTO KAFKA` using OAUTH credentials. * **Bugfix**: Prevent the ability to create a Kafka Pipeline using Parquet Format. * **Bugfix**: Queued Time is now excluded from the cost estimate for workload management leaf memory. * **Bugfix**: Specific error messages are now logged for GCS subprocess failures. * **Bugfix**: Fixed a network communication error that occurred after query rewrites. * **Bugfix**: Fixed an error that occurred when a user attempts to access a view that is based on a table that has no privileges granted on it. * **Enhancement**: Improved the ability to kill queries containing JSON built-in functions. * **Bugfix**: `JSON_KEY` escape characters were not working as expected. * **Bugfix**: `ALTER PIPELINE` setting `max_partitions_per_batch` to use a default 0 value is now allowed. * **Enhancement**: Improved performance for `JSON_EXTRACT_` built-ins in `ORDER BY` clauses. * **Enhancement**: Now suppressing a harmless `traceSuspiciousClockUpdate` trace message during recovery. * **Bugfix**: Fixed an issue where `INSERT...SELECT` queries with a `partition_id()` filter generating an error. * **Bugfix**: Fixed an issue with memory crashing when using `REGEXP_MATCH`, `JSON_INCLUDE_MASK`, or `JSON_EXCLUDE_MASK` built-ins. * **Enhancement**: Improved performance by optimizing joins on `TABLE(JSON_TO_ARRAY())` queries. * **Bugfix**: Fixed an allocation issue that caused poor performance on high load insertion queries. * **Enhancement**: Enabled support for external UDFs used in `INSERT` statements with multiple `VALUE` clauses. * **Enhancement:** Added BSON fundamentals and column type support for SingleStore Kai. * **Bugfix**: Fixed a bug that could result in unrecoverable databases if the database had 1024 or more tables. * **Bugfix**: Fixed an optimization out-of-memory issue cause by operators generated for wide user tables. * **Bugfix**: Fixed a crash that occurs in rare scenarios involving `ALTER TABLE` and failovers. * **Bugfix**: Fixed ineffective search options that change in subsequent vector search queries. * **Bugfix**: Resolved an issue related to `FROM_BASE64` and `TO_BASE64` builtins when processing large string inputs thereby preventing potential errors in reading communication packets. * **Bugfix**: Fixed the code involved in backups to improve download error messaging. * **Bugfix**: Fixed an bug that could cause scans using non-unique indexes which could return incorrect results or cause crashes. ## December 2023 * **Enhancement**: Added support for creating a `DEEP COPY` of tables with computed columns. * **Enhancement**: Added additional config and credential option validation while creating pipelines. * **Bugfix**: Fixed an issue where valid `LINK` config and credential parameters were not supported for both reading from and writing to a datasource. ## November 2023 * **Enhancement**: Notebooks now have autosave (currently saves every 5 seconds). * **Enhancement**: Added additional node metrics to the /cluster-metrics endpoint of the `memsql_exporter`. * Added three new fields to `MV_SYSINFO_DISK`: * `READ_OPERATIONS_CUMULATIVE_PER_DEVICE`: Number of read operations performed by the device since start up. * `WRITE_OPERATIONS_CUMULATIVE_PER_DEVICE`: Number of write operations performed by the device since start up. * `DEVICE_NAME`: Name of the device to which the values in `read_operations_cumulative_per_device` and `write_operations_cumulative_per_device` are associated. * **Enhancement**: Changed the SingleStore Helios workspace default and range for the `pipelines_cdc_row_emit_delay_us` engine variable. Throttling default value is set to 1. The supported range is from 0 to 1000000. * **Enhancement**: Addressed some performance issues that occur when performing large sets of security operations (creating a lot of groups/users/roles, etc.) via the new `privilege_caches_update_mode` engine variable. * **Enhancement**: Improved performance during snapshotting for CDC-in pipelines. ## October 2023 * **Enhancement**: Added ability to infer CSV data with text boolean values. * **Enhancement**: Added support for simple multi-column update with sub-query. * **Enhancement**: Added ability to use SSL keys with a password in the HTTP API. * **Bugfix**: Fixed an issue preventing nodes from attaching during upgrade. * **Bugfix**: Fixed an upgrade issue where some databases could temporarily become unrecoverable if snapshots were skipped during a pre-upgrade on a recently attached unlimited database. * **Enhancement**: The `singlestore_bundle.pem` file, which SQL clients can use to connect to SingleStore Helios, will be updated as of October 20, 2023. As a consequence, connecting to SingleStore Helios may not be possible until this file has been (re-)downloaded. Refer to [Connect to SingleStore Helios using TLS/SSL](https://docs.singlestore.com/cloud/connect-to-singlestore/connect-with-mysql/connect-with-mysql-client/connect-to-singlestore-helios-using-tls-ssl.md) for more information. ## September 2023 * **New Feature**: SingleStore Spaces - Find a gallery of notebooks to learn about scenarios that SingleStore covers at: * **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](https://docs.singlestore.com/cloud/user-and-workspace-administration/workspace-health-and-performance/configure-monitoring-with-datadog.md). Monitor the health and performance of your SingleStore Helios workspaces in Datadog. * **Enhancement**: Enhanced the performance of DDL statements for role manipulation. * **Enhancement**: Added two [engine variables](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists-4.md), `jwks_username_field` and `jwks_require_audience` to add flexibility and improve security. * **Enhancement**: Added two [engine variables](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists-5.md): `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](https://docs.singlestore.com/cloud/reference/sql-reference/data-types/integer-numbers.md). 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 variable](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists-6.md) `allow_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**: SingleStore 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 SingleStore 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](https://docs.singlestore.com/cloud/container-services/notebooks.md). 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 ` 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 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 `SPLIT` [string function](https://docs.singlestore.com/cloud/reference/sql-reference/string-functions/split.md) that was not detecting the correct argument type when used inside a [table-valued function](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/create-function-tvf.md) (TVF) or a [user-defined scalar-valued function](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/create-function-udf.md) (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 SingleStore Helios. * **Enhancement**: Added the `max_execution_time` [engine variable](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables/#non-sync-variables-list.md). 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 SingleStore Helios. * **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 SingleStore Helios workspace. This feature is currently available in [these regions](https://docs.singlestore.com/cloud/reference/singlestore-kai/#section-idm457032586024963367437811064.md). 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](https://docs.singlestore.com/cloud/reference/singlestore-kai.md). Here's a few additional references: * [Migrate from MongoDB® to SingleStore](https://docs.singlestore.com/cloud/reference/singlestore-kai/migrate-from-mongodb-to-singlestore.md) * [MongoDB® to SQL Mapping](https://docs.singlestore.com/cloud/reference/singlestore-kai/mongodb-to-sql-mapping.md) * [SingleStore Extension Commands](https://docs.singlestore.com/cloud/reference/singlestore-kai/singlestore-extension-commands.md) * [Supported MongoDB® Commands, Data Types, and Operators](https://docs.singlestore.com/cloud/reference/singlestore-kai/supported-mongodb-commands.md) **New Feature**: Introduced a [columnstore row](https://docs.singlestore.com/cloud/create-a-database/columnstore/how-the-columnstore-works.md) 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**: SingleStore Helios 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 SingleStore Helios workspaces with heterogeneous hardware, e.g., with different levels of SIMD support. **New Feature**: Added a `METADATA_ONLY` [argument](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-table.md) 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](https://docs.singlestore.com/cloud/query-data/query-tuning/query-performance-tools.md) 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](https://docs.singlestore.com/cloud/reference/information-schema-reference.md) 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.USERS` [view](https://docs.singlestore.com/cloud/reference/information-schema-reference/resource-and-user-management/users.md). **New Feature**: [Notebooks](https://docs.singlestore.com/cloud/container-services/notebooks.md) 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 Kai 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](https://docs.singlestore.com/cloud/security/database-access/authenticate-via-jwt.md) 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](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/load-data/#section-idm4580319144742433635119437575.md) and [Parquet](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/load-data/#section-idm46104602580400336351264225.md) 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 SingleStore 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 SingleStore Helios, 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](https://docs.singlestore.com/cloud/user-and-workspace-administration/workspace-health-and-performance/historical-monitoring.md) 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 SingleStore Helios. **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. ## February 2023 **Enhancement**: Background [snapshots](https://docs.singlestore.com/cloud/reference/sql-reference/operational-commands/backup-database/#UUID-8b6b8e22-d95a-f708-4d77-92d995d5594d.md) 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.TABLES` [view](https://docs.singlestore.com/cloud/reference/information-schema-reference/workspace-component/tables.md) are now properly set for views and TVFs (table-valued functions). **Bugfix**: Fixed an issue that occurred when the `MATCH_PARAM_` argument of the `JSON_MATCH_ANY`[function](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-match-any.md) was not in a predicate expression. **Enhancement**: Improved the performance of the `JSON_MATCH_ANY_EXISTS` [function](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-match-any.md) 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_threads` [engine variable](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists-9.md) 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](https://docs.singlestore.com/cloud/reference/data-api/data-api-endpoint-reference/jwks-setup.md) 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 `ROUTINES`[information\_schema](https://docs.singlestore.com/cloud/reference/information-schema-reference/information-schema-view-list.md) views. Also, the `DATETIME_PRECISION` column will include `TIME` and `TIMESTAMP` data types in the `COLUMNS`information\_schema view. **Enhancement**: Added the `REVERSE()` built-in [string function](https://docs.singlestore.com/cloud/reference/sql-reference/string-functions/reverse.md) 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 SingleStore. 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)](https://docs.singlestore.com/cloud/query-data/advanced-query-topics/with-common-table-expressions.md). **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 ; ``` **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](https://docs.singlestore.com/cloud/reference/sql-reference/date-and-time-functions.md). 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, SingleStore 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](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-table.md). **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()` as `VARCHAR()`: For a column defined as type CHAR of length len, SingleStore 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_`. 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. * *Code Engine - Powered by Wasm* The Code Engine feature allows you to create UDFs 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](https://docs.singlestore.com/cloud/reference/code-engine-powered-by-wasm.md). * *Workspaces* The Workspace feature allows you to spin up compute resources and size them up or down on-demand independent of storage.Workspaces also provide greater flexibility than workspaces by allowing databases to be shared across multiple workspaces thereby eliminating the need of maintaining data across multiple workloads.See [Compute Workspaces](https://docs.singlestore.com/cloud/getting-started-with-singlestore-helios/compute-workspaces.md) for more information.The [SingleStore Management API](https://docs.singlestore.com/cloud/reference/management-api.md) now supports workspaces. For more information see, [Management API Reference](https://docs.singlestore.com/cloud/reference/management-api/reference.md). * *`OUTBOUND` privilege* The `OUTBOUND` privilege can be used to mitigate security risks. The privilege can be assigned to users who are allowed to create outbound internet connectivity.For more information, see [GRANT](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/grant.md). ## 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](https://docs.singlestore.com/cloud/query-data/advanced-query-topics/flexible-parallelism.md). **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](https://docs.singlestore.com/cloud/reference/sql-reference/string-functions/trim.md) 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 "\" during parameterization. The string is unchanged for the query however. ``` CALL db.do_something_useful('root', SECRET('super-secret-password')); ``` See [SECRET](https://docs.singlestore.com/cloud/reference/sql-reference/string-functions/secret.md) 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](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/create-or-replace-external-function.md). **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](https://docs.singlestore.com/cloud/query-data/query-plan-operations.md) 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](https://docs.singlestore.com/cloud/query-data/query-plan-operations.md) for a detailed explanation of broadcasts and other distributed data movement. **New Feature**: Ingest, Added support for transactions in Kafka pipelines. ## In this section * [SingleStore Release Types](https://docs.singlestore.com/cloud/release-notes/singlestore-release-types.md) *** Modified at: June 1, 2026 Source: [/cloud/release-notes/](https://docs.singlestore.com/cloud/release-notes/) (An index of the documentation is available at /llms.txt)