search

pganalyze / pg_query_go

Go library to parse and normalize SQL queries using the PostgreSQL query parser
BSD 3-Clause "New" or "Revised" License
642 stars 78 forks source link

About optimize result when query #110

Open hdlinh1808 opened 6 months ago

hdlinh1808 commented 6 months ago

When I parse this query: select vpc_id from aws_ec2_instance where not not not vpc_id IS NULL it returns a lot of bool operator, how I can remove redudant:

{"version":160001,"stmts":[{"stmt":{"SelectStmt":{"targetList":[{"ResTarget":{"val":{"ColumnRef":{"fields":[{"String":{"sval":"vpc_id"}}],"location":7}},"location":7}}],"fromClause":[{"RangeVar":{"relname":"aws_ec2_instance","inh":true,"relpersistence":"p","location":19}}],"whereClause":{"BoolExpr":{"boolop":"NOT_EXPR","args":[{"BoolExpr":{"boolop":"NOT_EXPR","args":[{"BoolExpr":{"boolop":"NOT_EXPR","args":[{"NullTest":{"arg":{"ColumnRef":{"fields":[{"String":{"sval":"vpc_id"}}],"location":54}},"nulltesttype":"IS_NULL","location":61}}],"location":50}}],"location":46}}],"location":42}},"limitOption":"LIMIT_OPTION_DEFAULT","op":"SETOP_NONE"}}}]}

Thanks

lfittl commented 6 months ago

Hi @hdlinh1808!

In order to remove the redundant NOT you would have to modify the parse tree so that the relevant parts are no longer present (I'd suggest comparing with the same query modified to your intended result).

Note that you would have to work with the deserialized version, i.e. Go structs (not JSON), returned by the Parse (not ParseToJSON), modify it and then call Deparse:

https://github.com/pganalyze/pg_query_go/blob/fdbe27d46499bf3f3e69efea749c15d378ff2749/pg_query.go#L40