Different results between pg-mem and postgresql => is null

[jaassoon]

Describe the bug

Hi, guys! I defined table as below . I found that it works well in https://oguimbal.github.io/pg-mem-playground/. But it always return empty array when I query in jest test case.

To Reproduce

create table foo(
id uuid not null,
sub_id uuid,
bar integer
)

-- then 
insert into foo values('2c8ae58d-7d5a-47f4-b8c2-ee61154a46bd',null,2),( '3c7c772e-bb92-414e-8c83-44f50fbf43ec',null,3);

-- then
select f.* from foo as f left join  foo as foo_sub on foo_sub.sub_id = f.id where foo_sub.id is null;

test case

import { newDb } from 'pg-mem';
import { DataSource, Column, Entity, PrimaryColumn } from "typeorm";

@Entity({ name: 'foo' })
export class FooEntity {
    @PrimaryColumn({ name: 'id', type: 'uuid' })
    id: string;
    @Column({ name: 'sub_id', type: 'uuid', nullable:true })
    subId: string;
    @Column({ name: 'bar', type: 'integer' })
    bar: number;
}
describe('test', () => {
    let dataSource: DataSource;
    beforeEach(async () => {
        const db = newDb();
        db.public.registerFunction({
            implementation: () => 'test',
            name: 'version'
        });
        db.public.registerFunction({
            implementation: () => 'test',
            name: 'current_database'
        });
        db.createSchema('test');

        dataSource = await db.adapters.createTypeormDataSource({
            type: 'postgres',
            name: 'default',
            host: 'localhost',
            port: 5432,
            schema: 'test',
            database: 'test',
            username: 'postgres',
            password: 'postgres',
            entities: [FooEntity],
        });
        await dataSource.initialize();
        await dataSource.synchronize();
        db.getSchema('test').many(`insert into test.foo values('2c8ae58d-7d5a-47f4-b8c2-ee61154a46bd',null,2);
           insert into test.foo values('3c7c772e-bb92-414e-8c83-44f50fbf43ec',null,3);`);
    });

    afterEach(async ()=>{
        await dataSource.destroy()
    })
    it('it should be',async ()=>{
        const a = await dataSource.createQueryRunner().query(`select r.* from test.foo r`);
        console.log(a);
        const b = await dataSource.createQueryRunner().query(`select r.* from test.foo r
        left join test.foo lr on lr.sub_id = r.id
        where lr.id is null`);
        console.log(b);
        expect(b).toHaveLength(2);
        expect(b[0]).toEqual(
          expect.objectContaining({
             id: expect.any(String),
             sub_id: null,
             bar: 2
          })
          );
    })
})

pg-mem version

2.6.6

[oguimbal]

Hi !

The problem comes from this:

        db.public.none(`insert into test.foo values('2c8ae58d-7d5a-47f4-b8c2-ee61154a46bd',null,2);`);
        db.public.none(`insert into test.foo values('3c7c772e-bb92-414e-8c83-44f50fbf43ec',null,3);`);

Which inserts in the "public" schema.

Use db.getSchema('test').none(...) instead :)

[jaassoon]

@oguimbal Thank you for your reply. I try to use db.getSchema('test').none(...), but it still not work. Where am I going wrong?

[oguimbal]

Ok right, I found the issue, i'm reoping this. Will fix

[oguimbal]

Should be fixed in pg-mem@2.6.7

[jaassoon]

@oguimbal I am sorry again. It still not works, there is something changed though. Actually, it returns 2 records, but they are not I expected. At the moment, it returns:

[
{id:null,sub_id:null,bar:null},
{id:null,sub_id:null,bar:null}
]

I think the first row should be: {id:'uuid_xxx',sub_id:null,bar:2},

[jaassoon]

If I use pgmem to create table, it works. Something like that:

        db.getSchema('test').many(`
           CREATE TABLE "test"."foo" ("id" uuid NOT NULL, "sub_id" uuid, "bar" integer NOT NULL);
           insert into test.foo values('2c8ae58d-7d5a-47f4-b8c2-ee61154a46bd',null,2);
           insert into test.foo values('3c7c772e-bb92-414e-8c83-44f50fbf43ec',null,3);`);

But it still not work if I use the DDL generated by typeorm

        db.getSchema('test').many(`
           CREATE TABLE "test"."foo" ("id" uuid NOT NULL, "sub_id" uuid, "bar" integer NOT NULL, CONSTRAINT "PK_....." PRIMARY KEY ("id"));
           insert into test.foo values('2c8ae58d-7d5a-47f4-b8c2-ee61154a46bd',null,2);
           insert into test.foo values('3c7c772e-bb92-414e-8c83-44f50fbf43ec',null,3);`);

I thing , CONSTRAINT "PK_....." PRIMARY KEY ("id") is the point.

If I create table by typeorm, it not works. I guess the auto generated DDL is difference with the one above. By typeorm:

@Entity({ name: 'foo' })
export class FooEntity {
    @PrimaryColumn({ name: 'id', type: 'uuid' })
    id: string;
    @Column({ name: 'sub_id', type: 'uuid', nullable:true })
    subId: string;
    @Column({ name: 'bar', type: 'integer' })
    bar: number;
}
...
dataSource = await db.adapters.createTypeormDataSource({
           ...
            entities: [FooEntity],
        }); 

[jaassoon]

Below is the table structure auto generate by typeorm in postgresql:

\d foo
table : test.foo
column  type  nullable
id    uuid     not null
sub_id uuid 
bar integer not null

indexes:
"PK_......" primary key, btree(id)

But I dont know how to describe table in pgmem...

anyway, I can confirm the DDL from the logs. CREATE TABLE "test"."foo" ("id" uuid NOT NULL, "sub_id" uuid, "bar" integer NOT NULL , constraint "PK_..." PRIMARY KEY ("id) );

[jaassoon]

One more thing. It works following the steps as below:

1. auto generate table by typeorm Entity
2. drop table test.foo by pgmemdb.getSchema('test').none(...)
3. create table test.too by pgmemdb.getSchema('test').none(...)

[oguimbal]

Indeed. I fucked-up my unit test. Fixing it...

[oguimbal]

is it fixed with pg-mem@2.6.8 ?

(should be this time...)

[jaassoon]

@oguimbal Oh, it works like a charm! Thank you very much for your rapid fixing!