# Will Your Cassandra Database Project Succeed?: The New Stack [Blog](/blog/)>[Technology](/blog/category/technical/)>SQLreduce: Reduce Verbose SQL Queries to Minimal Examples SQLreduce: Reduce Verbose SQL Queries to Minimal Examples ========================================================= September 15, 2022 | By [ Instaclustr ](https://www.instaclustr.com/blog/author/instaclustr/) [ ](https://x.com/intent/tweet?text=SQLreduce:%20Reduce%20Verbose%20SQL%20Queries%20to%20Minimal%20Examples&url=https://www.instaclustr.com/blog/sqlreduce-reduce-verbose-sql-queries-to-minimal-examples/) [ ](https://www.linkedin.com/shareArticle?mini=true&url=https://www.instaclustr.com/blog/sqlreduce-reduce-verbose-sql-queries-to-minimal-examples/&title=&summary=SQLreduce:%20Reduce%20Verbose%20SQL%20Queries%20to%20Minimal%20Examples&source=) Developers often face very large SQL queries that raise some errors. SQLreduce is a tool to reduce that complexity to a minimal query. The Problem ----------- Often a developer will have extremely large queries that will fail either during creation, or perhaps when a server migration or data change has happened. It can be difficult to parse a 100-line SQL statement to determine where the error occurred, so this tool will help you to isolate the cause of the bug. Reduce Complexity With SQLreduce -------------------------------- This issue is solved by [SQLreduce](https://github.com/credativ/sqlreduce). SQLreduce takes as input an arbitrary SQL query which is then run against a PostgreSQL server. Various simplification steps are applied, checking after each step that the simplified query still triggers the same error from PostgreSQL. The end result is a SQL query with minimal complexity. Note that SQLreduce does not try to derive a query that is semantically identical to the original, or produces the same query result—the input is assumed to be faulty, and we are looking for the minimal query that produces the same error message from PostgreSQL when run against a database. If the input query happens to produce no error, the minimal query output by SQLreduce will just be SELECT. How It Works ------------ We’ll use a complicated query to demonstrate how SQLreduce works and which steps are taken to remove noise from the input. The query is bogus and contains a bit of clutter that we want to remove: $ psql -c 'select pg\_database.reltuples / 1000 from pg\_database, pg\_class where 0 < pg\_database.reltuples / 1000 order by 1 desc limit 10' ERROR: column pg\_database.reltuples does not exist 1 2 $ psql -c 'select pg\_database.reltuples / 1000 from pg\_database, pg\_class where 0 < pg\_database.reltuples / 1000 order by 1 desc limit 10' ERROR: column pg\_database.reltuples does not exist Let’s pass the query to SQLreduce: $ sqlreduce 'select pg\_database.reltuples / 1000 from pg\_database, pg\_class where 0 < pg\_database.reltuples / 1000 order by 1 desc limit 10' 1 $ sqlreduce 'select pg\_database.reltuples / 1000 from pg\_database, pg\_class where 0 < pg\_database.reltuples / 1000 order by 1 desc limit 10' SQLreduce starts by parsing the input using [pglast](https://github.com/lelit/pglast) and [libpg\_query](https://github.com/pganalyze/libpg_query) which expose the original PostgreSQL parser as a library with Python bindings. The result is a parse tree that is the basis for the next steps. The parse tree looks like this: selectStmt ├── targetList │ └── / │ ├── pg\_database.reltuples │ └── 1000 ├── fromClause │ ├── pg\_database │ └── pg\_class ├── whereClause │ └── < │ ├── 0 │ └── / │ ├── pg\_database.reltuples │ └── 1000 ├── orderClause │ └── 1 └── limitCount └── 10 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 selectStmt ├── targetList │ └── / │ ├── pg\_database.reltuples │ └── 1000 ├── fromClause │ ├── pg\_database │ └── pg\_class ├── whereClause │ └── < │ ├── 0 │ └── / │ ├── pg\_database.reltuples │ └── 1000 ├── orderClause │ └── 1 └── limitCount └── 10 Pglast also contains a query renderer that can render back the parse tree as SQL, shown as the regenerated query below. The input query is run against PostgreSQL to determine the result, in this case ERROR: column pg\_database.reltuples does not exist. Input query: select pg\_database.reltuples / 1000 from pg\_database, pg\_class where 0 < pg\_database.reltuples / 1000 order by 1 desc limit 10 Regenerated: SELECT pg\_database.reltuples / 1000 FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ORDER BY 1 DESC LIMIT 10 Query returns: ✔ ERROR: column pg\_database.reltuples does not exist Input query: select pg\_database.reltuples / 1000 from pg\_database, pg\_class where 0 < pg\_database.reltuples / 1000 order by 1 desc limit 10 Regenerated: SELECT pg\_database.reltuples / 1000 FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ORDER BY 1 DESC LIMIT 10 Query returns: ✔ ERROR: column pg\_database.reltuples does not exist 1 2 3 4 5 6 Input query: select pg\_database.reltuples / 1000 from pg\_database, pg\_class where 0 < pg\_database.reltuples / 1000 order by 1 desc limit 10 Regenerated: SELECT pg\_database.reltuples / 1000 FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ORDER BY 1 DESC LIMIT 10 Query returns: ✔ ERROR: column pg\_database.reltuples does not exist Input query: select pg\_database.reltuples / 1000 from pg\_database, pg\_class where 0 < pg\_database.reltuples / 1000 order by 1 desc limit 10 Regenerated: SELECT pg\_database.reltuples / 1000 FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ORDER BY 1 DESC LIMIT 10 Query returns: ✔ ERROR: column pg\_database.reltuples does not exist SQLreduce works by deriving new parse trees that are structurally simpler, generating SQL from that, and running these queries against the database. The first simplification steps work on the top level node, where SQLreduce tries to remove whole subtrees to quickly find a result. The first reduction tried is to remove LIMIT 10: SELECT pg\_database.reltuples / 1000 FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ORDER BY 1 DESC ✔ 1 SELECT pg\_database.reltuples / 1000 FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ORDER BY 1 DESC ✔ The query result is still ERROR: column pg\_database.reltuples does not exist, indicated by a ✔ check mark. Next, ORDER BY 1 is removed, again successfully: SELECT pg\_database.reltuples / 1000 FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ✔ 1 SELECT pg\_database.reltuples / 1000 FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ✔ Now the entire target list is removed: SELECT FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ✔ 1 SELECT FROM pg\_database, pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ✔ This shorter query is still equivalent to the original regarding the error message returned when it is run against the database. Now the first unsuccessful reduction step is tried, removing the entire FROM clause: SELECT WHERE 0 < ((pg\_database.reltuples / 1000)) ✘ ERROR: missing FROM-clause entry for table "pg\_database" 1 SELECT WHERE 0 < ((pg\_database.reltuples / 1000)) ✘ ERROR: missing FROM-clause entry for table "pg\_database" That query is also faulty, but triggers a different error message, so the previous parse tree is kept for the next steps. Again a whole subtree is removed, now the WHERE clause: SELECT FROM pg\_database, pg\_class ✘ no error 1 SELECT FROM pg\_database, pg\_class ✘ no error We have now reduced the input query so much that it doesn’t error out any more. The previous parse tree is still kept which now looks like this: selectStmt ├── fromClause │ ├── pg\_database │ └── pg\_class └── whereClause └── < ├── 0 └── / ├── pg\_database.reltuples └── 1000 1 2 3 4 5 6 7 8 9 10 selectStmt ├── fromClause │ ├── pg\_database │ └── pg\_class └── whereClause └── < ├── 0 └── / ├── pg\_database.reltuples └── 1000 Now SQLreduce starts digging into the tree. There are several entries in the FROM clause, so it tries to shorten the list. First, pg\_database is removed, but that doesn’t work, so pg\_class is removed: SELECT FROM pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ✘ ERROR: missing FROM-clause entry for table "pg\_database" SELECT FROM pg\_database WHERE 0 < ((pg\_database.reltuples / 1000)) ✔ 1 2 SELECT FROM pg\_class WHERE 0 < ((pg\_database.reltuples / 1000)) ✘ ERROR: missing FROM-clause entry for table "pg\_database" SELECT FROM pg\_database WHERE 0 < ((pg\_database.reltuples / 1000)) ✔ Since we have found a new minimal query, recursion restarts at top-level with another try to remove the WHERE clause. Since that doesn’t work, it tries to replace the expression with NULL, but that doesn’t work either. SELECT FROM pg\_database ✘ no error SELECT FROM pg\_database WHERE NULL ✘ no error 1 2 SELECT FROM pg\_database ✘ no error SELECT FROM pg\_database WHERE NULL ✘ no error Now a new kind of step is tried: expression pull-up. We descend into WHERE clause, where we replace A < B first by A and then by B. SELECT FROM pg\_database WHERE 0 ✘ ERROR: argument of WHERE must be type boolean, not type integer SELECT FROM pg\_database WHERE pg\_database.reltuples / 1000 ✔ SELECT WHERE pg\_database.reltuples / 1000 ✘ ERROR: missing FROM-clause entry for table "pg\_database" 1 2 3 SELECT FROM pg\_database WHERE 0 ✘ ERROR: argument of WHERE must be type boolean, not type integer SELECT FROM pg\_database WHERE pg\_database.reltuples / 1000 ✔ SELECT WHERE pg\_database.reltuples / 1000 ✘ ERROR: missing FROM-clause entry for table "pg\_database" The first try did not work, but the second one did. Since we simplified the query, we restart at top-level to check if the FROM clause can be removed, but it is still required. From A / B, we can again pull up A: SELECT FROM pg\_database WHERE pg\_database.reltuples ✔ SELECT WHERE pg\_database.reltuples ✘ ERROR: missing FROM-clause entry for table "pg\_database" 1 2 SELECT FROM pg\_database WHERE pg\_database.reltuples ✔ SELECT WHERE pg\_database.reltuples ✘ ERROR: missing FROM-clause entry for table "pg\_database" SQLreduce has found the minimal query that still raises ERROR: column pg\_database.reltuples does not exist with this parse tree: selectStmt ├── fromClause │ └── pg\_database └── whereClause └── pg\_database.reltuples 1 2 3 4 5 selectStmt ├── fromClause │ └── pg\_database └── whereClause └── pg\_database.reltuples At the end of the run, the query is printed along with some statistics: Minimal query yielding the same error: SELECT FROM pg\_database WHERE pg\_database.reltuples Pretty-printed minimal query: SELECT FROM pg\_database WHERE pg\_database.reltuples Seen: 15 items, 915 Bytes Iterations: 19 Runtime: 0.107 s, 139.7 q/s 1 2 3 4 5 6 7 8 9 10 11 Minimal query yielding the same error: SELECT FROM pg\_database WHERE pg\_database.reltuples Pretty-printed minimal query: SELECT FROM pg\_database WHERE pg\_database.reltuples Seen: 15 items, 915 Bytes Iterations: 19 Runtime: 0.107 s, 139.7 q/s This minimal query can now be inspected to fix the bug in PostgreSQL or in the application. This blog was originally published on credativ.com [ Add Instaclustr as a preferred source on Google ](https://google.com/preferences/source?q=instaclustr.com) ![mail icon]()#### Get the latest articles for open sourceIn your inbox Sign up now ### Related content [ Zero Downtime Migration to Instaclustr Yes, we can migrate existing Cassandra clusters to Instaclustr without any downtime. Here's what to expect from the process... ](https://www.instaclustr.com/blog/zero-downtime-migration-to-instaclustr/) [ Workflow Comparison: Uber Cadence vs Netflix Conductor When choosing what’s right for your company’s opensource workflow needs it is important to know the difference and similarities ... ](https://www.instaclustr.com/blog/workflow-comparison-uber-cadence-vs-netflix-conductor/) [ Will Your Cassandra Database Project Succeed?: The New Stack Open source Apache Cassandra® continues to stand out as an enterprise-proven solution for organizations seeking high availability... ](https://www.instaclustr.com/blog/will-your-cassandra-database-project-succeed-the-new-stack/) ×Sign upto ourNewsletter -----------------------