[MB-62932] Inconsistent results executing prepared statements with star/wildcard expressions in projection

Product: Couchbase Server
Component: Query-Service
Issue Link: MB-62932
Affects Version: 7.6.x
Fix Versions: 7.6.4, 8.0.0

Summary

When the encoded plan of a prepared statement includes path.* expressions in the projection, it is unmarshalled incorrectly and executed with inaccurate results, leading to missing fields in the returned entries.

In other words, prepared statements that use * in the SELECT clause may return incomplete or incorrect results if executed on a different node than the one on which they were originally prepared, particularly in clusters with multiple Query Service nodes.

Symptoms 

• Prepared statements may return incorrect query results when executed on a different Query Service node than the one on which they were originally prepared. While the correct documents are returned, some fields may be missing from the results.

Triggers

• Query is executed as a prepared statement.
• The statement contains a star (*) expression. For example:

  prepare prep1 as select d.* from default as d ORDER BY id;

Verification

We would expect to see all of the following in cases where this issue is occurring:

• The prepared statement contains * in the SELECT clause
• The incorrect query results consist of missing document fields
• Verify which Query Service node the prepared statement was prepared on

SELECT * FROM system:prepareds;

curl -u $USER:$PASSWORD $BASE_URL/admin/prepareds

• Then confirm if the incorrect query results only occur on Query Service nodes where the prepared statement was NOT prepared

Workarounds

Add a request level parameter when executing the prepared statement, setting sort_projection to false.

https://docs.couchbase.com/server/current/n1ql/n1ql-manage/query-settings.html#sort_projection