Tsurugi 1.10.0 - Release Notes (en)

[akirakw]

1.10.0 Release Notes

Overview

Tsurugi 1.10.0 adds support for scalar subqueries, the EXISTS operator, and subqueries in the IN clause as new SQL features.
These features allow a wider range of queries required for practical application development to be expressed.

In addition to the above SQL enhancements, this version also adds support for extended use of the VALUES clause (table value constructor) and SELECT statements without a FROM clause.
These features make it easier to work with data that does not depend on any specific table.

This version also expands the client library ecosystem by introducing Tsurugi Python DB-API, a client library compliant with Python DB-API 2.0 (PEP 249), the standard database connectivity API for Python.

• https://tsurugi-dbapi.readthedocs.io
• https://pypi.org/project/tsurugi-dbapi

Tsurugi Python DB-API enables easy integration with Python's rich data analysis and processing ecosystem.
In addition to standard database operations, it supports Tsurugi-specific transaction options, allowing full use of Tsurugi's unique capabilities.

This version also includes several important bug fixes and stability improvements.
We strongly recommend all users of previous versions to upgrade to this release.

New Features and Improvements

New Features and Improvements - SQL

• Added support for scalar subqueries
  • A scalar subquery is a subquery that returns a single value (a single row and a single column). You can now write a subquery anywhere a single value (scalar value) can be specified, such as in the SELECT clause or WHERE clause.

    SELECT
      order_id,
      amount,
      amount - (SELECT AVG(amount) FROM orders) AS diff_from_average
    FROM orders

  • Note: Correlated subqueries are not supported in this version. See the NOTE below for details.
• Added support for the EXISTS operator
  • The EXISTS operator is used to check whether a subquery returns one or more rows. You can now write a subquery existence condition in the WHERE clause and similar positions.

    SELECT
      order_id
    FROM
      orders
    WHERE
      EXISTS (
        SELECT *
        FROM warehouse_status
        WHERE is_open = TRUE
      )

  • Note: Correlated subqueries are not supported in this version. See the NOTE below for details.
• Added support for subqueries in the IN clause
  • You can now write a subquery inside an IN clause, allowing the results of the subquery to be used as the condition in an IN clause.

    SELECT
      order_id,
      amount
    FROM
      orders
    WHERE
      customer_id IN (
        SELECT customer_id
        FROM customers
        WHERE country_code = 'JP'
      )

  • Note: Correlated subqueries are not supported in this version. See the NOTE below for details.
  • Note: In this version, there are restrictions such as IN subqueries cannot be used as operands of the OR operator, and they cannot be used inside NOT IN or ALL operators. See the document referenced in the NOTE below for details.
• Extended the VALUES clause (table value constructor)
  • Previously, the VALUES clause was only available as part of an INSERT statement. In this version, the VALUES clause can now be used as a table value constructor in any position.
  • The VALUES clause can return a single value, multiple columns, or multiple rows as a "table". This allows returning arbitrary tabular data without specifying a table, or calling functions and UDFs.

    -- Returns a single value
    VALUES (1)
    
    -- Returns multiple rows and multiple columns
    VALUES (1, 'hello', 100), (2, 'world', 200), (3, 'foo', 300)
    
    -- Calls a function
    VALUES (CURRENT_TIMESTAMP)
    
    -- Calls a UDF
    VALUES (Greeter('hello'))

  • In most cases, however, you may find a SELECT statement without a FROM clause (described in the next item) more natural to use.
• Added support for SELECT statements without a FROM clause
  • Previously, a FROM clause was required in every SELECT statement. In this version, SELECT statements without a FROM clause are supported. Internally, when the FROM clause is omitted, a VALUES clause that generates a table with one row and zero columns is implicitly added.

    -- Equivalent to VALUES (1)
    SELECT 1
    
    -- Equivalent to VALUES (Greeter('hello'))
    SELECT Greeter('hello')

Note

For detailed specifications and current restrictions on SQL features, see the following document in tsurugidb:

• Available SQL features in Tsurugi

Note

This version does not support correlated subqueries, which reference columns of the outer query from within a subquery.
Note that scalar subqueries and the EXISTS operator used in the SELECT clause are often used as correlated subqueries.

Support for correlated subqueries is planned for Tsurugi 1.11.0.

New Features and Improvements - Client

• Tsurugi Python DB-API
  • Tsurugi Python DB-API is a client library for Tsurugi that complies with Python DB-API 2.0 (PEP 249), the standard database API for Python. In addition to standard database operations, it supports Tsurugi-specific transaction options, enabling the use of various Tsurugi features from Python.
  • Tsurugi Python DB-API provides API references, user guides, and more at the following documentation site:
    • https://tsurugi-dbapi.readthedocs.io
  • Tsurugi Python DB-API is available on PyPI:
    • https://pypi.org/project/tsurugi-dbapi

New Features and Improvements - UDF

• Improved User-Defined Table-valued Functions (APPLY operator)
  • Optimized SQL execution scheduling when calling User-Defined Table-valued Functions via the APPLY operator — the Tsurugi SQL extension syntax for invoking UDTFs.
  • By performing scheduling asynchronously within the SQL execution engine, resolved an issue where processing of other jobs could rarely stall during APPLY execution. Stability during highly intensive processing and large-volume data processing has been significantly improved.
• Enhanced validation and stabilization of UDF plugin initialization at Tsurugi startup
  • Added a validation feature that detects UDF plugin constraint violations at Tsurugi startup, such as duplicate gRPC method names or duplicate message names that can occur when multiple UDF plugins are deployed.
  • Previously, such constraint violations could cause Tsurugi to start in an invalid state and fail with a startup error. In this version, depending on the nature of the constraint violation, UDF plugins are disabled either entirely or individually, stabilizing the Tsurugi startup process.
    • In connection with this change, a protocol definition file (.desc.pb) has been added to the UDF plugin configuration definition file, which must be deployed. For details, see:
      • udf-plugin - UDF Plugin Configuration (ja)
  • UDF plugins generated with Tsurugi 1.9.0 or earlier cannot be used with Tsurugi 1.10.0. You must regenerate and redeploy UDF plugins using udf-plugin-builder.

Bug Fixes

Bug Fixes - SQL

• Fixed an issue where BETWEEN operators, IN operators, or similar operators applied to columns aggregated by GROUP BY clauses or aggregate functions could result in an invalid error.
• Fixed an issue where the table name included in transaction conflict exception messages was displayed as a surrogate ID.

Bug Fixes - Transaction

• Fixed an issue where, when another OCC transaction performs an INSERT within the scan range of an OCC transaction, the commit could succeed without detecting a Serializable violation under certain conditions.

Upgrade Notes

Client Compatibility List

Tsurugi 1.10.0 supports the following clients.
Please upgrade each client as needed when upgrading Tsurugi.

Client	Version
Tsubakuro/Java	1.13.0+
Iceaxe	1.13.0+
Tanzawa	1.13.0+
Tsubakuro/Rust	0.7.0+
Tsurugi MCP Server	0.5.0+
Tsurugi JDBC	0.2.0+
Tsurugi UDF	0.3.0+

Important

For Tsurugi UDF, you must regenerate and redeploy UDF plugins using udf-plugin-builder.

Upgrade Procedure

For instructions on upgrading from previous versions, see:

• Tsurugi Upgrade Guide

Other Information

For a full list of changes in this version, see the Changelog:

• Tsurugi 1.10.0 - Changelog

For known issues and additional information about fixes in this version, see:

• Tsurugi 1.10.0 - Known Issues(en)

---

This discussion was created from the release 1.10.0.