LEFT OUTER JOIN confusion


#1

Hello everyone,

I’m new to Couchbase, coming from MSSQL.

So today I stumbled upon a JOIN problem:

SELECT F.properties.mpObjectId as bBlockId, C_BB.bBlockNo, C_COU.descript 
FROM OBI_MPWEB AS B 
UNNEST B.bimJson.featureCollection.features AS F 
INNER JOIN OBI_MPWEB_CACHE AS C_BB ON F.properties.mpObjectId = meta(C_BB).id 
LEFT OUTER JOIN OBI_MPWEB_CACHE AS C_COU ON C_BB.countryId = meta(C_COU).id 
WHERE B._type = "market::bim" AND B.marketId = "b05282c6-c3d3-4bdd-a91f-a19d34e21be2" AND F.properties.featureType = "Component"

gives me the desired result

[
  {
    "bBlockId": "ea93e4c4-95bf-494b-bbc7-e469bb9d5de1",
    "bBlockNo": "44D405",
    "descript": "Ungarn"
  },
  {
    "bBlockId": "42dcfa98-2f0f-49fd-9a21-4195627110f3",
    "bBlockNo": "44A500",
    "descript": "Ungarn"
  } .....

But I don’t want the first join to be a INNER JOIN, because the desired RIGHT side values might not be cached, but the LEFT side of the join should always be returned (exact the way this works in MSSQL, give me what you have, regardless of JOIN success).

So I tried the obvious thing

SELECT F.properties.mpObjectId as bBlockId, C_BB.bBlockNo, C_COU.descript 
FROM OBI_MPWEB AS B 
UNNEST B.bimJson.featureCollection.features AS F 
LEFT OUTER JOIN OBI_MPWEB_CACHE AS C_BB ON F.properties.mpObjectId = meta(C_BB).id 
LEFT OUTER JOIN OBI_MPWEB_CACHE AS C_COU ON C_BB.countryId = meta(C_COU).id 
WHERE B._type = "market::bim" AND B.marketId = "b05282c6-c3d3-4bdd-a91f-a19d34e21be2" AND F.properties.featureType = "Component"

but this results in

[
  {
    "bBlockId": "38361de7-ca56-43e5-81fc-1ce21cc70ed4"
  },
  {
    "bBlockId": "3789f947-cc7b-433a-a338-c4a47c87524d"
  }...

WTF? :grin:

Where is my mistake? Why isn’t the JOIN also successful with doing it as a LEFT OUTER JOIN?

Thx for your help guys!

Regards,
Michael


#2

Did you check results. You are doing LEFT OUTER JOIN. If there is no matching entry for right side it is projected as MISSING, Any expression on MISSING result in MISING that is why you don’t have fields bBlockNo, descript

https://blog.couchbase.com/ansi-join-support-n1ql/


#3

Sure, you’re right with that.

But the entries for the join match are there, and they show up when doing a Inner Join. They just don’t show up when doing it with a Left Outer Join. And that’s the point. Right side values should be there but are not shown. So the Left Outer Join isn’t working correctly or not as expected for a ANSI Join.

So why doesn’t it work?


#4

The following seems working correctly.

INSERT INTO default VALUES ("f01", {"_type":"market::bim", "marketId":"b05282c6-c3d3-4bdd-a91f-a19d34e21be2",
                                    "bimJson":{"featureCollection":{"features": [{"properties":{"mpObjectId":"ea93e4c4-95bf-494b-bbc7-e469bb9d5de1","featureType":"Component"}},
                                                                                 {"properties":{"mpObjectId":"42dcfa98-2f0f-49fd-9a21-4195627110f3","featureType":"Component"}},
                                                                                 {"properties":{"mpObjectId":"08d49fee-0311-43bb-8870-fff1a08404c2","featureType":"Component"}},
                                                                                 {"properties":{"mpObjectId":"ba21bf18-06e8-43d1-b7b3-62a8f3b8cbad","featureType":"Component"}}
]}}});
INSERT INTO default VALUES ("ea93e4c4-95bf-494b-bbc7-e469bb9d5de1", {"countryId":"c100","bBlockNo":"44A500"});
INSERT INTO default VALUES ("42dcfa98-2f0f-49fd-9a21-4195627110f3", {"countryId":"c100","bBlockNo":"44D405"});
INSERT INTO default VALUES ("ba21bf18-06e8-43d1-b7b3-62a8f3b8cbad", {"countryId":"c200","bBlockNo":"44F600"});
INSERT INTO default VALUES ("c100", {"descript":"Ungarn"});

SELECT F.properties.mpObjectId as bBlockId, C_BB.bBlockNo, C_COU.descript
FROM default AS B
UNNEST B.bimJson.featureCollection.features AS F
LEFT OUTER JOIN default AS C_BB ON F.properties.mpObjectId = meta(C_BB).id
LEFT OUTER JOIN default AS C_COU ON C_BB.countryId = meta(C_COU).id
WHERE B._type = "market::bim" AND B.marketId = "b05282c6-c3d3-4bdd-a91f-a19d34e21be2" AND F.properties.featureType = "Component";

"results": [
{
    "bBlockId": "ea93e4c4-95bf-494b-bbc7-e469bb9d5de1",
    "bBlockNo": "44A500",
    "descript": "Ungarn"
},
{
    "bBlockId": "42dcfa98-2f0f-49fd-9a21-4195627110f3",
    "bBlockNo": "44D405",
    "descript": "Ungarn"
},
{
    "bBlockId": "08d49fee-0311-43bb-8870-fff1a08404c2"
},
{
    "bBlockId": "ba21bf18-06e8-43d1-b7b3-62a8f3b8cbad",
    "bBlockNo": "44F600"
}
]

Could you post EXPLAIN and corresponding indexes. Also If you can post sample document (only referenced fields) of OBI_MPWEB AS B and corresponding matching documents. What is CB version.


#5

Hey vsr1,

I’m sorry for this very very late reply …

It turned you this isn’t a reproducable problem, and now most times it works. I’m really not sure what was wrong, better what I did wrong. I guess it was a combination of several sideeffects.

For now I’d “close” this topic. If I’ll again discover this issue, I might come back here to reopen it.

Thx for your help until now!!

Regards,
Michael