Different query plan when using positional parameters

Couchbase Server
1

I have a query that produces a different query plan when I run it with inlined values vs positional parameters, I would assume they should produce the same plan.

SELECT RAW meta().id
FROM `lep`
WHERE `_type` = 'App\\Lead\\Lead'
  AND `createdAt` >= '2019-08-28'
  AND ANY `item` IN `responses` SATISFIES `item`.`bid` = 'BidRegistration::1234' END
ORDER BY RANDOM()
LIMIT 1

VS

SELECT RAW meta().id
FROM `lep`
WHERE `_type` = $1
  AND `createdAt` >= $2
  AND ANY `item` IN `responses` SATISFIES `item`.`bid` = $3 END
ORDER BY RANDOM()
LIMIT 1

These queries produce the following query plans.

For the query with inlined values.

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "DistinctScan",
            "scan": {
              "#operator": "IndexScan3",
              "covers": [
                "cover ((distinct (array (`response`.`bid`) for `response` in (`lep`.`responses`) end)))",
                "cover ((`lep`.`_type`))",
                "cover ((`lep`.`createdAt`))",
                "cover ((meta(`lep`).`id`))"
              ],
              "filter_covers": {
                "cover ((`lep`.`_type`))": "App\\Lead\\Lead",
                "cover (any `item` in (`lep`.`responses`) satisfies ((`item`.`bid`) = \"BidRegistration::1234\") end)": true
              },
              "index": "idx_lead_responses_bid",
              "index_id": "c1577ff034097ebc",
              "index_projection": {
                "entry_keys": [
                  1,
                  2
                ],
                "primary_key": true
              },
              "keyspace": "lep",
              "namespace": "default",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"BidRegistration::1234\"",
                      "inclusion": 3,
                      "low": "\"BidRegistration::1234\""
                    },
                    {
                      "high": "\"App\\\\Lead\\\\Lead\"",
                      "inclusion": 3,
                      "low": "\"App\\\\Lead\\\\Lead\""
                    },
                    {
                      "inclusion": 1,
                      "low": "\"2019-08-28\""
                    }
                  ]
                }
              ],
              "using": "gsi"
            }
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "(((cover ((`lep`.`_type`)) = \"App\\\\Lead\\\\Lead\") and (\"2019-08-28\" <= cover ((`lep`.`createdAt`)))) and cover (any `item` in (`lep`.`responses`) satisfies ((`item`.`bid`) = \"BidRegistration::1234\") end))"
                },
                {
                  "#operator": "InitialProject",
                  "raw": true,
                  "result_terms": [
                    {
                      "expr": "cover ((meta(`lep`).`id`))"
                    }
                  ]
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Order",
        "limit": "1",
        "sort_terms": [
          {
            "expr": "random()"
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "1"
      },
      {
        "#operator": "FinalProject"
      }
    ]
  },
  "text": "SELECT RAW meta().id FROM `lep` WHERE `_type` = 'App\\\\Lead\\\\Lead' AND `createdAt` >= '2019-08-28' AND ANY `item` IN\r\n`responses` SATISFIES `item`.`bid` = 'BidRegistration::1234' END ORDER BY RANDOM() LIMIT 1"
}

For the query with positional parameters.

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "DistinctScan",
            "scan": {
              "#operator": "IndexScan3",
              "index": "idx_lead_responses_bid",
              "index_id": "c1577ff034097ebc",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "lep",
              "namespace": "default",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"BidRegistration::1234\"",
                      "inclusion": 3,
                      "low": "\"BidRegistration::1234\""
                    },
                    {
                      "high": "\"App\\\\Lead\\\\Lead\"",
                      "inclusion": 3,
                      "low": "\"App\\\\Lead\\\\Lead\""
                    },
                    {
                      "inclusion": 1,
                      "low": "\"2019-08-28\""
                    }
                  ]
                }
              ],
              "using": "gsi"
            }
          },
          {
            "#operator": "Fetch",
            "keyspace": "lep",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "((((`lep`.`_type`) = $1) and ($2 <= (`lep`.`createdAt`))) and any `item` in (`lep`.`responses`) satisfies ((`item`.`bid`) = $3) end)"
                },
                {
                  "#operator": "InitialProject",
                  "raw": true,
                  "result_terms": [
                    {
                      "expr": "(meta(`lep`).`id`)"
                    }
                  ]
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Order",
        "limit": "1",
        "sort_terms": [
          {
            "expr": "random()"
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "1"
      },
      {
        "#operator": "FinalProject"
      }
    ]
  },
  "text": "SELECT RAW meta().id FROM `lep` WHERE `_type` = $1 AND `createdAt` >= $2 AND ANY `item` IN `responses` SATISFIES `item`.`bid` = $3 END ORDER BY RANDOM() LIMIT 1"
}

Is there something I’m missing that would cause these 2 queries to produce different plans even though they seem to be virtually identical?

2

What is the version of the Couchbase ? Did you tried with 6.0.2?
What is the index definition?
The implicit ARRAY index has some limitations for covering.

It might be bug MB-36204

Options.
If the index is partial index (WHERE clause in the index) Don’t use query parameter for that predicate. Use constants.
Don’t use adhoc =false for that query OR use PREPARE and execute

3

I’m using 6.0.1

The index is

CREATE INDEX `idx_lead_responses_bid` ON `lep`((distinct (array (`response`.`bid`) for `response` in `responses` end)),`_type`,`createdAt`) WHERE (`_type` = "App\\Lead\\Lead")

I tried using a constant for the predicate from the where in the index, it didn’t change the query plan.
eg.

SELECT RAW meta().id FROM `lep` WHERE `_type` ='App\\Lead\\Lead' AND `createdAt` >= $2 AND ANY `item` IN `responses` SATISFIES `item`.`bid` = $3 END ORDER BY RANDOM() LIMIT 1
SELECT RAW meta().id FROM `lep` WHERE `_type` = $1 AND `createdAt` >= $2 AND ANY `item` IN `responses` SATISFIES `item`.`bid` = $3 END ORDER BY RANDOM() LIMIT 1
4

I have opened Issue MB-36204 and previous post has work around