none
特定方式向表中插入同样的数据,会使得计算列异常。 RRS feed

  • 问题

  • SQL Server版本:Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

    首先有个表

    CREATE TABLE [dbo].[JsonTest] (
        [Id]     INT            IDENTITY (1, 1) NOT NULL,
        [data]   NVARCHAR (MAX) NOT NULL,
        [userid] AS             (case when isjson([data])>(0) then TRY_CAST(json_value([data],'lax $.UserId') AS [int])  end) PERSISTED,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    就是有个计算列userid,内容为按照json格式解析data中的数据。做了各种处理,应该任何情况下都是不会异常的。

    但是下面的插入数据就异常了:(JSON 文本格式不正确。位置 0 中存在非预期的字符 "a"。)

    exec sp_executesql N'insert JsonTest(data) values(@data);',N'@data nvarchar(max)',@data=N'a'

    但是略微改动一下,下面几种都不会异常

    exec sp_executesql N'insert JsonTest(data) values(@data);',N'@data nvarchar(1000)',@data=N'a';
    exec sp_executesql N'insert JsonTest(data) values(@data);',N'@data varchar(max)',@data=N'a';
    exec sp_executesql N'insert JsonTest(data) values(''a'');',N'';

    正常插入方式,也不会异常

    insert JsonTest(data) values('a');
    insert JsonTest(data) values('{"UserId":1}');
    
    declare @data nvarchar(max) = N'a';
    insert JsonTest(data) values(@data);

    下面是个简单的case , json相关的测试,可以看一下. 感觉在特定情况下json_value提前执行了。

    --会异常
    select case when isjson('a')>0 then json_value('a','lax $.userid') end
    --不会异常
    declare @data nvarchar(max) = N'a'
    select case when isjson(@data)>0 then json_value(@data,'lax $.userid') end

    --不会异常
    declare @data nvarchar(max) = N'a'
    select case when isjson('a')>0 then json_value(@data,'lax $.userid') end

    --会异常
    declare @data nvarchar(max) = N'a'
    select case when isjson(@data)>0 then json_value('a','lax $.userid') end
    --不会异常
    select case when isjson('a')>0 then 1/0 end
    (实际使用中我是用.net的SqlCommand来插入数据的。所以我用sp_executesql来模拟一下,因为我估计SqlCommand也是这样来调用sql语句的。)



    2021年3月19日 5:04

答案

  • 怀疑是BUG,这种在生成执行计划的时候就出错了,并没有到执行阶段

    declare @data nvarchar(max) = N'a'

    select case when isjson(@data)>0 then json_value('a','lax $.userid') end

    ---- 然后把 json_value 里面的 'a' 用变量,则怎么都不会出错

    怀疑是执行计划在处理常量的时候,会直接做什么处理导致

    要暂时解决的话,可以把字段定义改成如下(强制加个转换)
    [userid] AS             (case when isjson([data])>(0) then TRY_CAST(json_value(convert(nvarchar(max),[data]),'lax $.UserId') AS [int])  end) PERSISTED,

    2021年3月22日 5:39

全部回复

  • 您好,

    我使用SQL Server 2019(RTM-CU9)和SQL Server 2017(RTM-CU22)进行了以上测试,和您展示的情况是一样。

    另外如果创建一个执行插入语句的存储过程并执行也会是相同的情况。

    CREATE PROCEDURE dbo.InsertJsonTest
    (
    	@data NVARCHAR(max) = null
    )
    AS
    BEGIN
    	insert dbo.JsonTest(data) values(@data)
    END
    GO

    但是只要不声明变量为nvarchar(max),那么执行sp_executesql和存储过程都不会返回错误。


    似乎官方文档里JSON_VALUE(Transact-SQL)提到过如果必须返回大于4000个字符的标量值,请使用OPENJSON而不是JSON_VALUE。


    2021年3月19日 9:10

  • 下面是个简单的case , json相关的测试,可以看一下. 感觉在特定情况下json_value提前执行了。

    --会异常
    select case when isjson('a')>0 then json_value('a','lax $.userid') end
    --不会异常
    declare @data nvarchar(max) = N'a'
    select case when isjson(@data)>0 then json_value(@data,'lax $.userid') end

    --不会异常
    declare @data nvarchar(max) = N'a'
    select case when isjson('a')>0 then json_value(@data,'lax $.userid') end

    --会异常
    declare @data nvarchar(max) = N'a'
    select case when isjson(@data)>0 then json_value('a','lax $.userid') end

    (实际使用中我是用.net的SqlCommand来插入数据的。所以我用sp_executesql来模拟一下,因为我估计SqlCommand也是这样来调用sql语句的。)



    从以上测试看,当json_value里的参数直接为'a'时,似乎没有按照case then 的规范,即使isjson('a')>0不成立,json_value也进行了判断。

    但是如果json_value里的参数为@data时,则没有异常。

    2021年3月19日 9:17
  • 你好感谢回复。但是文档上说的是返回值,返回大于4000字符才需要用OPENJSON,而不是输入参数本身大于4000字符。我只是需要提取一个简单的int,应该是轻量级的JSON_VALUE更适合。

    另外,是不是可以更改计算列的方式来解决这个问题呢。比如写个函数,加入try catch。我试过了,但是没有成功,函数是不确定的,不能用来计算列。文档上说CAST函数是不确定的。但是我不知道TRY_CAST或TRY_CONVERT以及JSON函数是不是确定的,也不知道是不是try catch语句造成了函数不确定。

    2021年3月19日 10:01
  • 怀疑是BUG,这种在生成执行计划的时候就出错了,并没有到执行阶段

    declare @data nvarchar(max) = N'a'

    select case when isjson(@data)>0 then json_value('a','lax $.userid') end

    ---- 然后把 json_value 里面的 'a' 用变量,则怎么都不会出错

    怀疑是执行计划在处理常量的时候,会直接做什么处理导致

    要暂时解决的话,可以把字段定义改成如下(强制加个转换)
    [userid] AS             (case when isjson([data])>(0) then TRY_CAST(json_value(convert(nvarchar(max),[data]),'lax $.UserId') AS [int])  end) PERSISTED,

    2021年3月22日 5:39
  • 有人已经提过bug了

    https://feedback.azure.com/forums/908035-sql-server/suggestions/39297589-computed-column-with-json-value-inside-case-statem

    2021年3月22日 9:21