How group handles columns

[richb-hanover]

This discussion started as #3490, and evolved over time. I moved it over to this discussion since the original issue has been closed. In the penultimate message, @max-sixty wrote:

And to confirm for future travelers — this [by-columns can't contain expressions] doesn't represent any loss functionality — it's always possible to add a column before the group.

To allay my concerns about another constraint [not being able to access by-columns within the group], I convinced myself (and hopefully future travelers) that...

There is no loss of functionality by not making the by-columns available within the group. The group transform will add them back to the front of its output relation, so those columns are "not lost". And there is no general reason to use them in the transforms within the group(): after all, the group transform is designed to operate on all the rows that have the same values of the by-columns. And finally, if the result relation must depend on one of the by-columns, it is always possible to add a column (using derive) as described above.

I still have more questions about group, and I'll add them to this discussion soon.

[richb-hanover]

In #3870, @aljazerzen wrote about this query:

from inv=invoices
join item=invoice_items (==invoice_id)

group { inv.billing_city } (

  group { item.name } (
    aggregate {
      ct1 = count inv.name,
    }
  )
)

this is not a compiler panic, but bad PRQL program.

I'm struggling a bit with the concepts of `group'. Help me understand what makes this query a "bad program" and what error indication we could provide. Thanks.

[aljazerzen]

Ohh, that does not come across as I thought it would: with "bad program" I meant an "invalid PRQL program", not "program of low quality".

In this specific case, it is not even "invalid PRQL", but "invalid PRQL for your database". That's because table invoice_items does not have the column name.

Otherwise, group is used correctly here.

To make it clearer, I would combine the two groups into a single one:

from inv=invoices
join item=invoice_items (==invoice_id)

group { inv.billing_city, item.name } (
  aggregate {
    ct1 = count inv.name,
  }
)

[richb-hanover]

The query shows two problems:

1. I agree that my database doesn't have a column name - that causes the Binder Error message.

2. More importantly, using the query above in main causes a panic. I added a screenshot in Compiler Panic with multiple group transforms #3870 (comment) to demonstrate what I see

[richb-hanover]

@max-sixty @aljazerzen The reason I'm perseverating about this is that I'm still trying to use group to answer my question from September in #3490, about "what's the most popular song title for each of the cities in the database?"

I came up this approach:

• group by city & country
• within each partition, group by song title (its name)
• within that partition, find the title with the top count

And created this query:

from inv=invoices
join item=invoice_items (==invoice_id)
join t=tracks (item.track_id==t.track_id)

group {inv.billing_city, inv.billing_country} (

  group {t.name} (
    aggregate {
    ct1 = count t.name,
    }
  sort {-ct1}
  take 1
  )
)

In the current (main) Playground, this query gives a compiler error, with additional diagnostic message of "pipeline expected type [{*..}], but found type func infer -> infer" (It used to say something about invalid lineage - see #3806)

What other diagnostic information can I provide? Thanks.

[richb-hanover]

Additional information: In #3490 (comment), @max-sixty offered a similar query without the nested group transforms.

In the current main Playground, that query also gives the same "pipeline expected type [{*..}], but found type func infer -> infer" diagnostic message.

from inv=invoices
join item=invoice_items (==invoice_id)
join t=tracks (item.track_id==t.track_id)

group {inv.billing_country, t.name} (
  aggregate (c=count t.name)
)

group {billing_country} (
  aggregate (
    sort (-c)
    take 1
  )
)