[Question] Auto-generated aliases for derived tables (_<number>) and plan text stability (H2 2.4.240)

[Aleksandr Chesnokov]

Hi,

I have a question about auto-generated aliases for derived tables / subqueries in H2.

We tested this behavior on H2 version 2.4.240. In EXPLAIN output, H2 generates aliases like "_12345" for derived tables, even if no explicit alias is defined in the SQL query. From reading the source code, it looks like these names are generated using a session-level counter (SessionLocal#getNextSystemIdentifier), so the number depends on session state and is not tied to the SQL text.

As a result, identical queries may produce different textual plans across executions.

Could you please confirm that this is expected behavior and considered an internal implementation detail?

Also, is there any recommended way to make these internal aliases deterministic (or stabilize textual plan output), or is post-processing the plan text the only practical option?

Reproducer (Java):

package org.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class H2Reproducer {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";
        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {
            Statement stmt = conn.createStatement();

            stmt.execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50));");
            stmt.execute("INSERT INTO users VALUES(1, 'Alice'), (2, 'Bob');");

            String sql = "SELECT id, name FROM (SELECT id, name FROM users) WHERE id = 1";
            System.out.println("SQL: " + sql);

            for (int i = 0; i < 1_000; ++i) {
                ResultSet rs = stmt.executeQuery("EXPLAIN " + sql);
                while (rs.next()) {
                    System.out.println("PLAN: " + rs.getString(1));
                }
            }
        }
    }
}

Thank you!

[Aleksandr Chesnokov]

Just to give you a bit more details:

1) The reproducer prints EXPLAIN output like this (note the derived table alias):

PLAN: SELECT
    "ID",
    "NAME"
FROM (
    SELECT
        "ID",
        "NAME"
    FROM "PUBLIC"."USERS"
) "_973"
    /* SELECT
        ID,
        NAME
    FROM PUBLIC.USERS
        /* PUBLIC.PRIMARY_KEY_4: ID IS NOT DISTINCT FROM ?1 */
    WHERE ID IS NOT DISTINCT FROM ?1: ID = 1
     */
WHERE "ID" = 1

2) The same behavior is also reproduced for older H2 versions.

суббота, 14 февраля 2026 г. в 17:27:00 UTC+3, Aleksandr Chesnokov:

[Evgenij Ryazanov]

Hi!

The SQL Standard doesn't allow derived tables with neither correlation names nor (not supported by H2) recognition clauses, so your original SQL isn't Standard-compliant.

Some database systems forbid such SQL, some others allow it, but correlation name is still required and H2 generates it in that way. If such generation creates problems for you, add some correlation name by yourself to the original query.