You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by "jan.swaelens" <ja...@sofico.be> on 2016/04/18 13:33:11 UTC
SQL Aliases are not interpreted correctly
Hello,
Continuing my previous journey in order to use Ignite as a cache for my
database I am now testing with the next step in my sql test cases. Again
using the 'Activity' case from previous iterations I am now hitting an
unexpected case of interpretation of the sql aliases we use in our queries.
For example this query:
/SELECT DISTINCT activity0.activity_id, activity0.sequencenr,
activity0.name_mlid, activity0.name, activity0.description_mlid,
activity0.description, activity0.durationunit_enumid, activity0.duration,
activity0.required, activity0.predecessortype_enumid,
activity0.successortype_enumid, activity0.removefromworklist,
activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate,
activity0.priority_enumid, activity0.notify, activity0.timestamp,
activity0.ActivityType_id, activity0.container_id, activity0.realization_id,
activity0.kernel_id, activity0.sysrepoperation_id
FROM activity activity0/
Generates the following error:
/ ERROR [20160418 13:44:14] - Failed to execute job due to unexpected
runtime exception [jobId=9b28c192451-70dcb994-f236-456e-aaf9-3f0b3974a497,
ses=GridJobSessionImpl [ses=GridTaskSessionImpl
[taskName=o.a.i.i.processors.cache.query.jdbc.GridCacheQueryJdbcTask,
dep=GridDeployment [ts=1460978617361, depMode=SHARED,
clsLdr=weblogic.utils.classloaders.ChangeAwareClassLoader@67eb9c29 finder:
weblogic.utils.classloaders.CodeGenClassFinder@2fd95ea0 annotation:
miles@miles, clsLdrId=a5d7c192451-70dcb994-f236-456e-aaf9-3f0b3974a497,
userVer=0, loc=true,
sampleClsName=o.a.i.i.processors.cache.GridCacheAdapter$LoadCacheJob,
pendingUndeploy=false, undeployed=false, usage=2],
taskClsName=o.a.i.i.processors.cache.query.jdbc.GridCacheQueryJdbcTask,
sesId=7b28c192451-70dcb994-f236-456e-aaf9-3f0b3974a497,
startTime=1460979854456, endTime=9223372036854775807,
taskNodeId=70dcb994-f236-456e-aaf9-3f0b3974a497,
clsLdr=weblogic.utils.classloaders.ChangeAwareClassLoader@67eb9c29 finder:
weblogic.utils.classloaders.CodeGenClassFinder@2fd95ea0 annotation:
miles@miles, closed=false, cpSpi=null, failSpi=null, loadSpi=null, usage=1,
fullSup=false, subjId=70dcb994-f236-456e-aaf9-3f0b3974a497,
mapFut=IgniteFuture [orig=GridFutureAdapter [resFlag=0, res=null,
startTime=1460979854456, endTime=0, ignoreInterrupts=false, state=INIT]]],
jobId=9b28c192451-70dcb994-f236-456e-aaf9-3f0b3974a497]]
javax.cache.CacheException: class org.apache.ignite.IgniteException: Failed
to parse query: SELECT DISTINCT activity0.activity_id, activity0.sequencenr,
activity0.name_mlid, activity0.name, activity0.description_mlid,
activity0.description, activity0.durationunit_enumid, activity0.duration,
activity0.required, activity0.predecessortype_enumid,
activity0.successortype_enumid, activity0.removefromworklist,
activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate,
activity0.priority_enumid, activity0.notify, activity0.timestamp,
activity0.ActivityType_id, activity0.container_id, activity0.realization_id,
activity0.kernel_id, activity0.sysrepoperation_id
FROM activity activity0
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:674)
at
org.apache.ignite.internal.processors.cache.query.jdbc.GridCacheQueryJdbcTask$JdbcDriverJob.execute(GridCacheQueryJdbcTask.java:222)
at
org.apache.ignite.internal.processors.job.GridJobWorker$2.call(GridJobWorker.java:509)
at
org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6455)
at
org.apache.ignite.internal.processors.job.GridJobWorker.execute0(GridJobWorker.java:503)
at
org.apache.ignite.internal.processors.job.GridJobWorker.body(GridJobWorker.java:456)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: class org.apache.ignite.IgniteException: Failed to parse query:
SELECT DISTINCT activity0.activity_id, activity0.sequencenr,
activity0.name_mlid, activity0.name, activity0.description_mlid,
activity0.description, activity0.durationunit_enumid, activity0.duration,
activity0.required, activity0.predecessortype_enumid,
activity0.successortype_enumid, activity0.removefromworklist,
activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate,
activity0.priority_enumid, activity0.notify, activity0.timestamp,
activity0.ActivityType_id, activity0.container_id, activity0.realization_id,
activity0.kernel_id, activity0.sysrepoperation_id
FROM activity activity0
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:811)
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:665)
... 9 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to parse
query: SELECT DISTINCT activity0.activity_id, activity0.sequencenr,
activity0.name_mlid, activity0.name, activity0.description_mlid,
activity0.description, activity0.durationunit_enumid, activity0.duration,
activity0.required, activity0.predecessortype_enumid,
activity0.successortype_enumid, activity0.removefromworklist,
activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate,
activity0.priority_enumid, activity0.notify, activity0.timestamp,
activity0.ActivityType_id, activity0.container_id, activity0.realization_id,
activity0.kernel_id, activity0.sysrepoperation_id
FROM activity activity0
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1787)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:804)
... 10 more
Caused by: javax.cache.CacheException: Failed to parse query: SELECT
DISTINCT activity0.activity_id, activity0.sequencenr, activity0.name_mlid,
activity0.name, activity0.description_mlid, activity0.description,
activity0.durationunit_enumid, activity0.duration, activity0.required,
activity0.predecessortype_enumid, activity0.successortype_enumid,
activity0.removefromworklist, activity0.lastactivity_id,
activity0.lifecyclereporting, activity0.duedate, activity0.priority_enumid,
activity0.notify, activity0.timestamp, activity0.ActivityType_id,
activity0.container_id, activity0.realization_id, activity0.kernel_id,
activity0.sysrepoperation_id
FROM activity activity0
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1059)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:806)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:804)
at
org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1769)
... 11 more
Caused by: org.h2.jdbc.JdbcSQLException: Column "ACTIVITY0.ACTIVITY_ID" not
found; SQL statement:
SELECT DISTINCT activity0.activity_id, activity0.sequencenr,
activity0.name_mlid, activity0.name, activity0.description_mlid,
activity0.description, activity0.durationunit_enumid, activity0.duration,
activity0.required, activity0.predecessortype_enumid,
activity0.successortype_enumid, activity0.removefromworklist,
activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate,
activity0.priority_enumid, activity0.notify, activity0.timestamp,
activity0.ActivityType_id, activity0.container_id, activity0.realization_id,
activity0.kernel_id, activity0.sysrepoperation_id
FROM activity activity0 [42122-175]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
at org.h2.message.DbException.get(DbException.java:172)
at org.h2.message.DbException.get(DbException.java:149)
at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:144)
at org.h2.command.dml.Select.prepare(Select.java:810)
at org.h2.command.Parser.prepareCommand(Parser.java:240)
at org.h2.engine.Session.prepareLocal(Session.java:436)
at org.h2.engine.Session.prepareCommand(Session.java:379)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:70)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:351)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1043)
... 15 more/
Based on what I am seeing it looks like the query engine has an issue with
the alias being used? I checked around and this (as far as I can tell) is
supported ansi99 syntax.
Any thoughts are greatly appreciated!
Attached some additional info:
alias.gz
<http://apache-ignite-users.70518.x6.nabble.com/file/n4281/alias.gz>
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SQL Aliases are not interpreted correctly
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
Thanks works like a charm! Up to the next level in my experiment.
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281p4692.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SQL Aliases are not interpreted correctly
Posted by Alexey Kuznetsov <ak...@gridgain.com>.
Jun, I implemented support for aliases generation in Schema Import Utility
and pushed to master.
Please try with latest nightly-build
https://ignite.apache.org/community/contribute.html#nightly-builds
On Wed, Apr 20, 2016 at 2:02 PM, jan.swaelens <ja...@sofico.be>
wrote:
> Superb, that would really do the trick for my use cases!
>
> best regards
> jan
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281p4356.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com
Re: SQL Aliases are not interpreted correctly
Posted by "jan.swaelens" <ja...@sofico.be>.
Superb, that would really do the trick for my use cases!
best regards
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281p4356.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SQL Aliases are not interpreted correctly
Posted by Alexey Kuznetsov <ak...@gridgain.com>.
Hi Jan!
It seems that we can fix this issue with help of fields aliases.
I created issues for support fields aliases generation.
https://issues.apache.org/jira/browse/IGNITE-3028 (Schema Import utility)
https://issues.apache.org/jira/browse/IGNITE-3029 (Web Console)
Thanks!
On Wed, Apr 20, 2016 at 3:51 AM, vkulichenko <va...@gmail.com>
wrote:
> Hi Jan,
>
> The first approach you mentioned should work for you, Ignite uses object
> field names as SQL names. There is currently no way to generate classes
> with
> DB names used as field names, but as Alexey mentioned, this is something
> that can be added later.
>
> In the meantime, you can modify POJOs and CacheConfig manually.
>
> -Val
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281p4341.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com
Re: SQL Aliases are not interpreted correctly
Posted by vkulichenko <va...@gmail.com>.
Hi Jan,
The first approach you mentioned should work for you, Ignite uses object
field names as SQL names. There is currently no way to generate classes with
DB names used as field names, but as Alexey mentioned, this is something
that can be added later.
In the meantime, you can modify POJOs and CacheConfig manually.
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281p4341.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SQL Aliases are not interpreted correctly
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
Yes I totally agree that the generated Java class should follow the well
known conventions.
An intermediate solution might be (I say might since I do not know the inner
workings of Ignite) to have the variables 'ugly' in the Java class and have
pretty accessors (get/set).
All 'better' solutions will result in a high impact change as far as I can
tell by adding an interpreter which reformulates the sql syntax from raw sql
to a sql matching the java classes field names - I'm quite sure that's
something you won't even want in the product since it increases overhead.
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281p4327.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SQL Aliases are not interpreted correctly
Posted by Alexey Kuznetsov <ak...@gridgain.com>.
Hi, Jan.
Schema Import utility and Web Console generate code to follow java
conventions.
We could discuss an option "Generate POJO with NAMES as in database".
>From one point of view this option will be useful when you are going to
execute same query as you can run directly on database.
>From other hand - generated POJOs will looks ugly and not as idiomatic java
POJO.
Any way we could discuss this and create appropriate issue in JIRA.
My personal option - generate idiomatic Java code.
Thoughts?
On Tue, Apr 19, 2016 at 5:36 PM, jan.swaelens <ja...@sofico.be>
wrote:
> Hello,
>
> Ah I see yes, so in order for me to be able to 'switch' our db layer to use
> the cache I would need to customize the generator in order to generate
> classes and cache definitions matching with the actual tables names. Does
> it
> work in a case sensitive manner as well or is it case insensitive on field
> names like sql is?
>
> Or would you find it agreeable that the ignite generator has an option to
> generate code which allows this out of the box (even when the generated
> fields are not really matching the syntax which you would expect in java
> classes - i agree).
>
> br
> jan
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281p4323.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com
Re: SQL Aliases are not interpreted correctly
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
Ah I see yes, so in order for me to be able to 'switch' our db layer to use
the cache I would need to customize the generator in order to generate
classes and cache definitions matching with the actual tables names. Does it
work in a case sensitive manner as well or is it case insensitive on field
names like sql is?
Or would you find it agreeable that the ignite generator has an option to
generate code which allows this out of the box (even when the generated
fields are not really matching the syntax which you would expect in java
classes - i agree).
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281p4323.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SQL Aliases are not interpreted correctly
Posted by vkulichenko <va...@gmail.com>.
Hi,
Aliases are supported by Ignite, but you have to use Java names when running
in-memory queries, not DB names. You should refer to the CacheConfig class
for mappings. For example, here is the entry for activity_id field:
vals.add(new JdbcTypeField(Types.BIGINT, "ACTIVITY_ID", long.class,
"activityId"));
Java field name (the one used in the object) is activityId, so the query
should start like this:
SELECT DISTINCT activity0.activityId, ...
Please let me know if it helps.
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-tp4281p4298.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.