HN Explorer

Ask HN: Does anyone else think SQL needs help?

96 points by fny ↗ HN
I have been writing a decent amount of SQL for three years now, and while the paradigm is extremely powerful, the language lends itself to unmaintainable spaghetti code quickly.

Fundamentally, SQL lacks meaningful abstraction, and there's no sane way to package very common operations.

Say you want to find elements in a row that correspond to some maximum value when grouped by date. Today, you'd need to write something like this EVERY SINGLE TIME:

``` SELECT sd1.sale_person_id, sd1.sale_person_name, sd1.no_products_sold, sd1.commission_percentage, sd1.sales_department FROM sales_department_details sd1 INNER JOIN (SELECT sale_person_id, MAX(no_products_sold) AS no_products_sold FROM sales_department_details GROUP BY sale_person_id) sd2 ON sd1.sale_person_id = sd2.sale_person_id AND sd1.no_products_sold = sd2.no_products_sold; ```

Wouldn't something like this be nicer?

``` SELECT sale_person_id, max(no_products_sold) as max_sold, link(sale_person_name, max_sold), FROM sales_department_details ```

Frankly, it seems like some sort of macro system is needed. Perhaps the SQL compiles into the above?

212 comments

[ 0.78 ms ] story [ 232 ms ] thread
SQL2219
With dynamic sql you can write your own. I know you don't have time, just throwing it out there.
db48x
People build composable and reusable abstractions on top of sql all the time.
quantified
Examples would help. It's an assertion people frequently make. I have struggled with homegrown not-particularly-good ones. One problem is finding a good set of abstractions for the things I really need to abstract on. So there are probably different abstractions to note.

In the same vein that different languages provide higher-level abstractions for machine code or JVM bytecode.

PaulHoule
Stored procedures, user-defined functions, views…
quantified
We may have differences in how we define "abstract". Views and SPs encapsulate, but they don't change the difficulty of parameterizing on table name or column name within SQL, for example. No spread operator to say "select the columns from thing X that are going to be inserted into thing Y".

There are so many different ways to abstract aspects for different goals.

simonw
The Django ORM is one of my favourite abstractions on top of SQL.
rglullis
Nice try, Django core developer... :p
db48x
Now now, we all have some biases.
quantified
I haven't gotten into Python so its advantages are unknown. Does it provide an object model for join, aggregation, filter and from clauses? Can I compose named functions that represent transforms (map, filter, etc.) in a type-safe fashion with each other and have those translate to appropriate CTEs? ORM has no use for me, very little of what I work on exchanges data with programming-language objects.
simonw
> Does it provide an object model for join, aggregation, filter and from clauses?

Yes: https://docs.djangoproject.com/en/4.1/topics/db/queries/#ret... and https://docs.djangoproject.com/en/4.1/topics/db/queries/#loo...

> Can I compose named functions that represent transforms (map, filter, etc.) in a type-safe fashion with each other and have those translate to appropriate CTEs

