Test failures on Oracle/Python3

[Shai Berger]

On CI, as well as on my system:

ERROR: test_custom_functions (annotations.tests.NonAggregateAnnotationTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/django/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/django/django/django/db/backends/oracle/base.py", line 916, in execute
    return self.cursor.execute(query, self._param_generator(params))
cx_Oracle.DatabaseError: ORA-12704: character set mismatch


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/django/django/tests/annotations/tests.py", line 254, in test_custom_functions
    lambda c: (c.name, c.tagline)
  File "/home/django/django/django/test/testcases.py", line 869, in assertQuerysetEqual
    items = six.moves.map(transform, qs)
  File "/home/django/django/django/db/models/query.py", line 161, in __iter__
    self._fetch_all()
  File "/home/django/django/django/db/models/query.py", line 989, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/django/django/django/db/models/query.py", line 289, in iterator
    for row in compiler.results_iter():
  File "/home/django/django/django/db/models/sql/compiler.py", line 720, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/home/django/django/django/db/models/sql/compiler.py", line 817, in execute_sql
    cursor.execute(sql, params)
  File "/home/django/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/django/django/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/django/django/django/utils/six.py", line 624, in reraise
    raise value.with_traceback(tb)
  File "/home/django/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/django/django/django/db/backends/oracle/base.py", line 916, in execute
    return self.cursor.execute(query, self._param_generator(params))
django.db.utils.DatabaseError: ORA-12704: character set mismatch


======================================================================
ERROR: test_custom_functions_can_ref_other_functions (annotations.tests.NonAggregateAnnotationTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/django/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/django/django/django/db/backends/oracle/base.py", line 916, in execute
    return self.cursor.execute(query, self._param_generator(params))
cx_Oracle.DatabaseError: ORA-12704: character set mismatch


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/django/django/tests/annotations/tests.py", line 287, in test_custom_functions_can_ref_other_functions
    lambda c: (c.name, c.tagline_lower)
  File "/home/django/django/django/test/testcases.py", line 869, in assertQuerysetEqual
    items = six.moves.map(transform, qs)
  File "/home/django/django/django/db/models/query.py", line 161, in __iter__
    self._fetch_all()
  File "/home/django/django/django/db/models/query.py", line 989, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/django/django/django/db/models/query.py", line 289, in iterator
    for row in compiler.results_iter():
  File "/home/django/django/django/db/models/sql/compiler.py", line 720, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/home/django/django/django/db/models/sql/compiler.py", line 817, in execute_sql
    cursor.execute(sql, params)
  File "/home/django/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/django/django/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/django/django/django/utils/six.py", line 624, in reraise
    raise value.with_traceback(tb)
  File "/home/django/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/django/django/django/db/backends/oracle/base.py", line 916, in execute
    return self.cursor.execute(query, self._param_generator(params))
django.db.utils.DatabaseError: ORA-12704: character set mismatch

[Thomas C]

I can reproduce it, the query generating the failure is:

SELECT "ANNOTATIONS_COMPANY"."ID", "ANNOTATIONS_COMPANY"."NAME", "ANNOTATIONS_COMPANY"."MOTTO", "ANNOTATIONS_COMPANY"."TICKER_NAME", "ANNOTATIONS_COMPANY"."DESCRIPTION", COALESCE("ANNOTATIONS_COMPANY"."MOTTO", "ANNOTATIONS_COMPANY"."TICKER_NAME", "ANNOTATIONS_COMPANY"."DESCRIPTION", %s) AS "TAGLINE" FROM "ANNOTATIONS_COMPANY" ORDER BY "ANNOTATIONS_COMPANY"."NAME" ASC

with argument ('No Tag',)

See also ​https://community.oracle.com/thread/2165039?start=0&tstart=0, ​http://stackoverflow.com/questions/15967201/ora-12704-character-set-mismatch

[Thomas C]

The patch above implements the workaround in ​https://community.oracle.com/thread/2165039?start=0&tstart=0. It seems to solve the problem (at least on my Oracle VM), although it's kind of cumbersome.

[Shai Berger]

Replying to tchaumeny:

The patch above implements the workaround in ​https://community.oracle.com/thread/2165039?start=0&tstart=0. It seems to solve the problem (at least on my Oracle VM), although it's kind of cumbersome. It might be better to just skip that on Oracle.

The fact that the patch works is good info for solving the bug, but the patch itself cannot be accepted. We (well, aaugustin, mostly) made a significant effort to remove this kind of vendor-specific code from the test-suite, and we'd like to keep it as clean of it as possible. But more importantly, the test failure represents a genuine problem; the patch, essentially, offers a workaround by adding backend-specific cruft in user code. That is sort-of acceptable as a workaround, not as a solution.

The thing I am most curious about is why this fails only on Python3; our code should be sending exactly the same objects (in terms of bytes/unicode) on Python2, but it works there.

[Shai Berger]

Replying to charettes:

FWIW the deprecation warnings have been fixed in 68ef44c565d901945eb74768d439c93678315cf6.

Yes, those have nothing to do with the bug, and I probably just ran the test with -Werror by mistake. I've removed them from the description.

[Thomas C]

After some investigation, that seems to be a bug in cx_Oracle, which handles unicode objects differently between different versions — this isn't due to Django as the failure is reproductible using cx_Oracle cursor directly. I opened an issue there ​https://bitbucket.org/anthony_tuininga/cx_oracle/issue/6/python-3-string-parameters-are-not-handled with more detail.

[Shai Berger]

Replying to tchaumeny:

After some investigation, that seems to be a bug in cx_Oracle, which handles unicode objects differently between different versions — this isn't due to Django as the failure is reproductible using cx_Oracle cursor directly. I opened an issue there ​https://bitbucket.org/anthony_tuininga/cx_oracle/issue/6/python-3-string-parameters-are-not-handled with more detail.

Hey @tchaumeny, great work. I am swamped with non-Django issues at the moment, so will only be able to look deeper into this later this week, but your research looks sound and identifying the problem is more than half way to solving it; so I just wanted to say cheers.

[Shai Berger]

A fix has been ​submitted to cx-Oracle.

[Shai Berger <shai@…>]

In 4a4ad27712b44cebada1bdaebd082cf82df74610:

Marked some tests as expected failures on Oracle/cx_Oracle<=5.1.3/Python3

Refs #23843 which is really a cx_Oracle bug

[Shai Berger]

With the above, this is no longer a release blocker.

[Tim Graham]

Can we close this ticket? Seems we are just waiting on the fix being integrated in cx_Oracle (or is there more?).

[Shai Berger]

Replying to timgraham:

Can we close this ticket? Seems we are just waiting on the fix being integrated in cx_Oracle (or is there more?).

When cx_Oracle finally integrates the fix, we'd need to update the required version for Python 3.

[Tim Graham]

Shai, what's the status here? It looks like the issue didn't get fixed in cX_Oracle 5.2 so should we update our test skipping logic?

[Shai Berger]

Yes. cx_Oracle rejected the suggested fix over performance issues (AFAIK resulting from databases whose encoding is not utf-8), suggesting a workaround. We need to remove the test skipping and solve it properly. I had it listed in my to-do, but forgot about this ticket existing.

[Tim Graham <timograham@…>]

In f9636fd:

Refs #23843 -- Updated Oracle annotations workaround to reflect latest status.

[Tim Graham <timograham@…>]

In 632a917c:

[1.8.x] Refs #23843 -- Updated Oracle annotations workaround to reflect latest status.

Backport of f9636fdf922fe49ff82d02b17d6b34469fcf1fda from master