[Info-Ingres] SQL bug

[Wiśniewski, Piotr, (ProService AT)]

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

[Ingres Forums]

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

--
denjo02
------------------------------------------------------------------------
denjo02's Profile: http://community.ingres.com/forum/member.php?userid=702
View this thread: http://community.ingres.com/forum/showthread.php?t=11429

[Martin Bowes]

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

Hello,

_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Ingres Forums]

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
------------------------------------------------------------------------
smeke01's Profile: http://community.ingres.com/forum/member.php?userid=1015