Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: PRQL 0.2 – a better SQL

github.com

Post reply on HN
Print view View original

151–160 of 166 posts

Re: Show HN: PRQL 0.2 – a better SQL

#151

Post by setr »

Aeolun wrote: post #136
aerzen wrote: post #23

Earlier quoted context omitted.

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 interme…

> 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.

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.

Re: Show HN: PRQL 0.2 – a better SQL

#152

Post by riezebos »

maximilianroos wrote: post #41
jitl wrote: post #37

I'm surprised that none of the examples on Github or the website deals with join. I eventually found some in the "book" here: https://prql-lang.org/book/transforms/join.html from employees join side:left positions [id==employee_id] turns into SELECT employees.*, positions.* FROM employees LEFT JOIN positions ON id = employee_id I would love to see joins worked into the main learning examples. Without join, the exampl…

Great point, we'll add that. I don't think we do joins that much better than SQL does. We're thinking whether there's potential there, maybe through understanding foreign keys — but we're being conservative about introducing change without value.

I always found it surprising that joining on foreign key is not possible in SQL. I'm no expert, but looking at PRQL this feels like it should fit in quite well with the philosophy.

Re: Show HN: PRQL 0.2 – a better SQL

#153

Post by setr »

Flimm wrote: post #36

Looks fantastic. There are a lot of rough edges when building a string representing an SQL query in the programming language that you're using. You have to be careful to avoid SQL injections, for starters. Do the bindings for PRQL innovate at this level?

SQL injections will always be a thing, regardless of SQL vs Not-SQL, if you’re building strings to represent programs. Parameterization is precisely how you properly differentiate between code and data, and it’d be the same strategy no matter the language/system.

Re: Show HN: PRQL 0.2 – a better SQL

#154

Post by psychoslave »

aerzen wrote: post #96
psychoslave wrote: post #86

Earlier quoted context omitted.

I always found that side:left/right should also be expressible as rapport:antecedent/consequent as in propositional logic, rather than limiting these relationships to the geometric representation of Venn diagram. And maybe a shorter alternative might be tie:arm/leg.

I'm not sure if this is a joke, be we actually had a serious an idea to replace side:left/right with nulls_left:true and nulls_right:true This part of the join operation should be an after thought - just a flag after the central argument of the transform which should be the condition you join over.

I didn’t mean to make a joke here, what was the funny part? Reading it again, maybe the tie:arm/leg sounded too much of a BDSM stuff?

Not my initial idea though: I was just looking at short words that might hold the analogy need, from "relationship" you easily come to "tie", and then "arm/leg" for "anterior/posterior" seems pretty straight forward and analogous to "antecedent/(consequent|postcedent|succedent)".

Re: Show HN: PRQL 0.2 – a better SQL

#155

Post by throwaway236324 »

aerzen wrote: post #96
psychoslave wrote: post #86

Earlier quoted context omitted.

I always found that side:left/right should also be expressible as rapport:antecedent/consequent as in propositional logic, rather than limiting these relationships to the geometric representation of Venn diagram. And maybe a shorter alternative might be tie:arm/leg.

I'm not sure if this is a joke, be we actually had a serious an idea to replace side:left/right with nulls_left:true and nulls_right:true This part of the join operation should be an after thought - just a flag after the central argument of the transform which should be the condition you join over.

Have you considered using "optional"? I feel that this would be a more natural syntax for joins. It could default to "left join", which is probably more frequently used than a right join.

from employees join optional positions [id==employee_id] --> LEFT JOIN

from employees join positions [id==employee_id] --> JOIN

Then you'd use "optional right" or something similar for the "right join" case

Re: Show HN: PRQL 0.2 – a better SQL

#156

Post by psychoslave »

psychoslave wrote: post #154
aerzen wrote: post #96

Earlier quoted context omitted.

I'm not sure if this is a joke, be we actually had a serious an idea to replace side:left/right with nulls_left:true and nulls_right:true This part of the join operation should be an after thought - just a flag after the central argument of the transform which should be the condition you join over.

I didn’t mean to make a joke here, what was the funny part? Reading it again, maybe the tie:arm/leg sounded too much of a BDSM stuff? Not my initial idea though: I was just looking at short words that might hold the analogy need, from "relationship" you easily come to "tie", and then "arm/leg" for "anterior/posterior" seems pretty straight forward and analogous to "antecedent/(consequent|postcedent|succedent)".

I transformed this informal suggestion into an issue, see https://github.com/prql/prql/issues/718

