Release 0.300

Breaking Changes

  • Remove support for the ANSI SQL syntax of the trim function, such as TRIM(BOTH ' ' FROM x), which was added in 0.299 and made TRIM, BOTH, LEADING, and TRAILING reserved keywords. Use the trim, ltrim, and rtrim functions instead. #28334

  • Upgrade hudi-presto-bundle to version 1.1.0. NOTE: Partitioned Hudi 1.x tables require hive.parquet.use-column-names=true for correct column resolution. Without this setting, columns are read positionally and may return incorrect data. #27274

Highlights

  • Add support for the WINDOW clause, so a window specification can be named once and reused, for example SELECT rank() OVER w FROM t WINDOW w AS (PARTITION BY a ORDER BY b). #28471

  • Add support for declaring field names in a row constructor, for example ROW(1 AS a, 2 AS b). VALUES derives column names from the declared field names, so SELECT a FROM (VALUES ROW(1 AS a)) resolves. #28298

  • Add an optional FIRST or AFTER <column> clause to ALTER TABLE ... ADD COLUMN to control where the new column is placed. Omitting the clause appends the column, which is the previous behavior. FIRST and AFTER require connector support. #28328

  • Add ALTER TABLE ... ALTER COLUMN <column> FIRST | AFTER <column> to move an existing column within a table’s column order. Reordering columns requires connector support. #28349

  • Add support for reading Iceberg VARIANT columns as JSON columns. #28469

Details

General Changes

  • Fix REFRESH MATERIALIZED VIEW analysis to reject unsupported predicates nested within an OR. #28570

  • Fix SHOW CREATE MATERIALIZED VIEW failure for materialized views created with materialized-view-only properties. #28390

  • Fix SHOW CREATE TABLE displaying connector-internal hidden table properties. #28123

  • Fix ST_Centroid to return NULL for empty geometries according to the ISO spec. #26971

  • Fix a query failure that could occur during planning for some queries with a distinct aggregation over an inner join. #28231

  • Fix failures when using an offset RANGE frame with ORDER BY inherited from a named window. See Named WINDOW Clause. #28517

  • Fix fully qualified storage table names in materialized view queries. #28423

  • Fix query planning failure with dynamic filtering enabled when using scalar subqueries with min/max/count aggregation functions. #28392

  • Fix query runtime metrics being dropped for tables and materialized views referenced through a view. #28367

  • Fix the materialized view rewrite of a query that uses a window function or a WINDOW clause. #28473

  • Improve accuracy of history-based optimization statistics by recording them only from finished stages, skipping plan nodes which produced no output rows, and removing statistics of plan nodes of failed stages. #28356

  • Improve aggregation pushdown below an outer join when table constraints, enabled with the exploit_constraints session property, show that the outer side of the join is distinct. #28425

  • Improve property derivation to keep the probe side’s local properties across a cross join whose build side produces at most one row, instead of discarding them. Operators above such a join, such as window functions and streaming aggregations, can now use the order of the probe input. #28371

  • Improve query planning performance by resolving window-value functions lazily during expression optimization. #28265

  • Improve the optimize_join_fan_out optimization to collapse the null-supplying side of an outer join, and to detect the fan-out from the grouping a join input reports rather than from a fixed plan shape. #28301

  • Add xxhash128() function to compute the 128-bit XXH3 hash. <https://github.com/prestodb/presto/pull/28289>`_

  • Add support for derived columns in CREATE TABLE and ALTER TABLE, including in SHOW CREATE TABLE output. Derived columns require connector support. #28123

  • Add ALTER TABLE ... ALTER COLUMN <column> FIRST | AFTER <column> to move an existing column within a table’s column order. Reordering columns requires connector support. #28349

  • Add spark.max-splits-count-per-partition configuration property and max_splits_count_per_spark_partition session property to bound the number of splits assigned to a single Spark input partition. Unbounded by default. #28326

  • Add an optional FIRST or AFTER <column> clause to ALTER TABLE ... ADD COLUMN to control where the new column is placed. Omitting the clause appends the column, which is the previous behavior. FIRST and AFTER require connector support. #28328

  • Add opt-in coordinator query tracing to runtime statistics with the runtime_stats_tracing_enabled session property. #28417

  • Add session property rewrite_approx_distinct_if_to_mask and configuration property optimizer.rewrite-approx-distinct-if-to-mask, disabled by default, which rewrites approx_distinct(IF(p, e)) to approx_distinct(e) masked by p. #28363

  • Add support for declaring field names in a row constructor, for example ROW(1 AS a, 2 AS b). VALUES derives column names from the declared field names, so SELECT a FROM (VALUES ROW(1 AS a)) resolves. #28298

  • Add support for loading expression optimizers on the Presto on Spark driver from configuration supplied at startup, for deployments that have no etc/expression-manager/ directory on local disk. Previously such deployments could register an expression optimizer factory but never instantiate it. #28393

  • Add support for the WINDOW clause, so a window specification can be named once and reused, for example SELECT rank() OVER w FROM t WINDOW w AS (PARTITION BY a ORDER BY b). #28471

  • Remove support for the ANSI SQL syntax of the trim function, such as TRIM(BOTH ' ' FROM x), which was added in 0.299 and made TRIM, BOTH, LEADING, and TRAILING reserved keywords. Use the trim, ltrim, and rtrim functions instead. #28334

