Fix unit tests for older sqlite

[Tim Hatch]

This appears to only happen on the OSX buildbots since adding the cast in the query module. The expected sql needs to check the sqlite version same as the sqlite backend itself.

======================================================================
FAIL: test_all_ordered_by_priority (trac.ticket.tests.query.QueryTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/tim/trac/t05/build/trac/ticket/tests/query.py", line 76, in test_all_ordered_by_priority
    ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS int),t.id""")
  File "/Users/tim/trac/t05/build/trac/ticket/tests/query.py", line 29, in assertEqualSQL
    self.assertEqual(sql, correct_sql, failure_message)
AssertionError: "SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value\nFROM ticket AS t\n  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)\nORDER BY COALESCE(priority.value,'')='',1*priority.value,t.id" != "SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value\nFROM ticket AS t\n  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)\nORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS int),t.id"
---  
+++  
@@ -12,5 +12,5 @@
   LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority),
 ORDER BY COALESCE(priority.value,
 '')='',
-CAST(priority.value AS int),
+1*priority.value,
 t.id,


======================================================================
FAIL: test_all_ordered_by_priority_desc (trac.ticket.tests.query.QueryTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/tim/trac/t05/build/trac/ticket/tests/query.py", line 87, in test_all_ordered_by_priority_desc
    ORDER BY COALESCE(priority.value,'')='' DESC,CAST(priority.value AS int) DESC,t.id""")
  File "/Users/tim/trac/t05/build/trac/ticket/tests/query.py", line 29, in assertEqualSQL
    self.assertEqual(sql, correct_sql, failure_message)
AssertionError: "SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value\nFROM ticket AS t\n  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)\nORDER BY COALESCE(priority.value,'')='' DESC,1*priority.value DESC,t.id" != "SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value\nFROM ticket AS t\n  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)\nORDER BY COALESCE(priority.value,'')='' DESC,CAST(priority.value AS int) DESC,t.id"
---  
+++  
@@ -12,5 +12,5 @@
   LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority),
 ORDER BY COALESCE(priority.value,
 '')='' DESC,
-CAST(priority.value AS int) DESC,
+1*priority.value DESC,
 t.id,


======================================================================
FAIL: test_grouped_by_priority (trac.ticket.tests.query.QueryTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/tim/trac/t05/build/trac/ticket/tests/query.py", line 158, in test_grouped_by_priority
    ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS int),t.id""")
  File "/Users/tim/trac/t05/build/trac/ticket/tests/query.py", line 29, in assertEqualSQL
    self.assertEqual(sql, correct_sql, failure_message)
AssertionError: "SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.milestone AS milestone,t.component AS component,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value\nFROM ticket AS t\n  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)\nORDER BY COALESCE(priority.value,'')='',1*priority.value,t.id" != "SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.milestone AS milestone,t.component AS component,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value\nFROM ticket AS t\n  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)\nORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS int),t.id"
---  
+++  
@@ -13,5 +13,5 @@
   LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority),
 ORDER BY COALESCE(priority.value,
 '')='',
-CAST(priority.value AS int),
+1*priority.value,
 t.id,


----------------------------------------------------------------------
Ran 715 tests in 126.944s

FAILED (failures=3)
program finished with exit code 1

[Jonas Borgström]

Which OS X version is this?

OS X leopard ships with sqlite 3.4.0 which seems to have no problem with our unit tests.

[Tim Hatch]

667 MHz G4 running Tiger with both preinstalled Python 2.3.5
and the 2.5.2 Framework build.  Genshi 0.4.4 and Pygments 0.9.
No pytz.

[Tim Hatch]

Should be fixed in r6903