Thinking about joins and PRQL naming

[richb-hanover]

@max-sixty I've been thinking about how we can explain PRQL to newcomers with a minimum of surprises. To follow the chain of logic of #2951, are the following correct?

• The reason PRQL raises the "Unknown name" error is that it doesn't know anything about the schema, nor about columns in any table. So PRQL requires the author to be explicit about which columns belong to which tables after a join. True?

• This is because tables being joined might have identically-named columns, and PRQL (and also SQL) need a way to keep them separate. True?

• This ambiguity does not occur with a simple select because a table must have uniquely-named columns. True?

• But if two tables get joined that have a column named "Col2", but that aren't part of the join criteria, the author needs to assign variable names to distinguish the two names. True?

  from TableA 
  join TableB (==Col1)
  select { foo=TableA.Col2, bar=TableB.Col2 }
  select { foo } # to get the variable from TableA (or "bar" from TableB)
  

• I note that in the example below, if there are no variables in the third line, PRQL seems to force TableB.Col2 as the result. True?

  from TableA 
  join TableB (==Col1)
  select { TableA.Col2, TableB.Col2 }
  select { Col2 }
  

Any other surprised to explain? Thanks!

[aljazerzen]

These are all true, nice summary of the problem.

[aljazerzen]

Regarding the note:

Current behavior of assigning column names:

• will assume name Col2 when selecting TableA.Col2,
• will override preceding column names - i.e. this select {a = x, a = y} will produce a relation with columns (unnamed, a),

These two rules culminate produce the behavior you observe: first select produces columns (unnamed, Col2) and the second select one picks up the second column because it is named Col2.