Warning on using EntityOperator.IN with MySQL

[Leon Torres-2]

Hi folks,

We just got bit by a bug where the results of a query using EntityOperator.IN
was different from using the equivalent OR expression.

This turned out to be an inconsistency on the party of MySQL.  While we try to
narrow it down and figure out if it should be reported as a bug to the MySQL
foks, I thought it would be a good idea to let you all know about this.

The queries are at the bottom of the email.  You can try them yourself if you
have CRMSFA installed.  Reading the known issues for MySQL at
http://dev.mysql.com/doc/refman/5.0/en/open-bugs.html I don't notice any
possible reason why this would happen other than the vague statement,
"Subquery optimization for IN is not as effective as for =."  Perhaps the
optimizations are suspect.

In any case, I was wondering if we should have the option to make the entity
engine transform IN conditions into a set of OR conditions instead.  IN seems to
be slower than OR across databases, so perhaps it would be a nice option to have
beyond bugs like this.

- Leon


select * from PARTY_RELATIONSHIP where
(role_type_id_from = 'ACCOUNT_TEAM' AND party_id_from IN ('DemoSalesTeam1')
AND party_relationship_type_id = 'ASSIGNED_TO' AND
security_group_id IN ('SALES_MANAGER', 'SALES_REP', 'SALES_REP_LIMITED', 'CSR')
AND ((thru_date IS NULL OR thru_date > '2007-01-12 11:35:58.8') AND (from_date
IS NULL OR from_date <= '2007-01-12 11:35:58.8')));

select * from PARTY_RELATIONSHIP where
(role_type_id_from = 'ACCOUNT_TEAM' AND party_id_from IN ('DemoSalesTeam1')
AND party_relationship_type_id = 'ASSIGNED_TO' AND
(security_group_id = 'SALES_MANAGER' OR security_group_id = 'SALES_REP' OR
security_group_id = 'SALES_REP_LIMITED' OR security_group_id = 'CSR')
AND ((thru_date IS NULL OR thru_date > '2007-01-12 11:55:45.34') AND (from_date
IS NULL OR from_date <= '2007-01-12 11:55:45.34')));

[David E Jones]

On Jan 12, 2007, at 3:02 PM, Leon Torres wrote:

> Hi folks,
>
> We just got bit by a bug where the results of a query using  
> EntityOperator.IN was different from using the equivalent OR  
> expression.

Wow, that is a little scary...

> This turned out to be an inconsistency on the party of MySQL.  
> While we try to narrow it down and figure out if it should be  
> reported as a bug to the MySQL foks, I thought it would be a good  
> idea to let you all know about this.
>
> The queries are at the bottom of the email.  You can try them  
> yourself if you have CRMSFA installed.  Reading the known issues  
> for MySQL at http://dev.mysql.com/doc/refman/5.0/en/open-bugs.html 
> I don't notice any possible reason why this would happen other than  
> the vague statement,
> "Subquery optimization for IN is not as effective as for =."  
> Perhaps the optimizations are suspect.

That sounds like it may be specific to sub-queries, which we don't  
use right now in OFBiz (no one has added support for this in the  
entity engine). It may be referring to the use of IN rather than  
using sub-queries, which is less efficient partially because it  
requires 2 database round-trips, and partially because of what this  
statement is referring to with the database not having it all in one  
go, and so not being able to optimize the query (queries...) as well.

> In any case, I was wondering if we should have the option to make  
> the entity engine transform IN conditions into a set of OR  
> conditions instead.  IN seems to be slower than OR across  
> databases, so perhaps it would be a nice option to have beyond bugs  
> like this.

Hmmm... I'm not sure about IN being slower that OR, where did you  
find that information? I could see it being true perhaps in certain  
databases for a small set of values in the IN, but for larger sets  
I'd be surprised if this were the case.

-David

> - Leon
>
>
> select * from PARTY_RELATIONSHIP where
> (role_type_id_from = 'ACCOUNT_TEAM' AND party_id_from IN  
> ('DemoSalesTeam1')
> AND party_relationship_type_id = 'ASSIGNED_TO' AND
> security_group_id IN ('SALES_MANAGER', 'SALES_REP',  
> 'SALES_REP_LIMITED', 'CSR')
> AND ((thru_date IS NULL OR thru_date > '2007-01-12 11:35:58.8') AND  
> (from_date IS NULL OR from_date <= '2007-01-12 11:35:58.8')));
>
> select * from PARTY_RELATIONSHIP where
> (role_type_id_from = 'ACCOUNT_TEAM' AND party_id_from IN  
> ('DemoSalesTeam1')
> AND party_relationship_type_id = 'ASSIGNED_TO' AND
> (security_group_id = 'SALES_MANAGER' OR security_group_id =  
> 'SALES_REP' OR security_group_id = 'SALES_REP_LIMITED' OR  
> security_group_id = 'CSR')
> AND ((thru_date IS NULL OR thru_date > '2007-01-12 11:55:45.34')  
> AND (from_date IS NULL OR from_date <= '2007-01-12 11:55:45.34')));