Re: Warning on using EntityOperator.IN with MySQL

[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.

-David

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.

- Leon


select * from PARTY_RELATIONSHIP where

AND party_relationship_type_id = 'ASSIGNED_TO' AND


select * from PARTY_RELATIONSHIP where

AND party_relationship_type_id = 'ASSIGNED_TO' AND

(role_type_id_from = 'ACCOUNT_TEAM' AND party_id_from IN ('DemoSalesTeam1') 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'))); (role_type_id_from = 'ACCOUNT_TEAM' AND party_id_from IN ('DemoSalesTeam1') (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')));

smime.p7s
Description: S/MIME cryptographic signature