SQL parity
Verification
Scope
SqlTable pipelines on SQLite, PostgreSQL, and MySQL against the same pipelines on a DataFrame: verbs, row order, window functions, joins, byte-order text, boolean aggregates, edge-case arithmetic, and driver column types.
References
- Versions
- PHP 8.5.10, SQLite 3.45.2, PostgreSQL 17.0 (Laravel Herd), MySQL 8.0.46
Data
- Inputs
- Inline SELECT ... UNION ALL data in tests/SqlTest.php, tests/SqlReviewTest.php, and tests/DriverTypesTest.php; temporary tables only.
Method
Each test runs a pipeline as SQL, collects the result, and compares it with the DataFrame result or a fixed expectation.
- Check
- End-to-end run with the external program
- Recorded
- 2026-11-02, PHP 8.5.10, macOS 27.0.1 (arm64), commit 1abdeb1
Tolerances
exact equality
Applies to: Rows, row order, types, generated SQL, or exception class
Doubles rounded to 9 decimal places
x: package value; r: reference value.
Results
103 comparisons in 46 cases: 103 pass, 0 fail, 0 skipped.
By case
| Case | Comparisons | Status |
|---|---|---|
| keeps PostgreSQL text as text and types the rest as declared | 1 | pass |
| reads PostgreSQL timestamptz with an hour-only offset | 1 | pass |
| reads decimals as doubles, whole-number decimals as integers, and keeps them exact on request | 2 | pass |
| keeps MySQL text as text and types the rest as declared | 1 | pass |
| types empty results as declared | 2 | pass |
| still guesses SQLite values, which carry no declared type | 1 | pass |
| matches DataFrame after the review fixes: arrange twice keeps earlier order for ties | 3 | pass |
| matches DataFrame after the review fixes: mutate overwriting a sort key keeps the row order | 3 | pass |
| matches DataFrame after the review fixes: float literal against an integer column | 3 | pass |
| matches DataFrame after the review fixes: near with a float tolerance | 3 | pass |
| matches DataFrame after the review fixes: nested aggregate | 3 | pass |
| matches DataFrame after the review fixes: sqrt and log outside the domain | 3 | pass |
| matches DataFrame after the review fixes: exp overflow | 3 | pass |
| matches DataFrame after the review fixes: division by zero | 3 | pass |
| matches DataFrame after the review fixes: as_int truncates | 3 | pass |
| matches DataFrame after the review fixes: slice with a negative offset | 3 | pass |
| matches DataFrame after the review fixes: slice with a negative length | 3 | pass |
| matches DataFrame after the review fixes: summarise of a non-aggregate | 3 | pass |
| matches DataFrame after the review fixes: min, max and mean of no values | 3 | pass |
| matches DataFrame after the review fixes: str_trim of tabs and newlines | 3 | pass |
| matches DataFrame after the review fixes: brace in a column name | 3 | pass |
| aggregates boolean columns | 3 | pass |
| keeps text with trailing spaces apart and picks closest text in byte order | 3 | pass |
| does not break a PostgreSQL transaction when opening a view | 1 | pass |
| parses named bindings around comments and MySQL escapes | 1 | pass |
| compares strictly in in() and trims Unicode spaces, as R does | 1 | pass |
| keeps microseconds when serialising datetime literals | 1 | pass |
| ranks and orders factors by level, as dplyr does | 1 | pass |
| divides to a double, as R does | 1 | pass |
| gives NA for empty fixed patterns, as stringr does | 1 | pass |
| runs verbs lazily and matches DataFrame | 3 | pass |
| joins in SQL like DataFrame::join | 3 | pass |
| falls back to memory for closures and untranslatable expressions | 1 | pass |
| shows the query and keeps bindings in order | 1 | pass |
| compiles dialect-specific SQL | 1 | pass |
| opens tables and queries with tbl() | 1 | pass |
| rejects bindings for a table name in tbl() | 1 | pass |
| uses every aggregate inside grouped mutate like DataFrame | 3 | pass |
| keeps the row order when reading one column | 3 | pass |
| translates first, last, nth and str_replace to SQL | 3 | pass |
| runs windows in source row order without arrange | 3 | pass |
| keeps row order through select and distinct after arrange | 3 | pass |
| translates date parts and string functions to SQL | 3 | pass |
| keeps source order on stored tables with physical row ids | 2 | pass |
| compares, sorts, groups and joins text in byte order like R | 3 | pass |
| compares a datetime column with a midnight literal as DataFrame does | 3 | pass |
All 103 comparisons
| Case | Implementation | Quantity | Status |
|---|---|---|---|
| keeps PostgreSQL text as text and types the rest as declared | pass | ||
| reads PostgreSQL timestamptz with an hour-only offset | pass | ||
| reads decimals as doubles, whole-number decimals as integers, and keeps them exact on request | PostgreSQL | pass | |
| MySQL | pass | ||
| keeps MySQL text as text and types the rest as declared | pass | ||
| types empty results as declared | PostgreSQL | pass | |
| MySQL | pass | ||
| still guesses SQLite values, which carry no declared type | pass | ||
| matches DataFrame after the review fixes: arrange twice keeps earlier order for ties | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: mutate overwriting a sort key keeps the row order | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: float literal against an integer column | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: near with a float tolerance | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: nested aggregate | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: sqrt and log outside the domain | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: exp overflow | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: division by zero | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: as_int truncates | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: slice with a negative offset | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: slice with a negative length | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: summarise of a non-aggregate | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: min, max and mean of no values | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: str_trim of tabs and newlines | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| matches DataFrame after the review fixes: brace in a column name | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| aggregates boolean columns | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| keeps text with trailing spaces apart and picks closest text in byte order | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| does not break a PostgreSQL transaction when opening a view | pass | ||
| parses named bindings around comments and MySQL escapes | pass | ||
| compares strictly in in() and trims Unicode spaces, as R does | pass | ||
| keeps microseconds when serialising datetime literals | pass | ||
| ranks and orders factors by level, as dplyr does | pass | ||
| divides to a double, as R does | pass | ||
| gives NA for empty fixed patterns, as stringr does | pass | ||
| runs verbs lazily and matches DataFrame | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| joins in SQL like DataFrame::join | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| falls back to memory for closures and untranslatable expressions | pass | ||
| shows the query and keeps bindings in order | pass | ||
| compiles dialect-specific SQL | pass | ||
| opens tables and queries with tbl() | pass | ||
| rejects bindings for a table name in tbl() | pass | ||
| uses every aggregate inside grouped mutate like DataFrame | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| keeps the row order when reading one column | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| translates first, last, nth and str_replace to SQL | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| runs windows in source row order without arrange | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| keeps row order through select and distinct after arrange | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| translates date parts and string functions to SQL | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| keeps source order on stored tables with physical row ids | SQLite | pass | |
| PostgreSQL | pass | ||
| compares, sorts, groups and joins text in byte order like R | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass | ||
| compares a datetime column with a midnight literal as DataFrame does | SQLite | pass | |
| PostgreSQL | pass | ||
| MySQL | pass |
Limitations
Exclusions
- Source order of stored tables runs on SQLite and PostgreSQL only: MySQL tables have no physical row id like SQLite's rowid or PostgreSQL's ctid, and SqlTable numbers MySQL rows with ROW_NUMBER(), which the other row-order cases cover.
Known differences
- MySQL stores no Inf or NaN, and SQLite stores no NaN. These values return as NA and are compared as NA.
- Joins do not warn about unexpected many-to-many matches.
Reproduction
From the repository root
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/SqlTest.php tests/SqlReviewTest.php tests/DriverTypesTest.php- Raw record
- site/verifications/sql-parity.json