Question
I am getting an incompatible comparison error detailed below, but it's dependent on the size of the string I'm passing to the filter. Anyone know what the cause of or solution to this error would be, or where I can dig in deeper to identify the root-issue?
Details
When I query filtering with a string of length 255 I receive a False response as I expected (doesn't quite match my inserted column value):
>>> from core.models import TestTable
>>> test_str = '--publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,1234'
>>> len(test_str)
255
>>> test1 = TestTable.objects.filter(test_column=test_str)
>>> test1.exists()
False
However, with a string of length 256 which I expect return True (matches my inserted column value), it instead raises an error (this test is exactly the same as the one above except test_str is one character longer):
Note: I've redacted my path in the traceback below.
>>> from core.models import TestTable
>>> test_str = '--publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,12345'
>>> len(test_str)
256
>>> test2 = TestTable.objects.filter(test_column=test_str)
>>> test2.exists()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/[REDACTED]/.venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 565, in exists
return self.query.has_results(using=self.db)
File "/[REDACTED]/.venv/local/lib/python2.7/site-packages/django/db/models/sql/query.py", line 441, in has_results
return bool(compiler.execute_sql(SINGLE))
File "/[REDACTED]/.venv/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 818, in execute_sql
cursor.execute(sql, params)
File "/[REDACTED]/.venv/local/lib/python2.7/site-packages/sql_server/pyodbc/base.py", line 325, in execute
return self.cursor.execute(sql, params)
ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]The data types nvarchar and text are incompatible in the equal to operator. (402) (SQLParamData)')
Looking at and running the raw query works fine, which has me very suspicious of the django-pyodbc package I'm on.
>>> str(TestTable.objects.filter(test_column=test_str).query)
'SELECT [custom].[test_table].[test_id], [custom].[test_table].[test_column] FROM [custom].[test_table] WHERE [custom].[test_table].[test_column] = --publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,12345 '
Edited query (quotes fixed), returns without issues manually querying my DB:
SELECT [custom].[test_table].[test_id], [custom].[test_table].[test_column]
FROM [custom].[test_table]
WHERE [custom].[test_table].[test_column] = '--publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,12345'
Additional Information
System
- Server OS: Ubuntu 14.04.5 LTS
- DB Driver: FreeTDS
djanbo-pyodbcpackage: https://github.com/avidal/django-pyodbc/tree/django-1.4- Note: For some unknown reason, we build our project off of a locally stored copy of this package, but this is the branch of the package I believe we're on.
Table
CREATE TABLE [custom].[test_table] (
test_id INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
test_column NVARCHAR(4000),
);
INSERT INTO custom.test_table (test_column)
VALUES ('--publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,12345');
Model
class TestTable(models.Model):
test_id = models.AutoField(primary_key=True)
test_column = models.TextField(null=True)
class Meta:
db_table = u'custom].[test_table'
