Wrong result from SUM() function when run over a distinct result from a subquery

[marcin]

Thanks for the report, Ania.
This issue was partially solved, but we indeed found out that variants of such queries still have problems. It's being worked on, I've asked my colleagues to give it a priority.

[clada01]

I believe that the original example and the most recent example are fixed in version 2.5. (I had to guess at the schema and data for the last one, but think it's fixed).

However, we do have verbal reports that other examples exist which are not fixed. Don't have examples yet, though.

[clada01]

The first reported problem is fixed in 2.5. The second appears to be fixed. Here is the repro case used for the latter:


drop table login_fact\g

create table login_fact (user_sk int, days int, datetime date)\g
insert into login_fact values (0, 1, '2012-03-02')\g
insert into login_fact values (0, 1, '2012-03-02')\g
insert into login_fact values (0, 1, '2012-03-03')\g
insert into login_fact values (1, 1, '2012-03-02')\g
insert into login_fact values (1, 1, '2012-03-02')\g
insert into login_fact values (1, 1, '2012-03-02')\g

select count(distinct user_sk) x
from login_fact
where cast(datetime, ansidate) between '2012-03-01 00:00:00' and '2012-03-31 23:59:59' group by day(datetime)\g

select count(x) days from
(select count(distinct user_sk) x
from login_fact
where cast(datetime, ansidate) between '2012-03-01 00:00:00' and '2012-03-31 23:59:59' group by day(datetime)) a; \g

+----------------------+
|x |
+----------------------+
| 2|
| 1|
+----------------------+
(2 rows)
continue
* * * * * Executing . . .


+----------------------+
|days |
+----------------------+
| 2|
+----------------------+
(1 row)
continue

[clada01]

Both of the examples in this thread are part of the 2.5 regression test suite, so should not reoccur.

[ania]

I've just came across this old thread and checked, that problem reported in may still occurs.

We have now Vectorwise Version VW 2.5.1 (a64.lnx/162)NPTL

First I wanted to run this query:

select count(*) 
from (
  select day(datetime), count(distinct user_sk) x 
  from login_fact 
where datetime between '2012-03-01 00:00:00' and '2012-03-31 23:59:59' 
group by 1) c
\g

But it gives:

E_US0C1C Query requires too many range variables.
    (Thu Sep  6 05:47:44 2012)

Which is also a bit strange, but...

Secondly, I've tried this:

select count(*) 
from (
  select day(datetime), count(distinct user_sk) x 
  from login_201203_fact 
where datetime between '2012-03-01 00:00:00' and '2012-03-31 23:59:59' 
group by 1) c
\g

with data from march only.

The result is:

+----------------------+
|col1                  |
+----------------------+
|               2655309|
+----------------------+
(1 row)
continue
* select count(distinct day) from (select day(datetime) as day, count(distinct user_sk) x from login_201203_fact where datetime between '2012-03-01 00:00:00' and '2012-03-31 23:59:59' group by 1) c\g
Executing . . .


+----------------------+
|col1                  |
+----------------------+
|                    31|
+----------------------+
(1 row)
continue

And I'm still a bit unsatisfied with it.

[makni01]

I believe this was just fixed and will be made available in the next patch.