anse1 / sqlsmith

A random SQL query generator
GNU General Public License v3.0
754 stars 128 forks source link

CURSOR (WITH HOLD) support? #40

Open df7cb opened 3 years ago

df7cb commented 3 years ago

https://postgr.es/m/CAPV2KRjd=ErgVGbvO2Ty20tKTEZZr6cYsYLxgN_W3eAo9pf5sw@mail.gmail.com revealed a bug in a WITH HOLD cursor:

BEGIN;
DECLARE "test-cursor-crash" CURSOR WITH HOLD FOR
            SELECT di.itemId FROM "debug".downloaded_images di
            LEFT JOIN (SELECT itemId, MIN(last_update) as last_update FROM "debug"."foo" GROUP BY itemId) computed ON di.itemId=computed.itemId
            WHERE COALESCE(last_update, '1970-01-01') < download_time;
FETCH 10000 IN "test-cursor-crash";
COMMIT;

I realize this requires sending a whole transaction instead of an isolated query, but I think it's worthwhile to explore some of the less-frequently used SQL features as well.