Throw error when aggregate functions are used with select

[Jelenkee]

There is no use for using aggregation with select. Therefore is should be not allowed

#2020 (reply in thread)

Edit: should an error be thrown with derive as well?

[max-sixty]

Edit: should an error be thrown with derive as well?

Yes!

[richb-hanover]

As a beginning PRQL and SQL student, I am not sure that I understand the importance of throwing an error in these situations.

Looking at the code cited in #2020 (below), I can see that the select and sort might be redundant but I don't see why they're wrong (and thus deserving of throwing an error.)

In fact, my intuition about PRQL is that people can "slap together" various transforms as their understanding of the query evolves. It feels as if it would make PRQL development harder to get error messages if I introduce a transform (say a select that happens to be redundant) into the middle of a pipeline that makes the entire pipeline easier to understand. Or adding a subsequent select or aggregate that "invalidates" a previous one.

Or do I misunderstand the argument...? Thanks.

# from artists-0.prql in the Playground...
from tracks
select [album_id, name, unit_price]
sort [-unit_price, name]
group album_id (
    aggregate [
    track_count = count,
    album_price = sum unit_price
    ]
)
...

@max-sixty @Jelenkee

[max-sixty]

I read through the discussion and it's not super clear, I should have written the actual case down.

Here is it:

    from Genre
    aggregate [
        x = Name,
        z = f"a {x} b' {GenreId} c",        
    ]

We're not doing any aggregations there — the shape of the result should be a single row, but it's actually still N rows.

Does that make more sense?

[richb-hanover]

I think I see - I was concerned that the discussion was about throwing an error if there is sub-optimal PRQL (redundant selects or sorts, as above.)

It appears now that the following PRQL (tweaked up to work in the Playground) generates multiple rows of output when in fact, aggregate should (by definition), generate only a single row.

Is that the nub of the question? If so:

• Is this a bug in PRQL compiler (generating SQL that outputs multiple rows)?
• Is this a syntax/semantic error that should throw before generating any SQL?

(Thanks again for accommodating my SQL newbie knowledge...)

from Genres
    aggregate [
        x = Name,
        z = f"a {x} b' {Genre_Id} c",        
    ]

[max-sixty]

Is that the nub of the question? If so:

Yes!

* Is this a bug in PRQL compiler (generating SQL that outputs multiple rows)?

* Is this a syntax/semantic error that should throw before generating any SQL?

I see it as a bug in that we should be checking that the type of the functions are col -> row, and raising an error if the functions are instead col -> col. But our typing is nascent, so we don't do this yet.

Does that answer the question?

[richb-hanover]

Yes. That answers my question. But it brings up another question about the notation you use above. I'll open another issue about it (see #2477)