Relative names

[aljazerzen]

Abstract:
I propose to change references to columns from column to .column.

Reasoning:
I'll try to explain how resolver works and how I think about semantics of name and variables in PRQL.

During resolving, there is a major distinction between scoped and ephemeral variables:

• Scoped variables have a definition and live until their scope exists. For example, std.sum and std.select are global so they exist indefinitely, and function parameters exist only within function body.
• Ephemeral variables are just references into some other argument of a current function call. For example, when you call select, all columns of the relation exist as variables during resolution of the first argument.

It is beneficial to distinguish these two mechanism, because of their subtle differences. For example take this query:

func my_transform rel -> (
    rel
    select [alb.title, artist_id]
)

from alb = albums
my_transfrom

Here, relation is constructed with from and within the relation a name alb is assigned all column from table albums. Note that alb is not a "real" value, it's just a namespace for the columns. When this relation is passed to my_transform, it is stored in the rel parameter. rel is now a scoped variable while alb.title is a reference to one of its columns.

I'm not sure if I've explained that well, please tell me if I haven't.

If I compare this behavior with, say, Python and a dataframe library, scoped variables are all normal idents, while ephemeral variables would be represented with strings. This is a bit more verbose and cannot provide good errors, typing or autocomplete. (This is feature of PRQL that dataframe libraries cannot copy. Only a custom language for relations can construct custom rules for name resolution.)

So because there is distinction in resolving, I suggest we add a distinction in syntax:

func my_transform rel -> (
    rel
    select [.alb.title, .artist_id]
)

from alb = albums
my_transfrom
sort .title

Pros:

• distinction in syntax hints to the distinction in resolving
• for newcomers, the rule is simple: columns start with a dot

Cons:

• additional syntax we could be without

[snth]

I quite like the idea of a leading . for columns. I don't really know why yet but it feels like it would bring additional consistency. It also reminds me of JDOT (https://github.com/saulpw/jdot).

TBH, I did not understand the name resolution explanation yet but I will try again in the morning (it's close to midnight now). For example why is it alb.title in my_transform initially and not rel.title? And with the new syntax, why is it still .alb.title and not just .title (or if the alb is required then alb.title)?

Another possible benefit could be that it might disambiguate a column named "from" from the keyword from since the column would be referred to as .from. (IDK if this is currently a problem for the parser/compiler.)

[eitsupi]

It also reminds me of JDOT (https://github.com/saulpw/jdot).

Perhaps the origin is jq?
https://stedolan.github.io/jq/

I think jq is a very popular language for writing queries to json.

[max-sixty]

Sorry to take a while to respond.

I think I'm understanding 85% of this,so forgive me if I'm slow.

I can see two points here;

• discriminate between scoped and emphermal variables
• use .foo for some variables

Re the discriminaring — how easy do you think it is to explain when to use a period vs. not to? I worry it's not easy! (But possibly we could make it easier).

Re the periods — I don't have a strong secular objection to it. It would be a big change, and I'm not sure it gets us that much apart from the discrimination. But it is an effective way of allowing columns to be clearly different from functions.

---

To what extent do you think it's accurate to describe emphermal variables as just having a scope that's limited to that line?

[aljazerzen]

It just one point here: use .foo for ephemeral variables.

The rule for when to use the dot is simple: columns start with a dot.

---

describe emphermal variables as just having a scope that's limited to that line?

That's pretty accurate. But it may be confusing because even though the scope is limited to current function, almost identical scope could be created for next function in the pipeline.

[max-sixty]

It just one point here: use .foo for ephemeral variables.

Totally, but is there an easy way to define ephemeral variables to beginners?

[aljazerzen]

I'm saying that for beginners, ephemeral variables can be equivalent to columns. So the whole rule is columns start with a dot. And we don't even mention ephemeral variables.

That's because we don't have anything other than relations that we'd want to have references into. Maybe in the future, we could add support for referencing properties of JSON objects or structs.

[max-sixty]

Yes OK, that is complete in the examples above.

How about when it's a variable; for example:

func add a b -> a + b
# or
func add a b -> .a + .b
# or
func add .a .b -> .a + .b

Thanks for bearing with me...

[aljazerzen]

Oh, params are scoped variables so they don't need a leading dot. So like this:

func add a b -> a + b

func latest n rel -> (rel | sort [-.changed_at] | take n)

# rel and n are params -> scoped -> no dot
# .changed_at is a column (reference "into" rel) -> ephemeral variable -> dot 

[max-sixty]

OK great, I see, thanks.

I think it's tractable. I don't think it's that friendly, and it's much more alien for those who are used to SQL.

Do others share a concern that represents hierarchies inconsistently? For example alb is a relation. But to go into that hierarchy involves adding a period at its start; i.e. .alb.title. Generally to move down a hierarchy we'd only add things onto the end like alb.title or alb["title"]

---

I think this is insightful, and maybe we should discuss it more in our docs...

If I compare this behavior with, say, Python and a dataframe library, scoped variables are all normal idents, while ephemeral variables would be represented with strings. This is a bit more verbose and cannot provide good errors, typing or autocomplete. (This is feature of PRQL that dataframe libraries cannot copy. Only a custom language for relations can construct custom rules for name resolution.)

....I've heard this referred to as "bare words". I find it a great advantage of PRQL over something like python. It makes sense that we promote columns to not require quotes, since columns are so important in tabular data; they're almost like variables to us.

As @eitsupi points out, jq uses the .foo syntax, and that's worked well, though they use it all the way down the hierachy; i.e. .alb, never just alb.

---

So my current view is:

• Has some nice properties
• Concern about friendliness / alien-ness (but shouldn't be weighed highly unless this is a consensus view)
• Concern about hierarchies

How important do you think it is for the development of the lang? Can we instead have a hierarchy of scopes (like many langs do), and resolve ephemeral variables first, and scoped variable after that?

[eitsupi]

I recall that in dplyr, it is sometimes difficult to distinguish between variables outside the data frame and column names in the data frame, making the behavior confusing.

cyl <- 10

mtcars |>
  dplyr::mutate(new = cyl * 10)

It can be specified explicitly by .data or .env (but many people rarely do this because it increases the amount of writing).
https://rlang.r-lib.org/reference/dot-data.html

cyl <- 10

mtcars |>
  dplyr::mutate(new = .data$cyl * 10)

I think it is a good balance of clarity and ease of writing to always start column names with a dot.

[aljazerzen]

I've implemented the proposal and converted the tests in prql-compiler.

Here are a few examples:

from daily_orders
sort .day
group .month (sort .num_orders | window expanding:true (derive rank))
derive [num_orders_last_week = lag 7 .num_orders]

from employees
derive rn = row_number
filter .rn > 2

from employees
derive age = .year_born - s'now()'
select [
    f"Hello my name is {.prefix}{.first_name} {.last_name}",
    f"and I am {.age} years old."
]

from employees
derive count = 12
select [
    twelve = .count,
    aggregated = count,
    aggregated_verbose = std.count,
]

Here is my findings:

• this syntax is more verbose and less beginner-friendly than what we had before,
• it simplifies the implementation a bit,
• in some cases it is less ambiguous (see last example),
• it would be nice for auto-complete, since typing . would bring up just columns for current relation,
• there is a bit of inconsistency where we derive new names without the dot, but reference them with the dot,
• we can now use .* to refer to all columns of the relation, where before we could not use * (since that would be parsed as multiplication).

Possible alternatives:

• the leading dot is not required, but just encouraged,
• the special leading dot syntax is replaced with a full name, like rel.first_name instead of just .first_name. In this case, rel. prefix would also be optional.