The Django ORM doesn't use CTEs (yet). It's common however to create and compose custom filter operations which the ORM will then combine into larger SQL queries. A rough example:

    def today(qs):
        return qs.filter(created__date = datetime.date.today())

    def tagged(qs, tag):
        # This becomes a many-to-many lookup
        return qs.filter(tags__tag = tag)

    entries = today(tagged(Entry.objects.all(), "python")
This would build the following SQL query:

    SELECT
      "blog_entry"."id",
      "blog_entry"."created",
      "blog_entry"."slug",
      "blog_entry"."metadata",
      "blog_entry"."search_document",
      "blog_entry"."import_ref",
      "blog_entry"."card_image",
      "blog_entry"."series_id",
      "blog_entry"."title",
      "blog_entry"."body",
      "blog_entry"."tweet_html",
      "blog_entry"."extra_head_html",
      "blog_entry"."custom_template"
    FROM
      "blog_entry"
      INNER JOIN "blog_entry_tags" ON ("blog_entry"."id" = "blog_entry_tags"."entry_id")
      INNER JOIN "blog_tag" ON ("blog_entry_tags"."tag_id" = "blog_tag"."id")
    WHERE
      (
        "blog_tag"."tag" = 'python'
        AND ("blog_entry"."created" AT TIME ZONE 'UTC') :: date = '2022-07-30'
      )
    ORDER BY
      "blog_entry"."created" DESC
There are plenty of more sophisticated ways to build abstractions over queries in Django - using custom QuerySet methods for example: https://docs.djangoproject.com/en/4.1/topics/db/managers/#ca...
quantified
Cool, thank you for taking the effort to illuminate.
db48x
There is a database library called Diesel that I use which has good support for composition. The easiest example to point to is on their own webpage: https://diesel.rs/guides/composing-applications.html
quantified
Thank you. Like JOOQ this seems to provide a programming object model for SQL which is a good start. That's my 15-second scan, I look forward to looking further. A good expression model for everything, including non-parameterizable elements of SQL like table names and column names, type signatures for SQL results, is a great start.
PaulHoule
I am using JooQ now at work to write SQL statements in a Java DSL. Java’s type system mostly works as intended and is helpful, particularly in conjunction with an IDE. Many kinds of metaprogramming are possible with JooQ.
PaulHoule
In the semantic web, RDF can put every table in the universe in a unique namespace and OWL can bind together multiple tables and do inference at a high level, then you can query it with SPARQL which is quite similar to SQL at heart.

The Department of Defense was asked by Congress to answer “Where does the money go?” and tried to use OWL and SPARQL queries across all the relational databases they owned but they couldn’t get it to work.

I can’t help but think something along those lines could present as a ‘low code’ data tool.

You can accomplish something similar with views, foreign tables, stored procedures, user defined functions, and other mechanisms which are common in databases like PostgreSQL, Microsoft SQL, etc.

I find PostgreSQL pretty handy because it supports embedded data structures such as lists in columns, supports queries over JSON documents in columns, etc.

My favorite DB for side projects is ArangoDB which uses collections of JSON objects like tables and the obvious algebra over them which lets you query and update like the best relational dbs but you don’t have the programmability of views, stored procedures, etc.

shubb
I read this like "DoD tried to do quite a complicated semantic layer over tables project but failed because the RDF cluster of technologies is horrible. It would have been easier to code from scratch with SQL and python".

I think you are pretty familiar with those technologies. Do you think they have any value?

PaulHoule
I see the problem of the DoD being asked where the money goes as an intractable problem, not least because they have more than one reason that they don't want to tell you.

That is, it will defeat any technology that you bring to the table.

My own feelings about RDF are mixed. On one hand I've accomplished quite a few things with it, for instance, developing a technique for bringing multiple schema systems (say EMOF, XML Schema, and FIX Orchestra) into a single framework but it has been like

https://supernatural.fandom.com/wiki/Binb%C5%8Dgami

for me in that I have put huge amounts of time and money into it with very little return, not because of technical problems per se but because of problems marketing it.

There's a certain kind of stupidity inside the community that keeps it from making tools that are truly effective but there is even more stupidity outside the community that makes it seem it won't ever be understood. The semantic web seems to be cursed that it will never develop a brand like GraphQL, Docker, mongodb, or "FAANG" that works like a Jedi mind trick to bring people into its domains.

For instance I saw GraphQL as beneath contempt for many years because there was no specification, it was just "Facebook will return any answers that further their business interests if and when they want to" and somehow people are seduced by that the same way that they are seduced by Donald Trump or will cut out their left eyeball because Matt Butts said that is how to raise your PageRank by 0.1% next Thursday.

shubb
Sorry for the slow response and thank you for the long one
PaulHoule
The one that drives me nuts is

   select this,count(*) from that group by this
which is the most common data exploration query of them all which makes you type ‘this’ twice.
thomasdziedzic 

  select this, count(*) from that group by 1
dominotw
I actually find this less readable. i have to go back and scan the select to see what column this index corresponds to. Totally breaks the flow of reading.
eatonphil
Tested this out on Postgres and it works:

  SELECT
    name, count(1)
  FROM 
    (VALUES ('Kev',12), ('Meg', 14), ('Kev', 14)) 
  people(name, age)
  GROUP BY
    1
Produces: [[Kev, 2], [Meg, 1]].

But I don't get it. Could someone explain how this works?

lgsilver
Most SQL dialects accept a positional or a direct reference to the column name. “1” represents the first column created by the select statement, “name”.
eatonphil
Ah I see. Is that how `count(1)` also works? Or is that different.
xapata
Different. SQL's syntax is funky.
zasdffaa
Please never allow this into production. I believe it is deprecated, it certainly is fragile in general.
zasdffaa
What would you prefer to see instead, bearing in mind that anything you will suggest is likely to bsave minimal typing but introduce new complexity into sql which everyone agrees is already creaking like a constipated elephant.
PaulHoule
something like a function or macro

   CREATE MACRO count_values(this, that) WITH DEFINITION
       SELECT this,COUNT(*) FROM that GROUP BY this
RyanHamilton
How about always returning the "group" column by default. If the user wants to get rid of it later (much less frequent) they can do an outer select or remove the column themselves.
simonw
I find CTEs (the WITH statement) greatly improved my ability to run more complex queries because they offer an abstraction I can use to name and compose queries together.

SQL views are useful for that kind of thing too.

warmwaffles
The only issue I see with CTE's and have encountered, is poor query performance. The query planner needs to improve in this regard. At one point I noticed some of my CTE with queries being written to a temp file on disk and used to join against, when an inlined query did not do this producing the same results.
srcreigh
In Postgres you can use AS NIT MATERIALIZED to avoid the temp buffers. A recent PG version also makes this default, but only when the CTE is only queried once later.

This can definitely cause issues. I’ve seen queries with CTEs that basically process unindexed 100 MB data due to querying the materialized rows.

It would be cool to be able generate indexes with the CTEs though

warmwaffles
How would this look with a sample query? (using AS NIT MATERIALIZED)
jdmichal 

    WITH one AS NOT MATERIALIZED (
      SELECT 1 AS value, 'one' AS name
    )
    SELECT *
    FROM one
As srcreigh said, this is the default since I believe PostgreSQL 12, as long as the CTE is only used a single time. If you use it more than once, it by default wants to materialize it instead of recalculating each time. You can also force materialization by dropping the `NOT`.

Also note that materialization acts as an optimization fence. That is, PostgreSQL will not push down filter criteria and such from the query into the `WITH` clause. It can do such if it's not materialized.

warmwaffles
This is incredibly helpful. I did not know this was a possibility.
trimethylpurine
What you're saying is analogous to "My horse won't run when I tie its legs together." You'll see what I mean here and hopefully it helps you.

Not sure which engine you're using, but for MSSQL at least, the engine writes stuff to disk where it can index it so that it can avoid table scans on retrieval. That is ideal where you have big joins or large tables. And is generally good for most use cases.

When you use CTE's you're telling the engine, "I want this in memory, don't do any indexing." That's awesome for write heavy workloads. But if you plan on joining the results of the CTE later, you will find that you can't read from it very quickly because for every row joined you must scan the entire table.

Now you'd think that since it's also forcing the data to stay in memory (if there's enough) that this would be faster. But that's not true. There's compute power at work to make comparisons on each row. The point of indexing is to reduce the required compute power by reducing the number of rows that you need to compare.

With that in mind, it's obvious that it kills performance if you just told your engine to not use indexes but do a big join on a large set of data.

Temporary tables are a better choice for read heavy workload compared with CTEs. MSSQL will index temp tables automatically. And in very rare cases where the automatic indexes aren't performant, then you can index them manually with a few extra keywords.

Happy querying.

mixmastamyk
How to use a CTE with a group of several following statements? I think this is where you would use a view instead, but would appreciate confirmation.
cafxx
For complex SELECT queries, I do:

WITH cte1 AS ( /* whatever / ), cte2 AS ( / you can refer to cte1 here / ), cte3 AS ( / you can refer to cte1 and cte2 here */ ), ...

If I need access into multiple statements that I can't refactor into a single one, explicitly using temporary tables is a viable option (some engines implement CTEs and other operations using implicit temporary tables anyway). Otherwise, yes, views.

gwd
Right, but can you 1) store CTEs and 2) pass arguments in to CTEs, such that the same basic 'code' can be shared between multiple different queries?

