Inexplicable sql Union behaviour
nikosv
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' !
Ian Kirkham
eliminated rows in the original union.
Try running the union with UNION ALL instead and if I am correct there
will be at least two more rows in the result. These rows will have been
removed as duplicates (UNION is by default UNION DISTINCT) and the
actual records will only differ in columns that have not been selected
in the union. Adding the extra columns in the second query highlighted
differences in the records that stopped them being eliminated as
duplicates.
Regards,
Ian
Hash Join(last_aa)
Heap
Pages 1 Tups 3
D22 C7
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
nikosv
> What may be happening is the extra columns might reveal that you have
> eliminated rows in the original union.
> Try running the union with UNION ALL instead and if I am correct there
> will be at least two more rows in the result. These rows will have been
> removed as duplicates (UNION is by default UNION DISTINCT) and the
> actual records will only differ in columns that have not been selected
> in the union. Adding the extra columns in the second query highlighted
> differences in the records that stopped them being eliminated as
> duplicates.
> Regards,
> Ian
>
> -----Original Message-----
>
> [mailto:info-ingres-boun...@kettleriverconsulting.com] On Behalf Of
> nikosv
> Sent: 22 July 2010 09:33
>
> read more »
Hi Ian,
that was my initial thought as well but the UNION ALL revealed 9 rows.
Running UNION without ALL does indeed remove duplicates and from the 9
rows it returns 5 which is still incorrect; while if you run the two
queries in isolation or without the extra field the row summing is 3.
When using the extra field it looks like it totaly disrecards the
explicit instruction to use the index value last_aa = 25 and gets
the extra identical rows from last_aa = 28.
So when UNION-ed I get 1 row from the first select and 4 (2 from
last_aa=25 and another 2 from last_aa=28) rows from the second which
gives a total of 5 rows back
when UNION-ed ALL I get 1 row from the first select and 8 (4 from
last_aa=25 and another 4 from last_aa=28) which gives a total of 9
rows back.But each last_aa actually contains 2 rows not 4
If the union was running as expected it would only use last_aa=25 and
return 2 rows from it plus one from the first part of the union,a
total of 3
The additional issue is that the same pattern that gives extra rows is
repeated with other data/queries that do not include the extra field.
nikosv
> eliminated rows in the original union.
> Try running the union with UNION ALL instead and if I am correct there
> will be at least two more rows in the result. These rows will have been
> removed as duplicates (UNION is by default UNION DISTINCT) and the
> actual records will only differ in columns that have not been selected
> in the union. Adding the extra columns in the second query highlighted
> differences in the records that stopped them being eliminated as
> duplicates.
> Regards,
> Ian
>
> -----Original Message-----
>
> [mailto:info-ingres-boun...@kettleriverconsulting.com] On Behalf Of
> nikosv
> Sent: 22 July 2010 09:33
Ingres Forums
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/ingres-database.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
------------------------------------------------------------------------
hanal04's Profile: http://community.ingres.com/forum/member.php?userid=443
View this thread: http://community.ingres.com/forum/showthread.php?t=12356
nikosv
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.