SQL query with AND/OR operator on typeorm shows unexpected behavior

[Gabriel-LUCAS17]

Describe the bug

I'm trying to make some tests on typeorm with jest and I use pg-mem to simulate my postgres database. But one of the queries I'm using return an empty array with pg-mem while it should return some elements.

The query :

await ReaderRepository.create({
    id: v4(),
    name: 'r1',
    type: 'rfid'
}).save()

const test = await ReaderRepository.find({
    where: [
        {zone_in: IsNull(), type:'rfid'},
        {zone_out: IsNull(), type: 'rfid'}
    ],
    relations: {
        zone_in: true,
        zone_out: true
    }
})
console.log(test)

With pg-mem :

console.log
    []

With postgres (expected result) :

console.log
    [
      Reader {
        id: '4b9d1121-8524-4ef6-ace2-48e7176d63b5',
        name: 'r1',
        type: 'rfid',
        zone_in: null,
        zone_out: null
      }
    ]

I suspect it to be related to the behavior of AND / OR operators because :

this works :

const test = await ReaderRepository.find({
    where: [
        {zone_in: IsNull()},
        {zone_out: IsNull()}
    ]
})

this works :

const test = await ReaderRepository.find({
    where: {
        zone_in: IsNull(),
        type: 'rfid'
    }
})

and this works as well :

const test = await ReaderRepository.find({
    where: {
        zone_out: IsNull(),
        type: 'rfid'
    }
})

but this doesn't work :

const test = await ReaderRepository.find({
    where: {
        zone_in: IsNull(),
        zone_out: IsNull(),
        type: 'rfid'
    }
})

neither this :

const test = await ReaderRepository.find({
    where: [
        {zone_in: IsNull(), type: 'rfid'},
        {zone_out: IsNull(), type: 'rfid'}
    ]
})

To Reproduce

Reader.ts :

import { Marking } from './Marking'

@ObjectType()
@Entity()
@Unique('unique_name_type', ['name', 'type'])
class Reader extends BaseEntity {
  @Field(_type => ID)
  @PrimaryGeneratedColumn('uuid')
  id!: string

  @Field(_type => String)
  @Column({ nullable: false })
  name!: string

  @Field(_type => String)
  @Column({nullable: false})
  type!: string

  @Field(_type => Marking, {nullable : true})
  @OneToOne(() => Marking, {onDelete: "SET NULL"})
  @JoinColumn()
  zone_in?: Marking

  @Field(_type => Marking, {nullable : true})
  @OneToOne(() => Marking, {onDelete: "SET NULL"})
  @JoinColumn()
  zone_out?: Marking
}

Marking.ts

@ObjectType()
@Entity()
class Marking extends BaseEntity {
  @Field(_type => ID)
  @PrimaryGeneratedColumn('uuid', {primaryKeyConstraintName: "pk_marking_id"})
  id!: string

  @Field(_type => String)
  @Column({ type: 'text' })
  type!: 'polygon' | 'line'

  @Field({ description: `Custom area name.` })
  @Column({ type: 'text' })
  name!: string

  @Field({ description: `Pre-defined marking type.`})
  @Column({ type: 'text' })
  category!: string

  @Field(_type => [[Float, Float, Float]])
  @Column({
    type: 'json',
    transformer: {
      to: JSON.stringify,
      from: JSON.parse,
    },
  })
  points!: [x: number, y: number, z: number][] | null

  @Field({
    description: `
      ex: blue, red... Insert color identifier instead of hex code to handle
      eventuals color palette changes.
    `,
  })
  @Column({ type: 'text' })
  color!: string

  @Field(_type => String)
  @CreateDateColumn({
    type: 'timestamp',
    default: () => 'CURRENT_TIMESTAMP',
  })
  createdAt!: Date

  @Field(_type => String)
  @UpdateDateColumn({
    type: 'timestamp',
    default: () => 'CURRENT_TIMESTAMP',
    onUpdate: 'CURRENT_TIMESTAMP',
  })
  updatedAt!: Date
}

connection.ts

import { IMemoryDb, newDb } from 'pg-mem'
import { DataSource } from 'typeorm'
import { v4 } from 'uuid'

type FakeDbResponse = {
    db: IMemoryDb
    dataSource: DataSource
  }

  export const makeFakeDb = async (entities?: any[]): Promise<FakeDbResponse> => {
    const db = newDb({
      autoCreateForeignKeyIndices: true
    })

    db.public.registerFunction({
      implementation: () => 'test',
      name: 'current_database'
    })
    db.public.registerFunction({
        implementation: () => 'test',
        name: 'version'
    })
    db.public.registerFunction({
      implementation: () => v4(),
      name: 'uuid_generate_v4'
    })

    const dataSource = await db.adapters.createTypeormDataSource({
      type: 'postgres',
      entities: entities ?? ['@/models'] // Your entities folder here
    })
    await dataSource.initialize()
    await dataSource.synchronize()

    return { db, dataSource }
  }

Test File

import { Marking, Reader } from "@/models"
import { IBackup } from "pg-mem"
import { DataSource, IsNull, Repository } from "typeorm"
import { makeFakeDb } from "./connection"

describe('db tests', () => {
    let dataSource: DataSource
    let backup: IBackup
    let pgReaderRepo: Repository<Reader>

    beforeAll(async () => {
        const fakeDb = await makeFakeDb([
            Reader,
            Marking,
        ])

        dataSource = fakeDb.dataSource
        backup = fakeDb.db.backup()
        ReaderRepository = dataSource.getRepository(Reader)
    }

    beforeEach(() => {
        backup.restore()
    })

    afterAll(async () => {
        await dataSource.destroy()
    })

    it('should return only readers of type given which have missing zones', async () => {
        await ReaderRepository.create({
            id: v4(),
            name: 'r1',
            type: 'rfid'
        }).save()

        const result = await ReaderRepository.find({
            where: [
                {zone_in: IsNull(), type: 'rfid'},
                {zone_out: IsNull(), type: 'rfid'}
            ],
            relations: {
                zone_in: true,
                zone_out: true
            }
        })
        expect(result?.map(e => e.name)).toStrictEqual(['r1'])
    })
}

pg-mem version

"version": "2.6.12",
  "description": "A memory version of postgres",
    "release-deno": "git add -A && git commit -m \"Build deno [autogenerated commit]\" && PACKAGE_VERSION=$(cat package.json | grep \\\"version\\\" | head -1 | awk -F: '{ print $2 }' | sed 's/[\",]//g' | tr -d '[[:space:]]') && git tag $PACKAGE_VERSION && git push --tags",

typeorm version

"version": "0.3.11",

[elarrat]

I have the exact same behavior, also involving is null being used twice in the same query

[noamantebi]

Any news on this? I also encountere the same behavior.

[rafahuaman]

I'm running into similar issues on an entity with @DeleteDateColumn.

This query sometimes returns null and sometimes returns the inserted record.

  const result = await dataSource.manager.findOne(SomeEntity, {
    where: { id: item.id },
  });
  console.log("result:", result);
  //  result : null

The flakiness seems to be related to a IS NULL where clause because when I add withDeleted: true (which removes the AND ( "SomeEntity"."deletedAt" IS NULL )), the query always return the inserted record.

  const result = await dataSource.manager.findOne(SomeEntity, {
    where: { id: item.id },
    withDeleted: true,
  });
  console.log("result:", result);
  //  result: SomeEntity {
  //    id: 'f480813f-213f-4ace-b0b6-0e8e1e069bcd',
  //    ...
  //    deletedAt: null
  //  }  

I can run both queries one after the other and results will be different.