4 ms·

Here's one suggestion: SQL tediously requires specifying the equality condition on joins, when 90% of the time you just want to join on the fk defined between t

by paol 4y ago

Here's one suggestion: SQL tediously requires specifying the equality condition on joins, when 90% of the time you just want to join on the fk defined between the tables. from a join b should implicitly join on the FK if no condition is given. It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.

aidos 4y ago
This is something you might end up regretting later. It’s annoying adding another foreign key later and then having previously working queries fail at runtime due to an ambiguous join condition.
- paulhodge 4y ago
  Agree about not implicitly finding the join key. But as long as we're brainstorming imaginary features, then maybe as part of the schema, we could somehow declare the default join key to use, for any given two tables. In most cases it's pretty obvious what the best join key would be.
aerzen 4y ago
Hello another contributor here! Compilation does have to be stateless (for performance reasons), but we are planning to add some kind of schema definitions which could also specify foreign keys. So joins without conditions would be possible, we'll look into it! What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an intermediate table?
- JohnDeHope 4y ago
  "What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an intermediate table?" If it's not ambiguous, then let me do it. If I rely on ambiguity then throw an exception. In the case of multiple foreign keys, throw an exception, as there's no way to know which one I mean. It'd be nice if I could disambiguate the situation though. Normal SQL allows the `on` clause. from TableA inner join TableB on <expression> What if I could specify a foreign key constraint just as easily... from TableA inner join TableB by ConstraintC Where ConstraintC is the name of a foreign key constraint between Table A and Table B. It'd be nice to specify the constraint without having to specify the column name details. The same goes for the many to many relationship with an intermediate table. It could look something like this... from TableA inner join TableB through TableC I wouldn't introduce TableC into the scope of the statement. It's not in the FROM clause. It's used in the query but is not available for selecting from. If you want to bring in columns from it, join on it the usual way. As applications grow, and initially simple lookup table semantics get more nuanced, it might be nice to be able to constrain the join on the lookup table like this... from TableA inner join TableB through TableC where <expression> That way if my TableC has some extra columns, such as effective dates, or deleted flags, or that sort of thing, then I can filter out some of the joins that might usually happen.
  - kbenson 4y ago
    Unambiguous things can become ambiguous at later points. As soon as you add a second relation between the tables, what once was unambiguous now is, and because of something which may be entirely unrelated to the specifics of the original query. This is where many conveniences that use implicit data run into problems. A small convenience now for the possibility of accidentally breaking because of mostly unrelated changes later is a poor trade off for anyone that wants to have stable and consistent software. This is likely one of those cases where you're better off with tooling to help make writing the correct unambiguous code easier (or automated away) than introducing a feature which leads to less stable systems in some cases. Edit: Along the lines of what you note at the end, I would rather see joins able to use named relations as defined in the schema. Of there's a relation from table movie to table actor specifically names roles in the schema, I would rather be able to join movie on roles and have actors joined correctly using that relation, and aliases to roles which I could then use. Then you're using features that are designed and stable and not implicit and subject to changing how or whether they function based on semi-unrelated changes. That might look like: "from movie relate roles" which is equivalent to "from movie join actor roles on movie.id = roles.movie_id", but because actor.movie_id has a constraint in the schema named roles which restricts it to a movie.id already.
    - strbean 4y ago
      That sounds like the perfect solution!
    - asqueella 4y ago
      Agreed! But rather than making the query compiler infer join paths from the schema, wouldn't it make more sense to support defining common join paths (like your 'movie relate roles') in the language, and build a tool, that generates such definitions from the schema, as a separate step? I don't have a specific syntax in mind yet; for illustrative purposes: defjoin r,m,a = %prejoin_roles() -> { # define a common join path between three relations r,m,a: from r=ROLES # can hard-code table names or use parameters (which may refer to other parameters) join m=MOVIES [r.movie_id = m.movie_id] join a=ACTORS [r.actor_id = a.actor_id] } from r,m,a = %prejoin_roles() select m.title, a.character_name This `defjoin` thing is a limited version of PRQL `table`, which -- unlike a CTE -- remembers which relation each attribute comes from. Perhaps one can instead figure out how to extend `table` to support this.
- blep_ 4y ago
  If I may suggest an entirely different direction (that also requires either schema knowledge or adherence to a convention), I've always been mildly annoyed that foreign keys are kind of like pointers that don't behave like pointers. What if: create table users (id int primary key, name text); create table things (id int primary key, creator int references users); from things select [id, creator.name];
- Aeolun 4y ago
  > What do you think should happen if there are multiple foreign keys connecting the two tables? Compilation should fail and require you to explicitly specify what key to use. Please don’t do anything magic.
  - setr 4y ago
    You can’t add an additional foreign key to an existing table without potentially breaking all existing queries. Probably the biggest constraint SQL language design has is that its on a live system — things are not compiled at the same time.
smallnamespace 4y ago
If you're willing to sacrifice economics elsewhere, repeating the table's name in the id column is one workaround: from a join b on b.a_id = a.a_id You can even use NATURAL JOIN if you can guarantee that the only fkey/pkey names will overlap between tables. An unreasonable way to achieve that is to put the table name in every column. A more palatable way is to write some clever functions in your schema to scan the information table look for column name clashes (you essentially write a tiny "linter" inside your schema).
- hyperman1 4y ago
  If you have identical field names, you can do in sql: Select * from a join b using (a_id) Don't do this in Oracle though, pain follows when you try to touch an a_id column.
  - snthpy 4y ago
    You can also do this in PRQL: from a join b [a_id] is the equivalent query.
CuriousSkeptic 4y ago
SQL has that actually select * from a natural join b (not based on fk constraints though, it will join on all attributes with the same name in the relations)
go_prodev 4y ago
I agree with you that it's a pain writing join conditions with many fields... But I think that's a shortcoming of the client tool, rather than the language. If SQL tools auto completed the join conditions as best as they could it would probably be a great help.
sgk284 4y ago
The risk here is that if one table has two fks to another table, the syntax becomes ambiguous. And the number of fks two tables have to each other may change over time. This means that an append-only change to a table may break existing queries that have no knowledge of the new column. SQL addresses this via the natural join keyword `using`, where you enumerate the common columns between the two tables being joined. It isn't too convenient for your example unless your pk naming pattern happens to be `<entity>_id` instead of just `id` (note: this naming pattern has all sorts of other adverse consequences though). But it does provide convenience in some cases without introducing backwards compatibility risks as the schema evolves.
dragonwriter 4y ago
The problem is when one has multiple FKs between the same pair of tables. Of course, if you allow naming the relation when you create a foreign key, then you could use the source table-qualified relationship name for joins rather than the target table name, which would be unambiguous (and more communicative of intent). E.g., for a hypothetical table with two self-fks: FROM employees ee INNER JOIN ee.manager mgr INNER JOIN ee.team_lead lead

by paol 4y ago

aidos 4y ago

This is something you might end up regretting later. It’s annoying adding another foreign key later and then having previously working queries fail at runtime due to an ambiguous join condition.

paulhodge 4y ago
Agree about not implicitly finding the join key. But as long as we're brainstorming imaginary features, then maybe as part of the schema, we could somehow declare the default join key to use, for any given two tables. In most cases it's pretty obvious what the best join key would be.

aerzen 4y ago

Hello another contributor here! Compilation does have to be stateless (for performance reasons), but we are planning to add some kind of schema definitions which could also specify foreign keys. So joins without conditions would be possible, we'll look into it! What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an intermediate table?

JohnDeHope 4y ago
"What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an intermediate table?" If it's not ambiguous, then let me do it. If I rely on ambiguity then throw an exception. In the case of multiple foreign keys, throw an exception, as there's no way to know which one I mean. It'd be nice if I could disambiguate the situation though. Normal SQL allows the `on` clause. from TableA inner join TableB on <expression> What if I could specify a foreign key constraint just as easily... from TableA inner join TableB by ConstraintC Where ConstraintC is the name of a foreign key constraint between Table A and Table B. It'd be nice to specify the constraint without having to specify the column name details. The same goes for the many to many relationship with an intermediate table. It could look something like this... from TableA inner join TableB through TableC I wouldn't introduce TableC into the scope of the statement. It's not in the FROM clause. It's used in the query but is not available for selecting from. If you want to bring in columns from it, join on it the usual way. As applications grow, and initially simple lookup table semantics get more nuanced, it might be nice to be able to constrain the join on the lookup table like this... from TableA inner join TableB through TableC where <expression> That way if my TableC has some extra columns, such as effective dates, or deleted flags, or that sort of thing, then I can filter out some of the joins that might usually happen.
- kbenson 4y ago
  Unambiguous things can become ambiguous at later points. As soon as you add a second relation between the tables, what once was unambiguous now is, and because of something which may be entirely unrelated to the specifics of the original query. This is where many conveniences that use implicit data run into problems. A small convenience now for the possibility of accidentally breaking because of mostly unrelated changes later is a poor trade off for anyone that wants to have stable and consistent software. This is likely one of those cases where you're better off with tooling to help make writing the correct unambiguous code easier (or automated away) than introducing a feature which leads to less stable systems in some cases. Edit: Along the lines of what you note at the end, I would rather see joins able to use named relations as defined in the schema. Of there's a relation from table movie to table actor specifically names roles in the schema, I would rather be able to join movie on roles and have actors joined correctly using that relation, and aliases to roles which I could then use. Then you're using features that are designed and stable and not implicit and subject to changing how or whether they function based on semi-unrelated changes. That might look like: "from movie relate roles" which is equivalent to "from movie join actor roles on movie.id = roles.movie_id", but because actor.movie_id has a constraint in the schema named roles which restricts it to a movie.id already.
  - strbean 4y ago
    That sounds like the perfect solution!
  - asqueella 4y ago
    Agreed! But rather than making the query compiler infer join paths from the schema, wouldn't it make more sense to support defining common join paths (like your 'movie relate roles') in the language, and build a tool, that generates such definitions from the schema, as a separate step? I don't have a specific syntax in mind yet; for illustrative purposes: defjoin r,m,a = %prejoin_roles() -> { # define a common join path between three relations r,m,a: from r=ROLES # can hard-code table names or use parameters (which may refer to other parameters) join m=MOVIES [r.movie_id = m.movie_id] join a=ACTORS [r.actor_id = a.actor_id] } from r,m,a = %prejoin_roles() select m.title, a.character_name This `defjoin` thing is a limited version of PRQL `table`, which -- unlike a CTE -- remembers which relation each attribute comes from. Perhaps one can instead figure out how to extend `table` to support this.
blep_ 4y ago
If I may suggest an entirely different direction (that also requires either schema knowledge or adherence to a convention), I've always been mildly annoyed that foreign keys are kind of like pointers that don't behave like pointers. What if: create table users (id int primary key, name text); create table things (id int primary key, creator int references users); from things select [id, creator.name];
Aeolun 4y ago
> What do you think should happen if there are multiple foreign keys connecting the two tables? Compilation should fail and require you to explicitly specify what key to use. Please don’t do anything magic.
- setr 4y ago
  You can’t add an additional foreign key to an existing table without potentially breaking all existing queries. Probably the biggest constraint SQL language design has is that its on a live system — things are not compiled at the same time.

smallnamespace 4y ago

If you're willing to sacrifice economics elsewhere, repeating the table's name in the id column is one workaround: from a join b on b.a_id = a.a_id You can even use NATURAL JOIN if you can guarantee that the only fkey/pkey names will overlap between tables. An unreasonable way to achieve that is to put the table name in every column. A more palatable way is to write some clever functions in your schema to scan the information table look for column name clashes (you essentially write a tiny "linter" inside your schema).

hyperman1 4y ago
If you have identical field names, you can do in sql: Select * from a join b using (a_id) Don't do this in Oracle though, pain follows when you try to touch an a_id column.
- snthpy 4y ago
  You can also do this in PRQL: from a join b [a_id] is the equivalent query.

CuriousSkeptic 4y ago

SQL has that actually select * from a natural join b (not based on fk constraints though, it will join on all attributes with the same name in the relations)

go_prodev 4y ago

I agree with you that it's a pain writing join conditions with many fields... But I think that's a shortcoming of the client tool, rather than the language. If SQL tools auto completed the join conditions as best as they could it would probably be a great help.

sgk284 4y ago

The risk here is that if one table has two fks to another table, the syntax becomes ambiguous. And the number of fks two tables have to each other may change over time. This means that an append-only change to a table may break existing queries that have no knowledge of the new column. SQL addresses this via the natural join keyword `using`, where you enumerate the common columns between the two tables being joined. It isn't too convenient for your example unless your pk naming pattern happens to be `<entity>_id` instead of just `id` (note: this naming pattern has all sorts of other adverse consequences though). But it does provide convenience in some cases without introducing backwards compatibility risks as the schema evolves.

dragonwriter 4y ago

The problem is when one has multiple FKs between the same pair of tables. Of course, if you allow naming the relation when you create a foreign key, then you could use the source table-qualified relationship name for joins rather than the target table name, which would be unambiguous (and more communicative of intent). E.g., for a hypothetical table with two self-fks: FROM employees ee INNER JOIN ee.manager mgr INNER JOIN ee.team_lead lead