error executing query with `WHERE <multiple cols> in`

[Andrew-Bx]

Describe the bug

I'm trying to execute a query that does WHERE (col1, col2) in (SELECT ...) but getting an error. The query runs fine in postgres.

Error is:

QueryFailedError: cannot cast type uuid to timestamptz

To Reproduce

CREATE TABLE client2
(
    id uuid NOT NULL,
    CONSTRAINT "PK_client2" PRIMARY KEY (id)
);

CREATE TABLE client2_log
(
    id uuid NOT NULL,
    "syncDate" timestamp with time zone NOT NULL,
    "actionsSynced" integer NOT NULL,
    "clientId" uuid,
    CONSTRAINT "PK_client2_log" PRIMARY KEY (id),
    CONSTRAINT "FK_client2_log_client2" FOREIGN KEY ("clientId")
        REFERENCES client2 (id) MATCH SIMPLE
);

CREATE INDEX "IDX_client2_log_clientid_syncdate"
    ON client2_log USING btree
    ("clientId" ASC NULLS LAST, "syncDate" ASC NULLS LAST)
;

SELECT *
FROM
	client2_log
	INNER JOIN client2 ON client2.id = client2_log."clientId"
WHERE
	("clientId", "syncDate") IN (
		SELECT
			"clientId",
			MAX("syncDate")
		FROM
			client2_log
		GROUP BY
			"clientId"
	);

pg-mem version

3.0.5

[Andrew-Bx]

Since the error seems like it might be a slight red herring, I swapped the date column for a text column:

CREATE TABLE client3
(
    id uuid NOT NULL,
    CONSTRAINT "PK_client3" PRIMARY KEY (id)
);

CREATE TABLE client3_log
(
    id uuid NOT NULL,
    -- "syncDate" timestamp with time zone NOT NULL,
	"name" text,
    "actionsSynced" integer NOT NULL,
    "clientId" uuid,
    CONSTRAINT "PK_client3_log" PRIMARY KEY (id),
    CONSTRAINT "FK_client3_log_client3" FOREIGN KEY ("clientId")
        REFERENCES client3 (id) MATCH SIMPLE
);

CREATE INDEX "IDX_client3_log_clientid_name"
    ON client3_log USING btree
    ("clientId" ASC NULLS LAST, "name" ASC NULLS LAST)
;

SELECT *
FROM
	client3_log
	INNER JOIN client3 ON client3.id = client3_log."clientId"
WHERE
	("clientId", "name") IN (
		SELECT
			"clientId",
			MAX("name")
		FROM
			client3_log
		GROUP BY
			"clientId"
	);

Which gives the error:

subquery must return only one column

[Zomono]

(1) I had the same error. The tuple-syntax seems to be the problem. If for example the syntax is replaced with long chains of (A=A1 and B=B1) or (A=A2 and B=B2) the query runs successfully.

(2) I also have the problem when tuple comparisons are used in join-clauses like join b on (a.c1, a.c2) = (b.c1, b.c2). And again the problems is solved, when I execute the same query but with and-or-syntax instead of the tuple.

(3) The third query that failed with tuples is about the any keyword. select * from a where (a.c1, a.c2) = any(array(row('abc', 123), row('qwe', 456)));

I do not found an alternative syntax for the last one and it is the most important use case in my application, because the any-syntax is very useful to create prepared statements where the array is provided as prepared parameter.

So I really hope for a fix. Thanks for the great work on pg-mem so far. It improves our tests significantly.