MSSQL: Set boolean column to False tries to insert NULL

[grbspltt]

Issue Description

Trying to update an entity with a boolean property, set the value to false will create a DB error, Cannot insert the value NULL into column 'licActive', table 'vendor'; column does not allow nulls. Update fails.

Expected Behavior

If updating the property to false, SQL script should set the value to 0.

Actual Behavior

Vendor Schema

@Entity({name: 'vendors'})
class VendorEntity extends DefaultColumns implements IVendorEntity {
  @Column({type: 'nvarchar', length: 150, nullable: false})
  @IsString()
  @MaxLength(150)
  name: string;

 @Column({type: 'bit', nullable: false, default: 0})
  licActive: boolean;

@Column({type: 'smalldatetime', nullable: true})
  @IsDateString()
  @IsOptional()
  licFrom: Date | null;

  @Column({type: 'smalldatetime', nullable: true})
  @IsDateString()
  @IsOptional()
  licLastRefresh: Date | null;

  @Column({type: 'bit', nullable: false, default: 0})
  licNoVerified: boolean;

  @Column({type: 'smalldatetime', nullable: true})
  @IsDateString()
  @IsOptional()
  licTo: Date | null;
}

Update Code

async clearCSLB(id: number): Promise<any> {
    const now = new Date();
    const cslbUpdater: Partial<VendorEntity> = {
      licNoVerified: false,
      licActive: false,
      licFrom: null,
      licTo: null,
      licLastRefresh: now
    };

    return getRepository(VendorEntity)
      .createQueryBuilder()
      .update()
      .set(cslbUpdater)
      .where('id = :id', {id})
      .execute();
  }

Steps to Reproduce

1. Create entity
2. Update Entity with false in the boolean column

My Environment

Dependency	Version
Operating System	Windows 10
Node.js version	v12.18.3
Typescript version	v4.0.2
TypeORM version	v0.2.32

Additional Context

Error Output

query: 'UPDATE "vendors" SET "licTo" = @0, "licFrom" = @1, "licActive" = @2, "licNoVerified" = @3, "licLastRefresh" = @4, "version" = "version" + 1, "updated" = CURRENT_TIMESTAMP WHERE "id" = @5',
  parameters: [
    MssqlParameter {
      value: undefined,
      type: 'smalldatetime',
      params: []
    },
    MssqlParameter {
      value: undefined,
      type: 'smalldatetime',
      params: []
    },
    MssqlParameter { value: undefined, type: 'bit', params: [] },
    MssqlParameter { value: true, type: 'bit', params: [] },
    MssqlParameter {
      value: 2021-04-20T18:34:31.000Z,
      type: 'smalldatetime',
      params: []
    },
    10
  ]

Relevant Database Driver(s)

• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time, and I know how to start.
• Yes, I have the time, but I don't know how to start. I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[egedib]

I can't reproduce this issue.

The error output does not match your clearCSLB function.
The snippet you provided here specifies licFrom and licTo values as NULLs, so those can't be undefined parameters.
As for the boolean errors: I could only reproduce them if I set them to undefined in my clearCSLB function.

Is there another function that also updates vendors?

Additional data:

Dependency	Version
Operating System	Windows 10 x64
Node.js version	v12.16.3
Typescript version	v4.0.2
TypeORM version	v0.2.32

[grbspltt]

@egedib thank you so much for looking into this. The relevant code is
{
licNoVerified: false,
licActive: false,...

Those parameters are undefined instead of false. I have also tried to set to 0 instead of false but it gets the same error.

[egedib]

Can you compare my test repository with your solution? If you can, please run it and check if the same error is still there.

https://github.com/egedib/OrmBug_7581