[#CORE-3302] Distinct aggregates return wrong (duplicated) data

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:

« Hide

RHEL5 server running Firebird 2.5.0.26074
Windows 7 client (flamerobin)

Issue Links:

Relate

This issue is related to:

QA-451 Test for CORE-3302

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:

[ Permalink | « Hide ]

Adriano dos Santos Fernandes added a comment - 07/Jan/11 09:47 AM

Full test case, please.

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

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
==============================================================================

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 07/Jan/11 11:11 AM

I have found the reason of the failure, already testing.

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

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.