cursor.execute() with INSERT statement containing OUTPUT inserted + multiple VALUES entries does not raise IntegrityError #333
Description
Describe the bug
a UNIQUE constraint error is not appropriately triggered for a multi-row INSERT statement that uses OUTPUT inserted
this is a much more specific case than it first looked like, so I've run a full comparison to pyodbc to see what the best case behavior is. it looks like based on what pyodbc and pymssql do, it's not apparent there's an integrity violation until the fetchall() is called. so this suggests there is a class of error conditions in SQL server's protocol that does not become apparent until rows are fetched, which may be missing in mssql-python.
To reproduce
from mssql_python import connect
import mssql_python
conn_str = "UID=scott;PWD=tiger^5HHH;Server=mssql2022;Database=test;Encrypt=No"
conn = connect(conn_str)
cursor = conn.cursor()
cursor.execute("""DROP TABLE IF EXISTS uniq_cons""")
cursor.execute("""
CREATE TABLE uniq_cons (
id INTEGER NOT NULL IDENTITY,
data VARCHAR(50) NULL,
PRIMARY KEY (id),
UNIQUE (data)
)
""")
# insert a row. works
cursor.execute("INSERT INTO uniq_cons (data) OUTPUT inserted.id VALUES (?)", ('the data 1', ))
# this was OUTPUT inserted, so fetchall to make sure operation is complete
cursor.fetchall()
# insert a dupe row. raises as expected
try:
cursor.execute("INSERT INTO uniq_cons (data) OUTPUT inserted.id VALUES (?)", ('the data 1', ))
# this was OUTPUT inserted, so fetchall to make sure
cursor.fetchall()
except mssql_python.IntegrityError:
print("raised as expected")
else:
print("INCORRECT: should have raised")
# insert two rows in one statement. that works
cursor.execute("INSERT INTO uniq_cons (data) OUTPUT inserted.id VALUES (?), (?)", ('the data 2', 'the data 3'))
# this was OUTPUT inserted, so fetchall to make sure
cursor.fetchall()
# assert what's in the table. seems correct (we lost one IDENTITY value due
# to the exception, that's fine)
cursor.execute("SELECT * from uniq_cons ORDER BY id")
rows = cursor.fetchall()
# we can't compare mssql_python.row.Row to a tuple, might be another issue
# or not, not that important right now
assert [tuple(row) for row in rows] == [(1, "the data 1"), (3, "the data 2"), (4, "the data 3")]
# now to the problem!
try:
cursor.execute("INSERT INTO uniq_cons (data) OUTPUT inserted.id VALUES (?), (?)", ('the data 4', 'the data 4'))
# this was OUTPUT inserted, so fetchall to make sure.
# This is **IMPORTANT** because pyodbc AND pymssql, in the equivalent program, does not
# seem to raise its IntegrityError until the fetchall() is called; so it appears
# there is some weirdness in the SQL Server protocol that has to be handled here
cursor.fetchall()
except mssql_python.IntegrityError:
print("raised as expected")
else:
print("INCORRECT: should have raised")
print("what's actually in the table?")
cursor.execute("SELECT * from uniq_cons")
print(cursor.fetchall())Running this program with mssql-python shows:
# mssql-python version
raised as expected
INCORRECT: should have raised
what's actually in the table?
[(1, 'the data 1'), (3, 'the data 2'), (4, 'the data 3')]
replacing mssql_python with pyodbc, the output is:
# pyodbc version
raised as expected
raised as expected
what's actually in the table?
[(1, 'the data 1'), (3, 'the data 2'), (4, 'the data 3')]
Expected behavior
The INSERT that has dupe rows should raise IntegrityError. As pyodbc seems to not raise until the fetchall(), maybe it has to happen there, not sure why. but it shouldn't silently fail.
latest mssql-python 0.14.0