Joins
Verification
Scope
Output columns and rows of join_by() joins on all five backends: inequality, closest() rolling, between, within, and overlap conditions, across join types and the keep and naMatches options.
References
- Reference
- dplyr 1.2.1 join_by(), on R 4.6.1
- Versions
- R 4.6.1, dplyr 1.2.1, jsonlite 2.0.0, PHP 8.5.10, SQLite 3.45.2, PostgreSQL 17.0 (Laravel Herd), MySQL 8.0.46, php_polars 0.1.0
Data
- join cases
- Two tables with NA keys; two interval tables Source
Method
tests/r/joins.R runs each join with dplyr and writes the output. The PHP tests run the same joins on the same tables in each backend. Fixtures regenerated with the listed R versions on 2026-11-01; output byte-identical to the committed files.
- Check
- Comparison with a reference implementation
- Recorded
- 2026-11-02, PHP 8.5.10, macOS 27.0.1 (arm64), commit 1abdeb1
Tolerances
exact equality
Applies to: Columns and rows in order
Numbers as doubles, NA in identical positions
exact equality
Applies to: Columns and sorted rows
SQL and Polars joins have no defined row order, Polars with no fallback to a DataFrame
x: package value; r: reference value.
Results
70 comparisons in 14 cases: 70 pass, 0 fail, 0 skipped.
By case
| Case | Comparisons | Status |
|---|---|---|
| ge_eq | 5 | pass |
| closest_ge | 5 | pass |
| gt_only | 5 | pass |
| closest_le | 5 | pass |
| semi_lt | 5 | pass |
| anti_lt | 5 | pass |
| full_closest | 5 | pass |
| right_ge | 5 | pass |
| keep_true | 5 | pass |
| na_never | 5 | pass |
| between | 5 | pass |
| within | 5 | pass |
| overlaps | 5 | pass |
| closest_gt_inner | 5 | pass |
All 70 comparisons
| Case | Implementation | Quantity | Criterion | Values | Status |
|---|---|---|---|---|---|
| ge_eq | DataFrame | join | exact equality | 40 | pass |
| closest_ge | DataFrame | join | exact equality | 30 | pass |
| gt_only | DataFrame | join | exact equality | 48 | pass |
| closest_le | DataFrame | join | exact equality | 25 | pass |
| semi_lt | DataFrame | join | exact equality | 6 | pass |
| anti_lt | DataFrame | join | exact equality | 6 | pass |
| full_closest | DataFrame | join | exact equality | 35 | pass |
| right_ge | DataFrame | join | exact equality | 40 | pass |
| keep_true | DataFrame | join | exact equality | 48 | pass |
| na_never | DataFrame | join | exact equality | 40 | pass |
| between | DataFrame | join | exact equality | 0 | pass |
| within | DataFrame | join | exact equality | 0 | pass |
| overlaps | DataFrame | join | exact equality | 18 | pass |
| closest_gt_inner | DataFrame | join | exact equality | 24 | pass |
| ge_eq | Polars | join | exact equality | 40 | pass |
| closest_ge | Polars | join | exact equality | 30 | pass |
| gt_only | Polars | join | exact equality | 48 | pass |
| closest_le | Polars | join | exact equality | 25 | pass |
| semi_lt | Polars | join | exact equality | 6 | pass |
| anti_lt | Polars | join | exact equality | 6 | pass |
| full_closest | Polars | join | exact equality | 35 | pass |
| right_ge | Polars | join | exact equality | 40 | pass |
| keep_true | Polars | join | exact equality | 48 | pass |
| na_never | Polars | join | exact equality | 40 | pass |
| between | Polars | join | exact equality | 0 | pass |
| within | Polars | join | exact equality | 0 | pass |
| overlaps | Polars | join | exact equality | 18 | pass |
| closest_gt_inner | Polars | join | exact equality | 24 | pass |
| ge_eq | SQLite | join | exact equality | 40 | pass |
| closest_ge | SQLite | join | exact equality | 30 | pass |
| gt_only | SQLite | join | exact equality | 48 | pass |
| closest_le | SQLite | join | exact equality | 25 | pass |
| semi_lt | SQLite | join | exact equality | 6 | pass |
| anti_lt | SQLite | join | exact equality | 6 | pass |
| full_closest | SQLite | join | exact equality | 35 | pass |
| right_ge | SQLite | join | exact equality | 40 | pass |
| keep_true | SQLite | join | exact equality | 48 | pass |
| na_never | SQLite | join | exact equality | 40 | pass |
| between | SQLite | join | exact equality | 0 | pass |
| within | SQLite | join | exact equality | 0 | pass |
| overlaps | SQLite | join | exact equality | 18 | pass |
| closest_gt_inner | SQLite | join | exact equality | 24 | pass |
| ge_eq | PostgreSQL | join | exact equality | 40 | pass |
| closest_ge | PostgreSQL | join | exact equality | 30 | pass |
| gt_only | PostgreSQL | join | exact equality | 48 | pass |
| closest_le | PostgreSQL | join | exact equality | 25 | pass |
| semi_lt | PostgreSQL | join | exact equality | 6 | pass |
| anti_lt | PostgreSQL | join | exact equality | 6 | pass |
| full_closest | PostgreSQL | join | exact equality | 35 | pass |
| right_ge | PostgreSQL | join | exact equality | 40 | pass |
| keep_true | PostgreSQL | join | exact equality | 48 | pass |
| na_never | PostgreSQL | join | exact equality | 40 | pass |
| between | PostgreSQL | join | exact equality | 0 | pass |
| within | PostgreSQL | join | exact equality | 0 | pass |
| overlaps | PostgreSQL | join | exact equality | 18 | pass |
| closest_gt_inner | PostgreSQL | join | exact equality | 24 | pass |
| ge_eq | MySQL | join | exact equality | 40 | pass |
| closest_ge | MySQL | join | exact equality | 30 | pass |
| gt_only | MySQL | join | exact equality | 48 | pass |
| closest_le | MySQL | join | exact equality | 25 | pass |
| semi_lt | MySQL | join | exact equality | 6 | pass |
| anti_lt | MySQL | join | exact equality | 6 | pass |
| full_closest | MySQL | join | exact equality | 35 | pass |
| right_ge | MySQL | join | exact equality | 40 | pass |
| keep_true | MySQL | join | exact equality | 48 | pass |
| na_never | MySQL | join | exact equality | 40 | pass |
| between | MySQL | join | exact equality | 0 | pass |
| within | MySQL | join | exact equality | 0 | pass |
| overlaps | MySQL | join | exact equality | 18 | pass |
| closest_gt_inner | MySQL | join | exact equality | 24 | pass |
Reproduction
From the repository root
Rscript tests/r/joins.R
PHIBBLE_TEST_PGSQL="pgsql:host=127.0.0.1;port=5432;dbname=postgres" PHIBBLE_TEST_PGSQL_USER=postgres PHIBBLE_TEST_MYSQL="mysql:host=127.0.0.1;port=3306" PHIBBLE_TEST_MYSQL_USER=root php vendor/bin/pest tests/ContractV1Test.php tests/SqlTest.php
php -d extension=php_polars.so php vendor/bin/pest tests/Polars/PolarsContractTest.php- Raw record
- site/verifications/join-by.json
- Input data
- tests/fixtures/joins/expected.json