I have two queries which when run standalone produce the correct
results but when unioned produce extra rows.

query 1 :
select aa_qeshs= leylq.aa_qeshs, lektiko = leylq.lektiko,
aa_grammhs = 0, c_ylikoy = '',
ps_syskeya = 0, n_ylikoy = '',
periektiko = '', c_mon_metr = '',
aa_symb = 0, etos = '',
timh_symb = 0.0, pt_fpa = 0.0,
pt_ekpt = 0.0, timh_met = 0.0,
pt_fpa_met = 0.0, c_id_ypogr = '',
d_enarxhs = '',
d_lhxhs = '',
n_eid_kat = '',
am_ypal = 0, epwnymo = '',
onoma = '', n_id_ypogr = '',
telefax = '', thlef_1 = '',
c_rec = '2'
from leylq
where leylq.kwd_repo = 'ylpa30550r'
and
leylq.c_entyp = '504'

(1 row)
End of Request

query 2 :
select aa_qeshs = 0, lektiko = '',
aa_grammhs = dylpa.aa_grammhs, c_ylikoy =
dylpa.c_ylikoy,
ps_syskeya = dylpa.ps_syskeya, n_ylikoy =
mylik.n_ylikoy,
periektiko = sylik.periektiko, c_mon_metr =
mylik.c_mon_metr,
aa_symb = ylpro.aa_symb, etos = ylpro.etos,
timh_symb = ylpro.timh_symb, pt_fpa =
ylpro.pt_fpa,
pt_ekpt = ylpro.pt_ekpt, timh_met =
ylpro.timh_met,
pt_fpa_met = ylpro.pt_fpa_met, c_id_ypogr = '',
d_enarxhs = ylpro.d_enarxhs, d_lhxhs =
ylpro.d_lhxhs,
n_eid_kat = ylpro.n_eid_kat,
am_ypal = 0, epwnymo = '',
onoma = '', n_id_ypogr = '',
telefax = mprom.telefax, thlef_1 =
mprom.thlef_1,
c_rec = '3'
from dylpa,mylik,ylpro,sylik,mprom,hylpa
where hylpa.c_entyp = '504' and
hylpa.last_year = '1/1/2010' and
hylpa.last_aa = 25 and
mprom.c_prom = hylpa.c_prom and
dylpa.c_entyp = hylpa.c_entyp and
dylpa.last_year = dylpa.last_year and
dylpa.last_aa = hylpa.last_aa and
mylik.c_ylikoy = dylpa.c_ylikoy and
ylpro.c_prom = '999056200' and
ylpro.c_prom = mprom.c_prom and
ylpro.c_ylikoy = dylpa.c_ylikoy and
ylpro.aa_syskeya = dylpa.aa_syskeya and
ylpro.etos = dylpa.etos and
ylpro.aa_symb = dylpa.aa_symb and
sylik.c_ylikoy = dylpa.c_ylikoy and
sylik.aa_syskeya = dylpa.aa_syskeya

(2 rows)
End of Request

Unioned produce the correct result:
select aa_qeshs= leylq.aa_qeshs, lektiko = leylq.lektiko,
aa_grammhs = 0, c_ylikoy = '',
ps_syskeya = 0, n_ylikoy = '',
periektiko = '', c_mon_metr = '',
aa_symb = 0, etos = '',
timh_symb = 0.0, pt_fpa = 0.0,
pt_ekpt = 0.0, timh_met = 0.0,
pt_fpa_met = 0.0, c_id_ypogr = '',
d_enarxhs =
'',
d_lhxhs = '',
n_eid_kat =
'',
am_ypal = 0, epwnymo = '',
onoma = '', n_id_ypogr = '',
telefax = '', thlef_1 = '',
c_rec = '2'
from leylq
where leylq.kwd_repo = 'ylpa30550r'
and
leylq.c_entyp = '504'
union
select aa_qeshs = 0, lektiko = '',
aa_grammhs = dylpa.aa_grammhs, c_ylikoy =
dylpa.c_ylikoy,
ps_syskeya = dylpa.ps_syskeya, n_ylikoy =
mylik.n_ylikoy,
periektiko = sylik.periektiko, c_mon_metr =
mylik.c_mon_metr,
aa_symb = ylpro.aa_symb, etos = ylpro.etos,
timh_symb = ylpro.timh_symb, pt_fpa =
ylpro.pt_fpa,
pt_ekpt = ylpro.pt_ekpt, timh_met =
ylpro.timh_met,
pt_fpa_met = ylpro.pt_fpa_met, c_id_ypogr = '',
d_enarxhs = ylpro.d_enarxhs, d_lhxhs =
ylpro.d_lhxhs,
n_eid_kat = ylpro.n_eid_kat,
am_ypal = 0, epwnymo = '',
onoma = '', n_id_ypogr = '',
telefax = mprom.telefax, thlef_1 =
mprom.thlef_1,
c_rec = '3'
from dylpa,mylik,ylpro,sylik,mprom,hylpa
where hylpa.c_entyp = '504' and
hylpa.last_year = '1/1/2010' and
hylpa.last_aa = 25 and
mprom.c_prom = hylpa.c_prom and
dylpa.c_entyp = hylpa.c_entyp and
dylpa.last_year = dylpa.last_year and
dylpa.last_aa = hylpa.last_aa and
mylik.c_ylikoy = dylpa.c_ylikoy and
ylpro.c_prom = '999056200' and
ylpro.c_prom = mprom.c_prom and
ylpro.c_ylikoy = dylpa.c_ylikoy and
ylpro.aa_syskeya = dylpa.aa_syskeya and
ylpro.etos = dylpa.etos and
ylpro.aa_symb = dylpa.aa_symb and
sylik.c_ylikoy = dylpa.c_ylikoy and
sylik.aa_syskeya = dylpa.aa_syskeya
(3 rows)
End of Request

