Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Fix: Mssql introspection duplicate columns #847

Closed
tmrclark opened this issue Jan 17, 2024 · 0 comments · Fixed by #848
Closed

Fix: Mssql introspection duplicate columns #847

tmrclark opened this issue Jan 17, 2024 · 0 comments · Fixed by #848

Comments

@tmrclark
Copy link
Contributor

tmrclark commented Jan 17, 2024

Reproduction Repo

When using the MSSQL dialect, .introspect.getTables() returns duplicate columns in certain circumstances. A simple example is a column with the data type nvarcar. The output looks like

[
  {
    dataType: 'nvarchar',
    dataTypeSchema: 'sys',
    hasDefaultValue: false,
    isAutoIncrementing: false,
    isNullable: true,
    name: 'column_1'
  },
  {
    dataType: 'sysname',
    dataTypeSchema: 'sys',
    hasDefaultValue: false,
    isAutoIncrementing: false,
    isNullable: false,
    name: 'column_1'
  }
]

The Cause

This is a known issue caused by how MSSQL manages types internally. It surfaces in the .getTables method where the query joins sys.types to sys.columns on system_type_id when it should be using user_type_id

-- Returns 2 rows
SELECT c.name, ty.name, ty.system_type_id, ty.user_type_id
FROM sys.tables AS t 
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id
WHERE t.name = 'test_table'

VS

-- Returns 1 row
SELECT c.name, ty.name, ty.system_type_id, ty.user_type_id
FROM sys.tables AS t 
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE t.name = 'test_table'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant