Hacker News Reader

MarkusWinandon PostgreSQL 11 Reestablishes Window Functions Leadership

In principle yes, it is even mentioned in footnotes of the matrix (2, 3).

MarkusWinandon Ask HN: How to get better in advanced SQL

Since you ased about indexing in particular, here is my site about indexing:

https://use-the-index-luke.com/

You might also want to follow my other project:

https://modern-sql.com/

MarkusWinandon SQLite Release 3.25.0 adds support for window functions

I've just built the snapshot. The binary is still less than 2MB.

And it works:

  sqlite> with t(x) as (values (1), (2)) select sum(x) over (order by x) from t;
  1
  3

MarkusWinandon How PostgreSQL’s SQL dialect stays ahead of its competitors [PDF slides]

Thanks for pointing out!

The syntax used by SQL Server doesn't follow the standard. Or, in other words, the standard syntax gives an error in SQL server. However, I'll note that there is an alternative syntax in future versions of these slides.

Thanks again.

MarkusWinandon Important database news from the last few months

SQL uses nesting as you have shown instead of a "pipe" (think of it like functional programming).

The difference is just the syntax, not the expressiveness.

MarkusWinandon Important database news from the last few months

Just FYI: The SQL standard supports both types. The other one (valid time) is called "application versioning" in SQL.

Read this paper if you'd like to learn more about it: http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeat...

MarkusWinandon Important database news from the last few months

Ah, "dozen of extra lines" is not exactly what I was expecting for "isn't as powerful" ;)

MarkusWinandon Important database news from the last few months

> For one, there's no official specification

I'd take ISO/IEC 9075 as an official specification: https://webstore.iec.ch/publication/59685

> and isn't as powerful as I'd always want it to be

Do you have examples? There is a lot in SQL that is not commonly known—if you let me know what you are thinking of, I might be able to show you an adequate SQL feature.

MarkusWinandon How Oracle's Acquisition Was Actually the Best Thing to Happen to MySQL

I've recently blogged something in the same vein.

“MySQL is under new management since Oracle bought it through Sun. I must admit: it might have been the best thing that happened to SQL in the past 10 years, and I really mean SQL—not MySQL.”

https://modern-sql.com/blog/2018-04/mysql-8.0

MarkusWinandon Usql – Universal command-line interface for SQL databases

This is something we have to blame the vendors for.

There is an international SQL standard from ISO, it's just not commonly followed.

However, sometimes the standard isn't very useful in itself. For this example, FETCH FIRST x ROWS ONLY is the syntax mandated by the standard. Although some databases accept this in the meanwhile, LIMIT might have been a better choice as it is supported by more databases.

https://www.slideshare.net/MarkusWinand/modern-sql/120

Edit: ps.: Please don't use w3schools.com as a SQL reference. It's utterly outdated, prefers vendor syntax over standard and is sometimes just straight wrong.

MarkusWinandon One Giant Leap for SQL: MySQL 8.0 Released

Full quote.

> MySQL is very popular. According to db-engines.com, it’s the second most popular SQL database overall. More importantly: it is, by a huge margin, the most popular free SQL database.

The second sentence is linked: https://db-engines.com/en/ranking

The third sentence refers to the same source.

MarkusWinandon One Giant Leap for SQL: MySQL 8.0 Released

In SQL:2016, it is

> 4.24.13 Known functional dependencies in the result of a <group by clause>

Not that this is about the result of a <group by clause>

edit: The matrix I have in the article is not complete. I've just picked a few examples from the standard and checked them to get an overview. I guess the fact that PostgreSQL supports some of them but MySQL more of them is well represented in the matrix.

The full text is below. I'm happy to correct, when I'm interpreting this wrong (which is perfectly possible).

Let T1 be the table that is the operand of the <group by clause>, and let R be the result of the <group by clause>. Let G be the set of columns specified by the <grouping column reference list> of the <group by clause>, after applying all syntactic transformations to eliminate ROLLUP, CUBE, and GROUPING SETS. The columns of R are the columns of G, with an additional column CI, whose value in any particular row of R somehow denotes the subset of rows of T1 that is associated with the combined value of the columns of G in that row. If every element of G is a column reference to a known not null column, then G is a BUC-set of R. If G is a subset of a BPK-set of columns of T1, then G is a BPK-set of R. G ↦ CI is a known functional dependency in R.

MarkusWinandon One Giant Leap for SQL: MySQL 8.0 Released

The adoption of standard SQL is really poor on many SQL products. I mentioned that in the article:

> I believe the main reasons are a lack of knowledge and interest among developers along with poor support for modern SQL in database products.

However, it IS getting better at the moment. Have you seen the "New SQL" systems that implement window functions? MySQL is finally adding those features and MariaDB picks other features from the standard.

There is movement.

I'll just keep on making my comparison matrices to make the differences visible. This will (hopefully) increase developers interest, which is basically a market demand. Vendors listen to market demand. evil grin

MarkusWinandon One Giant Leap for SQL: MySQL 8.0 Released

> It was a bit depressing to see the solid column of Xs by SQLite

Except WITH [RECURSIVE] which is in SQLite since early 2014. Damn, I forgot to use that trivia in my MySQL bashing... ;)

Other than that, I met Richard Hipp in person some years back. He is a VERY pragmatic guy. I wrote an article about it:

https://use-the-index-luke.com/blog/2014-05/what-i-learned-a...

MarkusWinandon One Giant Leap for SQL: MySQL 8.0 Released

A big red X doesn't mean the database doesn't have other, similar but proprietary functionality. It just means: it doesn't support this function in a standard confirming way yet.

There is nothing against using non-standard functionality in that case. In this particular case. PostgreSQL got JSON support (~2012?) long before it was added to the standard (2016). Obviously, their JSON functions differ from the standard (as they didn't lobby their functions into the standard).

However, slowly but surely database will add the standard functionality, which makes life easier.

Having that said, the standard JSON_OBJECTAGG is more powerful as it gives you control over null handling (NULL ON NULL -vs- ABSENT ON NULL) and allows checking for duplicate keys ([(WITH|WITHOUT) UNIQUE KEYS]).

MarkusWinandon One Giant Leap for SQL: MySQL 8.0 Released

The purpose of the example is to demonstrate functional dependencies based on grouped tables.

MarkusWinandon One Giant Leap for SQL: MySQL 8.0 Released

Those gory details I've mentioned in the article.

This is how standard SQL JSON_OBJECTAGG works: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#js...

It can actually do much more:

<JSON object aggregate constructor> ::= JSON_OBJECTAGG <left paren> <JSON name and value> [ <JSON constructor null clause> ] [ <JSON key uniqueness constraint> ] [ <JSON output clause> ] <right paren>

This free technical report of ISO has some examples: http://standards.iso.org/ittf/PubliclyAvailableStandards/c06...

MarkusWinandon One Giant Leap for SQL: MySQL 8.0 Released

https://www.postgresql.org/search/?q=json_objectagg

But there is a patch pending for this. Not sure if this gonna be included in 11.

MarkusWinandon One Giant Leap for SQL: MySQL 8.0 Released

Hi!

The first feature matrix you see is about checking of functional dependencies.

For example, PostgreSQL doesn't see the functional dependency between the columns ID and MA in the result of this query.

    SELECT id
         , max(a) ma
      FROM ...
     GROUP BY id

As a consequence, this query doesn't work, because the outer query refers to MA which is not in the GROUP BY clause of the outer query:

    SELECT COUNT(*) cnt
         , ma
      FROM (SELECT id
                 , max(a) ma
              FROM ...
             GROUP BY id) x
      GROUP BY id

Detecting these things makes life easier.

MarkusWinandon Pivot – Rows to Columns

The example still groups by YEAR. So you won't need more than 12 months.

Regardless: SQL is a statically typed language. Also the type of the result—i.e. the names and types of the columns—must be known in advance. Some databases offer extensions for "dynamic pivot".

MarkusWinandon Standard SQL features where PostgreSQL beats its competitors

Welcome :)

MarkusWinandon Standard SQL features where PostgreSQL beats its competitors

I'm sorry it is not so obvious: there is a difference between red, which means it doesn't work, and transparent, which means I didn't test any further back.

MarkusWinandon SQL Indexing and Tuning E-Book for Developers

It is absolutely possible.

On the other hand: it's also possible that nobody would know me without the website.

Further: I've learned so much from free online resources, why not giving something back?

I know for sure that many people buy the book because they started reading online. Also: A lot of the training I do is sold because ONE participant has read the site/book and arranges a training for the department.

TL;DR: I works fine for me.

MarkusWinandon SQL Indexing and Tuning E-Book for Developers

I'm selling it as book.

But more importantly: the "fame" sells other services. Look here: https://winand.at/

MarkusWinandon Three-Valued Logic

This is a controversy outside of SQL.

SQL is pretty clear what null is:

> “Every [SQL] data type includes a special value, called the null value,”[0] “that is used to indicate the absence of any data value”[1]

(http://modern-sql.com/concept/null)

[0] SQL:2011-1: §4.4.2 [1] SQL:2011-1: §3.1.1.12

MarkusWinandon Three-Valued Logic

*Edit: exceptions :)

MarkusWinandon Three-Valued Logic

Just my 2c from SQL standards perspective:

> “Every [SQL] data type includes a special value, called the null value,” “that is used to indicate the absence of any data value”.

(http://modern-sql.com/concept/null)

For the Boolean type, null = unknown:

> Note that the truth value unknown is indistinguishable from the null for the Boolean type. Otherwise, the Boolean type would have four logical values.

(http://modern-sql.com/concept/three-valued-logic)

Although they are very closely related, I believe the general `null` value (for any data type) and three-valued logic (null for Boolean) should be separated (that's why I have two articles).

MarkusWinandon Three-Valued Logic

Yes, because it is a null reference.

SQL's null is a value. The difference is that processing null values does not cause exceptions in SQL.

MarkusWinandon The Case for Learned Index Structures

I'd say this is a generally misleading statement, but if you like it is right in some context.

In particular, one could argue that it is true for clustered indexes (e.g. in SQL Server or MySQL/InnoDB). In that case the table is actually in an ordered fashion and happens to be identical to the leaf-node-level of the index. However, if you argue that this level doesn't belong to the index but is just the payload, the level above the leaf nodes is as they say: it has one pointer per page.

MarkusWinandon The 3-minute SQL indexing quiz: Can you spot the five most common mistakes?

In that case, everything below 5/5 would be very bad ;)