PRQL and SQL Order of Execution

[whoarusty]

What I like about PRQL is that it closely resembles SQL's Order of Execution:

PRQL                SQL Order of Execution
from                FROM
join                JOIN
filter              WHERE
group               GROUP BY
filter              HAVING
select              SELECT
group and take 1    DISTINCT
sort                ORDER BY
take                LIMIT

I've tried to search and see if this was by design or a goal of PRQL ... but I couldn't find this information. Can anyone specifically confirm this?

I've been thinking about how to craft a beginner's guide to PRQL and wanted to use the idea of SQL's Order Of Execution. Would this also apply to PRQL? Or in other words ... is there a PRQL Order Of Execution?

I was hoping the idea of a beginner's guide is to formulate a process of crafting queries beyond just learning the specific commands of PRQL. Any thoughts?

[max-sixty]

That's a good list! Somewhat similar to https://prql-lang.org/book/transforms/index.html, but includes DISTINCT, which maybe we should add.

To respond to your question quickly, though not fully — I think about the "order of execution" as something PRQL "evades" by taking whatever the query contains. So the order of execution is the just the order in which the transform appear. Does that resonate at all?

Beginners' guide would be great! Whatever you think is missing now — you're probably the best judge of that...

[whoarusty]

Thanks for letting me know your thoughts.

From the link you provided, the idea of transforms is referred to as orthogonality -- "change just one thing without affecting others"

So the order of execution is the just the order in which the transform appear.

But it seems like the from transform is required first, right?

Using the following example:

>prqlc compile
Enter PRQL, then press ctrl-z to compile:

from employees
select salary

SELECT
  salary
FROM
  employees

However, if I swap the two transforms, PRQL seems to error:

>prqlc compile
Enter PRQL, then press ctrl-z to compile:

select salary
from employees

Error:
   ╭─[Root.prql:1:1]
   │
 1 │ ╭─▶ select salary
 2 │ ├─▶ from employees
   │ │
   │ ╰───────────────────── main expected type `relation`, but found type `infer -> infer`
   │
   │     Help: Have you forgotten an argument to function main?
   │
   │     Note: Type `relation` expands to `[tuple_of_scalars]`

I know you and the other developers are working hard at getting 0.9.0 out. So I'm only asking this as a reference to understanding PRQL in context of my original question "is there a PRQL Order Of Execution" and this can be tabled until another time if needed.

[max-sixty]

Yes — select salary alone doesn't have anything to select.

The select in from employees | select salary operates on employees, but in the former case it doesn't have anything to operate on. That's what I mean by "the order in which the transform appear"

Does that make sense?

(That error message is very bad! We should improve that...)

[whoarusty]

Yes, it makes sense to me ... as the PRQL philosophy and simplicity really resonates with me.

Where I'm coming from ... many SQL websites (especially the ones for beginners) don't mention SQL's Order Of Execution. Since SQL queries need the SELECT keyword first, I could see someone trying out PRQL trying select salary | from employees and not knowing why it doesn't work.

Personally, I think PRQL will be great for both beginners and for advanced SQL users. Because I'm just starting out with PRQL, I'm trying to document my experiences in a way that will benefit new / beginner database query coders. Today I created a Learn PRQL repo with this in mind -- it's just a shell of my ideas, but I hope to expand on the topic of execution order and then get into the "tutorial" part. I hope to complement the PRQL Book with my tutorial(s).

[max-sixty]

@whoarusty that looks awesome! Keep us in touch with your progress there; let us know what we can do to help