 | |
Re: [Info-Ingres] Inexplicable sql Union behaviour
comp.databases.ingres comp.databases.ingres
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: [Info-Ingres] Inexplicable sql Union behaviour -
07-22-2010
, 04:40 AM
#2
| |||
| |||
|
|
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----- From: info-ingres-boun... (AT) kettleriverconsulting (DOT) com [mailto:info-ingres-boun... (AT) kettleriverconsulting (DOT) com] On Behalf Of nikosv Sent: 22 July 2010 09:33 To: info-ing... (AT) kettleriverconsulting (DOT) com Subject: [Info-Ingres] Inexplicable sql Union behaviour 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 ... read more » Hi Ian, |
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.
#3
| |||
| |||
|
|
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----- From: info-ingres-boun... (AT) kettleriverconsulting (DOT) com [mailto:info-ingres-boun... (AT) kettleriverconsulting (DOT) com] On Behalf Of nikosv Sent: 22 July 2010 09:33 To: info-ing... (AT) kettleriverconsulting (DOT) com Subject: [Info-Ingres] Inexplicable sql Union behaviour 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 ... read more » |
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.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.