Maintenance Release Changelog v8.7

Bugfix: Fixed a lateral join parsing bug.

New feature: Added a SHOW CDC EXTRACTOR POOL command that displays information about the CDC-in pipelines. Refer to SHOW CDC EXTRACTOR POOL for more information.

Bugfix: Fixed an issue that caused a deadlock between the DROP TABLE and AGGREGATOR SYNC AUTO_INCREMENT queries.

Bugfix: Fixed an issue where the Master Aggregator could temporarily stop behaving like the Master Aggregator after being restarted.

Enhancement: Allow rewrite for joining tables on the JSON_ARRAY_CONTAINS_<type> function to support multiple predicates of that form on different columns.

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 for more information.

Enhancement: Added information on remote API calls and bottle service reliability metrics to MV_BOTTOMLESS_STATUS_EXTENDED information schema view.

Enhancement: Added metrics to track the availability of unlimited storage and the bottle service to MV_BOTTOMLESS_SUMMARY information schema view.

Bugfix: Fixed an issue where repair operation gets stuck while converting milestones.

Enhancement: Implemented heartbeats for bottle service. 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.

Bugfix: Fixed an issue that caused dangling compute sessions after a failed ALTER DATABASE command.

Bugfix: Fixed an edge case; common table expressions (CTEs) are now recognized in subselect to join rewrite.

Bugfix: Fixed an error in BSON_MATCH_ANY() pushdown.

Bugfix: Fixed an issue in the OBSERVE query that corrupted the blob cache when changes were captured from an unlimited storage database.

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 issue caused when multi-part names were used in common table expressions (CTEs) within UNION.

Bugfix: Fixed issue that causes uneven time distribution in extractor pool.

Bugfix: Fixed the result of ORDER BY <sort_key> LIMIT queries on tables with a sort key column defined in DESC order.

Enhancement: Improved out of memory (OOM) error message to make the message more accurate.

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.

Enhancement: Improved the performance of regular expression (regex) operations.

Bugfix: Additional binlog position updates for CDC-in pipelines in the extractor pool queue.

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.

Bugfix: Fixed an edge case certificate refresh issue for connections to remote storage in long running clusters.

Enhancement: Improved the Debezium DDL statements parser.

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.

Enhancement: Added single-parameter JSON_EXTRACT builtins.

Enhancement: Disabled the optimize_json_computed_column engine variable by default.

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

Enhancement: The optimize_json_computed_column engine variable is now disabled by default. This only impacts new workspaces/clusters.

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:

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.

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.

Bugfix: Fixed an issue with json computed column optimization crashes on computed column shard key.

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 TABLEs 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 a new engine variable, optimize_json_computed_column, which is used to enable or disable optimization for json computed columns. Enabled by default.

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.

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 for more information.

Enhancement: Added a method to throttle upload ingest when blob cache has low evictability and running out of disk space is imminent. This is controlled via two new engine variables: bottomless_upload_throttle_hard_limit_cache_usability and bottomless_upload_throttle_soft_limit_cache_usability.

bottomless_upload_throttle_hard_limit_cache_usability: The usability (free space + evictable space) of blob cache below which all columnstore ingest is throttled.

bottomless_upload_throttle_soft_limit_cache_usability: The usability (free space + evictable space) of blob cache below which some columnstore ingest is throttled.

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:

REOPTIMIZE [[EXPLAIN] | [COMMIT]] [<reoptimize_level>]

For more information, see REOPTIMIZE.

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

Bugfix: Fixed an edge case where unlimited storage garbage collection was causing the master aggregator to run out of connections.

Bugfix: Fixed a crash that occurred when dropping a database that contained tables with both very high and low column IDs.

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.

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 support for Iceberg ingest via Snowflake, REST, and JDBC.

Enhancement: Added support for BM25 full-text search (FTS) as a built-in feature.