XMLEXISTS predicate usage

If XMLEXISTS includes an XPath expression with a value predicate (expression), enclose the predicate in square brackets, such that [expression] is the result. Enclosing the value predicate in square brackets ensures evaluation of expression in accordance with what may be semantically expected.

XMLEXISTS predicate behavior

The following scenario demonstrates how a non-empty sequence causes XMLEXISTS to evaluate true, even though the non-empty sequence itself consists of the single value false. No index matching takes place and the query returns a much greater set of results than expected. The issue is avoided by bracketing value predicates appropriately with square brackets ([]).

Consider a table, an index and two queries:

   CREATE TABLE mytable (id BIGINT, xmlcol XML);
      CREATE INDEX myidx ON mytable(xmlcol) 
      GENERATE KEY USING XMLPATTERN '//text()' AS SQL VARCHAR(255);

   SELECT xmlcol FROM mytable
      WHERE XMLEXISTS('$doc/CUSTOMER/ORDERS/ORDERKEY/text()="A512" ' 
      PASSING xmlcol AS "doc") 

   SELECT xmlcol FROM mytable
      WHERE XMLEXISTS('$doc/CUSTOMER[ORDERS/ORDERKEY/text()="A512"] ' 
      PASSING xmlcol AS "doc") ;

The cause for this behavior is as follows: XMLEXISTS evaluates the XQuery expression and returns false (for XMLEXISTS) if the result is the empty sequence, and true (for XMLEXISTS) if the result is a non-empty sequence. This is followed by a possibly non-intuitive next step in the query evaluation: In the first query the expression instructs to 'compare the order key to A512'. The result of that expression is either the value false or true depending on the actual value of the order key. Therefore, the XMLEXISTS function always sees a return sequence with a single item in it, that is, an item that is false or an item that is true. Since a sequence with one item is a non-empty sequence, XMLEXISTS always returns true (for XMLEXISTS) overall and therefore the query returns all rows. Indexes cannot be leveraged if XMLEXISTS is used such that all rows are qualified.

Below are 5 examples of non-empty sequences, where 3 are sequences which contain just 1 item:

   (42, 3,4,78, 1966)
   (true)
   (abd, def)
   (false)
   (5)

Any such non-empty sequence will cause XMLEXISTS to return the value true (for XMLEXISTS), even if the non-empty sequence it encounters itself returns (false).

In the second query, the expression inside XMLEXISTS instructs to 'return the customers which contain an order with an orderkey equal to A512'. If no such customer exists in a document, then the result is indeed an empty sequence. This query will use an index and it will return the expected results.

XMLEXISTS predicate usage

When the entire expression is placed in square brackets, the meaning is fixed to be 'return XML data if [expression]', and you should always be returned an empty sequence if the XML data does not satisfy expression.

Since the value comparison is always within square brackets, each of the following sample fragments for XMLEXISTS predicate usage works as expected:

... WHERE XMLEXISTS('$doc[CUSTOMER/ORDERS/ORDERKEY/text()="A512"] '
       PASSING xmlcol as "doc") ;
   ... WHERE XMLEXISTS('$doc/CUSTOMER[ORDERS/ORDERKEY/text()="A512"] ' 
       PASSING xmlcol AS "doc") ;
   ... WHERE XMLEXISTS('$doc/CUSTOMER/ORDERS[ORDERKEY/text()="A512"] ' 
       PASSING xmlcol AS "doc") ;

The guideline also works for queries where there is no value comparison, for example, when you want to return the documents for all customers which happen to have a COMMENT child element:

... WHERE XMLEXISTS('$doc[CUSTOMER/COMMENT ] ' 
   PASSING xmlcol AS "doc") ;