Actian Community Forum

mzak · 2012-07-19

Hi!

I found bug in COUNT while using window function and view:

Code:

drop table if exists test_users;
create table test_users (
id integer not null,
name varchar(10) not null,
height integer not null
) with structure = vectorwise;

insert into test_users values (1, 'Tom', 100);
insert into test_users values (2, 'Tom', 50);
insert into test_users values (3, 'Jerry', 10);
insert into test_users values (4, 'Jerry', 20);
\g


Drop view if exists single_test_users;
\g

CREATE VIEW single_test_users as
SELECT
id, name, height
FROM (
  select
	id, name, height,
	ROW_NUMBER() OVER (
		PARTITION BY name
		ORDER BY height DESC
		) AS r
  from test_users cd
  group by id, name, height
) x
WHERE
r = 1
\g

--Should output 2, outputs 1 what is wrong
select count(id) from single_test_users \g

--Should output 2, outputs 2 what is correct
select count(distinct id) from single_test_users \g

If table is created instead of view everything works fine. Bug occurs only in views and subqueries

verde02 · 2012-07-20

group by name must include id , height re. columns in select clause nontheless the 1st select with the result of 1 seems incorrect and for the same select against a table we see the correct result.

mzak · 2012-07-20

My fault. I fixed query in previous post. Nonetheless select count(id) and count(distinct id) produce different results:

Code:

select distinct id from single_test_users \g

|id |
| 1|
| 4|

Code:

select id from single_test_users \g

|id |
| 1|
| 4|

Code:

select count(distinct id) from single_test_users \g

2

But

Code:

select count(id) from single_test_users \g

produces incorrect value: 1

kschendel · 2012-07-20

I think this is a variation of a known, very old, and extremely elusive problem related to combinations of count distinct and aggregates in views. Worth double checking though.