Link Search Menu Expand Document

Release notes archive

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

DB version 3.32

April 2024

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 aggregation 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 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’ string characters, where invoking btrim, ltrim, rtrim, or trim with a literal string but non-literal trim characters could result in an error.

DB version 3.31

March 2024

New features

PG compliant division

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

Prevents usage of new line delimeter 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 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(../sql_reference/information-schema/query-history-view.md). 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 is now a reserved keyword (../Reference/reserved-words.md). 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(../sql_reference/functions-reference/string/split-part.md) 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.

DB version 3.30

November 2023

New features

New comparison operators

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

DB version 3.29

October 2023

New features

EXPLAIN ANALYZE now available for detailed query metrics

You can now use the EXPLAIN command 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

PERCENTILE_CONT and PERCENTILE_DISC now return PostgreSQL-compliant results

PERCENTILE_CONT for decimal input now returns DOUBLE PRECISION instead of NUMERIC data type.

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 has been added to function ARRAY_JOIN.

DB version 3.28

September 2023

Resolved issues

  • IN expressions with scalar arguments now return Postgres-compliant results if there are NULLs 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