BUG #6483: Rows being evaluated, although being outside the LIMIT / OFFSET boundaries - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      6483
Logged by:          Kouber Saparev
Email address:      kouber@saparev.com
PostgreSQL version: 9.1.2
Operating system:   Debian
Description:=20=20=20=20=20=20=20=20

The rows of a SELECT statement are being evaluated, even when not shown in
the final result, when using an OFFSET > 0. Although I know that LIMIT is
imposed just before flushing the result set to the client, this behaviour
seems quite confusing, especially when using DML statements in the field
list of the SELECT itself.

CREATE TABLE xxx (id INT);

CREATE FUNCTION f(xxx) RETURNS VOID AS $$
BEGIN
  -- imagine some DML statements here --

  RAISE NOTICE '%', $1.id;
END;
$$ LANGUAGE PLPGSQL;=20

INSERT INTO xxx VALUES (1), (2), (3), (4), (5);


-- shows a notice for 1 and 2
SELECT x.id, f(x) FROM xxx as x LIMIT 2;


-- shows a notice for 1, 2, 3 and 4
SELECT x.id, f(x) FROM xxx as x LIMIT 2 OFFSET 2;