oracle xmlcast(xmlquery) in subselect

Question

I have a table (Oracle 11.2.0.2) with an xmltype column and other non-xmltype columns. I want to do a select based on the value of some of the non-xmltype columns plus the contents of the xmltype column.

I've tried doing this using xmlquery in my select, but I'm getting some funny results. Here's a reduced version my data: an order table with the username and the order details.

-- create order table
create table orders(username varchar2(20), order_data xmltype);

-- an order entered by jim
insert into orders values ('jim', xmltype('
<ord:order xmlns:ord="http://www.blah.com/order/1.0">
   <ord:zip>123</ord:zip>
   <ord:date_time>2012-09-24T00:27:00</ord:date_time>
   <ord:item>
      <ord:product>A</ord:product>
      <ord:quantity>12</ord:quantity>
   </ord:item>
   <ord:item>
      <ord:product>B</ord:product>
      <ord:quantity>34</ord:quantity>
   </ord:item>
</ord:order>'));

-- an order entered by bob
insert into orders values ('bob', xmltype('
<ord:order xmlns:ord="http://www.blah.com/order/1.0">
   <ord:zip>123</ord:zip>
   <ord:date_time>2012-09-24T00:27:00</ord:date_time>
   <ord:item>
      <ord:product>A</ord:product>
      <ord:quantity>56</ord:quantity>
   </ord:item>
   <ord:item>
      <ord:product>C</ord:product>
      <ord:quantity>78</ord:quantity>
   </ord:item>
</ord:order>'));

As an example query, I tried to find all orders entered by Bob which have more than 1 item by doing this:

SELECT username, itemcount
FROM
  (SELECT username,
    XMLCAST(XMLQUERY('count(/*:order/*:item)' PASSING order_data RETURNING CONTENT) AS  NUMBER) itemcount
  FROM orders
  )
WHERE username = 'bob'
AND itemcount > 1

but it doesn't return any results, however if I comment out the last line (AND itemcount > 1) I get:

username | linecount
--------------------
bob      |         2

...I'm not sure why that is, I thought maybe it wasn't treating it as the correct type but I'm casting it as a number with the xmlcast.

What's more puzzling is if I set the last line to be:

AND itemcount = 2

...it disappears again, but if I change it to:

AND itemcount != -7

...it reappears in the results, but with a value of zero:

username | linecount
--------------------
bob      |         0

So, I'm puzzled - I must be doing something wrong but I can't see what it is.

I've found a workaround using xmltable which works fine, so that'll do me:

SELECT username, X.*
FROM orders,
  XMLTABLE ( 
      xmlnamespaces ('http://www.blah.com/order/1.0' AS "ord" ),
      '/ord:order' PASSING order_data
          COLUMNS 
              itemcount number PATH 'count(ord:item)' ) AS X
WHERE username = 'bob'
AND itemcount > 1

Answer 1

I think this is an Oracle bug. It works fine for me on 11.2.0.1, but fails just like you describe on 11.2.0.2. I looked at My Oracle Support but couldn't find anything. You may need to contact Oracle support for help resolving this problem.