SQLreduce: Reduzieren Sie ausführliche SQL-Abfragen auf minimale Beispiele

Entwickler sehen sich oft sehr großen SQL-Abfragen gegenüber, die Fehler verursachen. SQLreduce ist ein Werkzeug, um diese Komplexität auf eine minimale Abfrage zu reduzieren.

SQLsmith generiert zufällige SQL-Abfragen

SQLsmith ist ein Werkzeug, das zufällige SQL-Abfragen generiert und sie gegen einen PostgreSQL-Server (und andere DBMS-Typen) ausführt. Die Idee ist, dass durch Fuzz-Testing des Query Parsers und Executors Corner-Case-Bugs gefunden werden können, die sonst bei manuellen Tests oder mit dem festen Satz von Testfällen in der PostgreSQL-Regressionstestsuite unbemerkt bleiben würden. Es hat sich als ein effektives Werkzeug erwiesen, mit dem seit 2015 über 100 Bugs in verschiedenen Bereichen des PostgreSQL-Servers und anderer Produkte gefunden wurden, darunter Sicherheitslücken, die von Executor-Bugs bis hin zu Segfaults in Typ- und Indexmethodenimplementierungen reichen. Zum Beispiel fand SQLsmith im Jahr 2018 heraus, dass die folgende Abfrage einen Segfault in PostgreSQL auslöste:

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;

Wie jedoch in diesem 40-zeiligen, 2,2 KB großen Beispiel sind die von SQLsmith generierten Zufallsabfragen, die einen Fehler auslösen, meist sehr groß und enthalten viel Rauschen, das nicht zum Fehler beiträgt. Bisher waren eine manuelle Überprüfung der Abfrage und eine mühsame Bearbeitung erforderlich, um das Beispiel auf einen minimalen Reproducer zu reduzieren, den Entwickler zur Behebung des Problems verwenden können.

Reduzieren Sie die Komplexität mit SQLreduce

Dieses Problem wird durch SQLreduce gelöst. SQLreduce nimmt als Eingabe eine beliebige SQL-Abfrage entgegen, die dann gegen einen PostgreSQL-Server ausgeführt wird. Es werden verschiedene Vereinfachungsschritte angewendet, wobei nach jedem Schritt geprüft wird, ob die vereinfachte Abfrage noch denselben Fehler von PostgreSQL auslöst. Das Endergebnis ist eine SQL-Abfrage mit minimaler Komplexität.

SQLreduce reduziert effektiv die Abfragen aus ursprünglichen Fehlerberichten von SQLsmith auf Abfragen, die manuell reduzierten Abfragen entsprechen. Zum Beispiel kann SQLreduce die obige Monsterabfrage effektiv auf Folgendes reduzieren:

SELECT pg_catalog.stddev(NULL) OVER () AS c5 FROM public.mlparted3 AS ref_0

Beachten Sie, dass SQLreduce nicht versucht, eine Abfrage abzuleiten, die semantisch identisch mit dem Original ist oder dasselbe Abfrageergebnis liefert – die Eingabe wird als fehlerhaft angenommen, und wir suchen nach der minimalen Abfrage, die dieselbe Fehlermeldung von PostgreSQL erzeugt, wenn sie gegen eine Datenbank ausgeführt wird. Wenn die Eingabeabfrage zufällig keinen Fehler erzeugt, ist die von SQLreduce ausgegebene Minimalabfrage nur SELECT.

Wie es funktioniert

Wir werden eine einfachere Abfrage verwenden, um zu demonstrieren, wie SQLreduce funktioniert und welche Schritte unternommen werden, um Rauschen aus der Eingabe zu entfernen. Die Abfrage ist falsch und enthält ein wenig Durcheinander, das wir entfernen wollen:

$ 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

Übergeben wir die Abfrage an SQLreduce:

$ sqlreduce 'select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10'

SQLreduce beginnt mit dem Parsen der Eingabe mit pglast und libpg_query, die den ursprünglichen PostgreSQL-Parser als Bibliothek mit Python-Bindungen bereitstellen. Das Ergebnis ist ein Parse-Baum, der die Grundlage für die nächsten Schritte bildet. Der Parse-Baum sieht wie folgt aus:

selectStmt
├── targetList
│   └── /
│       ├── pg_database.reltuples
│       └── 1000
├── fromClause
│   ├── pg_database
│   └── pg_class
├── whereClause
│   └── <
│       ├── 0
│       └── /
│           ├── pg_database.reltuples
│           └── 1000
├── orderClause
│   └── 1
└── limitCount
    └── 10

