SQL中VARCHAR类型小数与-1相乘的异常行为及原因咨询

问题现象 #

• 正确转换：执行SELECT CONVERT(decimal(3,2),'0.01')*-1 as Line1，返回预期结果 -0.01。
• 异常表现：
  • 执行SELECT '0.01'*-1.0 as Line2，返回0.00而非预期的-0.01；
  • 执行SELECT '0.1'*-1.0 as Line3，返回预期的-0.10。
• 阈值测试：
  • 当VARCHAR值为'0.0499999999999999999'时，与-1.0相乘返回0.00；
  • 当值为'0.05'时，返回-0.10，通过SQL_VARIANT_PROPERTY检测结果类型为numeric(5,2)。
• 扩展测试：
  • 执行SELECT '0.09'*-1.0 as Line7返回-0.10；
  • 执行SELECT '0.01'*-1.00 as Line8返回-0.0100，结果类型为numeric(7,4)，且每多一个末尾0，精度和刻度各增加2。

底层原因 #

这个问题的核心是SQL Server隐式转换时的刻度匹配与舍入规则：

1. 隐式转换的刻度对齐逻辑
   当执行字符串与数值的乘法运算时，SQL Server会优先将字符串隐式转换为与右侧数值同刻度的numeric类型，再执行运算。

• 对于'0.01'*-1.0：-1.0是numeric(2,1)（精度2，刻度1，即保留1位小数）。'0.01'转换为刻度1的数值时，会被四舍五入为0.0，再与-1.0相乘，最终得到0.00。
• 对于'0.1'*-1.0：'0.1'转换为刻度1的数值就是0.1，相乘后得到-0.10，符合预期。

2. 高刻度数值的行为
   当右侧是-1.00时，它属于numeric(3,2)（刻度2，保留2位小数）。此时'0.01'转换为刻度2的数值无需舍入，直接保留0.01，相乘后得到-0.0100，结果正确。
   每多一个末尾0，-1.000这类数值的精度和刻度各增加1，字符串转换时也会匹配更高的刻度，运算结果的精度和刻度则按精度=精度1+精度2、刻度=刻度1+刻度2的规则推导，因此出现“每多一个末尾0，精度和刻度各增加2”的现象。

3. 阈值测试的本质
   '0.0499999999999999999'转换为刻度1的数值时，四舍五入后是0.0，相乘得0.00；'0.05'转换为刻度1的数值时，四舍五入后是0.1，相乘得-0.10，完全符合刻度对齐后的舍入规则。

4. 显式转换的正确性
   CONVERT(decimal(3,2),'0.01')强制将字符串转换为刻度2的decimal类型，后续与-1（自动提升为decimal(1,0)）相乘时，结果刻度为2+0=2，无需舍入，因此得到正确的-0.01。

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