looks very reminiscent of 40 year old progress 4gl/openedge abl syntax
PRQL – A proposal for a better SQL
261–270 of 302 posts
Re: PRQL – A proposal for a better SQL
#262Earlier quoted context omitted.
> On the brief topic of form vs. flexibility. SQL is a thing that, when complex, is written by many people over the course of its lifetime - removing the ability to make bad decisions is better than enabling the ability to write simple things even simpler Hallelujah! But, to your footnote, this is a major reason why I despise ORMs. In my mind they make writing simple code slightly easier, but they make complicated SQ…
On ORMs, the best use I see of them is for “transparent” queries that you don’t define. Like fetching a record by id, or a single record and all of its related properties. Or a list of all the record in a table matching a simple filter. That’s 98% of what we do against the DB, and I’m all for having it basically invisible. Then let’s just bypass the ORM altogether the minute we think about joining or grouping things…
The fundamental problem with an ORM is that you're using a lower level language to compile to a higher level language. This is completely backwards. It's like having a framework in your assembly to generate Java code for you, so you don't have to bother with all that "weird" Java, and can just stay in your comfort zone.
Re: PRQL – A proposal for a better SQL
#263It would be definitely interesting to have a TypeScript of some sort but for SQL. So a more practical and prettier syntaxe like what I'm seeing here that compiles to SQL queries.
TypeScript is more verbose than JavaScript. While I love to use TypeScript I don't think I'd categorize it as prettier than JavaScript. And practical... well if you mean it is more maintainable then yes but if you mean faster to write then no. I don't want a more verbose SQL I want a less verbose SQL!
>I don't want a more verbose SQL I want a less verbose SQL!
A little off topic, but I have created many databases by writing and converting simple text to database scripts
Try this: https://text2db.com/
Re: PRQL – A proposal for a better SQL
#264Earlier quoted context omitted.
Yup, I like a lot of things about the way this looks. In particular, I like how friendly this looks to be for things like auto complete (pretty annoying to need to practically type the entire sql query only to go back and fix up the columns in order to get autocomplete to work). Specific things I'd like to see. How do you handle column ambiguity. In the examples, they show a join of positions to employee on employee_…
> SQL null handling rules are terrible. I understand them, I work with them, but at > the same time, they are so different from other languages concept of "null" that > they are easy to trip over. Could you elaborate? I'm really only versed in the MySQL accent, but I don't find anything unusual or unexpected about NULLS in MySQL. If there are any pitfalls that I should be aware of, I'd love to know about it here befo…
Not the person you replied to, but I don't think by “from other languages” he means other dialects of SQL.
Instead, I think other languages away from the database are being referred to - in many of those NULL is treated like any other value², for instance in Javascript¹ null==null is true and null!=null is false, and due to type coercion null on its own is “falsey”. Personally I have no problem with SQLs handling of NULL with one exception, and find other languages treating it as a single value rather than an unknown feels odd.
The one thing that I have occasionally tripped over with NULL in SQL is the effect of “ NOT IN ()” when NULL is one of the entries in - it makes sense when you think about it because the IN operator can only return true or false and it can't definitively say the searched for value isn't equal to the unknown one(s)³ but this doesn't seem intuitive.
Some SQL dialects do handle NULL a little differently, more like languages like JS. MS SQL Server can be made to with SET ANSI_NULLS OFF to force its ancient not-standards-compliant behaviour⁴.
[1] quick & easy to test in your browser's console
[2] well, technically in JS I think null is specifically a null object reference, that being one of the differences between null and undefined
[3] more concretely, “var NOT IN (1, 2, NULL)” being equivalent to “var1 AND var2 AND varNULL” which becomes “true AND true AND NULL” which is NULL as any logical operator against NULL returns NULL.
[4] though note that this option is officially deprecated, as of at least 2016, and might be removed or just ignored in future versions
Re: PRQL – A proposal for a better SQL
#265Earlier quoted context omitted.
On ORMs, the best use I see of them is for “transparent” queries that you don’t define. Like fetching a record by id, or a single record and all of its related properties. Or a list of all the record in a table matching a simple filter. That’s 98% of what we do against the DB, and I’m all for having it basically invisible. Then let’s just bypass the ORM altogether the minute we think about joining or grouping things…
Isn’t it more important that the query you write with the ORM is readable than the underlying SQL it spits out? Using an ORM I can get reusable parts of a query, while writing complex joins, I’m not sure why skipping that part is good?
In my experience neither the output or the input is readable when using an ORM to generate complex queries. For simple queries they're great. Writing raw SQL also makes it much easier to jump out into SQL-specific tooling to debug a query, then copy it straight back into the code once you're done.
Re: PRQL – A proposal for a better SQL
#266Earlier quoted context omitted.
In some cases for removing repeating (intermediate) calculations, I generally find it easier to use a lateral join (in postgres), like select title, country, avg(salary) as average_salary, sum(salary) as sum_salary, avg(gross_salary) as average_gross_salary, sum(gross_salary) as sum_gross_salary, avg(gross_cost) as average_gross_cost, sum(gross_cost) as sum_gross_cost, count(*) as emp_count from employees, lateral (…
So now we have easily come up with three different ways of rewriting the query to avoid that duplication (which obviously was not a problem at all to begin with): subquery, CTE and lateral join. And there are also several more well known ways (views, custom functions, computed columns etc) so the whole premise now for even inventing a "better" language than SQL is then false? Or what am I missing. It's also weird how…
> so the whole premise now for even inventing a “better” language than SQL is then false?
I don't think anyone is using the above examples to try invalidate PRSQL, just suggesting the baseline for comparisons should account for all constructs available in the SQL standards and common implementations there-of.
> Or what am I missing.
The statement “I can do X better than with ” does not properly show the benefit of if “I can do X better than with ” is also true (assuming is actually agreed to be better, not for instance convoluted/confusing/long-winded/other so just replacing some problems with others).
Re: PRQL – A proposal for a better SQL
#267Earlier quoted context omitted.
Yup, I like a lot of things about the way this looks. In particular, I like how friendly this looks to be for things like auto complete (pretty annoying to need to practically type the entire sql query only to go back and fix up the columns in order to get autocomplete to work). Specific things I'd like to see. How do you handle column ambiguity. In the examples, they show a join of positions to employee on employee_…
> SQL null handling rules are terrible. I understand them, I work with them, but at > the same time, they are so different from other languages concept of "null" that > they are easy to trip over. Could you elaborate? I'm really only versed in the MySQL accent, but I don't find anything unusual or unexpected about NULLS in MySQL. If there are any pitfalls that I should be aware of, I'd love to know about it here befo…
In SQL NULL does not mean "no value" it means "unknown value". Existence of such value introduces three value logic because expression "NULL = " is neither true nor false. This makes queries harder to understand without any benefit.
Additionally "unknown value" concept is not used consistently. Things like DISTINCT, or UNIQUE indexes (in some databases) treat NULL as single "no value".
Re: PRQL – A proposal for a better SQL
#268First, kudos because it takes courage to take on SQL in this way. Second, this kind of reversed SQL (filter-first, select-last) is much easier to reason about than the original and keep in mind that I prefer to code complex queries in SQL than to build or translate them in the ORM of the project I'm working on. Maybe a transpiler is an inevitable first step but I think that any SQL replacement should be itself the ta…
Thanks! I agree that integrating with the DB would allow much more from a lang. But PRQL is a bet that languages which start there (e.g Kusto) get lost because it requires changing DB, which is really hard. I worry EdgeDB may hit this issue too (but I'm really hoping it works, and they have an excellent team). As I think you're suggesting — you could imagine a language starting out as a transpiler, and then over time…
FYI I think the phrase you're looking for is "impedance mismatch"
(I noticed this on the github readme too)
Re: PRQL – A proposal for a better SQL
#269I might just be good in SQL but I haven't seen anything which is easier to read fore than SQL.
This example looks so weird and it only is getting worse.
Re: PRQL – A proposal for a better SQL
#270Earlier quoted context omitted.
> SQL null handling rules are terrible. I understand them, I work with them, but at > the same time, they are so different from other languages concept of "null" that > they are easy to trip over. Could you elaborate? I'm really only versed in the MySQL accent, but I don't find anything unusual or unexpected about NULLS in MySQL. If there are any pitfalls that I should be aware of, I'd love to know about it here befo…
In SQL NULL does not mean "no value" it means "unknown value". Existence of such value introduces three value logic because expression "NULL = " is neither true nor false. This makes queries harder to understand without any benefit. Additionally "unknown value" concept is not used consistently. Things like DISTINCT, or UNIQUE indexes (in some databases) treat NULL as single "no value".
> Existence of such value introduces three value logic because expression "NULL = " is neither true nor false.
> select if(null=null, "Yes", "No")
> +----------------------------+
> | No |
> +----------------------------+