Pglast enthält auch einen Query Renderer, der den Parse-Baum als SQL zurückgeben kann, wie in der regenerierten Abfrage unten gezeigt. Die Eingabeabfrage wird gegen PostgreSQL ausgeführt, um das Ergebnis zu bestimmen, in diesem Fall 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 arbeitet, indem es neue Parse-Bäume ableitet, die strukturell einfacher sind, SQL daraus generiert und diese Abfragen gegen die Datenbank ausführt. Die ersten Vereinfachungsschritte arbeiten auf dem obersten Knoten, wo SQLreduce versucht, ganze Teilbäume zu entfernen, um schnell ein Ergebnis zu finden. Die erste versuchte Reduktion ist das Entfernen von LIMIT 10:

SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ORDER BY 1 DESC ✔

Das Abfrageergebnis ist immer noch ERROR: column pg_database.reltuples does not exist, was durch ein ✔ Häkchen angezeigt wird. Als nächstes wird ORDER BY 1 entfernt, wieder erfolgreich:

SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔

Nun wird die gesamte Zielliste entfernt:

SELECT FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔

Diese kürzere Abfrage ist immer noch äquivalent zum Original bezüglich der Fehlermeldung, die zurückgegeben wird, wenn sie gegen die Datenbank ausgeführt wird. Nun wird der erste erfolglose Reduktionsschritt versucht, bei dem die gesamte FROM-Klausel entfernt wird:

SELECT WHERE 0 < ((pg_database.reltuples / 1000)) ✘ ERROR:  missing FROM-clause entry for table "pg_database"

Diese Abfrage ist auch fehlerhaft, löst aber eine andere Fehlermeldung aus, so dass der vorherige Parse-Baum für die nächsten Schritte beibehalten wird. Wieder wird ein ganzer Teilbaum entfernt, jetzt die WHERE-Klausel:

SELECT FROM pg_database, pg_class ✘ no error

Wir haben die Eingabeabfrage nun so weit reduziert, dass sie keine Fehler mehr ausgibt. Der vorherige Parse-Baum wird weiterhin beibehalten, der nun wie folgt aussieht:

selectStmt
├── fromClause
│   ├── pg_database
│   └── pg_class
└── whereClause
    └── <
        ├── 0
        └── /
            ├── pg_database.reltuples
            └── 1000

Nun beginnt SQLreduce, sich in den Baum einzugraben. Es gibt mehrere Einträge in der -Klausel, also versucht es, die Liste zu verkürzen. Zuerst wird pg_database entfernt, aber das funktioniert nicht, also wird pg_class entfernt:

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)) ✔

Da wir eine neue minimale Abfrage gefunden haben, startet die Rekursion auf oberster Ebene mit einem weiteren Versuch, die WHERE-Klausel zu entfernen. Da das nicht funktioniert, versucht es, den Ausdruck durch NULL zu ersetzen, aber auch das funktioniert nicht.

SELECT FROM pg_database ✘ no error
SELECT FROM pg_database WHERE NULL ✘ no error

Nun wird eine neue Art von Schritt versucht: Expression Pull-up. Wir steigen in die WHERE-Klausel ab, wo wir A < B zuerst durch A und dann durch B ersetzen.

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"

Der erste Versuch hat nicht funktioniert, aber der zweite. Da wir die Abfrage vereinfacht haben, starten wir auf oberster Ebene neu, um zu prüfen, ob die FROM-Klausel entfernt werden kann, aber sie ist immer noch erforderlich.

Von A / B können wir wieder A hochziehen:

SELECT FROM pg_database WHERE pg_database.reltuples ✔
SELECT WHERE pg_database.reltuples ✘ ERROR:  missing FROM-clause entry for table "pg_database"

SQLreduce hat die minimale Abfrage gefunden, die immer noch ERROR: column pg_database.reltuples does not exist mit diesem Parse-Baum auslöst:

selectStmt
├── fromClause
│   └── pg_database
└── whereClause
    └── pg_database.reltuples

Am Ende des Laufs wird die Abfrage zusammen mit einigen Statistiken ausgegeben:

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

Diese minimale Abfrage kann nun überprüft werden, um den Fehler in PostgreSQL oder in der Anwendung zu beheben.

Über credativ

Die credativ GmbH ist ein herstellerunabhängiges Beratungs- und Dienstleistungsunternehmen mit Sitz in Mönchengladbach. Mit über 22 Jahren Entwicklungs- und Serviceerfahrung im open source Bereich kann die credativ GmbH Sie mit beispiellosem und individuell anpassbarem Support unterstützen. Wir sind hier, um Ihnen bei all Ihren open source Infrastruktur-Bedürfnissen zu helfen und Sie zu unterstützen.

Dieser Artikel wurde ursprünglich von Christoph Berg geschrieben.