Hello,

The following SELECT statement return 0 rows instead of one
on ingres Solaris version
13351 1 II 9.0.4 (a64.sol/105)

What can be the cause of that and does the same error occur on other versions ?
Note that I get the correct result if I either change
" max(b.a) != count(b.a)" to " max(b.a) != count(*)"
or reduce the having clause to " max(b.a) != count(b.a)"



DECLARE GLOBAL TEMPORARY TABLE session.a (id i4 not null)
ON COMMIT PRESERVE ROWS WITH NORECOVERY
;
insert into session.a values (1);
insert into session.a values (2);
insert into session.a values (3);


DECLARE GLOBAL TEMPORARY TABLE session.b (
id i4 not null, id2 i4 not null,a i4 not null)
ON COMMIT PRESERVE ROWS WITH NORECOVERY
;

insert into session.b values (1, 4, 1);
insert into session.b values (1, 4, 2);
insert into session.b values (1, 4, 4);

select a.id from session.a a
where 1=1 and id=1 and exists (
select b.id2 from session.b b
where a.id=b.id
and a.id=1
group by b.id2
having max(b.a) != count(b.a)
or min(b.a) != 1
or count(b.a) != count(distinct b.a)
)
\g

The query returns 1 row in 9.2 with p13612, also with 9.1.2 p13585 - at
least on Solaris...


--
denjo02

Hi Piotr,

I get zero rows on:
II 9.0.4 (a64.lnx/105)NPTL + p12479
II 9.0.4 (a64.lnx/105)NPTL + p12707

I get 1 row on:
II 9.2.0 (a64.lnx/143)NPTL + p13556,
II 9.2.0 (a64.lnx/143)NPTL unpatched,
II 9.1.1 (a64.lnx/103)NPTL +p13159

So its starting to look like a generic 9.0.4 thing.

Martin Bowes

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Wisniewski, Piotr, (ProService AT)
Sent: 02 December 2009 07:55
To: Ingres and related product discussion forum
Subject: [Info-Ingres] SQL bug

Hello,

The following SELECT statement return 0 rows instead of one
on ingres Solaris version
13351 1 II 9.0.4 (a64.sol/105)

What can be the cause of that and does the same error occur on other versions ?
Note that I get the correct result if I either change
" max(b.a) != count(b.a)" to " max(b.a) != count(*)"
or reduce the having clause to " max(b.a) != count(b.a)"



DECLARE GLOBAL TEMPORARY TABLE session.a (id i4 not null)
ON COMMIT PRESERVE ROWS WITH NORECOVERY
;
insert into session.a values (1);
insert into session.a values (2);
insert into session.a values (3);


DECLARE GLOBAL TEMPORARY TABLE session.b (
id i4 not null, id2 i4 not null,a i4 not null)
ON COMMIT PRESERVE ROWS WITH NORECOVERY
;

insert into session.b values (1, 4, 1);
insert into session.b values (1, 4, 2);
insert into session.b values (1, 4, 4);

select a.id from session.a a
where 1=1 and id=1 and exists (
select b.id2 from session.b b
where a.id=b.id
and a.id=1
group by b.id2
having max(b.a) != count(b.a)
or min(b.a) != 1
or count(b.a) != count(distinct b.a)
)
\g


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

This looks like known bug 114008:

114008
(GENERIC)
Query with count distinct, count(*) and having clause gives
incorrect
result.

This is fixed at 9.1 onwards. The fix was not crossed into 9.0.


--
smeke01