Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1giSrt-0003PZ-1G for pgsql-hackers@arkaria.postgresql.org; Sat, 12 Jan 2019 23:39:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1giSrr-0007Zp-Bd for pgsql-hackers@arkaria.postgresql.org; Sat, 12 Jan 2019 23:39:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1giSrq-0007Zi-OC for pgsql-hackers@lists.postgresql.org; Sat, 12 Jan 2019 23:39:07 +0000 Received: from bay.acenet.us ([173.230.252.50]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1giSrn-00074o-8i for pgsql-hackers@postgresql.org; Sat, 12 Jan 2019 23:39:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=anastigmatix.net; s=default; h=Content-Type:In-Reply-To:MIME-Version:Date: Message-ID:From:Cc:References:To:Subject:Sender:Reply-To: Content-Transfer-Encoding:Content-ID:Content-Description:Resent-Date: Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=Du89gsWz4htbeMoDbwpcRTR1tdXi+7e0nZSz/U4AvWA=; b=itht2YR/fCT6YqvBxtaJSp9Gi JfkgwTCI3xUU/bjmwXljpF78VvLCryzpdRgclOOnvCgP+qAn6teYHjLiENg9StcBlhztkBQclBfjx 3ArsXz1j1wEITLSkaUgWihSmaLsY/NgctUvpQaZBJZWGdVu3eMCngr96AuDmxCRjJ4rO5C12fXIQg O1PuYcgdcvWFFfHCE21EGi6jJb7BUfTjt3idQXfI0iQtx/M9YUmTz7L5c8OkjBpA+IZGwxqwBJ+i/ mufOQgF49TXrgR1rD/f5QSR61rbXqHZZN/oEM+RM7311/bgARiLxmArgtET9IIDhalCBjuLB8kuCI UygxUrATw==; Received: from [192.182.57.242] (port=59724) by bay.acenet.us with esmtpsa (TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:128) (Exim 4.91) (envelope-from ) id 1giSrh-0038XO-AM; Sat, 12 Jan 2019 18:38:59 -0500 Subject: Re: PostgreSQL vs SQL/XML Standards To: Pavel Stehule References: <20181025194720.ylmw64xqlnjr5wja@alvherre.pgsql> <45C74339-3531-46BD-A595-37183C6730A1@winand.at> <5C283168.8060001@anastigmatix.net> <5C291741.9060506@anastigmatix.net> <5C297BB7.9070509@anastigmatix.net> Cc: Markus Winand , Alvaro Herrera , Tom Lane , PostgreSQL Hackers From: Chapman Flack X-Enigmail-Draft-Status: N1110 Message-ID: <5C3A7A90.3070207@anastigmatix.net> Date: Sat, 12 Jan 2019 18:38:56 -0500 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: Content-Type: multipart/mixed; boundary="------------070602070800000909000504" X-OutGoing-Spam-Status: No, score=-94.2 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - bay.acenet.us X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - anastigmatix.net X-Get-Message-Sender-Via: bay.acenet.us: authenticated_id: chap@anastigmatix.net X-Authenticated-Sender: bay.acenet.us: chap@anastigmatix.net X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------070602070800000909000504 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit On 12/30/18 03:23, Pavel Stehule wrote: > Unfortunately, there is a different releases of libxml2 with different > error reporting and it is hard (impossible) to prepare for all variants. :-/ > > I prepare xml.out for my FC29 (fresh libxml2) and for no support xml. > Other I prepare by patching - and this error (in context) is expected. It turns out that the variant was already accounted for in the xml_2.out variant result file, it just needed to have the new results added. Done in xmltable-xpath-result-processing-bugfix-4.patch attached. On 12/31/18 01:03, Pavel Stehule wrote: > po 31. 12. 2018 v 3:15 odesílatel Chapman Flack > napsal: >> But the PostgreSQL situation is a little more strange. PG uses BY VALUE >> semantics as the default when no passing method is specified. PG also uses >> BY VALUE semantics when BY REF is explicitly requested, which is rude, >> just like Oracle. But why should an explicit specification of BY VALUE >> (which is, after all, the semantics we're going to use anyway!) produce >> this? >> >> ERROR: syntax error at or near "value" >> >> To me, that doesn't seem like least astonishment. >> >> I am not seeing what would be complicated about removing that astonishment >> by simply allowing the grammar productions to also consume BY VALUE and >> ignore it. > > ok - I am not against implementation of ignored BY VALUE. But I don't like > a idea to disable BY REF. Done in attached xmltable-xmlexists-passing-mechanisms-1.patch along with some corresponding documentation adjustments. I am still working on more extensive documentation, but it seemed best to include the changes related to BY REF / BY VALUE in the same patch with the grammar change. -Chap --------------070602070800000909000504 Content-Type: text/x-patch; name="xmltable-xpath-result-processing-bugfix-4.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="xmltable-xpath-result-processing-bugfix-4.patch" diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 90d67f1..06f3f69 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10999,9 +10999,9 @@ $$ AS data; SELECT xmltable.* FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text); - element ----------------------- - Hello2a2 bbbCC + element +------------------------- + Hello2a2 bbbxxxCC ]]> diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c index 1cec168..df7f0cc 100644 --- a/src/backend/utils/adt/xml.c +++ b/src/backend/utils/adt/xml.c @@ -3674,15 +3674,15 @@ SPI_sql_row_to_xmlelement(uint64 rownum, StringInfo result, char *tablename, #ifdef USE_LIBXML /* - * Convert XML node to text (dump subtree in case of element, - * return value otherwise) + * Convert XML node to text (dump subtree), for attribute and text + * returns escaped text. */ static text * xml_xmlnodetoxmltype(xmlNodePtr cur, PgXmlErrorContext *xmlerrcxt) { xmltype *result; - if (cur->type == XML_ELEMENT_NODE) + if (cur->type != XML_ATTRIBUTE_NODE && cur->type != XML_TEXT_NODE) { xmlBufferPtr buf; xmlNodePtr cur_copy; @@ -4427,6 +4427,37 @@ XmlTableFetchRow(TableFuncScanState *state) #endif /* not USE_LIBXML */ } +#ifdef USE_LIBXML +/* + * Copy XmlChar string to PostgreSQL memory. Ensure releasing of + * source xmllib string. + */ +static char * +copy_and_safe_free_xmlchar(xmlChar *str) +{ + char *result; + + if (str) + { + PG_TRY(); + { + result = pstrdup((char *) str); + } + PG_CATCH(); + { + xmlFree(str); + PG_RE_THROW(); + } + PG_END_TRY(); + xmlFree(str); + } + else + result = NULL; + + return result; +} +#endif + /* * XmlTableGetValue * Return the value for column number 'colnum' for the current row. If @@ -4475,9 +4506,9 @@ XmlTableGetValue(TableFuncScanState *state, int colnum, /* * There are four possible cases, depending on the number of nodes * returned by the XPath expression and the type of the target column: - * a) XPath returns no nodes. b) One node is returned, and column is - * of type XML. c) One node, column type other than XML. d) Multiple - * nodes are returned. + * a) XPath returns no nodes. b) The target type is XML (return all + * as XML). For non-XML types: c) One node (return content). + * d) Multiple nodes (error). */ if (xpathobj->type == XPATH_NODESET) { @@ -4490,85 +4521,72 @@ XmlTableGetValue(TableFuncScanState *state, int colnum, { *isnull = true; } - else if (count == 1 && typid == XMLOID) - { - text *textstr; - - /* simple case, result is one value */ - textstr = xml_xmlnodetoxmltype(xpathobj->nodesetval->nodeTab[0], - xtCxt->xmlerrcxt); - cstr = text_to_cstring(textstr); - } - else if (count == 1) + else { - xmlChar *str; - xmlNodePtr node; - - /* - * Most nodes (elements and even attributes) store their data - * in children nodes. If they don't have children nodes, it - * means that they are empty (e.g. ). Text nodes and - * CDATA sections are an exception: they don't have children - * but have content in the Text/CDATA node itself. - */ - node = xpathobj->nodesetval->nodeTab[0]; - if (node->type != XML_CDATA_SECTION_NODE && - node->type != XML_TEXT_NODE) - node = node->xmlChildrenNode; - - str = xmlNodeListGetString(xtCxt->doc, node, 1); - if (str != NULL) + if (typid == XMLOID) { - PG_TRY(); - { - cstr = pstrdup((char *) str); - } - PG_CATCH(); + text *textstr; + StringInfoData str; + int i; + + /* Concatenate serialized values */ + initStringInfo(&str); + for (i = 0; i < count; i++) { - xmlFree(str); - PG_RE_THROW(); + textstr = + xml_xmlnodetoxmltype(xpathobj->nodesetval->nodeTab[i], + xtCxt->xmlerrcxt); + + appendStringInfoText(&str, textstr); } - PG_END_TRY(); - xmlFree(str); + cstr = str.data; } else { - /* Ensure mapping of empty tags to PostgreSQL values. */ - cstr = ""; - } - } - else - { - StringInfoData str; - int i; - - Assert(count > 1); + xmlChar *str; - /* - * When evaluating the XPath expression returns multiple - * nodes, the result is the concatenation of them all. The - * target type must be XML. - */ - if (typid != XMLOID) - ereport(ERROR, - (errcode(ERRCODE_CARDINALITY_VIOLATION), - errmsg("more than one value returned by column XPath expression"))); + if (count > 1) + ereport(ERROR, + (errcode(ERRCODE_CARDINALITY_VIOLATION), + errmsg("more than one value returned by column XPath expression"))); - /* Concatenate serialized values */ - initStringInfo(&str); - for (i = 0; i < count; i++) - { - appendStringInfoText(&str, - xml_xmlnodetoxmltype(xpathobj->nodesetval->nodeTab[i], - xtCxt->xmlerrcxt)); + str = xmlXPathCastNodeSetToString(xpathobj->nodesetval); + if (str) + cstr = copy_and_safe_free_xmlchar(str); + else + /* empty element */ + cstr = ""; } - cstr = str.data; } } else if (xpathobj->type == XPATH_STRING) { cstr = (char *) xpathobj->stringval; } + else if (xpathobj->type == XPATH_BOOLEAN) + { + char typcategory; + bool typispreferred; + xmlChar *str; + + /* Allow implicit casting from boolean to numbers */ + get_type_category_preferred(typid, &typcategory, &typispreferred); + + if (typcategory != TYPCATEGORY_NUMERIC) + str = xmlXPathCastBooleanToString(xpathobj->boolval); + else + str = xmlXPathCastNumberToString( + xmlXPathCastBooleanToNumber(xpathobj->boolval)); + + cstr = copy_and_safe_free_xmlchar(str); + } + else if (xpathobj->type == XPATH_NUMBER) + { + xmlChar *str; + + str = xmlXPathCastNumberToString(xpathobj->floatval); + cstr = copy_and_safe_free_xmlchar(str); + } else elog(ERROR, "unexpected XPath object type %u", xpathobj->type); diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out index 6e1f885..bfb98e3 100644 --- a/src/test/regress/expected/xml.out +++ b/src/test/regress/expected/xml.out @@ -1210,9 +1210,9 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" (2 rows) SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text); - element -------------------- - a1aa2a bbbbcccc + element +---------------------- + a1aa2a bbbbxxxcccc (1 row) SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text PATH 'element/text()'); -- should fail @@ -1493,3 +1493,29 @@ SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c 14 (4 rows) +-- XPath result can be boolean or number too +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"'); + a | b | c | d +----------+---+----+--- + a | a | hi | t +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)'); + a | b | c | d +----------+---+----+--- + a | a | hi | 1 +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH 'node()'); + x +--------------------------------------------------------------- + pre&deeppost +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH '/'); + x +---------------------------------------------------------------------- + pre&deeppost+ + +(1 row) + diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out index 0eba424..62b83fb 100644 --- a/src/test/regress/expected/xml_1.out +++ b/src/test/regress/expected/xml_1.out @@ -1343,3 +1343,28 @@ SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c --- (0 rows) +-- XPath result can be boolean or number too +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"'); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml ... + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)'); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml ... + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH 'node()'); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM XMLTABLE('*' PASSING 'prepre&deeppost' COLUMNS x xml PATH '/'); +ERROR: unsupported XML feature +LINE 1: SELECT * FROM XMLTABLE('*' PASSING 'prea1aa2a bbbbxxxcccc' COLUMNS element text); - element -------------------- - a1aa2a bbbbcccc + element +---------------------- + a1aa2a bbbbxxxcccc (1 row) SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text PATH 'element/text()'); -- should fail @@ -1473,3 +1473,29 @@ SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c 14 (4 rows) +-- XPath result can be boolean or number too +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"'); + a | b | c | d +----------+---+----+--- + a | a | hi | t +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)'); + a | b | c | d +----------+---+----+--- + a | a | hi | 1 +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH 'node()'); + x +--------------------------------------------------------------- + pre&deeppost +(1 row) + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH '/'); + x +---------------------------------------------------------------------- + pre&deeppost+ + +(1 row) + diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql index 3b91b56..7089fad 100644 --- a/src/test/regress/sql/xml.sql +++ b/src/test/regress/sql/xml.sql @@ -595,3 +595,10 @@ INSERT INTO xmltest2 VALUES('2', 'D'); SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c'); SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.'); SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54); + +-- XPath result can be boolean or number too +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"'); +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d integer PATH 'string-length(.)'); + +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH 'node()'); +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH '/'); --------------070602070800000909000504 Content-Type: text/x-patch; name="xmltable-xmlexists-passing-mechanisms-1.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="xmltable-xmlexists-passing-mechanisms-1.patch" diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 06f3f69..df0fe4d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10583,7 +10583,7 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; -XMLEXISTS(text PASSING BY REF xml BY REF) +XMLEXISTS(text PASSING BY { REF | VALUE } xml BY { REF | VALUE }) @@ -10596,7 +10596,7 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; Example: TorontoOttawa'); +SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE 'TorontoOttawa'); xmlexists ------------ @@ -10606,14 +10606,22 @@ SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Tor - The BY REF clauses have no effect in - PostgreSQL, but are allowed for SQL conformance and compatibility - with other implementations. Per SQL standard, the - first BY REF is required, the second is - optional. Also note that the SQL standard specifies - the xmlexists construct to take an XQuery - expression as first argument, but PostgreSQL currently only - supports XPath, which is a subset of XQuery. + The BY REF or BY VALUE clauses + have no effect in PostgreSQL, but are allowed for compatibility + with other implementations. Per SQL standard, the one that precedes + any argument is required, and indicates the default for arguments that + follow, and one may follow any argument to override the default. + PostgreSQL ignores BY REF and passes by value always. + + + + In the SQL standard, an xmlexists construct first + appears in SQL:2006, evaluates an expression in the XML Query language, + allows passing values for named parameters in the expression as well + as for the context item, and does not require the passed values to be + documents, or even of XML type. In PostgreSQL, this construct currently + only evaluates an XPath 1.0 expression, and allows passing only one value, + which must be an XML document, to be the context item. @@ -10820,7 +10828,7 @@ SELECT xpath_exists('/my:a/text()', 'test xmltable( XMLNAMESPACES(namespace uri AS namespace name, ...), - row_expression PASSING BY REF document_expression BY REF + row_expression PASSING BY { REF | VALUE } document_expression BY { REF | VALUE } COLUMNS name { type PATH column_expression DEFAULT default_expression NOT NULL | NULL | FOR ORDINALITY } , ... @@ -10850,11 +10858,11 @@ SELECT xpath_exists('/my:a/text()', 'test document_expression provides the XML document to operate on. - The BY REF clauses have no effect in PostgreSQL, - but are allowed for SQL conformance and compatibility with other - implementations. The argument must be a well-formed XML document; fragments/forests are not accepted. + The BY REF or BY VALUE clauses are + accepted, as described for the xmlexists predicate, + but ignored; PostgreSQL currently passes XML by value always. diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index aeb262a..bade0fe 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -635,8 +635,8 @@ X204 XMLQuery: initializing an XQuery variable NO X205 XMLQuery: EMPTY ON EMPTY option NO X206 XMLQuery: NULL ON EMPTY option NO X211 XML 1.1 support NO -X221 XML passing mechanism BY VALUE NO -X222 XML passing mechanism BY REF YES +X221 XML passing mechanism BY VALUE YES +X222 XML passing mechanism BY REF NO parser accepts BY REF but ignores it; passing is always BY VALUE X231 XML(CONTENT(UNTYPED)) type NO X232 XML(CONTENT(ANY)) type NO X241 RETURNING CONTENT in XML publishing NO diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index c086235..1b303d7 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -13942,20 +13942,25 @@ xmlexists_argument: { $$ = $2; } - | PASSING c_expr BY REF + | PASSING c_expr xml_passing_mech { $$ = $2; } - | PASSING BY REF c_expr + | PASSING xml_passing_mech c_expr { - $$ = $4; + $$ = $3; } - | PASSING BY REF c_expr BY REF + | PASSING xml_passing_mech c_expr xml_passing_mech { - $$ = $4; + $$ = $3; } ; +xml_passing_mech: + BY REF + | BY VALUE_P + ; + /* * Aggregate decoration clauses --------------070602070800000909000504--