Unit Tests Failures on various databases

[sgithens]

Running the unit tests on Avocado master from March 26th (d66d1ea), I am getting the following failures on MySQL and Oracle. Tests pass fine on SQLite.

Using memcached. I see one error in MySQL about unsupported version. I'm using 5.5.35.

MySQL:
FAILED (failures=14, errors=26, expected failures=2)
MySQL Output: https://gist.github.com/sgithens/9808928

Oracle:
FAILED (failures=14, errors=37, expected failures=1, unexpected successes=1)
Oracle Output: https://gist.github.com/sgithens/9808954

[ryanjohara]

Confirmed that this is an issue with MySQL 5.6, as well.

[sgithens]

On Oracle, I believe some of the NCLOB errors are related to this issue that I posted about earlier:

https://groups.google.com/d/msg/harveststack/wGFKO5okdQ8/9o5IpLekifIJ
https://groups.google.com/d/msg/harveststack/wGFKO5okdQ8/fLSoeLpSIaYJ

On the one particular view I was hitting it on, I had experimentally hacked around it with the code in the second link above, but didn't make any progress past that.

https://docs.djangoproject.com/en/1.6/ref/databases/#textfield-limitations
https://code.djangoproject.com/ticket/4186
https://groups.google.com/forum/#!topic/django-users/eYTdpy4eWLQ

[bruth]

I created a branch for this ticket that now integrates testing mysql and postgres in the Travis builds. You can see the first (failed) build here: https://travis-ci.org/cbmi/avocado/builds/22293081

[sgithens]

Awesome thanks!

I noticed this is tagged for version 2.4, when is that roughly scheduled for? If there is anything I can do (especially for the Oracle stuff) that would help resolve this sometime in April let me know.

[bruth]

Here is a quick assessment of the issues:

• PostgreSQL
  • DoesNotExist failures
    • Resolution (easy): replace raw pk lookups in tests with actual pks created (in case the sequences do not start from 1)
  • min/max failures on boolean
    • Resolution (easy): Remove those tests because they are nonsense
  • tests comparing SQL output is wrong
    • Resolution: Fix discrepancies by using the lower level SQL compiler API
  • sort related failure
    • Resolution: unknown; requires further investigation
• MySQL
  • Database error regarding LIMIT + IN clause
    • Resolution (easy): refactor this method to be friendly to this constraint
  • Unbound local variable (who knows why this shows up here??)
    • Resolution (easy): fix this function to raise an error if none of the conditions match
  • Invalid literal for float in aggregation test case
    • Resolution: MySQL may not support MAX/MIN for strings? Investigate.
  • Various integer issues, e.g 1 vs. 1L
    • Resolution: Handle discrepancies in tests
  • tests comparing SQL output is wrong
    • Resolution: Fix discrepancies by using the lower level SQL compiler API
  • aggregation output values
    • Resolution (easy): switch assertions to assertAlmostEqual to compare after being rounded
• Oracle
  • NCLOB issue
    • Resolution: unknown; requires further investigation
  • Non-matching queries
    • Resolution: same issue as mentioned above
  • "too many values" in cull method
    • Resolution: refactor already required for MySQL

[bruth]

The "sort related failure" is due to difference in how databases sort null values: https://code.djangoproject.com/ticket/13312

[sgithens]

After revisting the tests, I can confirm the Oracle NCLOB issue is due to the issue I linked above, in that Oracle doesn't support distinct queries on NCLOB fields. The "description" TextField of DataField (inherited from Base in avocado/core/models ) causes the database column to be NCLOB. After first I tried to go through and put defers on any distinct queries, but that became difficult quickly to reach some of the 3rd party modules causing distinct queries.

For now I have just modified Base.description to be CharField and will be sure to keep my descriptions to a reasonable limit. I need to post to the django list about this, and I'm not sure if there is a better way to fix this for Oracle. Ideally, I'd like some django switch for oracle that will automatically defer any TextFields from a query using distinct.

I will keep an Oracle branch in the meantime with that change, as making that field change fixes all the NCLOB test cases.