LEFT OUTER JOIN with filters on the right-side extent

SQL++
1

I’m working on a LINQ solution to handle filtering on the right-hand extent of a LEFT OUTER JOIN. I came up with a possible solution that appears to work (the link also contains a more detailed explanation of the problem):

I’m hoping someone can tell me what, if anything, is wrong with this approach. Poor performance? Unexpected results? Limited Couchbase Server version compatibility? I’m going to do some testing on my own, but I figured if in doubt ask the experts.

Thanks,
Brant

2

Hi @btburnett3,

The specific rewrite works. When user selects META().id, META().cas you need to rewrite the query differently. i.e subquery need to projects and parent query needs to project those fields.

I think following should work

SELECT * FROM `beer-sample` beer
LEFT JOIN `beer-sample` brewery ON KEYS beer.brewery_id
WHERE beer.type = 'beer' AND ( brewery IS MISSING OR brewery.type = 'brewery');

Once N1QL implements ANSI JOIN syntax no workaround required. cc @keshav_m

3

@vsr1

Yeah, you make a good point about the META limitations. I’ll have to think about that some more.

Unfortunately, you’re example query doesn’t meet the need. That is applying filtering after the join, rather than before the join. As an example, if beer has a key to a document that is not type = ‘brewery’, it will return the wrong result. The expectation is that the right side is filtered first, so there is no match against the left side. The beer is then returned with nulls for the right side. Your example would instead match on the join and then filter due to the type, resulting in the beer being removed from the result set.

Any other ideas?

Thanks,
Brant

4

How about this.

SELECT beer.*, brew.* FROM `beer-sample` beer
LEFT JOIN `beer-sample` brewery ON KEYS beer.brewery_id
LET  brew = CASE WHEN  brewery.type = 'brewery' THEN brewery ELSE MISSING END
WHERE beer.type = 'beer';
5

@vsr1

Well, that’s definitely closer, good idea. But I think this still has the same limitation on META()? Also, would prevent chaining into another JOIN because the LET clause is processed after all of the JOINs?

Brant

6

In that case you can use workaround you have until ANSI SQL Join’s are implemented. Which are in TODO list.

7

@vsr1

Do you know how soon ANSI JOINs are expected? 5.1, 5.5? I’m just trying to decide whether the workaround is worth the effort.

Thanks,
Brant

8

We are working on it. The scenario you are looking should be in 5.1 (next release)

9

@vsr1
Great, thanks. Based upon that I think I’ll wait for 5.1.