Apache Calcite跨库Join查询报“Multiple entries with same key”错误排查

错误详情 #

Error executing query: Error while executing SQL "EXPLAIN PLAN FOR SELECT c.customer_name, o.order_id, o.order_date FROM CRDB.customers c JOIN H2DB.orders o ON c.customer_id = o.customer_id": Multiple entries with same key: primary=JdbcTable {primary} and primary=JdbcTable {primary}

问题背景 #

尝试使用Apache Calcite执行跨CockroachDB和H2的SQL Join查询，该查询直接在对应数据库执行正常，但通过Calcite运行时触发上述错误。

相关Java代码 #

// Initialize Calcite connection with case-sensitive settings
Properties info = new Properties();
info.setProperty("lex", Lex.MYSQL.name());
info.setProperty("caseSensitive", "true");
Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();

// Connect to CockroachDB
org.postgresql.ds.PGSimpleDataSource cockroachDS = new org.postgresql.ds.PGSimpleDataSource();
cockroachDS.setUrl("jdbc:postgresql://localhost:26257/sample");
cockroachDS.setUser("root");
cockroachDS.setPassword("");

// Connect to H2
org.h2.jdbcx.JdbcDataSource h2DS = new org.h2.jdbcx.JdbcDataSource();
h2DS.setURL("jdbc:h2:testdata;AUTO_SERVER=TRUE");
h2DS.setUser("");
h2DS.setPassword("");

// Add schemas
rootSchema.add("CRDB", 
    JdbcSchema.create(rootSchema, "CRDB", cockroachDS, 
        "sample",  // catalog
        "public")); // schema

rootSchema.add("H2DB", 
    JdbcSchema.create(rootSchema, "H2DB", h2DS,
        "DEFAULT",  // catalog
        "PUBLIC")); // schema

// Execute join query with simplified schema references
String sql = 
    "SELECT c.customer_name, o.order_id, o.order_date " +
    "FROM CRDB.customers c " +
    "JOIN H2DB.orders o ON c.customer_id = o.customer_id";

try (Statement statement = calciteConnection.createStatement()) {
    System.out.println("Executing query: " + sql);
    
    // Enable debug logging
    statement.execute("EXPLAIN PLAN FOR " + sql);
    ResultSet explainRs = statement.getResultSet();
    System.out.println("\nQuery Plan:");
    while (explainRs.next()) {
        System.out.println(explainRs.getString(1));
    }
    
    // Execute actual query
    ResultSet rs = statement.executeQuery(sql);

    // Print results
    System.out.println("\nQuery Results:");
    while (rs.next()) {
        System.out.printf("Customer: %s, Order ID: %s, Date: %s%n",
            rs.getString(1),
            rs.getString(2),
            rs.getString(3));
    }
} catch (SQLException e) {
    System.err.println("Error executing query: " + e.getMessage());
    e.printStackTrace();
}

// Clean up
connection.close();

补充信息 #

• Calcite版本：1.38.0
• CockroachDB版本：24.*
• H2数据库版本：2.3
• Java版本：21

已尝试操作 #

• 验证customers和orders表在各自数据库中存在；
• 确认两个数据库的连接信息正确；
• 直接在两个数据库上运行查询，结果正常。

问题原因 #

该错误源于Calcite处理不同JDBC Schema的表时，内部元数据映射出现重复键冲突。CockroachDB和H2的表被加载后，Calcite默认生成的表标识符（如primary）未带上所属Schema的命名空间前缀，导致两个来自不同数据源的表对象使用了相同的键，触发重复键异常。

解决方法 #

核心是让Calcite能够区分不同Schema下的表，避免键冲突，可通过以下方式实现：

1. 调整Calcite连接配置，关闭不必要的大小写敏感或物化视图特性；
2. 创建JdbcSchema时移除可能导致冲突的Catalog参数，让Calcite自动处理命名空间；
3. 优先执行实际查询，避免EXPLAIN PLAN提前触发元数据冲突。

修正后的代码 #

// Initialize Calcite connection with adjusted settings
Properties info = new Properties();
info.setProperty("lex", Lex.MYSQL.name());
info.setProperty("caseSensitive", "false"); // 关闭大小写敏感，适配H2与CockroachDB的表名大小写差异
info.setProperty("materializationsEnabled", "false"); // 禁用物化视图，减少元数据冲突可能
Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();

// Connect to CockroachDB
org.postgresql.ds.PGSimpleDataSource cockroachDS = new org.postgresql.ds.PGSimpleDataSource();
cockroachDS.setUrl("jdbc:postgresql://localhost:26257/sample");
cockroachDS.setUser("root");
cockroachDS.setPassword("");

// Connect to H2
org.h2.jdbcx.JdbcDataSource h2DS = new org.h2.jdbcx.JdbcDataSource();
h2DS.setURL("jdbc:h2:testdata;AUTO_SERVER=TRUE");
h2DS.setUser("");
h2DS.setPassword("");

// Add schemas - 移除Catalog参数，避免跨数据源的Catalog名称冲突
rootSchema.add("CRDB", 
    JdbcSchema.create(rootSchema, "CRDB", cockroachDS, 
        null,  // 不指定Catalog
        "public")); 

rootSchema.add("H2DB", 
    JdbcSchema.create(rootSchema, "H2DB", h2DS,
        null,  // 不指定Catalog
        "PUBLIC")); 

// Execute join query
String sql = 
    "SELECT c.customer_name, o.order_id, o.order_date " +
    "FROM CRDB.customers c " +
    "JOIN H2DB.orders o ON c.customer_id = o.customer_id";

try (Statement statement = calciteConnection.createStatement()) {
    System.out.println("Executing query: " + sql);
    
    // 优先执行实际查询，避免EXPLAIN提前触发元数据冲突
    ResultSet rs = statement.executeQuery(sql);

    // Print results
    System.out.println("\nQuery Results:");
    while (rs.next()) {
        System.out.printf("Customer: %s, Order ID: %s, Date: %s%n",
            rs.getString(1),
            rs.getString(2),
            rs.getString(3));
    }
    
    // 可选：若需要查看执行计划，可在查询正常运行后取消注释
    /*
    statement.execute("EXPLAIN PLAN FOR " + sql);
    ResultSet explainRs = statement.getResultSet();
    System.out.println("\nQuery Plan:");
    while (explainRs.next()) {
        System.out.println(explainRs.getString(1));
    }
    */
} catch (SQLException e) {
    System.err.println("Error executing query: " + e.getMessage());
    e.printStackTrace();
} finally {
    // Clean up
    connection.close();
}

额外说明 #

• 关闭caseSensitive是因为H2默认使用大写表名，而CockroachDB使用小写，大小写敏感设置可能导致Calcite无法正确识别表；
• 移除catalog参数是避免两个数据库的Catalog名称（sample和DEFAULT）在Calcite元数据处理中引发冲突；
• 若EXPLAIN PLAN仍报错，可暂时注释该部分，优先保证查询正常执行。

内容的提问来源于stack exchange，提问作者Anoop