Search code examples
pythonsql-serverdjangodjango-pyodbc

Incompatible Comparison Error from Filter Length

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-pyodbc package: 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'
Solution

  • There are several django-pyodbc flavors out there, but out of the packages I've tried, django-pyodbc-azure has performed best. I would recommend giving it a shot:

    https://github.com/michiya/django-pyodbc-azure