[1.3.174] Error while parsing an SQL

[GoogleCodeExporter]

Hallo,
I use a Query (which works in 1.3.171) 

SELECT cnt, ts_1/100 hours, ts_1 % 100 minutes FROM (SELECT AVGc cnt, ts % 
10000 ts_1 FROM (SELECT COUNT(*) c, 
10000*DAY(START_TIME_STAMP)+HOUR(START_TIME_STAMP)*100+15*ROUND(MINUTE(START_TIM
E_STAMP)/15) ts FROM REQUEST  WHERE SERVER_INSTANCE=? GROUP BY ts ORDER BY ts) 
GROUP BY ts_1 ORDER BY ts_1 )

This query produces this error by calling with entityManager#createNativeQuery 
by the entitymanager of hibernate.

This conversion breaks:

SELECT
    COUNT(*) AS C,
    (((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + (15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))) AS TS
FROM PUBLIC.REQUEST
WHERE SERVER_INSTANCE = ?1
GROUP BY ((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + 
(15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))
ORDER BY =(((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + 
(15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))) AS[*] TS

On 1.3.171 it works.

Ciao
  Peter Schütt

P.S. The full stacktrace:

>Exception occurred in target VM: org.hibernate.exception.SQLGrammarException: 
could not execute query 
javax.persistence.PersistenceException: 
org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1215)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1148)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
    at com.timocom.process.profile.statistics.RequestStatistics.getRequestDistributionOverTheDay(RequestStatistics.java:163)
    at com.timocom.process.profile.statistics.TestRequestStatistics.testGetRequestDistributionOverTheDay(TestRequestStatistics.java:195)
    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 org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:264)
    at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:153)
    at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:124)
    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 org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray2(ReflectionUtils.java:208)
    at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:159)
    at org.apache.maven.surefire.booter.ProviderFactory.invokeProvider(ProviderFactory.java:87)
    at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:153)
    at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:95)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
    ... 33 more
Caused by: org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT
    COUNT(*) AS C,
    (((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + (15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))) AS TS
FROM PUBLIC.REQUEST
WHERE SERVER_INSTANCE = ?1
GROUP BY ((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + 
(15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))
ORDER BY =(((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + 
(15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))) AS[*] TS"
Syntax error in SQL statement "SELECT
    COUNT(*) AS C,
    (((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + (15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))) AS TS
FROM PUBLIC.REQUEST
WHERE SERVER_INSTANCE = ?1
GROUP BY ((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + 
(15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))
ORDER BY =(((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + 
(15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))) AS[*] TS"; SQL statement:
CREATE FORCE VIEW PUBLIC._96 AS
SELECT
    COUNT(*) AS C,
    (((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + (15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))) AS TS
FROM PUBLIC.REQUEST
WHERE SERVER_INSTANCE = ?1
GROUP BY ((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + 
(15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))
ORDER BY =(((10000 * DAY(START_TIME_STAMP)) + (HOUR(START_TIME_STAMP) * 100)) + 
(15 * ROUND((MINUTE(START_TIME_STAMP) / 15)))) AS TS [42000-174]
    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.message.DbException.getSyntaxError(DbException.java:184)
    at org.h2.command.Parser.getSyntaxError(Parser.java:497)
    at org.h2.command.Parser.prepare(Parser.java:213)
    at org.h2.engine.Session.prepare(Session.java:404)
    at org.h2.engine.Session.prepare(Session.java:391)
    at org.h2.table.TableView.compileViewQuery(TableView.java:101)
    at org.h2.table.TableView.initColumnsAndTables(TableView.java:146)
    at org.h2.table.TableView.init(TableView.java:97)
    at org.h2.table.TableView.<init>(TableView.java:63)
    at org.h2.table.TableView.createTempView(TableView.java:451)
    at org.h2.command.Parser.readTableFilter(Parser.java:1063)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1724)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1832)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1718)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1560)
    at org.h2.command.Parser.readTableFilter(Parser.java:1052)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1724)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1832)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1718)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1560)
    at org.h2.command.Parser.parseSelect(Parser.java:1548)
    at org.h2.command.Parser.parsePrepared(Parser.java:415)
    at org.h2.command.Parser.parse(Parser.java:289)
    at org.h2.command.Parser.parse(Parser.java:265)
    at org.h2.command.Parser.prepareCommand(Parser.java:226)
    at org.h2.engine.Session.prepareLocal(Session.java:437)
    at org.h2.engine.Session.prepareCommand(Session.java:380)
    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.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
    at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1700)
    at org.hibernate.loader.Loader.doQuery(Loader.java:801)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    ... 40 more
<

Original issue reported on code.google.com by pep...@googlemail.com on 11 Nov 2013 at 10:44

[GoogleCodeExporter]

If you can reduce this to a standalone testcase, I can take a look at, 
otherwise I'm not likely to get to it anytime soon.

Original comment by noelgrandin on 14 Nov 2013 at 7:29

[GoogleCodeExporter]

I think this is fixed in the trunk, change log entry "Subqueries or views with 
"order by" an alias expression could not be executed due to a regression 
introduced in version 1.3.174.". Could you verify this please?

Original comment by thomas.t...@gmail.com on 16 Nov 2013 at 1:00

• Changed state: Accepted

[GoogleCodeExporter]

Hi Thomas,

I am facing a similar issue and at least I can corm that rolling back to 
1.3.173 works.

Can you please let us know when the 1.3.175 is planned to release?

Thanks,
Shoaib

Original comment by shoaib.a...@gmail.com on 18 Dec 2013 at 3:53

[GoogleCodeExporter]

> when the 1.3.175 is planned to release?

I'm not sure, but I think it will be early next year (in the first 3 weeks).

Original comment by thomas.t...@gmail.com on 18 Dec 2013 at 8:02

[GoogleCodeExporter]

Fixed in version 1.3.175

Original comment by thomas.t...@gmail.com on 18 Jan 2014 at 6:17

• Changed state: Fixed

[GoogleCodeExporter]

Thanks for the great news. When will be 1.3.175 pushed to Maven?

Original comment by shoaib.a...@gmail.com on 20 Jan 2014 at 3:05

[GoogleCodeExporter]

I think it's there already:
http://repo2.maven.org/maven2/com/h2database/h2/

See also http://h2database.com/html/build.html#maven2

Original comment by thomas.t...@gmail.com on 20 Jan 2014 at 3:12

[GoogleCodeExporter]

Yeah, you are right. I can also confirm that I am no more running into the bad 
SQL issue I was facing earlier with 1.3.1.74. Great work. Thanks Thomas.

Original comment by shoaib.a...@gmail.com on 20 Jan 2014 at 3:49

[GoogleCodeExporter]

Hi Thomas,

I just realized that one of the older issue got regressed in 175 build. That is 
for Java procedures that you get an error because of -Dsun.java2d.d3d=false in 
your path.

Original comment by shoaib.a...@gmail.com on 21 Feb 2014 at 5:51

[GoogleCodeExporter]

Hi,

Could you send a mail to the Google Group about this issue please? Please don't 
open a new issue, as only very few people monitor issues, while there is more 
help in the Google Group.

Original comment by thomas.t...@gmail.com on 21 Feb 2014 at 6:44