I'm a principal engineer who is relatively new with SQL (just learned it 5 years ago, using it for a side project, not my daily work) and almost completely self-taught; and the biggest problem I have with SQL is that I find it nearly impossible to do even basic levels of DRY in a way that's not ugly.

ttfkam
I believe what you're looking for is (in PostgreSQL) called a set returning function.
sebazzz
The problem at least with MSSQL is that these Table Valued Functions or User Defined Functions (scalar) are definitely not zero-cost abstractions.

Another issue is that the “SELECT” is also part of the function, which makes it even worse - so you can’t create just one function and reuse it all over the place. If only you could tell SQL that a certain JOIN always contains one row, or zero or one row (for a LEFT JOIN), then there would be more room for optimization.

trimethylpurine
I think that's subjective. You can index to optimize for whatever your function is doing.

I don't see any reason that the "select" limits reusability. If you define your function with relevant parameters you can create theoretically infinite reusability.

SakeOfBrevity
Check out dbt (not dbt-cloud), great open-source SQL tool, macro system is included in the ever-growing set of features. It helps managing SQLs a lot.
sph
The problem with SQL is that it's all about transforming data, and that would better be modelled as a pipeline.

https://prql-lang.org/

CodesInChaos
You could take a look at EdgeDB. It's built on postgres, but uses its own query language and data model. Its data model has build in support for links and polymorphism. The query language EdgeQL makes it easy to follow links and output embedded arrays.
bawolff
It sounds like you just reinvented VIEWs?
e12e
VIEWs and generated columns should be able to help with these problems?

https://www.postgresql.org/docs/14/ddl-generated-columns.htm...

> A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables.

RedShift1
Unfortunately with Postgres, generated columns are always stored on disk, so you lose some storage space.
bawolff
Finally, something for mariadb fans to hold on to in the flame war.
RedShift1
Haha I guess :-D. Though I have a workaround for complicated generated columns that I don't wish to store: using a function. So my select would look like:

  SELECT a, b, something_complicated(table) FROM table.
Of course you have to remember to do that but hey it's something.
srcreigh
My reason for liking MariaDB is that MySQL uses a B-Tree for primary storage. In Postgres if you want a table with large keys, you have to store them twice once in the index once in primary storage (hash table). In MySQL there’s no hash table you basically just store the rows in an index. Could be as much as 40% reduction in storage… 400GB instead of 1TB.

Postgres does have index compression as of a recent version, but I’m not sure how much that would improve random keys.

ttfkam
A 40% reduction would be 600GB, and an index that took up 40% of the total would be excessive/unrealistic as well.

You're right of course that Postgres lacks true clustered indexes. The closest thing it has is covering indexes, which don't help at all with saving space; quite the opposite in fact.

srcreigh
Thanks. You’re right. And yeah, the 40% ish reduction would only happen if the table has for ex every column in a unique index. Not very common.
eyelidlessness
I prefer Postgres, and the thing I most envy from Maria is first class bitemporal tables. Granted it took me probably a week curiously returning to the topic to fully grasp their value, and I don’t think I’m alone in that lack of immediate intuition. And granted non-SQL databases like Datomic are much better positioned for use cases where people realize they want the feature. And granted it definitely can’t be more storage efficient to store bitemporal data than not to. But I think it’s a much more compelling feature, from the outside.

As far as memory complexity, this got me thinking a userspace implementation (I’ve prototyped one for Postgres using VIEWs for fun and personal education) could reasonably borrow optimization techniques from persistent data structures common in FP languages. But I don’t know how well a query planner would cope with that, and my instinct is that the optimization would be better applied at the storage engine level (again as I see it, advantage: Maria).

ttfkam
The key to temporal tables in Postgres is the following:

• add a timestamp range column

• create a history table that matches the main table

• using table inheritance, have the live table inherit from the history table

At this point all queries on the main table are "live" and queries on the history include past and present data.

• add an insert trigger that initializes the live table's timestamp range to start "now()"

• add AFTER UPDATE/DELETE/TRUNCATE triggers to insert the OLD record into the history table with appropriate timestamp ranges

Bonus

• have event triggers automatically set up the temporal tables on CREATE TABLE

eyelidlessness
So for temporal data this is a pretty solid approach. For bitemporal data, it’s more complicated to do in plain SQL (though it can be done). To clarify: I’m using temporal here the same way you are, as an immutable/append-only record of state as it existed at the time. Whereas bitemporal data has that property plus a separate historical record of when data was/should have been/should be or optimistically will be effectively true.

The thing which helped me grasp this was correcting historical accounting according to the domain (schema), eg updating meeting records. You of course want to preserve the previous record upon update, but the “current” state of what was decided a week ago might still change.

tacosbane
I think every dialect has an idiomatic way to do what you're asking. e.g., in Snowflake it's `select * from sales_department_details qualify no_products_sold = max(no_products_sold) over (partition by sales_person_id)`, PSQL `select distinct on (sales_person_id) * from sales_department_details order by sales_person_id, no_products_sold desc`, ...
hantusk
Postgres can do the"function over (partition by col_a order by col_b) as well.

Agree, SQL can already do what the OP proposes in almost the exact same syntax.

and together with CTEs, intermediate temp tables or views, you can totally avoid the spaghetti and break down the queries in manageable chunks.

tacosbane
postgres has window functions but it does not have the `qualify` clause.
ttfkam
True, but you can simulate it by running the query with the window function in a CTE and then make a separate SELECT with the appropriate WHERE clause. More verbose, but the same effect and result. Using NOT MATERIALIZED on the CTE can also potentially let the planner do better query optimization on it.
benjiweber
create view max_products_sold as select sale_person_id, max(no_products_sold) AS no_products_sold from sales_department_details group by sale_person_id;

select sale_person_id, sale_person_name, no_products_sold, commission_percentage, sales_department from sales_department_details natural join max_products_sold;

dasil003
Agreed that SQL lends itself to spaghetti. However after working with it for 25 years both as a developer but also collaborating with analysts and data scientists, I have to say I appreciate its basic declarative nature and remarkable portability as it has expanded into distributed databases.

For me the value of being able to walk up to an unfamiliar database of widely varying tech and start getting value out of it immediately is the killer app. Macros or other DB-specific extensions would be useful at times, but I’m not sure how much they would enable solving the messiness problem in a generic way that reshapes the paradigm more broadly. My instinct is the messiness is a consequence of the direct utility and trying to make it nicer from a programmerly aesthetics point of view might be hard to sell. It’s not like SQL doesn’t have affordances for aesthetics already (eg. WITH clauses).

fny
SQL is masquerades as declarative. It's relational algebra in disguise. This results in...

- Queries that are difficult to interpret/verify.

- Non trivial amount of effort to go from question to query: I often feel like I have to jump through hoops to obtain something trivial. I have to think, and I don't want to think.

dasil003
I agree that SQL is needlessly baroque. Not sure whether I agree that there are too many hoops for trivial things though. Leaving SQL ergonomics aside for the moment, isn't relational algebra something which does require one to think carefully to avoid hastily producing plausible but incorrect queries?
alliao
PowerBI is definitely down the "don't want to think" alley, lots of misleading graphs are born that way lol
spaghettiToy
Disagree. Microsoft sells these as "unskilled", but at the end of the day, you still need skill but also perseverance.

At least SQL is old enough to get plenty of relevant search results. You can be a copy-paste DJ if you really can't figure it out.

alliao
I agree! the bar's so low that I've seen a lot of people just click around till they get a pretty graph without knowing what/why they did!
nextos
It's relational algebra, plus relational calculi.

> Fundamentally, SQL lacks meaningful abstraction

My RDBMS course at college was really hardcore and SQL code answers for exam questions were enormous.

One neat trick they didn't teach us, but still perfectly legal SQL:1999, is to use WITH recursive constructs.

This way you can decompose very complex queries into simple ones, that can tested independently.

grvdrm
The recursive stuff is pretty cool. In general, I like CTEs a way to break queries into pieces to improve readability and understanding (among other improvements).

Also, integrating aggregate functions along with row_number() and similar functions to perform more complex work with less code.

funcDropShadow
> SQL is masquerades as declarative. It's relational algebra in disguise.

IMHO declarative and relational algebra are orthogonal concepts. And why do you say SQL is relational algebra in disguise (paraphrased) ? Most introductions to SQL I've read, but certainly the SQL page on Wikipedia [1], starts with a remark about Codd's relational model. Granted SQL does not implement it perfectly, and it is not perfectly declarative, although that is a matter of taste.

> Non trivial amount of effort to go from question to query: I often feel like I have to jump through hoops to obtain something trivial.

I've had colleagues which expressed similar sentiments, but I have the opposite impression. I think I can express many queries on a much higher abstraction level than with most other languages.

> I have to think, and I don't want to think.

Not wanting to think while writing code is an understandable wish, but also a big red flag about developers for me. Even the best AI with perfect mind reading capabilities will not be able to execute the query you want, if you don't know what you want yourself. Even the AI would guess sometimes right, you would have no way of knowing whether it was right or wrong.

[1]: https://en.wikipedia.org/wiki/SQL

fny
> Not wanting to think while writing code is an understandable wish, but also a big red flag about developers for me.

Dude... these are simple queries. I have *far* more important things to ponder than the specific incantations needed to get one of the values that correspond to a maximum.

trimethylpurine
max() is an iso standard function. But you need to think relationally to express exactly which maximum you're looking for, that's true. I can see wanting the language to do that for you, but that would be extremely limiting, since every problem seeking the "maximum" isn't remotely similar. Imagine a request for fewer tools in a toolbox. It might take time to learn where they are located, but they are all in the box for a reason. There's a certain point in learning SQL where you stop thinking about your work in rows and columns and you start thinking in tables. When that happens, you'll easily write good code with very little thinking. Give yourself time. Also look into dynamic SQL if you're concerned with repetition. There's no reason SQL should be overly so.
usehackernews
I want to train a language model (or use existing ones) to translate question to query for trivial questions.

I find 80% of queries that people want across a company are trivial.

I’d have saved myself many many hours over the years if people could just ask a question in natural language rather than asking me to write a query.

systemvoltage
I think if your SQL becomes spaghetti, it is time to fetch the data out of the database and then process it. SQL databases aren't a data-science studio that lends itself for extreme processing capabilities just because it is fast. Furthermore, it is the last thing that scales typically. Relational processing is what its good at. Doing things like bayesian/statistical analysis on the data in SQL is asking for trouble.
setr
Relational databases are perfect for data analysis scenarios, especially when it fits in a single server. Why are you pumping data over the network, and serializing/deserializing it from a table structure usually back into a table structure, when you could just process it , often using only the data in the datastore, and pipe the final computed results over?

Hell they even have a name for databases specialized for this usecase — OLAP

The problem is that databases, and SQL, are such a pain to extend and define sufficiently general operations that you don’t just have scipy in the database, and thus your complicated analytics have to be fully specified with the few rudimentary analytic functions that are actually provided to you.

pdntspa
I can't believe that at time of this writing, only a few other comments are mentioning VIEWs! Like that is this dude's exact complaint!

smh...

default-kramer
Let's say you have 14 derived facts (eg aggregations, computations involving joined tables, etc) about a SalesPerson. Do create 14 single-column views? Or have one view with 14 columns? Either way the composability is terrible. If you go with 14 views, the syntax is awful and not very resistant to refactoring. If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).
pdntspa
You can SELECT just the columns you want with one view, and IIRC that will exclude other columns from being retrieved unless they are the source of a derived value.
wswope
> If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).

This is outdated; most modern DBs treat views as they would CTEs. If you don’t reference a defined field downstream, it doesn’t need to get calculated.

default-kramer
It's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process the joins to make sure the result set has the correct shape. For example, a human might know that a join will always be one:one or one:zero-or-one, but the DB has no choice but to make sure. Perhaps using subqueries instead of joins would work, but that gets ugly too.

(Or maybe my knowledge is outdated and the optimizers have gotten way better than they were 3-4 years ago.)

