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 Clusters for additional information.

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.

• 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 file filtering based on the WHERE clause in the pipeline declaration. Refer to Iceberg Ingest for more information.

JSON

• Introduced Multi-Value Hash Index 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 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 the auditlog_root_only engine variable that enables audit logging for only the root user.

• 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 <pipeline_name> clause to the SHOW PROFILE 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.

• Added a HEADER DETECTION ON/OFF clause to control inference from CSV files. For more information, refer to Schema and Pipeline Inference.

• Added support for inferring Parquet files with the INFER PIPELINE command. For more information, refer to Schema and Pipeline Inference.

• Added support for inferring Iceberg schemas automatically on pipeline creation with the INFER PIPELINE command. For more information, refer to Schema and Pipeline Inference.

• 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.

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 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 <charset> and COLLATE <collation> 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.

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

• For deployments with small cache size, changed the default blob cache size to 75% of the disk. Refer to Setting the Blob Cache for details.

• 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) 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.

• Added support for updated Standard and Enterprise licenses.

• 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:

• auditlog_root_only: Enables audit logging for only the root user.

• 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_max_connections: Specifies the maximum number of HTTP connections per node for managed external functions, such as Python User Defined Function (UDF)s.

• 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: 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 : Added cardinality_threshold_for_join_split_rewrite and target_group_size_for_join_split_rewrite to control the behavior of this optimization.

• Replication: Note: Only use the following under the guidance of, or as directed by, SingleStore Support.

  • repl_page_desc_limit_before_stall: Limits the number of page descriptors in the page map that a 'ReplLog' can use before stalling new transactions.

  • repl_io_buffer_limit: Limits the number of IO buffers that a 'ReplLog' can use before stalling new transactions.[dr_]

  • repl_network_buffer_size: Replication tries to set both send and receive buffer sizes to reduce waits on TCP ACK packets. Can be set for both DR and non-DR replicas.[dr_]

  • repl_max_chunk_size: When sending logs or snapshots to a replica, limit the chunk size to this value

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.