### Does this PR have any customer impact?
No
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
Feature
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
Yes
### Are you introducing any new config? If yes, do you have tests with and without them being set?
No
### ChangeLog (Refer [Template](../oss/CHANGELOG.md))
### Description
Add schema mappings to support reverse order against the index
This requires us to have a reverse order by operator and associated sort support.
This registers the order by functions - the implementation wil be flushed out in a separate change.
This is due to the fact that amorderbyop only supports order by forwards when added with index recheck.
Index Recheck is required in our case since orderby on the index is lossy. To overcome this, we introduce a forward walk index operator and a reverse walk index operator as 2 separate operators that can be pushed to the index.
----
#### AI description (iteration 1)
#### PR Classification
This pull request implements a new reverse ordering feature through API changes and database schema updates.
#### PR Summary
The changes introduce reverse order functionality for BSON queries by adding new C functions and corresponding SQL deployment scripts.
- `pg_documentdb/src/query/bson_dollar_operators.c` adds `command_bson_orderby_reverse` and `bson_orderby_compare_sort_support` functions with appropriate PG function registrations.
- SQL files under `/oss/pg_documentdb/sql/udfs/` and `/oss/pg_documentdb/sql/operators/` provide new UDFs and operators (including a reverse order operator and btree comparator support) to enable the feature.
- Expected API schema files in both `/oss/pg_documentdb/src/test/regress/expected/` and `/pgmongo/src/test/regress/expected/` are updated to include the new functions.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
### Does this PR have any customer impact?
Yes
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
Feature
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
Yes
### Are you introducing any new config? If yes, do you have tests with and without them being set?
Yes
### ChangeLog (Refer [Template](../oss/CHANGELOG.md))
### Description
Index hints are a mongo feature that allows customers to specify which index they would like to target for a query. Typically this is used in diagnostic scenarios when testing query performance.
With the new composite index op class coming in, we’re introducing side by side support for composite which makes it so we would want customers to be able to test which index is better for planning.
In this spirit, index hints is introduced which follows the same infra as the force pushdown for geonear and text and adds a new option for index hints.
As part of this the code is also refactored to cleanup some of the logic in the relpathlist and fix correctness issues around text index force pushdown (which was needed to handle index hints)
----
#### AI description (iteration 1)
#### PR Classification
This pull request introduces a new feature to support index hints in DocumentDB.
#### PR Summary
The changes integrate index hint support into query planning and execution, allowing users to force a specific index during query processing. Key updates include:
- **`src/opclass/index_support.c`**: Added new functions and a force index operator type (`ForceIndexOpType_IndexHint`) for filtering available indexes and generating custom operator expressions based on index hints.
- **`src/planner/documents_planner.c`**: Modified base table query planning logic to detect index hints and adjust index pushdown, including updates to path and restriction handling.
- New UDFs (e.g., `bson_dollar_index_hint`) and configuration variables (`EnableIndexHintSupport`, `UseLegacyForcePushdownBehavior`) were added to support the hint mechanism.
- Test files and SQL scripts (e.g., in `/oss/internal/pg_documentdb_distributed/src/test/regress/`) were introduced or updated to validate index hint pushdown behavior.
- Documentation and release notes (e.g., `docs/pgmongo/design_docs/data_plane/indexing/index_hints.md`, `pgmongo_1.106.md`, and `CHANGELOG.md`) were updated to describe the index hint feature.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
### Does this PR have any customer impact?
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
### Are you introducing any new config? If yes, do you have tests with and without them being set?
### ChangeLog (Refer [Template](../oss/CHANGELOG.md))
### Description
[OSS] fix 0.104-1 hotfix upgrade scripts
----
#### AI description (iteration 1)
#### PR Classification
Hotfix update to upgrade SQL upgrade scripts correcting version references.
#### PR Summary
This pull request updates SQL upgrade scripts to use the correct UDF and documentdb version numbers.
- In `pgmongo/sql/pgmongo--1.103-0--1.103-1.sql`, the include directive was updated from version 0.105-0 to 0.104-1.
- In `oss/pg_documentdb/sql/documentdb--0.104-0--0.104-1.sql`, the UDF include reference was changed to use version 0.104-1.
- The file `oss/pg_documentdb/sql/documentdb--0.104-0--0.105-0.sql` was moved to `oss/pg_documentdb/sql/documentdb--0.104-1--0.105-0.sql` with no code changes.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
### Does this PR have any customer impact?
Yes
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
Feature
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
Yes
### Are you introducing any new config? If yes, do you have tests with and without them being set?
Yes
### ChangeLog (Refer [Template](../oss/CHANGELOG.md))
### Description
More general improvements to order by and composite:
Create Indexes
1) Support creating a composite index side by side with a regular index
2) Block creation of descending or wildcard and unsupported set ups for composite indexes
Planner:
1) Support ordering by the Nth path of an index if the prefixes are equality.
2) Validate that if it's not the first column, prefixes are provided
3) Support incremental sort with index pushdown.
----
#### AI description (iteration 1)
#### PR Classification
This pull request delivers a new feature and API enhancement to enable order-by index pushdown and refined composite index support.
#### PR Summary
The update streamlines composite index processing by introducing stricter equivalency checks, new UDF functions for full-scan order-by pushdown, and enhanced validations during index creation while updating explain outputs and tests.
- `src/metadata/index.c` refines index options equivalency logic to correctly compare composite term settings.
- `src/opclass/bson_gin_composite_entrypoint.c` and `src/index_am/rum.c` add functions to support order-by pushdown and manage duplicate entries efficiently.
- `sql/udfs/query/bson_dollar_comparison--0.105-0.sql` (and related operator files) introduce new UDFs including `bson_dollar_fullscan` to enable full scan operations.
- `src/commands/create_indexes.c` enforces new composite index validations (restricting use to ascending, non-wildcard regular indexes) with precise error reporting.
- Test scripts and explain plan outputs in the `regress` and `pgmongo` directories are updated to confirm the new composite order-by behavior.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
### Does this PR have any customer impact? No
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc) Feature
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes) Yes
### Are you introducing any new config? If yes, do you have tests with and without them being set? Yes, yes
### ChangeLog (Refer [Template](../oss/CHANGELOG.md))
### Description
Support sorting with collation.
We define new custom sort operators which compare operands using applicable collation string.
`<<<` is for ascending order.
`>>>` is for descending order.
Example:
`SELECT * from <collection> ORDER BY <value> USING <<<`
This will further be used for partitioning, grouping, and related comparisons.
----
#### AI description (iteration 1)
#### PR Classification
New feature: Support for collation in sort operations.
#### PR Summary
This pull request adds support for collation in sort operations within the database engine.
- `src/test/regress/expected/bson_dollar_ops_collation_tests_runtime.out`: Added multiple test cases to verify collation behavior with various sort and order by operations, including case sensitivity and numeric ordering.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
Related work items: #4014123
### Does this PR have any customer impact?
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
### Are you introducing any new config? If yes, do you have tests with and without them being set?
### ChangeLog (Refer [Template](../oss/CHANGELOG.md))
### Description
Change signature of `bson_query_match` with `let` and `collation` support to match other internal functions.
Fix schema name for pgmongo.
----
#### AI description (iteration 1)
#### PR Classification
New feature: Collation support for query match in unsharded collections.
#### PR Summary
This pull request adds collation support for query match operations in unsharded collections, ensuring proper collation enforcement.
- Modified `bson_query_match` function calls in `/pgmongo/src/test/docdb_compat/expected/bson_dollar_ops_collation_tests_runtime.out` to adjust collation parameters.
- Updated collation parameters in `bson_query_match` function calls in `/oss/internal/pg_documentdb_distributed/src/test/regress/expected/bson_dollar_ops_collation_tests_runtime.out`.
- Adjusted SQL test cases in `/oss/internal/pg_documentdb_distributed/src/test/regress/sql/bson_dollar_ops_collation_tests_runtime.sql` to reflect new collation support.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
Related work items: #3914004
### Does this PR have any customer impact? No
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc) Feature
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes) Yes
### Are you introducing any new config? If yes, do you have tests with and without them being set? Yes; yes
### ChangeLog (Refer [Template](../oss/CHANGELOG.md))
### Description
Support `collation` with query operator using an internal UDF:
```
bson_query_match(document bson, query bson, collationString text, variableSpec bson)
```
Enable feature with `EnableCollationAndLetForQueryMatch` GUC.
Collation on sharded collections with collation-aware shard keys not supported yet.
----
#### AI description (iteration 1)
#### PR Classification
New feature
#### PR Summary
This pull request adds collation support for query operators in the engine.
- `query_operator.c`: Added logic to handle collation strings in BSON query operators and expanded function to support collation and variable specifications.
- `metadata_cache.c`: Introduced a new function to get the OID of `bson_query_match` with collation and let arguments.
- `feature_flag_configs.c`: Added a new feature flag to enable collation and let for write commands.
- `bson_query_match--0.102.sql` and `bson_query_match--latest.sql`: Added new SQL functions to support collation in BSON queries.
- `documents_planner.c`: Updated planner to recognize and handle the new collation and let functionality in queries.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
### Does this PR have any customer impact?
No
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
Refactoring
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
Yes
### Are you introducing any new config? If yes, do you have tests with and without them being set?
No
### Release Note (Refer [Template](../docs/release_notes/pgmongo_template.md))
### Description
Porting udfs into documentdb
----
#### AI description (iteration 1)
#### PR Classification
Code cleanup and feature enhancement.
#### PR Summary
This pull request continues the porting of UDFs into DocumentDB, updating and including various SQL files for better functionality and organization.
- Updated `documentdb--0.24-0.sql` to include new UDFs and operators, replacing old function references.
- Added new UDFs for query, projection, geospatial, aggregation, metadata, schema management, index management, CRUD commands, user management, schema validation, telemetry, TTL, vector search, extension version, diagnostic, and RBAC metadata.
- Removed outdated function definitions and replaced them with updated SQL includes.
### Does this PR have any customer impact?
No
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
Refactoring
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
Yes
### Are you introducing any new config? If yes, do you have tests with and without them being set?
No
### Release Note (Refer [Template](../docs/release_notes/pgmongo_template.md))
### Description
Move udf files from pg_helio_api -> pg_documentdb
----
#### AI description (iteration 1)
#### PR Classification
Code cleanup
#### PR Summary
This pull request moves User Defined Function (UDF) files from the `pg_helio_api` directory to the `pg_documentdb` directory.
- Updated `pgmongo/sql/pgmongo--1.23-0--1.24-0.sql` to include UDFs from `pg_documentdb` instead of `pg_helio_api`.
- Modified `oss/pg_helio_api/sql/pg_helio_api--1.15-0--1.16-0.sql` to reference UDFs from `pg_documentdb`.
- Adjusted `oss/pg_helio_api/sql/pg_helio_api--1.10-0.sql` to include UDFs from `pg_documentdb`.
- Updated `scripts/validate_helio_extension_state.sh` to add validation exceptions for the moved UDF files.
- Changed `pgmongo/sql/pgmongo--1.21-0--1.22-0.sql` to reference UDFs from `pg_documentdb`.
Vinod Sridharan
Santiago Fernandez Madero
Cornelius Boateng
Zinan Ren