Piotr created NIFI-10097:
----------------------------
Summary: Issue with query execution after h2 upgrade from 1.4.199
to 2.1.210
Key: NIFI-10097
URL: https://issues.apache.org/jira/browse/NIFI-10097
Project: Apache NiFi
Issue Type: Bug
Components: Core Framework
Affects Versions: 1.16.2, 1.16.1
Environment: software platform, nifi_1.16.2
Reporter: Piotr
After Nifi upgrade from version 1.15.3 to 1.16.1 and 1.16.2 we cannot execute
custom query on table configure_details in built-in H2 database.
Query:
*select * from configure_details*
return values in proper way:
{
"ACTION_ID" : example_value,
"NAME" : "example_value",
"VALUE" : "example_value",
"PREVIOUS_VALUE" : null
}
when we execute query
*select ACTION_ID,NAME,PREVIOUS_VALUE from configure_details*
query also return values in proper way(of course without field "value")
but when we try execute this query:
*select ACTION_ID,NAME,VALUE ,PREVIOUS_VALUE from configure_details*
processor return below error:
QueryDatabaseTableRecord[id=16cf3068-45f2-10f6-888c-938bc7c78ac1] Unable to
execute SQL select query SELECT * FROM (select ACTION_ID,NAME,VALUE
,PREVIOUS_VALUE from configure_details) AS test due to
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT
* FROM (select ACTION_ID,NAME,[*]VALUE ,PREVIOUS_VALUE from configure_details)
AS test"; expected "*, INTERSECTS, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL
statement:
SELECT * FROM (select ACTION_ID,NAME,VALUE ,PREVIOUS_VALUE from
configure_details) AS test [42001-210]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:521)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
at org.h2.message.DbException.getSyntaxError(DbException.java:265)
at org.h2.command.Parser.getSyntaxError(Parser.java:890)
at org.h2.command.Parser.readTerm(Parser.java:5191)
at org.h2.command.Parser.readFactor(Parser.java:3423)
at org.h2.command.Parser.readSum(Parser.java:3410)
at org.h2.command.Parser.readConcat(Parser.java:3375)
at org.h2.command.Parser.readCondition(Parser.java:3160)
at org.h2.command.Parser.readExpression(Parser.java:3076)
at org.h2.command.Parser.parseSelectExpressions(Parser.java:2876)
at org.h2.command.Parser.parseSelect(Parser.java:2893)
at org.h2.command.Parser.parseQueryPrimary(Parser.java:2783)
at org.h2.command.Parser.parseQueryTerm(Parser.java:2650)
at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2629)
at
org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2622)
at org.h2.command.Parser.parseQueryExpression(Parser.java:2615)
at
org.h2.command.Parser.readDerivedTableWithCorrelation(Parser.java:1898)
at org.h2.command.Parser.readTablePrimary(Parser.java:1799)
at org.h2.command.Parser.readTableReference(Parser.java:2342)
at org.h2.command.Parser.parseSelectFromPart(Parser.java:2793)
at org.h2.command.Parser.parseSelect(Parser.java:2900)
at org.h2.command.Parser.parseQueryPrimary(Parser.java:2783)
at org.h2.command.Parser.parseQueryTerm(Parser.java:2650)
at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2629)
at
org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2622)
at org.h2.command.Parser.parseQueryExpression(Parser.java:2615)
at org.h2.command.Parser.parseQuery(Parser.java:2576)
at org.h2.command.Parser.parsePrepared(Parser.java:707)
at org.h2.command.Parser.parse(Parser.java:674)
at org.h2.command.Parser.parse(Parser.java:649)
at org.h2.command.Parser.prepareCommand(Parser.java:551)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:615)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:288)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:191)
at java.base/java.lang.Thread.run(Thread.java:829)
OR:
ExecuteSQL[id=1eea42cf-b249-13dd-ffff-ffffeee487f3] Unable to execute SQL
select query select ACTION_ID,NAME,VALUE as test,PREVIOUS_VALUE from
configure_details due to org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error
in SQL statement "select ACTION_ID,NAME,[*]VALUE as test,PREVIOUS_VALUE from
configure_details"; expected "*, INTERSECTS, NOT, EXISTS, UNIQUE, INTERSECTS";
SQL statement:
select ACTION_ID,NAME,VALUE as test,PREVIOUS_VALUE from configure_details
[42001-210]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:521)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
at org.h2.message.DbException.getSyntaxError(DbException.java:265)
at org.h2.command.Parser.getSyntaxError(Parser.java:890)
at org.h2.command.Parser.readTerm(Parser.java:5191)
at org.h2.command.Parser.readFactor(Parser.java:3423)
at org.h2.command.Parser.readSum(Parser.java:3410)
at org.h2.command.Parser.readConcat(Parser.java:3375)
at org.h2.command.Parser.readCondition(Parser.java:3160)
at org.h2.command.Parser.readExpression(Parser.java:3076)
at org.h2.command.Parser.parseSelectExpressions(Parser.java:2876)
at org.h2.command.Parser.parseSelect(Parser.java:2893)
at org.h2.command.Parser.parseQueryPrimary(Parser.java:2783)
at org.h2.command.Parser.parseQueryTerm(Parser.java:2650)
at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2629)
at
org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2622)
at org.h2.command.Parser.parseQueryExpression(Parser.java:2615)
at org.h2.command.Parser.parseQuery(Parser.java:2576)
at org.h2.command.Parser.parsePrepared(Parser.java:707)
at org.h2.command.Parser.parse(Parser.java:674)
at org.h2.command.Parser.parse(Parser.java:649)
at org.h2.command.Parser.prepareCommand(Parser.java:551)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:615)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:288)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:191)
at java.base/java.lang.Thread.run(Thread.java:829)
. No FlowFile to route to failure: {}
For tests we used processors types:
- ExecuteSQL 1.16.1&2
- QueryDatabaseTableRecord 1.16.1&2
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