jdmichal
At least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.
cm2187
For LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.
jdmichal
That's an excellent point. I hadn't thought of it because the joins where I witnessed this were all on primary keys, as $DIETY intended. (That's a joke.)
trimethylpurine
With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.
trimethylpurine
I'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.
zasdffaa
As others have said, any decent optimiser will prune out values that aren't SELECT'ed. I don't think it's that complex either - find the unused columns, remove them from subviews, recurse all the way down. Try it on MSSQL/Postgres and you can see it being done.
fny
The bigger problem is views are tightly coupled to the underlying data.
saltcured
Right, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data.

Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.

gerdesj
Please give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table.

Now, ... reuse. Surely you write the result to another table and then refer to that.

saltcured
The detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples.

Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros...

Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions.

Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle.

The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface.

Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions.

So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.

oedo
>functions that take sets as input and produce arbitrarily different sets as output

It is possible to return arbitrary sets from functions in many systems-- recent versions of the SQL standard even specify table functions (with syntax "RETURNS TABLE(columns)")[0] as well as polymorphic table functions (PTFs)[1]. And in addition to implementations of those standards, extensions like PL/[pg]SQL also have a few other ways of effecting table-like returns (parameter modes, composite type returns).

>function overloading

Not standard, but some systems support it[2].

[0] https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC...

[1] https://share.ansi.org/Shared%20Documents/News%20and%20Publi...

[2] https://www.postgresql.org/docs/current/xfunc-overload.html

saltcured
Right, I'm aware of table-returning functions and the general idea of functions as table sources in a FROM clause. I think the biggest gap is not having complementary table-consuming function signatures and ways to use functions as table sinks other than aggregate function calls.

I'd also want to allow a function to be both a table source and a table sink, which I think means that you might need a sub-query like syntax to supply the source that will be consumed by the function. This is a vague idea and not a well-defined language proposal:

    CREATE OR REPLACE FUNCTION
    -- invent a table-sink func signature?
    func1( TABLE t1(a int, b text, c text) )
    RETURNS TABLE (x int, y boolean) AS $$
      -- access input set via declared name
      SELECT 
        a, b < c
      FROM t1
    $$ LANGUAGE SQL;

    CREATE OR REPLACE FUNCTION
    func2( TABLE t1(a int, b boolean) )
    RETURNS int AS $$
      SELECT a WHERE b
    $$ LANGUAGE SQL;

    -- implicit positional matching of func input cols?
    SELECT f1r.x, f1r.y
    FROM func1( SELECT e1, e2, e3 FROM ...) AS f1r;

    -- explicit input mapping to override positions?
    SELECT f1r.x, f1r.y
    FROM func1( SELECT e1, e2, e3 INTO b, a, c FROM ...) AS f1r;

    -- function composition
    SELECT func2(func1( SELECT e1, e2, e3 FROM ...));
kiernanmcgowan
Views, especially Materialized Views if you’re on Postgres, are about as close as you can come to a “one neat trick that solves all your problems” in modern software development imo.
remus
I'd love to use materialised views more often but my experience is that they're full of annoying limitations, typically around functions that feel like they should be immutable but aren't.
kiernanmcgowan
Right, I think the timestamp with timezone type is a great example of one of these short comings.

I’ve generally used it as a step in a data pipeline, where some intermittent state or aggregation where being stale/stable for a period of time is ok. Did this recently for a daily trends report on some news data - live querying the data is slow, but baking it off into a MV makes it much faster and keeps the query result from changing too rapidly to new data.

ttfkam
Materialized views + pg_cron = chef's kiss for simple yet performant report generation.
irrational
Oh good, it’s not just me. As soon as I read his post I said to myself, “It sounds like he hasn’t heard of views.” But the first comment didn’t mention views and I was so confused that I started wondering if I misunderstood the post.
viraptor
There are situations where you can't use the views. For example I'm writing software which integrates with $vendor app. Not only do I have restricted select access, I have no influence on the schema at all. By definition, I have to send a complete query every time. And there are many similar scenarios.
EMM_386
They did mention they've only been using it 3 years.

It's been an ANSI standard for 36 years.

wodenokoto
I disagree. This is a problem with filtering data. Creating a view every time you want 1 row from each group based on some calculation is beyond cumbersome IMHO.
tluyben2
But OP says every single time in their post which nicely fits a view as building block for more readable and simpler sql. Their issue seems not a one of filtering problem.
default-kramer
Yes, I can't believe that the awesome power of relational DBs is still hindered by the major flaws of SQL. Here is my attempt at something better: https://docs.racket-lang.org/plisqin/Read_Me_First.html The feature I am most pleased with is that joins are values (or expressions, if you prefer) which can be then refactored using normal techniques.
mamcx
Check my project:

https://tablam.org

---

Exist 2 major ways to solve this: You do a transpiler (like most ORM are, actually) or you go deeper and fix from the base.

The second could yield much better results. It will sound weird at first, but not exist anything that block the idea of "a SQL" language work for make the full app, with UI and all that.

SQL as-is is just too limited, in some unfortunate ways even for the use-case of have a restricted language usable for ad-hoc queries, but that is purely incidental.

The relational model is much more expressive than array, functional models (because well, it can supersede/integrate both) and with some extra adjustment you can get something like python/ML that could become super-productive.

sshine
Pine [1] and jq [2] are combinator query languages.

Every expression represents a filter of some kind.

In SQL (and in relational algebra), every expression represents a dataset.

That means composing SQL expressions is composing data, not composing operations on data.

Since there is a somewhat low, backwards-compatible barrier to entry to build a combinator language on top of SQL (Pine is an example), whether or not trading the increased learning curve of using combinators for better composability is a good idea can stand the test of time.

[1]: https://github.com/pine-lang/pine [2]: https://stedolan.github.io/jq/

trollied
>Frankly, it seems like some sort of macro system is needed

Views, materialized views, CTEs.

fny
No, these are all highly coupled to the underlying data structure. Even stored procedures tend to know a bit too much.

Say I have a table with the following properties:

- It has a column that corresponds to an id

- It has a column that corresponds to a category

- It has a column that corresponds to a value

- It has a column that corresponds to a timestamp

- It has a bunch of other columns

I want to have a general transformation that allows me to:

Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max.

The stored procedure ends up being a mashup of string concatenation. Meanwhile, it just feels something is missing to make the language expressive enough.

zasdffaa
> No, these are all highly coupled to the underlying data structure

Difficult to be sure what you mean, but I think you're wrong. You can select from any table source (edit: result set, if you prefer that term).

> Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max.

   select min(category) as mincat, max(category) as maxcat,
      min(value) as minval, -- etc
   where ts between <start> and <end>
I am not sure what you're asking for if the above is not it. Can you give an example please?
setr
He wants to do the same thing, but on 100 different tables. The only thing that changes is the tablename and column names involved.

You can copy, paste and edit 100 times and create 100 view objects

Or in eg numpy-python, you’d write a single function taking the table/columns as arguments, and reuse it.

I think the usual SQL solution would be to implement a stored function and start smashing strings together

dmux
That makes sense -- in that case my first instinct would be to attempt using the "information schema" (metadata about tables and their columns) to get the tables I'm interested in (and their columns). Luckily all that metadata is presented as regular old tables itself, so it's pretty straightforward to query!
setr
That’s what he means by it’s coupled to the underlying data structure; you can’t simply define a general purpose utility function to accomplish this (without smashing strings), that could be applied to arbitrary tables — eg on an arbitrary schema, or even on unnamed tables (eg resultset of a select query)

Which doesn’t matter in many of these simple examples, but this is the thing that really stops you from getting to have your import gravity ecosystem of utilities that modern application programming enjoys. And the continuing buildup of libraries on top of libraries for further complexity.

Instead you’re limited to extensions/packages, which are coupled tightly to the RDBMS internals (which is why you don’t see much of SQL extensions shared across DBs, despite at the end of the day operating all on the same shared framework [relations]) and whatever functionality the database provider has deemed fit to provide (perhaps at cost)

zasdffaa
So you do that on 100 different tables - then what? Join them, return them to the client, what? Stuff's missing here.
setr
Let’s say three usecases:

1. To Union them into multiple views

2. To instantiate individual views per query, to reuse in other queries

3. To return to client directly

I don’t see how this expansion will help you, but go wild. The problem remains that you can’t separate the logic of the view from the schema it’s acting upon

dmux
>Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max.

The min/max aggregations over a period of time per category is pretty straight forward (aggregating rows), but what do you mean by "along with the other columns linked to min/max"? How would other columns come into play after aggregating rows?

emidln
Perhaps you're looking for a way of arranging SQL as an AST represented by data structures (or objects) that can be fed to a compiler. HoneySQL[0] is one such implementation of this idea and it makes your general transformation trivial for Clojure programs. You don't need to mess around with string concatenation because you have a predictable and extensible compiler for data structures (which are themselves easily composable/transformable/storable with Clojure) that you can trust to do the right thing. If you're using some weird database or need an esoteric syntax, extending the compiler to your clause is easy to do[1].

[0] https://github.com/seancorfield/honeysql

[1] https://github.com/seancorfield/honeysql#extensibility

fny
I've started to toy with this already and using a tool like sql parse to abstract existing queries.

Like say you have a query that applies to one table and potentially others where the column names are slightly different. Why cant you just port that query?

RedShift1
If you make the joining keys have the same name, you can use USING(col_foo) instead of table1.col_foo = table2.col_foo. It's one of the reasons I always use tablename_id as primary key name and foreign key name. Doesn't always work (for example 2 foreign keys linking to the same table but represent something different like created_by and modified_by).
michael_j_x
Feel you. I've long moved to jupyter notebooks as my de-facto database tool. I use python functions that generate the SQL, which I then execute in jupyter and load the results into pandas.
mechanical_bear
I’m raising my eyebrow here as far I can. Care to share what sort of use case this is for? Seems…elaborate.