views:

342

answers:

3

I have the following function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO    
ALTER FUNCTION [dbo].[IP4toBIGINT](
    @ip4 varchar(15)
) 
RETURNS bigint
WITH SCHEMABINDING
AS
BEGIN
    -- oc3 oc2 oc1 oc0
    -- 255.255.255.255
    -- Declared as BIGINTs to avoid overflows when multiplying later on     DECLARE @oct0 bigint, @oct1 bigint, @oct2 bigint, @oct3 bigint;
    DECLARE @Result bigint;

    SET @oct3 = CAST(PARSENAME(@ip4, 4) as tinyint);
    SET @oct2 = CAST(PARSENAME(@ip4, 3) as tinyint);
    SET @oct1 = CAST(PARSENAME(@ip4, 2) as tinyint);
    SET @oct0 = CAST(PARSENAME(@ip4, 1) as tinyint);

    -- Combine all values, multiply by 2^8, 2^16, 2^24 to bitshift.
    SET @Result = @oct3 * 16777216 + @oct2 * 65536 + @oct1 * 256 + @oct0;
    RETURN @Result;

END

But...

SELECT 
     OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'IsDeterministic') as IsDeterministic 
    ,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'IsPrecise') as IsPrecise 
    ,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'IsSystemVerified') as IsSystemVerified 
    ,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'SystemDataAccess') as SystemDataAccess 
    ,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'UserDataAccess') as UserDataAccess

Returns (result transposed):

IsDeterministic 0

IsPrecise 1

IsSystemVerified 1

SystemDataAccess 0

UserDataAccess 0

I tried dropping and recreating the function several times to make sure it's not some caching issue. CAST should be deterministic here since I'm using it for strings->integers.

I'm completely stumped, any ideas?

+5  A: 

PARSENAME is nondeterministic, on the whole. Yes, you are using it in a context which is deterministic, but I'm guessing that the server does not know that. Try replacing PARSENAME and see if it changes.

Craig Stuntz
MSDN states that PARSENAME is always deterministic
MicSim
MicSim, I guess you haven't tried it before down-voting?
Craig Stuntz
MicSim... the docs are clearly incorrect. Shocking!
Didn't try it out, but believed MSDN. Downvote gone and +1. :-)
MicSim
Ya you're right it's PARSENAME, I was thrown off by MSDN which explicitly says that it is:http://msdn.microsoft.com/en-us/library/ms178091(SQL.90).aspx
Serguei
+3  A: 

It's the PARSENAME causing problems. Replacing it with a hardcoded string results in determinism. No idea why... parse name is supposedly just a fancy split function.

Check this out:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO    
ALTER FUNCTION [dbo].[IP4toBIGINT](
    @ip4 varchar(15)
) 
RETURNS bigint
WITH SCHEMABINDING
AS
BEGIN
    -- oc3 oc2 oc1 oc0
    -- 255.255.255.255
    -- Declared as BIGINTs to avoid overflows when multiplying later on         
    DECLARE @oct0 bigint, @oct1 bigint, @oct2 bigint, @oct3 bigint;
    DECLARE @Result bigint;

    SET @oct3 = CAST('1' as tinyint);
    SET @oct2 = CAST('2' as tinyint);
    SET @oct1 = CAST('3' as tinyint);
    SET @oct0 = CAST('4' as tinyint);

    -- Combine all values, multiply by 2^8, 2^16, 2^24 to bitshift.
    SET @Result = @oct3 * 16777216 + @oct2 * 65536 + @oct1 * 256 + @oct0

    RETURN @Result
END
GO

SELECT 
     OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'IsDeterministic') as IsDeterministic 
    ,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'IsPrecise') as IsPrecise 
    ,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'IsSystemVerified') as IsSystemVerified 
    ,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'SystemDataAccess') as SystemDataAccess 
    ,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'UserDataAccess') as UserDataAccess

Results:

IsDeterministic IsPrecise IsSystemVerified  SystemDataAccess UserDataAccess
1               1         1                 0                0
A: 

Hmm yes so the problem is the use of PARSENAME indeed. MSDN explicitly says it is deterministic. Perhaps this is because SQL assumes you will be reading the DB schema? Which would suggest non-determinism, but I'm just speculating.

Serguei