Fri Dec 03, 2021 1:19 pm
Login Register Lost Password? Contact Us


LEFT/RIGHT ONLY Joins

Questions around writing code and queries

Tue Oct 25, 2016 3:57 pm Change Time Zone

I wanted to ask a question and possible bug in the ONLY joins.

I am trying to do a JOIN between a dataset and an index. I am trying to make it a keyed join obviously and for that I need the index on the right side but I am finding the following problem.

This code:
Code: Select all
JOIN(
   DS,
   IDX,
   LEFT.id=RIGHT.id AND RIGHT.count < 0,
   TRANSFORM(...),
   RIGHT ONLY
)

And this code:
Code: Select all
DS2 := IDX(count>0);
JOIN(
   DS,
   DS2,
   LEFT.id=RIGHT.id,
   TRANSFORM(...),
   RIGHT ONLY
)


The first one is the "optimal" code since it uses the index for the join and at the same time
Is not returning the same results. It looks like the first one is returning everything that doesn't match left on "id" but is ignoring the condition of "count > 0". I don't know if this is the expected behavior since the documentation kind of says that (every record from RIGHT with no match on LEFT) but I was expecting that it was "every record on RIGHT that doesn't match the expression".

Could I get some clarification on this so I can work around it or log a ticket on Jira?
dsanchez
 
Posts: 16
Joined: Tue May 24, 2016 4:11 pm

Tue Oct 25, 2016 5:29 pm Change Time Zone

Daniel,

Your example code and description are not in agreement.

You code says: RIGHT.count < 0

But your text says: ignoring the condition of "count > 0"

Is this a typo, or the problem? :)

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1606
Joined: Wed Oct 26, 2011 7:40 pm

Tue Oct 25, 2016 5:41 pm Change Time Zone

I "negated" the condition since I expect the RIGHT ONLY Join to return what DOESN'T match the expression.
dsanchez
 
Posts: 16
Joined: Tue May 24, 2016 4:11 pm

Tue Oct 25, 2016 6:26 pm Change Time Zone

Daniel,

Is IDX.count a payload field or a search term in the index?

Also, count < 0 and count > 0 are not exact opposites -- what about count = 0?

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1606
Joined: Wed Oct 26, 2011 7:40 pm

Wed Oct 26, 2016 8:34 am Change Time Zone

Count is part of the key and yeah I know they are not exact opposites, is just and example of the minimum amount of code needed to reproduce the case.
dsanchez
 
Posts: 16
Joined: Tue May 24, 2016 4:11 pm

Wed Oct 26, 2016 2:01 pm Change Time Zone

Daniel,

OK, if count is a search term in the INDEX and not a payload field, then I have to presume it is NOT the first element in the key. I also have to assume that the count field does contain some negative values (which I would not normally expect in a field named "count").

Based on those assumptions, try your examples this way:
Code: Select all
JOIN(
   DS,
   IDX,
   KEYED(LEFT.id=RIGHT.id AND RIGHT.count < 0),
   TRANSFORM(...),
   RIGHT ONLY
)

and this:
Code: Select all
//using KEYED and WILD to make sure the INDEX isn't treated as a DATASET
DS2 := IDX(KEYED(count>=0),WILD(id)); //including 0 here to create exact opposites
JOIN(
   DS,
   DS2,
   LEFT.id=RIGHT.id,
   TRANSFORM(...),
   RIGHT ONLY
)

If these changes still produce different results, then let's go back to the beginning and tell me exactly what you're trying to accomplish and exactly what the structure of your INDEX declaration is. Also tell me exactly how the results differ between the two examples.

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1606
Joined: Wed Oct 26, 2011 7:40 pm


Return to Programming

Who is online

Users browsing this forum: No registered users and 1 guest