sqlsmith: sql reducer

[kwannoel]

Usecase

Generated queries can get rather nested and complex. This means they have to be shrunk in order to pinpoint the root cause of bugs.

Rough steps of manual sql reducing, starting with innermost subqueries:

• 1st phase: Replace columns with scalar values
  • Removes correlation of subquery with outer query.
  • Sometimes error only surfaces with correlation, in that case leave it.
  • This also means we need to know what the types of each select item is.
• 2nd phase: Remove ORDER BY
• 3rd phase: Remove GROUP BY
• 4th phase: Remove WHERE
• 5th phase: Remove FROM
• ...

As you can see the steps are fairly mechanical, and can be automated.

We can either find an existing implementation, or implement a barebones version ourselves, if it's not too much effort.

Usage

1. Input failing ddl + sql query and phase.
2. output shrunk query after 1 shrinking step. Output must be valid sql.

Existing implementations

• https://github.com/jonamuen/sql-reduce
• https://github.com/sqlancer/sqlancer/pull/624

[kwannoel]

Not a high priority, since sqlsmith work is wrapping up, and many bugs are already caught and fixed. But I think it could be interesting to implement one, and still moderately useful, as new features are added to RW and tested in sqlsmith.

Creating an issue in case someone is interested in it.

[xxchan]

May refer to how QuickCheck shrinks 😄

[kwannoel]

@jon-chuang

[jon-chuang]

output shrunk query after 1 shrinking step. Output must be valid sql.

Let's speculatively try to remove instead of just 1 step. We should speculatively remove things until removing something will no longer produce the panic.

[xxchan]

I found actually I have played with this idea very long time ago, but not sure whether it works. 🥵 Just post it here for reference: https://github.com/risingwavelabs/risingwave/compare/main...xxchan/shrink

[github-actions[bot]]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.