Return null, not undefined, for SQL NULL: what it means for the code #2588

For v5: the JavaScript value alasql produces for SQL NULL becomes null. Today it is undefined. This issue describes where undefined comes from today, what the change means for the code, where it should be done (when inserting, when selecting, or both), and what breaks for users.

It is about the value only. Three-valued logic in comparisons (#1414) is a separate topic.

Why

• JSON.stringify({a: undefined}) is {}. A result row loses its column on the way to a server, a file or localStorage. With null the key survives.
• Result rows keep one shape. Exports and column detection take their columns from the rows.
• Every other SQL library in JavaScript returns null for NULL. A SQL user expects it.
• It keeps a real distinction: "the value is NULL" against "there is no such property".

Where undefined comes from today

Measured on v4.20.0.

So the same query can already return both. SELECT a, b over [{a: 1}, {a: 2, b: null}] gives [{a: 1, b: undefined}, {a: 2, b: null}].

The reason for undefined was never a rule about NULL. A row is a plain object, a missing property reads as undefined, and so INSERT can skip the columns it was not given and every read still "works". NULL was then compiled to undefined so that the literal and the missing property look the same. The original argument was speed (#85, 2015).

In src/ (generated parser excluded) the word undefined occurs about 350 times: about 110 typeof … == 'undefined' tests and about 55 direct comparisons, the rest mostly inside generated code. There are about 110 comparisons with null. Many of these are about optional arguments and not about NULL; they have to be told apart one by one.

Things that are already wrong because of the split

Measured on v4.20.0. These are reasons to do the work, and they are the test cases for it.

• SELECT a, b INTO CSV(…) writes the texts "undefined" and "null" into the cells.
• GROUP BY b makes one group for null and another for a missing b. SELECT DISTINCT b merges them.
• UPPER(?) with null returns 'NULL'; LEN(?), TRIM(?), SUBSTRING(?, 1, 2) throw; ABS(?) is 0; YEAR(?) is 1970. With undefined all of them return undefined.
• CAST(NULL AS INT) is 0, and CAST(NULL AS INT) + 80 is 80.
• -b is 0 when b is null and undefined when b is missing.
• INSERT INTO u VALUES (1, NULL) into an INT column stores NaN. With a column list it stores undefined. With a null parameter it stores 0. The row with NaN is then not found by WHERE b IS NULL and is counted by COUNT(b). (Known from fix: INSERT without a column list stores NaN instead of NULL in numeric columns #2563.)
• SUM(b) over no rows is 0; the other aggregates give undefined.
• A user function with a default parameter, alasql.fn.f = (x = 5) => x: f(NULL) returns 5 (the default fires on undefined), f(?) with null returns null.
• Tests 2000, 1977 and 1666 expect null and get undefined (Recover test 2000, 1977 and 1666 #2168).

Binary operators are already safe: b < 1, b + 80, b * 2 treat null and a missing b alike today.

Insert time or select time?

Both have a job, and neither is enough alone.

When selecting (output). The code generator for the select list emits one assignment per output column. Writing it as expr ?? null turns every undefined into null in one place, whatever produced it: a missing key, an outer join, a function, an aggregate.

• Cost, measured in a loop shaped like the generated code, one million rows of five columns: 16.3 ms plain, 16.4 ms with ?? null. Not measurable.
• It cannot fix SELECT * over a plain array. Those rows are handed back as they are, and a row that lacks a key keeps lacking it. alasql should not rewrite values it only passes through.

When inserting (storage). For a table with declared columns, INSERT can write null into every column it was not given.

• Cost, measured the same way, one million rows: 15.0 ms leaving the key out, 15.9 ms writing null.
• Every row of the table then has the same keys. SELECT * returns all columns, exports get all columns, and what is saved to localStorage, IndexedDB or a file reads back the same.
• It does not apply to a table without declared columns, and never to an array passed as a parameter (FROM ?). alasql does not write into the caller's data unless asked to (UPDATE ?).

Reading. Because the caller's data will always contain both, every operator, function, aggregate, grouping and join must treat null and undefined as the same NULL. This is the precondition for the other two. For the functions it is a one-line change in und().

Proposed rule, in one sentence: alasql reads null and undefined alike, stores and creates null, and returns null for every column it computes.

Work list

1. und() and every other NULL test: accept both (y == null).
2. NullValue.toJS() returns null.
3. Select list: ?? null on computed columns; SELECT * over a table with declared columns fills the declared columns.
4. INSERT, and ALTER TABLE … ADD COLUMN: write null for declared columns without a value. Explicit NULL into a typed column stores null, not NaN or 0.
5. Aggregates, CASE, scalar subqueries, NULLIF, SELECT VALUE / ROW with no rows: return null.
6. Outer joins: unmatched columns are null; with SELECT * and declared columns, the keys are present.
7. GROUP BY, ORDER BY, DISTINCT, join keys: one NULL, whichever of the two is in the data.
8. Unary operators and CAST: NULL in, NULL out.
9. Exports (CSV, TAB, XLSX, HTML, SQL): one rule for a NULL cell (empty, or NULL for INTO SQL).
10. Type declarations: nullable columns are T | null.
11. Go through the undefined tests in src/ and mark each as "optional argument" or "NULL".

What breaks for users

• Code that tests a result with === undefined, typeof x === 'undefined' or 'key' in row.
• Result rows gain keys that used to be absent after JSON.stringify.
• User functions and aggregates receive null where they received undefined for a NULL literal; default parameters no longer fire.
• Stored tables: rows written by v4 have missing keys, rows written by v5 have null. Reading both alike makes this harmless, but a saved database is not byte-identical.

First step: size it once

Before the work is scheduled, do items 1 to 3 on a throwaway branch and run the suite with strict assertions (assert.deepStrictEqual; the local branch refactor/deep-strict-test converts 1,541 assertions in 354 files and is green on develop). The number of failing assertions is the size of the change, and the list is the to-do list for the tests. Run test/performance/ on the same branch for the speed question from #85.

This is a measurement, done once. There is no option or switch for the value in v5: it is null. Today 107 of the 617 test files mention undefined; they are updated with the change.

Related

• Table with null values return undefined (implement NULL values) #85 Table with null values return undefined (implement NULL values), open since 2015
• Recover test 2000, 1977 and 1666 #2168 Recover test 2000, 1977 and 1666
• Issues with aggregate functions, date handling, null handling: GREATEST, LEAST, MAX, MIN, VAR, STDEV, VARP, QUART/2/3 #2481 Issues with aggregate functions, date handling, null handling
• fix: make COALESCE return null instead of undefined #2507 make COALESCE return null instead of undefined; fix: ISNULL() should fill in the replacement for NULL instead of behaving like NULLIF() #2561 ISNULL(); fix: INSERT without a column list stores NaN instead of NULL in numeric columns #2563 INSERT stores NaN instead of NULL (closed pull requests, to be redone)
• Fix for ROUND of NULL #2157 Fix for ROUND of NULL, where undefined was last confirmed as the representation
• Comprison on NULL differes from SQL #1414 Comparison on NULL differs from SQL (three-valued logic; separate)