[pg] Join by columns with different types

[rekby]

Run with ydb-local https://github.com/ydb-platform/ydb/wiki/Local-run-postgres-tests

echo "
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t1 (id1 int4, PRIMARY KEY(id1));
  CREATE TABLE t2 (id2 int8, PRIMARY KEY(id2));
  SELECT * FROM t1 JOIN t2 ON (id1=id2);
  " | psql postgres://root:1234@localhost:5432/local

Result:

DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
Status: GENERIC_ERROR
Issues:
<main>: Error: Type annotation, code: 1030
    <main>:1:1: Error: At function: AssumeColumnOrder, At function: PgReplaceUnknown, At function: OrderedMap, At function: OrderedMap, At function: EquiJoin
        <main>:1:1: Error: Cannot compare key columns (a._alias_t1.id1 has type: pgint4, b._alias_t2.id2 has type: pgint8)

Expected result:

DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
 id1 | id2
-----+-----
(0 rows)

[rekby]

It is similar to https://github.com/ydb-platform/ydb/issues/2957

[marsaly79]

PG equality operator is resolved correctly. The optimizer tries to use EquiJoin which can only handle equal types atm. The quick fix would be to not use EquiJoin if its LHS & RHS types aren't the same.

[marsaly79]

Here is the list of PG's = operators, which compare different types:

532, 533: int2 -> int4
1862, 1868: int2 -> int8
15, 416: int4 -> int8
254, 260: name -> text
353, : xid -> int4
1120, 1130: float4 -> float8
2347, 2373: date -> timestamp
2360, 2386: date -> timestamptz
2536, 2542: timestamp -> timestamptz

The first id is lhs -> rhs conversion, the second is that of rhs -> lhs conversion. There is no xid -> int4 cast in pg_cast.dat, this case will be fixed later.

[marsaly79]

5556

[marsaly79]

https://nda.ya.ru/t/cCWka2tK76WymX