get oracle error ORA-01795 when has more than 1000 org

Description

oracle limit 1000 item on IN clause so you get bellow error when system has more than 1000 org

ORA-01795: maximum number of expressions in a list is 1000 error

it happen by MRole.getOrgWhere build query Org_ID IN (list of org)

work-around
build up condition by (0, Org_ID) IN ((0, org1), (0, org2),....)

Environment

None

Activity

Show:
Hiep Lq
April 24, 2020, 9:05 PM

thanks
agree. separate long "IN" to many "IN" is better for index. will test it and feedback
i just don't like this one "orgWhere.replaceAll" but it's ok for this case.

>> Now, I don't like that I'm changing a public method that could be used by external plugins.
i prefer use old name with overloading function like me do

external plugin that get error only drop on this condition: use fullyQualified for table name + oracle + more than 1000 org
it get sql error syntax instead of ORA-01795. so it's same break.

it mean what plugin break will break, what plugin don't break will don't break

Hiep Lq
April 24, 2020, 9:42 PM

it's success tested

Heng Sin Low
April 24, 2020, 10:47 PM
Edited

The way hiep add it is fine since the existing public method is not change. If the existing public method is a bad one, we should deprecate it - deprecation is a way of putting migration note in code.

For the in limit, we shouldn’t hard code it in code and should add it to the AdempiereDatabase interface - getInLimit() and DB_Oracle should return 1000 (have not check what is the limit for PostgreSQL).

Also, perhaps we should explode whether that can be change to a subquery instead. Regardless of the db limit, a long in clause is just not efficient.

Hiep Lq
June 8, 2020, 3:30 PM

hi your patch is fine and should merge to core

1. caller (of plugin) isn't force to change code
2. in case caller isn't change they get sql syntax error when IN clause more than 1000 item but it's fine because they also get error ORA-01795 without this patch.
anyway if they has situation like me then they need to update code

3. about postgresq. from this thread postgresql-max-number-of-parameters-in-in-clause
1. some JDBC version, limit is 32768 item
2. some postgres version IN should change to ANY to get performance because ANY convert value to temp table
4. by my test with current JDBC of master branch and postgresql
1. IN also use temp table like ANY
2. no limit item (i test with 99999 item)

so we can use hard code and modify for oracle only


on CacheReset process add below code for test

Carlos Ruiz
June 9, 2020, 10:42 AM

Created pull request 109

> For the in limit, we shouldn’t hard code it in code and should add it to the AdempiereDatabase interface
> - getInLimit() and DB_Oracle should return 1000 (have not check what is the limit for PostgreSQL).

Not sure if is worthy, the only DB with such limitation I think is Oracle, postgresql doesn't have it AFAIK (and according to Hiep tests)

> Also, perhaps we should explode whether that can be change to a subquery instead.
> Regardless of the db limit, a long in clause is just not efficient.

Checked the possibility for subquery - is too complex as the list of accessible orgs is composed in different ways depending on many flags and is called recursively in some cases.

Regards,

Carlos Ruiz

Assignee

Hiep Lq

Reporter

Hiep Lq

Labels

Tested By

None

Fix versions

Priority

Major
Configure