> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.firebolt.io/feedback

```json
{
  "path": "/reference/release-notes/release-notes-archive",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

> Release notes archive for the Firebolt data warehouse.

# Release notes archive

export const idx_name_0 = undefined

We provide an archive of release notes for your historical reference.

## Firebolt Release Notes - Version 4.30

### New Features

**Introduced vector search indexes for low-latency similarity search over high-dimensional embeddings**

Introduced vector search indexes that provide sub-second top-K similarity search at scale using the HNSW algorithm. This feature enables fast approximate nearest neighbor (ANN) retrieval directly in SQL, powering semantic search, recommendations, and AI-driven analytics without requiring a separate vector database. Vector search indexes maintain full ACID consistency with base table data and support both in-memory and disk-backed serving modes with tunable precision and performance parameters.

Create a vector search index on a column containing embeddings using the `CREATE INDEX ... USING HNSW` syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE INDEX doc_embeddings_idx ON documents USING HNSW (
  embedding vector_cosine_ops
) WITH (
  dimension = 256,
);
```

Query the index using the `vector_search()` table-valued function to find the most similar vectors:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT *
FROM vector_search (
  INDEX doc_embeddings_idx,
  target_vector => <target_vector>,
  top_k => 10
);
```

For more information, see the [vector search index overview](/overview/indexes/vector-search-index), [`CREATE VECTOR INDEX`](/reference-sql/commands/data-definition/create-vector-search-index), and [`vector_search()`](/reference-sql/functions-reference/vector/vector-search) documentation.

**Introduced the `AI_EMBED_TEXT` function for text embeddings with Amazon Bedrock**

Introduced the `AI_EMBED_TEXT` function to generate text embeddings using Amazon Bedrock. This feature enhances text processing capabilities by enabling efficient embedding generation directly within the data warehouse.

**Revamped history-based statistics lifecycle is now in public preview**

Users that want to collect history-based statistics (HBS) and utilize them for query optimization now have full control over the entire HBS lifecycle.
The new lifecycle primitives (available from 4.30 as public preview) allow Firebolt users to decide:

* For which queries you want to collect HBS.
* What type of training examples and models you want to include in your HBS objects.
* For which queries to use an HBS object and which HBS object to use.

The controls can be used as building blocks for implementing different operational patterns (A/B testing of HBS objects, online vs offline training, etc.).
We will discuss those in an upcoming blog post.s

**Enhanced the `ARRAY_SLICE` function to support arrays with `STRUCT` values**

The `ARRAY_SLICE` function now supports arrays containing `STRUCT` values. This enhancement allows users to handle complex data types more efficiently.

**Enabled viewing and customization of connection snippets for SDK access in Firebolt UI Connect**

Users are now able to view and customize connection snippets for accessing Firebolt using available SDKs. This enhancement simplifies the integration process, offering more control and flexibility when connecting to the data warehouse.

**Added `number_of_shredded_columns` and `number_of_columns_streams` to `INFORMATION_SCHEMA.TABLES` for enhanced data storage management**

Added `number_of_shredded_columns` and `number_of_columns_streams` to the `INFORMATION_SCHEMA.TABLES` system view. These additions provide access to details about the number of shredded STRUCT columns and the number of streams used for storing these columns. This information helps users better understand and manage data storage structures.

**Added partial support for Iceberg schema evolution with field reordering, addition, and removal functions**

Introduced partial support for Iceberg schema evolution, allowing the reordering, addition, and removal of fields from a table. This enhancement provides greater flexibility when managing evolving table structures.

### Behavior Changes

### Performance Improvements

**Added `max_table_partitions_on_insert` to limit total table partitions for table health.**
Added the `max_table_partitions_on_insert` flag to [limit the total number of partitions in a table](/reference-sql/system-settings#partition-limits). Excessive partitioning can degrade Firebolt table health and query performance. If an ingestion query would cause the total partition count to exceed this value, the query will fail. The default limit is 10000 partitions for accounts created after Dec 1 2025.

### Bug Fixes

**Non-deterministic or order-dependent plans on dimension tables**

Fixed potential incorrect results from non-deterministic or order-dependent plans directly on top of dimension table scans.

**Fixed an issue where `NULL` values in single-column `IN SELECT` expressions returned `FALSE` instead of `NULL` when the subquery was non-empty without `NULL` values**

Resolved an issue where `NULL` values on the left side of a single-column `IN SELECT` expression incorrectly returned `FALSE` instead of `NULL`. This occurred when the subquery was non-empty and contained no `NULL` values. This correction ensures accurate query results.

**Invalid plans for queries with `array_agg(<struct_literal>)` expressions**

Fixed an issue where `array_agg(<struct_literal>)` aggregates were causing the query planner to construct invalid plans in multi-node execution environments.

**Fixed CSV parsing to handle files with extra columns gracefully**

Resolved an issue where `read_csv` queries failed when files contained more columns than expected. CSV parsing now automatically skips unknown fields.

## Firebolt Release Notes - Version 4.29

### New Features

**Firebolt Agent in the develop workspace**

Users can now use the Firebolt Agent to assist in development. There are multiple modes available: chat mode, in-editor generate mode, in-editor improve mode, fix errors mode

**Integration with Metabase Cloud**

Firebolt now supports Metabase Cloud. This integration allows users to connect and analyze their data directly in Metabase.

**Cross-database queries**

This new feature allows you to seamlessly query and manipulate data across multiple databases within your Firebolt account. Using a simple three-part identifier (database.schema.table), you can now perform JOINs, UNIONs, MERGEs, INSERT and CREATE operations between databases. This enhancement provides the flexibility to combine data for complex analysis while maintaining the organizational benefits of database separation.

Example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
    s.customer_id,
    s.sale_amount,
    m.campaign_name
FROM
    sales.public.customer_sales AS s
JOIN
    marketing.public.website_visits AS m
ON
    s.customer_id = m.customer_id;
```

For more information, refer to the [reference-page on cross-database queries](/reference-sql/commands/queries/cross-database-queries).

**Added support for `ALTER TABLE` operations to set or drop a primary index**

`ALTER TABLE ... SET PRIMARY INDEX (...)` enables users to add or change the primary index of an existing table.

Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER TABLE table_name SET PRIMARY INDEX (column_name[, another_column_name,...]);
```

`DROP PRIMARY INDEX` enables removing the primary index.

Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER TABLE table_name DROP PRIMARY INDEX
```

