The testModerationQueueContents test case does not work on databases without a microsecond precision datetime data type
|Reported by:||egenix_viktor||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||1.0|
|Severity:||Keywords:||MSSQL microsecond datetime ordering|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
Database server: MS SQL Server 2005
Unit test: regressiontests/moderation_view_tests
Test case: ModerationQueueTests.testModerationQueueContents
Statement: self.assertEqual(list(response.context["comments"]), [c1, c2])
The test fails due to wrong row order, since list(response.context["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
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.
Change History (5)
comment:1 Changed 7 years ago by egenix_viktor
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
comment:2 Changed 6 years ago by jacob
- milestone set to 1.1
- Triage Stage changed from Unreviewed to Accepted
comment:4 Changed 6 years ago by Travis Cline <travis.cline@…>
- Resolution set to invalid
- Status changed from new to closed