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

Java SDK
1

CB 7.6.1 EE

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 have 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.

(I found this issue LEFT JOIN works as inner join condition)

Thanks

2

And here are the plans for both queries.

First with the left join without the CASE WHEN :

{
    "plan": {
        "#operator": "Sequence",
        "~children": [
            {
                "#operator": "IndexScan3",
                "as": "i",
                "index": "pulse_invoice_search_adv8",
                "index_id": "2d57629c909b4ef9",
                "index_projection": {
                    "primary_key": true
                },
                "keyspace": "pulse",
                "namespace": "default",
                "optimizer_estimates": {
                    "cardinality": 0.000014786595593415846,
                    "cost": 61.88290754627935,
                    "fr_cost": 61.88290754627935,
                    "size": 50
                },
                "spans": [
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"b2b\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"b2b\""
                            },
                            {
                                "high": "\"error\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"error\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"b2b\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"b2b\""
                            },
                            {
                                "high": "\"pending\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"pending\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"b2c\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"b2c\""
                            },
                            {
                                "high": "\"error\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"error\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"b2c\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"b2c\""
                            },
                            {
                                "high": "\"pending\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"pending\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"cpam\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"cpam\""
                            },
                            {
                                "high": "\"error\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"error\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"cpam\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"cpam\""
                            },
                            {
                                "high": "\"pending\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"pending\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    }
                ],
                "using": "gsi"
            },
            {
                "#operator": "Fetch",
                "as": "i",
                "early_projection": [
                    "ageId",
                    "docId",
                    "id",
                    "invRecipientType",
                    "invRecordingDate",
                    "invStatus",
                    "orgId",
                    "proId",
                    "sysActive",
                    "type"
                ],
                "keyspace": "pulse",
                "namespace": "default",
                "optimizer_estimates": {
                    "cardinality": 0.000014786595593415846,
                    "cost": 61.88311693097565,
                    "fr_cost": 61.88311693097565,
                    "size": 1867
                }
            },
            {
                "#operator": "Parallel",
                "~child": {
                    "#operator": "Sequence",
                    "~children": [
                        {
                            "#operator": "Filter",
                            "alias": "i",
                            "condition": "(((`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 (\"2024-06-01\" <= (`i`.`invRecordingDate`)) and ((`i`.`invRecordingDate`) <= \"2024-06-07\"))",
                            "optimizer_estimates": {
                                "cardinality": 0.000014786595593415846,
                                "cost": 61.883117569886636,
                                "fr_cost": 61.883117569886636,
                                "size": 1867
                            }
                        }
                    ]
                }
            },
            {
                "#operator": "Join",
                "as": "p",
                "keyspace": "pulse",
                "namespace": "default",
                "on_filter": "((meta(`p`).`id`) is not null)",
                "on_keys": "(`i`.`proId`)",
                "optimizer_estimates": {
                    "cardinality": 0.000021425809851642675,
                    "cost": 61.88333120772024,
                    "fr_cost": 61.88333120772024,
                    "size": 3734
                },
                "outer": true
            },
            {
                "#operator": "Parallel",
                "~child": {
                    "#operator": "Sequence",
                    "~children": [
                        {
                            "#operator": "InitialProject",
                            "discard_original": true,
                            "optimizer_estimates": {
                                "cardinality": 0.000021425809851642675,
                                "cost": 61.88333644474219,
                                "fr_cost": 61.88333644474219,
                                "size": 3734
                            },
                            "preserve_order": true,
                            "result_terms": [
                                {
                                    "expr": "(`i`.`id`)"
                                },
                                {
                                    "expr": "(`i`.`docId`)"
                                },
                                {
                                    "expr": "(`i`.`proId`)"
                                },
                                {
                                    "expr": "(`i`.`invRecipientType`)"
                                }
                            ]
                        }
                    ]
                }
            }
        ]
    }
}

And now the left join with the CASE WHEN :

