Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sqlsmith: sql reducer #7504

Open
Tracked by #7329
kwannoel opened this issue Jan 20, 2023 · 6 comments
Open
Tracked by #7329

sqlsmith: sql reducer #7504

kwannoel opened this issue Jan 20, 2023 · 6 comments
Labels
difficulty/medium Issues that need some knowledge of the whole system good first issue Good for newcomers no-issue-activity priority/low

Comments

@kwannoel
Copy link
Contributor

kwannoel commented Jan 20, 2023 •
edited
Loading

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
@kwannoel kwannoel mentioned this issue Jan 20, 2023
Open
24 tasks
@github-actions github-actions bot added this to the release-0.1.16 milestone Jan 20, 2023
@kwannoel kwannoel removed this from the release-0.1.16 milestone Jan 20, 2023
@kwannoel
Copy link
Contributor Author

kwannoel commented Jan 20, 2023 •
edited
Loading

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.

@kwannoel kwannoel added good first issue Good for newcomers difficulty/medium Issues that need some knowledge of the whole system labels Jan 20, 2023
@xxchan
Copy link
Member

xxchan commented Jan 20, 2023

May refer to how QuickCheck shrinks 😄

@kwannoel
Copy link
Contributor Author

kwannoel commented Feb 9, 2023

@jon-chuang

@jon-chuang
Copy link
Contributor

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
Copy link
Member

xxchan commented Feb 9, 2023 •
edited
Loading

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: main...xxchan/shrink

@kwannoel kwannoel mentioned this issue Mar 13, 2023
Merged
7 tasks
@github-actions
Copy link
Contributor

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
difficulty/medium Issues that need some knowledge of the whole system good first issue Good for newcomers no-issue-activity priority/low
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@jon-chuang @xxchan @kwannoel