.save() is throwing duplicate key value violates unique constraint when uuid is lowercase

sbogeFS commented 1 year ago

Issue description

Expected Behavior

I expect that the data is saved in teh database without any problems as everything works out when I convert uuids to uppercase.

Actual Behavior

I get the following error when I send uuids in lower case through 'postman': `QueryFailedError: Error: Violation of PRIMARY KEY constraint 'PK_2e27cd7b3d79c50d197cb0b3924'. Cannot insert duplicate key in object 'XXX.demand'. The duplicate key value is (58b1b016-54ed-477d-95e0-61a0f3fb3a61). at c:\Users\XXX\node_modules\typeorm\driver\sqlserver\SqlServerQueryRunner.js:200:30 at c:\Users\XXX\node_modules\mssql\lib\base\request.js:440:25 at Request.userCallback (c:\UsersXXX\node_modules\mssql\lib\tedious\request.js:492:15) at Request.callback (c:\Users\XXX\node_modules\tedious\lib\request.js:205:14) at Parser.onEndOfMessage (c:\Users\XXX\node_modules\tedious\lib\connection.js:2823:22) at Object.onceWrapper (c:\Users\XXX\lib\events.js:631:28) at Parser.emit (c:\Users\XXX\lib\events.js:517:28) at Readable. (c:\Users\XXX\node_modules\tedious\lib\token\token-stream-parser.js:32:12) at Readable.emit (c:\Users\XXX\lib\events.js:517:28) at endReadableNT (node:internal/streams/readable:1368:12) {query: 'DECLARE @OutputTable TABLE ("lastUpdate" date…@6, @7, DEFAULT);

SELECT * FROM @OutputTable', parameters: Array(8), driverError: RequestError: Violation of PRIMARY KEY cons…7cd7b3d79c50d197cb0b3924'. Cannot insert d…, code: 'EREQUEST', originalError: Error: Violation of PRIMARY KEY constraint…9c50d197cb0b3924'. Cannot insert duplicat…, …}`

Steps to reproduce

I have defined the following entity `@Entity() export class Demand implements BasicEntityInterface { @PrimaryColumn('uuid') @AutoMap() id: string

@UpdateDateColumn() @AutoMap(() => Date) lastUpdate?: Date

@Column({ type: 'text', nullable: true }) @AutoMap() note?: string

@Column({ type: 'int' }) @AutoMap() amount: number

@Column({ type: 'int', nullable: true }) @AutoMap() previousAmount?: number

@ManyToOne(() => TransportUnitType, { nullable: false }) @AutoMap(() => TransportUnitType) transportUnitType: TransportUnitType

@ManyToOne(() => Material, { nullable: false }) @AutoMap(() => Material) materialType: Material

@ManyToOne(() => DayDemand, (dayDemand) => dayDemand.demands) @AutoMap(() => DayDemand) dayDemand?: DayDemand

@ManyToOne(() => MonthPurchase, (monthPurchase) => monthPurchase.forecasts) @AutoMap(() => MonthPurchase) monthPurchase?: MonthPurchase } For this entity I generate and set the uuid by mysefl for some reasons. For nearly all other entities I use auto generated columns (expect DayDemand also gts self generated uuids). I send the following body with postman: { "array": [ { "id": "B1259BFF-7F63-EE11-9937-6045BD9187CD", "lastUpdate": "2023-12-15", "factoryId": "24037948-276C-EE11-9937-6045BD9187CD", "date": "2023-01-03", "demands": [ { "id": "58B1B016-54ED-477D-95E0-61A0F3FB3A61", "transportUnitTypeId": "D3237517-276C-EE11-9937-6045BD9187CD", "materialId": "5A037948-276C-EE11-9937-6045BD9187CD", "amount": 7 }, { "id": "B85CDA2E-D169-EE11-9937-6045BD9187CD", "transportUnitTypeId": "D4237517-276C-EE11-9937-6045BD9187CD", "materialId": "59037948-276C-EE11-9937-6045BD9187CD", "amount": 4 } ] }, { "id": "A1259BFF-7F63-EE11-9937-6045BD9187CD", "lastUpdate": "2023-12-15", "factoryId": "24037948-276C-EE11-9937-6045BD9187CD", "date": "2023-01-04", "demands": [ { "id": "A75CDA2E-D169-EE11-9937-6045BD9187CD", "transportUnitTypeId": "D3237517-276C-EE11-9937-6045BD9187CD", "materialId": "5A037948-276C-EE11-9937-6045BD9187CD", "amount": 7 }, { "id": "A85CDA2E-D169-EE11-9937-6045BD9187CD", "transportUnitTypeId": "D4237517-276C-EE11-9937-6045BD9187CD", "materialId": "59037948-276C-EE11-9937-6045BD9187CD", "amount": 4 } ] } ] } When I change "id": "58B1B016-54ED-477D-95E0-61A0F3FB3A61", which is the first demand in the above example to "id": "58B1B016-54ED-477D-95E0-61A0F3FB3A61",` or when I covert more characters to lower case, then I get the error. If everything is in uppercase then everything works out without any problems.

