Semantic Diff for SQL
github.com
Semantic Diff for SQL
1–10 of 34 posts
Re: Semantic Diff for SQL
#2What about difftastic?
Re: Semantic Diff for SQL
#3What about difftastic?
Difftastic seems like a really cool tool. There are a few reasons, however, why it doesn't apply well to use cases I had in mind:
1. It's in JS and not Python. These days a common data (including data tooling) stack revolves around Python and fitting JS into this ecosystem is not straightforwad.
2. Limited dialect support. As far as I can see it only supports "PostgreSQL flavor" (not sure what exactly is meant by "flavor" here). Support for dialects like Spark, Trino, Hive, etc SQL was crucial.
Definitely a worthy mention, though, thank you!
Re: Semantic Diff for SQL
#4I 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.
Re: Semantic Diff for SQL
#5I 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.
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.
Re: Semantic Diff for SQL
#6What about difftastic?
Difftastic seems like a really cool tool. There are a few reasons, however, why it doesn't apply well to use cases I had in mind: 1. It's in JS and not Python. These days a common data (including data tooling) stack revolves around Python and fitting JS into this ecosystem is not straightforwad. 2. Limited dialect support. As far as I can see it only supports "PostgreSQL flavor" (not sure what exactly is meant by "fl…
Difftastic's wiki also has a breakdown of some structured diff algorithms: https://github.com/Wilfred/difftastic/wiki/Structural-Diffs
(I've been working on a similar problem, effectively diffing an XML tree.)
Re: Semantic Diff for SQL
#7I 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.
[deleted]
Re: Semantic Diff for SQL
#8I 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.
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.
Re: Semantic Diff for SQL
#9I've been very impressed with sqlglot, and am looking forward to trying this feature. The only issue I've had with sqlglot is transpiling for use with a specific spark version: in my experience Spark is not great about surfacing obvious 'not registered' errors when a function isn't supported (especially in >=2.4). I ran into this with width_bucket, which is only in the most recent release. I am curious whether there's a straightforward way to write with a specific release and catch the error in transpilation rather than execution.
Side note: Iaroslav (post author) and Toby (sqlglot creator) are both amazing, and I'm so glad that they're working on open source projects like this.
Re: Semantic Diff for SQL
#10I've been very impressed with sqlglot, and am looking forward to trying this feature. The only issue I've had with sqlglot is transpiling for use with a specific spark version: in my experience Spark is not great about surfacing obvious 'not registered' errors when a function isn't supported (especially in >=2.4). I ran into this with width_bucket, which is only in the most recent release. I am curious whether there'…
It's easy to add errors for dialects by calling #unsupported when a function is used.
In terms of versioning of engines, I haven't implemented that yet, but presumably it could be done by adding a dialect subclass and having versioning route to it, so we could do something like parse(sql, dialect="spark", version=...) which could then route to a 2.3 version of spark.
Happy to chat more about this and we can see about adding it (or feel free to make a pr). You can DM me on twitter or some other avenue as well if you want to dive in deep.