Missing values

Vectro has no separate null marker. A missing number or date is NaN; missing text is ''. Aggregates skip missing values the way SQL skips NULL, so a blank price doesn’t drag an average to zero.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  region: ['EU', 'US', '', 'APAC', 'US', 'EU'],
  price:  [120, NaN, 45, NaN, 99, 15],
});

console.log(orders.agg({
  rows:     'count()',
  priced:   'count(price)',
  total:    'sum(price)',
  average:  'mean(price)',   // 279 / 4, not 279 / 6
  cheapest: 'min(price)',
}));
await engine.close();

Where missing values come from

Source	Missing number or date	Missing text
CSV	an empty field	an empty field
JSON lines	null, or the key absent	null, or the key absent
Arrow	a null	a null
engine.table(columns)	NaN, null or undefined	''

In engine.table and engine.tableFromRows, null and undefined in a number column load as missing, like NaN. A missing date in an array of Date objects can be null too.

When rows come back from toRows(), missing numbers are NaN, missing text is '' and missing dates are null.

Aggregates

Aggregate	With missing values
count()	counts every row
count(x)	counts the rows where x is present
count(x > 5)	counts the rows where the condition holds (a missing x doesn’t)
sum(x)	adds the values present
mean(x)	divides by the number of values present
min(x), max(x)	ignore missing values; NaN if none are present
countDistinct(x), approxDistinct(x)	a missing number doesn’t count as a value

These rules are about missing numbers and dates. On a text column, count(region) and countDistinct(region) count '' like any other label; filter it out first with filter("region != ''") if it shouldn’t count.

One difference from SQL: in agg and groupBy().agg, the sum of no values is 0. SQL’s SUM of no values is NULL, and sql() gives NaN there.

In expressions

isMissing(x) is true where x is missing, and coalesce(x, y, ...) gives the first value that is present. Both work in any expression: filters, derived columns, aggregates.

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({ price: [120, NaN, 45, NaN, 99], qty: [2, 1, 4, 1, 3] });

console.log(t.filter('isMissing(price)').count());
console.log(t.sum('coalesce(price, 0) * qty'));

// Comparisons with NaN are false, so negations let missing rows through:
console.log(t.filter('price > 50').count());     // 2
console.log(t.filter('!(price > 50)').count());  // 3: 45 and the two missing
console.log(t.filter('price != 50').count());    // 5
console.log(t.filter('!(price > 50) && !isMissing(price)').count()); // 1
await engine.close();

Expressions follow JavaScript’s rule for NaN: every comparison with it is false. So price > 50 drops missing prices, but !(price > 50) and price != 50 keep them. Add !isMissing(price) when you mean “a price that isn’t over 50”.

For text, compare with the empty string: filter("region == ''").

Group by, joins and sort

• Group by: a missing number key (NaN) is a group of its own, and comes after the other numbers. A missing text key '' is a group of its own too.
• Joins: a key with a missing part never matches, as NULL doesn’t in SQL. A left join fills the right columns of unmatched rows with NaN or ''. See joins.
• Sort and top: missing values come last, whether you sort up or down. SQL’s NULLS FIRST puts them at the start. See sort and top-k.
• Compressed columns store a missing value as a reserved code, so packing never loses one. See compressed columns.

In SQL

sql() uses the same markers, read as NULL:

• x IS NULL and x IS NOT NULL work on numbers, dates and text (where '' is null).
• COALESCE, IFNULL, NVL and NULLIF work on numbers and text.
• COUNT(x), SUM, AVG, MIN, MAX, percentiles and window aggregates skip missing numbers.
• ORDER BY x NULLS FIRST | NULLS LAST places them; the default is last.
• CASE WHEN price > 100 ... sends a missing price to ELSE, as in SQL.
• x IS DISTINCT FROM y treats two missing values as equal.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const t = engine.table({
  region: ['EU', 'US', '', 'APAC', 'US', 'EU'],
  price:  [120, NaN, 45, NaN, 99, 15],
});

console.log(sql(`
  SELECT COUNT(*) AS n, COUNT(price) AS priced, AVG(price) AS avg_price,
         SUM(CASE WHEN price IS NULL THEN 1 ELSE 0 END) AS unpriced
  FROM t`, { t }).toRows());

console.log(sql(`
  SELECT COALESCE(region, 'unknown') AS region, COALESCE(price, 0) AS price
  FROM t ORDER BY price DESC`, { t }).toRows());
await engine.close();

Three-valued logic in SQL

SQL follows the standard: a comparison with a missing value is unknown, and NOT unknown is still unknown, so the row is dropped. These all leave out rows whose price is missing, as PostgreSQL and DuckDB do:

• NOT (price > 50)
• price <> 50
• price NOT IN (45, 15)
• price NOT BETWEEN 40 AND 50

The same holds for missing text: region <> 'EU', region NOT IN (...), region LIKE '%' and region NOT LIKE 'E%' all skip rows where region is '', and COUNT(region) and COUNT(DISTINCT region) don’t count it.

Table expressions follow JavaScript

The expressions you pass to filter, derive and friends compare the way JavaScript does: price != 50 and !(price > 50) are true where price is NaN. Add && !isMissing(price) when a missing value should not pass:

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({ price: [10, NaN, 60, 50] });
console.log(t.filter('price != 50').count());                         // 3: NaN != 50 is true
console.log(t.filter('price != 50 && !isMissing(price)').count());    // 2
await engine.close();

Counting text works as in SQL: count(region), countDistinct(region) and approxDistinct(region) skip ''.