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

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

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!)

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

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

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