Hacker News Reader

Comment by MarkusWinand | Hacker News Reader

The most important code management tool SQL has are views and the WITH clause.

https://modern-sql.com/use-case/literate-sql

Something like "polymorpihc" views would be extremley useful, but is not there yet (in standard SQL).

In other areas there are sublte features like the explicit WINDOW clause to avoid repeating similar OVER clauses. Breifly mentioned at the end of this doc:

https://www.postgresql.org/docs/current/tutorial-window.html

This was adopted in recent years by many FOSS-DBs, but not yet by the big commercial ones:

https://modern-sql.com/static/sm-blog-2019-postgesql-11.over...

Similarily the chaining of grouping sets can be helpful in those very rare cases you need them.

The Oracle Database intrdouced "SQL Macros" recently. For my taste, they are too 'rude' to make me happy on first sight (much like C #define):

https://blog.dbi-services.com/oracle-20c-sql-macros-a-scalar...

View on HN

Be careful with WITH clauses in Postgres though. Prior to Postgres 12, the engine always materialized queries inside the WITH clauses, even when its unnecessary. This can lead to certain queries being orders of magnitude slower than normal if written with a WITH clause.

Luckily 12 has been out a while now, and future versions will continue to not have this issue.

You don't need to follow this blindly though. Benchmark with EXPLAIN to see the loss/gain on performance vs. readability for your particular query.

Little tip: don't use indents for URLs. URLs are parsed automatically and need no special formatting.

Thx, updated.

> Similarily the chaining of grouping sets

Do you mean 'group by grouping sets'? If not, could you clarify.

Not only the grouping sets alone, the chaining of them.

E.g.

   GROUP BY tenant
          , GROUPING SETS ( (year, month)
                          , (year)
                          )

is equivalant to

   GROUP BY GROUPING SETS ( (tenant, year, month)
                          , (tenant, year)
                          )

and

   GROUP BY tenant, year
          , GROUPING SETS ( (month)
                          , ()
                          )

(however, I find the last one odd: I like to keep the "tenant" part seperate from the actual grupings you'd like to do).

Nice answer, thanks. Have you ever used this syntax? Has anybody here.

I ask because I'm a heavy SQL user when I'm working, and I've never ever found the need (almost used a cube once but not quite) (I guess it's maybe for analysis rather than transactional stuff).

Yeah, we use CUBE to create caches of aggregate data cut by various combinations of criteria, which allows us to keep UIs with lots of filters snappy, instead of having to hit a view.

I abused it in the past for aggregating every bitmask of CIDR, where the underlying data was by IP. So the groups were functions of the IP, one per mask level. We needed aggregations of arbitrary levels and this worked relatively well. We also tried it using it for some reporting that was tied to organizational hierarchy - same intuition - the group sets included org, parent, grandparent, etc ...

What do you mean by "polymorphic views"? And you seem to imply they are implemented somewhere other than standard SQL, is that correct?

The polymorphic view as I would like them would enable us to re-use entiere <query expressions> (i.e. "subqueries") applied to different data sources (i.e. "tables").

E.g (made-up syntax)

   CREATE TEMPLATE VIEW numbered_by_ts(t1) AS (
     SELECT t1.*
          , ROW_NUMBER() OVER(ORDER BY ts) AS rn
       FROM t1
   )

e.g. use it like this:

   SELECT *
     FROM numbered_by_ts(table_name);

I think they should behave more like C++ templates rather than C pre-processor makros[1], thus I used the keyword TEMPLATE in CREATE VIEW. If you provide a table that doesn't have a column TS, it would be a syntax error, just like it is with C++ templates.

With common table expressions it would be easily possible to inject entire subqueries into the template view:

   WITH cte AS (SELECT ... FROM ...)
   SELECT *
     FROM numbered_by_ts(cte);

For completness, WITH queries should also be allowed to be TEMPLATEs.

Speaking of the WITH clause, SQLite support ploymorphic views because SQLite CTEs are visible in elements that are "generally contained"[2] in the statement (the SQL standard defines it differently[3]).

Example:

   CREATE TABLE t1 (
    ts TIMESTAMP
   );
   INSERT INTO t1 VALUES ('2000-01-01 00:00:00');

   CREATE VIEW numbered_by_ts AS
   SELECT t1.*
        , ROW_NUMBER() OVER(ORDER BY ts) AS rn
     FROM t1
   ;

   CREATE TABLE ta (
    ts TIMESTAMP
   );
   INSERT INTO ta VALUES ('2010-01-01 00:00:00');

   CREATE TABLE tb (
    no_ts TIMESTAMP
   );
   INSERT INTO tb VALUES ('2020-01-01 00:00:00');

   SELECT * FROM numbered_by_ts;
   -- accesses t1, resturns year 2000

   WITH t1 AS (SELECT * FROM ta)
   SELECT * FROM numbered_by_ts;
   -- accesses ta, resturns year 2010

   WITH t1 AS (SELECT * FROM tb)
   SELECT * FROM numbered_by_ts;
   -- syntax error: no "ts" column in tb

SQLite is the only system I know of that implements WITH like that. See "views bypass with" here: https://modern-sql.com/feature/with#compatibility

The polymorpic table functions, as introduced by SQL:2016, might be able to accomplish all of that but for me they feel like using a sledgehamer for cracking a nut.

[1] As far as I understand the Oracle 20c SQL makros behave like C pre-processor makros.

[2] "Generally contain" as defined by "Syntactic containment" in ISO/IEC 9075-1. The 2011 version of that can be downloaded for free at ISO: http://standards.iso.org/ittf/PubliclyAvailableStandards/c05...

[3] ISO/IEC 9075-2, "<query expression>": the definition of "query name in scope" says "contained", not "generally contained".

Thank you! Very surprising behavior from SQLite, although it might come in handy if I use it carefully.

Although I ask "why should macros have to return complete query expressions?" To me, something like the following would be much more flexible.

Example 1, %ts_number returns a single numeric expression:

  CREATE MACRO %ts_number(x) AS
    ROW_NUMBER() OVER(ORDER BY x.ts)

  select x.*, %ts_number(x) as rn
  from SomeTable x

Example 2, %%foo returns 3 clauses to be combined into a larger query:

  CREATE MACRO %%foo(x) AS
    WHERE x.ts > 0
    ORDER BY ROW_NUMBER() OVER (ORDER BY x.ts)
    SELECT ROW_NUMBER() OVER (ORDER BY x.ts) as rn

  select x.*
  from SomeTable x
  %%foo(x) -- this combines the 3 clauses into this query

  -- So the result is equivalent to
  select x.*, ROW_NUMBER() OVER (ORDER BY x.ts) as rn
  from SomeTable x
  WHERE x.ts > 0
  ORDER BY ROW_NUMBER() OVER (ORDER BY x.ts)

I suppose it could even be up to the macro how its clauses get combined (before or after any existing clauses). In the following example, `...` is a special language element for use in macros. %%foo would append to the end of all the existing WHERE and ORDER BY clauses, but it would prepend to the existing SELECT clauses:

  CREATE MACRO %%foo(x) AS
    WHERE ... AND x.ts > 0
    ORDER BY ..., ROW_NUMBER() OVER (ORDER BY x.ts)
    SELECT ROW_NUMBER() OVER (ORDER BY x.ts) as rn, ...

Reply on news.ycombinator.com