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.0SELECT
(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
, ortrim
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
andARRAY_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 areNULL
s in theIN
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