My Environment

Dependency	Version
Operating System	Windows 10 Pro
Node.js version	18.18.000
Typescript version	5.0.4
TypeORM version	0.3.17
"typeorm"	"^0.3.17",
"@nestjs/typeorm"	"^9.0.1",

Additional Context

I use nestjs and run the app locally on my computer. I send requests with postman. This issue is related to typeorm#4122

Relevant Database Driver(s)

[ ] aurora-mysql
[ ] aurora-postgres
[ ] better-sqlite3
[ ] cockroachdb
[ ] cordova
[ ] expo
[ ] mongodb
[ ] mysql
[ ] nativescript
[ ] oracle
[ ] postgres
[ ] react-native
[ ] sap
[ ] spanner
[ ] sqlite
[ ] sqlite-abstract
[ ] sqljs
[X] sqlserver

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

Yes, I have the time, but I don't know how to start. I would need guidance.

sbogeFS commented 1 year ago

It seems to me that typorm stores all uuids as Uppercase in the db and fetching (like its done with save method) get all uuids in Uppercase. By comparing them with a fully or partly lowrecase uuid leads to an conflict because typorm thinks the uuids are different the db driver reveals that the are not. I'm not sure where this might be fixed if this is really the problem.

sushilm2011 commented 1 year ago

This is not a problem but conceptually it is correct.

Explaination:

The UUID format, though often presented as a string, is fundamentally a 128-bit number. This means that it can be represented as 16 bytes of data. When we see UUIDs as strings, such as "550e8400-e29b-41d4-a716-446655440000", this is a hexadecimal representation of those bytes.

SQL Server's uniqueidentifier type uses the binary (16-byte) representation of the UUID for storage, rather than the string form. When you input a UUID as a string (either uppercase or lowercase), SQL Server will convert it to its binary form. During this conversion, case does not matter because the hexadecimal values represent the same binary values regardless of case.

To give a simple example, consider the hexadecimal numbers 'A' and 'a'. Both represent the decimal number 10. When converted to binary, both 'A' and 'a' become 1010. So, even though their ASCII values differ, their numeric (and thus, binary) representations are the same.

The same principle applies to UUIDs. Whether you input them in uppercase or lowercase form, their binary representation remains consistent. This is why SQL Server considers them identical when stored as uniqueidentifier data type, and it's why the case doesn't matter for uniqueness checks.

sbogeFS commented 1 year ago

Hi @sushilm2011, thank you for your reply. I understand the mechanism behind. But I thought uuids are treated that way in every case such that tyeporm could handle it the same and does not have to differentiate between upper and lower case? Best regards, Sven

ramirezsandin commented 6 months ago

This is clearly an issue with TypeORM, one more to be added to the infinite list of undesired behaviours.

If you try to use update it will succeed because the evaluation of equality is done at database level.

Whereas if you do save it fails because the evaluation is done at framework level (javascript), comparing 2 strings one uppercase and another one lowercase.

To overcome this issue, the transformer property can be used in the column definition, so everytime it is accessed it is converted to lowecase. Obviously the input must be lowercase as well.

  @PrimaryColumn({
    name: 'ID',
    type: 'uniqueidentifier',
    transformer: {
      to: (value) => value?.toLowerCase(),
      from: (value) => value?.toLowerCase() ?? null,
    },
  })
  id: string;

Best regards, Jorge

typeorm / typeorm