JDBC catalog查询无法使用日期条件

💬 StarRocks 用户问答
#1

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】我在测试通过jdbc catalog的方式，来查询oracle中的数据。简单的select可以查询返回结果，但是给查询添加了日期条件后，查询就会报错。
同样的查询条件，我还测试了查询mysql，它是可以正常返回查询结果的。

【背景】
【业务影响】
【是否存算分离】否
【StarRocks版本】3.4.0
【集群规模】单机版
【机器信息】
【联系方式】ouqiubin@163.com
【附件】

----- 创建oracle的jdbc catalog

CREATE EXTERNAL CATALOG jdbc1
PROPERTIES
(
“type”=“jdbc”,
“user”=“xxxxxx”,
“password”=“123456”,
“jdbc_uri”=“jdbc:oracle:thin:@192.168.110.1:1521:rhis”,
“driver_url”=“file:///opt/driver/ojdbc8-23.2.0.0.jar”,
“driver_class”=“oracle.jdbc.driver.OracleDriver”
);

------ 带日期条件的查询语句

查询语句1：

select * from jdbc1.xxxxxx.DWD_D_TEST
where SDATE > STR_TO_DATE(‘2021-01-01 00:00:56’, ‘%Y-%m-%d %H:%i:%s’)

  • 查询语句1的报错：
    SQL 错误 [1064] [42000]: open JDBCScanner failed, error: java.sql.SQLDataException: ORA-01861: literal does not match format string

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:540)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:256)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:253)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:86)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:765)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:921)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1099)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3640)
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1384)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3687)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at com.starrocks.jdbcbridge.JDBCScanner.open(JDBCScanner.java:95)
    Caused by: Error : 1861, Position : 107, Sql = SELECT * FROM (SELECT ORD, SDATE, USERID, UNAME FROM “USERMCHIS_TJSFEZX210616”.“DWD_D_TEST”
    WHERE (SDATE > ‘2025-01-03 00:00:56’)
    )
    WHERE ROWNUM <= 200,

OriginalSql = SELECT * FROM (SELECT ORD, SDATE, USERID, UNAME FROM “USERMCHIS_TJSFEZX210616”.“DWD_D_TEST” WHERE (SDATE > ‘2025-01-03 00:00:56’))
WHERE ROWNUM <= 200, Error Msg = ORA-01861: literal does not match format string

查询语句2：

select * from jdbc1.xxxxxx.DWD_D_TEST
where SDATE > to_date(‘2025-01-03 00:00:56’, ‘YYYY-MM-DD HH24:MI:SS’)

查询语句2的报错：

SQL 错误 [1064] [42000]: Getting analyzing error from line 2, column 14 to line 2, column 68. Detail message: No matching function with signature: to_date(varchar, varchar).

京ICP备2022026421号-1