Thread: BUG #3938: Row-wise comparison fails

The following bug has been logged online:

Bug reference:      3938
Logged by:
Email address:      cgriffo@practicepartner.com
PostgreSQL version: 8.3
Operating system:   Windows XP
Description:        Row-wise comparison fails
Details:

The row-wise compare fails in the select statement below. This works in
PostgreSQL 8.2 but fails in 8.3.

--drop TABLE test;

CREATE TABLE test
(
  id integer,
  str1 character varying(5),
  str2 character
);
CREATE INDEX test_index1 ON test (str1, str2);

insert into test (id, str1, str2) values(1, 'a', '1');
insert into test (id, str1, str2) values(2, 'b', '2');

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

cgriffo@practicepartner.com wrote:

> The row-wise compare fails in the select statement below. This works in
> PostgreSQL 8.2 but fails in 8.3.

Confirmed here.

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

The error message is:

alvherre=# SELECT * from test where (str1, str2, id) > ('a', '1', 0);
ERREUR:  could not find member 4(25,25) of opfamily 426

Note that if I change the order of columns, it works:

alvherre=# SELECT * from test where (id,str1, str2) > (0,'a', '1');
 id | str1 | str2
----+------+------
  1 | a    | 1
  2 | b    | 2
(2 lignes)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> alvherre=# SELECT * from test where (str1, str2, id) > ('a', '1', 0);
> ERREUR:  could not find member 4(25,25) of opfamily 426

> Note that if I change the order of columns, it works:

Weird.  I suppose I broke this in the operator-family rewrite.
Will look.

            regards, tom lane

I wrote:
> Weird.  I suppose I broke this in the operator-family rewrite.
> Will look.

Sigh ... I fat-fingered some loop control logic ...

Index: indxpath.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v
retrieving revision 1.226
diff -c -r1.226 indxpath.c
*** indxpath.c    1 Jan 2008 19:45:50 -0000    1.226
--- indxpath.c    7 Feb 2008 17:46:10 -0000
***************
*** 2619,2627 ****
                           op_strategy, lefttype, righttype, opfam);
              }
              new_ops = lappend_oid(new_ops, expr_op);
          }
-         lefttypes_cell = lnext(lefttypes_cell);
-         righttypes_cell = lnext(righttypes_cell);
      }

      /* If we have more than one matching col, create a subset rowcompare */
--- 2619,2627 ----
                           op_strategy, lefttype, righttype, opfam);
              }
              new_ops = lappend_oid(new_ops, expr_op);
+             lefttypes_cell = lnext(lefttypes_cell);
+             righttypes_cell = lnext(righttypes_cell);
          }
      }

      /* If we have more than one matching col, create a subset rowcompare */


            regards, tom lane