On Fri, Apr 26, 2024 at 2:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > No other programming language that I know of, and no other database
> > that I know of, looks at x.y.z and says "ok, well first we have to
> > figure out whether the object is named x or x.y or x.y.z, and then
> > after that, we'll use whatever is left over as a field selector."
>
> It may indeed be true that nobody but SQL does that, but nonetheless
> this is exactly what SQL99 requires AFAICT. The reason we use this
> parenthesis notation is precisely that we didn't want to get into
> that sort of tea-leaf-reading about how many identifiers mean what.
> The parens put it on the user to tell us what part of the chain
> is field selection.
I really thought this was just PostgreSQL, not SQL generally, but I
just experimented a bit with Oracle on dbfiddle.uk using this example:
CREATE TYPE foo AS OBJECT (a number(10), b varchar2(2000));
CREATE TABLE bar (quux foo);
INSERT INTO bar VALUES (foo(1, 'one'));
SELECT bar.quux, quux, (quux).a, (bar.quux).a FROM bar;
This works, but if I delete the parentheses from the last line, then
it fails. So evidently my understanding of how this works in other
systems is incorrect, or incomplete. I feel like I've encountered
cases where we required extra parenthesization that Oracle didn't
need, but it's hard to discuss that without examples, and I don't have
them right now.
> Yes, we can. Please do not rant further about this until you've
> read the <identifier chain> section of a recent SQL spec.
I'm hurt to see emails that I spent time on characterized as a rant,
even if I was wrong on the facts. And I think appealing to the SQL
standard is a poor way of trying to end debate on a topic.
--
Robert Haas
EDB: http://www.enterprisedb.com