findOne({ someField: undefined }) vs findOne({ someField: null})

[meuter]

Issue type:

[x] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[x] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

When calling findOne() on a table with a column that is marked as nullable, if you pass undefined for that column in the select condition, the query does find any entry. If you pass null instead of undefined, there there is a typing error (see below for the exact message)

Here's a small example to reproduce the problem:

@Entity()
class Person {

    @PrimaryGeneratedColumn()
    id!: number

    @Column()
    name!: string

    @Column({ nullable: true })
    age?: number
}


(async () => {

    const connection = await createConnection({
        type: "sqlite",
        database: ":memory:",
        synchronize: true,
        logging: true,
        entities: [Person]
    })

    const repo = connection.getRepository(Person)
    const name = "John"

    await repo.save(repo.create({ name }))

    const result = [
        await repo.findOne({ name, }),  // (1) works OK
        await repo.findOne({ name, age: undefined }), // (2) returns undefined
        await repo.findOne({ name, age: null }), // (3) does not compile, typing error
    ]

    console.log(result)
})()

When you try and run this with ts-node, this you get typing error on (3):

    return new TSError(diagnosticText, diagnosticCodes)
           ^
TSError: ⨯ Unable to compile TypeScript:
source/backend/index.ts:92:15 - error TS2769: No overload matches this call.
  Overload 1 of 3, '(id?: string | number | Date | ObjectID | undefined, options?: FindOneOptions<Person> 
| undefined): Promise<Person | undefined>', gave the following error.
    Argument of type '{ name: string; age: null; }' is not assignable to parameter of type 'string | number | Date | ObjectID | undefined'.
      Object literal may only specify known properties, and 'name' does not exist in type 'Date | ObjectID'.
  Overload 2 of 3, '(options?: FindOneOptions<Person> | undefined): Promise<Person | undefined>', gave the following error.
    Argument of type '{ name: string; age: null; }' is not assignable to parameter of type 'FindOneOptions<Person>'.
      Object literal may only specify known properties, and 'name' does not exist in type 'FindOneOptions<Person>'.
  Overload 3 of 3, '(conditions?: FindConditions<Person> | undefined, options?: FindOneOptions<Person> | undefined): Promise<Person | undefined>', gave the following error.
    Argument of type '{ name: string; age: null; }' is not assignable to parameter of type 'FindConditions<Person>'.
      Types of property 'age' are incompatible.
        Type 'null' is not assignable to type 'number | FindOperator<number | undefined> | undefined'.    

92         await repo.findOne({ name, age: null }),
                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


    at createTSError (C:\Code\mixarr\node_modules\ts-node\src\index.ts:293:12)
    at reportTSError (C:\Code\mixarr\node_modules\ts-node\src\index.ts:297:19)
    at getOutput (C:\Code\mixarr\node_modules\ts-node\src\index.ts:399:34)
    at Object.compile (C:\Code\mixarr\node_modules\ts-node\src\index.ts:457:32)
    at Module.m._compile (C:\Code\mixarr\node_modules\ts-node\src\index.ts:536:43)
    at Module._extensions..js (internal/modules/cjs/loader.js:995:10)
    at Object.require.extensions.<computed> [as .ts] (C:\Code\mixarr\node_modules\ts-node\src\index.ts:539:12)
    at Module.load (internal/modules/cjs/loader.js:815:32)
    at Function.Module._load (internal/modules/cjs/loader.js:727:14)
    at Function.Module.runMain (internal/modules/cjs/loader.js:1047:10)
error Command failed with exit code 1.

If you comment out (3), here's the output:

query: BEGIN TRANSACTION
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" IN ('person')
query: SELECT * FROM "sqlite_master" WHERE "type" = 'index' AND "tbl_name" IN ('person')
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'typeorm_metadata'
query: CREATE TABLE "person" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar NOT NULL, "age" integer)
query: COMMIT
query: BEGIN TRANSACTION
query: INSERT INTO "person"("id", "name", "age") VALUES (NULL, ?, NULL) -- PARAMETERS: ["John"]
query: COMMIT

// Here are the generated query
query: SELECT "Person"."id" AS "Person_id", "Person"."name" AS "Person_name", "Person"."age" AS "Person_age" FROM "person" "Person" WHERE "Person"."name" = ? LIMIT 1 -- PARAMETERS: ["John"]
query: SELECT "Person"."id" AS "Person_id", "Person"."name" AS "Person_name", "Person"."age" AS "Person_age" FROM "person" "Person" WHERE "Person"."name" = ? AND "Person"."age" = ? LIMIT 1 -- PARAMETERS: ["John",null]

// Here's the result (1) works, and (2) is undefined
[ Person { id: 1, name: 'John', age: null }, undefined ]

So given a nameand an age (potentially undefined), I don't know how to find a match properly:

• if I call findOne({name,age}) i get undefined
• if I call findOne({name, age: age || null} i get an error.

I would expect findOne({name,age}) to return the match in this case.

[xileftenurb]

as a possible workaround, you could use this syntax :

{
    name,
    ...age && {age}
}

this will send to typeorm {name} if age is truthy, and {name, age} if it's falsy.
of course, it would be better to have a correct handling on the typeorm side, but for the moment, the workaround should work.

[meuter]

Thanks for the workaround!

[imnotjames]

Duplicate of #3416