All good, however when you add an extra field to the Union, you get 2
extra rows :
(Note the extra filed, last_aa=0 in the first query and
last_aa=dylpa.last_aa in the second)

select aa_qeshs= leylq.aa_qeshs, lektiko = leylq.lektiko,
aa_grammhs = 0, c_ylikoy = '',
ps_syskeya = 0, n_ylikoy = '',
periektiko = '', c_mon_metr = '',
aa_symb = 0, etos = '',
timh_symb = 0.0, pt_fpa = 0.0,
pt_ekpt = 0.0, timh_met = 0.0,
pt_fpa_met = 0.0, c_id_ypogr = '',
d_enarxhs =
'',
d_lhxhs = '',
n_eid_kat =
'',
am_ypal = 0, epwnymo = '',
onoma = '', n_id_ypogr = '',
telefax = '', thlef_1 = '',
c_rec = '2' , last_aa=0
from leylq
where leylq.kwd_repo = 'ylpa30550r'
and
leylq.c_entyp = '504'
union
select aa_qeshs = 0, lektiko = '',
aa_grammhs = dylpa.aa_grammhs, c_ylikoy =
dylpa.c_ylikoy,
ps_syskeya = dylpa.ps_syskeya, n_ylikoy = mylik.n_ylikoy,
periektiko = sylik.periektiko, c_mon_metr =
mylik.c_mon_metr,
aa_symb = ylpro.aa_symb, etos =
ylpro.etos,
timh_symb = ylpro.timh_symb, pt_fpa =
ylpro.pt_fpa,
pt_ekpt = ylpro.pt_ekpt, timh_met =
ylpro.timh_met,
pt_fpa_met = ylpro.pt_fpa_met, c_id_ypogr = '',
d_enarxhs = ylpro.d_enarxhs, d_lhxhs =
ylpro.d_lhxhs,
n_eid_kat = ylpro.n_eid_kat,
am_ypal = 0, epwnymo = '',
onoma = '', n_id_ypogr = '',
telefax = mprom.telefax, thlef_1 =
mprom.thlef_1,
c_rec = '3' ,last_aa=dylpa.last_aa
from dylpa,mylik,ylpro,sylik,mprom,hylpa
where hylpa.c_entyp = '504' and
hylpa.last_year = '1/1/2010' and
hylpa.last_aa = 25 and
mprom.c_prom = hylpa.c_prom and
dylpa.c_entyp = hylpa.c_entyp and
dylpa.last_year = dylpa.last_year and
dylpa.last_aa = hylpa.last_aa and
mylik.c_ylikoy = dylpa.c_ylikoy and
ylpro.c_prom = '999056200' and
ylpro.c_prom = mprom.c_prom and
ylpro.c_ylikoy = dylpa.c_ylikoy and
ylpro.aa_syskeya = dylpa.aa_syskeya and
ylpro.etos = dylpa.etos and
ylpro.aa_symb = dylpa.aa_symb and
sylik.c_ylikoy = dylpa.c_ylikoy and
sylik.aa_syskeya = dylpa.aa_syskeya

(5 rows)
End of Request

It returns 2 extra rows.

The query plans of both unions are identical.Even the total number of
rows throughout the Query Plan returned are the same but most
importantly the top node returns 3 tups.This number is the correct one
and is consistent accross both queries:

QUERY PLAN 2,3, no timeout, of union
T7

Hash Join(last_aa)

Heap

Pages 1 Tups 3

D22 C7

however the second query returns 5 rows out of the blue.....

That is one part of the story.The same behaviour is observed in other
queries where there is no extra field added like in the sample
above;they return extra rows without any modification of the query
(i.e adding extra fields)

System specifications where it is observed :
HP-UX ia64
II 9.1.2 (i64.hpu/100)

Any help greatly appreciated because I've been banging my head for a
couple of hours trying to pinpoint the 'leak' !

Hiya

If this is an Enterprise installation with a support contract please
log an issue with Level 1. If not you may want to try the evaluation
edition of ingres-9.2.0-143-com-hp-hpux-ia64-32-64bit.tar found on
'Downloads | Ingres Open Source Database - Ingres'
(http://www.ingres.com/downloads/ingr...e.php#ingcomed) to see if
this is a problem that has been fixed in a later release. Alternatively
you could try the subversion source download and build to see if this is
fixed in head revision code.

Regards

Alex


--
hanal04

thanks I'll ckeck it out
Is there a publicly available/searchable list of known issues/bugs and
patch contents/bug fixes available that I can use for future refrence?
I think this kind of functionallity is available with a service desk
account but what about without one?
(I'd like to check it myself and guide the client's tech support with
an accurate description of what is needed)
It would be good to have all that kind of functionality available
publicly even without having the functionality of logging an issue.