SqlQuery throws exception in Ignite 2.0.0

Question

I have just upgraded to V2.0.0 and tried the following code ..

SqlQuery diffSql = new SqlQuery(PendingAvGroup.class,
     " from PendingAvGroup as pnd  " +
     "where not exists(select 1 from " +
     "\"Processed_PendingAvGroup_cache\".PendingAvGroup prc " +
     " WHERE prc.resourceGroupId = pnd.resourceGroupId)"
     );

But i get the following error ..

Caused by: org.h2.jdbc.JdbcSQLException: Column "Pending_PendingAvGroup_cache.PENDINGAVGROUP._KEY" not found; SQL statement: SELECT "Pending_PendingAvGroup_cache".PendingAvGroup._key, "Pending_PendingAvGroup_cache".PendingAvGroup._val from PendingAvGroup as pnd where not exists(select 1 from "Processed_PendingAvGroup_cache".PendingAvGroup prc WHERE prc.resourceGroupId = pnd.resourceGroupId) [42122-195] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.195.jar:1.4.195]

Its appears that when ever I provide a FROM clause I get '._KEY" not found;' error ... even for a single table.

Id really appreciate anyones help on this .. Thanks Paul

Sergi Vladykin Sergi Vladykin · Accepted Answer · 2017-05-12T08:03:36

This is actually a quirky thing of SQL (I'm not sure if it is a standard or just H2). By default Ignite generates query with the original table name in select clause "Pending_PendingAvGroup_cache".PendingAvGroup, but it must actually use your alias pnd there.

To solve this you have to set the alias on SqlQuery and Ignite will generate correct query:

SqlQuery diffSql = new SqlQuery(PendingAvGroup.class, "...").setAlias("pnd");

Another option is to use SqlFieldsQuery:

new SqlFieldsQuery("select _key, _val from ....")

SqlQuery throws exception in Ignite 2.0.0

1 Answers