AbstractSQLQuery listResults with offset and limit causes SQLSyntaxErrorException in Oracle11g #140

Closed
dileeph opened this Issue May 4, 2012 · 1 comment

Comments

Projects
None yet
2 participants
@dileeph

dileeph commented May 4, 2012

Issue

Code

from(bookmark).orderBy(bookmark.bookmarkId.asc()).offset(2).limit(10).listResults(bookmark);

throws SQLSyntaxErrorException.

If the code was simply

from(bookmark).orderBy(bookmark.bookmarkId.asc()).listResults(bookmark);

the exception is not there.

Version: querydsl-sql-2.5.0. (However from code history it looks like this issue has always been there!).

Reason
At database level, reason is that the query generated has the same column names twice in the select portion.

Code snippet 1 gives query

select * from (
       select a.*, rownum rn from (
               select bk.TITLE, bk.YAXIS, bk.BOOKMARK_ID, bk.TITLE, bk.YAXIS,bk.BOOKMARK_ID
              from BOOKMARK bk
              order by bk.BOOKMARK_ID asc
 ) a) where rn > 2 and rn <= 12

causing "ORA-00918: column ambiguously defined" at Oracle level. If the select blocks around the inner query were not there Oracle doesn't complain as expected.

Reason at code level is that "queryMixin.addToProjection(expr);" is called twice, once in "public SearchResults listResults(Expression expr)" and once again in " public CloseableIterator iterate(Expression expr)".

    @Override
    public <RT> SearchResults<RT> listResults(Expression<RT> expr) {
        queryMixin.addToProjection(expr);
        long total = count();
        try {
            if (total > 0) {
                QueryModifiers modifiers = queryMixin.getMetadata().getModifiers();
                return new SearchResults<RT>(list(expr), modifiers, total);
            } else {
                return SearchResults.emptyResults();
            }

        } finally {
            reset();
        }
    }

    @Override
    public <RT> CloseableIterator<RT> iterate(Expression<RT> expr) {
        expr = queryMixin.convert(expr);
        queryMixin.addToProjection(expr);
        return iterateSingle(queryMixin.getMetadata(), expr);
    }

Suggested fix
Remove the addToProjection line in listResults.

    @Override
    public <RT> SearchResults<RT> listResults(Expression<RT> expr) {
        
        /* comment this line out. **/
        //queryMixin.addToProjection(expr);
        long total = count();
        try {
            if (total > 0) {
                QueryModifiers modifiers = queryMixin.getMetadata().getModifiers();
                return new SearchResults<RT>(list(expr), modifiers, total);
            } else {
                return SearchResults.emptyResults();
            }




        } finally {
            reset();
        }
    }

Full trace

  com.mysema.query.QueryException: Caught SQLSyntaxErrorException for select * from (
  select a.*, rownum rn from (
  select bk.TITLE, bk.YAXIS, bk.BMK_TYPE, bk.XAXIS, bk.ZOOM, bk.USERID, bk.BOOKMARK_ID, bk.CLIPPING_STATUS,   bk.DEFAULT_MAP, bk.MAP_TYPE, bk.ACCESS_TYPE, bk.TITLE, bk.YAXIS, bk.BMK_TYPE, bk.XAXIS, bk.ZOOM, bk.USERID,       bk.BOOKMARK_ID, bk.CLIPPING_STATUS, bk.DEFAULT_MAP, bk.MAP_TYPE, bk.ACCESS_TYPE
   from BOOKMARK bk
   order by bk.BOOKMARK_ID asc
    ) a) where rn > 2 and rn <= 12
at com.mysema.query.sql.AbstractSQLQuery.iterateSingle(AbstractSQLQuery.java:450)
at com.mysema.query.sql.AbstractSQLQuery.iterate(AbstractSQLQuery.java:344)
at com.mysema.query.sql.AbstractSQLQuery.list(AbstractSQLQuery.java:464)
at com.mysema.query.sql.AbstractSQLQuery.listResults(AbstractSQLQuery.java:474)
at com.immco.mapzilladbsrc.AppTest.listSearchBookmarks(AppTest.java:70)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:30)
at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at com.mysema.query.sql.AbstractSQLQuery.iterateSingle(AbstractSQLQuery.java:412)
... 29 more

timowest added a commit that referenced this issue May 4, 2012

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest May 25, 2012

Member

Released in 2.6.0

Member

timowest commented May 25, 2012

Released in 2.6.0

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment