31–34 of 34 posts
I wonder if this is a topical thread to check if anyone is aware of a Java based solution to parse a CREATE VIEW statement to get a mapping between the view columns and the corresponding source table columns. I checked out jsqlparser[0] and it does produce an AST which can be parsed using the visitor-pattern[1] but was wondering if there is a more "out-of-the-box" solution involving less work. Due to various reasons,…
Earlier quoted context omitted.
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...".
> 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.
> (a + b) => (b + a) > Semantically the query hasn’t changed Now hang on a minute. Extend that to 3 and it can be (mssql but true in any I guess): declare @hi int = 2147483647; declare @lo int = -2147483648; declare @x int = @hi + @lo + @hi; -- ok declare @y int = @hi + @hi + @lo; -- 'Arithmetic overflow error' Worse yet with floats. I see what you're saying and good stuff, I'm thinking about this myself and I apprec…
@hi + @lo + @hi means (@hi + @lo) + @hi
@hi + @hi + @lo means (@hi + @hi) + @lo
Just because you can write this without the parentheses does not mean you can ignore them. To get from one to the other need not just the commutativity rule relied on by the diffing algorithm but also the associativity rule:
(@hi + @lo) + @hi =assoc=> @hi + (@lo + @hi) =comm=> @hi + (@hi + @lo) =assoc=> (@hi + @hi) + @lo
Here the third (associativity) change introduces the overflow, not the commutativity change which should always be safe for both integers and floating point numbers.
> (a + b) => (b + a) > Semantically the query hasn’t changed Now hang on a minute. Extend that to 3 and it can be (mssql but true in any I guess): declare @hi int = 2147483647; declare @lo int = -2147483648; declare @x int = @hi + @lo + @hi; -- ok declare @y int = @hi + @hi + @lo; -- 'Arithmetic overflow error' Worse yet with floats. I see what you're saying and good stuff, I'm thinking about this myself and I apprec…
Associativity and communtativity are different things. @hi + @lo + @hi means (@hi + @lo) + @hi @hi + @hi + @lo means (@hi + @hi) + @lo Just because you can write this without the parentheses does not mean you can ignore them. To get from one to the other need not just the commutativity rule relied on by the diffing algorithm but also the associativity rule: (@hi + @lo) + @hi =assoc=> @hi + (@lo + @hi) =comm=> @hi + (…
His example included SUM(b + c) and SUM(c + b) as equivalent. As such it probably is[1] but extend it to 3 numbers and you can't rearrange, as I think we agree.
[1] assuming b and c are numbers not strings, as + is string concat in some dialects