将 None 作为参数值传递给 SQL Server 时出现意外行为

Unexpected behaviour when passing None as a parameter value to SQL Server

提问人:Kurt 提问时间:11/13/2023 最后编辑:Gord ThompsonKurt 更新时间:11/13/2023 访问量:28

问:

给定以下 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 返回表的所有两行。 sqlstatement2 返回表的零行。

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
    '''

#Process 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()

#Process 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 数据类型而不是字符串数据类型呢?

我的直觉是,当语句比较时,sp_prepexec 语句出于某种原因将位置参数 P1 创建为 varchar(1) ?添加到 varchar 列并将 P1 设置为 和 int,当语句 comapres ?到 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

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
python sql-server odbc pyodbc

评论

0赞 Thom A 11/13/2023
作为相关的说明,像这样的条款是不可取的。相反,最好在子句中使用 和 have,或者(甚至更好)当参数具有值时,仅在查询中使用 。WHERE ISNULL(@P1, id) = idWHERE @P1 = id OR @P1 IS NULLRECOMPILEOPTIONWHERE

答:

3赞 Charlieface 11/13/2023 #1

我的直觉是,当语句比较时,sp_prepexec 语句出于某种原因将位置参数 P1 创建为 varchar(1) ?添加到 varchar 列并将 P1 设置为 和 int,当语句 comapres ?添加到 int 列。

是的,这正是它的作用。它不知道参数有多大,因为你没有告诉它。GitHub 上已经注意到了这一点。

因为您在 的左侧使用它,所以右侧被强制转换为左侧,因此查询没有给出正确的结果。ISNULL

您有多种解决方案:

  • 可用于设置字符串参数的类型和大小:setinputsizes

    cursor.setinputsizes([(pyodbc.SQL_VARCHAR, 10, 0)])
    
  • 进行显式强制转换。这是一个更好的选择,因为它永远不会失败,您可以单独设置每个值。

    WHERE
        ISNULL(CAST(? AS varchar(10)), column1) = column1
    

    您也可以通过先将其设置为正确大小的变量来执行此操作。

  • 将查询重写为不使用 ,无论如何都应该这样做,因为它会阻止使用索引。ISNULL

    • 要么使用需要传递两次参数的注释,要么将其分配给 SQL 中的变量。OR
      sqlStatement2 = '''
      SELECT 
          id,
            column1
      FROM test3
      WHERE
          (? = column1 OR ? IS NULL)
      ORDER BY 
          ID;
      '''
      
    • 或者在我看来,最好的选择是使查询动态化,以便您预先决定是否按该列进行筛选。
      sqlStatement2 = '''
      SELECT 
          id,
            column1
      FROM test3
      '''
      
      if someValue is not None:
          sqlStatement2 = sqlStatement2 + '''WHERE
            ? = column1
      '''
      
      sqlStatement2 = sqlStatement2 + '''ORDER BY 
          ID;
      '''
      

请注意,在最后两个选项中,设置变量大小可能仍然是理想的选择。

评论

0赞 Kurt 11/13/2023
如果可能的话,我希望尽量远离动态字符串,并让左侧的isnull使用否定任何索引。我不喜欢两次传递相同的参数,甚至尝试了cursor.execute(sqlStatement1,[sqlArgs]*2)语法,但无法使其工作,老实说,我不喜欢它。我知道我可以使用匿名 t-sql 块来做到这一点,但这似乎有点矫枉过正和混乱。我想我会尝试你的选角。感谢您的详细回复。
0赞 Charlieface 11/13/2023
sqlArgs.append(None) sqlArgs.append(None)是你会如何做到这一点
0赞 Gord Thompson 11/13/2023
像往常一样,很好的答案。不过,一个可能的澄清:有些人可能会(错误地)解释“[setinputsizes] 为所有字符串变量设置它”意味着整个查询的所有字符串参数都将作为 发送,而实际上它只是该列的字符串参数(基于它在参数列表中的位置)。varchar(10)
1赞 Charlieface 11/13/2023
啊,对不起,我实际上以为这就是它的作用。现已修复。
0赞 Kurt 11/13/2023
比我的OP好多了。 谢谢大家!