[
https://issues.apache.org/jira/browse/NIFI-10097?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Piotr updated NIFI-10097:
-------------------------
Description:
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
was:
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
> 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.1, 1.16.2
> Environment: software platform, nifi_1.16.2
> Reporter: Piotr
> Priority: Major
>
> 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)
