JDBI调用Dremio时SQL预编译异常求助

问题背景 #

我用Java结合Jdbi编写了查询Dremio中Iceberg数据源的方法，传入参数后执行报错，但相同的SQL在Dremio UI中可以正常运行。

Java查询方法代码 #

@Retry
@SqlQuery("""
        WITH IdsGrouped AS (
            SELECT /* comment */
                record_key,
                max(gen_datetime) as max_dt
            FROM "ad".iceberg_data.<sourceSchema.schema>_<sourceSchema.tablePrefix>
            WHERE record_key IN (<ids>)
            GROUP BY record_key
        )
        SELECT *
        FROM "ad".iceberg_data.<sourceSchema.schema>_<sourceSchema.tablePrefix>
        WHERE (record_key, gen_datetime) IN (SELECT * FROM IdsGrouped)
        """)
@UseStringTemplateEngine
@RegisterRowMapper(MapMapper.class)
List<Map<String, Object>> findDataById(@Define("sourceSchema") SourceSchema sourceSchema,
                                 @DefineCollection(value = "ids") Collection<String> ids);

报错信息 #

org.jdbi.v3.core.statement.UnableToCreateStatementException: java.sql.SQLException: while preparing SQL:
    WITH IdsGrouped AS (
        SELECT /* comment */ 
        record_key,
        max(gen_datetime) as max_dt 
        FROM "ad".iceberg_data.bl_merged WHERE record_key IN ('69823926','70674377','101113') 
        GROUP BY record_key
        ) 
     SELECT * 
     FROM "ad".iceberg_data.bl_merged 
     WHERE (record_key, gen_datetime) IN (SELECT * FROM IdsGrouped)

堆栈跟踪信息 #

at com.btg.fit.nex.pln.common.persistence.decorator.RetryDecorator.lambda$decorateHandler$1(RetryDecorator.java:35)
at net.jodah.failsafe.Functions.lambda$get$0(Functions.java:48)
at net.jodah.failsafe.RetryPolicyExecutor.lambda$supply$0(RetryPolicyExecutor.java:62)
at net.jodah.failsafe.Execution.executeSync(Execution.java:129)
at net.jodah.failsafe.FailsafeExecutor.call(FailsafeExecutor.java:376)
at net.jodah.failsafe.FailsafeExecutor.get(FailsafeExecutor.java:67)
at com.btg.fit.nex.pln.common.persistence.decorator.RetryDecorator.lambda$decorateHandler$2(RetryDecorator.java:31)
at org.jdbi.v3.sqlobject.internal.SqlObjectInitData$1.lambda$invoke$0(SqlObjectInitData.java:132)
at org.jdbi.v3.core.internal.Invocations.invokeWith(Invocations.java:44)
at org.jdbi.v3.core.internal.Invocations.invokeWith(Invocations.java:26)
at org.jdbi.v3.core.LazyHandleSupplier.lambda$invokeInContext$1(LazyHandleSupplier.java:82)
at org.jdbi.v3.core.internal.Invocations.invokeWith(Invocations.java:44)
at org.jdbi.v3.core.internal.Invocations.invokeWith(Invocations.java:26)
at org.jdbi.v3.core.LazyHandleSupplier.invokeInContext(LazyHandleSupplier.java:81)
at org.jdbi.v3.sqlobject.internal.SqlObjectInitData$1.call(SqlObjectInitData.java:138)
at org.jdbi.v3.sqlobject.internal.SqlObjectInitData$1.invoke(SqlObjectInitData.java:132)
at org.jdbi.v3.sqlobject.SqlObjectFactory.lambda$attach$2(SqlObjectFactory.java:108)
at org.jdbi.v3.core.internal.OnDemandExtensions.lambda$invoke$5(OnDemandExtensions.java:98)
at org.jdbi.v3.core.internal.exceptions.Unchecked.lambda$function$4(Unchecked.java:76)
at org.jdbi.v3.core.internal.OnDemandExtensions.invoke(OnDemandExtensions.java:98)
at org.jdbi.v3.core.internal.OnDemandExtensions.lambda$createProxy$2(OnDemandExtensions.java:82)
at org.jdbi.v3.core.Jdbi.callWithExtension(Jdbi.java:491)
at org.jdbi.v3.core.Jdbi.withExtension(Jdbi.java:478)
at org.jdbi.v3.core.internal.OnDemandExtensions.lambda$createProxy$3(OnDemandExtensions.java:82)
at jdk.proxy2/jdk.proxy2.$Proxy67.findDataById(Unknown Source)
at com.btg.fit.nex.pln.logistics.precombination.Fetcher.findDataById(Fetcher.java:68)

自定义Retry装饰器定义 #

@SqlMethodDecoratingAnnotation(RetryDecorator.class)
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
public @interface Retry {
    int value() default 3;
}

可能的原因及排查方向 #

1. Dremio JDBC驱动兼容性问题 #

Dremio的UI和JDBC驱动对SQL语法的解析可能存在差异，尤其是多列IN子句(record_key, gen_datetime) IN (...)这种语法，部分旧版本JDBC驱动可能不支持Iceberg表的该语法特性。

2. Jdbi字符串模板的参数处理问题 #

虽然生成的SQL看起来正确，但@UseStringTemplateEngine配合@DefineCollection直接拼接字符串到IN子句，可能存在隐藏的格式问题（比如特殊字符转义、空格处理），导致JDBC端解析SQL失败。

3. 自定义重试装饰器的上下文问题 #

自定义的RetryDecorator在重试过程中可能没有正确维护Jdbi的Handle上下文，导致Statement创建时出现异常，比如重复绑定参数或Handle未正确复用。

4. 预编译参数替代字符串拼接的必要性 #

直接用@DefineCollection拼接IN子句属于静态SQL拼接，而JDBC驱动通常更期望预编译参数方式，这种拼接可能触发驱动的语法检查机制，导致预编译失败。

排查建议 #

• 改用预编译参数绑定：将SQL中的<ids>替换为:ids，参数注解换成@BindList("ids") Collection<String> ids，避免直接字符串拼接，同时确保Jdbi配置启用多值绑定支持。
• 升级Dremio JDBC驱动：更新到最新版本的Dremio JDBC驱动，验证对Iceberg表和多列IN语法的支持。
• 临时禁用重试装饰器：去掉方法上的@Retry注解，测试是否是重试逻辑导致的上下文异常。
• 纯JDBC测试生成的SQL：将报错信息中生成的完整SQL复制出来，用纯JDBC代码执行，排除Jdbi框架的影响，确认驱动本身是否支持该SQL。

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