One alternative would be to use serde serialization of the schema (so someone on the other side can get a proper arrow object), though I suspect that may end up being too convoluted.

Is there a name for this format of Type(Params)? Just wondering how a client might parse it. It looks almost like JSON but not quite.

I don't think so, it's really only a serialization of a recursive type enum variant DataType::List(Box<Field>).

This however sort of breaks the abstraction, since we serialize only the name of the (root) Field on the left, but on the right the entire (nested) Field struct gets unwrapped.

Yeah, I guess the more human-readable verison would be Int64[]. Would you lose any information if you forced it to be like that? I guess nullable is important.

Related: What would be the type of a scalar column that is nullable? Is it even possible to produce such a query?

We could also serialize it into SQL datatypes (TEXT instead of Utf8, BIGINT[] instead of List(...Int64...)), though I don't remember if we even support lists at the DDL level. There was some code that serializes column types into the database in the arrow-native JSON format if that didn't go away with the delta-rs migration (EDIT: here it is,

Something like this (Splitgraph query):

SELECT
    "foo",
    "baz",
CASE
    WHEN baz % 2 = 0 THEN null
    WHEN baz % 2 <> 0 THEN 'ok'
END isbaz
FROM
    "miles/big-random-csv:7700078ffc7592e8ad029070bcdccaafb03500b5f32be52f7ba73a5b93b95afd"."file_1"
LIMIT 100;

What would be the type of isbaz column?

I was able to construct such a query on my Seafowl test DB:

curl -i \
    -H "Content-Type: application/json" \
https://splitgraph-testing-nomad.seafowl.cloud/q -d@-<<EOF
{"query": "SELECT name, value, CASE when value > 2 THEN null WHEN value < 2 THEN 'ok' END islessthantwo FROM test_cache;"}
EOF

which returns the rows with nullable results as undefined (this is expected):

HTTP/2 200 
content-type: application/octet-stream
vary: Content-Type, Origin, X-Seafowl-Query
content-length: 386
date: Thu, 20 Apr 2023 14:54:11 GMT
server: Fly/ad79467d (2023-04-14)
via: 2 fly.io
fly-request-id: 01GYFJH6RQ0132ZX4000EK6RSY-lhr

{"name":"Alpha","value":2.3}
{"name":"Alpha","value":2.4}
{"name":"Beta","value":9.3}
{"name":"Beta","value":4.0}
{"name":"Gamma","value":6.8}
{"islessthantwo":"ok","name":"Gamma","value":1.2}
{"name":"Alpha","value":2.3}
{"name":"Alpha","value":2.4}
{"name":"Beta","value":9.3}
{"name":"Beta","value":4.0}
{"name":"Gamma","value":6.8}
{"islessthantwo":"ok","name":"Gamma","value":1.2}

So what would we expect the column type of islessthantwo to be in the header?

What would be the type of a scalar column that is nullable? Is it even possible to produce such a query?

Nullability is being tracked at the Field level, not the data type level, so in the case of a scalar column that is nullable there would be no info about it (as we'd record only the field name).

There was some code that serializes column types into the database in the arrow-native JSON format if that didn't go away with the delta-rs migration.

That's still there, though it may be too verbose.

We could also serialize it into SQL datatypes (TEXT instead of Utf8, BIGINT[] instead of List(...Int64...))

I think this is the best approach overall, as it also keeps the output implementation agnostic, i.e. SQL in SQL out. I'll try to find a way to make this work.

We could also (which might be overkill) emit a JSONSchema instead of rolling our own custom format, which supports dates and floating point numbers. Could be useful for clients autogenerating parsers downstream.

It would definitely be nice to include nullability in this response header, even if it's technically part of field.nullable rather than field.type.

This header will be a pain to parse by the client:

• need to split the header value on the semicolon and the equals sign
• the parameterized DF types like List(...) or Timestamp(...) are awkward to parse
• I'm worried about how this encodes column names with spaces or Unicode characters (EDIT: or characters like the equals sign or something that's not allowed in headers)

Maybe an alternative could be having a single value in the header like

application/json; seafowl-schema=[JSON encoding of the schema, uriencoded so that it's header-safe]

?

It's not a pain to parse on the client, because you can just .split() on ; to get each parameter, and then for each parameter, split on = to get the (key,value). The part that is a pain to parse is the types themselves, but that comes down to what we actually want to do with them.

However, I do agree that a JSON encoding of the schema would be easier to parse. It still doesn't solve the issue of what to call (how to interpret) the actual types like List(...) though.

you can just .split() on ; to get each parameter, and then for each parameter, split on = to get the (key,value)

what if my column name contains a semicolon? 🙃

It still doesn't solve the issue of what to call (how to interpret) the actual types like List(...) though.

The JSON Arrow serialization I suggested (from

what if my column name contains a semicolon? 🙃

I'm not sure this would be a valid HTTP parameter name, even if it was escaped. :/

EDIT: Actually, I think it's valid as long as the parameter value is surrounded by quotes.

I didn't find any function/crate for converting Arrow type back to SQL-looking types, so I'd have to make a custom converter. In principle this would be fine for simple types, but for nested and recursive types it would get ugly. Plus, we'd still lose the nullability information regardless.

That's why I went ahead with using the arrow schema JSON serialization format now. Note that this is not an official format, it's just something that is used in integration testing, but is consistent. (Also note that I haven't employed url encoding for now.)

The returned schema is very verbose (check out the test assertions), though in theory analytical queries employ a smaller column cardinality (plus we're not "polluting" the actual result output as the schema info is hidden in the header). This could still pose a problem in terms of header size limits; as per Cloudflare: Request headers observe a total limit of 32 KB, but each header is limited to 16 KB. Maybe we could make the schema type info in the headers optional, so that for some very large output column cardinality people can disable it.

EDIT: In the example of that large type conversion test, the entire content-type header text is 1.73KB, which seems comfortably within the size limits (though other clients/services may have different limits). We could also make this typo info be opt-in (controlled via a query param, some default request header or some custom one), so that by default we don't do it?

For the record, I've further modified this approach in order to url-encode the schema when sending via content-type header parameter, as discussed offline.

We could also serialize it into SQL datatypes (TEXT instead of Utf8, BIGINT[] instead of List(...Int64...)), though I don't remember if we even support lists at the DDL level. There was some code that serializes column types into the database in the arrow-native JSON format if that didn't go away with the delta-rs migration (EDIT: here it is,

minor: unwrap_or_else could work here (no idea if you tried it; I haven't tested this code)

I think we should still call it arrow-schema, even though it's percent encoded.

That's fine with me. @milesrichardson just an fyi that I'm revising the parameter name back to arrow-schema.