Queries that return nested tuples

[vanillajonathan]

What happened?

It compile a query but a is no where in the output. It probably shouldn't compile at all.

PRQL input

from foo
derive {
  a = {
    b = "b"
  },
}

SQL output

SELECT
  *,
  'b' AS b
FROM
  foo

-- Generated by PRQL compiler version:0.13.0 (https://prql-lang.org)

Expected SQL output

No response

MVCE confirmation

• Minimal example
• New issue

Anything else?

No response

[aljazerzen]

That's expected behavior, but we have to formally decided on it (I think).

The result type of your query is [{a = {b = text} }]. Because tuples are not a thing in all SQL dialects (and hard to work with even where they are supported), we flatten the resulting type into this [{b = text}].

A few more examples:

• [{a = {b = text, c = int}}] -> [{b = text, c = int}],
• [{a = int, b = {c = text}}] -> [{a = int, c = text}],
• [{a = {b = text}, c = {d = int}}] -> [{b = text, d = int}].

There is an alternative behavior that we could implement:

• [{a = {b = text} }] -> [{`a.b` = text}]

A bit more explicit.

I think that one of the changes on my branch is a change to this second behavior.

[dkratunov]

This is also very relevant in engines that support dictionary/map types.

For example, as far as I can tell, there's no way to work with a Map(String,String) column in Clickhouse when using the prql dialect.