From 3c8de95979008d67713429d858957c5c78c23d75 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 11 Aug 2017 17:27:54 -0400 Subject: [PATCH] Add regression tests exercising more code paths in nodeLimit.c. Perusal of the code coverage report shows that the existing regression test cases for LIMIT/OFFSET don't exercise the nodeLimit code paths involving backwards scan, empty results, or null values of LIMIT/OFFSET. Improve the coverage. --- src/test/regress/expected/limit.out | 179 ++++++++++++++++++++++++++++ src/test/regress/sql/limit.sql | 42 +++++++ 2 files changed, 221 insertions(+) diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out index 65c8c44a9a..c18f547cbd 100644 --- a/src/test/regress/expected/limit.out +++ b/src/test/regress/expected/limit.out @@ -108,6 +108,185 @@ SELECT ''::text AS five, unique1, unique2, stringu1 | 904 | 793 | UIAAAA (5 rows) +-- Test null limit and offset. The planner would discard a simple null +-- constant, so to ensure executor is exercised, do this: +select * from int8_tbl limit (case when random() < 0.5 then null::bigint end); + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +select * from int8_tbl offset (case when random() < 0.5 then null::bigint end); + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +-- Test assorted cases involving backwards fetch from a LIMIT plan node +begin; +declare c1 cursor for select * from int8_tbl limit 10; +fetch all in c1; + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +fetch 1 in c1; + q1 | q2 +----+---- +(0 rows) + +fetch backward 1 in c1; + q1 | q2 +------------------+------------------- + 4567890123456789 | -4567890123456789 +(1 row) + +fetch backward all in c1; + q1 | q2 +------------------+------------------ + 4567890123456789 | 4567890123456789 + 4567890123456789 | 123 + 123 | 4567890123456789 + 123 | 456 +(4 rows) + +fetch backward 1 in c1; + q1 | q2 +----+---- +(0 rows) + +fetch all in c1; + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +declare c2 cursor for select * from int8_tbl limit 3; +fetch all in c2; + q1 | q2 +------------------+------------------ + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 +(3 rows) + +fetch 1 in c2; + q1 | q2 +----+---- +(0 rows) + +fetch backward 1 in c2; + q1 | q2 +------------------+----- + 4567890123456789 | 123 +(1 row) + +fetch backward all in c2; + q1 | q2 +-----+------------------ + 123 | 4567890123456789 + 123 | 456 +(2 rows) + +fetch backward 1 in c2; + q1 | q2 +----+---- +(0 rows) + +fetch all in c2; + q1 | q2 +------------------+------------------ + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 +(3 rows) + +declare c3 cursor for select * from int8_tbl offset 3; +fetch all in c3; + q1 | q2 +------------------+------------------- + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(2 rows) + +fetch 1 in c3; + q1 | q2 +----+---- +(0 rows) + +fetch backward 1 in c3; + q1 | q2 +------------------+------------------- + 4567890123456789 | -4567890123456789 +(1 row) + +fetch backward all in c3; + q1 | q2 +------------------+------------------ + 4567890123456789 | 4567890123456789 +(1 row) + +fetch backward 1 in c3; + q1 | q2 +----+---- +(0 rows) + +fetch all in c3; + q1 | q2 +------------------+------------------- + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(2 rows) + +declare c4 cursor for select * from int8_tbl offset 10; +fetch all in c4; + q1 | q2 +----+---- +(0 rows) + +fetch 1 in c4; + q1 | q2 +----+---- +(0 rows) + +fetch backward 1 in c4; + q1 | q2 +----+---- +(0 rows) + +fetch backward all in c4; + q1 | q2 +----+---- +(0 rows) + +fetch backward 1 in c4; + q1 | q2 +----+---- +(0 rows) + +fetch all in c4; + q1 | q2 +----+---- +(0 rows) + +rollback; -- Stress test for variable LIMIT in conjunction with bounded-heap sorting SELECT (SELECT n diff --git a/src/test/regress/sql/limit.sql b/src/test/regress/sql/limit.sql index 8015f81fc2..2a313d80ca 100644 --- a/src/test/regress/sql/limit.sql +++ b/src/test/regress/sql/limit.sql @@ -31,6 +31,48 @@ SELECT ''::text AS five, unique1, unique2, stringu1 FROM onek ORDER BY unique1 LIMIT 5 OFFSET 900; +-- Test null limit and offset. The planner would discard a simple null +-- constant, so to ensure executor is exercised, do this: +select * from int8_tbl limit (case when random() < 0.5 then null::bigint end); +select * from int8_tbl offset (case when random() < 0.5 then null::bigint end); + +-- Test assorted cases involving backwards fetch from a LIMIT plan node +begin; + +declare c1 cursor for select * from int8_tbl limit 10; +fetch all in c1; +fetch 1 in c1; +fetch backward 1 in c1; +fetch backward all in c1; +fetch backward 1 in c1; +fetch all in c1; + +declare c2 cursor for select * from int8_tbl limit 3; +fetch all in c2; +fetch 1 in c2; +fetch backward 1 in c2; +fetch backward all in c2; +fetch backward 1 in c2; +fetch all in c2; + +declare c3 cursor for select * from int8_tbl offset 3; +fetch all in c3; +fetch 1 in c3; +fetch backward 1 in c3; +fetch backward all in c3; +fetch backward 1 in c3; +fetch all in c3; + +declare c4 cursor for select * from int8_tbl offset 10; +fetch all in c4; +fetch 1 in c4; +fetch backward 1 in c4; +fetch backward all in c4; +fetch backward 1 in c4; +fetch all in c4; + +rollback; + -- Stress test for variable LIMIT in conjunction with bounded-heap sorting SELECT -- 2.40.0