Invalid object name 'SYS.INDEXES' on SQL Server case sensitive collate #1414

New Issue

2019-09-05T03:50:34Z

shaohua-deng commented

2019-09-05 03:50:34 +00:00

(Migrated from github.com)

xorm: v0.7.6
SQL Server: 2017 docker on Ubuntu 18.04
The database is created with case sensitive collate like this:
CREATE DATABASE testdb COLLATE Chinese_PRC_CS_AS_WS;

The line below returns an error "Invalid object name 'SYS.INDEXES'"
ce79b1cf28/dialect_mssql.go (L450)

While it doesn't fail on another database created with a default collate SQL_Latin1_General_CP1_CI_AS
CREATE DATABASE testdb2

Then I copied the SQL statement to a database terminal and modified all the system table names to lower case, it succeed.

SELECT
    IXS.NAME                    AS  [INDEX_NAME],
    C.NAME                      AS  [COLUMN_NAME],
    IXS.is_unique AS [IS_UNIQUE]
FROM sys.indexes IXS
INNER JOIN sys.index_columns  IXCS
    ON IXS.OBJECT_ID=IXCS.OBJECT_ID  AND IXS.INDEX_ID = IXCS.INDEX_ID
INNER   JOIN  sys.columns C  
    ON IXS.OBJECT_ID=C.OBJECT_ID AND IXCS.COLUMN_ID=C.COLUMN_ID

As I see from Microsoft web site, all SQL server system objects are named in lower case
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql?view=sql-server-2017

xorm: v0.7.6 SQL Server: 2017 docker on Ubuntu 18.04 The database is created with case sensitive collate like this: `CREATE DATABASE testdb COLLATE Chinese_PRC_CS_AS_WS;` The line below returns an error "Invalid object name 'SYS.INDEXES'" https://github.com/go-xorm/xorm/blob/ce79b1cf28a4a457a730212fc1bec616a8db4db9/dialect_mssql.go#L450 While it doesn't fail on another database created with a default collate SQL_Latin1_General_CP1_CI_AS `CREATE DATABASE testdb2` Then I copied the SQL statement to a database terminal and modified all the system table names to lower case, it succeed. ``` SELECT IXS.NAME AS [INDEX_NAME], C.NAME AS [COLUMN_NAME], IXS.is_unique AS [IS_UNIQUE] FROM sys.indexes IXS INNER JOIN sys.index_columns IXCS ON IXS.OBJECT_ID=IXCS.OBJECT_ID AND IXS.INDEX_ID = IXCS.INDEX_ID INNER JOIN sys.columns C ON IXS.OBJECT_ID=C.OBJECT_ID AND IXCS.COLUMN_ID=C.COLUMN_ID ``` As I see from Microsoft web site, all SQL server system objects are named in lower case https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql?view=sql-server-2017

shaohua-deng commented

2019-09-05 04:18:59 +00:00

(Migrated from github.com)

Here is my proposal of code change
d726b609a4

I only run the SQL statement directly, didn't have any go test.

If it's OK, I can submit a pull request.

Here is my proposal of code change https://github.com/shaohua-deng/xorm/commit/d726b609a4c1c33aa41843b134edd8f875577163 I only run the SQL statement directly, didn't have any go test. If it's OK, I can submit a pull request.

Sign in to join this conversation.

No Milestone

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#1414