pgsql-3938-ryan

Version:

8.3

Bug Link:

http://archives.postgresql.org/pgsql-bugs/2008-02/msg00044.php

Patch Link:

http://archives.postgresql.org/pgsql-committers/2008-02/msg00079.php

http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/indxpath.c?r1=1.226&r2=1.227;f=h

Symptom:

Row-wise comparison fails if the column order is different from the definition.

Failure type:

Incorrect result (rejecting valid input)

Is there any log message?

Yes

How it is diagnosed:

Reproduced!

How to reproduce:

postgres=# CREATE TABLE test

(

  id integer,

  str1 character varying(5),

  str2 character

);

CREATE TABLE

postgres=# CREATE INDEX test_index1 ON test (str1, str2);

CREATE INDEX

postgres=# insert into test (id, str1, str2) values(1, 'a', '1');

INSERT 0 1

postgres=# insert into test (id, str1, str2) values(2, 'b', '2');

INSERT 0 1

postgres=# SELECT * from test where (str1, str2, id) > ('a', '1', 0);

ERROR:  could not find member 4(25,25) of opfamily 426

<---if we change the order of columns, it works

postgres=# SELECT * from test where (id,str1, str2) > (0,'a', '1');

 id | str1 | str2

----+------+------

  1 | a    | 1

  2 | b    | 2

(2 rows)

Root Cause:

in expand_indexqual_rowcompare --- in converting a forboth()
into an iteration over three parallel lists, the next
steps are accidentally put outside the loop!

expand_indexqual_rowcompare(...)

{

        ...

        /* Return clause as-is if it's all usable as index quals */

        if (matching_cols == list_length(clause->opnos))

                return rinfo;

        if (op_strategy == BTLessEqualStrategyNumber...)

        {

                new_ops = list_truncate(list_copy(clause->opnos), matching_cols);

        }

        else

        {

                ListCell   *opfamilies_cell;

                ListCell   *lefttypes_cell;

                ListCell   *righttypes_cell;

                ...

                lefttypes_cell = list_head(lefttypes);

                righttypes_cell = list_head(righttypes);

                foreach(opfamilies_cell, opfamilies)

                {

                        Oid                        opfam = lfirst_oid(opfamilies_cell);

                        Oid                        lefttype = lfirst_oid(lefttypes_cell);

                        Oid                        righttype = lfirst_oid(righttypes_cell);

                        expr_op = get_opfamily_member(opfam, lefttype, righttype,

                                                                                  op_strategy);

                        if (!OidIsValid(expr_op))        /* should not happen */

                        <-- fails here on the second iteration!!

                                elog(ERROR, "could not find member %d(%u,%u) of                                                 opfamily %u",op_strategy, lefttype, righttype,                                         opfam);

                        if (!var_on_left)

                        {

                                expr_op = get_commutator(expr_op);

                                if (!OidIsValid(expr_op))                /* should not happen */

                                        elog(ERROR, "could not find commutator of                                                                 member %d(%u,%u) of opfamily                                                                 %u",op_strategy, lefttype, righttype, opfam);

                        }

                        new_ops = lappend_oid(new_ops, expr_op);

+                        lefttypes_cell = lnext(lefttypes_cell);

+                        righttypes_cell = lnext(righttypes_cell);

                }

-                lefttypes_cell = lnext(lefttypes_cell);

-                righttypes_cell = lnext(righttypes_cell);

        }

...

}

Pattern:

return value check