Issue Details (XML | Word | Printable)

Key: CORE-3302
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Paul Tessman
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Distinct aggregates return wrong (duplicated) data

Created: 07/Jan/11 02:32 AM   Updated: 24/Feb/11 11:19 AM
Component/s: Engine
Affects Version/s: 3.0 Initial, 2.5.0
Fix Version/s: 2.5.1, 3.0 Alpha 1

Time Tracking:
Not Specified

Environment:
RHEL5 server running Firebird 2.5.0.26074
Windows 7 client (flamerobin)
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
We've noticed a discrepancy between our Firebird 2.5.0 and 2.1.2 servers. The following is the query in question:

select
  list(distinct case extract(weekday from date_of_task) when 0 then 'Sun' when 1 then 'Mon' when 2 then 'Tue' when 3 then 'Wed' when 4 then 'Thu' when 5 then 'Fri' when 6 then 'Sat' end)
from tasks where date_of_task is not null;

"tasks" being any table containing a date field, and "date_of_task" being that date field.

On 2.1.2, this query returns:

Fri,Mon,Sat,Sun,Thu,Tue,Wed

as one would expect. On 2.5.0, however, this query returns:

Fri,Fri,Fri,Fri,Fri,Fri,Mon,Mon,Mon,Mon,Mon,Mon,Sat,Sat,Sat,Sat,Sat,Sat,Sun,Sun,Sun,Sun,Sun,Sun,Thu,Thu,Thu,Thu,Thu,Thu,Tue,Tue,Tue,Tue,Tue,Tue,Wed,Wed,Wed,Wed,Wed,Wed

Let me know if I can provide further information of help.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 07/Jan/11 09:47 AM
Full test case, please.

Dmitry Yemanov added a comment - 07/Jan/11 10:46 AM
I can reproduce it easily with any table containing the date field. It seems that the problem lies somewhere inside the SortAggregate handling, i.e. the internal sorting supporting the DISTINCT clause. Six subsequent SORT_get() calls return the same value and only then switch to another one.

Dmitry Yemanov added a comment - 07/Jan/11 10:59 AM
I confirm that the problem is not with LIST but with any aggregate function. COUNT(DISTINCT) returns 42 instead of 7.

Adriano dos Santos Fernandes added a comment - 07/Jan/11 11:04 AM - edited
I'm testing 2.5.0. This is my test which ran ok:

SQL> show table tasks;
DATE_OF_TASK DATE Nullable
SQL> select * from tasks;

DATE_OF_TASK
============
2011-01-07
2011-01-07
2011-01-07
2011-01-06
2011-01-06
2011-01-06
2011-01-08
2011-01-08
2011-01-08

SQL> select count(distinct date_of_task) from tasks;

       COUNT
============
           3

SQL> select
CON> list(distinct case extract(weekday from date_of_task) when 0 then 'Sun' when 1 then 'Mon' when 2 then 'Tue' when
3 then 'Wed' when 4 then 'Thu' when 5 then 'Fri' when 6 then 'Sat' end)
CON> from tasks where date_of_task is not null;

             LIST
=================
              0:1
==============================================================================
LIST:
Fri,Sat,Thu
==============================================================================

Dmitry Yemanov added a comment - 07/Jan/11 11:11 AM
I have found the reason of the failure, already testing.

Dmitry Yemanov added a comment - 07/Jan/11 11:17 AM
I was testing with a rather big table (millions of rows). And this is a real regression, v2.1 has the correct code.

Dmitry Yemanov added a comment - 07/Jan/11 11:28 AM
The problem was that MOVE_CLEAR() zapped only first part of the sort key, as asb_length was not rounded up. Prior versions used ROUNDUP_LONG for both SORT_init() and SORT_put(), but v2.5 missed that rounding where SORT_put() is called.