These operations update only the table's metadata. The existing data retains the original primary index structure until `VACUUM (UPGRADE=TRUE) table_name` is run.
For more information see [ALTER TABLE SET PRIMARY INDEX](/reference-sql/commands/data-definition/alter-table#alter-table-set-primary-index) reference page.

**Add preliminary support for writing Iceberg tables**

Added preliminary support for writing file-based Iceberg tables. This feature is in public preview. We are gathering feedback and further refining this feature.

Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE ICEBERG TABLE iceberg_table_name
  AS SELECT <select_query>
WITH LOCATION = <location_name>;
```

For details, please refer to [CREATE ICEBERG TABLE AS SELECT](/reference-sql/commands/data-definition/create-iceberg-table-as-select).

**Add `iceberg_unsafe_version_guessing_mode`**

For file-based Apache Iceberg catalogs without a `version-hint.text` file, you can now enable Firebolt to [attempt to guess the correct metadata file to read](/reference-sql/system-settings#guessing-iceberg-metadata-version) by setting `iceberg_unsafe_version_guessing_mode` to `version_num`. This behavior is disabled by default.

**Added support for configurable query cache size at the engine level**

The `CREATE ENGINE` and `ALTER ENGINE` commands now support the `QUERY_CACHE_MEMORY_FRACTION` parameter. You can configure the size of the engine's [query cache](/overview/queries/understand-query-performance-subresult) relative to the total engine's usable main memory (default is 20%). To adjust this setting, set `QUERY_CACHE_MEMORY_FRACTION` to a decimal value using `ALTER ENGINE my_engine SET QUERY_CACHE_MEMORY_FRACTION = 0.4` (configures the engine to use up to 40% of its main memory for query caching). This allows you to configure the engine resources used for query caching based on your specific workload needs.

**Allowed primitive data type names like `INT`, `TEXT`, and `DOUBLE` to be used as table and column identifiers without quotation marks**

Primitive data type names such as `INT`, `TEXT`, and `DOUBLE` can now be used as table and column identifiers without requiring quotation marks. This enhancement simplifies SQL queries and improves ease of use.

**Added the SQL functions `JSON_IS_VALID` and `CHECK_JSON` for JSON document validation**

Added two new SQL functions to verify the validity of JSON documents. These functions help ensure JSON data integrity and are useful for data validation.

* `JSON_IS_VALID(TEXT) -> BOOL` checks whether the input string is a valid JSON document and returns TRUE if it is. It returns NULL if the input is NULL.
* `CHECK_JSON(TEXT) -> TEXT` returns NULL if the input string is a valid JSON document or if it is NULL. Otherwise, it provides an error message.

Example:

```
SELECT JSON_IS_VALID('{"name": "John"}'); -- returns TRUE
```

**Extended `AT TIME ZONE` operator to support `TEXT` columns for time zone arguments**

The `AT TIME ZONE` operator now supports `TEXT` columns as the time zone argument, in addition to `TEXT` constants. This enhancement broadens the flexibility when dealing with time zone conversions in SQL queries.

**`WITH` clause within subqueries for more efficient queries.**

Support for the `WITH` clause within subqueries is now available. This enhancement allows for more efficient query writing and improved readability, making complex SQL queries easier to manage and understand.

**Multiple URLs in the `READ_AVRO` function**

Users can now specify multiple URLs in the `READ_AVRO function`. For example, you can use: `SELECT * FROM READ_AVRO(['s3://bucket1/pat*rn1/*.avro', 's3://bucket2/patter*2/*'])`. This update allows more flexible data input options from multiple sources.

**New columns in `information_schema.engine_running_queries`**

Added new columns to [`information_schema.engine_running_queries`](/reference-sql/information-schema/engine-running-queries):
`CLUSTER_ORDINAL`, `NODE_ORDINAL`, `SERVICE_ACCOUNT_NAME`, and `LOGIN_NAME`.
This allows for getting more detailed information on where exactly a query is running.

### Behavior Changes

**Added safeguards to prevent dropping databases that are set as default for any engine**

A database cannot be dropped if it is used as default databases for any engine.
This safeguard prevents invalid configurations where an engine's default database no longer exists.
To list all the engines which use a database as default, execute:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
select engine_name from information_schema.engines where default_database = 'database_name'
```

and then proceed with ALTER ENGINE to remove the default database from the listed engines.\
The results from information\_schema.engines change according to your permissions. Consult your account admin if those instructions are not sufficient.

For more information refer to the [ALTER ENGINE](/reference-sql/commands/engines/alter-engine) and [information\_schema.engines](/reference-sql/information-schema/engines) reference pages

**End of ARM opt-out for COMPUTE\_OPTIMIZED engines**

Engines in the COMPUTE\_OPTIMIZED (CO) family that have opted out of using the ARM architecture will be migrated to an equal type on ARM architecture.
It will no longer be possible to create CO engines with an AMD-specific engine type (e.g., S\_AMD, M\_AMD). Instead, use a standard type (e.g., S, M).

### Performance Improvements

**Late materialization optimization for top-k queries to boost performance by up to 10 times.**

[Late materialization](/performance-and-observability/query-planning/late-materialization) optimization is now available for top-k queries.This change significantly improves the speed of eligible queries, potentially making them 10 times faster. Users gain enhanced query performance, reducing wait times for data processing.

**Integrated filter predicates into table scans on managed tables to optimize data retrieval efficiency.**

Filter predicates are now integrated into table scans on managed tables, enhancing data retrieval efficiency. The scan process loads columns, evaluates predicates, and checks for qualified rows. It only continues loading and checking additional predicates if rows qualify. This integration reduces the data loaded during scans with selective filters, optimizing performance. Previously, only some selective filters were included in the scan process. This update streamlines data retrieval and improves efficiency.

**Cached statistics from Parquet files optimized query performance for repeated workloads.**

Firebolt now caches statistics from Parquet files to optimize query performance. This speeds up workloads that query the same Parquet files multiple times, enhancing overall efficiency for users.

**Enabled fully scale-out right and full outer joins between fact and dimension tables on multi-node engines**

Right and full outer joins between fact tables and dimension tables are now performed in a fully scale-out manner on multi-node engines. This change improves performance and balances memory usage across the cluster.

### Bug Fixes

**Fixed a bug that prevented `LATERAL` from enabling lateral references within a view.**

Resolved a bug that prevented the `LATERAL` keyword from enabling lateral references within a view. This fix ensures correct query behavior and results when using lateral references in views.

**Fixed the `CALL RECOMMEND_DDL` function to succeed with `SELECT` statements filtering on `GEOGRAPHY` columns**

Fixed an issue where the `CALL RECOMMEND_DDL` function failed when any `SELECT` statement in the second argument included a filter on a `GEOGRAPHY` column. The `CALL RECOMMEND_DDL` function now succeeds in these cases, but it does not recommend adding `GEOGRAPHY` columns to the primary index because `GEOGRAPHY` columns are not supported in primary indexes.

## Firebolt Release Notes - Version 4.28

### New Features

**Enabled support for Tableau Cloud access to Firebolt**

Firebolt now supports Tableau Cloud. See [documentation](https://docs.firebolt.io/guides/integrations/tableau) for detailed instructions.

**Extended ownership transfer to support roles as owners**

You can now transfer ownership of objects to roles in addition to users. The existing `ALTER <object_type> <object_name> OWNER TO <owner>` syntax now accepts both user names and role names as the owner.

Supported objects that can be owned by roles: location, database, engine, schema, table, view, role, user.

Examples:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER DATABASE my_db OWNER TO my_role;
ALTER TABLE my_table OWNER TO admin_role;
ALTER ENGINE my_engine OWNER TO ops_role;
ALTER ROLE user_role OWNER TO admin_role;
```

This enhancement provides more flexible ownership management by allowing roles to own objects, enabling better delegation of administrative responsibilities within teams.

**ALTER DEFAULT PRIVILEGES**

Firebolt now introduces ALTER DEFAULT PRIVILEGES to manage permissions for future schemas. This RBAC feature simplifies access control by setting default privilege, such as `USAGE`, `CREATE`, or `ALL` privileges.

Applies only to newly created schemas on the account wide scope, using the following syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER DEFAULT PRIVILEGES { GRANT | REVOKE } <privilege> ON SCHEMAS { TO | FROM } <role_name>
```

For example, executing the following will grant the `USAGE` privilege over every future schema created in the account.

```
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO role_name;
```

Users can view current assigned default privileges by querying `information_schema.object_default_privileges`.

**Added support for reading non-UTF-8 values in string columns from Parquet**

When reading Parquet string columns, you can now choose to have invalid UTF-8 bytes replaced with the unicode replacement character � (U+FFFD) instead of raising an error. This is available across [`read_parquet`](/reference-sql/functions-reference/table-valued/read_parquet), [COPY FROM](/reference-sql/commands/data-management/copy-from), and [external table definitions](/reference-sql/commands/data-definition/create-external-table). In all cases, set `replace_non_utf_bytes` to `true`, for example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
select * from read_parquet(location => 'orders', replace_non_utf_bytes => true);
create external table customers (...) type = (parquet) replace_non_utf_bytes = true;
copy into invoices from ... with (type = parquet, replace_non_utf_bytes = true);
```

**Extended `MERGE` and `INSERT ON CONFLICT` syntax to support `INSERT *` and `UPDATE SET *`**

`MERGE` and `INSERT ON CONFLICT` now support `INSERT *` and `UPDATE SET *` (see full [syntax](/reference-sql/commands/data-management/merge.mdx)). This is syntactic sugar for previously existing functionality, that can be used to ingest or overwrite rows without any data transformation. Note that these substitutions are allowed *only* when the source and target tables have precisely matching column names.

**Information schema to inspect engine caches**

Added the [`information_schema.engine_caches`](/reference-sql/information-schema/engine-caches) view to inspect the contents of
various caches like [result cache](/overview/queries/understand-query-performance-subresult)
and [reusable common table expressions](/reference-sql/commands/queries/select#reusable-common-table-expressions).

### Performance Improvements

**Introduced late materialization optimization for top-k queries**

The [late materialization](/performance-and-observability/query-planning/late-materialization) optimization was introduced for top-k queries.
This enhancement speeds up eligible top-k queries tremendously, improving query processing efficiency and performance.

**Larger reusable CTEs**

Increased the maximum entry size for common table expressions (CTEs) that are marked as [materialized reusable](/reference-sql/commands/queries/select#reusable-common-table-expressions).
Because reusable is specified explicitly in queries, it has a higher priority than the default query cache size limit.

**Functional dependencies detection**

Our query planner now tracks functional dependencies between columns to produce more efficient execution plans. For example, when an ORDER BY clause contains columns that functionally depend on earlier keys, the optimizer can safely remove redundant sort keys.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT name, surname, name || ' ' || surname as full_name FROM employees ORDER BY name, surname, full_name;
```

In this case, sorting by `name` and `surname` already guarantees the correct order, since `full_name` functionally depends on them, so the optimizer eliminates the redundancy.

Our optimizer also uses uniqueness information for collecting these functional dependencies. In the following example, if `emp_id` is declared as `UNIQUE`, all other columns in `employees` table will functionally depend on `emp_id`, allowing the optimizer eliminate the unnecessary `dept_id` key.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT * FROM employees ORDER BY emp_id, dept_id;
```

Functional dependencies are also leveraged during planning to detect when an intermediate result is already fully sorted. This allows the optimizer to make smarter decisions about plan distribution.

**Hint comments**

Added an additional [hint comment](/performance-and-observability/query-planning/query-hints) that can indicate [unique columns](/performance-and-observability/query-planning/query-hints/unique-columns).
This enables evaluating performance improvements that the planner is able to achieve without having to declare unique constraints [in the ddl](/performance-and-observability/query-planning/unique-constraint).
Enabled using the [`no_partial_agg`](/performance-and-observability/query-planning/query-hints/no-partial-aggregation) hint at the `GROUP BY` level.

**Optimized the processing of right and full outer joins between fact and dimension tables**

Right and full outer joins between fact tables and dimension tables can now be executed in a fully scale-out manner on multi-node engines, resulting in improved performance and more balanced memory usage across the cluster.

**Replaced patterns without wildcards in `LIKE` expressions with string comparisons for improved execution time and pruning efficiency**

Patterns without wildcards in `LIKE` expressions are now replaced by string comparisons. For example, `SELECT TEXT_COL LIKE 'abc' FROM T1` is replaced by `SELECT TEXT_COL = 'abc' FROM T1`. This change improves evaluation efficiency and enables better pruning for these expressions.

**Redundant filter removal**

Our query planner now uses range analysis to remove redundant filter statements, such as in the filter `x > 3 and x > 2`.
It also detects contradictions like `x < 2 and x > 2`, which can achieve significant performance improvements for some queries.

**Evaluate `NOT IN` expressions as an anti-join when compared with the `TRUE` constant using an equality predicate**

`NOT IN` expressions are now being evaluated as an anti-join when compared with the `TRUE` constant using an equality predicate.

### Bug Fixes

**Fixed AWS role ARN credentials being ignored in `LOCATION` objects for Iceberg table queries**

Resolved an issue where AWS role ARN credentials were ignored in `LOCATION` objects for Iceberg table queries. This fix ensures proper authentication when accessing data in Iceberg tables.

**Fixed a bug in handling escaped non-wildcard characters in `LIKE` patterns with trailing wildcards**

A bug was fixed in the handling of escaped non-wildcard characters in LIKE patterns with trailing wildcards. Previously, any backslash in a LIKE pattern was incorrectly assumed to refer to the next wildcard character. This caused issues when the pattern contained only `%` wildcards, and these wildcards were positioned at the end of the pattern. For example, when a column `t1.t` contains the string `'abbcd'`, the query `SELECT t LIKE 'a\\b%c%' FROM t1` would incorrectly return `FALSE`. This fix ensures accurate pattern matching, improving query reliability.

## Firebolt Release Notes - Version 4.27

### New Features

**Enabled copying of results to the clipboard from the export results menu**

Users can now copy results to the clipboard directly from the export results menu. This feature provides a quick and convenient way to transfer data for use in other applications.

**Added support for pattern when using read TVFs with location objects**

The [`READ_PARQUET`](/reference-sql/functions-reference/table-valued/read_parquet), [`READ_CSV`](/reference-sql/functions-reference/table-valued/read_csv) and [`READ_AVRO`](/reference-sql/functions-reference/table-valued/read_avro) functions now additionally accept a pattern when using a location object. Users can refine which files in a specific location are read. For example, use `SELECT * FROM READ_PARQUET(location => 'my_location', pattern => 'testdata/*')` to scan only the files under `testdata/` in location `my_location`.

**Cross-query subresult reuse**

You can now mark a common table expression (CTE) as [`materialized reusable`](/reference-sql/commands/queries/select#reusable-common-table-expressions).
This means that Firebolt keeps the result of the CTE in memory, and using the same reusable CTE in a different query can reuse the cached result for blazing fast performance.
Of course, the caches are fully transactional and get invalidated when the underlying data changes.

**Granular control over the query optimizer**

In addition to the [user-guided optimizer mode](/performance-and-observability/query-planning/user-guided-mode), you can now control the behavior of the query optimizer in a more granular way using hints encoded as special comments in your SQL statements.
By adding the [`/*! no_join_ordering */`](/performance-and-observability/query-planning/query-hints/no-join-ordering) hint, you can instruct the optimizer to always follow the join order specified in the SQL statement.
This can be useful if you have better estimates than the optimizer about the cardinalities of the join inputs.
By adding the [`/*! no_partial_agg */`](/performance-and-observability/query-planning/query-hints/no-partial-aggregation) hint, you can instruct the optimizer to disable partial aggregation for the query.
This can be useful if you already know that the aggregation is unlikely to reduce the cardinality by much.

**Added support for `ALTER COLUMN ... SET UNIQUE` and `DROP UNIQUE` statements for managing column unique constraints**

Support was added for the `ALTER TABLE ... ALTER COLUMN ... SET UNIQUE` and `ALTER TABLE ... ALTER COLUMN ... DROP UNIQUE` statements.\
These commands allow users to modify a column's [unique constraint](/performance-and-observability/query-planning/unique-constraint).

Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER TABLE table_name ALTER COLUMN column_name SET UNIQUE;  -- add unique constraint to a column
ALTER TABLE table_name ALTER COLUMN column_name DROP UNIQUE; -- remove unique constraint from a column
```

This feature does not support modifying fields within inner structure types. Full Documentation is available [here](/reference-sql/commands/data-definition/alter-table#alter-table-alter-column-set-unique).

**Added support for `ALTER TABLE SET ([table_param=<param_value>] ...)`**

You can now change the `COMPRESSION`, `COMPRESSION_LEVEL` and `DESCRIPTION` table parameters.

Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER TABLE table_name SET (COMPRESSION=LZ4);
ALTER TABLE table_name SET (COMPRESSION=ZSTD, COMPRESSION_LEVEL=3);
ALTER TABLE table_name SET (COMPRESSION=ZSTD, COMPRESSION_LEVEL=3, DESCRIPTION='a table with zstd compression');
```

Changed compression settings affect newly ingested data. Full Documentation is available [here](/reference-sql/commands/data-definition/alter-table#alter-table-set).

**Added AWS Glue support to the `READ_ICEBERG` function for querying Iceberg tables with optional Lake Formation integration**

AWS Glue support was added to the [`READ_ICEBERG`](/reference-sql/functions-reference/iceberg/read_iceberg) table-valued function. This allows querying Iceberg tables with AWS Glue as the metadata catalog. Additionally, it offers optional Lake Formation integration. This enhances flexibility and expands integration options for users. To use Glue, set `CATALOG=AWS_GLUE` when creating a location object or simply pass the Glue endpoint as URL.

**Added support for creating struct values using the `STRUCT(...)` function and named fields syntax.**

Added support for creating struct values in SQL statements. Users can use the `STRUCT(...)` function to create struct values with unnamed fields. The syntax `{'field_name_1': value_1, 'field_name_2': value_2}` creates struct values with named fields.

**First release of AI features powered by Amazon Bedrock: call LLMs directly from SQL**

Firebolt now lets you invoke large language models (LLMs) straight from your SQL queries using two new functions:

* [`AWS_BEDROCK_AI_QUERY`](/reference-sql/functions-reference/ai/aws-bedrock-ai-query) invokes an Amazon Bedrock model and returns the raw response payload as a JSON string (`TEXT`). Provide a Bedrock model ID, a serialized JSON request body, and a `LOCATION` with AWS credentials.
* [`AI_QUERY`](/reference-sql/functions-reference/ai/ai-query) sends a simple text prompt to an Amazon Bedrock endpoint and returns the generated text (`TEXT`). Initially, this uses Bedrock as the backend and supports Meta Llama 3.3 70B Instruct; the endpoint must contain `'meta.llama3-3-70b-instruct-v1:0'`.

Examples:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT AWS_BEDROCK_AI_QUERY(
  'amazon.nova-micro-v1:0',
  $${"schemaVersion":"messages-v1","messages":[{"role":"user","content":[{"text":"What is AWS?"}]}],"inferenceConfig":{}}$$,
  'my_bedrock_location'
) AS result;
```

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT AI_QUERY(
  'us.meta.llama3-3-70b-instruct-v1:0',
  'What is AWS?',
  'my_bedrock_location'
) AS result;
```

LLM invocations count toward your account’s daily LLM token budget. You can set the budget and check current usage:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER ACCOUNT "<account_name>" SET (LLM_TOKEN_BUDGET = 10000);
```

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT * FROM account_db.information_schema.quotas;
```

```text theme={"theme":{"light":"github-light","dark":"github-dark"}}
name,units,limit,usage
'LLM_TOKEN_BUDGET',tokens,10000,3500
```

See the [`LLM_TOKEN_BUDGET` quota](/reference-sql/information-schema/quotas) for details on current limits and usage.

For a step-by-step introduction, see the [Getting started with AI guide](/guides/ai/getting-started-with-ai).

### Performance Improvements

**Added Parquet row group pruning**

Filtered Parquet scans now utilize embedded column minimum and maximum metadata to remove row groups that do not meet filter conditions. This can significantly reduce the amount of data scanned and is available for all Parquet scans ([`READ_ICEBERG`](/reference-sql/functions-reference/iceberg/read_iceberg), [`READ_PARQUET`](/reference-sql/functions-reference/table-valued/read_parquet), and external tables). To see whether Parquet pruning is used, consult `EXPLAIN (PHYSICAL)` or `EXPLAIN (ANALYZE)` and look for a `pruning_predicate` in the `read_from_s3` TVF's arguments.

**Added support for join pruning with `UNNEST` on the probe side**

Join pruning can now be applied when data from the probe side is being unnested before the join. This can improve query performance by reducing the amount of data scanned.

**Optimized semi joins with static values to be evaluated as filters**

Semi joins on a single column with static values are now evaluated as filters instead of joins. For instance, the query `SELECT * FROM your_table WHERE some_column IN (SELECT 'filter value 1' UNION ALL SELECT 'filter value 2')` is now processed as a filter. This change can enhance pruning performance.

**Increased the size limit for cached Iceberg metadata**

Increased the size limit for subresults cached by the `MaybeCache` operator, used above `list_iceberg_files` operators. This change allows processing larger Iceberg tables more quickly, as metadata retrieval is skipped for previously processed snapshots.

### Bug Fixes

**Fixed an issue where joins and `IS NOT DISTINCT FROM` on floating-point columns with negative and positive zero values could produce incorrect results when using arrays.**

A rare issue was fixed where a join on arrays of floating-point columns or using `IS NOT DISTINCT FROM` on floating-point columns could produce incorrect results when both negative and positive zero values were present. Previously, a query like `SELECT * FROM lhs, rhs WHERE lhs.a = rhs.a;` would return no rows if `lhs.a` contained `[-0.0]` and `rhs.a` contained `[0.0]`. Now, it correctly returns `{-0}, {0}`.

**Fixed an issue with using AWS role ARNs in LOCATION object for Iceberg queries**

Fixed an issue where Iceberg queries were not using AWS role ARN credentials to access S3 when specified in LOCATION objects.

## Firebolt Release Notes - Version 4.26

### New Features

**Introduced Explicit Transaction support.**

You can now explicitly `BEGIN`, `COMMIT` or `ROLLBACK` transactions using standard SQL syntax. This feature ensures atomicity and snapshot isolation across multiple statements, including DML, DDL, and SELECT.
Particularly useful for customers who require guaranteed consistency across complex transactional operations.

Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
BEGIN [TRANSACTION];
-- multiple SQL statements (e.g. CREATE/DROP/INSERT/UPDATE ...)
COMMIT [TRANSACTION];
```

* For more details on transactions, see the documentation [here](/reference-sql/explicit-transactions).

**Added the `READ_AVRO` table-valued function with full support for all Avro data types**

* Introduced the `READ_AVRO` table-valued function (TVF). This function allows users to efficiently read Avro files.
* Added full support for all Avro data types in the `READ_AVRO` function.

**Added support for `ALTER TABLE ... MODIFY COLUMN ... FIRST|AFTER ...` which enables column reordering in existing table.**

Support was added for the `ALTER TABLE ... MODIFY COLUMN ... FIRST|AFTER ...` DDL statement. This allows users to change the position of columns within a table's schema.

Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER TABLE table_name MODIFY COLUMN column_name FIRST;
ALTER TABLE table_name MODIFY COLUMN column_name AFTER other_col;
```

* The `FIRST` keyword moves the specified column to be the first column in the table.
* The `AFTER` keyword moves the specified column to appear after another specified column.
* This statement is supported only on managed tables.

**Added support for `FILTER` clause in aggregate functions**

The `FILTER` clause enables conditional aggregation - only values for which the condition evaluate to `true` are considered by the aggregate function.

Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT agg_func(args) FILTER(WHERE condition) FROM ...
```

**Added support for UNIQUE column constraint**

Support was added in CREATE TABLE and ALTER TABLE ADD COLUMN to mark columns as unique, i.e. all values are distinct.
This constraint is **not enforced** by the system, it is the user's responsibility to ensure that all values are unique.
Marking a column as unique allows the optimizer to make transformations that produce incorrect results when the contract is violated.

Syntax Examples:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE t(
    id INTEGER NOT NULL UNIQUE
);

ALTER TABLE t ADD COLUMN name TEXT UNIQUE;
```

**Added support for `->>` JSON field extraction operator**

The `->>` operator is used to extract a value from a JSON field.

Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
json_text_column->>'field_name'
```

**Improved the format of `EXPLAIN` output**

The format of `EXPLAIN` output was changed to use meaningful variable names instead of index-based column references, making it easier to read and understand query plans.

Example of explaining TPC-H Q6:

```
[0] [Projection] revenue: (CASE WHEN any_0 THEN sum_0 ELSE NULL END)
|   [Types]: revenue: double precision null
 \_[1] [Aggregate] GroupBy: [] Aggregates: [any_0: any(c_0), sum_0: sum(multiply_checked_0)]
   |   [Types]: any_0: boolean not null, sum_0: double precision not null
    \_[2] [Projection] multiply_checked_0: (lineitem.l_extendedprice * lineitem.l_discount), c_0: TRUE
      |   [Types]: multiply_checked_0: double precision not null, c_0: boolean not null
       \_[3] [Filter] (lineitem.l_shipdate >= DATE '1994-01-01'), (cast(lineitem.l_shipdate as timestamp) < TIMESTAMP '1995-01-01 00:00:00'), (lineitem.l_discount >= 0.05), (lineitem.l_discount <= 0.07), (lineitem.l_quantity < 24)
          \_[4] [StoredTable] Name: "lineitem"
                [Types]: lineitem.l_quantity: double precision not null, lineitem.l_extendedprice: double precision not null, lineitem.l_discount: double precision not null, lineitem.l_shipdate: date not null
```

## Firebolt Release Notes - Version 4.25

### New Features

**Improved type inference in the `READ_CSV()` function**

`READ_CSV()` function enhances data handling by detecting and assigning specific data types such as `BIGINT`, `DOUBLE PRECISION`, and `BOOLEAN` to CSV columns. Previously, the function returned all columns as `TEXT`. The `INFER_SCHEMA` parameter manages this behavior and is true by default, ensuring better data type detection. Set it to false to treat all types as `TEXT`. This improvement allows users to manage and analyze data more effectively, aligning data types with their actual content.

**Added release channel information to the engines table accessible via `SHOW ENGINES` or `information_schema.engines` view**

The engines table now includes release channel information. Users can retrieve this data by using the `SHOW ENGINES` query or by querying `information_schema.engines`. This addition helps users easily access and manage engine release details.

To display all engines with the release channel column included, use the following command:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SHOW ENGINES;
```

To find the release channel of a specific engine, use the following query:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT release_channel FROM information_schema.engines WHERE engine_name = 'myengine';
```

**Added a `PARTITION_ID` field to `VACUUM_JOB` telemetry**

The `VACUUM_JOB` telemetry now includes a `PARTITION_ID` field. This addition helps users better analyze and monitor partition-specific vacuum activities.
Documentation is available [here](/reference-sql/commands/data-management/vacuum).

This enhancement provides users with better visibility and control over their engine configurations, promoting efficient management and planning.

### Performance Improvements

**Improved predicate pushdown support**

Added an optimizer rule that pushes Filter predicates down through Window operators when safe to do so.

**Enhanced the `VACUUM` process by merging tablets from different nodes into a single tablet**

The `VACUUM` process now merges tablets from different nodes into a single tablet. This improvement enhances storage efficiency and optimizes performance in managing data across nodes.

**After the deployment of 4.25, we will migrate engines in the COMPUTE\_OPTIMIZED (CO) family from AMD architecture to ARM**

We expect this to provide a general performance improvement.

## Firebolt Release Notes - Version 4.24

### New Features

**Introduced Organization-Level Role-Based Access Control (ORBAC)**

Firebolt has introduced Organizational-Level Role-Based Access Control (ORBAC), extending traditional account-level RBAC
to provide centralized control over identities and permissions across all accounts in an organization.
ORBAC enables administrators to manage global resources such as accounts, logins, service accounts, network policies,
and organization roles from a unified model.

Key highlights include:

* **Global Scope**: ORBAC operates across the entire organization rather than being limited to individual accounts.
* **Strict Deny-by-Default**: No access is granted by default; all permissions must be explicitly assigned via Organization Roles.
* **New System Role**: The built-in `org_admin` role is granted all organization-level privileges by default.
* **New Information Schema Views**: Added views like `enabled_roles`, `applicable_roles`, `transitive_applicable_roles`,
  and `object_privileges` under `org_db.information_schema` for visibility into role assignments and access.

Example usage:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
-- Create a new organization role
CREATE ORGANIZATION ROLE account_manager_role;

-- Grant permission to manage all accounts
GRANT MODIFY ANY ACCOUNT TO ORGANIZATION ROLE account_manager_role;

-- Assign role to a login
GRANT ORGANIZATION ROLE account_manager_role TO LOGIN "john@acme.com";
```

ORBAC provides enterprise-grade governance for managing organizational access, complementing existing account-level RBAC.
It is ideal for administrators managing multiple Firebolt accounts and seeking consistent, scalable permission models.

**Added support for PostgreSQL `INSERT ON CONFLICT` syntax**
The optional `ON CONFLICT` extension to the [`INSERT`](/reference-sql/commands/data-management/insert) statement provides a way to reconcile or merge new data with existing records by either skipping or overwriting rows that already exist. It is available for a narrow subset of `INSERT` statements. For more generic UPSERT or deduplication functionality, use the [`MERGE`](/reference-sql/commands/data-management/merge) statement.

Example usage:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
-- Insert and update if conflict occurs on column 'did'
INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

-- Insert and ignore if conflict occurs on column 'did'
INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')
    ON CONFLICT (did) DO NOTHING;
```

Documentation is available [here](/reference-sql/commands/data-management/insert#insert-on-conflict).

**Added background warmup feature to engines**

Added a feature that allows engines to proactively fetch recently used data at startup. While engines are running,
they will now periodically record recently read files, and when engines start, they will proactively fetch these files
from s3.

This feature can be enabled / disabled in SQL. For example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
-- Enable auto_warmup on myengine
ALTER ENGINE myengine SET AUTO_WARMUP = true;

-- Disable auto_warmup on myengine
ALTER ENGINE myengine SET AUTO_WARMUP = false;
```

**Added release channel configuration to engines**

Added a feature that allows to configure per-engine release settings, in particular, to specify release a channel that controls whether the engine gets upgraded normally or can get an earlier preview version. Available values are: `DEFAULT` or `PREVIEW` to explicitly allow early upgrades.

Examples:

1. Create a new engine and assign it to the preview channel:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE ENGINE myengine WITH RELEASE = (CHANNEL = PREVIEW);
```

2. Update the engine to assign it back to the default channel:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER ENGINE myengine SET RELEASE TO (CHANNEL = DEFAULT);
```

**Updated `ALTER ENGINE ... SET` syntax**

Syntax of configuration parameters assignment is aligned with PostgreSQL and allows to assign values
using `TO` keyword as an equivalent of equal sign.

For example to change auto stop configuration:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
ALTER ENGINE myengine SET AUTO_STOP TO 30;
```

**Introduced new ingestion related setting**

Introduced `max_insert_threads` to [control the maximum number of threads](/reference-sql/system-settings#limiting-max-insert-threads) for `INSERT` statements, limiting the degree of parallelism for tablet writing operations.
This can reduce memory footprint during ingestion.

**Output of EXPLAIN**

* The output for accessing an aggregating index via `TABLESCAN` is now represented as "@{idx_name_0}" instead of "{idx_name_0}\_\_AGG\_IDX\_TABLE".
* Included `GRANULES` in `EXPLAIN ANALYZE` output. This provides users with deeper insights into query performance and helps optimize indexing strategies.

**Added telemetry data to the `telemetry` column in `information_schema.engine_query_history`**
The `telemetry` column in `information_schema.engine_query_history` now shows telemetry data about query execution. This enhancement helps users to better analyze the performance of their queries.

**Added the `peak_memory_bytes` column to the `information_schema.engine_query_history` table to monitor query memory usage**
The `information_schema.engine_query_history` table now contains the `peak_memory_bytes` column, which provides details on query memory usage. This addition helps users monitor and optimize query performance.

### Performance Improvements

**Enhanced the `VACUUM` statement to merge tablets from different nodes into a single tablet**

The `VACUUM` statement now merges tablets from different nodes into a single tablet. This enhancement improves data management efficiency and optimizes storage space.

**Enabled filter expressions to be pushed into `PREWHERE` above joins with configuration for primary index column usage in `PREWHERE`**
Firebolt now allows filter expressions to be pushed down into `PREWHERE` above joins. By default, expressions on the first primary index column are not chosen for `PREWHERE`, as they are used for granule pruning.

A new setting, `ALLOW_PREWHERE_ON_FIRST_PRIMARY_INDEX_COL`, lets the optimizer select predicates on the first primary index column for `PREWHERE`. This change can improve performance when the first primary index column has high cardinality and the query accesses additional wide columns.

### Connectors

**Added support for Pandas**

Firebolt now supports [Pandas](https://pandas.pydata.org), allowing to interact with Firebolt data using Pandas DataFrames. A guide on how to use Pandas with Firebolt is available [here](/guides/integrations/pandas).

**Added support for Tableau Prep**

Firebolt now supports [Tableau Prep](https://www.tableau.com/products/prep), allowing to prepare and clean data before analysis. A guide on how to use Tableau Prep with Firebolt is available [here](/guides/integrations/tableau).

## Firebolt Release Notes - Version 4.23

### New Features

**Added `MERGE` SQL Statement**

Added support for `MERGE` SQL Statement, which allows users to perform multiple data modifications (`INSERT`, `UPDATE`, `DELETE`) within a single transaction. `MERGE` is useful for common database tasks like removing duplicates, syncing data between tables (upsert), or cleaning out old records. Documentation is available [here](/reference-sql/commands/data-management/merge).

**Added `COMMENT ON` statement**

Added support for the COMMENT ON statement, which allows users to add or update descriptions for objects such as engine, location, database, table, and column.

**Added the `REVERSE(TEXT)` function**

Added the `REVERSE(TEXT)` function which returns the input string with all characters in reverse order. Documentation for the function is available [here](/reference-sql/functions-reference/string/reverse).

**Added the `SOUNDEX(TEXT)` function**

Added the `SOUNDEX(TEXT)` function which returns the phonetic sound of an input string, allowing comparison of words that sound similar but are spelled differently. Documentation for the function is available [here](/reference-sql/functions-reference/string/soundex).

**Added support for cross-region Amazon S3 access**

Added the ability to ingest and export data to Amazon S3 buckets located in different regions from your Firebolt engines. This update enhances flexibility in data management for users with geographically distributed data. Cross-region access is disabled by default and can be enabled on a per-query basis using the `cross_region_request_mode` setting.

**Added granule-level pruning information to `EXPLAIN ANALYZE` via `PRIMARY_INDEX_PRUNED_GRANULES` and `PRIMARY_INDEX_TOTAL_GRANULES` fields**

Added pruning information at the granule level to `EXPLAIN ANALYZE` through the fields `PRIMARY_INDEX_PRUNED_GRANULES` and `PRIMARY_INDEX_TOTAL_GRANULES`. This provides insights into query optimization by displaying how effectively the primary index reduces data during query execution.

**Added an `optimizer_mode` setting.**

Added a new setting that allows users to control the amount of work that the optimizer attempts to do.
Documentation for the setting is available [here](/reference-sql/system-settings#changing-the-optimizer-mode).

**Added an `enable_storage_statistics` setting.**

Added a new setting that allows users to control whether statistics information obtained from storage metadata is used for cost-based decisions made by the query optimizer.
Documentation for the setting is available [here](/reference-sql/system-settings#using-storage-manager-statistics-in-the-optimizer).

## Firebolt Release Notes - Version 4.22

### New Features

**Support for Iceberg is now in public preview**

Added the `READ_ICEBERG()` table-valued function, which allows reading from external Iceberg tables, and added Iceberg support to `LOCATION` with `SOURCE = ICEBERG`.
Documentation for the function is available [here](/reference-sql/functions-reference/iceberg/read_iceberg) and documentation for the new type of location [here](/reference-sql/commands/data-definition/create-location-iceberg).

**Added the `ICU_NORMALIZE` function to standardize text formats across locales**

Added the `ICU_NORMALIZE` function to process text based on a specific locale. This helps in standardizing text formats across different languages and regions, ensuring uniformity and compatibility in data outputs.
Documentation for the function is available [here](/reference-sql/functions-reference/string/icu_normalize)

**Added the `AGO(interval)` function for subtracting intervals from the current timestamp.**

Added the `AGO(interval)` function, which subtracts the specified interval from the current timestamp. This addition provides users with a convenient way to calculate past dates and times, enhancing time-based data analysis.
Documentation for the function is available [here](/reference-sql/functions-reference/date-and-time/ago).

**Added a named parameter `INFER_SCHEMA` to the `READ_CSV` function**

Added a named parameter `INFER_SCHEMA` to the [READ\_CSV function](/reference-sql/functions-reference/table-valued/read_csv). When `INFER_SCHEMA` is true, the function determines column data types instead of using `TEXT`.

**Extended Parquet data type support**

Added support for reading the following data types from Parquet files:

* `fixed_size_binary` as [BYTEA](/reference-sql/data-types/bytea)
* `uuid` as [BYTEA](/reference-sql/data-types/bytea)
* `time32` and `time64` as [TIMESTAMP](/reference-sql/data-types/timestampntz) with `1970-01-01` as the date component. Note that `time64` with nanosecond precision is truncated to microsecond precision.

**Introduced new ingestion related settings**
Introduced `insert_sharding` to [enforce partition locality](/reference-sql/system-settings#insert-sharding) during ingestion into partitioned tables.
Introduced `tablet_min_size_bytes` and `tablet_max_size_bytes` to [control](/reference-sql/system-settings#target-tablet-size) min/max tablet sizes during ingestion.

**Added an ability to override public settings per query**
You can now override settings by appending `WITH (<setting_1_name> = <setting_1_value>, ...)` to queries.
This lets you apply settings directly to specific queries without affecting the entire session.
Documentation is available [here](/reference-sql/system-settings#setting-via-with).

### Performance Improvements

**Rearchitected the Parquet reader for predictable memory usage and improved performance with external tables and `READ_PARQUET` function.**
The Parquet reader was rearchitected to provide more predictable memory usage when reading from external tables or using the `READ_PARQUET` table-valued function. This change improves performance for many Parquet workloads. Users benefit from enhanced memory efficiency and faster query processing. More updates, like applying these changes to the `COPY FROM` command, are planned for future releases.
This improvement is being rolled out gradually over multiple weeks.

### UX changes

**Introduced a new flow for managing service accounts with enhanced security via improved secret rotation and user associations in any organization account**

Introduced a new flow for creating and altering service accounts that enables user associations in any organization account. This improvement simplifies account management and enhances security through an improved process for rotating secrets.

### Bug Fixes

**Fixed incorrect results when using `UNION ALL` with overlapping aggregation or join keys followed by further aggregation or joining**

Fixed a bug that could cause incorrect results on multi-node engines when doing a UNION ALL over subqueries that have overlapping but separate aggregation or join keys, and then later on aggregating by or joining on these keys.

## Firebolt Release Notes - Version 4.21

### New Features

**Enabled renaming of databases with the `ALTER DATABASE <existing name> RENAME TO <new name>` command**

Users can now [rename databases](/reference-sql/commands/data-definition/alter-database#alter-database-rename-to) using the `ALTER DATABASE <existing name> RENAME TO <new name>` command. This update provides greater flexibility in managing database names, allowing for easier organization and management.

**Expanded the `information_schema.engine_query_history` table with `NODE_ORDINAL`, `CLUSTER_ORDINAL`, and `NUMBER_OF_CLUSTERS` columns to enhance query distribution insights and engine scaling behavior analysis**

The [information\_schema.engine\_query\_history](/reference-sql/information-schema/engine-query-history) table now includes three new columns: `NODE_ORDINAL`, `CLUSTER_ORDINAL`, and `NUMBER_OF_CLUSTERS`. `NODE_ORDINAL` identifies the node that received the query, `CLUSTER_ORDINAL` indicates the engine cluster that handled the query, and `NUMBER_OF_CLUSTERS` shows the number of clusters active during the query.

These additions help users understand query distribution across clusters and provide insights into engine scaling behavior, such as automatic upscaling and downscaling.

**Add preliminary support for `STRUCT` data type**

Added preliminary support for `STRUCT` data type. This feature is in public preview. We are gathering feedback and further refining this feature. For details, please refer to the [Firebolt SQL reference](/reference-sql/data-types/struct).

**Added `evicted_bytes` to `information_schema.engine_metrics_history`**

Added a new metric that tracks the total amount of data (in bytes) that has been evicted from disk.
This metric helps users monitor disk space management and understand how much data is being evicted from the cache.
For details, please refer to the [Information schema for engine metrics history](/reference-sql/information-schema/engine-metrics-history).

### Performance Improvements

**Enhanced REGEXP\_LIKE\_ANY performance**

We have significantly improved the performance of `REGEXP_LIKE_ANY` by up to 10x when the pattern list (`<pattern>`) contains no regular expression metacharacters.
By intelligently detecting these cases, we now leverage **[Hyperscan](https://github.com/intel/hyperscan)**, a high-performance regex engine, in combination with the **Volnitsky text search algorithm** to deliver substantial speedups in text matching workloads.

## Firebolt Release Notes - Version 4.20

### New Features

**Introduced the [ARRAYS\_OVERLAP](/reference-sql/functions-reference/array/arrays-overlap) function to identify shared elements in input arrays**\
Introduced the `ARRAYS_OVERLAP` function to determine if two or more input arrays share common elements. This enhancement enables users to identify overlapping array elements, improving data analysis capabilities.

**Added the [NGRAM](/reference-sql/functions-reference/string/ngram) function to generate overlapping n-grams from text**\
Added a new scalar function: `NGRAM`. The function `NGRAM(n, text)` generates a sequence of overlapping n-grams from the given text. Each n-gram is a contiguous substring of n characters. It returns the result as an SQL array of text. This function is useful for text analysis and can help in search queries.

**Example**:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT ngram(4, 'abc😊def');
```

**Results**:

```
{abc😊,bc😊d,c😊de,😊def}
```

**Added support for [VALUES](/reference-sql/commands/queries/select#values-lists) lists to create constant tables**\
Added support for `VALUES` lists to create an in-memory constant table with one or multiple rows for use in queries.
For example:

```
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num, letter);
```

is effectively equivalent to:

```
SELECT 1 AS num, 'one' AS letter
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
```

Syntactically, `VALUES` can be used anywhere a `SELECT` is allowed.

**Added support for `$$` to quote multiline string literals containing newlines and single quotes**\
Added support for `$$` to quote string literals that contain newlines and single quotes. This enhancement helps users create multiline strings without needing to escape special characters, making SQL scripts easier to read and maintain. For example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT $$
That's a 
   multi-line
     string
       literal
$$
```

### Performance Improvements

**Improved Aggregating index scan performance by eliminating unnecessary re-aggregation**\
The Aggregating index scan now detects when data is already fully aggregated. It directly projects results without re-aggregating. This improvement reduces query latency and increases performance for applicable workloads.

**Enhanced schema discovery performance for Parquet files in `COPY FROM` and `READ_PARQUET()` functions**\
Enhanced the performance of [Automatic Schema Discovery](/reference-sql/commands/data-management/copy-from#automatic-schema-discovery) for Parquet files in the `COPY FROM` command and the `READ_PARQUET()` function. This improvement accelerates data loading and processing for users.

### Bug Fixes

**Fixed the `information_schema.tables.ddl` output for tables using `TO_...` family of functions in the partition expression**\
Fixed the `information_schema.tables.ddl` output for tables with a partition expression such as `TO_YYYYMMDD`. This update ensures that the expression no longer incorrectly results in the `TO_YEARMONTHDAY` function.

## Firebolt Release Notes - Version 4.19

### New Features

**Added support for [GROUPING SETS](/reference-sql/commands/queries/select#group-by-grouping-sets), [ROLLUP](/reference-sql/commands/queries/select#group-by-rollup) and [CUBE](/reference-sql/commands/queries/select#group-by-cube) clauses**\
Expanded SQL support with the addition of `GROUPING SETS`, `ROLLUP` and `CUBE` clauses for `GROUP BY` operations. These clauses enable more flexible and efficient multi-level aggregations in a single query, simplifying complex reporting and analytics workflows.

* `GROUPING SETS`: Specify multiple groupings in a single query.
* `ROLLUP`: Create subtotals that roll up from the most detailed level to a grand total.
* `CUBE`: Generate subtotals for all combinations of a set of columns.
  These enhancements unlock more powerful data summarization directly within SQL.

**Added `COVAR_POP`, `COVAR_SAMP` and `CORR` functions**\
Added support for advanced statistical analysis directly in SQL with the introduction of the following aggregate functions:

* [COVAR\_POP](/reference-sql/functions-reference/aggregation/covar-pop) : Calculates the population covariance between two sets of values.
* [COVAR\_SAMP](/reference-sql/functions-reference/aggregation/covar-samp) : Computes the sample covariance.
* [CORR](/reference-sql/functions-reference/aggregation/corr) : Returns the Pearson correlation coefficient.
  These functions enable deeper insight into relationships between variables, making it easier to perform in-database analytics without external tools.

**Added [ARRAY\_INTERSECT](/reference-sql/functions-reference/array/array-intersect) function to find common elements in arrays**\
Added a new function `ARRAY_INTERSECT`, which finds common elements across given arrays. This functionality simplifies operations that require comparing multiple arrays to identify shared items, enhancing data analysis capabilities.

**Added the ability to attach a certificate's public key to a service account using ALTER SERVICE ACCOUNT.**

**Added the ability to control an engine's Auto VACUUM behavior**\
`CREATE ENGINE` and `ALTER ENGINE` statements now support an `AUTO_VACUUM` parameter.

**Added the ability to revoke a privilege for a specific object, even if the privilege is inherited via an ANY-privilege from its parent object.**\
We've added the ability to revoke a privilege for a specific object, even when that privilege is inherited through an ANY-privilege granted to the parent object. For example, if a `SELECT ANY` privilege is granted on a schema, it is now possible to revoke the `SELECT` privilege for a specific table within that schema.

### Performance Improvements

**Reduced memory usage for the `COPY TO` function with `SINGLE_FILE=true`**\
Memory usage was reduced when using the `COPY TO` function with `SINGLE_FILE=true`. This change optimizes performance and resource efficiency, especially for users dealing with large datasets.

**Enabled `RESULT` and `SUBRESULT` cache to reuse query results between user interface and JDBC connector**\
The `RESULT` and `SUBRESULT` cache now reuses query results between those sent from the user interface and those sent from the JDBC connector. This improves performance by reducing redundant computations.

**Rearchitected the Parquet reader for more predictable memory usage with external tables and the `READ_PARQUET` function**\
We have significantly rearchitected our parquet reader. This should lead to more predictable memory usage when reading from external tables or using the `READ_PARQUET` function and improve performance for many parquet workloads. More improvements, including bringing these changes to `COPY FROM`, will follow in upcoming releases.

**Improved join pruning to support more join types and pruning through window operators**\
Join pruning now supports almost any join between the table scan being pruned and the probe side of the join where pruning is applied. It now also supports pruning through window operators when the column used for pruning is included in all windows' `PARTITION BY` clause. This enhancement improves query performance by reducing unnecessary data processing.

### Behavior Changes

**Updated how ANY privileges are displayed in [information\_schema.object\_privileges](/reference-sql/information-schema/object-privileges)**\
The representation of privileges in the `information_schema.object_privileges` has been updated so that all objects privileges granted to the user are explicitly enumerated.
So `usage any database on account` privilege will be expanded to:

* usage any database on account
* usage database db1
* usage database db2 ...

## Firebolt Release Notes - Version 4.18

### New Features

**Users can now ALTER their corresponding USER object without administrative or RBAC permissions**<br />
Users can now [ALTER](/reference-sql/commands/access-control/alter-user) their corresponding [USER](/overview/organizations-accounts#users) object and change its properties without needing role-based access control permissions ([RBAC](/overview/security/rbac)). This enhancement simplifies user self-management by reducing the dependency on administrative permissions. Restrictions remain for sensitive properties including [logins or service accounts](/overview/organizations-accounts#organizations), which require higher-level permissions.

**Use a LOCATION object to store credentials for authentication**<br />
You can now use [CREATE LOCATION](/reference-sql/commands/data-definition/create-location) to create a `LOCATION` object in your Firebolt account. Use `LOCATION` to store credentials and authenticate to external systems without needing to provide static credentials each time you run a query or create a table. `LOCATION` works with ([RBAC](/overview/security/rbac)) so you can manage permissions securely. You can view detailed information about your locations including source type, URL, description, owner, and creation time in [information\_schema.locations](/reference-sql/information-schema/locations).

**Added creation timestamps for tables, views, indexes, and locations**<br />
Use creation timestamps in `information_schema` views for [tables](/reference-sql/information-schema/tables), [views](/reference-sql/information-schema/views), [indexes](/reference-sql/information-schema/indexes), and [locations](/reference-sql/information-schema/locations) to help track objects for data management.

**Added support for SQL pipe syntax**\
Firebolt now supports [SQL Pipe syntax](/guides/sql-dialect/sql-pipe-syntax), an alternative way to structure SQL queries using the `|>` operator. This syntax allows for a linear, step-by-step flow of query transformations, improving readability and simplifying query composition. It supports all standard SQL operations and can be combined with traditional SQL syntax.

**Added wildcard character functionality to `READ_PARQUET` and `READ_CSV` to simultaneously read multiple files**<br />
You can use wildcard characters such as `*` or `?` to specify a file URL as a [glob pattern](https://en.wikipedia.org/wiki/Glob_\(programming\)) in the [READ\_PARQUET](/reference-sql/functions-reference/table-valued/read_parquet) and [READ\_CSV](/reference-sql/functions-reference/table-valued/read_csv) table-valued functions to read multiple files simultaneously. This enhancement simplifies managing large datasets by reducing the need to make multiple function calls.

**Added functionality to transfer ownership of objects in the Firebolt Workspace**<br />
You can now [transfer ownership](/guides/security/ownership#managing-ownership-in-the-firebolt-workspace) of Firebolt objects through the **Firebolt Workspace** user interface (UI). You can transfer ownership of individual objects or bulk transfer owned by a specific user. You can also delete individual objects or in bulk, helping to simplify the management of object ownership within the UI.

### Performance Improvements

**Enabled result and subresult caching for queries with window functions**<br />
Enabled [result and subresult caching](/overview/queries/understand-query-performance-subresult) for queries that contain [window functions](/reference-sql/functions-reference/window), which can reduce query runtimes by storing previous results and enhance overall query performance and efficiency.

### Bug Fixes

**Fixed an issue where `CREATE VIEW` statements did not preserve the order of named function parameters**<br />
An issue was resolved where [CREATE VIEW](/reference-sql/commands/data-definition/create-view) statements did not maintain the correct order of named function parameters, which could lead to syntax errors when querying the view. This fix improves query reliability by ensuring the proper order of function parameters.

## Firebolt Release Notes - Version 4.17

### New Features

**Introduced the `IF` function to enhance query readability and simplify conditional expressions**\
The new [`IF`](/reference-sql/functions-reference/conditional-and-miscellaneous/if) function simplifies query writing as a more concise alternative to the `CASE WHEN` expression.\
You can now use
`IF(<cond_expr>, <then_expr>, <else_expr>)`
as a shorter equivalent to
`CASE WHEN <cond_expr> THEN <then_expr> ELSE <else_expr> END`.

**Added `INCREMENTAL` index optimization with `VACUUM`**\
The [`VACUUM`](/reference-sql/commands/data-management/vacuum) statement now supports an `INDEXES = INCREMENTAL` option, allowing incremental optimization of related indexes. This new mode uses fewer resources compared to a full reevaluation, improving index layouts. Although incremental optimization may not achieve the optimal layout of a full reevaluation, it maintains a balance between performance and resource usage.

**Added `MAX_CONCURRENCY` option to `VACUUM` statement**\
The `VACUUM` command now supports the `MAX_CONCURRENCY` option, enabling you to limit concurrent processes during optimization. This allows for control of the number of concurrent processes in a `VACUUM` operation, optimizing resource usage and improving performance in multi-threaded environments.

**Added longitude wrapping for `GEOGRAPHY` data**\
Firebolt now automatically wraps longitude values outside the range of -180 to 180 degrees when parsing `GEOGRAPHY` data from WKT, GeoJSON, WKB, or using the `ST_GeogPoint` function. For example, `POINT(180.5 1)` is now correctly interpreted as `POINT(-179.5 1)`. This improvement simplifies geographic data handling.

**Enhanced the `EXPLAIN` function to support all SQL statements except for DDL and DCL**\
The [`EXPLAIN`](/reference-sql/commands/queries/explain) feature now supports analysis of all SQL statements. However, it does not provide output details for DDL (Data Definition Language) and DCL (Data Control Language) statements.

### Performance Improvements

**Optimized `COPY FROM` filtering performance**\
Filters applied to pseudo columns, such as `$SOURCE_FILE_NAME` and `$SOURCE_FILE_TIMESTAMP`, are now pushed down to the file listing during the `COPY FROM` process when using multiple URL and pattern locations. This enhancement improves performance by reducing unnecessary data processing and speeds up data loading operations.

### Bug Fixes

**Fixed latitude handling for `LineString` in WKT**\
Fixed an issue where latitudes outside the valid range of -90 to 90 degrees, in `LineString` data were incorrectly accepted when parsing from WKT. For example, `LINESTRING(0.5 1, 1 90.5)` now correctly returns an error instead of being interpreted as `LINESTRING(0.5 1, -179 89.5)`. This fix enhances data integrity and prevents erroneous geographic entries.

## Firebolt Release Notes - Version 4.16

### New Features

**Added `MAX_CONCURRENCY` option to the `VACUUM` statement for enhanced concurrency control**\
The [VACUUM](/reference-sql/commands/data-management/vacuum) statement now includes the `MAX_CONCURRENCY` option, allowing users to limit the number of concurrent streams. This improves control over resource usage during `VACUUM` operations.

**Introduced the `INDEXES = ALL | NONE` for the `VACUUM` statement**\
The [VACUUM](/reference-sql/commands/data-management/vacuum) statement now supports the `INDEXES = ALL | NONE` option, giving users control over whether indexes are optimized during `VACUUM` operations.

**`VACUUM` now runs automatically**<br />
Firebolt now automatically evaluates the data layout of tables and runs [VACUUM](/reference-sql/commands/data-management/vacuum) to optimize performance and storage efficiency. After [INSERT](/reference-sql/commands/data-management/insert), [UPDATE](/reference-sql/commands/data-management/update), or [DELETE](/reference-sql/commands/data-management/delete) operations modify data, the engine that performed the operation determines whether `VACUUM` is required. This decision is based on factors such as the number of deleted rows and the need to consolidate storage for faster query performance and reduced disk space usage.

**Added support for casting text literals to interval literals**\
Firebolt now supports casting text literals to interval literals using expressions like `'1 month'::INTERVAL`, making it easier to define time intervals in queries.

**Added default value support for `GEOGRAPHY` columns**\
Firebolt now supports default values for columns with the [GEOGRAPHY](/reference-sql/data-types/geography#geography-data-type) data type. For example, `CREATE TABLE geo_table (geo_column GEOGRAPHY DEFAULT 'GEOMETRYCOLLECTION EMPTY')` ensures consistency across database entries when no explicit value is provided.

**Added `MIN_CLUSTERS` and `MAX_CLUSTERS` columns to `INFORMATION_SCHEMA.ENGINES`**\
The [INFORMATION\_SCHEMA.ENGINES](/reference-sql/information-schema/engines) table now includes `MIN_CLUSTERS` and `MAX_CLUSTERS` columns, providing visibility into cluster configuration for improved database management.

**Added support for `STATEMENT_TIMEOUT` to manage query run time limits**\
Added support for `STATEMENT_TIMEOUT`. This feature specifies the number of milliseconds a statement is allowed to run. Any statement or query exceeding the specified time is canceled. A value of zero disables the timeout by default. Using `STATEMENT_TIMEOUT` helps prevent excessively long-running queries, improving system efficiency and resource use.

**Added the PostgreSQL function `DATE(<arg>)` as an alternative to `<arg>::DATE`**\
Firebolt now supports the `DATE(<arg>)` function, offering an alternative to the `<arg>::DATE` syntax for improved readability and usability in SQL queries.

**Added support for `FROM` first syntax**\
SQL queries can now use `FROM` before `SELECT`, allowing for more flexible query structures such as `FROM t SELECT a, SUM(b) GROUP BY a` or even `FROM t` without a `SELECT` clause.

**Support for AWS PrivateLink is now in public preview**\
[Firebolt now supports AWS PrivateLink](/guides/security/privatelink), allowing Firebolt Enterprise customers to securely access the Firebolt API without exposing traffic to the public internet. AWS PrivateLink enhances security, minimizes data exposure, and improves network reliability by keeping traffic within AWS.

**Added concurrency auto-scaling**\
Engines can now be created with concurrency auto-scaling enabled, or modified to enable concurrency auto-scaling. Setting the `MIN_CLUSTERS` and `MAX_CLUSTERS` parameters on [CREATE ENGINE](/reference-sql/commands/engines/create-engine) and [ALTER ENGINE](/reference-sql/commands/engines/alter-engine) commands turns on concurrency auto-scaling: the engine will dynamically resize between the specified `MIN_CLUSTERS` and `MAX_CLUSTERS` values to match demand.

**Firebolt introduces three fully managed editions**

Firebolt now offers **Standard, Enterprise, and Dedicated editions**, each designed for different capabilities, security, and scalability needs.

* **Standard**: High-performance, elastic scaling – in and out, up and down – for cost-efficient, fully managed analytics on a single cluster.
* **Enterprise & Dedicated**: Includes scaling capabilities like **multi-cluster scaling**, as well as advanced security features like **AWS PrivateLink**.
* **Dedicated**: Built for regulated industries (finance, healthcare) with **single-tenant infrastructure** and compliance with **HIPAA, SOC 2, ISO**.

Enterprise and Dedicated customers also get **24/7 support** with **faster support response times**, **Slack-based support**, and support from a **designated engineer**. For more information on Firebolt's editions, refer to the [Pricing and billing](/overview/billing) page.

### Performance Improvements

**Introduced pruning for `GEOGRAPHY` columns at the tablet level to enhance query performance**\
Firebolt now prunes [GEOGRAPHY](/reference-sql/data-types/geography#geography-data-type) data at the tablet level to enhance query performance. To activate spatial pruning on tables created before this release, run `VACUUM`. For additional details, see our [blog post](https://www.firebolt.io/blog/architecture-and-internal-representation-of-the-geography-data-type).

**Added `INDEX_GRANULARITY` storage parameter to optimize table storage**\
The `CREATE TABLE` statement now supports the `INDEX_GRANULARITY` storage parameter, allowing users to configure internal tablet range sizes for better performance based on query patterns.

### Bug Fixes

**Fixed permission conflicts on public schemas across multiple databases**\
Resolved an issue where granting identical permissions on public schemas in different databases caused conflicts. This fix ensures correct permission application for improved database security.

## Firebolt Release Notes - Version 4.15

### New Features

**Improved `EXPLAIN (STATISTICS)` to include estimated row counts and column distinct counts**

The [EXPLAIN (STATISTICS)](/reference-sql/commands/queries/explain) function now provides estimated row counts and column distinct counts, when available. This enhancement offers more detailed insights for analyzing query performance.

**Added a Tableau connector for the current version of Firebolt**

[Tableau](https://www.tableau.com/) is a visual analytics platform that empowers users to explore, analyze, and present data through interactive visualizations. The current Firebolt connector in Tableau Exchange supports only an older version of Firebolt. You can now download the latest connector directly from Firebolt and integrate it with [Tableau Desktop](https://www.tableau.com/products/desktop) or [Tableau Server](https://www.tableau.com/products/server). Follow the installation instructions in [Integrate with Tableau](/guides/integrations/tableau) to set up the updated connector.

**Added a DBeaver connector for the current version of Firebolt**

[DBeaver](https://dbeaver.io/) is a free, open-source database administration tool that supports multiple database types, provides a graphical interface for managing databases, running queries, and analyzing data. You can now connect to DBeaver using the [Firebolt JDBC driver](https://docs.firebolt.io/Guides/developing-with-firebolt/connecting-with-jdbc.html). Follow the instructions in [Integrate with DBeaver](/guides/integrations/dbeaver) to set up a connection to DBeaver.

**Added the Firebolt Resource Center to the Firebolt Workspace**

The [Firebolt Resource Center](https://www.firebolt.io/resources) is now accessible from the **Firebolt Workspace**. Select the Firebolt icon in the bottom-right corner to access resources including the [Get started guide](/guides/getting-started), [Knowledge Center](https://www.firebolt.io/knowledge-center), [Documentation](https://docs.firebolt.io/), [Release notes](/reference/release-notes), Announcements, and a unified search tool covering all Firebolt resources.

### Performance Improvements

**Optimized `LEFT JOIN` conversion for better query performance**

A nested `LEFT JOIN` can now be automatically replaced with a more efficient join when its results are not needed due to filtering in a later step. This optimization occurs when a `LEFT JOIN` removes rows where the right-hand side contains `NULL` values, effectively discarding the extra rows introduced by the earlier `LEFT JOIN`.
In such cases, simplifying the join structure improves efficiency without altering query results. This conversion reduces unnecessary operations, lowering computational overhead and enhancing performance.

**Improved performance by allowing multiple `INSERT INTO <tbl> VALUES ...` statements to be combined in a single request**

Workloads that send multiple consecutive `INSERT INTO <tbl> VALUES ...` statements into the same table can now run much faster by sending all statements in a single request separated by semicolons. These statements are now automatically merged and processed together on the server within a single transaction, which means that either all of them succeed or fail. This improvement reduces network overhead and enhances performance for batch data insertion.

### Behavior Changes

**Use `NULL` instead of empty strings for passing unset TVF parameters**

Table-valued functions (TVFs) such as [LIST\_OBJECTS](/reference-sql/functions-reference/table-valued/list-objects), [READ\_PARQUET](/reference-sql/functions-reference/table-valued/read_parquet), and [READ\_CSV](/reference-sql/functions-reference/table-valued/read_csv) that accept string named parameters like `aws_access_key_id` and `aws_role_arn` will no longer treat empty strings (`''`) as unset arguments. The empty strings will instead be forwarded to the credential provider and may return errors. If you want to pass an explicitly unset parameter, use `NULL` instead.

### Bug Fixes

**Resolved issue in distributed `GROUP BY` and `JOIN` planning**

Resolved a bug in the optimization process for distributed `GROUP BY` and `JOIN` operators. This bug sometimes led to missed optimization opportunities and, in rare cases, incorrect results.

**Fixed a bug in correlated `EXISTS` subqueries that caused duplicated outer tuples in query results**

Fixed a bug with non-trivial correlated `EXISTS` subquery, which is a dependent subquery inside an `EXISTS` condition that references a column from an outer query. An example of this kind of query follows:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT *,
  EXISTS(SELECT 1 FROM table2 where COALESCE(table2.col_1, table2.col_2) = table1.col_1)
FROM table1
```

Previously, if an outer table contained a value, and the inner table had two matching values, the outer table's row would appear twice in the final result instead of just once. This happened because the query checked for matches individually for each row in the inner table, rather than treating the condition as a simple existence check.

This bug fix corrected this issue by ensuring that the `EXISTS` condition only determines whether at least one match exists, without duplicating rows in the outer table. Now, each row in the outer table correctly appears once, with `TRUE` if a match exists and `FALSE` otherwise, improving the accuracy of query results.

## Firebolt Release Notes - Version 4.14

### New Features

**Added `E2E_DURATION_US` to include total query time in Firebolt infrastructure for enhanced performance monitoring and optimization**

Added a new column `E2E_DURATION_US` in the system tables `INFORMATION_SCHEMA.ENGINE_RUNNING_QUERIES`, `INFORMATION_SCHEMA.ENGINE_QUERY_HISTORY`, and `INFORMATION_SCHEMA.ENGINE_USER_QUERY_HISTORY` which shows the total time a query has spent within the Firebolt infrastructure. In contrast, `DURATION_US` measures only the time spent using the engine without considering retries or routing. The `E2E_DURATION_US` metric measures the total time a query takes from initiation to final result delivery, and includes all sub-components of latency such as routing, preparation, queuing, compilation, retries, and runtimes. For example, if a query starts a stopped engine, the engine's startup time is included in the query's end-to-end duration. This update provides a more accurate representation of total query latency, for performance monitoring and optimization.

**Unhid `scanned_storage_bytes` and `scanned_cache_bytes` from information schema views**

Unhid `scanned_storage_bytes` and `scanned_cache_bytes` columns from `information_schema.engine_query_history` and `information_schema.engine_user_query_history` views. These columns were previously accessible when explicitly used in a `SELECT` clause, but will now appear by default when you use `SELECT *`.

### Performance Improvements

**Enhanced data ingestion performance for `GEOGRAPHY` objects of type `POINT`**

Improved data loading performance for `GEOGRAPHY` objects of type `POINT`, enabling up to four times faster loading of geographical point data for more efficient data integration and analysis.

**Improved file listing times for large external scans**

In operations that read data from Amazon S3 buckets such as external table scans or `COPY FROM` queries, Firebolt lists files in a URL to an Amazon S3 bucket. This process is constrained by the AWS API, which limits file listing to 1,000 files per request. Firebolt has increased the number of concurrent operations so that listing a large number of files is up to 3.5 times faster.

**Added result cache support for cross and complex joins for improved performance**

The [query result cache](/reference-sql/system-settings#result-cache) now supports queries using cross joins or complex joins with `OR` conditions and inequalities. This change reduces redundant calculations, improving query performance.

### Bug Fixes

**`USAGE` permissions are now required to access `INFORMATION_SCHEMA` views**

Accessing `INFORMATION_SCHEMA` views now requires `USAGE` permissions on the database. Queries to `INFORMATION_SCHEMA` will fail if these permissions are missing, ensuring consistent enforcement across permission-restricted queries. Ensure that your database has the necessary permissions to prevent access issues.

**Improved `EXPLAIN` command accuracy for default values of `DATE`, `TIMESTAMP`, and `TIMESTAMPTZ` columns**

The `EXPLAIN` command now displays default values for columns of type `DATE`, `TIMESTAMP`, and `TIMESTAMPTZ` columns. This update fixes a bug that previously caused default values to be shown incompletely, improving clarity and accuracy in query plan analysis.

**Resolved filtering issue for views in `information_schema.tables` to enforce user permissions**

Fixed a bug in `information_schema.tables` which previously listed views that users were not authorized to access. Even though querying these views would fail, users could still see that they existed. Now `information_schema.tables` only lists views that users are allowed to access.

## Firebolt Release Notes - Version 4.13

### New Features

**`GRANT ALL ON ACCOUNT` and `REVOKE ALL ON ACCOUNT` statements for role-based privileges**\
The statements `GRANT ALL ON ACCOUNT account_name TO role_name` and `REVOKE ALL ON ACCOUNT account_name FROM role_name` are now supported. They grant or revoke all account-related privileges to the specified role `role_name`.

**Support for nested arrays in Parquet files**\
You can now ingest Parquet files containing nested array structures at any depth. For example: `array(array(array(string)))`.

### Behavior Changes

**Removed secured objects from `information_schema` views**\
Users can now only access information about objects for which they have the appropriate permissions or ownership for in [information\_schema views](/reference-sql/information-schema/views).

### Bug Fixes

**`@` character support restored in usernames**\
The usage of character `@` is allowed in usernames again, which was previously restricted. The following statements are now valid and will not cause errors:

```
CREATE USER "user@example.com";
ALTER USER user_name RENAME TO "user@example.com";
```

**Resolved memory overuse during CSV import**\
Resolved a memory overconsumption problem that occurred when importing CSV files into existing tables.

**Resolved `EXPLAIN VACUUM` and `EXPLAIN` to improve error handling and result accuracy**\
The following behavior of `EXPLAIN VACUUM` has been updated:

1. If a table is fully vacuumed, no further actions are performed, and the message "Table is fully vacuumed, no vacuum jobs were executed" is returned to the user.
2. The `EXPLAIN VACUUM` output no longer returns an empty result when the vacuumed object is an aggregating index.
3. `EXPLAIN` has been updated to show an error if the specified relation does not exist.

**Fixed incorrect evaluation of `IS NULL` in outer joins**\
Fixed an issue where `IS NULL` predicates on non-nullable columns from the non-preserving side of an outer join were incorrectly reduced to `FALSE` during common table expression (CTE) optimization. When the optimizer attempted to fuse multiple CTEs, it mistakenly replaced `t2.x IS NULL` with `FALSE`, altering query semantics and producing incorrect results. This occurred because `t2.x`, though defined as non-nullable, became nullable when used in a left join. The fix ensures that `IS NULL` predicates are correctly preserved during optimization.

## Firebolt Release Notes - Version 4.12

### New Features

**Added `ST_S2CELLIDFROMPOINT` to retrieve the [S2 Cell ID](http://s2geometry.io/devguide/s2cell_hierarchy) of a `GEOGRAPHY` Point**

You can now use [ST\_S2CELLIDFROMPOINT](/reference-sql/functions-reference/geospatial/st_s2cellidfrompoint) to retrieve the S2 cell ID, which identifies the region on Earth that fully contains, or covers, a single Point `GEOGRAPHY` object. You can also specify a cell resolution level.

**Added keyboard shortcuts to the Firebolt Develop Space**

The Firebolt **Develop Space** user interface added the following Windows/Mac [keyboard shortcuts](/guides/getting-started/using-the-develop-workspace#keyboard-shortcuts-for-the-develop-space):

* Ctrl + Alt + E / Ctrl + ⌘ + E – Toggle expanding or collapsing query results.
* Ctrl + Alt + N / Ctrl + ⌘ + N – Create a new script.
* Ctrl + Alt + \[ / Ctrl + ⌘ + \[ – Jump to the previous script.
* Ctrl + Alt + ] / Ctrl + ⌘ + ] – Jump to the next script.

**Introduced the `INFORMATION_SCHEMA.ROUTINES` view for built-in functions and operators**

Added the [INFORMATION\_SCHEMA.ROUTINES](/reference-sql/information-schema/routines) view to return information about all of Firebolt's built-in functions and operators including their database, schema, name, type, return data type, parameter data types, and whether they are deterministic.

**Added support for the `GEOGRAPHY` data type in external tables using CSV and JSON formats**

Firebolt can now read columns of type `GEOGRAPHY` from external tables in CSV or JSON format, which allows the querying of geospatial data including Points and Polygons.

**Added a new `MONITOR USAGE` privilege**

You can use the `MONITOR USAGE` privilege to view all queries running on an engine using [information\_schema.engine\_query\_history](/reference-sql/information-schema/engine-query-history) or [information\_schema.engine\_running\_queries](/reference-sql/information-schema/engine-running-queries) views.

**Introduced support for network policy `ADD`/`REMOVE` commands**\
Admins can now append or remove specific IP addresses in `ALLOW` or `BLOCK` lists without overriding existing values. This update simplifies network policy management when handling large IP lists and reduces the risk of concurrent updates overwriting each other.

### Performance Improvements

**Improved performance of the `ST_COVERS`, `ST_CONTAINS`, and `ST_INTERSECTS` functions**

Optimized the [ST\_COVERS](/reference-sql/functions-reference/geospatial/st_covers), [ST\_CONTAINS](/reference-sql/functions-reference/geospatial/st_contains), and [ST\_INTERSECTS](/reference-sql/functions-reference/geospatial/st_intersects) functions to improve performance when processing LineStrings and Points with non-intersecting inputs, and Polygons with inputs that do not intersect their boundaries.

**Improved performance of the `REGEXP_LIKE_ANY` function**

The [REGEXP\_LIKE\_ANY](/reference-sql/functions-reference/string/regexp-like-any) function now performs more efficiently when matching against multiple patterns by compiling a single combined [RE2](https://github.com/google/re2/) regular expression object instead of evaluating each pattern separately.

### Behavior Changes

**Updated user name rules to improve consistency and validation**

The following changes affect the use of user names in [CREATE USER](/reference-sql/commands/access-control/create-user) AND [ALTER USER](/reference-sql/commands/access-control/alter-user):

* The `@` character is no longer allowed in user names.
* The range of permissible characters in user names is expanded. For more information, see [CREATE USER](/reference-sql/commands/access-control/create-user).
* When renaming a user with  [ALTER USER](/reference-sql/commands/access-control/alter-user) `old_name RENAME TO new_name`, the `new_name` must now comply with the updated user name rules.
* Any new names created with [CREATE USER](/reference-sql/commands/access-control/create-user) must now comply with the updated user name rules.

### Bug Fixes

**Fixed an error where `APACHE_DATASKETCHES_HLL_ESTIMATE` failed for `NULL` inputs**

Resolved an error in the [APACHE\_DATASKETCHES\_HLL\_ESTIMATE](/reference-sql/functions-reference/datasketches/apache-datasketches-hll-estimate) function that occurred if any of its input values were `NULL`. The function can now process `NULL` inputs.

**Resolved issue that allowed account lockout on last login**

Fixed an issue where the `ALTER USER SET LOGIN/SERVICE_ACCOUNT=...` statement could lock out the only active login in an account, rendering the account inaccessible. The operation now fails with an explicit error message in such cases.

**Fixed incorrect ownership modification for `information_schema`**

The statement `ALTER SCHEMA information_schema SET OWNER owner_name;` previously succeeded, which was incorrect, because `information_schema` cannot be modified. The operation now fails with an explicit error message.

**Fixed an out-of-memory error during large CSV imports**

Updated the ingestion pipeline for [COPY FROM](/reference-sql/commands/data-management/copy-from) to ensure that large CSV files without a predefined schema can load into new tables without causing memory errors. This error did not affect external tables.

**Prevent running queries when using a dropped database**

When the current database does not exist, such as when it has been dropped, most queries fail as expected. We fixed a bug where some queries against specific `information_schema` views, such as `engines`, `catalogs`, `applicable_roles`, would still succeed in such cases. These queries now fail consistently, like all other queries against a non-existent database.
For example, running `SELECT * FROM information_schema.engines` when the database is dropped previously worked, but now fails.

## Firebolt Release Notes - Version 4.11

### New Features

**Introduced the `GEOGRAPHY` data type and functions for geospatial data handling \[public preview]**

Added a new [GEOGRAPHY](/reference-sql/data-types/geography) data type and functions for working with geospatial data. Firebolt supports the three industry standard formats [Well-Known Text (WKT)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry), [Well-Known Binary (WKB)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Well-known_binary), and [GeoJSON](https://datatracker.ietf.org/doc/html/rfc7946) for geospatial data.

This public preview release includes the following functions:

* [ST\_ASBINARY](/reference-sql/functions-reference/geospatial/st_asbinary) – Converts shapes of the `GEOGRAPHY` data type to the [Well-Known Binary (WKB)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Well-known_binary) format for geographic objects.
* [ST\_ASEWKB](/reference-sql/functions-reference/geospatial/st_asewkb) – Converts shapes of the `GEOGRAPHY` data type to the [extended Well-Known Binary (EWKB)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Format_variations) format using Spatial Reference Identifier (SRID) 4326, which corresponds to the [WGS84](https://en.wikipedia.org/wiki/World_Geodetic_System#WGS_84) coordinate system.
* [ST\_ASGEOJSON](/reference-sql/functions-reference/geospatial/st_asgeojson) – Converts shapes of the `GEOGRAPHY` data type to the [GeoJSON](https://datatracker.ietf.org/doc/html/rfc7946) format.
* [ST\_ASTEXT](/reference-sql/functions-reference/geospatial/st_astext) – Converts shapes of the `GEOGRAPHY` data type to the [Well-Known Text (WKT)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry) format.
* [ST\_CONTAINS](/reference-sql/functions-reference/geospatial/st_contains) – Determines if one `GEOGRAPHY` object fully contains another.
* [ST\_COVERS](/reference-sql/functions-reference/geospatial/st_covers) – Determines if one `GEOGRAPHY` object fully encompasses another.
* [ST\_DISTANCE](/reference-sql/functions-reference/geospatial/st_distance) – Calculates the shortest distance, measured as a geodesic arc between two `GEOGRAPHY` objects, measured in meters.
* [ST\_GEOGFROMGEOJSON](/reference-sql/functions-reference/geospatial/st_geogfromgeojson) – Constructs a `GEOGRAPHY` object from a [GeoJSON](https://datatracker.ietf.org/doc/html/rfc7946) string.
* [ST\_GEOGFROMTEXT](/reference-sql/functions-reference/geospatial/st_geogfromtext) – Constructs a `GEOGRAPHY` object from a [Well-Known Text (WKT)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry) string.
* [ST\_GEOGFROMWKB](/reference-sql/functions-reference/geospatial/st_geogfromwkb) – Constructs a `GEOGRAPHY` object from a [Well-Known Binary](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Well-known_binary) (WKB) byte string.
* [ST\_GEOGPOINT](/reference-sql/functions-reference/geospatial/st_geogpoint) – Constructs a Point in the `GEOGRAPHY` data type created from specified longitude and latitude coordinates.
* [ST\_INTERSECTS](/reference-sql/functions-reference/geospatial/st_intersects) – Determines whether two input `GEOGRAPHY` objects intersect each other.
* [ST\_X](/reference-sql/functions-reference/geospatial/st_x) – Extracts the longitude coordinate of a `GEOGRAPHY` Point.
* [ST\_Y](/reference-sql/functions-reference/geospatial/st_y) – Extracts the latitude coordinate of a `GEOGRAPHY` Point.

**Added keyboard shortcuts to the Firebolt Develop Space**

The user interface in the Firebolt **Develop Space** added the following [keyboard shortcuts](/guides/getting-started/using-the-develop-workspace#keyboard-shortcuts-for-the-develop-space):

* Cmd + Enter – Runs the current query.
* Cmd+Shift+Enter – Runs all queries in a script.

**Added the window function `FIRST_VALUE`**

Added a new [FIRST\_VALUE](/reference-sql/functions-reference/window/first-value) window function that returns the first value evaluated in a specified window frame.

## Firebolt Release Notes - Version 4.10

### New Features

**Added `CREATE TABLE CLONE` to clone an existing table in a database**

You can create a clone of an existing table in a database using `CREATE TABLE CLONE`, which is extremely fast because it copies the table structure and references without duplicating the data. The clone functions independently of the original table. Any changes to the data or schema of either table will not affect the other.

**Added 3-part identifier support for specifying databases in queries**

You can now reference a database other than the current one in queries by using 3-part identifiers, which specify the database, schema, and object. For example, even if you previously selected a database `db`
by using `USE DATABASE db`, you can still query a different database by using a query such as
`SELECT * FROM other_db.public.t`. The limitation still exists that every query only addresses a single database.

**Added `ALTER TABLE ADD COLUMN` to add a column to an existing table**

You can now use `ALTER TABLE ADD COLUMN` to add columns to Firebolt-managed tables.
This functionality is temporarily limited to tables that were created on Firebolt version 4.10 or higher.

**Added support of `ALTER TABLE RENAME` command**
You can use `ALTER TABLE RENAME` to change the name of Firebolt-managed tables.
This functionality is temporarily limited to tables created on Firebolt version 4.10 or higher.

**Added support for external file access using AWS session tokens**

You can now use `<AWS_SESSION_TOKEN>` with access keys to securely authenticate and access external files on AWS with the following features:

* The [COPY TO](/reference-sql/commands/data-management/copy-to) and [COPY FROM](/reference-sql/commands/data-management/copy-from) commands.
* [External tables](/guides/loading-data/working-with-external-tables) located in an Amazon S3 bucket.
* The following table-valued functions: `read_parquet`, `read_csv`, and `list_objects`.

### Behavior Changes

**Enhanced PostgreSQL compliance for casting data types from text to float**

Cast from text to floating-point types is now compliant with PostgreSQL with the following improvements:

1. **The correct parsing of positive floats** – A plus sign (`+`) preceding a float is now handled correctly. Example: `'+3.4'`.
2. **Exponent-only input** – Float values starting with an exponent `'e'` or `'E'` are rejected. Example: `'E4'`.
3. **Incomplete exponents** – Float values ending with an exponent without a subsequent exponent value are rejected. Example: `'4e+'`.

**Account-level rate limits implemented for the system engine**

Firebolt has implemented account-level rate limits to ensure equitable resource usage among all users of the system engine. When these limits are exceeded, requests will be rejected with the following error message: `429: Account system engine resources usage limit exceeded`. This rate limit targets accounts with exceptionally high resource consumption. Accounts with typical resource usage should not be affected and require no further action.

### Bug Fixes

**Corrected runtime reporting**

Resolved an issue where the runtime displayed in Firebolt's user interface and JSON responses omitted including processing times for some query steps.

**Resolved "Invalid Input Aggregate State Type" error with aggregating indexes**

Fixed an issue where the "invalid input aggregate state type" error could occur when queries read from aggregating indexes that defined a `COUNT(*)` aggregate function before other aggregate functions. After this fix, such aggregating indexes can now be queried correctly without needing to be rebuilt.

**Fixed a rare bug in subresult caching logic**

Addressed a rare issue in the logic for caching and reusing subresults that could cause query failures with specific query patterns. This issue did not impact the correctness of query results.

**Resolved issue preventing schema owners from granting "ANY" privileges**

Fixed an issue where schema owners were unable to grant "ANY" privileges on their schema to other users.\
For example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
GRANT SELECT ANY ON SCHEMA public TO ...
```

Schema owners can now execute this command which allows the specified user or role to perform SELECT operations on any table.

## Firebolt Release Notes - Version 4.9

### New Features

**Added the `enable_result_cache` setting for controlling query result caching during benchmarking**

You can set `enable_result_cache` to `FALSE` to disable the use of Firebolt's result cache, which is set to `TRUE` by default. Disabling result cashing can be useful for benchmarking query performance. When `enable_result_cache` is disabled, resubmitting the same query will recompute the results rather than retrieving them from cache. For more information, see [Result Cache](/reference-sql/system-settings#result-cache).

**Added `LAG` and `LEAD` support for negative offsets.**

The second parameter in both [LAG](/reference-sql/functions-reference/window/lag) and [LEAD](/reference-sql/functions-reference/window/lead) can now accept negative numbers. Given a negative number, a `LAG` will become a `LEAD` and vice versa. For example, `LAG(x,-5,3)` is the same as `LEAD(x,5,3)`.

### Performance Improvements

**Faster string searches for case-insensitive simple regular expressions in `REGEXP_LIKE`**

Simple regular expressions in [REGEXP\_LIKE](/reference-sql/functions-reference/string/regexp-like) with case-insensitive matching, using the `i` flag, now use the same optimized string search implementation as [ILIKE](/reference-sql/functions-reference/string/ilike), achieving up to three times faster runtimes in observed cases.

### Bug Fixes

**Empty character classes in regular expressions**

Fixed a rare case where empty character classes were mistakenly interpreted as valid character classes instead of being treated as raw characters. In cases like `[]a]`, the expression is now correctly interpreted as a pattern that matches any single character from the list `]a`, rather than treating `[]` as an empty character class followed by `a]`.

**Trailing backslash in regular expressions**

Fixed a rare case where invalid regular expressions with a trailing backslash `\` were accepted.

## Firebolt Release Notes - Version 4.8

### New Features

**Introduced new bitwise shift functions `BIT_SHIFT_RIGHT` and `BIT_SHIFT_LEFT`**

The following bitwise shift functions are now supported:

* `BIT_SHIFT_RIGHT` shifts the bits of a number to the right by a specified number of positions, which effectively divides the number by `2` for each position shifted.
* `BIT_SHIFT_LEFT` shifts the bits of a number to the left by a specified number of positions, which effectively multiples the number by `2` for each position shifted.

**Introduced new trigonometric functions `ACOS`, `ATAN`, `ASIN`, `COS`, `COT`, `TAN`, `DEGREES`, and `PI`**

The following trigonometric functions are now supported:

* `ACOS` calculates the arccosine of a value in radians.
* `ATAN` calculates the arctangent of a value in radians.
* `ASIN` calculates the arcsine of a value in radians.
* `COS` calculates the cosine of a value in radians.
* `COT` calculates the cotangent of a value in radians.
* `TAN` calculates the tangent of a value in radians.
* `DEGREES` converts a value in radians to degrees.
* `PI` returns π as a value of type `DOUBLE PRECISION`.

**Introduced the `timezone` query-level setting with `time_zone` as an alias**

Added the `timezone` query-level setting. The previous `time_zone` query setting still works, and is now an alias for `timezone`.

**Introduced new `PERCENTILE_CONT` and `MEDIAN` aggregate functions**

Added the following aggregate functions:

* `PERCENTILE_CONT` calculates a specified percentile of values in an ordered dataset.
* `MEDIAN` returns the median of a given column. It is equivalent to `PERCENTILE_CONT(0.5)`: half the values in the column are smaller, and half are bigger than the returned value. If the number of values in the column is even, `MEDIAN` returns the arithmetic mean of the two middle values.

**Added support to meet HIPAA regulations for health information**

Added [support to meet federal HIPAA regulations](/overview/security#hipaa-compliance) to ensure the confidentiality, integrity, and availability of electronic protected health information within the Firebolt platform.

### Performance Improvements

**Improved expression comparison logic within queries**

Improved expression comparison logic to better recognize identical expressions within queries. This enhancement supports a broader range of queries and boosts the overall quality of query plans.

**Improving cold reads by reducing the amount of Amazon S3 requests needed to load data**

Improved the performance of cold reads by minimizing the number of Amazon S3 requests required to load data. In the case of tiny tablets, this improvement lead to a 50% improvement in performance.

### Bug Fixes

**Fixed a bug preventing view creation with type conversions to array types**

Fixed an issue that prevented users from creating database views that involve type conversion to array types.

## Firebolt Release Notes - Version 4.7

### New Features

**Added Snappy compression support to the COPY TO command for PARQUET output format**<br />
You can now apply Snappy compression, which is faster than GZIP, when using `COPY TO` with `TYPE=PARQUET`. Specify `COMPRESSION=SNAPPY` within `COPY TO` to enable this.

**Added `information_schema.engine_user_query_history` view to log only user-initiated queries**<br />
Added a new query history view, `information_schema.engine_user_query_history`, which shows all queries initiated by users. This view filters information from `information_schema.engine_query_history` view, which logs all engine queries including system-generated ones like UI updates and page-load requests.

**Added support for `information_schema.enabled_roles`**<br />
Added a new view `information_schema.enabled_roles` which lists the roles available in the account.

**Added a system setting `enable_subresult_cache` for controlling subresult reuse**<br />
A new system setting `enable_subresult_cache` allows users to enable or disable caching of query subresults for subsequent reuse.
Caching remains enabled by default. This setting allows users to temporarily disabling caching, e.g. for benchmarking purposes.

**Added "FROM first" syntax allowing the `FROM` clause to precede the `SELECT` clause**<br />
Added support for the "FROM first" syntax, which allows placing the `FROM` clause before the `SELECT` clause, for example `FROM t SELECT a, SUM(b) GROUP BY a`. You can now also omit the `SELECT` clause, as in `FROM t`.

**Introduced a new function `GEN_RANDOM_UUID_TEXT` to generate a universally unique identifier (UUID)**<br />
The new function `GEN_RANDOM_UUID_TEXT` accepts no arguments and returns a version `4` UUID as defined by [RFC-4122](https://tools.ietf.org/html/rfc4122#section-4.4) as a `TEXT` value.

**Introduced `~` and `!~` operators as aliases for `REGEXP_LIKE` and `NOT REGEXP_LIKE`**<br />
Added the `~` operator as an alias for `REGEXP_LIKE`, and the `!~` operator, which serves as an alias for `NOT REGEXP_LIKE`.

**Introduced JSON functions `JSON_POINTER_EXTRACT_KEYS`, `JSON_POINTER_EXTRACT_VALUES`, `JSON_POINTER_EXTRACT_TEXT`**<br />
The following new JSON functions are now supported:

* `JSON_POINTER_EXTRACT_KEYS` extracts keys from a JSON object
* `JSON_POINTER_EXTRACT_VALUES` extracts values from a JSON object
* `JSON_POINTER_EXTRACT_TEXT` extracts the JSON string value as SQL TEXT

**Introduced trigonometric functions `RADIANS`, `SIN`, `ATAN2`**<br />
The following trigonometric functions are now supported:

* `RADIANS` to convert degrees into radians
* `SIN` to compute the sine in radians
* `ATAN2` to calculate the arctangent with two arguments. `ATAN2(y,x)` is the angle between the positive x-axis and the line from the origin to the point `(x,y)`, expressed in radians.

**Introduced new functions to calculate standard deviation and variance for both samples and populations**<br />
New functions that accept `REAL` and `DOUBLE` inputs and return standard deviations and variances:

* `STDDEV_SAMP` - Returns the sample standard deviation of all non-`NULL` numeric values produced by an expression, which measures how spread out values are in a sample.
* `STDDEV_POP` - Returns the population standard deviation of all non-`NULL` numeric values produced by an expression, which measures how spread out values are in an entire population.
* `VAR_SAMP` - Returns the sample variance of all non-`NULL` numeric values produced by an expression, which measures the average of the squared differences from the sample mean, indicating how spread out the values are within a sample.
* `VAR_POP` - Returns the population variance of all non-`NULL` numeric values produced by an expression. The population variance measures the average of the squared differences from the population mean, indicating how spread out the values are within the entire population.

**Introduced new array functions `ARRAY_ALL_MATCH` and `ARRAY_ANY_MATCH`**<br />
The new functions `ARRAY_ALL_MATCH` and `ARRAY_ANY_MATCH` accept an (optional) lambda function and an array and return `TRUE` if all elements (`ARRAY_ALL_MATCH`) or any element (`ARRAY_ANY_MATCH`) satisfy the lambda condition, and `FALSE` otherwise.  When no lambda is passed, the array has to be of type `BOOLEAN`, and the identity lambda `x -> x` is used.

### Performance Improvements

**Improved performance of `JSON_EXTRACT`, `JSON_EXTRACT_ARRAY`, and `JSON_VALUE` functions**<br />
Enhanced the performance of the `JSON_EXTRACT`, `JSON_EXTRACT_ARRAY`, and `JSON_VALUE` functions.

### Behavior Changes

**Updated sorting method for array columns with `NULL` values to align with PostgreSQL behavior**

The sorting method for array columns containing `NULL` values has been updated to ensure that `ASC NULLS FIRST` places `NULL` values before arrays, and `DESC NULLS LAST` places `NULL` values after arrays, which aligns with PostgreSQL behavior.

The following code example creates a temporary table `tbl` which contains three rows: a `NULL` array, an array with the value `1`, and an array with a `NULL` element. Then, a `SELECT` statement sorts all rows in ascending order:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH tbl(i) AS (
  SELECT NULL::INT[]
  UNION ALL
  SELECT ARRAY[1]::INT[]
  UNION ALL
  SELECT ARRAY[NULL]::INT[]
)
SELECT * FROM tbl ORDER BY i ASC NULLS FIRST;
```

The query previously returned `{NULL}, {1}, NULL`, but now returns `NULL, {1}, {NULL}`.

`NULLS FIRST` and `NULLS LAST` apply to the array itself, not to its elements. By default, ascending order (`ASC`) assumes `NULLS LAST`, while descending order (`DESC`) assumes `NULLS FIRST` when sorting arrays.

**Allowed use of the SESSION\_USER function without parentheses**

The `SESSION_USER` function can now be used without parentheses, like this: `SELECT SESSION_USER`. As a result, any column named `session_user` now needs to be enclosed in double quotes as follows: `SELECT 1 AS "session_user"` or `SELECT "session_user" FROM table`.

### Bug Fixes

**Corrected JSON output format to display NaN values consistently as `nan`**<br />
The JSON output format previously showed some NaN values as `-nan`. This was corrected to consistently display NaN values as `nan` in the JSON output.

**Resolved an issue with `CHECKSUM` and `HASH_AGG` failing when combining literals and table columns**<br />
Fixed an issue where the `CHECKSUM` and `HASH_AGG` functions failed when used with a combination of literals and table columns.

**Fixed a rare inaccuracy that could cause incorrect results on multi-node engines when performing certain `UNION ALL` operations**<br />
Fixed a rare inaccuracy when performing certain `UNION ALL` operations on subqueries that are the result of aggregations or joins on overlapping but distinct keys, followed by an aggregation or join on the common keys of the subqueries' aggregations or joins.

**Fixed a rare inaccuracy that could cause incorrect results with CTEs using `RANDOM()` in specific join scenarios**<br />
Fixed a rare inaccuracy that caused incorrect results when a common table expression using the `RANDOM()` function was used multiple times, and at least one of these uses was on the probe side of a join involving a primary index key of the underlying table.

## Firebolt Release Notes - Version 4.6

**September 2024**

### New Features

**`COPY TO` support for the `SNAPPY` compression type**<br />

[COPY TO](/reference-sql/commands/data-management/copy-to) now supports `SNAPPY` as a new compression option for Parquet files. This enhancement offers greater flexibility for managing file size and performance, particularly for workloads requiring faster compression. Each file is written in Parquet format, with the specified compression applied to the data pages in the column chunks.

**`COPY FROM` support for filtering by source file metadata**<br />

[COPY FROM](/reference-sql/commands/data-management/copy-from) now supports filtering by source file metadata using the `WHERE` clause.

**Added support for vector distance calculations with new functions**

Firebolt has added support for vector distance and similarity calculations with the following new functions: [VECTOR\_COSINE\_DISTANCE](/reference-sql/functions-reference/vector/vector-cosine-distance), [VECTOR\_MANHATTAN\_DISTANCE](/reference-sql/functions-reference/vector/vector-manhattan-distance), [VECTOR\_EUCLIDEAN\_DISTANCE](/reference-sql/functions-reference/vector/vector-euclidean-distance), [VECTOR\_SQUARED\_EUCLIDEAN\_DISTANCE](/reference-sql/functions-reference/vector/vector-squared-euclidean-distance), [VECTOR\_COSINE\_SIMILARITY](/reference-sql/functions-reference/vector/vector-cosine-similarity), and [VECTOR\_INNER\_PRODUCT](/reference-sql/functions-reference/vector/vector-inner-product).

### Behavior Changes

**Introduced `SHOW CATALOGS` statement and aliased `SHOW DATABASES` to it while deprecating `SHOW DATABASE X`**

A new statement `SHOW CATALOGS` now acts as an alias for `SHOW DATABASES`. The statement `SHOW DATABASE X` is no longer supported.

**`COPY FROM` now unzips Parquet files with gzip extensions**

Before version 4.6, the `COPY FROM` command did not apply file-level decompression to Parquet files with a `.gzip` or `.gz` extension. The command treated these files as standard Parquet files, assuming that any compression existed only within the internal Parquet format structure.

With the release of version 4.6, `COPY FROM` now processes Parquet files similarly to other formats. When a Parquet file has a `.gz` or `.gzip` extension, the command will first decompress the file before reading it as a Parquet format file. Hence, it will now fail while reading internally compressed Parquet files with gzip extensions. Users experiencing issues with loading files after this change should contact the support team at [support@firebolt.io](mailto:support@firebolt.io) for assistance.

### Bug Fixes

**Fixed a rare bug that caused some query failures from incorrect computation of cacheable subresults**

Fixed a rare bug impacting the logic that determined which subresults could be cached and reused. This issue could have caused query failures in certain patterns, but it did not impact the accuracy of the query outcomes.

**Updated name of aggregatefunction2 to aggregatefunction in explain output**

The name `aggregatefunction2` has been updated to `aggregatefunction` in the [EXPLAIN](/reference-sql/commands/queries/explain) output.

**Fixed incorrect results in `ARRAY_AGG` expressions by excluding `NULL` values for false conditions in aggregating indexes**

Aggregate expressions like `ARRAY_AGG(CASE WHEN <cond> THEN <column> ELSE NULL END)` previously returned incorrect results by excluding `NULL` values for rows when the condition was `FALSE`.

## Firebolt Release Notes - Version 4.5

**September 2024**

### New Features

**Allowed casting from `TEXT` to `DATE` with truncation of timestamp-related fields**
Casting from `TEXT` to `DATE` now supports text values containing fields related to timestamps. These fields are accepted, but truncated during conversion to `DATE`.

The following code example casts the `TEXT` representation of the timestamp `2024-08-07 12:34:56.789` to the `DATE` data type. The conversion truncates the time portion, leaving only the date, as follows:

Example:

```
SELECT '2024-08-07 12:34:56.789'::DATE`
```

Results in

```
DATE `2024-08-07`
```

**Added the `CONVERT_FROM` function**

Added the `CONVERT_FROM` function that converts a `BYTEA` value with a given encoding to a `TEXT` value encoded in UTF-8.

**Added the BITWISE aggregate functions**

Added support for the following functions: BIT\_OR (bitwise OR), BIT\_XOR (bitwise exclusive OR), and BIT\_AND (bitwise AND).

**Added the `REGEXP_LIKE_ANY` function**

Added the `REGEXP_LIKE_ANY` function that checks whether a given string matches any regular expression pattern from a specified list of patterns.

### Bug Fixes

**Updated `created` and `last_altered` column types in `information_schema.views` from `TIMESTAMP` to `TIMESTAMPTZ`**
The data types of the `created` and `last_altered` columns in `information_schema.views` have been changed from `TIMESTAMP` to `TIMESTAMPTZ`.

**Fixed runtime constant handling in the sort operator**
Fixed the handling of runtime constants in the sort operator.
Now, the sort operator can be correctly combined with `GENERATE_SERIES`.
For example, the query `SELECT x, GENERATE_SERIES(1,7,3) FROM GENERATE_SERIES(1,3) t(x)` now correctly displays values `1` to `3` in the first column, instead of just `1`.

## Firebolt Release Notes - Version 4.4

**August 2024**

### New Features

**Extended support for date arithmetic**

Now you can subtract two dates to get the number of elapsed days. For example, `DATE '2023-03-03' - DATE '1996-09-03'` produces `9677`.

**Role-based permissions for COPY FROM and external tables**

Added support for role-based permissions (ARNs) to the COPY FROM command and external table operations.

**Added `trust_policy_role` column to `information_schema.accounts` view for S3 access**

Added a new column `trust_policy_role` to the `information_schema.accounts` view. This column shows the role used by Firebolt to access customer S3 buckets.

**Enabled selection of external tables' pseudo columns without adding data columns**

Users can now select an external table's pseudo columns (source file name, timestamp, size, and etag) without adding any data columns. For example, `select $source_file_timestamp from t_external` returns the file timestamps for each row. The query `select count($source_file_timestamp) from t_external` returns the total number of rows in the external table, similar to `count(*)`. The query `select count(distinct $source_file_name) from t_external` returns the number of distinct objects containing at least one row in the source S3 location.
Regarding `count(*)` performance, formats like CSV or JSON still require reading the data fully to determine an external file's row count. However, Parquet files provide the row count as part of the file header, and this is now used instead of reading the full data.

**Extended support for arbitrary join conditions, including multiple inequality predicates**

We now support more join conditions. As long as there is one equality predicate comparing a left column to a right column of the join (not part of an OR expression), the remaining join condition can now be an arbitrary expression. The limitation on the number of inequality predicates was removed.

**New functions `URL_ENCODE` and `URL_DECODE`**

We added support for the `URL_ENCODE` and `URL_DECODE` functions.

**New logarithm functions `ln`, `log`**

We added support for calculating logarithms. The natural logarithm is available using `ln(val double precision)`. The base 10 logarithm is available using `log(val double precision)`. Logarithms with custom bases are available using `log(base double precision, val double precision)`.

**New function \`SQRT**

Added support for the `SQRT` function to compute the square root.

**New functions `JSON_VALUE`, `JSON_VALUE_ARRAY`, `JSON_EXTRACT_ARRAY`**

Added support for the functions `JSON_VALUE`, `JSON_VALUE_ARRAY`, and `JSON_EXTRACT_ARRAY`.

**New function `SESSION_USER`**

Support has been added for the `SESSION_USER` function, which retrieves the current user name.

**New columns in `information_schema.engine_query_history`**

Added two new columns to `information_schema.engine_query_history`: `query_text_normalized_hash` and `query_text_normalized`.

### Breaking Changes

**Reserved the keyword GEOGRAPHY, requiring double quotes for use as an identifier**

The word GEOGRAPHY is now a reserved keyword and must be quoted using double quotes for use as an identifier. For example, `create table geography(geography int);` will now fail, but `create table "geography" ("geography" int);` will succeed.

**Deprecated the legacy HTTP ClickHouse headers**

We no longer accept or return the legacy HTTP ClickHouse header format `X-ClickHouse-*`.

**Fixed `json_value` zero-byte handling**

The `json_value` function no longer returns null characters (0x00), as the TEXT datatype does not support them. For example, `select json_value('"\u0000"');` now results in an error.

**Change default values for NODES and TYPE during CREATE ENGINE**

When performing a CREATE ENGINE, the default values for NODES and TYPE parameters have changed. NODES defaults to `2` (previously `1`) and TYPE defaults to `M` (previously `S`). To create an engine with the previous default values, run the following command:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE ENGINE my_engine WITH NODES=1 TYPE=S
```

### Bug Fixes

**Fixed directory structure duplication in the S3 path when using the COPY TO statement with SINGLE\_FILE set to FALSE**

Fixed an issue in `COPY TO` when `SINGLE_FILE=FALSE`. Previously, the specified directory structure in the location was repeated twice in the S3 path. For example, files were output to "s3://my-bucket/out/path/out/path/" instead of "s3://my-bucket/out/path/".

**Fixed the file extension in the S3 path when using the COPY TO statement with GZIP-Parquet format**

Fixed an issue in `COPY TO` when `TYPE=PARQUET` and `COMPRESSION=GZIP`, which uses the Parquet file format with internal GZIP compression for the columns. Previously, the output files would have the extension ".parquet.gz". Now, the extension is ".gz.parquet".

## Firebolt Release Notes - Version 4.3

**August 2024**

### New Features

**Role-based permissions for COPY FROM and External Table processes**

Enabled role-based permissions for COPY FROM and External Table processes.

**HLL-based count distinct functions compatible with the Apache DataSketches library**

Firebolt now supports count-distinct functions using the HLL (HyperLogLog) algorithm, compatible with the Apache DataSketches library.
For details and examples, see documentation on the functions
[APACHE\_DATASKETCHES\_HLL\_BUILD](/reference-sql/functions-reference/datasketches/apache-datasketches-hll-build),
[APACHE\_DATASKETCHES\_HLL\_MERGE](/reference-sql/functions-reference/datasketches/apache-datasketches-hll-merge),
and [APACHE\_DATASKETCHES\_HLL\_ESTIMATE](/reference-sql/functions-reference/datasketches/apache-datasketches-hll-estimate).

**Supported additional join conditions and removed the restriction on the number of inequality predicates**

Firebolt has added enhanced support for more join conditions. As long as there is one equality predicate comparing a left column to a right column of the join, which is not part of a disjunctive (OR) expression, the remaining join condition can be arbitrary. The previous limitation on the number of inequality predicates has been removed.

### Performance Improvements

**Multi-node query performance**

Firebolt has improved the performance of data transfer between nodes, resulting in faster overall query execution times.

**Enhanced Interval Arithmetic Support**

Firebolt has enhanced support for interval arithmetic. You can now use expressions of the form `date_time + INTERVAL * d`, where `date_time` is a expression of type Date, Timestamp, TimestampTz, and `d` is an expression of type DOUBLE PRECISION. The interval is now scaled by `d` before being added to `date_time`. For example, writing `INTERVAL '1 day' * 3` is equivalent to writing `INTERVAL '3 days'`.

**Optimized selective inner and right joins on primary index and partition by columns to reduce rows scanned**

Selective inner and right joins on primary index and partition by columns now can now benefit from pruning. This reduces the number of rows scanned by filtering out rows that are not part of the join result early in the process. This optimization works best when joining on the first primary index column or a partition by column. The optimization is applied automatically when applicable, and no action is required. Queries that used this optimization will display "Prune:" labels on the table scan in the EXPLAIN (PHYSICAL) or EXPLAIN (ANALYZE) output.

### Bug Fixes

**Fixed a bug in the combination of cross join and the `index_of` function**

Resolved an issue where the `index_of` function would fail when applied to the result of a cross join that produced a single row.

### Breaking Changes

**Temporarily restricted column DEFAULT expressions in CREATE TABLE statements**

Column DEFAULT expressions in CREATE TABLE statements have been temporarily restricted, they can only consist of literals and the following functions: `CURRENT_DATE()`, `LOCALTIMESTAMP()`, `CURRENT_TIMESTAMP()`, `NOW()`. Existing tables with column DEFAULT expressions are not affected.

**Underflow detection while casting from TEXT to floating point data types**

Firebolt now detects underflow, a condition where a numeric value becomes smaller than the minimum limit that a data type can represent, when casting from TEXT to floating point data types. For example, the query `select '10e-70'::float4;` now returns an error, while it previously returned `0.0`.

**Returning query execution errors in JSON format through the HTTP API**

Firebolt's HTTP API now returns query execution errors in JSON format, allowing for future enhancements like including metadata such as error codes, or the location of a failing expression within the SQL script.

**Changed default of case\_sensitive\_column\_mapping parameter in COPY FROM**

The default value for the `CASE_SENSITIVE_COLUMN_MAPPING` parameter in `COPY FROM` is now `FALSE`, meaning that if a target table contains column names in uppercase and the source file to ingest has the same columns in lowercase, the ingestion will consider them the same column and ingest the data.

**`extract` function returns Numeric(38,9) for Epoch, second, and millisecond extraction**

The result data type of the `extract` function for epoch, second, and millisecond was changed to return the type Numeric(38,9) instead of a narrower Numeric type. For example, `select extract(second from '2024-04-22 07:10:20'::timestamp);` now returns Numeric(38,9) instead of Numeric(8,6).

## Firebolt Release Notes - Version 4.2

**July 2024**

### New features

**New `ntile` window function**

Firebolt now supports the `ntile` window function. Refer to our [NTILE](/reference-sql/functions-reference/window/ntile) documentation for examples and usage.

### Enhancements, changes and new integrations

**Improved query performance**

Queries with "`SELECT [project_list] FROM [table] LIMIT [limit]`" on large tables are now significantly faster.

**Updated table level RBAC**

Table level RBAC is now supported by Firebolt. This means that RBAC checks also cover schemas, tables, views and aggregating indexes. Refer to our [RBAC](/guides/security/rbac) docs for a detailed overview of this new feature. The new Firebolt version inhibits the following change:

* System built-in roles are promoted to contain table level RBAC information. This means that new privileges are added to `account_admin`, `system_admin` and `public` roles. The effect is transparent— any user assigned with those roles will not be affected.

**Removal of Deprecated Columns from `INFORMATION_SCHEMA.ENGINES`**

We removed the following columns from `INFORMATION_SCHEMA.ENGINES` that were only for FB 1.0 compatibility: `region`, `spec`, `scale`, `warmup`, and `attached_to`. These columns were always empty. (These columns are hidden and do not appear in `SELECT *` queries, but they will still work if referenced explicitly.)

### Breaking Changes

**Improved rounding precision for floating point to integer casting**

Casting from floating point to integers now uses Banker's Rounding, matching PostgreSQL's behavior. This means that numbers that are equidistant from the two nearest integers are rounded to the nearest even integer:

Examples:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT 0.5::real::int
```

This returns 0.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT 1.5::real::int
```

This returns 2.

Rounding behavior has not changed for numbers that are strictly closer to one integer than to all others.

**JSON functions update**

Removed support for `json_extract_raw`, `json_extract_array_raw`, `json_extract_values`, and `json_extract_keys`. Updated `json_extract` function: the third argument is now `path_syntax`, which is a JSON pointer expression. See [JSON\_EXTRACT](/reference-sql/functions-reference/json/json-extract) for examples and usage.

**Cluster ordinal update**

Replaced `engine_cluster` with [cluster\_ordinal](/reference-sql/information-schema/engine-metrics-history) in `information_schema.engine_metrics_history`. The new column is an integer representing the cluster number.

**Configurable cancellation behavior on connection drop**

Introduced the `cancel_query_on_connection_drop` setting, allowing clients to control query cancellation on HTTP connection drop. Options include `NONE`, `ALL`, and `TYPE_DEPENDENT`. Refer to [system settings](/reference-sql/system-settings#query-cancellation-mode-on-connection-drop) for examples and usage.

**JSON format as default for error output**

The HTTP API now returns query execution errors in JSON format by default. This change allows for the inclusion of meta information such as error codes and the location of failing expressions in SQL scripts.

**STOP ENGINE will drain currently running queries first**

`STOP ENGINE` command now supports graceful drain, meaning any currently running queries will be run to completion. Once all the queries are completed, the engine will be fully stopped and terminated. If you want to stop the engine immediately, you can issue a STOP ENGINE command use the TERMINATE option. For example, to immediately stop an engine, my\_engine, you can use:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
 STOP ENGINE myEngine WITH TERMINATE = TRUE
```

**Scaling engines will not terminate currently running queries**

`ALTER ENGINE` command now supports graceful drain, meaning when you scale an engine (vertically or horizontally), any currently running queries will not be terminated. New queries after the scaling operation will be directed to a new cluster, while queries running on the old cluster will be run to completion.

**Updated RBAC ownership management**

We have introduced several updates to role and privilege management:

* The `security_admin` role will be removed temporarily and re-introduced in a later release.
* `Information_object_privileges` includes more privileges. Switching to to a specific user database (e.g by executing `use database db`) will only show privileges relevant for that database. Account-level privileges no longer show up when attached to a specific database.
* Every newly created user is granted with a `public` role. This grant can be revoked.

## Firebolt Release Notes - Version 4.1

**June 2024**

* [Resolved issues](#resolved-issues)

### Resolved issues

* Fixed an issue causing errors when using `WHERE column IN (...)` filters on external table scans.

## Firebolt Release Notes - Version 4.0

**June 2024**

* [Enhancements, changes, and new integrations](#enhancements-changes-and-new-integrations)
* [Breaking Changes](#breaking-changes)

### Enhancements, changes and new integrations

**Query Cancelation on HTTP Connection Drop**

Going forward, when the network connection between the client and Firebolt is dropped (for example because the Firebolt UI tab was closed or due to network issues), DML queries (INSERT, UPDATE, DELETE, etc) are no longer canceled automatically, but will keep running in the background. You can continue to monitor their progress in `information_schema.engine_running_queries` or cancel them manually using the `cancel query` statement if desired. DQL queries (SELECT) are still canceled automatically on connection drop.

**New Aggregate Functions: `CHECKSUM` and `hash_agg`**

`CHECKSUM` and `hash_agg` functions are now supported for aggregating indexes. Note that when the `hash_agg` function doesn't receive rows, the result is 0.

### Breaking Changes

**Array Casting Nullability Update**

Cast to array will no longer support specifying nullability of the inner type.
Example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
a::array(int null)
```

or

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
cast(a as array(int not null)) 
```

will now fail, and need to be rewritten as:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
a::array(int) 
```

or

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
cast(a as array(int)). 
```

**Postgres-compliant Cast**

Casts now behave the same across the product and adhere to the list of supported casts. Some usages of casts (explicit, implicit, or assignment cast) that were previously allowed are no longer supported and now result in errors. For more details on list of supported casts, see the documentation [here](/reference-sql/data-types#type-conversion).

## Firebolt Release Notes - Version 3.34

**May 2024**

* [Enhancements, changes, and new integrations](#enhancements-changes-and-new-integrations)
* [Resolved issues](#resolved-issues)

### Enhancements, changes and new integrations

**Removed `MATCH` function**

The `match` function has been removed and replaced with [regexp\_like](/reference-sql/functions-reference/string/regexp-like).

**Producing an error for array function failure instead of NULL**

Array function queries that accept two or more array arguments now produce an error. If you call an array function such as `array_transform(..)` or `array_sort(..)` with multiple array arguments, the arrays must have the same size.
For example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
array_transform(x, y -> x + y, arr1, arr2)
```

This raises an error if `array_length(arr1) != array_length(arr2)`. We now also perform this check for NULL literals. If you previously used `array_transform(x, y -> x + y, NULL::INT[], Array[5, 6])`, you got back `NULL`. Now, the query using that expression will raise an error.

**Added ARRAY\_FIRST function**

The [array\_first](/reference-sql/functions-reference/lambda/array-first) function has been added. It returns the first element in the given array for which the given function returns `true`.

**New name for `any_match`**

A new name for `any_match` has been added: [array\_any\_match](/reference-sql/functions-reference/lambda/array-any-match). `any_match` will be kept as an alias.

**Updated ARRAY\_SUM return types**

The `array_sum` function of `bigint[]` now returns a numeric value and `array_sum` of `real[]` returns a real value.

**Precedence of operators**

Breaking change in operator precedence between comparison operators such as `=`, `<`, `>`, and `IS` operator. New behavior is compatible with Postgres.

Examples of query that changed behavior:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
select 1 is null = 2 is null
```

This used to be `true`, because it was interpreted as `select (1 is null) = (2 is null)`. It now becomes an error of incompatible types in `=`

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
select false = false is not null
```

The result used to be `false` - `select false = (false is not null)`, but now is `true` - `select (false = false) is not null`.

**Dropping the role**

Role cannot be dropped if there are permissions granted to the role. The error message will be displayed if you need to manually drop permissions associated to the role.

**Coalesce Short-Circuiting**

`COALESCE` now supports short-circuiting in Firebolt. Queries such as `COALESCE(a, 1 / 0) FROM t` could fail before, even when there were no NULLs in t. Only `CASE WHEN` supported short circuiting. Firebolt is now aligned with PostgreSQL and supports short circuiting in `COALESCE` as well.

**Create table under I\_S schema**

You can now execute `CREATE TABLE`/`VIEW`/`AGGREGATING INDEX` only under the public schema.

**Improved error message for JSON `PARSE_AS_TEXT` format**

The error message for external tables created with JSON `PARSE_AS_TEXT` format has been revised. This format reads specifically into a *single* column of type either TEXT or `TEXT NOT NULL`. (Note there may be external table partition columns defined after the single TEXT column, and they are okay). Now, only the error message regarding the `CREATE EXTERNAL TABLE` statement on a user's first attempt to use `SELECT` will be seen. Support for reading format JSON `PARSE_AS_TEXT=TRUE` into a `TEXT NOT NULL` column has been added.

**Implemented column\_mismatch**

Support for `ALLOW_COLUMN_MISMATCH` in `COPY INTO` has been added.

**Corrected NULL behavior of `STRING_TO_ARRAY`**

The behavior of `string_to_array` now matches its behavior in PostgreSQL. The change affects NULL delimiters where the string is split into individual characters, as well as empty strings and where the output is now an empty array.

**Changed city\_hash behavior for nullable inputs**

The behavior for `city_hash` has changed for nullable inputs.
For example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT CITY_HASH([null]) = CITY_HASH([''])
```

This is now false.

**Function `ARRAY_AGG` now preserves NULLS**

The `array_agg` function has been changed to return PostgreSQL-compliant results:

* `array_agg` now preserves `NULL` values in its input, e.g. `select array_agg(x) from unnest(array [1,NULL,2] x)` returns `{1,NULL,2}`
* `array_agg` now returns `NULL` instead of an empty array if there are no input values

**Lambda parameters are no longer supported by `array_sum`**

Array aggregate functions no longer support lambda parameters. To get the old behavior for conditional lambda functions, use transform instead.
For example:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
array_sum(transform(...))
```

**Explicit Parquet conversion from DATE to INT is now needed**

A breaking change has been implemented in raising an error on reading a Parquet/ORC `DATE`/`TIMESTAMP` column if the `EXTERNAL TABLE` expects the column to have type `INT`/`BIGINT`. `DATE`/`TIMESTAMP` cannot be cast to `INT`/`BIGINT`, and external table scans will no longer allow this cast either. You need to explicitly transform the Parquet/ORC `DATE`/`TIMESTAMP` column with `EXTRACT`(`EPOCH FROM` col) to insert it into an `INT`/`BIGINT` column.

### Resolved issues

* Fixed a bug where negation did not check for overflows correctly.

## Firebolt Release Notes - Version 3.33

**April 2024**

* [Enhancements, changes, and new integrations](#enhancements-changes-and-new-integrations)
* [Resolved issues](#resolved-issues)

### Enhancements, changes and new integrations

**Removed 'element\_at' Function**

The `element_at` function for arrays has been removed and replaced with the `[]` operator.

**Change of return type from BIGINT to INTEGER**

The `index_of`/`array_position` function now returns INTEGER instead of BIGINT.

**Removed LIMIT DISTINCT syntax**

The `LIMIT_DISTINCT` syntax is no longer supported by Firebolt.

**Updated CAST function behavior**

All cast logic has been moved to runtime in Firebolt. The `castColumn` function is now replaced by `fbCastColumn`, ensuring consistent casting behavior and resolving issues with the `COPY FROM` operation and other cast calls. Uses of implicit/explicit `CAST` may result in errors due to this fix.

<Note>
  New breaking change.
</Note>

### Resolved issues

* Fixed a bug in `array_position` where searching for `NULL` in an array with non-null elements incorrectly returned a match in some cases.

## Firebolt Release Notes - Version 3.32

**April 2024**

* [New features](#new-features)
* [Enhancements, changes, and new integrations](#enhancements-changes-and-new-integrations)
* [Resolved issues](#resolved-issues)

### New features

**Expose and document 'typeof' as a toTypeName function**

The `typeof` function has been added, which returns the data type of a SQL expression as a string.

### Enhancements, changes and new integrations

**Spilling Aggregations**

Firebolt can now process most aggregations that exceed the available main memory of the engine by spilling to the SSD cache when needed. This happens transparently to the user. A query that made use of this capability will populate the `spilled_bytes` column in `information_schema.query_history`. Spilling does not support aggregations where a single group exceeds the available memory (e.g., `select count(distinct high_cardinality_column) from huge_table`) and may not yet work reliably for all aggregate functions or engine specs. We will continue improving the feature in upcoming releases.

**No overflow detected in cast from FLOAT to DECIMAL**

Fix results of casting from `float32` to decimals with precision > 18.
In addition to the correct results breaking change, there are certain queries that was working before that now will fail involving overflow.

Example query:

* `SELECT` 17014118346046923173168730371588410572::REAL::DECIMAL(37,0).

Previously, this was working and returned a wrong result, but now it will fail with an overflow error.

**ARRAY\_COUNT returns 0 instead of NULL**

`ARRAY_COUNT` on `NULL` array now returns `0` instead of `NULL`.

**No overflow check in arithmetic operations**

Arithmetic operators (i.e. multiplication, addition, subtraction, and division) now perform correct overflow checking. This means that queries that used to return wrong results in the past now throw runtime errors.

Example queries:

* `SELECT` 4294967296 \* 4294967296 -> now throws an error, before it would return 0

* `SELECT` 9223372036854775807 + 9223372036854775807 -> now throws an error, before it would return -2

* `SELECT` (a + b) \* c -> this might throw runtime errors if there are large values in the column, but this is highly data dependent.

**Implement bool\_or/bool\_and aggregate functions**

New aggregate functions bool\_or and bool\_and have been added.

**Remove old deprecate REGENERATE AGGREGATING INDEX**

'REGENERATE AGGREGATING INDEX' syntax has now been removed.

**Align the syntax of our "escape" string literals with PostgreSQL**

Escape [string literals](/reference-sql/data-types) now support octal and Unicode escape sequences. As a result, escape string literals now behave exactly like PostgreSQL. Example: `SELECT E'\U0001F525b\x6F\154t';` returns `🔥bolt`. If the setting `standard_conforming_strings` is not enabled for you, regular string literals (e.g., `SELECT 'foo';`) will also recognize the new escape sequences. However, we recommend exclusively using escape string literals for using escape sequences. Please be aware that you will get different results if you previously used (escape) string literals containing the syntax we now use for Unicode and octal escape sequences.

**Change return value of length and octet\_length to INT**

Length and array\_length now return INTEGER instead of BIGINT.

**Subqueries in the GROUP BY/HAVING/ORDER BY clauses change**

Subqueries in `GROUP BY/HAVING/ORDER BY` can no longer references columns from the selection list of the outer query via their aliases as per PG compliance.  `select 1 + 1 as a order by (select a);` used to work, but now fails with `unresolved name a` error.

**Bytea serialization to CSV fix**

Changed Bytea to CSV export: from escaped to non escaped.

Example:

* `COPY` (select 'a'::bytea) to 's3...'; the results will now be "\x61" instead of "\x61".

### Resolved issues

* Fixed results of casting literal float to numeric. In the past the float literal was casted to float first then to numeric, this caused us to lose precision.

Examples:

* `SELECT` 5000000000000000000000000000000000000.0::DECIMAL(38,1); -> 5000000000000000000000000000000000000.0
* `SELECT` (5000000000000000000000000000000000000.0::DECIMAL(38,1)+5000000000000000000000000000000000000.0::DECIMAL(38 1)); -> ERROR: overflow.

Note that before, it was not an error and resulted in: 9999999999999999775261218463046128332.8.

* Fixed a longstanding bug with >= comparison on external table source\_file\_name. Whereas this would previously have scraped fewer files than expected off the remote S3 bucket, you will now get all files properly (lexicographically) compared against the input predicate.

* Fixed a bug when `USAGE ANY ENGINE` (and similar) privileges were shown for \* account. Now it is being show for current account.

* Fixed a bug involving ['btrim'](/reference-sql/functions-reference/string/btrim) string characters, where invoking `btrim`, `ltrim`, `rtrim`, or `trim` with a literal string but non-literal trim characters could result in an error.

## Firebolt Release Notes - Version 3.31

**March 2024**

* [New features](#new-features)
* [Enhancements, changes, and new integrations](#enhancements-changes-and-new-integrations)
* [Resolved issues](#resolved-issues)

### New features

**PG compliant division**

LQP2 has a new division operator that is PG compliant, by default.

**Prevents usage of new line delimiter for schema inference**

An error will now occur if schema inference is used with the option “delimiter” set to something other than the default.

### Enhancements, changes and new integrations

**Simplified table protobuf representation**

Unique constraints in tables will be blocked for new accounts.

**Support for nullable arrays**

Support has been added to allow the [ARRAY\_ANY\_MATCH](/reference-sql/functions-reference/lambda/array-any-match) lambda function to work with nullable arrays.

**Updated AWS billing error message**

The error message for an AWS billing issue on Engine Start was on Engine Start was changed to add more information and clarity.

**New requirements updated for EXPLAIN**

For `EXPLAIN` queries, we now allow only one of the following options at the same time: `ALL`, `LOGICAL`, `PHYSICAL`, `ANALYZE`.`EXPLAIN (ALL)` now returns the plans in multiple rows instead of multiple columns.

**Disabled Unix Time Functions**

The following functions are not supported anymore:
'from\_unixtime'
'to\_unix\_timestamp'
'to\_unix\_time'

**Renamed spilled metrics columns**

The columns `spilled_bytes_uncompressed` and `spilled_bytes_compressed` of `information_schema.query_history` have been replaced by a single column `spilled_bytes`. It contains the amount of data that was spilled to disk temporarily while executing the query.

**Aggregating index placement**

Aggregating index is now placed in the same namespace as tables and views.

**Syntax and planner support for LATERAL scoping**

[LATERAL](/reference-sql/lexical-structure/reserved-words) is now a reserved keyword. It must now be used within double-quotes when using it as an object identifier.

### Resolved issues

Changed return for division by 0 from null to fail.

Updated error log for upload failure for clarity.

Fixed a bug in 'unnest' table function that occurred when not all of the 'unnest' columns were projected.

Changed the behavior of [split\_part](/reference-sql/functions-reference/string/split-part) when an empty string is used as delimiter.

Fixed a bug where floating point values `-0.0` and `+0.0`, as well as `-nan` and `+nan` were not considered equal in distributed queries.

TRY\_CAST from TEXT to NUMERIC now works as expected: if the value cannot be parsed as NUMERIC it produces null.

## Firebolt Release Notes - Version 3.30

**November 2023**

* [New features](#new-features)
* [Enhancements, changes, and new integrations](#enhancements-changes-and-new-integrations)
* [Resolved issues](#resolved-issues)

### New features

**New comparison operators**

[New comparison operators](/reference-sql/lexical-structure/operators) `IS DISTINCT FROM` and `IS NOT DISTINCT FROM` have been added.

### Enhancements, changes and new integrations

**Support for nullable arrays**

Support has been added to allow the ANY\_MATCH lambda function to work with nullable arrays

### Resolved issues

* Indirectly granted privileges have been removed from the `information_schema.object_privileges` view.

* Fixed an issue where `ARRAY_FIRST` and `ARRAY_FIRST_INDEX` returned an error if the given input was nullable.

## Firebolt Release Notes - Version 3.29

**October 2023**

* [New features](#new-features)
* [Enhancements, changes, and new integrations](#enhancements-changes-and-new-integrations)

### New features

**EXPLAIN ANALYZE now available for detailed query metrics**

You can now use the [EXPLAIN command](/reference-sql/commands/queries/explain) to execute `EXPLAIN (ANALYZE) <select statement>` and get detailed metrics about how much time is spent on each operator in the query plan, and how much data is processed. The query plan shown there is the physical query plan, which you can inspect using `EXPLAIN (PHYSICAL) <select statement>` without executing the query. It shows how query processing is distributed over the nodes of an engine.

### Enhancements, changes and new integrations

**Virtual column 'source\_file\_timestamp' uses new data type**

The virtual column `source_file_timestamp` has been migrated from the data type `TIMESTAMP` (legacy timestamp type without time zone) to the type `TIMESTAMPTZ` (new timestamp type with time zone).

Despite the increased resolution, the data is still in second precision as AWS S3 provides them only as unix seconds.

Use `source_file_timestamp - NOW()` instead of `DATE_DIFF('second', source_file_timestamp, NOW())`

**New function added**

A new alias [ARRAY\_TO\_STRING](/reference-sql/functions-reference/array/array-to-string) has been added to function `ARRAY_JOIN`.

## Firebolt Release Notes - Version 3.28

**September 2023**

* [Resolved issues](#resolved-issues)

### Resolved issues

* `IN` expressions with scalar arguments now return Postgres-compliant results if there are `NULL`s in the `IN` list.

* information\_schema.running\_queries returns ID of a user that issued the running query, not the current user.

* Update error message to explain upper case behavior