id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
9407	The testModerationQueueContents test case does not work on databases without a microsecond precision datetime data type	egenix_viktor	nobody	"Database server: MS SQL Server 2005

Unit test: `regressiontests/moderation_view_tests`

Test case: `ModerationQueueTests.testModerationQueueContents`

Statement: `self.assertEqual(list(response.context[0][""comments""]), [c1, c2])`

The test fails due to wrong row order, since `list(response.context[0][""comments""])` gives `[c2, c1]` instead of `[c1, c2]`.

The SQL query generated (split into multiple lines for readability):

{{{
SELECT TOP 2
    [django_comments].[id],
    [django_comments].[content_type_id],
    [django_comments].[object_pk],
    [django_comments].[site_id],
    [django_comments].[user_id],
    [django_comments].[user_name],
    [django_comments].[user_email],
    [django_comments].[user_url],
    [django_comments].[comment],
    [django_comments].[submit_date],
    [django_comments].[ip_address],
    [django_comments].[is_public],
    [django_comments].[is_removed]
FROM [django_comments]
WHERE ([django_comments].[is_public] = %s AND
       [django_comments].[is_removed] = %s )
ORDER BY [django_comments].[submit_date] ASC
}}}

Parameters:

{{{
(False, False)
}}}

Which will be translated to `(0, 0)`.

The above test case depends on sorting the comments on their `submit_date`. Since MS SQL does not have a microsecond component in its `datetime` columns both comments are inserted with the same `submit_date` value with nearly 100% probability. It might cause a result set with unexpected record order, since the above query sorts the rows on a column containing identical values, therefore no sort order defined at all.

So, this bug is due to the lack of microsecond precision of MS SQL Server's `datetime` data type. However, it works on SQLite, since SQLite can store the microseconds as well.

The test case should be modified to insert the test data with different `submit_date` values or sort on the primary key as well as a secondary ordering."		closed	Database layer (models, ORM)	1.0		invalid	MSSQL microsecond datetime ordering		Accepted	0	0	0	0	0	0