Left outer join

[איריס עירון (איתי)‎]

is there a left outer join ןמ Jaql?

In other words:
--------------------
$x= [{attr: 'a', value: 1}];

$y = [{attr: 'a'},{attr: 'b'}];

join $x,$y where $x.attr==$y.attr into {$x.attr, $y.value};
[{attr: 'a', value: 1}]

How do we keep the values in $y that do not have a matching record in
$x while assigning them a default value ? The expected result should
be be:
[{attr: 'a', value: 1}, {attr: 'b', value: someDefaultValue}]

Thanks,
- Iris

--
You received this message because you are subscribed to the Google Groups "Jaql Users" group.
To post to this group, send email to jaql-...@googlegroups.com.
To unsubscribe from this group, send email to jaql-users+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/jaql-users?hl=en.

[M.A. Hernandez]

Iris,

Try

join preserve $x, preserve $y where $x.attr==$y.attr into { x:$x.attr,
y: $y.attr };

Mauricio

[Kevin Beyer]

Mauricio's response gets you most of the way there, but I wanted to
add a little detail: The keyword 'preserve' can be added before any
join variable to preserve all the elements from that input, even if
they fail the join condition. (I cannot remember what we do with any
nulls in the input, but most of the time, as in this case, it is
irrelevant.) The 'into' clause will be fired with a 'null' for that
input variable. What Mauricio wrote is a full outer join of the two
inputs. A left outer join (or is it a right outer -- I can never
remember, which is why we don't use that syntax!) is uses just one
'preserve':


X = [
{ attr: 'a', value: 1 },
{ attr: 'c', value: 2 },
];

Y = [

{ attr: 'a' },
{ attr: 'b' }
];

join x in X,
preserve y in Y
where x.attr == y.attr
into { y.attr, value: firstNonNull( x.value, -1 ) }
;

[ { "attr": "b", "value": -1 },
{ "attr": "a", "value": 1 } ]

Notice that "c" was not returned the in result. The following
illustrates the difference:

join preserve x in X,
preserve y in Y
where x.attr == y.attr
into { firstNonNull(x,y).attr, value: firstNonNull( x.value, -1 ) }
;

[ { "attr": "b", "value": -1 },
{ "attr": "c", "value": 2 },
{ "attr": "a", "value": 1 } ]

[Iris Eiron]

Hi,

I was expecting the two following statements to produce the same
results. But they don't. the second statement always generate -1 as
value. Why?

join x in X,
preserve y in Y
where x.attr == y.attr
into { y.attr, value: firstNonNull( x.value, -1 ) }

join x in X,
preserve y in Y
where x.attr == y.attr

into { y.attr, value: if (x.value!=null) (x.value) else ( -1) ) }

Thanks,
- Iris

[Iris Eiron]

Hi,

I was expecting the two following statements to produce the same
results. But they don't. the second statement always generate -1 as
value. Why?

join x in X,
preserve y in Y
where x.attr == y.attr
into { y.attr, value: firstNonNull( x.value, -1 ) }

join x in X,
preserve y in Y
where x.attr == y.attr

into { y.attr, value: if (x.value!=null) (x.value) else ( -1) ) }

Thanks,
- Iris


On May 25, 11:56 am, Kevin Beyer <kevin.beyer.j...@gmail.com> wrote:

[Kevin Beyer]

The answer comes from this important point: Jaql has three-valued
logic for comparison operators (like ==, !=, etc). Any comparison to
a null produces a null result. (The comparison between two values of
different types also produces null). So this explains your
difference. There is a isnull(x) function that will test for null,
which would have worked in your if expression. Here's a bunch of
examples:

jaql> null == null;
null

jaql> 1 == null;
null

jaql> 1 != null;
null

jaql> not( null == null );
null

When push comes to shove and a real boolean is required, say for a
'if' or a 'filter' to be processed, a null is converted to a strict
boolean as false.

jaql> (x = null, if (x!=null) x else -1);
-1

jaql> (x = 1, if (x!=null) x else -1);
-1

jaql> (x = null, firstNonNull( x, -1 ));
-1

jaql> (x = 1, firstNonNull( x, -1 ));
1

jaql> (x = null, if (not isnull(x)) x else -1);
-1

jaql> (x = 1, if (not isnull(x)) x else -1);
1


Going beyond your question, logical operators are also three-valued:

and | null false true
-------+---------------------------
null | null false null
false | false false false
true | null false true

or | null false true
-------+---------------------------
null | null null true
false | null false true
true | true true true

not |
-------+-------
null | null
false | true
true | false

Why did we choose to do this? The biggest reason is joins and
indexes:

join A, B where A.x == B.y

We didn't want to generate the cross product of all the A and B
records that have no 'x' or no 'y' field. For indexes (down the
road), say we indexed A.x, we wanted to avoid any representation of
'x' for records with no 'x' field (or 'x' when is null).

Our solution here lines up with SQL's handling of nulls. It also
follows IEEE's handling of NaN (two NaNs are not equal, nor not
equal).

-K