Comment by remywang | Hacker News Reader

Comment by remywang | Hacker News Reader

Author here! This was mostly intended for entertainment, and partly to demonstrate the idea of "relations as vectors" and "queries as polynomials". But I guess I'll indulge myself a bit more:

> Just use sqldiff

sqldiff is sensitive to ordering, e.g., it'll say the relation [1, 2] is different from [2, 1] (I consider them to be the same because they are the same multiset). You'd need to sort with ORDER BY first, but that also requires listing all attributes explicitly like the GROUP BY solution (ORDER BY * doesn't work).

> What about CHECKSUM

It's also sensitive to ordering, and I was told different tables can have the same CHECKSUM (hash collisions?).

> Are the tables the same if they only differ by schema?

I'd say no. Perhaps a better definition of "the same" is that all SQL queries (using "textbook" SQL features) return the same result over the tables, if you just replace t1 with t2 in the query. Wait, but how do you know if the results are the same... :)

> There are better ways to compare tables

Absolutely, my recursive query runs in time O(N^N) so I'm sure you can be a little better than that.

> Perhaps a better definition of "the same" is that all SQL queries (using "textbook" SQL features) return the same result over the tables, if you just replace t1 with t2 in the query. Wait, but how do you know if the results are the same... :)

It's actually still a useful definition! (Assuming we're talking about all deterministic SQL queries and can define precisely what we mean by that!)

It's a useful definition because it includes all possible aggregations as well, including very onerous ones like ridiculous STRING_AGG stuff. Those almost certainly be candidates for reasonable queries to solve your original problem, but they are useful in benchmarking whether a proposed solution is accurate.

Not sure if Go code is your kind of thing. But if it is my colleague wrote a SQLite "diff" capability for our online SQLite hosting service:

https://github.com/sqlitebrowser/dbhub.io/blob/5c9e1ab1cfe0f...

The code there can also output a "merge" object out of the differences too, in order to merge the differences from one database object into another.

When you say "bag semantics" are you just referring to Set Theory? Is there a formal distinction between bags and sets?

With bag (multiset) semantics, {1, 2, 2} is different from {1, 2}. With set semantics, they're the same.

tl;dr: SQL hates you and it's your fault it hates you.

to elaborate: I'm rapidly appreciating that SQL is a "bondage and discipline" language where it is impossible for SQL to fail at any given task, it can only be failed by its users/developers.

edit: further, it occurs to me also that SQL hates you because in a sane language you'd be able to write up some generic method that says "compare every element of these collections to each other" that was reusable for all possible collections. But try defining a scalar user-defined function that takes two arbitrary resultsets as parameters. But not only can I not do that, I'm a bad person for wanting that because it violates Relational Theory.

You should definitely check out dbt :)

Some links in my comment: https://news.ycombinator.com/item?id=36911937

because it violates Relational Theory

It doesn't violate relational theory at all -- it merely violates the query compiler's requirements about what must be known at the time of query compilation.

You can absolute write a query that does what you want (you need a stored procedure that generates ad-hoc functions based on the involved tables' signatures), but stored sql objects must have defined inputs and outputs. What you're asking for is a meta-sql function that can translate to different query plans based on its inputs, and that's not allowed because its performance cannot be predetermined.

C has had this problem solved with preprocessor macros for 50 years.

Reply on news.ycombinator.com