For example, the following query:
{code}movies = em.createQuery( "from Movie where title in (:titles) and viewerRating in (:ratings)", Movie.class )
.setParameter( "titles", Arrays.asList( "Barnie", "Front Door", "To thatch a roof" ) )
.setParameter( "ratings", Arrays.asList( (byte) 6, (byte) 7 ) )
.getResultList();{code}
causes the following error on execution:
{code}org.h2.jdbc.JdbcSQLException: Data conversion error converting "Front Door"; SQL statement:
SELECT
_GEN_0___Z0._KEY __C0_0,
_GEN_0___Z0._VAL __C0_1
FROM PUBLIC.MOVIE _GEN_0___Z0
WHERE (_GEN_0___Z0.TITLE IN(?1, ?2, ?3)) AND (_GEN_0___Z0.VIEWERRATING IN(?4, ?5)) [22018-195]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.value.Value.convertTo(Value.java:996)
at org.h2.expression.ConditionIn.getValue(ConditionIn.java:56)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:93)
at org.h2.expression.Expression.getBooleanValue(Expression.java:178)
at org.h2.command.dml.Select.isConditionMet(Select.java:299)
at org.h2.command.dml.Select.access$600(Select.java:64)
at org.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1455)
at org.h2.result.LazyResult.hasNext(LazyResult.java:79)
at org.h2.result.LazyResult.next(LazyResult.java:59)
at org.h2.command.dml.Select.queryFlat(Select.java:519)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:625)
at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114)
at org.h2.command.dml.Query.query(Query.java:352)
at org.h2.command.dml.Query.query(Query.java:333)
at org.h2.command.CommandContainer.query(CommandContainer.java:113)
at org.h2.command.Command.executeQuery(Command.java:201)
at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:111)
at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:1081)
... 56 more
Caused by: java.lang.NumberFormatException: For input string: "Front Door"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Integer.parseInt(Integer.java:580)
at java.lang.Byte.parseByte(Byte.java:149)
at java.lang.Byte.parseByte(Byte.java:175)
at org.h2.value.Value.convertTo(Value.java:936)
... 73 more{code}
This happens because actual parameter values are passed in wrong order, not following parameter's positions (?1, ?2...) , leading to type mismatch .
This bug may occur not only when IN predicate is used, but in any query with multiple named parameters. As a result, it may cause type conversion exception, or wrong return result -- if all values match types, but are misordered. |
|
