agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Surafel Temesgen <surafel3000@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>
Subject: Re: FETCH FIRST clause WITH TIES option
Date: Tue, 7 Apr 2020 16:36:54 -0400
Message-ID: <20200407203654.GA15931@alvherre.pgsql> (raw)
In-Reply-To: <20200407004925.GA23627@alvherre.pgsql>
Pushed, with some additional changes.
So *of course* when I add tests to verify that ruleutils, I find a case
that does not work properly -- meaning, you get a view that pg_dump
emits in a way that won't be accepted:
CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek WHERE thousand < 995
ORDER BY thousand FETCH FIRST NULL ROWS WITH TIES;
note the "NULL" there. ruleutils would gladly print this out as:
View definition:
SELECT onek.thousand
FROM onek
WHERE onek.thousand < 995
ORDER BY onek.thousand
FETCH FIRST NULL::integer ROWS WITH TIES;
which is then not accepted.
The best fix I could come up for this was to reject a bare NULL in the
limit clause. It's a very stupid fix, because you can still give it a
NULL, using
CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek WHERE thousand < 995
ORDER BY thousand FETCH FIRST (NULL+1) ROWS WITH TIES;
and the like. But when ruleutils get this, it will add the parens,
which will magically make it work.
It turns out that the SQL standard is much more limited in what it will
accept there. But our grammar (what we'll accept for the ancient LIMIT
clause) is very lenient -- it'll take just any expression. I thought
about reducing that to NumericOnly for FETCH FIRST .. WITH TIES, but
then I have to pick: 1) gram.y fails to compile because of a
reduce/reduce conflict, or 2) also restricting FETCH FIRST .. ONLY to
NumericOnly. Neither of those seemed very palatable.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
view thread (61+ messages) latest in thread
Message-ID: <20200407203654.GA15931@alvherre.pgsql>
Permalink: ../20200407203654.GA15931@alvherre.pgsql/
Also on: postgresql.org/message-id/20200407203654.GA15931@alvherre.pgsql
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, surafel3000@gmail.com, tomas.vondra@2ndquadrant.com, andrew@tao11.riddles.org.uk
Subject: Re: FETCH FIRST clause WITH TIES option
In-Reply-To: <20200407203654.GA15931@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox