BUG #4275: Multicolumn subquery expression not allowed on both sides of IS DISTINCT FROM

Started by Carl-Daniel Hailfingeralmost 18 years ago4 messagesbugs
Jump to latest
#1Carl-Daniel Hailfinger
c-d.hailfinger.devel.2006@gmx.net

The following bug has been logged online:

Bug reference: 4275
Logged by: Carl-Daniel Hailfinger
Email address: c-d.hailfinger.devel.2006@gmx.net
PostgreSQL version: 8.2.9
Operating system: openSUSE Linux 10.3, x86
Description: Multicolumn subquery expression not allowed on both
sides of IS DISTINCT FROM
Details:

The PostgreSQL docs say a multicolumn subquery expression is not allowed on
both sides of IS DISTINCT FROM. The implementation conforms to the docs, but
AFAICS the SQL:2003 standard allows multicolumn subquery expressions on both
sides of the operator.

This works:
# SELECT ROW(true,true)=(SELECT true,true);

This doesn't:
# SELECT (SELECT true,true)=(SELECT true,true);
Error: 42601: subquery must return only one column
Location: transformSubLink, parse_expr.c:1139

Expected result:
The second query has the same result as the first query (true).

Please allow me to explain why I think the standard allows multicolumn
subqueries on both sides of IS DISTINCT FROM:

<distinct predicate> ::= <row value predicand 3> <distinct predicate
part 2>

<distinct predicate part 2> ::= IS DISTINCT FROM <row value predicand
4>

<row value predicand 3> ::= <row value predicand>

<row value predicand 4> ::= <row value predicand>

<row value predicand> ::=
<row value special case>
| <row value constructor predicand>

<row value constructor predicand> ::=
<common value expression>
| <boolean predicand>
| <explicit row value constructor>

<explicit row value constructor> ::=
<left paren> <row value constructor element> <comma> <row value
constructor element list> <right paren>
| ROW <left paren> <row value constructor element list> <right
paren>
| <row subquery>

So the following should be valid:
<distinct predicate> ::= <row subquery> IS DISTINCT FROM <row
subquery>

Somebody in the #postgresql irc channel was so kind to test this on 8.3.3
with the same results.

Regards,
Carl-Daniel

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Carl-Daniel Hailfinger (#1)
Re: BUG #4275: Multicolumn subquery expression not allowed on both sides of IS DISTINCT FROM

"Carl-Daniel Hailfinger" <c-d.hailfinger.devel.2006@gmx.net> writes:

This works:
# SELECT ROW(true,true)=(SELECT true,true);

This doesn't:
# SELECT (SELECT true,true)=(SELECT true,true);
Error: 42601: subquery must return only one column

This is not a bug, since we do not claim support for feature F641.

regards, tom lane

#3Carl-Daniel Hailfinger
c-d.hailfinger.devel.2006@gmx.net
In reply to: Tom Lane (#2)
Re: BUG #4275: Multicolumn subquery expression not allowed on both sides of IS DISTINCT FROM

On 01.07.2008 16:32, Tom Lane wrote:

"Carl-Daniel Hailfinger" <c-d.hailfinger.devel.2006@gmx.net> writes:

This works:
# SELECT ROW(true,true)=(SELECT true,true);

This doesn't:
# SELECT (SELECT true,true)=(SELECT true,true);
Error: 42601: subquery must return only one column

This is not a bug, since we do not claim support for feature F641.

Thanks for the information. Is there any plan to support that feature in
8.4?

Regards,
Carl-Daniel

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Carl-Daniel Hailfinger (#3)
Re: BUG #4275: Multicolumn subquery expression not allowed on both sides of IS DISTINCT FROM

Carl-Daniel Hailfinger <c-d.hailfinger.devel.2006@gmx.net> writes:

On 01.07.2008 16:32, Tom Lane wrote:

This is not a bug, since we do not claim support for feature F641.

Thanks for the information. Is there any plan to support that feature in
8.4?

I don't know of anyone working on it at the moment.

regards, tom lane