QueryDSL-SQL Generates Bad Query With Oracle, CTE's and limit() #1487

Closed
Redattack34 opened this Issue Aug 18, 2015 · 0 comments

Projects

None yet

2 participants

@Redattack34

Using QueryDSL 4.0.3. I have several queries which use recursive CTE's to search a version table. This works fine with H2 and PostgreSQL. However, I'm trying to get it to work with Oracle and it's producing invalid SQL.

Here's my code:

new SQLQuery<>( conn, templates )
.withRecursive( versionCTE, versionCTE.versionId, versionCTE.versionDepId, versionCTE.versionCreated )
    .as( SQLExpressions.unionAll(
            SQLExpressions.select( version.versionId, version.versionDependsOnId, version.versionCreated )
                .from( version )
                .join( workspace ).on( workspace.workspaceCurrentVersionId.eq( version.versionId ) )
                .where( workspace.workspaceName.eq( params.getWorkspace().val() ),
                    workspace.satellite.eq( params.getSatellite().val() ) ),
             SQLExpressions.select( version.versionId, version.versionDependsOnId, version.versionCreated )
                 .from( version, versionCTE )
                 .where( version.versionId.eq( versionCTE.versionDepId ) ) ) )
.select( baseTable, versionedTable, versionCTE.versionId )
.from( versionCTE, baseTable, versionedTable )
.where(
    versionedPred.apply( id )
        .and( tableVersionId.eq( versionCTE.versionId ) )
        .and( satelliteMatches( versionedSatellite, params.getSatellite() ) ),
    basePred.apply( id )
        .and( satelliteMatches( baseSatellite, params.getSatellite() ) ) )
.orderBy( new OrderSpecifier<>( Order.DESC, versionCTE.versionCreatedAsDateTime ) )
.limit( 1 )
.fetchOne();

This is the malformed SQL generated by QueryDSL (I modified it slightly to cut out a huge list of columns):

with select * from (
 versionCTE (version_id, version_depends_on_id, version_created) as ((select version.version_id, version.version_depends_on_id, version.version_created
from version version
join workspace workspace
on workspace.workspace_current_version_id = version.version_id
where workspace.workspace_name = ? and workspace.satellite = ?)
union all
(select version.version_id, version.version_depends_on_id, version.version_created
from version version, versionCTE versionCTE
where version.version_id = versionCTE.version_depends_on_id))
select access_structure.*, access_structure_pdata.*, versionCTE.*
from versionCTE versionCTE, access_structure access_structure, access_structure_pdata access_structure_pdata
where access_structure_pdata.struct_id = ? and access_structure_pdata.version_id = versionCTE.version_id and access_structure_pdata.satellite = ? and (access_structure.struct_id = ? and access_structure.satellite = ?)
order by cast(versionCTE.version_created as timestamp) desc
) where rownum <= ?`

Notice how the CTE's with is interfering with the select * from (...) where rownum <= ? wrapper produced by the limit() call. Moving the with inside the select * from ( clause executes correctly, as does moving the entire CTE outside of the select * from ( clause leaving only the select access_structure.*, ... query inside it.

@timowest timowest added the progress label Aug 27, 2015
@Shredder121 Shredder121 closed this in #1501 Aug 31, 2015
@timowest timowest removed the progress label Aug 31, 2015
@timowest timowest added this to the 4.0.4 milestone Aug 31, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment