Search code examples
sqlpostgresqlcomparisonrecord

Record type comparison with different numbers of columns isn't failing

Why does the following query not trigger a "cannot compare record types with different numbers of columns" error in PostgreSQL 11.6?

with
s AS (SELECT 1)
, main AS (
SELECT (a) = (b) , (a) = (a), (b) = (b), a, b -- I expect (a) = (b) fails
FROM s
  , LATERAL (select 1 as x, 2 as y) AS a
  , LATERAL (select 5 as x) AS b
)
select * from main;

While this one does:

with
x AS (SELECT 1)
, y AS (select 1, 2)
select (x) = (y) from x, y;
Solution

  • See the note in the docs on row comparison

    Errors related to the number or types of elements might not occur if the comparison is resolved using earlier columns.

    In this case, because a.x=1 and b.x=5, it returns false without ever noticing that the number of columns doesn't match. Change them to match, and you will get the same exception (which is also why the 2nd query does have that exception).

    testdb=# with
s AS (SELECT 1)
, main AS (
SELECT a = b , (a) = (a), (b) = (b), a, b -- I expect (a) = (b) fails
FROM s
  , LATERAL (select 5 as x, 2 as y) AS a
  , LATERAL (select 5 as x) AS b
)
select * from main;
ERROR:  cannot compare record types with different numbers of columns