sql-server - MSSQL 聚合忽略 where 子句

Question

我有一个奇怪的问题，当对类型转换 varchar 列执行聚合函数时，我收到“消息 8114，级别 16，状态 5，第 1 行。将数据类型 nvarchar 转换为 bigint 时出错”。查询 where 子句应过滤掉非数字值。

表结构类似于:

IF EXISTS (SELECT * FROM sys.all_objects ao WHERE ao.name = 'Identifier' AND ao.type = 'U') BEGIN DROP TABLE Identifier END
IF EXISTS (SELECT * FROM sys.all_objects ao WHERE ao.name = 'IdentifierType' AND ao.type = 'U') BEGIN DROP TABLE IdentifierType END

CREATE TABLE IdentifierType
(
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Style] [int] NULL,
  CONSTRAINT [PK_IdentifierType_ID] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

CREATE TABLE Identifier
(
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [IdentifierTypeID] [int] NOT NULL,
  [Value] [nvarchar](4000) NOT NULL,
  CONSTRAINT [PK_Identifier_ID] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

ALTER TABLE Identifier WITH CHECK ADD  CONSTRAINT [FK_Identifier_IdentifierTypeID] FOREIGN KEY([IdentifierTypeID]) REFERENCES IdentifierType ([ID])
GO

Identifier.Value 是一个 VARCHAR 列，它可以并且确实包含非数字数据。将查询过滤为 IdentifierType.Style = 0 应该意味着 'Value' 只返回整数的字符串表示形式。下面的查询失败并显示“消息 8114，级别 16，状态 5，第 1 行。将数据类型 nvarchar 转换为 bigint 时出错”。

SELECT
  MAX(CAST(Value AS BIGINT))
FROM 
  Identifier i,
  IdentifierType it
WHERE
  i.IdentifierTypeID = it.ID AND
  it.Style = 0

如果我扩展 WHERE 子句以包含“AND ISNUMERIC(i.Value) = 1”，它将返回最大整数值。这对我来说意味着我的结果集中有一个非数字字符串。然而我没有得到任何返回的行:

SELECT
  *
FROM 
  Identifier i,
  IdentifierType it
WHERE
  i.IdentifierTypeID = it.ID AND
  it.Style = 0 AND 
  ISNUMERIC(i.Value) <> 1

我一直无法识别导致类型转换失败的行。上面的查询应该已经暴露了异常行。此外，也没有空字符串或超长字符串(最大字符串为 6 个字符长)

MSSQL 是否有可能尝试对所有行执行 CAST 而不是首先通过 WHERE 子句进行过滤？

或者有没有其他人见过类似的东西？

还有一个解决方法是将查询的组件实例化到临时表中，然后从中选择 MAX 值。

SELECT
  Value
INTO
  IdentifierClone
FROM 
  Identifier i,
  IdentifierType it
WHERE
  i.IdentifierTypeID = it.ID AND
  it.Style = 0

SELECT MAX(CAST(Value as BIGINT)) FROM IdentifierClone

但是子查询不起作用。

任何帮助或想法将不胜感激。

Answer 1

尝试使用 REGEX 表达式查找问题记录。这是一个示例，其中 ISNUMERIC 未检测到问题，但正则表达式可以

CREATE TABLE tst (value nvarchar(4000))
INSERT INTO tst select '£'
-- Record found ...
SELECT * FROM tst WHERE value NOT LIKE '%[0-9]%'
-- No record found ...
SELECT * from tst where isnumeric(value) <> 1