Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
*URGENT* BUG - SELECT with UNION is actually removing rows! |
Tue, Jun 22 2010 9:09 AM | Permanent Link |
Hedley Muscroft | Here's the test database to download :-
http://www.pioneersoftware.co.uk/files/temp/db.7z If you open the database in EDB Manager (UNICODE) and execute the following SQL :- ----------------- select allocdatetime as datetime, pay_id, cast(null as bigint) as credit_id, inv.staff_id, alloc.per_id, pay.clinic_id, paymethod.name as paymentmethod, alloc.amount, ('Payment #' || cast(pay.paynum as varchar(30)) || ' received on ' || cast(cast(pay.datetime as date) as varchar(30)) || ' for ' || cast(cast(pay.amount as numeric(999,2)) as varchar(30)) || ' [' || paymethod.name || '] taken by ' || staff.knownas) as description from alloc inner join inv on (alloc.inv_id=inv.id) inner join pay on (alloc.pay_id=pay.id) inner join per on (pay.per_id=per.id) inner join paymethod on (pay.paymethod_id=paymethod.id) left join staff on (pay.createdby_staff_id=staff.id) where per_id=1000000010000000024 ----------------- Not that it's important, but this gets PAYMENT -> INVOICE allocations for a specific client. You'll see it returns THREE rows. Now execute this SQL :- ----------------- select credit.datetime, cast(null as bigint) as pay_id, credit.id as credit_id, credit.staff_id, credit.per_id, credit.clinic_id, 'Credit Note' as paymentmethod, (credit.total * -1) as amount, ('Credit #' || cast(credit.creditnum as varchar(30)) || ' issued on ' || cast(cast(credit.datetime as date) as varchar(30)) || ' for ' || cast(cast(credit.total as numeric(999,2)) as varchar(30)) || ' issued by ' || staff.knownas) as description from credit inner join per on (credit.per_id=per.id) left join staff on (credit.createdby_staff_id=staff.id) where per_id=1000000010000000024 ----------------- The above gets CREDIT notes for the same client. This returns ZERO rows. Now UNION the two :- ----------------- select allocdatetime as datetime, pay_id, cast(null as bigint) as credit_id, inv.staff_id, alloc.per_id, pay.clinic_id, paymethod.name as paymentmethod, alloc.amount, ('Payment #' || cast(pay.paynum as varchar(30)) || ' received on ' || cast(cast(pay.datetime as date) as varchar(30)) || ' for ' || cast(cast(pay.amount as numeric(999,2)) as varchar(30)) || ' [' || paymethod.name || '] taken by ' || staff.knownas) as description from alloc inner join inv on (alloc.inv_id=inv.id) inner join pay on (alloc.pay_id=pay.id) inner join per on (pay.per_id=per.id) inner join paymethod on (pay.paymethod_id=paymethod.id) left join staff on (pay.createdby_staff_id=staff.id) where per_id=1000000010000000024 UNION select credit.datetime, cast(null as bigint) as pay_id, credit.id as credit_id, credit.staff_id, credit.per_id, credit.clinic_id, 'Credit Note' as paymentmethod, (credit.total * -1) as amount, ('Credit #' || cast(credit.creditnum as varchar(30)) || ' issued on ' || cast(cast(credit.datetime as date) as varchar(30)) || ' for ' || cast(cast(credit.total as numeric(999,2)) as varchar(30)) || ' issued by ' || staff.knownas) as description from credit inner join per on (credit.per_id=per.id) left join staff on (credit.createdby_staff_id=staff.id) where per_id=1000000010000000024 ----------------- Alternatively, there's a VIEW in the database called "view_staffalloc" which does the same as the UNIONed query :- ----------------- select * from view_staffalloc where per_id=1000000010000000024 ----------------- Now, I'm pretty sure that the UNIONed query should return THREE rows - but it doesn't - it returns TWO rows! The UNION removes a row! This is CRITICAL because it's messing up my clients financial reports. Tim - I really hope you can implement a quick fix please! Kind Regards, Hedley PS: EDB 2.03 Build 13 |
Tue, Jun 22 2010 9:40 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Hedley,
<< Now, I'm pretty sure that the UNIONed query should return THREE rows - but it doesn't - it returns TWO rows! The UNION removes a row! >> The issue is that the last two rows in the "alloc" query are duplicates, and EDB is removing one of the duplicates during the UNION operation. The workaround is to use UNION ALL, if possible. I'll have a fix available by later this week. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Jun 22 2010 9:50 AM | Permanent Link |
Hedley Muscroft | Well - that shows my ignorance, I wasn't even aware of UNION ALL!
Just to be clear then: Am I correct in thinking that EDB is at fault because it's incorrectly removing duplicate rows from the SAME SELECT query, whereas UNION should only remove duplicates which arise because they appear in different SELECT results? (I hope that makes sense!) |
Tue, Jun 22 2010 9:59 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Hedley,
<< Just to be clear then: Am I correct in thinking that EDB is at fault because it's incorrectly removing duplicate rows from the SAME SELECT query, whereas UNION should only remove duplicates which arise because they appear in different SELECT results? >> Actually, I was just double-checking on this, because I think that my first response was incorrect. I think that the SQL UNION clause is *supposed* to remove duplicates in all cases, thus making the EDB behavior correct, but I have to dig through the standards docs to make sure. Everything that I've checked online and with a couple of other engines here, results in *all* duplicates being removed, regardless of whether they came from the same SELECT statement or not. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Jun 22 2010 10:08 AM | Permanent Link |
Hedley Muscroft | Well, I've just checked this in PostgreSQL (which I also regularly use) and you're 100% correct - this has been entirely my mistake.
My apologies Tim - I'll go back and start re-writing all my unioned SELECTs with UNION ALL!!!! Many thanks for your help and sorry again! Kind Regards, Hedley |
Tue, Jun 22 2010 7:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Hedley,
<< Well, I've just checked this in PostgreSQL (which I also regularly use) and you're 100% correct - this has been entirely my mistake. My apologies Tim - I'll go back and start re-writing all my unioned SELECTs with UNION ALL!!!! >> No problem, I'm just happy that nothing is wrong. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |