### 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?
No
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
Feature
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
No
### 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
This change ports more customizations of RUM into the documentdb_rum fork
1) Support Fast rumgettuple when there's no order by clauses or it's a simple order by. This ensures that we don't fetch all the rows, put it into a tuplestore, sort it and return it and instead just rely on the index to return it in TID order *OR* the raw index order if specified.
2) For Compare partial entries, instead of storing the FULL rum item that has the addInfo -> If there's no addinfo just store the tid in the tuplestore
3) Support order by on the raw index key operator
----
#### AI description (iteration 1)
#### PR Classification
This PR implements new ordering and scan functionalities and enhances partial-match processing in the documentdb_rum index.
#### PR Summary
The changes port additional features to the documentdb_rum index, introducing full support for order-by ordered scans, rechecking mechanisms, and improved handling of minimal tuple matches while updating configuration and testing outputs. Key changes include:
- **`oss/internal/pg_documentdb_rum/src/rumget.c`**: Added new functions (e.g. for ordered scans, rechecking order-by keys, and minimal tuple handling) to support ordered index scans.
- **`oss/internal/pg_documentdb_rum/src/rumscan.c`**: Implemented support for ordering entries, validated ordering function signatures, and enforced error conditions for multi-column order-by.
- **`pg_documentdb_rum/src/pg_documentdb_rum.h`**: Updated scan structure definitions with new fields for ordered scan support and the simple scan toggle.
- Updated output expectations (e.g. `innerScanLoops` values) and configuration (GUC variable `RumAllowOrderByRawKeys`) in related test scripts and utility files.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
----
#### AI description (iteration 1)
#### PR Classification
This pull request introduces new feature ports to support ordered index scan (order‑by functionality) in the documentdb_rum index.
#### PR Summary
The changes add extensive support for ordered scans in the documentdb_rum index by porting and adapting functionality originally developed for pgmongo. High-level updates include:
- **`/oss/internal/pg_documentdb_rum/src/rumget.c`**: New and updated functions (e.g. for moving scan pointers, obtaining ordered items, and preparing matched entries) enable order‑by processing during index scans.
- **`/oss/internal/pg_documentdb_rum/src/rumscan.c`**: Enhancements in handling order‑by keys and validating ordering function signatures allow use of raw ordering keys, with error checks added for multicolumn indexes.
-...
### Does this PR have any customer impact?
No
### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
Comments
### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
No
### 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
All the previous version SQL changes should be merged into this PR so that we can update the hashes in this PR
----
#### AI description (iteration 1)
#### PR Classification
This PR is a documentation update that cleans up inline SQL comments to align with the latest contributing guidelines.
#### PR Summary
The changes refine the SQL file comments for geospatial operator classes and functions by removing outdated references to Mongo indexing and validations.
- `oss/pg_documentdb/sql/operators/bson_gist_geospatial_op_classes--0.16-0.sql`: Updated comments by replacing "mongo 2d index options" and "mongo 2dsphjere index options" with simplified descriptions.
- `oss/pg_documentdb/sql/udfs/geospatial/bson_gist_extensibility_functions--0.16-0.sql` and `oss/pg_documentdb/sql/udfs/geospatial/bson_gist_extensibility_functions--latest.sql`: Revised function comments to remove "mongo db validations" for a more general description of geometry validations.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
Related work items: #4354111
### 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
Support for pushdown of to PFE indexes
----
#### AI description (iteration 1)
#### PR Classification
New feature
#### PR Summary
This pull request adds support for pushdown operations to PFE indexes, enhancing query performance and index utilization.
- Added new functions in `sql/udfs/index_mgmt/bson_indexbounds_functions--0.102-0.sql` for BSON index bounds operations.
- Introduced new operators in `sql/operators/bson_btree_pfe_operators--0.102-0.sql` for BSON and BSON query comparisons.
- Updated `src/query/query_operator.c` to handle new index bounds operators and expressions.
- Added new type definitions in `sql/types/bsonindexbounds--0.102-0.sql` for BSON index bounds.
- Modified `src/metadata/metadata_cache.c` to include OIDs and functions for BSON index bounds types and operators.
<!-- 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
Start porting schemas for documentdb api.
Introducing API_SCHEMA_NAME_V2, API_SCHEMA_INTERNAL_NAME_V2, API_CATALOG_SCHEMA_NAME_V2
----
#### AI description (iteration 1)
#### PR Classification
Porting type/operator/schema into documentdb.
#### PR Summary
This pull request ports various types, operators, and schemas into documentdb as part of an ongoing effort.
- `pg_documentdb/sql/documentdb--0.24-0.sql`: Created multiple schemas and included various SQL files for query functions, operators, and metadata.
- `pg_documentdb/sql/operators/bson_dollar_operators--0.16-0.sql`: Updated operator definitions to use `__API_SCHEMA_INTERNAL_V2__`.
- `pgmongo/sql/operators/bson_dollar_negation_operators--1.15-1.sql` and `pg_documentdb/sql/operators/bson_dollar_negation_operators--0.16-0.sql`: Changed operator definitions to use `__API_SCHEMA_INTERNAL_V2__`.
- `Makefile.cflags`: Added checks for new schema variables `API_SCHEMA_NAME_V2`, `API_SCHEMA_INTERNAL_NAME_V2`, and `API_CATALOG_SCHEMA_NAME_V2`.
- `pg_documentdb/sql/schema/unique_shard_path_operator_class--0.24-0.sql`: Updated operator class definitions to use `__API_SCHEMA_INTERNAL_V2__`.
### 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 type/operator/schema files from pg_helio_api -> pg_documentdb
Except udf files
----
#### AI description (iteration 1)
#### PR Classification
Code cleanup
#### PR Summary
This pull request moves type, operator, and schema files from the `pg_helio_api` directory to the `pg_documentdb` directory.
- Updated SQL include paths in multiple `pg_helio_api` SQL files to reference `pg_documentdb` directory.
- Modified `pgmongo` SQL files to include paths from `pg_documentdb` instead of `pg_helio_api`.
- Adjusted `validate_helio_extension_state.sh` script to account for new file paths.
- Updated `Makefile` in `pg_helio_api` to include the new directory structure.
- Changed include paths in `pg_documentdb_distributed` and `pg_helio_distributed` C source files to reflect the new directory structure.
Vinod Sridharan
Deepak Bisht
Cornelius Boateng
Zinan Ren