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!
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/h2-database/e773ee06-9b7b-4ed9-94c6-e76a56e1f5d8n%40googlegroups.com.
