New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Inbound handling for ORA-01795 #1022

Closed
eldest opened this Issue Oct 29, 2014 · 10 comments

Comments

Projects
None yet
3 participants
@eldest

eldest commented Oct 29, 2014

Is there an inbound support for this?
http://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is

I use querydsl(3.4.2) to work with Oracle DB.

The next query:

    @Override
    @QueryDslSupport
    public List<DocumentHdr> findAll(List<Long> ids) {
        return getHibernateQuery()
                .from(documentHdr)
                .where(documentHdr.id.in(ids))
                .orderBy(documentHdr.enterprise.name.asc(), documentHdr.field.name.asc(), documentHdr.wellName.asc())
                .list(documentHdr);
    }

works fine until i get more than 1000 of elements at ids list, so I have to split it on the several lists and add them to "in" part via "or" statement.

The exception looks like:

19:08:18,467 ERROR [c.l.w.aop.ExceptionInterceptor] Method 'AbstractDocumentService.getTablePartition(..)', with args: [5, 2, [5693, 580, 5275, 6006....
 has thrown an exception, 
java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is - 1000
@Shredder121

This comment has been minimized.

Show comment
Hide comment
@Shredder121

Shredder121 Oct 29, 2014

Member

You can for now use Iterables.partition() and build the where with each list.

I think we first have to think about including this in Querydsl.
@timowest what do you think?

EDIT: since in() accepts a Collection, it is required to use Lists.partition() instead.

Member

Shredder121 commented Oct 29, 2014

You can for now use Iterables.partition() and build the where with each list.

I think we first have to think about including this in Querydsl.
@timowest what do you think?

EDIT: since in() accepts a Collection, it is required to use Lists.partition() instead.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Oct 29, 2014

Member

This is difficult to abstract in Querydsl since the in operation might be embedded quite deep, so it might be difficult to get the logic right if we decide to execute multiple queries and merge the results. Also pagination etc will make this difficult.

But if the workaround is just splitting the list and creating multiple in expressions and combining them via or, then this could be maybe done by Querydsl.

Member

timowest commented Oct 29, 2014

This is difficult to abstract in Querydsl since the in operation might be embedded quite deep, so it might be difficult to get the logic right if we decide to execute multiple queries and merge the results. Also pagination etc will make this difficult.

But if the workaround is just splitting the list and creating multiple in expressions and combining them via or, then this could be maybe done by Querydsl.

@eldest

This comment has been minimized.

Show comment
Hide comment
@eldest

eldest Oct 30, 2014

Yes, this is how I solved it for now.

    @Override
    @QueryDslSupport
    public List<DocumentHdr> findAll(List<Long> ids) {
        Iterator<List<Long>> iterator = Lists.partition(ids, 1000).iterator();
        BooleanExpression whereStatement = documentHdr.id.in(iterator.next());
        while (iterator.hasNext()) whereStatement = whereStatement.or(documentHdr.id.in(iterator.next()));

        return getHibernateQuery()
                .from(documentHdr)
                .where(whereStatement)
                .orderBy(documentHdr.enterprise.name.asc(), documentHdr.field.name.asc(), documentHdr.wellName.asc())
                .list(documentHdr);
    }

or

    @Override
    @QueryDslSupport
    public List<DocumentHdr> findAll(List<Long> ids) {
        return getHibernateQuery()
                .from(documentHdr)
                .where(QueryDslUtils.getInExpression(documentHdr.id, ids))
                .orderBy(documentHdr.enterprise.name.asc(), documentHdr.field.name.asc(), documentHdr.wellName.asc())
                .list(documentHdr);
    }

    public static <T> BooleanExpression getInExpression(SimpleExpression<T> expression, Collection<T> collection) {
        Iterator<List<T>> iterator = Iterables.partition(collection, 1000).iterator();
        BooleanExpression whereExpression = expression.in(iterator.next());
        while (iterator.hasNext()) whereExpression = whereExpression.or(expression.in(iterator.next()));

        return whereExpression;
    }

eldest commented Oct 30, 2014

Yes, this is how I solved it for now.

    @Override
    @QueryDslSupport
    public List<DocumentHdr> findAll(List<Long> ids) {
        Iterator<List<Long>> iterator = Lists.partition(ids, 1000).iterator();
        BooleanExpression whereStatement = documentHdr.id.in(iterator.next());
        while (iterator.hasNext()) whereStatement = whereStatement.or(documentHdr.id.in(iterator.next()));

        return getHibernateQuery()
                .from(documentHdr)
                .where(whereStatement)
                .orderBy(documentHdr.enterprise.name.asc(), documentHdr.field.name.asc(), documentHdr.wellName.asc())
                .list(documentHdr);
    }

or

    @Override
    @QueryDslSupport
    public List<DocumentHdr> findAll(List<Long> ids) {
        return getHibernateQuery()
                .from(documentHdr)
                .where(QueryDslUtils.getInExpression(documentHdr.id, ids))
                .orderBy(documentHdr.enterprise.name.asc(), documentHdr.field.name.asc(), documentHdr.wellName.asc())
                .list(documentHdr);
    }

    public static <T> BooleanExpression getInExpression(SimpleExpression<T> expression, Collection<T> collection) {
        Iterator<List<T>> iterator = Iterables.partition(collection, 1000).iterator();
        BooleanExpression whereExpression = expression.in(iterator.next());
        while (iterator.hasNext()) whereExpression = whereExpression.or(expression.in(iterator.next()));

        return whereExpression;
    }

@timowest timowest added this to the 3.6.0 milestone Nov 1, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 1, 2014

Member

@eldest I added now a pull request that fixes the issue. Could you take a look and maybe try it out?

Member

timowest commented Nov 1, 2014

@eldest I added now a pull request that fixes the issue. Could you take a look and maybe try it out?

@eldest

This comment has been minimized.

Show comment
Hide comment
@eldest

eldest Nov 2, 2014

@timowest Ok, thank you, but I'll be able to test it at wednesday only.

eldest commented Nov 2, 2014

@timowest Ok, thank you, but I'll be able to test it at wednesday only.

@eldest

This comment has been minimized.

Show comment
Hide comment
@eldest

eldest Nov 6, 2014

@timowest Well, I've tried it out and I have to say that It doesn't work for me.
I use HibernateQuery for my queries, and as I understand I need the same collection splitting at JPQLSerializer.visitPathInCollection and listMaxSize parameter at HQLTemplates or even upper.
As for HibernateSQLQuery, I don't know how to use it together with HibernateQuery, so I can't check it.

eldest commented Nov 6, 2014

@timowest Well, I've tried it out and I have to say that It doesn't work for me.
I use HibernateQuery for my queries, and as I understand I need the same collection splitting at JPQLSerializer.visitPathInCollection and listMaxSize parameter at HQLTemplates or even upper.
As for HibernateSQLQuery, I don't know how to use it together with HibernateQuery, so I can't check it.

@Shredder121

This comment has been minimized.

Show comment
Hide comment
@Shredder121

Shredder121 Nov 6, 2014

Member

The current logic is only for querydsl-sql and for NativeSQL queries.

As for JPQL, there is not really a way for us to reach the underlying database, and I don't think we want to do that.
@timowest we could provide a setter for listMaxSize in the JPQLTemplates, so that end users can manually set the max size, and use the same logic from SQLSerializer.

Member

Shredder121 commented Nov 6, 2014

The current logic is only for querydsl-sql and for NativeSQL queries.

As for JPQL, there is not really a way for us to reach the underlying database, and I don't think we want to do that.
@timowest we could provide a setter for listMaxSize in the JPQLTemplates, so that end users can manually set the max size, and use the same logic from SQLSerializer.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 9, 2014

Member

As already mentioned in the pull request fixing this for JPA queries is problematic, since this would make the contract between Querydsl and the JPA provider too blurry. The Querydsl JPA serialization contract is to create valid JPQL queries, the JPA provider will then create valid SQL out of that.

After this fix the expression creation will at least be a lot easier for JPA queries

ExpressionUtils.inAny(documentHdr.id, Lists.partition(ids, 1000));

compared to the non transformed

documentHdr.id.in(ids);
Member

timowest commented Nov 9, 2014

As already mentioned in the pull request fixing this for JPA queries is problematic, since this would make the contract between Querydsl and the JPA provider too blurry. The Querydsl JPA serialization contract is to create valid JPQL queries, the JPA provider will then create valid SQL out of that.

After this fix the expression creation will at least be a lot easier for JPA queries

ExpressionUtils.inAny(documentHdr.id, Lists.partition(ids, 1000));

compared to the non transformed

documentHdr.id.in(ids);
@Shredder121

This comment has been minimized.

Show comment
Hide comment
@Shredder121

Shredder121 Nov 9, 2014

Member

In addition to the code snippet above, you can also use Iterables.partition(), which accepts any Iterable like Set.

I first thought Iterables.partition worked like Splitter, splitting to an Iterable, but it is partitioned to a List

Member

Shredder121 commented Nov 9, 2014

In addition to the code snippet above, you can also use Iterables.partition(), which accepts any Iterable like Set.

I first thought Iterables.partition worked like Splitter, splitting to an Iterable, but it is partitioned to a List

@Shredder121 Shredder121 closed this in #1026 Nov 16, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 30, 2014

Member

Released in 3.6.0

Member

timowest commented Nov 30, 2014

Released in 3.6.0

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