Hacker News Reader

Comment by karmakaze | Hacker News Reader

I thought this was going to be something else like being able to tell that a rewritten query returns the same set of rows, but with potentially a very different query plan. E.g. dependent EXISTS subquery vs IN subquery.

View on HN

This is what the sqlglot optimizer is used for. The optimizer converts EXISTS and IN into a canonicalized SQL (some variant of left join) which can then be compared to another query.

So if you run the optimizer first and then the diff tool, it could solve this kind of use case.

This exists for Postgres, let me try to find the name of the tool

EDIT: I can't find it, I searched for 30 mins, I promise this exists though. If anyone else can remember the name of it, please post.

Pg_query maybe?

https://pganalyze.com/blog/pg-query-2-0-postgres-query-parse...

Scroll down to the section marked "Fingerprints in...".

As far as I can tell, pg_query would treat the equivalent EXISTS and IN (...) forms as different queries with different fingerprints, which seems to fit it's intended use. The github README says:

> Usage: Fingerprinting a query

> Fingerprinting allows you to identify similar queries that are different only because of the specific object that is being queried for (i.e. different object ids in the WHERE clause), or because of formatting.

Pretty cool that we can get the parse tree that PostgreSQL would make from Go or Ruby via the library.

Reply on news.ycombinator.com