Error when updating BYTE(4) column with a bytearray parameter

[ShaunCurrier]

I get the following error when trying to update a BYTE(4) column with a bytearray. DatabaseError: (3639, u'[22018] [Teradata][ODBC Teradata Driver][Teradata Database] Comparing BYTE data with other types is illegal. ') The column was initially populated with a bytearray generated in exactly the same way as the byte array that I am trying to update with. The SQL that I am using to cause this is the following:

UPDATE table
SET byte_column = ?
WHERE some_field1 = ? AND
      some_field2 = ? AND
      some_field3 = ? AND
      some_field4 = ?;

where byte_column's parameter is a <type 'bytearray'> and is equal to bytearray(b'Y\x08@o'). The python call that is causing this error is curs.execute(myquery, parameters_list) where parameters_list is a <type 'list'> that contains the bytearray that I can't update. I've checked and double-checked that the parameter is positioned correctly in the list to line up with the byte_column parameter.

[escheie]

What is the result of the following query:

SELECT byte_column FROM table
WHERE some_field1 = ? AND
      some_field2 = ? AND
      some_field3 = ? AND
      some_field4 = ?;

I'm thinking the error has less to do with the update column and more to do with the equality operations in the WHERE clause. I'm able to update BYTE(4) columns no problem.

[ShaunCurrier]

Thanks for your response. I tried this and I got my bytearray back as expected: bytearray(b'Y\x08@o')

[ShaunCurrier]

Just to make sure I was not crazy, I double checked that the output of the SELECT statement equaled the parameter that I am trying to input with the failing UPDATE statement. curs.fetchone()[0] == new_bytearray It evaluates to True.

[escheie]

Since I'm unable to reproduce this issue, would you be able to provide a very simple script that reproduces the error. Here is a starting point, does this fail for you?

conn.execute("CREATE TABLE testByteDataType (id INTEGER, b BYTE(4))")
conn.execute("INSERT INTO testByteDataType VALUES (1, ?)", (bytearray([0xAA, 0xBB, 0xCC, 0xDD]), ))
conn.execute("UPDATE testByteDataType SET b = ? WHERE id = 1", (bytearray([0xAA, 0xAA, 0xAA, 0xAA]), ))

Can you also provide the TD, ODBC, Python versions and OS information in case that's a factor.

[ShaunCurrier]

Looks like we're running TD version 15.00.02.03. ODBC Driver for Teradata version is 15.10.00.00. My python version is 2.7 x64. I'm going to try what you suggest and get back to you.

[ShaunCurrier]

I modified your suggested code so that it would run for me, I did not understand the self.assertEqual part so I changed it. This prints True True:

conn.execute("CREATE TABLE testByteDataType (id INTEGER, b BYTE(4))")
conn.execute("INSERT INTO testByteDataType VALUES (1, ?)", (bytearray([0xAA, 0xBB, 0xCC, 0xDD]), ))
for row in conn.execute("SELECT * FROM testByteDataType WHERE id = 1"):
    print row.b == bytearray([0xAA, 0xBB, 0xCC, 0xDD])
conn.execute("UPDATE testByteDataType SET b = ? WHERE id = 1", (bytearray([0xAA, 0xAA, 0xAA, 0xAA]), ))
for row in conn.execute("SELECT * FROM testByteDataType WHERE id = 1"):
    print row.b == bytearray([0xAA, 0xAA, 0xAA, 0xAA])

...so I am not reproducing it with this. I am going to try running something like this during debug in my application to further troubleshoot.

[ShaunCurrier]

I tried running this code during debug at the line which causes the issue. Instead of updating my application table, I use the code to create the test table, populate it with my real application bytearray, read it back out and compare it (which succeeds), update it, and read it back out again (which succeeds).

I want to try creating a test table with an identical definition to my application table and doing the same thing.

[ShaunCurrier]

While in debug at the point of failure, I tried creating a test table whose definition matched that of my application table and doing similar code to the above. It succeeded. I'm really stumped.

[ShaunCurrier]

I tried updating the test table with a similar query to my application query and did reproduce the error. This appears to be related to my query.

[ShaunCurrier]

The WHERE clause is causing the issue. When try to do a query like this with the where clause value being a literal string embedded in the query, it succeeds:

UPDATE table
SET byte_column = ?
WHERE some_CHAR_field1 = 'STRING01';

But, when I try to do a query that should be identical to this, except parameterizing the value in the WHERE clause, it fails:

UPDATE table
SET byte_column = ?
WHERE some_CHAR_field1 = ?;

...where the WHERE clause parameter is a <type 'str'> of length 8 and the column some_CHAR_field1 is a CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL.

I'm not sure if this proves that there is a bug, but it seems pretty convincing to me. I haven't tested the other AND'ed parts of my where clause to see if this is specific to the string column or if the other columns with different data types are affected as well. I'll wait for your feedback.

[escheie]

I've been able to reproduce the bug. The problem is that when the byte array precedes character data, the character data is treated as bytes instead of chars. I will release a new version shortly with the fix.

[ShaunCurrier]

The fix worked! Thanks.