[
https://issues.apache.org/jira/browse/IGNITE-19284?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714945#comment-17714945
]
Yury Gerzhedovich commented on IGNITE-19284:
--------------------------------------------
For most databases using dynamic parameters for interval type is not so trivial
and usually has a different syntax than directly using the type.
One of the working syntaxes is used explicitly cast dynamic parameters to
INTERVAL TYPE, like a
select id, val from testTable where val <= DATE '1990-01-01' - ?::INTERVAL DAY
and pass parameters as int for example. But right now the query fails at
TypeUtils#toInternal() during convert int to Duration.
As WA could be used set parameter for the case not as int but as an instance of
Duration class, like
ps.setObject(1, Duration.ofDays(1));
> SQL Prepared statement interval parsing: Failed to parse query: Encountered ""
> ------------------------------------------------------------------------------
>
> Key: IGNITE-19284
> URL: https://issues.apache.org/jira/browse/IGNITE-19284
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 3.0
> Reporter: Alexander Belyak
> Priority: Major
> Labels: ignite-3
> Attachments: interval.zip
>
>
> Query with parameter for interval fails while it executing through
> PreparedStatement:
> 1) Start ignite3 single node cluster
> 2) Execute:
> {noformat}
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> public class TimeoutExceptionReproducer {
> private static final String DB_URL = "jdbc:ignite:thin://127.0.0.1:10800";
> public static void main(String[] args) throws SQLException {
> try (Connection connection = DriverManager.getConnection(DB_URL)) {
> parce(connection);
> }
> }
> public static void parce(Connection connection) throws SQLException {
> try (Statement stmt = connection.createStatement()) {
> stmt.executeUpdate("create table testTable (id int primary key,
> val date not null)");
> }
> System.out.println("Table created");
> try (Statement stmt = connection.createStatement()) {
> try (ResultSet rs = stmt.executeQuery("select id, val from
> testTable where val <= DATE '1990-01-01' - interval 1 day")) {
> while (rs.next()) {
> // No op
> }
> }
> }
> System.out.println("Statement done");
> try (PreparedStatement ps = connection.prepareStatement(
> "select id, val from testTable where val <= DATE '1990-01-01' -
> interval ? day")) {
> ps.setInt(1, 1);
> // Or
> //ps.setString(1, "1");
> try (ResultSet rs = ps.executeQuery()) {
> while(rs.next()) {
> // NoOp
> }
> }
> }
> System.out.println("Prepared statement done");
> }
> }{noformat}
> Return error:
> {noformat}
> Table created
> Apr 13, 2023 11:49:53 AM org.apache.ignite.internal.logger.IgniteLogger
> logInternal
> INFO: Partition assignment change notification received
> [remoteAddress=127.0.0.1/<unresolved>:10800]
> Exception in thread "main" java.sql.SQLException: Exception while executing
> query [query=select id, val from testTable where val <= DATE '1990-01-01' -
> interval ? day]. Error message:IGN-SQL-3
> TraceId:14caecea-db6f-4948-8a21-ae5f5efae06b Failed to parse query:
> Encountered "" at line 1, column 62.
> Statement done
> Was expecting one of:
>
> at
> org.apache.ignite.internal.jdbc.proto.IgniteQueryErrorCode.createJdbcSqlException(IgniteQueryErrorCode.java:57)
> at
> org.apache.ignite.internal.jdbc.JdbcStatement.execute0(JdbcStatement.java:148)
> at
> org.apache.ignite.internal.jdbc.JdbcPreparedStatement.executeWithArguments(JdbcPreparedStatement.java:657)
> at
> org.apache.ignite.internal.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:85)
> at TimeoutExceptionReproducer.parce(TimeoutExceptionReproducer.java:36)
> at
> TimeoutExceptionReproducer.main(TimeoutExceptionReproducer.java:13)Process
> finished with exit code 1{noformat}
> There is nothing in the server logs, even on
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
