On Saturday, June 01, 2013 9:37 PM
> Row type literals constructed with ROW() cause an error when used in an
> IN
> clause (string literals casted appropriately are allowed). This is
> especially problematic since many client libraries use these literals
> to
> pass values of row-type arguments, hence making it impossible to use
> them in
> IN-clause queries.
>
> To wit:
> divide=# create type the_row as (mfg text, id text);
> CREATE TYPE
> divide=# create table the_table (widget the_row);
>
>
> CREATE TABLE
>
>
> divide=# insert into the_table values(row('foo', 'bar')::the_row);
>
>
> INSERT 0 1
>
>
> divide=# insert into the_table values('(bar,baz)'::the_row);
>
>
> INSERT 0 1
> divide=# select * from the_table;
> widget
> -----------
> (foo,bar)
> (bar,baz)
> (2 rows)
>
> divide=# select * from the_table where widget in
> ('(foo,bar)'::the_row);
> widget
> -----------
> (foo,bar)
> (1 row)
>
> divide=# select * from the_table where widget in
> (row('foo','bar')::the_row);
> ERROR: arguments of row IN must all be row expressions
> LINE 1: select * from the_table where widget in (row('foo','bar')::t...
The similar query for equal ('=') operator works fine.
select * from the_table where widget = (row('foo','bar')::the_row);
The reason for above is that in function transformAExprOp(..), it uses
make_row_comparison_op() to operate on expressions only if both left and right
are row expressions, else it will use make_op() to operate on expressions.
Refer code below in function transformAExprOp()
else if (lexpr && IsA(lexpr, RowExpr) &&
rexpr && IsA(rexpr, RowExpr))
{
....
result = make_row_comparison_op(pstate,
a->name,
((RowExpr *) lexpr)->args,
((RowExpr *) rexpr)->args,
a->location);
}
else
{
....
result = (Node *) make_op(pstate,
a->name,
lexpr,
rexpr,
a->location);
}
However for IN clause, if any one expr (left or right) is RowExpr, then it will
try to use make_row_comparison_op, which result in error.
Refer below code of function transformAExprIn():
if (haveRowExpr)
{
if (!IsA(lexpr, RowExpr) ||
!IsA(rexpr, RowExpr))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("arguments of row IN must all be row
expressions"),
parser_errposition(pstate,
a->location)));
cmp = make_row_comparison_op(pstate,
a->name,
(List *)
copyObject(((RowExpr *) lexpr)->args),
((RowExpr *) rexpr)->args,
a->location);
}
else
cmp = (Node *) make_op(pstate,
a->name,
copyObject(lexpr),
rexpr,
a->location);
Changing the functionality of transformAExprIn() similar to transformAExprOp()
will fix this issue, but not sure if there is any other side effect of same.
With Regards,
Amit Kapila.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
