#8878: union all returns distinct rows if used in derived table

Umairpk reported 2013-05-09T22:31:36Z · last modified 2013-07-15T17:27:25Z

union all returns distinct rows if used in derived table

Dev	emil.goicovici
QA	tariqrahiman

Priority	Low
Complexity	Unknown

Component	MongoSQL
Version	14.0

-------------Test script-------------

------

create collection uniontest

insert into uniontest

(id) values(1)

insert into uniontest

(id) values(1)

insert into uniontest

(id) values(1)

-------

select * from(

select * from uniontest

union

select * from uniontest)

---It returns distinct rows as it should

select * from uniontest

union all

select * from uniontest

--6 rows returned thats normal

select * from(

select * from uniontest

union all

select * from uniontest)

--3 rows returned however 6 rows should have returned because derived table property is just to return rows without applying distinct, here its behaving like union

select id from (select id from uniontest)

--It will return all 3 rows and it shows that derived table just returns the rows as it is.

All Comments (4) Change History

JennyNishimura 2013-05-10T16:04:08Z

The following query returns 6 rows.

select * from uniontest
union all
select * from uniontest

But the following query returns 3 rows.

select * from(
select * from uniontest
union all
select * from uniontest)

Emil, please check why these two queries return different result sets.

The following query returns 6 rows.

select * from uniontest
union all
select * from uniontest

But the following query returns 3 rows.

select * from(
select * from uniontest
union all
select * from uniontest)

Emil, please check why these two queries return different result sets.

emil.goicovici 2013-05-10T16:22:07Z

When using subqueries as datasource ( e.g. select * from (select .... ) ), the resultset of the inner query is saved as a temporary collection. This is required to be able to apply WHERE filters on the outer query. Because the resultset of the inner query (the UNION ALL compound select) contains _id duplicates, only 3 rows are saved on the temporary collection.

Next week I'll look for a workaround on this problem.

rkshakya 2013-07-15T06:17:18Z

Hi Emil,

Could you please inform if you are going to work on this issue further? Thanks

emil.goicovici 2013-07-15T09:58:01Z

No, because the support for subqueries (i.e. query inside the FROM clause) has been removed. SELECT ... FROM SELECT ... queries were requiring an intermediate collection for storing the result of the inner query, thus performing unexpected write operations. There were many other concerns, see issue #9420 for more details.

Search Tips

Aqua Data Studio / nhilam

union all returns distinct rows if used in derived table

Issue #8878

Completion

1 issue link

Issue #9420

Search Tips

Aqua Data Studio / nhilam

Title

union all returns distinct rows if used in derived table

Issue #8878

Completion

1 issue link

Issue #9420