Subject: Re: PostgreSQL vs SQL/XML Standards
To: Pavel Stehule <pavel.stehule@gmail.com>
References: <2a7715e1-6170-c3f6-12c7-7e5dda84cecd@anastigmatix.net>
 <20181025150849.j4py2nkqjx3cgw26@alvherre.pgsql>
 <CAFj8pRDs0F=3RxA4Q+3dAiosnEcTSGVLQqBZxXEM=DEiQb6sWw@mail.gmail.com>
 <5BD24640.50105@anastigmatix.net>
 <CAFj8pRBOS9TUM14-7_RjUj=5gJFG4O37ySUeReqZBTmnGnCCMw@mail.gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
From: Chapman Flack <chap@anastigmatix.net>
Message-ID: <5BD29725.1020603@anastigmatix.net>
Date: Fri, 26 Oct 2018 00:25:09 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CAFj8pRBOS9TUM14-7_RjUj=5gJFG4O37ySUeReqZBTmnGnCCMw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 10/25/18 23:16, Pavel Stehule wrote:
>> XMLTABLE would be the headache. Using the standard name for something
>> that ain't the standard function has not left any painless way that the
>> standard function could be added. OTOH, it has only been in the wild
>> since 10, so renaming it to something else (xpath_table?) will probably
>> be more painless if done soon than it ever would be later.
>>
> I don't share your opinion. XMLTABLE implements subset of standard. More it
> is well compatible with Oracle (in this subset).
> 
> If we have library with XPath 2.0 or higher, we can continue with it.

The difficulty here is that the expression language required by the standard
is XQuery, and an XPath expression (whether 1.0 or 2.0+) can always be
parsed as an XQuery expression. (So, /syntactically/, yes, "subset".)

For XPath 2.0, that is no problem, because an XPath 2.0 expression and
the identically-spelled XQuery expression /mean the same thing/.

For XPath 1.0, it is very definitely a problem, because an XPath 1.0
expression and the identically-spelled XQuery expression /do not mean
the same thing/. Some of the important semantic differences are in [1].

So, if a future PostgreSQL version has an XMLTABLE function that accepts
XQuery, as the standard requires, and existing users upgrade and they have
XMLTABLE query expressions written as XPath 1.0, those queries will be
accepted and parsed, but they will not mean the same thing. The function
will not be able to tell when it is being called with XQuery semantics
intended, vs. when it is being called with XPath 1.0 semantics intended.

Now, perhaps there is a nicer way than renaming the function. It could
work like overloading. Create two trivial domains over text, say xpath1
and xquery, and have two XMLTABLE functions with different first parameter
types. Then if you called with the expression '"cat" < "dog"'::xquery
you would get the correct result 't', and with '"cat" < "dog"'::xpath1
you would get the (also correct) result 'f'.

(It would not be exactly overloading, because of the special sugared
syntax known to the parser, but it could look like overloading, and be
intuitive to the user.)

If you have convenient access to Oracle to check compatibility, could you
compare this query?

SELECT * FROM XMLTABLE('.'
PASSING '<sale hatsize="7" customer="alice" taxable="false"/>'
COLUMNS
a boolean PATH 'string("cat" < "dog")',
b boolean PATH 'string("cat" > "dog")',
c boolean PATH 'string(sale/@taxable = false())');

(I suspect in Oracle it would also work without the string() wrappings,
but just to make it easy, I think this way it will work in both Oracle
and PG—that is, not error, though results may differ.)

-Chap


[1]
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#Related_to_version_of_XPath