Different results between pg-mem and postgresql

[SvenSeemann]

Hey,

I'm building a test case for my app and it occurred that there is different behaviour when executing a query with pg-mem and a postgres docker container.

I create my table and fill it with some test values:

CREATE SCHEMA IF NOT EXISTS permissions;
CREATE TABLE IF NOT EXISTS permissions.resource_shares(
            id varchar(100) not null,
            user_id varchar(100),
            group_id varchar(100),
            resource_name varchar(100) not null,
            permission varchar(100) not null,
            resource_id varchar(100) not null
        );

INSERT INTO permissions.resource_shares(id, user_id, group_id, resource_name, permission, resource_id)
        VALUES
            ('2c8ae58d-7d5a-47f4-b8c2-ee61154a46bd', '3c7c772e-bb92-414e-8c83-44f50fbf43ec', null, 'TestResource', 'CREATOR', '2e18b802-da72-42ac-834c-128b82e8d9d2'),
            ('22da9208-817b-4509-be59-a96ce41637c8', null, '19695c32-8493-47a2-8ae3-0ac87867e8b7', 'TestResource', 'READ', '2e18b802-da72-42ac-834c-128b82e8d9d2');

My tests case then basically runs this query (which should return the second row):

SELECT * 
        FROM permissions.resource_shares 
        WHERE 
            resource_name = 'TestResource' AND
            permission in ('CREATOR', 'DELETE', 'UPDATE', 'READ') AND
            (group_id in ('19695c32-8493-47a2-8ae3-0ac87867e8b7') OR user_id = 'SOMETHING_IRRELEVANT');    

Running this query with pg-mem returns an empty result, running the same code on a postgres docker container returns the second row (as expected).

Here is the example code to display the different behaviour: https://github.com/SvenSeemann/pg-mem-bug

[oguimbal]

Hi,

Wops... that is a huge bug (I hate silent bugs).

I narrowed it down... it seems to happen when there is one of your OR operands that evaluates to NULL.

CREATE TABLE IF NOT EXISTS test(
            id varchar(100) not null,
            val text
        );

INSERT INTO test(id, val) VALUES ('a', null), ('b', 'row b');

SELECT *  FROM test
        WHERE id in ('a', 'b') OR val = 'SOMETHING_IRRELEVANT';

👉 This only returns row B

[oguimbal]

This is too important not to be fixed right now, I just shipped pg-mem@1.9.11 which should solve this.

Reopen if it is not the case !

Thanks for this report 😱