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
it's success tested
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.
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
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.