Re: Wrong SQL when joining in an OR-part of the qualifier
Re: Wrong SQL when joining in an OR-part of the qualifier
- Subject: Re: Wrong SQL when joining in an OR-part of the qualifier
- From: Theodore Petrosky via Webobjects-dev <email@hidden>
- Date: Mon, 14 Feb 2022 17:58:28 -0500
I have only worked with Oracle and Postgresql. Any time I have a problem like
this, I create a view on the database that does half the query. Or in your
case, the ‘view’ could do all the heavy lifting.
It’s an idea.
Ted
> On Feb 14, 2022, at 1:13 AM, ocs--- via Webobjects-dev
> <email@hidden> wrote:
>
> Hi there,
>
> lately, I am encountering wrong fetches (with FrontBase, if important). I log
> out my qualifier and SQL, and the gist of the problem is a join in the
> OR-part of the qualifier. I want to fetch rows where there's an empty
> attribute, OR when there's a specific join:
>
> 06:52:06.510 DEBUG -> ((savedPresentationEndDate = null) or
> (lastValidPriceOfferCache.user = (model.DBUser)'[rc/Registration
> centre#1000004]'))
>
> Alas, SQL-level, it boils down to something entirely different:
>
> 06:52:06.535 DEBUG "DBAuction"@318794136 expression took 1 ms: SELECT ...
> FROM "T_AUCTION" t0, "T_PRICE_OFFER" T1 WHERE (T1."C_CREATOR_ID" = 1000004 OR
> t0."C_PRESENTATION_END_DATE" is NULL) AND t0."C_LAST_VALID_PRICE_OFFER_CACHE"
> = T1."C_UID"
>
> The SQL generator properly sets up the OR checking the right target PK, but
> then, instead of placing the join into the OR-part where it belongs to, it
> forces the join absolute to the entire condition — even to the NULL-check
> which should be completely independent.
>
> That self-evidently is not what I need here: if t0."C_PRESENTATION_END_DATE"
> is NULL, I want to fetch the row regardless of whatever vaue there is or is
> not in the C_LAST_VALID_PRICE_OFFER_CACHE foreign key. Actually, if there
> happens to be NULL in C_PRESENTATION_END_DATE, I would prefer if the
> SQL-level would not try to join at all, for it is self-evidently superfluous.
> I've tried manually
>
> SELECT * FROM "T_AUCTION" t0, "T_PRICE_OFFER" T1 WHERE
> (t0."C_PRESENTATION_END_DATE" is NULL) OR (T1."C_CREATOR_ID" = 1000004 AND
> t0."C_LAST_VALID_PRICE_OFFER_CACHE" = T1."C_UID")
>
> which is what, I believe, the SQL generator should have created from the
> qualifier, and it seems to work (at least, it produces no error; I cannot
> easily check whether the rows returned are OK. but they seem to t the first
> look).
>
> Can perhaps anybody see how to fix the problem? (But for by generating
> directly my SQL at the application level, which I can do if need be, but
> would rather not, unless really the only way.)
>
> Thanks,
> OC
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden