MSSQL: Uppercase reference to "SYS"."COLUMNS" during entity initialization s/b lowercase.

[krystalmonolith]

Issue Description

• The MSSQL code refers to the "sys"."columns" table as "SYS"."COLUMNS" at src/driver/sqlserver/SqlServerQueryRunner.ts:1675.
• MSSQL database servers default to a case insensitive collation.
• If the entire database collation is ALTER'ed to a case sensitive collation then uppercase "SYS"."COLUMNS" does not match the actual table name "sys"."columns" resulting in a "query failed" during entity initialization.
• My NestJS server treats the "query failed" as a fatal error and never connects to the database.

Expected Behavior

• All references to the MSSQL table "sys"."columns" should be lowercase.
• Three of the four references to "sys"."columns" in src/driver/sqlserver/SqlServerQueryRunner.ts are already lower case: Only line shown below 1675 is incorrect.

             return `SELECT "TABLE_CATALOG", "TABLE_SCHEMA", "COLUMN_NAME", "TABLE_NAME" ` +
                  `FROM "${TABLE_CATALOG}"."INFORMATION_SCHEMA"."COLUMNS" ` +
                  `WHERE ` +
/* This line! */  `EXISTS(SELECT 1 FROM "${TABLE_CATALOG}"."SYS"."COLUMNS" "S" WHERE OBJECT_ID("TABLE_CATALOG" + '.' + "TABLE_SCHEMA" + '.' + "TABLE_NAME") = "S"."OBJECT_ID" AND "COLUMN_NAME" = "S"."NAME" AND "S"."is_identity" = 1) AND ` +
                  `(${conditions})`;

Actual Behavior

Formatted for clarity and naming redacted...

query: SELECT "TABLE_CATALOG", "TABLE_SCHEMA", "COLUMN_NAME", "TABLE_NAME" 
FROM "MyDatabase"."INFORMATION_SCHEMA"."COLUMNS" 
WHERE EXISTS(SELECT 1 FROM "MyDatabase"."SYS"."COLUMNS" "S" 
WHERE OBJECT_ID("TABLE_CATALOG" + '.' + "TABLE_SCHEMA" + '.' + "TABLE_NAME") = "S"."OBJECT_ID" AND "COLUMN_NAME" = "S"."NAME" AND "S"."is_identity" = 1) 
AND (("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Category') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_RMU') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_ProductLine') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Business') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Duration') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_AppSWLicense') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Model') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputColumnFunction') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputFunctionParameter') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputFile') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRowGroup') OR ("TABLE_SCHEMA"= 'dbo' AND "TABLE_NAME" = 'a_OutputRow') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputColumn') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputFile_join_OutputRowGroup') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRowGroup_join_OutputRowGroup') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRowGroup_join_OutputRow') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRow_join_OutputColumn') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputColumn_join_OutputFunctionParameter'))

query failed: SELECT "TABLE_CATALOG", "TABLE_SCHEMA", "COLUMN_NAME", "TABLE_NAME" 
FROM "MyDatabase"."INFORMATION_SCHEMA"."COLUMNS" 
WHERE EXISTS(SELECT 1 FROM "MyDatabase"."SYS"."COLUMNS" "S" 
WHERE OBJECT_ID("TABLE_CATALOG" + '.' + "TABLE_SCHEMA" + '.' + "TABLE_NAME") = "S"."OBJECT_ID" AND "COLUMN_NAME" = "S"."NAME" AND "S"."is_identity" = 1) 
AND (("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Category') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_RMU') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_ProductLine') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Business') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Duration') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_AppSWLicense') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Model') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputColumnFunction') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputFunctionParameter') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputFile') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRowGroup') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRow') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputColumn') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputFile_join_OutputRowGroup') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRowGroup_join_OutputRowGroup') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRowGroup_join_OutputRow') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRow_join_OutputColumn') 
OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputColumn_join_OutputFunctionParameter'))

error: QueryFailedError: Error: Invalid object name 'MyDatabase.SYS.COLUMNS'.
    at QueryFailedError.TypeORMError [as constructor] (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\src\error\TypeORMError.ts:7:9)
    at new QueryFailedError (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\src\error\QueryFailedError.ts:9:9)
    at C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\src\driver\sqlserver\SqlServerQueryRunner.ts:218:30
    at C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\mssql\lib\base\request.js:438:25
    at Request.userCallback (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\mssql\lib\tedious\request.js:493:15)
    at Request.callback (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\request.js:205:14)
    at Parser.onEndOfMessage (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\connection.js:3078:22)
    at Object.onceWrapper (events.js:519:28)
    at Parser.emit (events.js:400:28)
    at Readable.<anonymous> (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\token\token-stream-parser.js:34:12) {
  query: `SELECT "TABLE_CATALOG", "TABLE_SCHEMA", "COLUMN_NAME", "TABLE_NAME" FROM "MyDatabase"."INFORMATION_SCHEMA"."COLUMNS" WHERE EXISTS(SELECT 1 FROM "MyDatabase"."SYS"."COLUMNS" "S" WHERE OBJECT_ID("TABLE_CATALOG" + '.' + "TABLE_SCHEMA" + '.' + "TABLE_NAME") = "S"
."OBJECT_ID" AND "COLUMN_NAME" = "S"."NAME" AND "S"."is_identity" = 1) AND (("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Category') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_RMU') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_ProductLine') OR ("TABLE
_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Business') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Duration') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_AppSWLicense') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_Model') OR ("TABLE_SCHEMA" = 'dbo' AND
 "TABLE_NAME" = 'a_OutputColumnFunction') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputFunctionParameter') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputFile') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRowGroup') OR ("TABLE_SCHEM
A" = 'dbo' AND "TABLE_NAME" = 'a_OutputRow') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputColumn') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputFile_join_OutputRowGroup') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRowGroup_join_O
utputRowGroup') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRowGroup_join_OutputRow') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputRow_join_OutputColumn') OR ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'a_OutputColumn_join_OutputFunctionPara
meter'))`,
  parameters: undefined,
  driverError: RequestError: Invalid object name 'MyDatabase.SYS.COLUMNS'.
      at handleError (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\mssql\lib\tedious\request.js:388:15)
      at Connection.emit (events.js:400:28)
      at Connection.emit (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\connection.js:1071:18)
      at Parser.<anonymous> (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\connection.js:1176:12)
      at Parser.emit (events.js:400:28)
      at Readable.<anonymous> (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\token\token-stream-parser.js:27:14)
      at Readable.emit (events.js:400:28)
      at addChunk (internal/streams/readable.js:290:12)
      at readableAddChunk (internal/streams/readable.js:265:9)
      at Readable.push (internal/streams/readable.js:204:10) {
    code: 'EREQUEST',
    originalError: Error: Invalid object name 'MyDatabase.SYS.COLUMNS'.
        at handleError (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\mssql\lib\tedious\request.js:386:19)
        at Connection.emit (events.js:400:28)
        at Connection.emit (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\connection.js:1071:18)
        at Parser.<anonymous> (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\connection.js:1176:12)
        at Parser.emit (events.js:400:28)
        at Readable.<anonymous> (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\token\token-stream-parser.js:27:14)
        at Readable.emit (events.js:400:28)
        at addChunk (internal/streams/readable.js:290:12)
        at readableAddChunk (internal/streams/readable.js:265:9)
        at Readable.push (internal/streams/readable.js:204:10) {
      info: [ErrorMessageToken]
    },
    number: 208,
    lineNumber: 1,
    state: 1,
    class: 16,
    serverName: 'DBSERVER',
    procName: '',
    precedingErrors: []
  },
  code: 'EREQUEST',
  originalError: Error: Invalid object name 'MyDatabase.SYS.COLUMNS'.
      at handleError (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\mssql\lib\tedious\request.js:386:19)
      at Connection.emit (events.js:400:28)
      at Connection.emit (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\connection.js:1071:18)
      at Parser.<anonymous> (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\connection.js:1176:12)
      at Parser.emit (events.js:400:28)
      at Readable.<anonymous> (C:\Users\user\dev\myDatabase\MyDatabaseEditor\backend\node_modules\tedious\lib\token\token-stream-parser.js:27:14)
      at Readable.emit (events.js:400:28)
      at addChunk (internal/streams/readable.js:290:12)
      at readableAddChunk (internal/streams/readable.js:265:9)
      at Readable.push (internal/streams/readable.js:204:10) {
    info: ErrorMessageToken {
      name: 'ERROR',
      event: 'errorMessage',
      number: 208,
      state: 1,
      class: 16,
      message: "Invalid object name 'MyDatabase.SYS.COLUMNS'.",
      serverName: 'DBSERVER',
      procName: '',
      lineNumber: 1
    }
  },
  number: 208,
  lineNumber: 1,
  state: 1,
  class: 16,
  serverName: 'DBSERVER',
  procName: '',
  precedingErrors: []
}

Steps to Reproduce

1. Create a new MSSQL database (ex: MyDatabase) with the default case-insensitive collation. (The default database collation can be viewed in SQL Studio by right clicking the database name and selecting 'Properties'.)

2. Change the entire database's collation to a case-sensitive collation:

USE master;
GO
ALTER DATABASE MyDatabase
  COLLATE Latin1_General_CS_AI_KS;
GO  

FYI: The "_CS" in Latin1_General_CS_AI_KS means case-sensitive.

3.) Create any @Entity().

4.) Start TypeORM the first time and it will create the entity without any errors.

5.) TypeORM will generate a "query failed" when started the second time when it tries to get the id columns of the existent entity.

My Environment

Dependency	Version
Operating System	Windows 10 Enterprise 20H2 19042.1348
Node.js version	v14.17.6
Typescript version	4.3.5
TypeORM version	0.2.39

Additional Context

  "dependencies": {
    "@nestjs/common": "^8.0.0",
    "@nestjs/config": "^1.0.2",
    "@nestjs/core": "^8.0.0",
    "@nestjs/mapped-types": "^1.0.0",
    "@nestjs/platform-express": "^8.0.0",
    "@nestjs/serve-static": "^2.2.2",
    "@nestjs/typeorm": "^8.0.2",
    "class-transformer": "^0.4.0",
    "class-validator": "^0.13.1",
    "mssql": "^7.2.1",
    "typeorm": "^0.2.39",
  },
  "devDependencies": {
   ...
   "typescript": "^4.3.5"
}

Relevant Database Driver(s)

DB Type	Reproducible
aurora-data-api	n/a
aurora-data-api-pg	n/a
better-sqlite3	n/a
cockroachdb	n/a
cordova	n/a
expo	n/a
mongodb	n/a
mysql	n/a
nativescript	n/a
oracle	n/a
postgres	n/a
react-native	n/a
sap	n/a
sqlite	n/a
sqlite-abstract	n/a
sqljs	n/a
sqlserver	yes

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, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[krystalmonolith]

Pull Request is #8401

[AlexMesser]

fixed in #8401