Re: BUG #5434: select from xpath_table gives wrong results

On Thu, Apr 22, 2010 at 4:45 AM, Oleg <sacramento_fan(at)mail(dot)ru> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5434
> Logged by:          Oleg
> Email address:      sacramento_fan(at)mail(dot)ru
> PostgreSQL version: 8.4.3
> Operating system:   KUbuntu
> Description:        select from xpath_table gives wrong results
> Details:
>
> Here is a code:
>
> create table tmp_xml(rowId SERIAL UNIQUE NOT NULL,
>                  xml_context xml,
>                  CONSTRAINT PK_tmp_xml PRIMARY KEY (rowId));
>
> insert into tmp_xml (xml_context)
> values('<root>'
>         '<obj obj_id="1" obj_name="First Object"/>'
>         '<obj obj_id="2" obj_name="Second Object" obj_master="1"/>'
>         '<obj obj_id="3" obj_name="Third Object"/>'
>         '<obj obj_id="4" obj_name="Fourth Object" obj_master="1"/>'
>       '</root>');
>
> SELECT t.OBJ_ID, t.OBJ_NAME, t.OBJ_MASTER
> FROM xpath_table('rowId',
>                 'xml_context',
>                'tmp_xml',
>
> '/root/obj/@obj_id|/root/obj/@obj_name|/root/obj/@obj_master|',
>                 'true')
>        AS t(row_id integer,
>             OBJ_ID integer,
>             OBJ_NAME varchar(255),
>             OBJ_MASTER INT
>             );
>
> I was sure, that result of this query should be like this:
>
> obj_id | obj_name        | obj_master
> ----------------------------------------
>   1   |   First Object  |
>   2   |   Second Object |    1
>   3   |   Third Object  |
>   4   |   Fourth Object |    1
>
> But instead of I got this result:
>
> obj_id | obj_name        | obj_master
> ----------------------------------------
>   1   |   First Object  |    1
>   2   |   Second Object |    1
>   3   |   Third Object  |
>   4   |   Fourth Object |
>
> Why is this so? Maybe I use xpath_table wrong way?

xpath_table() doesn't understand that the xpaths you're giving it are
interrelated. It evaluates them independently of each other. I
suppose /root/obj/@obj_master only matches twice, and
/root/obj/@obj_id matches four times. xpath_table doesn't know or
care that the second match for the first expression and the second
match for the last expression are matching different parts of the
input text.

There has been some discussion of how to design an interface that
would allow users to avoid this rather surprising result, but I'm not
aware that anyone is working on it at present.

...Robert