Re: Show HN: PRQL 0.2 – a better SQL

#157

Post by catchclose8919 »

slotrans wrote: post #22

Hot takes: SQL is great, actually. This thing isn't better.

Not sure if this is better, but SQL is HORRIBLE... we probably put up with it bc it's based on sane math & theory, and we almost never write it by hand.

There's zero thought to any kind of ergonomics, there's no way to say "join table Y but prefix all its columns with employee_", it's expressed backwards ffs (instead of starting with FROM), results of queries with joins are forced to be flat tables and there's no way to get trees as you need 99% in app code - all the repetitve app code to "nest" entities in results that also needs to make brittles assumptions about ordering and uniqueness because people couldn't standardize on a "RESULT AS TREE [NESTING INTO ]" clause or smth. equivalent etc. etc.

PRQL though seems to also lack all the essetial features you would expect around joins.

Suff like Arrango DB's AQL seems to be a nice example of adding the missing feature to SQL, probably more of the need to accomodate graph data too, but it actually solves SQLs problems even in relational contexts - see https://www.arangodb.com/docs/stable/aql/tutorial-join.html#... .

Re: Show HN: PRQL 0.2 – a better SQL

#158

Post by BeefWellington »

vore wrote: post #137
BeefWellington wrote: post #121

Earlier quoted context omitted.

As go_prodev indicated, the former just isn't how you reason about data modeled in a relational form. The latter makes little sense. What constraints are you placing on a college that do not also apply to a highschool, given they're both schools? > The other thing in the grandparent's comment that's a constant pain in SQL is representing an ordered list: how do you insert items into the middle of the list? Depending…

That's exactly it though: having to do dumb tricks is the painful part. There's plenty of things with user-defined order where an explicit index isn't surfaced, like to-do lists, playlists, etc.

A user-defined order is an explicit index.

EDIT: To expand further, I would generally model a playlist as its own tables anyways. Something like:

  CREATE TABLE playlist (
    id ID_TYPE_FOR_DB PRIMARY KEY,
    name varchar(4096), /* Or whatever storage size makes sense */
  )

  CREATE TABLE playlist_entry (
    id ID_TYPE_FOR_DB PRIMARY KEY,
    song_ref ID_TYPE_FOR_DB FOREIGN KEY REFERENCES songs(id),
    order INT, /* Or bigint or whatever you want */
  )

Re: Show HN: PRQL 0.2 – a better SQL

#159

Post by creamyhorror »

Wow! This is cool stuff.

I was looking through the documentation for conditional logic, control flow, IF(), and the CASE...WHEN...THEN operator. It seems like the ternary operator is the single way to implement conditionality?

At https://prql-lang.org/book/examples/functions.html I found the example `func if_valid x`, which is then used in the `derive` expression `prices_adj | ret | if_valid`. This usage of `if_valid` at the end of the pipeline seems a bit awkward: if I want to do the whole calculation only if some condition is met, I'd like to write it at the front of the pipeline. Can I nest the `ret` function in the if-function like so?: `func ret_if_valid x -> is_valid_price ? (ret x) : null` Then I guess I'd have to do:

    return_total = prices_adj | ret_if_valid
Next, can a string variable be used as part of a column name? It's something I needed recently in order to categorise values and use them to do a pivot. For example, I had to do:

    SELECT CASE category WHEN 'a' THEN 'x' AS newCategory
    ...
    SELECT SUM( if(newCategory = 'a', revenue, 0) ) AS aRevenue, ...
Ideally I'd like to do:

    aggregate [
        for x in ['a', 'b', 'c']:
            (concat x 'Revenue') = sum ( [new_category == x] ? revenue : 0 ),
    ...]
i.e. I'd like to use x as part of the final column name. A pivot example would be great (or maybe you already have a PIVOT implementation in mind).

I'd be interested in contributing to this, guess I'll take a look at the code and any community chat you've set up!

Re: Show HN: PRQL 0.2 – a better SQL

#160

Post by jhgb »

ttfkam wrote: post #87
jhgb wrote: post #61

Earlier quoted context omitted.

One of the reasons why SQL is crap: there should be no distinction between the two in relational algebra. A set of {A, B, C, B, C} is the same as {A, B, C}.

Detecting duplicates has a cost you can't just hand-wave away. UNION ALL tells the engine not to worry about it and just output as it sees it, usually going faster. Depends on your data needs.

And not treating relationships as sets has costs of its own -- for example it breaks relational formula equivalences that could be used for query optimization.
Post reply on HN