Project

General

Profile

Bug #2347

hql to SQL name mapping issue

Added by Ovidiu Maxiniuc almost 10 years ago. Updated about 8 years ago.

Status:
Closed
Priority:
High
Assignee:
Ovidiu Maxiniuc
Target version:
-
Start date:
Due date:
% Done:

100%

billable:
No
vendor_id:
GCD
case_num:

om_upd20140729a.zip (57 KB) Ovidiu Maxiniuc, 07/29/2014 02:14 PM

History

#1 Updated by Ovidiu Maxiniuc almost 10 years ago

  • Priority changed from Normal to High
  • Start date deleted (07/28/2014)

Consider the following Progress code sample:

define temp-table tt9
   field ci as character
   field cs as character case-sensitive.

create tt9.
ci = " Abc ".
cs = " Abc ".
validate tt9.

create tt9.
ci = "Abc".
cs = "Abc".
validate tt9.

find first tt9 where ci = trim(ci) and length(trim(cs)) = 3. 
display "found it!".

This is a correct Progress procedure and when executed should print evidently (the 2 nd record satisfies the where clause from find)
┌─────────┐
│found it!│
└─────────┘

The procedure is correctly converted into java as:
            RecordBuffer.openScope(tt9);
            tt9.create();
            tt9.setCi(new character(" Abc "));
            tt9.setCs(new character(" Abc "));
            tt9.validate();
            tt9.create();
            tt9.setCi(new character("Abc"));
            tt9.setCs(new character("Abc"));
            tt9.validate();
            new FindQuery(tt9, "upper(tt9.ci) = trimws(upper(tt9.ci)) and lengthOf(trimws(upper(tt9.cs))) = 3", null, "tt9.id asc").first();

            FrameElement[] elementList0 = new FrameElement[]
            {
               new Element("found it!", frame0.widgetExpr1())
            };

            frame0.display(elementList0);

When executed, the following messages are printed:
[07/28/2014 20:46:23 EEST] (org.hibernate.engine.jdbc.spi.SqlExceptionHelper:WARN) SQL Error: 90022, SQLState: 90022
[07/28/2014 20:46:23 EEST] (org.hibernate.engine.jdbc.spi.SqlExceptionHelper:ERROR) Function "TRIMWS" not found; SQL statement:
select temprecord0_.id as col_0_0_ from tt1 temprecord0_ where temprecord0_._multiplex=? and temprecord0_.__ici=trimws(temprecord0_.__ici) and lengthOf(trimws(upper(temprecord0_.__scs)))=3 order by temprecord0_.__ici asc, temprecord0_.__scs desc, temprecord0_._multiplex asc, temprecord0_.id desc limit ? [90022-169]
[07/28/2014 20:46:23 EEST] (com.goldencode.p2j.persist.Persistence:WARNING) [00000001:00000007:bogus-->local/_temp/primary] error loading record
select tt9.id from TempRecord1Impl as tt9 where (tt9._multiplex = ?0) and (tt9.__ici = trimws(tt9.__ici) and lengthOf(trimws(upper(tt9.__scs))) = 3) order by tt9.__ici asc, tt9.__scs desc, tt9._multiplex asc, tt9.id desc
[07/28/2014 20:46:23 EEST] (ErrorManager:SEVERE) {00000001:00000007:bogus} stack trace follows
com.goldencode.p2j.persist.PersistenceException: [00000001:00000007:bogus-->local/_temp/primary] error loading record
        at com.goldencode.p2j.persist.Persistence.load(Persistence.java:1879)
        at com.goldencode.p2j.persist.RandomAccessQuery.executeImpl(RandomAccessQuery.java:3187)
        at com.goldencode.p2j.persist.RandomAccessQuery.execute(RandomAccessQuery.java:2493)
        at com.goldencode.p2j.persist.RandomAccessQuery.first(RandomAccessQuery.java:1161)
        at com.goldencode.p2j.persist.RandomAccessQuery.first(RandomAccessQuery.java:1067)
        at com.goldencode.testcases.<customer>_schema.TestRoot$1.body(TestRoot.java:42)
        at com.goldencode.p2j.util.BlockManager.processBody(BlockManager.java:7048)
        at com.goldencode.p2j.util.BlockManager.topLevelBlock(BlockManager.java:6881)
        at com.goldencode.p2j.util.BlockManager.externalProcedure(BlockManager.java:221)
        at com.goldencode.p2j.util.BlockManager.externalProcedure(BlockManager.java:203)
        at com.goldencode.testcases.<customer>_schema.TestRoot.execute(TestRoot.java:28)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at com.goldencode.p2j.util.Utils.invoke(Utils.java:1234)
        at com.goldencode.p2j.main.StandardServer$MainInvoker.execute(StandardServer.java:1701)
        at com.goldencode.p2j.main.StandardServer.invoke(StandardServer.java:1201)
        at com.goldencode.p2j.main.StandardServer.standardEntry(StandardServer.java:364)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at com.goldencode.p2j.util.MethodInvoker.invoke(MethodInvoker.java:76)
        at com.goldencode.p2j.net.Dispatcher.processInbound(Dispatcher.java:694)
        at com.goldencode.p2j.net.Conversation.block(Conversation.java:319)
        at com.goldencode.p2j.net.Conversation.run(Conversation.java:163)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.GenericJDBCException: Function "TRIMWS" not found; SQL statement:
