Re: Puzzled by ROW constructor behaviour?

On Tue, Nov 22, 2022 at 2:11 PM Eagna <eagna(at)protonmail(dot)com> wrote:

>
> NUMBER 2
>
> > SELECT ROW(1, 2.5, 'this is a test') = (VALUES (1, 2.5, 'this is a
> test')) AS test2;
>
> My question is that if a ROW constructor works for a VALUES clause in
> statement NUMBER 2, then why not NUMBER 3?
>

You've drawn a false equivalence from the similarity of the syntax.

The following also works:

SELECT 1 = (SELECT 1*1);
But I don't think there is any argument that while this works:

INSERT INTO tbl (col) SELECT 1*1;

This doesn't:

INSERT INTO tbl (col) 1;

There is no difference with replacing 1 with a composite type and the
SELECT subquery with VALUES instead.

Your "VALUES" is just a scalar subquery expression that, if it indeed
produces a single row and column, can be compared to any other scalar value
(if it produces more than one row you will get an error - try it).

David J.