{
    "plan": {
        "#operator": "Sequence",
        "~children": [
            {
                "#operator": "IndexScan3",
                "as": "i",
                "index": "pulse_invoice_search_adv8",
                "index_id": "2d57629c909b4ef9",
                "index_projection": {
                    "primary_key": true
                },
                "keyspace": "pulse",
                "namespace": "default",
                "optimizer_estimates": {
                    "cardinality": 0.000014786595593415846,
                    "cost": 61.88290754627935,
                    "fr_cost": 61.88290754627935,
                    "size": 50
                },
                "spans": [
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"b2b\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"b2b\""
                            },
                            {
                                "high": "\"error\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"error\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"b2b\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"b2b\""
                            },
                            {
                                "high": "\"pending\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"pending\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"b2c\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"b2c\""
                            },
                            {
                                "high": "\"error\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"error\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"b2c\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"b2c\""
                            },
                            {
                                "high": "\"pending\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"pending\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"cpam\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"cpam\""
                            },
                            {
                                "high": "\"error\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"error\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    },
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"pulseOrganization::S00001\"",
                                "inclusion": 3,
                                "index_key": "`orgId`",
                                "low": "\"pulseOrganization::S00001\""
                            },
                            {
                                "high": "\"pulseAgency::S00001\"",
                                "inclusion": 3,
                                "index_key": "`ageId`",
                                "low": "\"pulseAgency::S00001\""
                            },
                            {
                                "inclusion": 0,
                                "index_key": "`invType`"
                            },
                            {
                                "high": "\"cpam\"",
                                "inclusion": 3,
                                "index_key": "`invRecipientType`",
                                "low": "\"cpam\""
                            },
                            {
                                "high": "\"pending\"",
                                "inclusion": 3,
                                "index_key": "`invStatus`",
                                "low": "\"pending\""
                            },
                            {
                                "high": "\"2024-06-07\"",
                                "inclusion": 3,
                                "index_key": "`invRecordingDate`",
                                "low": "\"2024-06-01\""
                            }
                        ]
                    }
                ],
                "using": "gsi"
            },
            {
                "#operator": "Fetch",
                "as": "i",
                "early_projection": [
                    "ageId",
                    "docId",
                    "id",
                    "invRecipientType",
                    "invRecordingDate",
                    "invStatus",
                    "orgId",
                    "proId",
                    "sysActive",
                    "type"
                ],
                "keyspace": "pulse",
                "namespace": "default",
                "optimizer_estimates": {
                    "cardinality": 0.000014786595593415846,
                    "cost": 61.88311693097565,
                    "fr_cost": 61.88311693097565,
                    "size": 1867
                }
            },
            {
                "#operator": "Parallel",
                "~child": {
                    "#operator": "Sequence",
                    "~children": [
                        {
                            "#operator": "Filter",
                            "alias": "i",
                            "condition": "(((`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 (\"2024-06-01\" <= (`i`.`invRecordingDate`)) and ((`i`.`invRecordingDate`) <= \"2024-06-07\"))",
                            "optimizer_estimates": {
                                "cardinality": 0.000014786595593415846,
                                "cost": 61.883117569886636,
                                "fr_cost": 61.883117569886636,
                                "size": 1867
                            }
                        }
                    ]
                }
            },
            {
                "#operator": "Join",
                "as": "p",
                "keyspace": "pulse",
                "namespace": "default",
                "on_filter": "((meta(`p`).`id`) is not null)",
                "on_keys": "case when (`i`.`docId`) then (`i`.`docId`) else (`i`.`proId`) end",
                "optimizer_estimates": {
                    "cardinality": 0.10344145312464717,
                    "cost": 61.88333120772024,
                    "fr_cost": 61.88333120772024,
                    "size": 3734
                },
                "outer": true
            },
            {
                "#operator": "Parallel",
                "~child": {
                    "#operator": "Sequence",
                    "~children": [
                        {
                            "#operator": "Filter",
                            "condition": "((meta(`p`).`id`) is not null)",
                            "optimizer_estimates": {
                                "cardinality": 0.10344145312464717,
                                "cost": 61.88965214920031,
                                "fr_cost": 61.88965214920031,
                                "size": 3734
                            }
                        },
                        {
                            "#operator": "InitialProject",
                            "discard_original": true,
                            "optimizer_estimates": {
                                "cardinality": 0.10344145312464717,
                                "cost": 61.91493591512057,
                                "fr_cost": 61.91493591512057,
                                "size": 3734
                            },
                            "preserve_order": true,
                            "result_terms": [
                                {
                                    "expr": "(`i`.`id`)"
                                },
                                {
                                    "expr": "(`i`.`docId`)"
                                },
                                {
                                    "expr": "(`i`.`proId`)"
                                },
                                {
                                    "expr": "(`i`.`invRecipientType`)"
                                }
                            ]
                        }
                    ]
                }
            }
        ]
    }
}

There is a “Filter” in the "#operator": "Parallel" which is not in the first plan.

3

Will take look. There is extra filter (“on_filter”: “((meta(p).id) is not null)”,) which is not right.
In mean time you can 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';
4

Thanks, it works this way.

5

@jeremieburtin ,

Not able to repro this. Can you post output of

select ds_version();

6

@jeremieburtin could you also confirm the definition of pulse_invoice_search_adv8 please.

7 
[
  {
    "$1": "7.6.1-3200-enterprise"
  }
]
8 
CREATE INDEX `pulse_invoice_search_adv8` ON `pulse`(`orgId`,`ageId`,`invType`,`invRecipientType`,`invStatus`,`invRecordingDate`,
(`invSecuredInfo`.`date`),`invNumber`,(`invFseData`.`numFacture`),
(`invChorusInfos`.`numeroFacture`),`invQuoteNumber`,`mefId`,`cusId`,
`patId`,`docId`,`proId`,`estId`,((`invFseData`.`PS`).`numIdentFact`),`invBilledPeriodStartDate`,
`invBilledPeriodStopDate`,
`invSourceType`,`invRootGuid`) 
WHERE ((`type` = "pulseInvoice") and (`sysActive` = true))
9

Thanks. Even with your index definition I’ve not yet reproduced the issue using 7.6.1-3200-enterprise. I have only 9 documents (based on your data); possibly I need to generate a number of other non-qualifying ones too - could you confirm the total document count in pulse ?

10

I have 482,955 documents in my pulse bucket, and 13.2K in the pulse_invoice_search_adv8 index.

11

By any chance you had index on pulse(META().id)

If it okay can you upload the output from (/tmp/pulse_bucket_export.json ). Optimizer stats

cbexport json -c couchbase://localhost:8091 -u Administrator -p password -b pulse -f list -o /tmp/pulse_bucket_export.json --include-key docKey --include-data _system._query --scope-field system_scope --collection-field system_collection

If not able to upload and if you have Enterprise aggrement you can contact support

12

Nope, only indexes using any META() are thoses created by sync gateway.

13

It’s the end of the day here in France, I’ll do that tomorrow as soon as possible.

thanks :slight_smile:

1 Like
14

We may also need index definitions too.

curl -s Administrator:password@127.0.0.1:9102/getIndexStatus |jq '.status[].definition' | sed 's/^"//g' | sed 's/"$/;/g' | sed 's/\\"/"/g' | grep pulse

Please open private message to me and @dh post over there index definitions and stats output

FYI: opened MB-62274 . Want rule out there is another issue present.

17

MB-62274 will fix this issue in next releases

1 Like