[FIXED] SQL Alchemy the data types text and text are incompatible in the equal operator

Issue

In part of my django API I have the following, to update old notes:

old_note = request.databaseSession.query(Tmemo).\
    filter(Tmemo.memosern1 == serial).\
    one()

This query works without any problem as I am using it many other places. The weird problem comes with:

old_note.memotext = newtext

Then when I commit the following errors happens:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', u'[42000] 
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The data types 
text and text are incompatible in the equal to operator. (402) 
(SQLExecDirectW)') [SQL: u'UPDATE tmemo SET memotext=? WHERE 
tmemo.memosern1 = ? AND tmemo.memosern2 = ? AND tmemo.memotype = ? AND 
tmemo.memotext = ?'] [parameters: ('asassasasasaassaassaasas2121', 
u'P03000000060445', u'MEMO', u'5', u'asassasasasaassaassaasas')] 
(Background on this error at: http://sqlalche.me/e/f405)

Tmemo is just a table with column for a serial number memosern1 and memosern2, type with memotype and the notes itself as memotext, serials and type are varchar and memotext as text. I really don't understand what text and text are incompatible means really, it makes no sense, also I am just filtering the serial number, nothing else, so what is up with this weird query?

And I can perfectly create another row in the table with the exact same variables being used (newtext), I just can't update an existent one.

Any ideias? I can provide more info

Solution

The TEXT-type is really just a BLOB and can be very large, depending on your database server 2**64-1 bytes or even more. Because of its (possible) size, the equality operator is not implemented for TEXT and BLOB, therefore you can't compare values of type TEXT to anything (in your case TEXT to TEXT).

What you have to do is either use a VARCHAR or - alternatively, slightly hacky - compute and store the hash of the TEXT-column and compare that instead. If you compute the MD5() of the TEXT-value, store only the upper 64bit as an INT and put a functional index on that column, you can basically have the same result without too much cost (except the extra hash + index operation on insert/update and a very slight chance that you compare something to equality that is not really equal).

In the long term, you should use VARCHAR instead of TEXT, as TEXT will be removed from future versions of SQL Server.

Answered By - user2722968