Couchbase SQL++ Left Join Query with CASE acts as inner join

Question

I have the following query :

SELECT i.id, i.docId, i.proId, i.invRecipientType
FROM pulse i
WHERE i.type = 'pulseInvoice'
  AND i.invRecipientType IN ['cpam', 'b2b', 'b2c']
  AND i.orgId = 'pulseOrganization::S00001'
  AND i.ageId = 'pulseAgency::S00001'
  AND i.invStatus IN ['pending', 'error']
  AND i.sysActive = true
  AND i.invRecordingDate >= '2024-06-01' 
  AND i.invRecordingDate <= '2024-06-07'

It returns 9 rows as follows :

[
  {
    "id": "pulseInvoice::isl98829abd50231111efa73e4311e980c8e3",
    "docId": null,
    "proId": null,
    "invRecipientType": "b2c"
  },
  {
    "id": "pulseInvoice::enugl9e03791024ab11efa035dffe4b27df14",
    "docId": "pulseDoctor::FRMS10002116811",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::z0rty6df5e850f58211eeac6b8d2531aa0da6",
    "docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::s4r3249dc43c0cbe711eeb25455d694fb3241",
    "docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::a2jic16022bd0226f11ef9c075de3aecc5009",
    "docId": null,
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::uw2o015f07890226f11ef9c075de3aecc5009",
    "docId": null,
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::wnw7o6dd806c0927f11eeb9215f9ff36c9f4c",
    "docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::bpvi8c99ce980180d11efa0cf07229265c17d",
    "docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::q556cbc7bcea024ac11efad1c19ce67df01f1",
    "docId": "pulseDoctor::FRMS10002116811",
    "proId": null,
    "invRecipientType": "cpam"
  }
]

If I add a left join with a CASE WHEN it seems to act as an inner join and excludes some rows :

SELECT i.id, i.docId, i.proId, i.invRecipientType
FROM pulse i
LEFT JOIN pulse p ON META(p).id = (CASE WHEN i.proId  THEN i.proId ELSE i.docId END)
WHERE i.type = 'pulseInvoice'
  AND i.invRecipientType IN ['cpam', 'b2b', 'b2c']
  AND i.orgId = 'pulseOrganization::S00001'
  AND i.ageId = 'pulseAgency::S00001'
  AND i.invStatus IN ['pending', 'error']
  AND i.sysActive = true
  AND i.invRecordingDate >= '2024-06-01' 
  AND i.invRecordingDate <= '2024-06-07'

I now get only 6 results, those with null docId and proId are excluded.

[
  {
    "id": "pulseInvoice::enugl9e03791024ab11efa035dffe4b27df14",
    "docId": "pulseDoctor::FRMS10002116811",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::z0rty6df5e850f58211eeac6b8d2531aa0da6",
    "docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::s4r3249dc43c0cbe711eeb25455d694fb3241",
    "docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::wnw7o6dd806c0927f11eeb9215f9ff36c9f4c",
    "docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::bpvi8c99ce980180d11efa0cf07229265c17d",
    "docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
    "proId": null,
    "invRecipientType": "cpam"
  },
  {
    "id": "pulseInvoice::q556cbc7bcea024ac11efad1c19ce67df01f1",
    "docId": "pulseDoctor::FRMS10002116811",
    "proId": null,
    "invRecipientType": "cpam"
  }
]

Am I missing something ? If I specify the left join on i.docId or i.proId only, it returns the 9 rows, but combining it with the CASE filters them out.

Thanks

Answer 1

There seems some bug. In mean time try this

select i.id, i.docId, i.proId, i.invRecipientType
FROM pulse i
LEFT JOIN pulse p ON KEYS IFMISSINGORNULL(i.proId, i.docId)
WHERE i.type = 'pulseInvoice'
and i.invRecipientType IN ['cpam', 'b2b', 'b2c']
and i.orgId = 'pulseOrganization::S00001'
and i.ageId = 'pulseAgency::S00001'
and i.invStatus IN ['pending', 'error']
and i.sysActive = true
and i.invRecordingDate >= '2024-06-01'
and i.invRecordingDate <= '2024-06-07';