Prestissimo (Native Execution) Changes

  • Fix Arrow connector column projection when there are duplicate column names. #28462

  • Fix NOT NULL column constraints being ignored for table writes, which allowed an INSERT to silently write NULL into a NOT NULL column. #28450

  • Fix the propagation of structured execution failure information from the Flight shim server through the Arrow Flight federation connector back to the Presto coordinator, retaining original exception type and error code. #28126

  • Fix a native worker crash when a table handle has no table layout. The query now fails with an error instead of stopping the worker process. #28341

  • Add asyncDataCacheBytes and queryMemoryBytes to the native worker /v1/status endpoint to report async data cache memory and query memory separately. #27783

  • Add the exchange.materialization.use-zero-copy-collect configuration property to allow chain-capable shuffle backends to avoid an intermediate coalescing copy. This property is enabled by default and can be set to false to restore the contiguous path. #27981

  • Add sidecar.retry.max-failure-interval exponential-backoff retry configuration property for all native sidecar HTTP calls so that transient sidecar failures are retried for up to 5 seconds before propagating as a query failure. See Retry configuration. #28429

  • Add session property native_abandon_partial_topn_row_number_min_pct which sets the percentage of accumulated input rows still retained at or above which the partial TopNRowNumber operator is abandoned. Defaults to 80, the existing Velox default, so behavior is unchanged unless set. #28314

  • Add session property native_abandon_partial_topn_row_number_min_rows which sets the number of input rows the partial TopNRowNumber operator accumulates before checking whether to abandon it. Defaults to 100000, the existing Velox default, so behavior is unchanged unless set. #28314

  • Add session property native_rpc_ratelimiter_hard_limit to set a session-level ceiling for native RPC admission, and allow the adaptive RPC rate limiter to admit fewer than one request at a time so that a distributed query does not overload a backend service. Defaults to -1, which keeps the existing native_rpc_ratelimiter_max_limit behavior. #28573

Security Changes

JDBC Connector Changes

  • Add caching of table and column metadata within a transaction for JDBC-based connectors, which reduces query planning latency. Enabled by default, and controlled by the metadata-transaction-cache-enabled and metadata-transaction-cache-maximum-size configuration properties. #28402

Elasticsearch Connector Changes

  • Fix query failures with Not in GZIP format when Elasticsearch returns compressed responses. #28472

  • Upgrade elasticsearch-java to 9.5.3. #28472

Hive Connector Changes

  • Upgrade hudi-presto-bundle to version 1.1.0. NOTE: Partitioned Hudi 1.x tables require hive.parquet.use-column-names=true for correct column resolution. Without this setting, columns are read positionally and may return incorrect data. #27274

Hudi Connector Changes

  • Upgrade hudi-presto-bundle to version 1.1.0. #27274

Iceberg Connector Changes

  • Fix reading nested fields of TIMESTAMP WITH TIME ZONE, TIME, and UUID types from Iceberg Parquet tables when nested dereference pushdown is enabled. #28512

  • Fix Invalid view JSON error when listing or querying views in an Iceberg REST catalog that were created by a non-Presto engine such as Netezza, Spark, or Trino. #28321

  • Fix Class com.facebook.presto.hive.s3.PrestoS3FileSystem not found failure when reading a newly registered Iceberg table stored in S3 with iceberg.pushdown-filter-enabled enabled. #28486

  • Fix table statistics ignoring a filter that was pushed into the table scan, which made the estimated row count that of the whole table and could lead to a worse plan. #28549

  • Fix Table already exists error when creating materialized views with the same name in different schemas while iceberg.materialized-view-default-storage-schema is configured. See Storage. #28438

  • Add presto.derived-columns.spec.json table property, to store derived column specs as embedded JSON. #28123

  • Add support for ALTER TABLE ... ALTER COLUMN <column> FIRST | AFTER <column>, which moves an existing column within the table’s column order. #28349

  • Add support for reading Iceberg VARIANT columns as JSON columns. #28469

  • Add support for the FIRST and AFTER <column> clauses of ALTER TABLE ... ADD COLUMN, which control where the new column is placed in the table’s column order. #28328

  • Add support for keeping derived column metadata in sync when columns are added, dropped, renamed, or have their type changed. #28123

Lance Connector Changes

  • Fix SHOW TABLES and SHOW SCHEMAS silently returning only the first page of results for catalogs larger than the namespace server’s page size. #28267

Oracle Connector Changes

  • Fix the Oracle connector ignoring per-session user-credential-name and password-credential-name extraCredentials, always using the static connection-user and connection-password instead. #28351

Prometheus Connector Changes

Verifier Changes

  • Fix function call substitution failing when the substitute declares more window fields than the call it matched. This is now treated as a non-match. #28473

  • Fix function call substitution silently dropping a named window. A substitution is no longer applied to a call whose window refers to a name declared in a WINDOW clause, because the substitution cannot see the partitioning, ordering or frame that name carries. #28473

  • Improve SCHEMA_MISMATCH failure reports to identify the columns that differ. #28440

  • Add validation rejecting a function call substitute whose window refers to a named window, for example rank() over w. A substitute is parsed on its own, so the name has nothing to resolve against. #28473

SPI Changes

  • Add setColumnPosition to ConnectorMetadata, which moves an existing column within a table’s column order. The default implementation reports that the connector does not support moving columns. #28349

  • Add an addColumn overload to ConnectorMetadata that takes a ColumnPosition. The default implementation delegates to the existing three-argument addColumn when the column is appended, so connectors need no changes; override it to support the FIRST and AFTER clauses. #28328

Documentation Changes

Credits

Aditi Pandit, Adrian Carpente (Denodo), Ajay Kharat, Amit Dutta, Anant Aneja, Andrii Rosa, Apurva Kumar, Arjun Gupta, Auden Woolfson, Bryan Cutler, Chandrakant Vankayalapati, Chandrashekhar Kumar Singh, Christian Zentgraf, Deepak Majeti, Deepak Mehra, Deepthi Bose, Denis Krivenko, Dilli Babu Godari, Dong Wang, Ge Gao, Hani Damlaj, Harsh Chokshi, Jacob GLZ, Jalpreet Singh Nanda, Jay Narale, Jeremy G, Jianjian Xie, Joe Abraham, Julian Rossi, KNagaVivek, Kevin Tang, Luis Garcés-Erice, Maria Basmanova, Matt Karrmann, Matthias Braun, Nandakumar Balagopal, Natasha Sehgal, Naveen Mahadevuni, Nishitha K Bhaskaran, Nivin C S, Prashant Sharma, Pratik Joseph Dabre, Pratyaksh Sharma, Reetika Agrawal, RindsSchei225e, Sayari Mukherjee, Shahim Sharafudeen, Shakyan Kushwaha, Shreya, Shrinidhi Joshi, Sreeni Viswanadha, Steve Burnett, Timothy Meehan, Tirumala Saiteja Goruganthu, XiaoDu, Yabin Ma, Yihong Wang, Ying, abhash, abhinavmuk04, adheer-araokar, bibith4, carlnayak, dependabot[bot], jkhaliqi, karthikchundi-commits, mohsaka, zhichenxu-meta