向SQL Server传入None作为参数值时的异常行为排查

一、test3表定义 #

/****** Object:  Table [dbo].[test3]    Script Date: 11/12/2023 9:30:17 AM ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test3]') AND type in (N'U'))
DROP TABLE [dbo].[test3]
GO
/****** Object:  Table [dbo].[test3]    Script Date: 11/12/2023 9:30:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test3](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [column1] [varchar](10) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test3] ON 
GO
INSERT [dbo].[test3] ([id], [column1]) VALUES (1, N'aaa')
GO
INSERT [dbo].[test3] ([id], [column1]) VALUES (2, N'bbb')
GO
SET IDENTITY_INSERT [dbo].[test3] OFF
GO

二、问题现象 #

执行sqlStatement1返回表中全部2行数据，执行sqlStatement2返回0行数据。

测试代码 #

import pyodbc

connectionString = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=7D3QJR3;DATABASE=mint2;Trusted_Connection=yes'
currentConnection = pyodbc.connect(connectionString) 

sqlStatement1 = '''
SELECT 
    id,
    column1
FROM test3
WHERE
    ISNULL(?, id) = id
ORDER BY 
    ID
    '''

sqlStatement2 = '''
SELECT 
    id,
    column1
FROM test3
WHERE
    ISNULL(?, column1) = column1
ORDER BY 
    ID
    '''

#处理sqlStatement1
sqlArgs = []
sqlArgs.append(None)
cursor = currentConnection.cursor()
cursor.execute(sqlStatement1,sqlArgs)
rows = cursor.fetchall()
print('ROWS WITH ID=NULL:' + str(len(rows)))
cursor.close()

#处理sqlStatement2
sqlArgs = []
sqlArgs.append(None)
cursor = currentConnection.cursor()
cursor.execute(sqlStatement2,sqlArgs)
rows = cursor.fetchall()
print('ROWS WITH COLUMN1=NULL:' + str(len(rows)))
cursor.close()

三、问题 #

为何针对int类型字段的查询正常，针对varchar类型字段的查询却异常？

四、原因推测 #

问题根源在于sp_prepexec语句对参数类型的推断存在差异：

• 当参数与int类型字段对比时，位置参数P1会被推断为int类型；
• 当参数与varchar类型字段对比时，位置参数P1会被推断为varchar(1)类型。

对应执行的SQL对比 #

针对int字段的执行语句

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 int',N'
SELECT 
    id,
    column1
FROM test3
WHERE
    ISNULL(@P1, id) = id
ORDER BY 
    ID
    ',NULL
select @p1

针对varchar字段的执行语句

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 varchar(1)',N'
SELECT 
    id,
    column1
FROM test3
WHERE
    ISNULL(@P1, column1) = column1
ORDER BY 
    ID
    ',NULL
select @p1

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