DuckDB window function returns tuple instead of value

[angrytongan]

Not exactly sure where the problem is, so trying here first.

When running a DuckDB window function, the return value of the function is a tuple in Observable Framework:

WITH
    data AS (
        SELECT 1 AS val
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        ORDER BY val
    )

SELECT
    val,
    sum(val) OVER (
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative
FROM
    data
ORDER BY
    val

This query yields:

Casting inside the query does return the right value:

WITH
    data AS (
        SELECT 1 AS val
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        ORDER BY val
    )

SELECT
    val,
    CAST(sum(val) OVER (
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS INTEGER) AS cumulative
FROM
    data
ORDER BY
    val

Not sure if it's DuckDB or Observable Framework. Would be good if someone could point me in the right direction 👍

[df-rw]

Running the (non-cast) query using the duckdb CLI gives:

duckdb foo.db
v1.1.1 af39bd0dcf
Enter ".help" for usage hints.
D WITH
      data AS (
          SELECT 1 AS val
          UNION ALL SELECT 2
          UNION ALL SELECT 3
          UNION ALL SELECT 4
          UNION ALL SELECT 5
          ORDER BY val
      )

  SELECT
      val,
      sum(val) OVER (
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS cumulative
  FROM
      data
  ORDER BY
      val
  ;
┌───────┬────────────┐
│  val  │ cumulative │
│ int32 │   int128   │
├───────┼────────────┤
│     1 │          1 │
│     2 │          3 │
│     3 │          6 │
│     4 │         10 │
│     5 │         15 │
└───────┴────────────┘

The output type is a HUGEINT (docs) which turns into a tuple in Observable Framework. Should types be cast in queries, or is this something Observable Framework should handle?

[mythmon]

I'd recommend casting in the query to an appropriately sized integer type, since sum will always return HUGEINTs. It wouldn't be correct for DuckDB to automatically cast those to a smaller numeric type.

If you need the extra range of HUGEINT, you could use the title directly and convert it into a JS BigNum, but that would require extra work from what I understand.

[df-rw]

Thanks Michael 👍

[mbostock]

Yep, this is how Apache Arrow represents large integers. A trivial reproduction of this is:

```sql id=[{n}]
SELECT 11111111100::INT128 AS n
```

This defines n as a Arrow.util.BN instance in the same fashion as if you had written:

const n = new Arrow.util.BN(new Uint16Array([0x19bc, 0x9646, 0x0002, 0, 0, 0, 0, 0]));

In order to work with this value as a number, you’ll need to cast it to a number. You can do that in JavaScript as:

Number(n)

You can also implicitly cast it to a number e.g. as +n.

Alternatively, if you want a bigint, you can go through a string (unfortunately it does not appear that you can coerce directly to BigInt with the current version of Apache Arrow):

BigInt(String(n))

Inputs.table doesn’t know about the BN class and thus displays its structure as-is rather than casting it to a primitive. As @mythmon said, you can also cast it to a DOUBLE in SQL:

SELECT (11111111100::INT128)::DOUBLE AS n

Or, if you know that the value will fit in 32 bits, you could use INT:

SELECT (1234::INT128)::INT AS n

There was previously a bug https://github.com/apache/arrow/issues/37920 in how Apache Arrow converted decimals to numbers, but that appears to be fixed now. There’s also duckdb/duckdb-wasm#1703 in DuckDB-Wasm.

Lastly I’ll note that DuckDB-Wasm has a castBigIntToDouble query option, which we implicitly default to true in DuckDBClient:

framework/src/client/stdlib/duckdb.js

Lines 168 to 170 in e8e590c

	if (config.query?.castBigIntToDouble === undefined) {
	config = {...config, query: {...config.query, castBigIntToDouble: true}};
	}

However, it seems that this doesn’t really function as desired, perhaps because Apache Arrow is returning a BN instance instead of a native BigInt. So in short, ideally Apache Arrow or DuckDB-Wasm would do this conversion automatically, but they don’t, so currently you’ll have to do it.

[df-rw]

Thanks Mike 👍