= ANY does not return any rows

[Lewenhaupt]

Describe the bug

Does not appear as if ANY queries with ARRAYs are supported. Tried to query on just one id and then it works, but as soon as I attempt to query using ANY and ARRAY (i.e. ANY(ARRAY[1,2]) ) it does not return anything.

To Reproduce

-- create tables
CREATE TABLE "user" ("id" SERIAL NOT NULL, "name" text NOT NULL, CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY ("id"));

-- insert data
INSERT INTO "user"("name") VALUES ('me') RETURNING "id";
SELECT "id" FROM "user" WHERE "id"=1; -- Works and returns the row
SELECT "id" FROM "user" WHERE "id"=ANY(ARRAY[1]); -- Does not work
SELECT "id" FROM "user" WHERE "id"=ANY('{1}'); -- Does not work either

pg-mem version

2.6.13

[Lewenhaupt]

@oguimbal Yeah I can't get it to work when actually running it in the playground or in my own tests. But I did try to add a unit test to pg-mem to see if that had the same problem, but it didn't?

Added this in select.queries.spec.ts and it passes... Both in main and latest tag 2.6.13

it('can filter on any', () => {
        stuff();
        expect(many(`select * from test where val = any('{999}');`))
            .to.deep.equal([
                { txt: 'A', val: 999 },
            ])
    });

[Lewenhaupt]

@oguimbal Any input?

[rswheeldon]

Interestingly if you do:

select * from "user" where "name" = any(array['me']);

on your example it does work. What the difference is I haven't yet figured out but it's annoying because the thing I'm trying to test runs into similar problems (pg-mem version 2.8.1 FWIW)

[rswheeldon]

@Lewenhaupt Interestingly this also works - which might be a useful workaround for your case? I wouldn't necessarily recommend it for production use-cases since the index usage is liable to be worse.

select * from "user" where array["id"::integer] && array[1];