SQL tables

Opening a table

tbl($pdo, 'name') opens a table or view. tbl($pdo, 'SELECT ...', $bindings) starts from a query with positional (?) or named (:name) bindings. The same tables can be opened with SqlTable::table() and SqlTable::query(). SqlTable::fromQuery($builder) starts from a Laravel or Doctrine query builder, and SqlTable::fromSource($pdoSource) from a PdoSource.

phibble reads the column names when the table is opened. It runs the query itself when the rows are needed: collect() returns a DataFrame, and toRows(), nrow(), iteration, and printing also run it.

showQuery() returns the SQL with ? placeholders, and bindings() returns their values in order.

// Load penguins.csv into an in-memory SQLite database.
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE penguins (species TEXT, island TEXT, bill_len REAL, bill_dep REAL,
    flipper_len INTEGER, body_mass INTEGER, sex TEXT, year INTEGER)');
$insert = $pdo->prepare('INSERT INTO penguins VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
foreach (DataFrame::fromCsv('penguins.csv')->toRows() as $row) {
    $insert->execute(array_values($row));
}

$penguins = tbl($pdo, 'penguins');

$summary = $penguins
    ->filter(col('year')->eq(2008))
    ->groupBy(['species'])
    ->summarize(['n' => n(), 'mass' => mean(col('body_mass'), na_rm: true)]);

echo get_class($summary), "\n\n";
echo $summary->showQuery(), "\n\n";
echo json_encode($summary->bindings()), "\n\n";
echo $summary->collect();

Phibble\Sql\SqlTable

SELECT "species", "n", "mass" FROM (SELECT "species", "n", "mass", ROW_NUMBER() OVER (ORDER BY ("species" IS NULL), "species") AS "__phibble_row" FROM (SELECT "species" AS "species", COUNT(*) AS "n", (CASE WHEN COUNT("body_mass") = 0 THEN NULL ELSE AVG(CAST("body_mass" AS REAL)) END) AS "mass" FROM (SELECT * FROM (SELECT *, rowid AS "__phibble_row" FROM "penguins") AS q1 WHERE ("year" = ?)) AS q2 GROUP BY "species") AS q3) AS q4 ORDER BY ("__phibble_row" IS NULL), "__phibble_row"

[2008]

# A tibble: 3 × 3
  species       n  mass
  <chr>     <int> <dbl>
1 Adelie       50 3742 
2 Chinstrap    18 3800 
3 Gentoo       46 5020.

Each verb wraps the previous query in a subquery, so each step’s SQL stays simple. Database planners usually flatten the nesting.

What compiles to SQL

These verbs compile when their arguments are expressions or column names: select(), rename(), filter(), mutate(), arrange(), groupBy(), summarize(), distinct(), slice(), head(), and join().

The compiled SQL keeps R’s semantics:

• An aggregate returns NA when its group contains NA, unless na_rm: true. SQL aggregates skip NULL, so phibble adds a check on the count of non-NULL values.
• Cumulative functions return NA after the first NA.
• Ranks leave NA rows unranked.
• isIn() never returns NA.
• / returns a float, and division by zero gives NA.
• Groups in summarize() output are sorted, with NA last.

When the table falls back to memory

Some arguments have no faithful SQL translation. A SqlTable then collects the rows and runs the verb in memory, and the verb returns a DataFrame. The values are then those DataFrame computes, apart from the known differences below. The fallbacks are:

• any PHP closure,
• round(), because SQL’s ROUND does not round half to even,
• as_string() and as_bool(),
• paste() and paste0() with arguments other than text literals, because SQL formats numbers differently from R,
• regular-expression patterns, except str_detect() in MySQL (which uses ICU, as stringr does). Patterns wrapped in fixed(), and plain patterns without regular-expression characters, are translated everywhere,
• str_extract(), str_extract_all(), str_split(), str_split_i(), str_to_title(), center str_trunc(), and str_pad(side: 'both'); str_pad() and str_squish() in SQLite; str_to_upper() and str_to_lower() in SQLite, whose UPPER() and LOWER() change only ASCII letters,
• parsing text to dates (ymd() and the others) on columns, floor_date(), ceiling_date(), round_date(), period arithmetic, with_tz(), and force_tz(), because the result type depends on the column type, which SQL tables do not track. Date parts (year() and the others), wday(), and difftime() are translated; a call on literals only, such as ymd('2024-01-01'), is evaluated once and passed as a value,
• first(), last(), and nth() without a row order (from arrange() or order_by:), or with default: or na_rm:,
• iqr() outside PostgreSQL, cumprod(), and consecutive_id(),
• sd() and var() in SQLite, whose formula would lose precision,
• str_trim(), because R removes all Unicode white space and SQL TRIM removes spaces,
• nested aggregates such as mean(x - mean(x)), summarize() entries that are not one value per group, and slice() with negative positions,
• median() and quantile() outside PostgreSQL,
• quantile() without na_rm: true,
• median(), quantile(), and n_distinct() inside grouped mutate() or filter(), because SQL has no window form of them,
• take(), eachGroup(), rowwise(), mutateGroups() with closures, and filterGroups(),
• joins with multiple, relationship, or unmatched set, joins between different connections, full joins in MySQL, and full joins with NA matching in PostgreSQL.

$pdo = new PDO('sqlite::memory:');
$t = tbl($pdo, 'SELECT 1 AS id, 2.5 AS x UNION ALL SELECT 2, 3.5');

// Compiles to SQL.
echo get_class($t->mutate(['y' => col('x')->mul(2)])), "\n";

// round() rounds half to even in R, which SQL ROUND does not, so the rows
// are collected and the expression runs in memory.
echo get_class($t->mutate(['y' => Phibble\Expr\round(col('x'))])), "\n";

// So does any closure.
echo get_class($t->filter(fn ($r) => $r->x > 3)), "\n";

echo $t->mutate(['y' => Phibble\Expr\round(col('x'))]);

Phibble\Sql\SqlTable
Phibble\DataFrame
Phibble\DataFrame
# A tibble: 2 × 3
     id     x     y
  <int> <dbl> <dbl>
1     1   2.5     2
2     2   3.5     4

SQLite compares by storage class, and it orders all text above all numbers. A column declared without a type stores bound values as text, so col('x')->gt(0) is true for every row of such a column. Declare column types (INTEGER, REAL, TEXT) when creating SQLite tables that phibble will query.

Known differences

These cases can differ from DataFrame, because the database cannot represent R’s result:

• MySQL has no infinity or NaN. Results that are Inf, -Inf, or NaN in R, such as x / 0, log(0), or mean() of no values, are NA in MySQL. SQLite stores infinities but has no NaN, so NaN is NA there.
• Integer arithmetic beyond 64 bits fails in the database, where DataFrame switches to a double.
• Joins do not warn about unexpected many-to-many matches.
• Column names starting with __phibble_ are reserved for the hidden columns that keep row order and window results.

Text compares in byte order and case-sensitively, as R does: phibble applies COLLATE "C" in PostgreSQL and utf8mb4_0900_bin in MySQL to text columns wherever values are compared, sorted, grouped, or joined.

Window functions

Grouped mutate() and filter() compile aggregates and window functions to OVER (PARTITION BY ...). Window functions such as lag() and cumsum() need a row order. SQL tables have none, so phibble numbers the rows of the source query when the table is opened and keeps that number through every step. Windows use the order_by: option where given, otherwise the order set by arrange(), and otherwise the source order. The same order decides the order of collected rows and breaks ties after arrange(), which is stable as in dplyr. The source order is the order in which the database returns the source query’s rows. For a stored table, add arrange() to make it explicit.

$pdo = new PDO('sqlite::memory:');
$sales = tbl($pdo, <<<'SQL'
    SELECT 'a' AS store, 1 AS week, 10 AS units
    UNION ALL SELECT 'a', 2, 14 UNION ALL SELECT 'a', 3, 9
    UNION ALL SELECT 'b', 1, 7 UNION ALL SELECT 'b', 2, NULL
    SQL);

$out = $sales
    ->groupBy(['store'])
    ->arrange(['store', 'week'])
    ->mutate([
        'running' => cumsum(col('units')),
        'of_best' => col('units')->div(max(col('units'), na_rm: true)),
    ]);

echo $out->showQuery(), "\n\n";
echo $out->collect();

SELECT "store", "week", "units", "running", "of_best" FROM (SELECT "store", "week", "units", "running", (CASE WHEN (CASE WHEN COUNT("units") OVER (PARTITION BY "store") = 0 THEN -9e999 ELSE MAX("units") OVER (PARTITION BY "store") END) = 0 THEN (CASE WHEN "units" > 0 THEN 9e999 WHEN "units" < 0 THEN -9e999 WHEN "units" = 0 THEN NULL END) ELSE CAST("units" AS REAL) / (CASE WHEN COUNT("units") OVER (PARTITION BY "store") = 0 THEN -9e999 ELSE MAX("units") OVER (PARTITION BY "store") END) END) AS "of_best", "__phibble_row" FROM (SELECT "store", "week", "units", (CASE WHEN COUNT(*) OVER (PARTITION BY "store" ORDER BY ("store" IS NULL), "store", ("week" IS NULL), "week", ("__phibble_row" IS NULL), "__phibble_row" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > COUNT("units") OVER (PARTITION BY "store" ORDER BY ("store" IS NULL), "store", ("week" IS NULL), "week", ("__phibble_row" IS NULL), "__phibble_row" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) THEN NULL ELSE SUM("units") OVER (PARTITION BY "store" ORDER BY ("store" IS NULL), "store", ("week" IS NULL), "week", ("__phibble_row" IS NULL), "__phibble_row" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END) AS "running", "__phibble_row" FROM (SELECT *, ROW_NUMBER() OVER () AS "__phibble_row" FROM (SELECT 'a' AS store, 1 AS week, 10 AS units
UNION ALL SELECT 'a', 2, 14 UNION ALL SELECT 'a', 3, 9
UNION ALL SELECT 'b', 1, 7 UNION ALL SELECT 'b', 2, NULL) AS phibble_source) AS q1) AS q2) AS q3 ORDER BY ("store" IS NULL), "store", ("week" IS NULL), "week", ("__phibble_row" IS NULL), "__phibble_row"

# A tibble: 5 × 5
# Groups:   store [2]
  store  week units running of_best
  <chr> <int> <int>   <int>   <dbl>
1 a         1    10      10   0.714
2 a         2    14      24   1    
3 a         3     9      33   0.643
4 b         1     7       7   1    
5 b         2    NA      NA  NA

Joins

join() between two tables on the same PDO connection compiles to a SQL join. The output columns follow DataFrame::join(): x’s columns, then y’s other columns, with .x and .y suffixes on clashing names. NA keys match each other, as in dplyr, through IS in SQLite, IS NOT DISTINCT FROM in PostgreSQL, and <=> in MySQL. Pass naMatches: false for SQL’s usual equality. The order of rows from a SQL join is not defined, so call arrange() when it matters.

$pdo = new PDO('sqlite::memory:');
$visits = tbl($pdo, "SELECT 1 AS clinic_id, 120 AS bp UNION ALL SELECT 2, 135 UNION ALL SELECT NULL, 128");
$clinics = tbl($pdo, "SELECT 1 AS id, 'north' AS name UNION ALL SELECT NULL, 'unassigned'");

$joined = $visits->join($clinics, [['clinic_id', 'id']], 'left');

echo $joined->showQuery(), "\n\n";
echo $joined->arrange(['bp'])->collect();

SELECT "clinic_id", "bp", "name" FROM (SELECT jq1x."clinic_id" AS "clinic_id", jq1x."bp" AS "bp", jq1y."name" AS "name", jq1x."__phibble_row" AS "__phibble_row", jq1y."__phibble_row" AS "__phibble_row_1" FROM (SELECT *, ROW_NUMBER() OVER () AS "__phibble_row" FROM (SELECT 1 AS clinic_id, 120 AS bp UNION ALL SELECT 2, 135 UNION ALL SELECT NULL, 128) AS phibble_source) AS jq1x LEFT JOIN (SELECT *, ROW_NUMBER() OVER () AS "__phibble_row" FROM (SELECT 1 AS id, 'north' AS name UNION ALL SELECT NULL, 'unassigned') AS phibble_source) AS jq1y ON (jq1x."clinic_id" IS jq1y."id")) AS q2 ORDER BY ("__phibble_row" IS NULL), "__phibble_row", ("__phibble_row_1" IS NULL), "__phibble_row_1"

# A tibble: 3 × 3
  clinic_id    bp name      
      <int> <int> <chr>     
1         1   120 north     
2        NA   128 unassigned
3         2   135 <NA>

Dialects

Dialect::for($pdo) chooses the dialect from the PDO driver. The dialects differ in identifier quoting, cast types, string functions, and NULL-safe equality. The SQL for one expression in each dialect:

use Phibble\Sql\Compiler;
use Phibble\Sql\MysqlDialect;
use Phibble\Sql\PostgresDialect;
use Phibble\Sql\SqliteDialect;

$expressions = [
    'mean, NA aware' => mean(col('bp')),
    'ratio' => col('bp')->div(col('baseline')),
    'contains' => str_detect(col('name'), 'north'),
    'in, with NA' => col('arm')->isIn(['a', null]),
];

foreach ([new SqliteDialect(), new PostgresDialect(), new MysqlDialect()] as $dialect) {
    echo "-- {$dialect->name()}\n";
    $compiler = new Compiler($dialect, mode: 'summary');
    foreach ($expressions as $label => $expr) {
        echo \str_pad($label, 16), $compiler->compile($expr)->sql, "\n";
    }
    echo "\n";
}

$window = new Compiler(new PostgresDialect(), groups: ['clinic'], order: [[col('visit'), false]]);
echo $window->compile(cumsum(col('bp')))->sql, "\n";

-- SQLite
mean, NA aware  (CASE WHEN COUNT(*) > COUNT("bp") THEN NULL ELSE (CASE WHEN COUNT("bp") = 0 THEN NULL ELSE AVG(CAST("bp" AS REAL)) END) END)
ratio           (CASE WHEN "baseline" = 0 THEN (CASE WHEN "bp" > 0 THEN 9e999 WHEN "bp" < 0 THEN -9e999 WHEN "bp" = 0 THEN NULL END) ELSE CAST("bp" AS REAL) / "baseline" END)
contains        (INSTR("name", ?) > 0)
in, with NA     (COALESCE("arm" IN (?), FALSE) OR "arm" IS NULL)

-- PostgreSQL
mean, NA aware  (CASE WHEN COUNT(*) > COUNT("bp") THEN NULL ELSE (CASE WHEN COUNT("bp") = 0 THEN 'NaN'::float8 ELSE AVG(CAST("bp" AS DOUBLE PRECISION)) END) END)
ratio           (CASE WHEN "baseline" = 0 THEN (CASE WHEN "bp" > 0 THEN 'Infinity'::float8 WHEN "bp" < 0 THEN '-Infinity'::float8 WHEN "bp" = 0 THEN 'NaN'::float8 END) ELSE CAST("bp" AS DOUBLE PRECISION) / "baseline" END)
contains        (POSITION(? IN "name") > 0)
in, with NA     (COALESCE("arm" IN (?), FALSE) OR "arm" IS NULL)

-- MySQL
mean, NA aware  (CASE WHEN COUNT(*) > COUNT(`bp`) THEN NULL ELSE (CASE WHEN COUNT(`bp`) = 0 THEN NULL ELSE AVG(CAST(`bp` AS DOUBLE)) END) END)
ratio           (CASE WHEN `baseline` = 0 THEN (CASE WHEN `bp` > 0 THEN NULL WHEN `bp` < 0 THEN NULL WHEN `bp` = 0 THEN NULL END) ELSE CAST(`bp` AS DOUBLE) / `baseline` END)
contains        (LOCATE(?, `name`) > 0)
in, with NA     (COALESCE(`arm` IN (?), FALSE) OR `arm` IS NULL)

(CASE WHEN COUNT(*) OVER (PARTITION BY "clinic" ORDER BY ("visit" IS NULL), "visit" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > COUNT("bp") OVER (PARTITION BY "clinic" ORDER BY ("visit" IS NULL), "visit" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) THEN NULL ELSE SUM("bp") OVER (PARTITION BY "clinic" ORDER BY ("visit" IS NULL), "visit" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END)

The test suite runs the dplyr reference cases through SqlTable on SQLite, and on PostgreSQL and MySQL when their DSNs are set. See Development.