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')));

Reply via email to