在 SQL Server 中从字符串转换为 uniqueidentifier 错误时转换失败

Conversion failed when converting from a character string to uniqueidentifier error in SQL Server

提问by CreativeJourney

I've been getting the error "Conversion failed when converting from a character string to uniqueidentifier" and am finally at the end of my rope. I've narrowed down my problem to as small as possible while keeping the error in tact. Install the CSV splitter from here first if you want to reproduce:

我一直收到错误“从字符串转换为 uniqueidentifier 时转换失败”，我终于走到了尽头。我已经将我的问题缩小到尽可能小，同时保持错误的机智。如果要重现，请先从此处安装 CSV 拆分器：

http://www.sqlservercentral.com/articles/Tally+Table/72993/

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Here's the test code. I'm on SQL 2008R2 but in a database that is SQL 2005 compatible:

这是测试代码。我在 SQL 2008R2 上，但在与 SQL 2005 兼容的数据库中：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZZZTESTTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[ZZZTESTTABLE]
GO

CREATE TABLE [dbo].[ZZZTESTTABLE](
    [Col1] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_ZZZTESTTABLE] PRIMARY KEY CLUSTERED 
(
    [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- Test table that I would like to check my values against
insert dbo.ZZZTESTTABLE(Col1) values('85B049B7-CDD0-4995-B582-5A74523039C0')

-- Test string that will be split into table in the DelimitedSplit8k function
declare @temp varchar(max) = '918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5'

-- I'm trying to delete all data in the ZZZTESTTABLE that is not in my string but I get the error 
delete dbo.ZZZTESTTABLE
where Col1 not in 
(
-- ERROR OCCURS HERE
    select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
)

HERE's the source for the DelimitedSplit8K function so you don't have to go and find it:

这里是 DelimitedSplit8K 函数的源代码，因此您不必去寻找它：

CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT 0 UNION ALL
                 SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item       = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
   FROM cteStart s
;

采纳答案by Remus Rusanu

The use of this UDF is indeed making procedural assumptions about order of execution. It assumes that the WHEREclause inside the UDF will be evaluated beforethe cast(item as uniqueidentifier). This assumption is erroneous as the optimizer is free to change the plan to move the WHERE clause above the cast and the net effect is that the cast is asked to converts a partial token to a guid (ie. a string like 18E809E-EA7A-44B5-B230-776C42594D91).

这个 UDF 的使用确实是对执行顺序做出程序假设。它假定WHERE的UDF内条款将被评估之前的cast(item as uniqueidentifier)。这种假设是错误的，因为优化器可以自由更改计划以将 WHERE 子句移到强制转换之上，并且最终效果是要求强制转换将部分标记转换为 guid（即像 的字符串18E809E-EA7A-44B5-B230-776C42594D91）。

For a more detailed answer read T-SQL functions do no imply a certain order of execution.

要获得更详细的答案，请阅读T-SQL 函数并不意味着一定的执行顺序。

As a workaround you can force NULL into the projected values of the UDF for the rows that don't meet the WHERE clause:

作为一种解决方法，您可以将 NULL 强制为不符合 WHERE 子句的行的 UDF 的投影值：

CREATE FUNCTION dbo.DelimitedSplit8K
...
cteStart(N1, nullify) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1, 
                    case when (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) then 1 else 0 end
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item       = case s.nullify
            when 1 then SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
            else null
            end
   FROM cteStart s;
go

Because the CASE expression is guaranteed to be evaluated before the CAST (since the input of the CAST is the output of the CASE) the reordering of the WHERE clause is safe.

因为 CASE 表达式保证在 CAST 之前计算（因为 CAST 的输入是 CASE 的输出），所以重新排序 WHERE 子句是安全的。

回答by Jeff Ogata

Not sure what is happening here, but the problem does not appear to be the format of the guids or the output of the function. Executing this works:

不确定这里发生了什么，但问题似乎不是 guid 的格式或函数的输出。执行这个工作：

declare @temp varchar(max) = '918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5'    
select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')

Maybe the query processor is looking at the return schema of the function and saying that it can't be cast to uniqueidentifier? Hopefully someone else can provide a specific answer to that.

也许查询处理器正在查看函数的返回模式并说它不能转换为uniqueidentifier？希望其他人可以为此提供具体的答案。

Selecting the output of the split function into a temp table will work:

将 split 函数的输出选择到临时表中将起作用：

select cast(Item as uniqueidentifier) as Item into #temp from dbo.DelimitedSplit8K(@temp, ',')

-- I'm trying to delete all data in the ZZZTESTTABLE that is not in my string but I get the error 
delete dbo.ZZZTESTTABLE
where Col1 not in 
(
-- ERROR OCCURS HERE
    --select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
    select Item from #temp
)

回答by Skorpioh

Why cast Item to uniqueidentifier when you can do it the other way around.

当您可以反过来做时，为什么要将 Item 转换为 uniqueidentifier 。

Instead of

代替

where Col1 not in 
(
-- ERROR OCCURS HERE
    select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
)

you may try this:

你可以试试这个：

where cast(Col1 as varchar(64)) not in 
(
    select Item 
    from dbo.DelimitedSplit8K(@temp, ',')
)

回答by RichardTheKiwi

Looks like I misread the question the first time. Good job producing a test script that reproduces the error. The following works for me:

看起来我第一次误读了这个问题。很好地生成了一个重现错误的测试脚本。以下对我有用：

delete dbo.ZZZTESTTABLE
WHERE Col1 in
(
    select Z.Col1
    from dbo.ZZZTESTTABLE Z
    LEFT JOIN dbo.DelimitedSplit8K(@temp, ',') S on S.Item = Z.Col1
    where S.Item is null
)
OPTION (force order)