select temprecord0_.id as col_0_0_ from tt1 temprecord0_ where temprecord0_._multiplex=? and temprecord0_.__ici=trimws(temprecord0_.__ici) and lengthOf(trimws(upper(temprecord0_.__scs)))=3 order by temprecord0_.__ici asc, temprecord0_.__scs desc, temprecord0_._multiplex asc, temprecord0_.id desc limit ? [90022-169]
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
        at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:146)
        at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
        at com.sun.proxy.$Proxy4.prepareStatement(Unknown Source)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1749)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1726)
        at org.hibernate.loader.Loader.doQuery(Loader.java:852)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:293)
        at org.hibernate.loader.Loader.doList(Loader.java:2411)
        at org.hibernate.loader.Loader.doList(Loader.java:2397)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2227)
        at org.hibernate.loader.Loader.list(Loader.java:2222)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
        at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1247)
        at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
        at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:905)
        at com.goldencode.p2j.persist.Persistence.load(Persistence.java:1723)
        at com.goldencode.p2j.persist.RandomAccessQuery.executeImpl(RandomAccessQuery.java:3187)
        at com.goldencode.p2j.persist.RandomAccessQuery.execute(RandomAccessQuery.java:2493)
        at com.goldencode.p2j.persist.RandomAccessQuery.first(RandomAccessQuery.java:1161)
        at com.goldencode.p2j.persist.RandomAccessQuery.first(RandomAccessQuery.java:1067)
        at com.goldencode.testcases.<customer>_schema.TestRoot$1.body(TestRoot.java:42)
        at com.goldencode.p2j.util.BlockManager.processBody(BlockManager.java:7048)
        at com.goldencode.p2j.util.BlockManager.topLevelBlock(BlockManager.java:6881)
        at com.goldencode.p2j.util.BlockManager.externalProcedure(BlockManager.java:221)
        at com.goldencode.p2j.util.BlockManager.externalProcedure(BlockManager.java:203)
        at com.goldencode.testcases.<customer>_schema.TestRoot.execute(TestRoot.java:28)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at com.goldencode.p2j.util.Utils.invoke(Utils.java:1234)
        at com.goldencode.p2j.main.StandardServer$MainInvoker.execute(StandardServer.java:1701)
        at com.goldencode.p2j.main.StandardServer.invoke(StandardServer.java:1201)
        at com.goldencode.p2j.main.StandardServer.standardEntry(StandardServer.java:364)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at com.goldencode.p2j.util.MethodInvoker.invoke(MethodInvoker.java:76)
        at com.goldencode.p2j.net.Dispatcher.processInbound(Dispatcher.java:694)
        at com.goldencode.p2j.net.Conversation.block(Conversation.java:319)
        at com.goldencode.p2j.net.Conversation.run(Conversation.java:163)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.h2.jdbc.JdbcSQLException: Function "TRIMWS" not found; SQL statement:
