SELECT ... WHERE ... IN(xxx) and Oracle limits

[digulla]

Some databases like Oracle have a limit on the number of elements that you can specify in an IN().

Has jOOQ some built-in functionality to work around this limit or is that something which I have to handle in my own code?

[Sergey Epik]

jOOQ splits the IN argument into several OR-connected IN arguments

[Lukas Eder]

Yes, thanks Sergey. This was recently discussed on the user group. jOOQ splits long IN clauses after 1000 elements for those dialects that have some limitation. This can be seen here:

https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/InCondition.java

Cheers, Lukas

[digulla]

Sybase has a limit, too: It limits the number of logic operations in a single WHERE and replaces IN() with "field = value1 OR field = value2 OR ..."

This case doesn't seem to be handled. Is that an omission or by design?

[Lukas Eder]

I wasn't aware of such a Sybase (ASE or SQL Anywhere?) limitation. Can you provide a documentation link explaining this? What would need to be adapted by jOOQ?

Cheers
Lukas

[digulla]

Am Donnerstag, 14. Juni 2012 14:59:10 UTC+2 schrieb Lukas Eder:

I wasn't aware of such a Sybase (ASE or SQL Anywhere?) limitation. Can you provide a documentation link explaining this? What would need to be adapted by jOOQ?

I couldn't find it in the docs; try to create an IN with more than 384 (old Sybase) or 1024 (since Sybase 12.5) elements in the IN() clause.

The reason is that Sybase internally transforms "x IN()" into "x = value1 OR x = value2 OR ..." and Sybase only allows 384/1024 local operators in a single SELECT.

PS: Greetings from Danilo Tommasina :-) We'll see you on July, 3rd in Zurich!

Regards,

A. Digulla

[Lukas Eder]

>> I wasn't aware of such a Sybase (ASE or SQL Anywhere?) limitation. Can you
>> provide a documentation link explaining this? What would need to be adapted
>> by jOOQ?
>
> I couldn't find it in the docs; try to create an IN with more than 384 (old
> Sybase) or 1024 (since Sybase 12.5) elements in the IN() clause.
>
> The reason is that Sybase internally transforms "x IN()" into "x = value1 OR
> x = value2 OR ..." and Sybase only allows 384/1024 local operators in a
> single SELECT.

Hmm, yes I roughly remember having seen this before when writing
relevant integration tests. Maybe, my integration tests just skip this
part for Sybase ASE. So how would this be resolved? By replacing bind
values by inline values in generated SQL? Can it even be resolved?

> PS: Greetings from Danilo Tommasina :-) We'll see you on July, 3rd in
> Zurich!

He's working with you? It's a small world! Or at least, it's a small
country! :-) Say hi back to him!

Looking forward to meeting you at the JUGS!

Cheers
Lukas

[digulla]

Am Dienstag, 19. Juni 2012 15:40:00 UTC+2 schrieb Lukas Eder:

> I couldn't find it in the docs; try to create an IN with more than 384 (old
> Sybase) or 1024 (since Sybase 12.5) elements in the IN() clause.
>
> The reason is that Sybase internally transforms "x IN()" into "x = value1 OR
> x = value2 OR ..." and Sybase only allows 384/1024 local operators in a
> single SELECT.

Hmm, yes I roughly remember having seen this before when writing
relevant integration tests. Maybe, my integration tests just skip this
part for Sybase ASE. So how would this be resolved? By replacing bind
values by inline values in generated SQL? Can it even be resolved?

We're not aware of a solution. I think the first step would be a unit test followed by a question on stackoverflow.com :-)

Regards,

A. Digulla

[Lukas Eder]

>> Hmm, yes I roughly remember having seen this before when writing
>> relevant integration tests. Maybe, my integration tests just skip this
>> part for Sybase ASE. So how would this be resolved? By replacing bind
>> values by inline values in generated SQL? Can it even be resolved?
>
>
> We're not aware of a solution. I think the first step would be a unit test
> followed by a question on stackoverflow.com :-)

Yes, funny. My relevant integration test contains a comment related to
this, testLargeINCondition() in
https://github.com/lukaseder/jOOQ/blob/master/jOOQ-test/src/org/jooq/test/_/testcases/PredicateTests.java

I've already tried reaching the max number of bind values for SQL
Server 2008 R8 (2100), and ASE 15.5 (2000). Here's the Stack Overflow
question related to this topic:
http://stackoverflow.com/questions/11266609/how-to-circumvent-the-maximum-number-of-bind-values-for-sybase-ase-and-sql-serve

I assume that inlining of bind variables is the only option... But let's see

N.B: I've just now discovered a troubling bug related to the splitting
of large NOT IN condition, which isn't done correctly by jOOQ:
https://sourceforge.net/apps/trac/jooq/ticket/1515

Cheers
Lukas

[Lukas Eder]

> I've already tried reaching the max number of bind values for SQL
> Server 2008 R8 (2100), and ASE 15.5 (2000). Here's the Stack Overflow
> question related to this topic:
> http://stackoverflow.com/questions/11266609/how-to-circumvent-the-maximum-number-of-bind-values-for-sybase-ase-and-sql-serve
>
> I assume that inlining of bind variables is the only option... But let's see

This will be tracked here:
https://sourceforge.net/apps/trac/jooq/ticket/1520

So far, there is no useful answer short of inlining variables...