Channel join behaving in surprising (incorrect?) ways

[antoniofabio]

join seems to drop duplicate keys, and gives inconsistent results when used with remainder: true.
There's a similar (stale) issue which I don't think got to the core of the problem, which is not about outer vs inner join.

Given this code:

left = Channel.from(
  ['k1', 'l1'],
  ['k1', 'l2'],
  ['k2', 'l3']
  )
right = Channel.from(
  ['k1', 'r1'],
  ['k2', 'r2']
  )

left.join(right).view()

I get this output:

[k1, l1, r1]
[k2, l3, r2]

while I would have expected this:

[k1, l1, r1]
[k1, l2, r1]
[k2, l3, r2]

The result of using 'remainder: true' is even more puzzling. This code:

left = Channel.from(['k1', 'l1'], ['k1', 'l2'], ['k2', 'l3'], ['k3', 'l4'])
right = Channel.from(['k1', 'r1'], ['k2', 'r2'])

left.join(right, remainder: true).view()

results into this:

[k1, l1, r1]
[k2, l3, r2]
[k1, l2, null]
[k3, l4, null]

The same behavior is shown by the 'cross' operator.

[shenker]

I agree it is unintuitive.

Here's a workaround:

def inner_join(ch_a, ch_b) {
    return ch_b.cross(ch_a).map { [it[0][0], *it[1][1..-1], *it[0][1..-1]] }
}

left = Channel.from(
    ['k1', 'l1', 'l1'],
    ['k1', 'l2', 'l2'],
    ['k2', 'l3', 'l3']
)
right = Channel.from(
    ['k1', 'r1', 'r1'],
    ['k2', 'r2', 'r2']
)

inner_join(left, right).view()

Note that because ch_a.cross(ch_b) is different than ch_b.cross(ch_a), this won't work as expected if you switch left and right. The channel that emits unique keys must be the second argument to inner_join.

[pditommaso]

The join operator is not sql join. The join does it matching a value emitted by one channel with a value produced by another channel having the same key. Once a match is found both values are consumed.

[antoniofabio]

That is indeed the behavior which seems surprising to me. Perhaps the doc could be improved, by e.g. including your comment above in the manual page?

Some additional useless blubber below, feel free to skip :)

---

This currently the very first sentence describing 'join':

The join operator creates a channel that joins together the items emitted by two channels for which exits a matching key.

The manual page then goes into some intuitive examples (re-enforcing the impression this is just like SQL join), before expanding into the 'remainder' option, which I think is subtly misleading:

The join operator can emit all the pairs that are incomplete, i.e. the items for which a matching element is missing, by specifying the optional parameter remainder

I originally read this with the understanding that incomplete tuples emitted by the operator with the 'remainder: true' option are those which lack a matching key, but that is not true. As it stands, incomplete tuples obtained via 'remainder: true' might be due to either absence of a matching key tout court, or just happen to have appeared in the stream after a matching key had been already consumed.

Another pre-emptive comment, in case anybody would ever get up to the task of implementing sql-like joins for nextflow channels, I would suggest to use different operators for different kinds of join, instead of having a single 'join' with multiple options dictating specific behaviors. My related experience is with R: in base R, you have the historic base R 'merge' function, which can do everything and then something, depending on how you craft its longish list of arguments. Then you have the much more recent tidyverse library functions: 'join', 'left_join', 'right_join', 'full_join', 'anti_join', 'crossing'. I can tell you I can always use the tidyverse functions without looking up the manual. With 'merge', I stopped even trying.

[zachary-foster]

I also think there should be an option to replicate the values instead of using null. I have multiple parts in my pipeline where I want this functionality. For example, I am looking up reference genomes on NCBI for a bunch of samples based on sendsketch results. To take advantage of caching and avoid redundant downloads, once I find what assemblies to download for each sample, I combine the list for all samples, make it unique, and then download the unique assemblies for all the samples. Afterwards, I have two channels I need to combine: one per-sample channel and one per-genome channel. The values in the per-genome channel have to be replicated to join the two, since the same genome can apply to multiple samples. There are a few other parts of the pipeline with the same kind of issue. I like @antoniofabio 's idea of replicating the R tidyverse join syntax. Thanks for considering!

[bentsherman]

The combine operator with the by option should be equivalent to a SQL join:

left = Channel.of(
  ['k1', 'l1'],
  ['k1', 'l2'],
  ['k2', 'l3']
)
right = Channel.of(
  ['k1', 'r1'],
  ['k2', 'r2']
)

left.combine(right, by: 0).view()