select temprecord0_.id as col_0_0_ from tt1 temprecord0_ where temprecord0_._multiplex=? and temprecord0_.__ici=trimws(temprecord0_.__ici) and lengthOf(trimws(upper(temprecord0_.__scs)))=3 order by temprecord0_.__ici asc, temprecord0_.__scs desc, temprecord0_._multiplex asc, temprecord0_.id desc limit ? [90022-169]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
        at org.h2.message.DbException.get(DbException.java:169)
        at org.h2.message.DbException.get(DbException.java:146)
        at org.h2.command.Parser.readJavaFunction(Parser.java:2122)
        at org.h2.command.Parser.readFunction(Parser.java:2174)
        at org.h2.command.Parser.readTerm(Parser.java:2476)
        at org.h2.command.Parser.readFactor(Parser.java:2046)
        at org.h2.command.Parser.readSum(Parser.java:2033)
        at org.h2.command.Parser.readConcat(Parser.java:2006)
        at org.h2.command.Parser.readCondition(Parser.java:1964)
        at org.h2.command.Parser.readAnd(Parser.java:1854)
        at org.h2.command.Parser.readExpression(Parser.java:1844)
        at org.h2.command.Parser.parseSelectSimple(Parser.java:1801)
        at org.h2.command.Parser.parseSelectSub(Parser.java:1684)
        at org.h2.command.Parser.parseSelectUnion(Parser.java:1527)
        at org.h2.command.Parser.parseSelect(Parser.java:1515)
        at org.h2.command.Parser.parsePrepared(Parser.java:405)
        at org.h2.command.Parser.parse(Parser.java:279)
        at org.h2.command.Parser.parse(Parser.java:251)
        at org.h2.command.Parser.prepareCommand(Parser.java:217)
        at org.h2.engine.Session.prepareLocal(Session.java:415)
        at org.h2.engine.Session.prepareCommand(Session.java:364)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1114)
        at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:74)
        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:265)
        at sun.reflect.GeneratedMethodAccessor318.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
        at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
        at com.sun.proxy.$Proxy4.prepareStatement(Unknown Source)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1749)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1726)
        at org.hibernate.loader.Loader.doQuery(Loader.java:852)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:293)
        at org.hibernate.loader.Loader.doList(Loader.java:2411)
        at org.hibernate.loader.Loader.doList(Loader.java:2397)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2227)
        at org.hibernate.loader.Loader.list(Loader.java:2222)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
        at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1247)
        at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
        at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:905)
        at com.goldencode.p2j.persist.Persistence.load(Persistence.java:1723)
        at com.goldencode.p2j.persist.RandomAccessQuery.executeImpl(RandomAccessQuery.java:3187)
        at com.goldencode.p2j.persist.RandomAccessQuery.execute(RandomAccessQuery.java:2493)
        at com.goldencode.p2j.persist.RandomAccessQuery.first(RandomAccessQuery.java:1161)
        at com.goldencode.p2j.persist.RandomAccessQuery.first(RandomAccessQuery.java:1067)
        at com.goldencode.testcases.<customer>_schema.TestRoot$1.body(TestRoot.java:42)
        at com.goldencode.p2j.util.BlockManager.processBody(BlockManager.java:7048)
        at com.goldencode.p2j.util.BlockManager.topLevelBlock(BlockManager.java:6881)
        at com.goldencode.p2j.util.BlockManager.externalProcedure(BlockManager.java:221)
        at com.goldencode.p2j.util.BlockManager.externalProcedure(BlockManager.java:203)
        at com.goldencode.testcases.<customer>_schema.TestRoot.execute(TestRoot.java:28)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at com.goldencode.p2j.util.Utils.invoke(Utils.java:1234)
        at com.goldencode.p2j.main.StandardServer$MainInvoker.execute(StandardServer.java:1701)
        at com.goldencode.p2j.main.StandardServer.invoke(StandardServer.java:1201)
        at com.goldencode.p2j.main.StandardServer.standardEntry(StandardServer.java:364)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at com.goldencode.p2j.util.MethodInvoker.invoke(MethodInvoker.java:76)
        at com.goldencode.p2j.net.Dispatcher.processInbound(Dispatcher.java:694)
        at com.goldencode.p2j.net.Conversation.block(Conversation.java:319)
        at com.goldencode.p2j.net.Conversation.run(Conversation.java:163)
        at java.lang.Thread.run(Thread.java:745)

First I thought this is a MSSQL issue, but after this test-case it's clear that it's also a H2 issue. PostgreSQL is not affected because the ddr declares the correct functions names (and they are resolved directly by SQL rather than looked up when preparing the HQL/SQL).

In details:
When initializing the database, the com.goldencode.p2j.persist.pl.Functions.trimWS() is registered to HQLPreprocessor with its unique name (in this case trimWS, same as method as it is not overloaded).
At runtime, when looked up, the name of the function from generated hql is used (@trimws). Such mapping is not found so the error state is printed to console and the query is aborted with error. In order to find the right SQL function, the name of the function from generated hql / java code MUST match the name of the method implementation from Operators / Functions classes because this is how the lookup is performed (the mapping key).

Adding some annotation to declared functions is not useful at runtime lookup, but can be used for automation for extracting pl ddr file and similar solution for MSSQL. I investigated both pl java files and I found only a few methods affected: chr / toChr, ltrimws / ltrimWS, trimws / trimWS, replace / replaceText. A solution would be to rename these methods or hql intermediary function name to match each-other.

Alternatively, we can use the annotation, but when registering the functions/operators with HQLPreprocessor to add another parameter, the name of the HQL function. The name of the Method registered is not important, only its signature.

LE: GES replaced customer's name with <customer> for this entry.

#2 Updated by Ovidiu Maxiniuc almost 10 years ago

The provided solution uses HQLFunction annotation to make available at runtime the name of the function used in generated HQL that a specified method must be called. It will be registered at database initialization and when a function name is encountered in HQLPreprocessor it is looked-up in the internal overload mapping and the correct SQL alias is retrieved.
The HQLFunction is tested when a method is registered. Because it supports immutable/volatile attribute, it also allows p2j.ddr to be automatically generated at conversion time. For simplicity, the name of HQL function obtained used in HQL code is optional, if not specified then it matches the name of the method.

Example:
find first t1 where trim(c1) = "v1".
Will be converted as
new FindQuery(t1, "trimws(t1.f1) = 'v1'", null, "t1.id asc").first();
The Functions.trimWS is annotated with HQLFunction(name="trimws"). So at database initialization H2 will register "db:trimws:TEXT" to "trimWS_1" and create the trimWS_1 as alias to Functions.trimWS.
When HQLPreprocessor parses the hql and encounters the trimws function it is looked-up (in manuallyOverload) and will replace rewrite the hql as "trimWS_1(t1.f1) = 'v1'". This will be recognized as alias for our method successfully executes the query.

#3 Updated by Eric Faulhaber over 9 years ago

Ovidiu, were the changes in this update rolled up into om_upd20140811a.zip (#2332)?

#4 Updated by Ovidiu Maxiniuc over 9 years ago

Yes, they are part of om_upd20140811a.zip (#2332).
It's hard for me to maintain them in separate updates, at some point multiple changes occur on the same file.

#5 Updated by Eric Faulhaber over 9 years ago

Makes sense. So no need for a separate code review here. Once that update (0811a) is finished/tested, we can close out this issue.

#6 Updated by Ovidiu Maxiniuc over 8 years ago

  • % Done changed from 70 to 100
  • Assignee set to Ovidiu Maxiniuc

The proposed update was merged to 2332a task branch from #2332 and committed to trunk. This can be closed now.

#7 Updated by Eric Faulhaber about 8 years ago

  • Status changed from WIP to Closed

Also available in: Atom PDF