递归查询'manager'中'manager_name'列锚点与递归部分类型不匹配排查

问题现象 #

执行递归查询时反复出现以下错误：

Types don't match between the anchor and the recursive part in column "manager_name" of recursive query "manager".

即使对所有列执行了CAST转换，错误仍未消除。

原查询语句 #

WITH manager (
    full_name,
    first_name,
    email,
    crm_user_id,
    "role",
    parent_role,
    manager_name,
    manager_email,
    crm_manager_id,
    role_path,
    manager_path
)
AS (
    SELECT
        CAST(full_name as VARCHAR(512)),
        CAST(first_name as VARCHAR(512)),
        CAST(email as VARCHAR(512)),
        CAST(crm_user_id as VARCHAR(18)),
        CAST([role] as VARCHAR(128)),
        CAST(parent_role as VARCHAR(128)),
        CAST(NULL as VARCHAR(512)),
        CAST(NULL as VARCHAR(512)),
        CAST(NULL as VARCHAR(18)),
        CAST([role] as VARCHAR(max)),
        CAST(full_name as VARCHAR(max))
    FROM dbo.Forecast_owners
    WHERE parent_role IS NULL
UNION ALL
    SELECT
        CAST(employee.full_name as VARCHAR(512)),
        CAST(employee.first_name as VARCHAR(512)),
        CAST(employee.email as VARCHAR(512)),
        CAST(employee.crm_user_id as VARCHAR(18)),
        CAST(employee.[role] as VARCHAR(128)),
        CAST(employee.parent_role as VARCHAR(128)),
        CAST(manager.full_name as VARCHAR(512)),
        CAST(manager.email as VARCHAR(512)),
        CAST(manager.crm_user_id as VARCHAR(18)),
        CAST((manager.role_path + '/' + employee.[role]) as VARCHAR(max)),
        CAST((manager.manager_path + '/' + employee.full_name) as VARCHAR(max))
    FROM
        dbo.Forecast_owners employee
    JOIN manager ON employee.parent_role = manager.[role]
)
SELECT 
    *
FROM
    manager

表结构DDL #

CREATE TABLE Forecast_owners (
    full_name varchar(512) COLLATE Latin1_General_CI_AS NULL,
    first_name varchar(512) COLLATE Latin1_General_CI_AS NULL,
    email varchar(512) COLLATE Latin1_General_CI_AS NULL,
    crm_user_id varchar(18) COLLATE Latin1_General_CI_AS NULL,
    [role] varchar(128) COLLATE Latin1_General_CI_AS NULL,
    parent_role varchar(128) COLLATE Latin1_General_CI_AS NULL
);

服务器排序规则 #

执行SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));得到结果：SQL_Latin1_General_CP1_CI_AS

问题原因 #

错误根源是排序规则不匹配：

• 锚点查询中，CAST(NULL as VARCHAR(512))这类语句会默认使用服务器排序规则SQL_Latin1_General_CP1_CI_AS
• 递归查询中，manager.full_name等列来自Forecast_owners表，该表列的排序规则是Latin1_General_CI_AS
• SQL Server会将排序规则视为类型的一部分，两者不一致时，就会判定为“类型不匹配”

解决方法 #

方法一：锚点部分指定NULL的排序规则，与表列保持一致 #

修改锚点查询中三个NULL列的CAST语句，添加排序规则指定：

CAST(NULL as VARCHAR(512)) COLLATE Latin1_General_CI_AS,
CAST(NULL as VARCHAR(512)) COLLATE Latin1_General_CI_AS,
CAST(NULL as VARCHAR(18)) COLLATE Latin1_General_CI_AS,

方法二：递归部分指定列的排序规则，与锚点保持一致 #

修改递归查询中对应列的CAST语句，添加服务器默认排序规则：

CAST(manager.full_name as VARCHAR(512)) COLLATE SQL_Latin1_General_CP1_CI_AS,
CAST(manager.email as VARCHAR(512)) COLLATE SQL_Latin1_General_CP1_CI_AS,
CAST(manager.crm_user_id as VARCHAR(18)) COLLATE SQL_Latin1_General_CP1_CI_AS,

修改后的完整查询（方法一示例） #

WITH manager (
    full_name,
    first_name,
    email,
    crm_user_id,
    "role",
    parent_role,
    manager_name,
    manager_email,
    crm_manager_id,
    role_path,
    manager_path
)
AS (
    SELECT
        CAST(full_name as VARCHAR(512)),
        CAST(first_name as VARCHAR(512)),
        CAST(email as VARCHAR(512)),
        CAST(crm_user_id as VARCHAR(18)),
        CAST([role] as VARCHAR(128)),
        CAST(parent_role as VARCHAR(128)),
        CAST(NULL as VARCHAR(512)) COLLATE Latin1_General_CI_AS,
        CAST(NULL as VARCHAR(512)) COLLATE Latin1_General_CI_AS,
        CAST(NULL as VARCHAR(18)) COLLATE Latin1_General_CI_AS,
        CAST([role] as VARCHAR(max)),
        CAST(full_name as VARCHAR(max))
    FROM dbo.Forecast_owners
    WHERE parent_role IS NULL
UNION ALL
    SELECT
        CAST(employee.full_name as VARCHAR(512)),
        CAST(employee.first_name as VARCHAR(512)),
        CAST(employee.email as VARCHAR(512)),
        CAST(employee.crm_user_id as VARCHAR(18)),
        CAST(employee.[role] as VARCHAR(128)),
        CAST(employee.parent_role as VARCHAR(128)),
        CAST(manager.full_name as VARCHAR(512)),
        CAST(manager.email as VARCHAR(512)),
        CAST(manager.crm_user_id as VARCHAR(18)),
        CAST((manager.role_path + '/' + employee.[role]) as VARCHAR(max)),
        CAST((manager.manager_path + '/' + employee.full_name) as VARCHAR(max))
    FROM
        dbo.Forecast_owners employee
    JOIN manager ON employee.parent_role = manager.[role]
)
SELECT 
    *
FROM
    manager

内容的提问来源于stack exchange，提问作者Pedro Paixao