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

Given the following test3 table

/****** 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

Problem:

The sqlstatement1 returns all two rows of the table. The sqlstatement2 returns zero rows of the table.

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

So why does it work with an int data type but not a string data type?

My gut is that the sp_prepexec statement is creating the positional parameter P1 as varchar(1) for some reason when the statement compares ? to a varchar column and sets P1 to and int when the statement comapres ? to a int column:

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

Solution

My gut is that the sp_prepexec statement is creating the positional parameter P1 as varchar(1) for some reason when the statement compares ? to a varchar column and sets P1 to and int when the statement comapres ? to a int column.

Yes that is exactly what it does. It has no knowledge of how large to make the parameter, because you haven't told it. This has been noted on GitHub.

Because you are using it on the left side of an ISNULL, the right side is casted to the left side, hence the query is not giving the correct results.

You have a number of solutions:

You can use setinputsizes to set the type and size of a string parameter:
```
cursor.setinputsizes([(pyodbc.SQL_VARCHAR, 10, 0)])
```
Do an explicit cast. This is a much better option, as it can never fail and you can set each value individually.
```
WHERE
    ISNULL(CAST(? AS varchar(10)), column1) = column1
```
You can also do this by setting it to a variable of the correct size first.

Rewrite your query to not use ISNULL, which you should do anyway, because it prevents the use of indexes.

Either use an OR

sqlStatement2 = '''
SELECT 
    id,
      column1
FROM test3
WHERE
    (? = column1 OR ? IS NULL)
ORDER BY 
    ID;
'''

Note that you need to pass the parameter twice, or assign it to a variable within the SQL.

Or the best option in my opinion, make the query dynamic, so you decide up front whether to filter by that column.

sqlStatement2 = '''
SELECT 
    id,
      column1
FROM test3
'''

if someValue is not None:
    sqlStatement2 = sqlStatement2 + '''WHERE
      ? = column1
'''

sqlStatement2 = sqlStatement2 + '''ORDER BY 
    ID;
'''

Note that in these last two options, it's probably still ideal to set the variable size.