On 12/5/06 9:50 AM, Kurt Hansen wrote:
> I must be missing something. When Manager creates a JOIN, it adds a "OR
> (tN.FK IS NULL)" to the where clause even though FK is defined as
> not_null => 1 in tN. [...]
>
> Here are the offending FROM and WHERE clauses:
>
> FROM
> transactions t1
> LEFT OUTER JOIN persons t2 ON(t1.id = t2.transaction_id)
> LEFT OUTER JOIN customs t3 ON(t1.id = t3.transaction_id)
> LEFT OUTER JOIN donorccs t4 ON(t1.id = t4.transaction_id)
> LEFT OUTER JOIN baskets t5 ON(t1.id = t5.transaction_id)
> WHERE
> t1.Result = 0 AND
> NOT(t5.Sustainer = 'Yes') AND
> ((t1.id = t2.transaction_id) OR (t2.transaction_id IS NULL)) AND
> ((t1.id = t3.transaction_id) OR (t3.transaction_id IS NULL)) AND
> ((t1.id = t4.transaction_id) OR (t4.transaction_id IS NULL)) AND
> ((t1.id = t5.transaction_id) OR (t5.transaction_id IS NULL))
Do you really want a LEFT OUTER JOIN, or is a simple join sufficient? In
other words, are you trying to get only the "t1"s that have corresponding
"t2"s, "t3"s, "t4"s, and "t5"s. If so, use require_objects instead of
with_objects in your Manager query.
If you do want "t1"s even if they don't have one or more related rows in
t[2-5]s, then using with_objects is correct. The redundant JOIN conditions
are added for MySQL only. They're there to persuade MySQL to use its
indexes. (In some cases, MySQL was ignoring relevant indexes when those
clauses were omitted.) The "IS NULL" parts have to be there in order to
preserve the semantics of the query (i.e., we still want t1 rows even if one
or more corresponding t[2-5] rows do not exist).
It's not that the actual FK columns in the database will ever be null. It's
that such a row may not exist at all for a given t1 row, but we still want
to include that t1 row in the query, in which case all the result rows for
that other tN table will be null.
Anyway, to disable this feature, add this parameter to the Manager call:
redundant_join_conditions => 0
Is anyone else running into performance problems with this? If so, let me
know and I'll change the default to be off instead of on for MySQL. If
you'd like to do this yourself right now, you can override the
likes_redundant_join_conditions() method in the Rose::DB driver class:
use Rose::DB::MySQL;
no warnings 'redefine';
sub Rose::DB::MySQL::likes_redundant_join_conditions { 0 }
> RDBO version: 0.75
You also might want to consider updating RDBO since many bugs and leaks have
been fixed since 0.75.
-John
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
