[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-announce
Subject: SQLreduce: Reduce verbose SQL queries to minimal examples
From: credativ GmbH via PostgreSQL Announce <announce-noreply () postgresql ! org>
Date: 2022-03-09 17:30:36
Message-ID: 164684703662.1369298.1462374323276330699 () wrigleys ! postgresql ! org
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
[SQLsmith](https://github.com/anse1/sqlsmith) has proven to be an effective
tool for finding bugs in different areas in the PostgreSQL server and other
products, including security bugs, ranging from executor bugs to segfaults in
type and index method implementations.
However, the random queries generated by SQLsmith that trigger some error are
most often very large and contain a lot of noise that does not contribute to
the error. So far, manual inspection of the query and tedious editing was
required to reduce the example to a minimal reproducer that developers can use
to fix the problem.
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.
SQLreduce is effective at reducing the queries from
[original error reports from \
SQLsmith](https://github.com/anse1/sqlsmith/wiki#score-list) to queries that match \
manually-reduced queries.
More details on [how it works in a blog \
post](https://www.credativ.de/en/blog/postgresql/sqlreduce-reduce-verbose-sql-queries-to-minimal-examples/).
# Example
In 2018,
[SQLsmith found a segfault](https://www.postgresql.org/message-id/87woxi24uw.fsf@ansel.ydns.eu)
in PostgreSQL running Git revision 039eb6e92f. The reproducer back then was a huge \
40-line, 2.2kB query:
```
select
case when pg_catalog.lastval() < pg_catalog.pg_stat_get_bgwriter_maxwritten_clean() \
then case when pg_catalog.circle_sub_pt( cast(cast(null as circle) as circle),
cast((select location from public.emp limit 1 offset 13)
as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol \
from public.brintest limit 1 offset 2) then (select f1 from public.circle_tbl limit \
1 offset 4) else (select f1 from public.circle_tbl limit 1 offset 4)
end,
case when (select pg_catalog.max(class) from public.f_star)
~~ ref_0.c then cast(null as circle) else cast(null as circle) end
) as circle) then ref_0.a else ref_0.a end
else case when pg_catalog.circle_sub_pt(
cast(cast(null as circle) as circle),
cast((select location from public.emp limit 1 offset 13)
as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol \
from public.brintest limit 1 offset 2) then (select f1 from public.circle_tbl limit \
1 offset 4) else (select f1 from public.circle_tbl limit 1 offset 4)
end,
case when (select pg_catalog.max(class) from public.f_star)
~~ ref_0.c then cast(null as circle) else cast(null as circle) end
) as circle) then ref_0.a else ref_0.a end
end as c0,
case when (select intervalcol from public.brintest limit 1 offset 1)
>= cast(null as "interval") then case when ((select pg_catalog.max(roomno) \
from public.room)
!~~ ref_0.c)
and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
else case when ((select pg_catalog.max(roomno) from public.room)
!~~ ref_0.c)
and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
end as c1,
ref_0.a as c2,
(select a from public.idxpart1 limit 1 offset 5) as c3,
ref_0.b as c4,
pg_catalog.stddev(
cast((select pg_catalog.sum(float4col) from public.brintest)
as float4)) over (partition by ref_0.a,ref_0.b,ref_0.c order by ref_0.b) as \
c5, cast(nullif(ref_0.b, ref_0.a) as int4) as c6, ref_0.b as c7, ref_0.c as c8
from
public.mlparted3 as ref_0
where true;
```
SQLreduce can effectively reduce that monster to just this:
```
SELECT pg_catalog.stddev(NULL) OVER () AS c5 FROM public.mlparted3 AS ref_0
```
# Availability
SQLreduce is open source licensed under the MIT license. The source code is on \
GitHub: https://github.com/credativ/sqlreduce
Debian/Ubuntu packages for sqlreduce are shipped on \
[apt.postgresql.org](https://apt.postgresql.org).
SQLreduce is an open source product by [credativ GmbH](https://www.credativ.de/).
[Attachment #5 (text/html)]
<!doctype html>
<html>
<head>
<meta name="viewport" content="width=device-width">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>SQLreduce: Reduce verbose SQL queries to minimal examples</title>
<style>
@media only screen and (max-width: 620px) {
table[class=body] h1 {
font-size: 28px !important;
margin-bottom: 10px !important;
}
table[class=body] p,
table[class=body] ul,
table[class=body] ol,
table[class=body] td,
table[class=body] span,
table[class=body] a {
font-size: 16px !important;
}
table[class=body] .wrapper,
table[class=body] .article {
padding: 10px !important;
}
table[class=body] .content {
padding: 0 !important;
}
table[class=body] .container {
padding: 0 !important;
width: 100% !important;
}
table[class=body] .main {
border-left-width: 0 !important;
border-radius: 0 !important;
border-right-width: 0 !important;
}
table[class=body] .btn table {
width: 100% !important;
}
table[class=body] .btn a {
width: 100% !important;
}
table[class=body] .img-responsive {
height: auto !important;
max-width: 100% !important;
width: auto !important;
}
}
@media all {
.ExternalClass {
width: 100%;
}
.ExternalClass,
.ExternalClass p,
.ExternalClass span,
.ExternalClass font,
.ExternalClass td,
.ExternalClass div {
line-height: 100%;
}
.apple-link a {
color: inherit !important;
font-family: inherit !important;
font-size: inherit !important;
font-weight: inherit !important;
line-height: inherit !important;
text-decoration: none !important;
}
#MessageViewBody a {
color: inherit;
text-decoration: none;
font-size: inherit;
font-family: inherit;
font-weight: inherit;
line-height: inherit;
}
.btn-primary table td:hover {
background-color: #34495e !important;
}
.btn-primary a:hover {
background-color: #34495e !important;
border-color: #34495e !important;
}
}
</style>
</head>
<body class="" style="background-color: #f6f6f6; font-family: sans-serif; \
-webkit-font-smoothing: antialiased; font-size: 14px; line-height: 1.4; margin: 0; \
padding: 0; -ms-text-size-adjust: 100%; -webkit-text-size-adjust: 100%;"> <table \
border="0" cellpadding="0" cellspacing="0" class="body" style="border-collapse: \
separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%; \
background-color: #f6f6f6;"> <tr>
<td style="font-family: sans-serif; font-size: 14px; vertical-align: \
top;"> </td> <td class="container" style="font-family: sans-serif; font-size: \
14px; vertical-align: top; display: block; Margin: 0 auto; max-width: 580px; padding: \
10px; width: 580px;">
<div class="content" style="box-sizing: border-box; display: block; Margin: \
0 auto; max-width: 580px; padding: 10px;">
<span class="preheader" style="color: transparent; display: none; height: \
0; max-height: 0; max-width: 0; opacity: 0; overflow: hidden; mso-hide: all; \
visibility: hidden; width: 0;"></span> <table class="main" style="border-collapse: \
separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%; background: \
#ffffff; border-radius: 3px;">
<tr>
<td class="wrapper" style="font-family: sans-serif; font-size: 14px; \
vertical-align: top; box-sizing: border-box; padding: 20px;">
<table border="0" cellpadding="0" cellspacing="0" \
style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; \
width: 100%;"> <tr>
<td style="font-family: sans-serif; font-size: 14px; \
vertical-align: top;">
<div>
<h1 style="color: #000; font-family: sans-serif; line-height: 1.4; margin: 0; \
margin-bottom: 30px; font-size: 25px; font-weight: 300; text-align: \
center">SQLreduce: Reduce verbose SQL queries to minimal examples</h1> </div>
<p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; \
margin-bottom: 15px"><a href="https://github.com/anse1/sqlsmith" style="color: \
#3498db; text-decoration: underline">SQLsmith</a> has proven to be an effective tool \
for finding bugs in different areas in the PostgreSQL server and other products, \
including security bugs, ranging from executor bugs to segfaults in type and index \
method implementations.</p> <p style="font-family: sans-serif; font-size: 14px; \
font-weight: normal; margin: 0; margin-bottom: 15px">However, the random queries \
generated by SQLsmith that trigger some error are most often very large and contain a \
lot of noise that does not contribute to the error. So far, manual inspection of the \
query and tedious editing was required to reduce the example to a minimal reproducer \
that developers can use to fix the problem.</p>
<p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; \
margin-bottom: 15px">This issue is solved by <a \
href="https://github.com/credativ/sqlreduce" style="color: #3498db; text-decoration: \
underline">SQLreduce</a>. 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.</p>
<p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; \
margin-bottom: 15px">SQLreduce is effective at reducing the queries from <a \
href="https://github.com/anse1/sqlsmith/wiki#score-list" style="color: #3498db; \
text-decoration: underline">original error reports from SQLsmith</a> to queries that \
match manually-reduced queries.</p> <p style="font-family: sans-serif; font-size: \
14px; font-weight: normal; margin: 0; margin-bottom: 15px">More details on <a \
href="https://www.credativ.de/en/blog/postgresql/sqlreduce-reduce-verbose-sql-queries-to-minimal-examples/" \
style="color: #3498db; text-decoration: underline">how it works in a blog \
post</a>.</p> <h1 style="color: #000; font-family: sans-serif; line-height: 1.4; \
margin: 0; margin-bottom: 30px; font-size: 25px; font-weight: 300; text-align: \
center">Example</h1> <p style="font-family: sans-serif; font-size: 14px; font-weight: \
normal; margin: 0; margin-bottom: 15px">In 2018, <a \
href="https://www.postgresql.org/message-id/87woxi24uw.fsf@ansel.ydns.eu" \
style="color: #3498db; text-decoration: underline">SQLsmith found a segfault</a> in \
PostgreSQL running Git revision 039eb6e92f. The reproducer back then was a huge \
40-line, 2.2kB query:</p> <p style="font-family: sans-serif; font-size: 14px; \
font-weight: normal; margin: 0; margin-bottom: 15px"><code>select case when \
pg_catalog.lastval() < pg_catalog.pg_stat_get_bgwriter_maxwritten_clean() then \
case when pg_catalog.circle_sub_pt( cast(cast(null as circle) as circle),
cast((select location from public.emp limit 1 offset 13)
as point)) ~ cast(nullif(case when cast(null as box) &> (select \
boxcol from public.brintest limit 1 offset 2) then (select f1 from public.circle_tbl \
limit 1 offset 4) else (select f1 from public.circle_tbl limit 1 offset 4)
end,
case when (select pg_catalog.max(class) from public.f_star)
~~ ref_0.c then cast(null as circle) else cast(null as circle) end
) as circle) then ref_0.a else ref_0.a end
else case when pg_catalog.circle_sub_pt(
cast(cast(null as circle) as circle),
cast((select location from public.emp limit 1 offset 13)
as point)) ~ cast(nullif(case when cast(null as box) &> (select \
boxcol from public.brintest limit 1 offset 2) then (select f1 from public.circle_tbl \
limit 1 offset 4) else (select f1 from public.circle_tbl limit 1 offset 4)
end,
case when (select pg_catalog.max(class) from public.f_star)
~~ ref_0.c then cast(null as circle) else cast(null as circle) end
) as circle) then ref_0.a else ref_0.a end
end as c0,
case when (select intervalcol from public.brintest limit 1 offset 1)
>= cast(null as "interval") then case when ((select \
pg_catalog.max(roomno) from public.room)
!~~ ref_0.c)
and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
else case when ((select pg_catalog.max(roomno) from public.room)
!~~ ref_0.c)
and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
end as c1,
ref_0.a as c2,
(select a from public.idxpart1 limit 1 offset 5) as c3,
ref_0.b as c4,
pg_catalog.stddev(
cast((select pg_catalog.sum(float4col) from public.brintest)
as float4)) over (partition by ref_0.a,ref_0.b,ref_0.c order by ref_0.b) as \
c5, cast(nullif(ref_0.b, ref_0.a) as int4) as c6, ref_0.b as c7, ref_0.c as c8
from
public.mlparted3 as ref_0
where true;</code></p>
<p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; \
margin-bottom: 15px">SQLreduce can effectively reduce that monster to just this:</p> \
<p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; \
margin-bottom: 15px"><code>SELECT pg_catalog.stddev(NULL) OVER () AS c5 FROM \
public.mlparted3 AS ref_0</code></p> <h1 style="color: #000; font-family: sans-serif; \
line-height: 1.4; margin: 0; margin-bottom: 30px; font-size: 25px; font-weight: 300; \
text-align: center">Availability</h1> <p style="font-family: sans-serif; font-size: \
14px; font-weight: normal; margin: 0; margin-bottom: 15px">SQLreduce is open source \
licensed under the MIT license. The source code is on GitHub: \
https://github.com/credativ/sqlreduce</p> <p style="font-family: sans-serif; \
font-size: 14px; font-weight: normal; margin: 0; margin-bottom: 15px">Debian/Ubuntu \
packages for sqlreduce are shipped on <a href="https://apt.postgresql.org" \
style="color: #3498db; text-decoration: underline">apt.postgresql.org</a>.</p> <p \
style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; \
margin-bottom: 15px">SQLreduce is an open source product by <a \
href="https://www.credativ.de/" style="color: #3498db; text-decoration: \
underline">credativ GmbH</a>.</p>
</td>
</tr>
</table>
</td>
</tr>
</table>
<div class="footer" style="clear: both; Margin-top: 10px; text-align: \
center; width: 100%;">
<table border="0" cellpadding="0" cellspacing="0" \
style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; \
width: 100%;"> <tr>
<td class="content-block" style="font-family: sans-serif; \
vertical-align: top; padding-bottom: 10px; padding-top: 10px; font-size: 12px; color: \
#999999; text-align: center;">
<span class="apple-link" style="color: #999999; font-size: 12px; \
text-align: center;"> This email was sent to you from credativ GmbH. It was delivered \
on their behalf by the PostgreSQL project. Any questions about the content of the \
message should be sent to credativ GmbH.
</span>
<br><br>
You were sent this email as a subscriber of the <em>pgsql-announce</em> mailinglist, \
for the content tag Related Open Source.
To unsubscribe from
further emails, or change which emails you want to receive, please click the personal \
unsubscribe link that you can find in the headers of this email, or visit
<a href="https://lists.postgresql.org/unsubscribe/" style="color: #3498db; \
text-decoration: underline">https://lists.postgresql.org/unsubscribe/</a>.
</td>
</tr>
</table>
</div>
</div>
</td>
<td style="font-family: sans-serif; font-size: 14px; vertical-align: \
top;"> </td> </tr>
</table>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic