Numeric type and decimal handling: harmonization, casts, config defaults

[neilotoole]

What this is

sq has started harmonizing how numeric results behave across SQL drivers, so the
same SLQ means the same thing on every source. Two pieces have already landed
(unreleased), and several follow-ups are in design. This thread lays out the
whole shape and asks for feedback on the open decisions before they are locked,
since some of them change output (breaking). Depth lives in the linked issues;
this is the map and the questions.

Already shipped (unreleased, breaking)

• avg() to float on every driver (avg() returns inconsistent types across drivers (no portable result type) #594). Previously a float on some
  drivers, a lossless decimal on Postgres/MySQL, and a truncated int on SQL Server
  (an actual bug). Now uniformly float64, matching jq's numeric model. Lossy past
  ~15-16 significant digits.
• sum() to decimal on every driver (sum() returns inconsistent types across drivers (int64 / decimal / float); float is the wrong target #839), for integer and decimal
  columns. A sum of exact values is itself exact, so float would regress
  precision. In JSON a decimal renders as a quoted string, so sum(.actor_id) is
  now "20100" rather than 20100.

So avg and sum deliberately default to different types: avg to float (always
fractional/approximate), sum to decimal (exact). That asymmetry is load-bearing
below.

The two axes

Numeric handling splits into two independent questions that earlier got
conflated:

1. Type of a result: float64 vs decimal.Decimal. Affects precision and
   the Go type that flows through.
2. Rendering of a decimal in typed output (JSON, YAML): a bare number
   (100.5) vs a quoted string ("100.5").

Proposed: one config option per axis, plus a per-query lever.

• result.numeric.type = auto | float | decimal (default auto): picks the
  type wherever sq has a float-vs-decimal choice (avg, sum, division, future
  stddev/sqrt). auto keeps the per-construct defaults; float/decimal force
  one everywhere. (Config option for numeric result type (result.numeric.type), global or per-source #837)
• format.decimal = string | number: picks how a decimal renders in JSON/YAML.
  (No ticket yet.)
• Portable cast functions decimal(x), int(x), float(x) in SLQ, to opt
  into a type per query without leaving SLQ: avg(decimal(.price)) averages in
  decimal (lossless). (SLQ: add portable cast functions (decimal(), int(), float(), ...) #836)

A correctness bug in the same family

• Division / returns different values per driver (SLQ division operator: integer vs decimal division differs across drivers (non-portable) #838). .actor_id / 8
  for actor_id = 58 is 7 on Postgres/SQLite/SQL Server/rqlite (integer
  truncation) and 7.25 on MySQL/ClickHouse/DuckDB/Oracle (verified live). Same
  SLQ, different answer. The fix normalizes the truncating drivers.
• On Oracle this currently manifests as a hard scan crash, tracked separately
  (oracle: non-integer NUMBER result from integer operands misclassified as int (scan crash) #844).

Open decisions (input wanted here)

1. format.decimal default: string or number? string is precision-safe
   (preserves sum() returns inconsistent types across drivers (int64 / decimal / float); float is the wrong target #839, safe beyond float64 range) but jq users dislike quoted
   numbers. number is jq-ergonomic but lossy on read for large values and
   reverts sum() returns inconsistent types across drivers (int64 / decimal / float); float is the wrong target #839's "20100". Which should be the default?
2. Division (SLQ division operator: integer vs decimal division differs across drivers (non-portable) #838): normalize, and to what? Should / always be
   non-truncating (so 58/8 is 7.25 everywhere)? And should the normalized
   result be float (jq-style) or decimal (lossless)? Current leaning: float.
3. Config key name. result.numeric.type is the current recommendation (the
   result.* namespace signals "changes the type," vs format.* which is
   display-only). Reactions? Terser alternative: result.number.
4. Cast surface (SLQ: add portable cast functions (decimal(), int(), float(), ...) #836). Unary functions decimal(x) / int(x) / float(x)
   (jq-idiomatic, and they compose for operand-vs-result casting). The SQL-style
   cast(x, type) and a :type suffix were rejected (the suffix collides with
   SLQ's alias syntax). Agree?
5. Release sequencing. Proposed: ship the aggregate harmonization (avg() returns inconsistent types across drivers (no portable result type) #594,
   sum() returns inconsistent types across drivers (int64 / decimal / float); float is the wrong target #839) plus the Oracle crash fix (oracle: non-integer NUMBER result from integer operands misclassified as int (scan crash) #844) next; do division (SLQ division operator: integer vs decimal division differs across drivers (non-portable) #838) either with it
   (one breaking wave) or as a fast follow; then a "controls" release for
   format.decimal, casts (SLQ: add portable cast functions (decimal(), int(), float(), ...) #836), and config (Config option for numeric result type (result.numeric.type), global or per-source #837). Should division ride with
   the aggregates or wait?

Heads up: these change output

#594 and #839 already change numeric output and are breaking; format.decimal
and the division normalization would too. If you consume sq's numeric output
(especially JSON), this is the moment to flag concerns.

Links

• avg() returns inconsistent types across drivers (no portable result type) #594 avg to float (shipped, unreleased)
• sum() returns inconsistent types across drivers (int64 / decimal / float); float is the wrong target #839 sum to decimal (shipped, unreleased)
• SLQ: add portable cast functions (decimal(), int(), float(), ...) #836 portable cast functions
• Config option for numeric result type (result.numeric.type), global or per-source #837 result.numeric.type config option
• Config option for decimal rendering (format.decimal = string|number) in JSON/YAML #846 format.decimal rendering option
• SLQ division operator: integer vs decimal division differs across drivers (non-portable) #838 division portability
• oracle: non-integer NUMBER result from integer operands misclassified as int (scan crash) #844 Oracle NUMBER scan crash

[neilotoole]

Full design rationale (the why behind each decision)

The opening post is the map. This comment is the reasoning behind the calls, for
anyone who wants the why.

Why two axes

"What type is a numeric result" and "how does a decimal render" are independent,
and conflating them is what made the earlier behavior inconsistent. Type
(float64 vs decimal.Decimal) affects precision and the Go value that flows
through sq. Rendering (bare 100.5 vs quoted "100.5") only affects
presentation in typed formats. Keeping them as two options means you can choose
lossless decimal internally and still control whether it surfaces as a JSON
number or string, rather than the type silently dictating the rendering.

Why avg stays float by default (the most-relitigated call)

The tempting move is to default avg to decimal (lossless, symmetric with
sum) and rely on format.decimal=number for jq-style output. It does not work
out, for three reasons:

1. Type currently does double duty. Today avg=float renders bare via the
   float path and sum=decimal renders string via the decimal path. The type
   difference is what gives them different default renderings for free. Collapse
   avg to decimal and both route through the same format.decimal switch, which
   cannot give two different defaults to two functions.
2. Only the current pairing is zero-config-correct. With format.decimal
   defaulting to string (precision-safe), avg=float gives a bare 100.5 and
   sum=decimal gives "20100", both right with no knobs. avg=decimal would
   render "100.5" and regress avg() returns inconsistent types across drivers (no portable result type) #594.
3. For a terminal avg, decimal buys nothing observable. sq does not compute
   aggregates itself; it pushes avg to the database, scans the single result,
   and formats it. There is no further in-process computation. Rendered bare, any
   consumer parses a JSON number as float64 anyway (so bare decimal == float on
   read); rendered string, precision survives but that is the non-jq path. So
   avg=decimal + format.decimal=number delivers exactly what avg=float already
   delivers, at a heavier type. (Caveat: if an aggregate is embedded in a larger
   expression that the DB computes further on, the type does matter before sq sees
   it; that is what the explicit cast and result.numeric.type=decimal are for.)

So the decimal-avg instinct is right as an opt-in (cast or config), not as the
default.

Why result.numeric.type is three-valued (auto)

The shipped defaults are asymmetric on purpose: avg float, sum decimal,
division (proposed) float. A single scalar default cannot reproduce that:
float would undo #839, decimal would undo #594. auto keeps the
per-construct defaults; float/decimal override them uniformly.

What the type knob actually promises (the contract)

result.numeric.type controls the type and precision of a result, not where the
SQL CAST goes. For decimal it means lossless where the engine can compute it
exactly, achieved per driver by operand-casting (computing the aggregate in
decimal) or by leaving the native exact result intact, not by casting an
already-computed float (which would only re-type a lossy value). The cast site
legitimately varies: SQL Server must operand-cast where Postgres can result-cast.

format.decimal: scope and the XLSX exception (#846)

The number-vs-string distinction only exists in typed formats. Verified current
behavior: JSON always quotes a decimal, YAML always emits it bare, XLSX is
per-value adaptive (native number when it fits float64 exactly, string cell
otherwise). Three different policies today; format.decimal standardizes the
streaming text formats (JSON, YAML). XLSX stays adaptive and is deliberately not
governed by the knob: per-value adaptive is safe in Excel's individually-typed
cells but a footgun in JSON/YAML (a mixed 100.5 / "100.5" column breaks
jq '.[].x + 1'), and forcing XLSX onto string/number could only degrade it.

The cast is the foundational primitive (#836)

The existing avg/sum overrides are already hardcoded casts. Seen that way:
the harmonization is a per-driver default implicit cast, result.numeric.type is
which implicit cast to inject, division normalization is an operand cast on the
truncating drivers, and #836 is the user-written explicit cast that overrides all
of them. Two consequences: an explicit cast in a query must suppress the implicit
harmonization cast (else avg(decimal(.price)) double-casts and re-lossies), and
it argues for building the cast engine first with the rest layered on top.

Per-driver wrinkle: DuckDB sum()

Under auto, DuckDB's sum() over a DOUBLE column stays float, the one
exception to "sum is decimal everywhere." DuckDB's native int (HUGEINT) and
decimal sums are already lossless, and the override is operand-blind, so a
DECIMAL(38, 6) cast would also hit those and regress them. The exception lives
only in auto; decimal would cast that case, float would force the others.

Verified division behavior (#838)

.actor | (.actor_id / 8), run live against the sakila sources:

Driver	actor_id=1	actor_id=58	Semantics
Postgres	0	7	integer, truncating
SQLite	0	7	integer, truncating
SQL Server	0	7	integer, truncating
rqlite	0	7	integer, truncating
MySQL	0.1250	7.2500	decimal
ClickHouse	0.125	7.25	float, non-truncating
DuckDB	0.125	7.25	float, non-truncating
Oracle	0.125	7.25	NUMBER, non-truncating (but sq currently errors at scan, #844)

[neilotoole]

Filed #853 to track one concrete outlier from this discussion: sum() surfaces as decimal on every driver except duckdb, where a sum over a DOUBLE/FLOAT column still comes back as float. Every other driver either casts (postgres/mysql/oracle/clickhouse/sqlserver) or kind-pins (sqlite3/rqlite) the result to decimal.

The issue includes an implementation sketch: the fix is the kind-pin path already used by sqlite3/rqlite (not a SQL cast, which would regress DuckDB's native HUGEINT integer range), plus a small coercion in DuckDB's value munge, which normalizes by raw Go type rather than by declared kind. It carries the same float-drift tradeoff already accepted for sqlite3.

Closing it would make the sum result kind uniformly decimal across all drivers, with no DuckDB asterisk.

UPDATE: #853 has been merged.