sql - hsqldb 中的间隔格式异常无效,但查询在 Postgres 中有效
问题描述
我正在 postgresql 中运行查询。它在 psql 中运行良好,但为了进行单元测试,我创建了一个内存 HSQLDB 并在其中执行了相同的查询。但是抛出了“无效的间隔格式”异常。由于我是 hsqldb 的新手,所以我无法找出问题的根本原因。
询问:
INSERT INTO microbatch_redrive
SELECT mii.*,(to_char(CURRENT_TIMESTAMP ,'yyyy-mm-ddThh:mi:ss.msZ')) FROM
(SELECT * FROM
( SELECT mi.*,me.extractid FROM
(SELECT * FROM microbatch_info WHERE
((numattempts<= ${maxNumOfAttempts})
AND (starttime>= '${starttime}')
AND (starttime<= '${endtime}')))
AS mi LEFT OUTER JOIN microbatch_extract_info AS me ON mi.microbatchid = me.microbatchid)
AS mime WHERE((mime.raverunstatus='SUCCEEDED' AND mime.extractid is null)
OR (mime.starttime < to_char(CURRENT_TIMESTAMP,'yyyy-mm-ddThh:mi:ss.msZ') AND mime.raverunstatus='NOT_STARTED')
OR (mime.raverunstatus = 'FAILED')
OR (mime.raverunstatus='STARTED' AND mime.starttime< (to_char(CURRENT_TIMESTAMP - INTERVAL '1 hour' * ${extractTimeIntervalInHour},'yyyy-mm-ddThh:mi:ss.msZ')))
)
) AS mii LEFT OUTER JOIN
(SELECT microbatchid FROM microbatch_redrive WHERE raverunstatus='REDRIVE_SUCCEEDED') AS mr ON mii.microbatchid=mr.microbatchid WHERE mr.microbatchid IS NULL;
错误:
Caused by: org.hsqldb.HsqlException: data exception: invalid interval format
[junit] at org.hsqldb.error.Error.error(Unknown Source)
[junit] at org.hsqldb.error.Error.error(Unknown Source)
[junit] at org.hsqldb.types.IntervalType.getIntervalType(Unknown Source)
[junit] at org.hsqldb.ParserBase.readIntervalType(Unknown Source)
[junit] at org.hsqldb.ParserBase.readDateTimeIntervalLiteral(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.readExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.readSQLFunction(Unknown Source)
[junit] at org.hsqldb.ParserDQL.readFunction(Unknown Source)
[junit] at org.hsqldb.ParserDQL.readColumnOrFunctionExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadRowElementList(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadRowOrCommonValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadRowValuePredicand(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadPredicateRightPart(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadRowElementList(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.readWhereGroupHaving(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadSubqueryTableBody(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadSubqueryTableBody(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadTableSubqueryOrNull(Unknown Source)
[junit] at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
[junit] at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
[junit] at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
[junit] at org.hsqldb.ParserCommand.compilePart(Unknown Source)
[junit] at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
[junit] at org.hsqldb.Session.executeDirectStatement(Unknown Source)
[junit] at org.hsqldb.Session.execute(Unknown Source)
提前致谢。
解决方案
INTERVAL '1 hour'
是 Postgres 特定的区间语法。
在标准 SQL 中,您需要:INTERVAL '1' hour
HSQLDB 也支持(如果您需要适用于两种产品的语句,则 Postgres 也支持)。
所以完整的表达式应该是:
CURRENT_TIMESTAMP - INTERVAL '1' hour * ${extractTimeIntervalInHour}
推荐阅读
- javascript - 如何使用多个异步/等待功能?
- javascript - 更新 mongodb 条目并非一直有效
- java - LinkedBlockingQueue 和工作线程 - 这个代码线程安全吗?
- eclipse - ant,maven,gradle构建工具有什么区别
- sql - 从 SQL Server 中链接服务器上的存储过程中获取返回值
- scala - 如何对所有具有上下文绑定的类进行模式匹配
- bluetooth-lowenergy - 阿波罗 BLE 通信
- shell - 在 shell 中使用 jq 库时观察到意外的令牌错误
- batch-file - 尽管指定了分隔符,但在传递带有空格的文件名变量时,循环的批处理文件不起作用
- android - Android 应用程序从服务器检索数据,保存在数据库中并显示给用户