#8728: Errors with the Union Clause in mongodb

tariqrahiman reported 2013-04-24T01:31:51Z · last modified 2013-07-09T22:48:06Z

Errors with the Union Clause in mongodb

Dev	emil.goicovici
QA	JennyNishimura

Priority	Low
Complexity	Unknown

Component	MongoSQL
Version	14.0

Version: 14.0-dev-41

Build #: 32003

Build Date: 2013-Apr-19 02:57:23 PM

Operating Environment: Mac OS X (10.8.2, x86_64) / MacRoman / en / US / Apple Inc. 1.6.0_41-b02-445-11M4107

Error Messages with the Union Clause in mongodb

1. First query executes correctly

2. Based on the first query, i added union inside the () and get Xꐇᡧꂭꀘ not supported as subquery. I dont understand what Xꐇᡧꂭꀘ is

3. The 3rd query is same as 4rth and 5th, but gives Select statement has different number of rows. If i do select JSON * from (SELECT JSON * FROM baseball ) as table i dont see the _id field

4. The 4rth and 5th query gives (Bad Key: 'managerName.first') which seems to be correct, I added these to show the difference in the 3rd query

select city a, teamName from baseball

union

select * from ( SELECT city a, teamName FROM baseball) as table

select * from baseball

union

select * from ( SELECT * FROM baseball union SELECT * FROM baseball) as table

select JSON * from baseball

union all

select JSON * from (SELECT JSON * FROM baseball ) as table

select * from baseball

union all

select * from (SELECT * FROM baseball ) as table

select Flatten_array * from baseball

union all

select Flatten_array * from (select Flatten_array * from baseball ) as table

All Comments (6) Change History

emil.goicovici 2013-04-25T07:05:37Z

All the five queries work fine now.

tariqrahiman 2013-05-01T22:16:44Z

Verified in 14.0-dev-48

slavakiev 2013-06-16T12:21:08Z

Tested on Aqua Data Studio 14.0.0-beta-23 Build #: 32874 on Ubuntu 12.04( Mongo DB 2.4.3) and Windows XP ( Mongo DB 2.0.9).

Statements 2,4,5 give errors: 2,4 - "different number of columns" and 5- "dupplicate keys"

emil.goicovici 2013-06-20T18:48:07Z

Fixed both cases.

slavakiev 2013-06-25T19:16:46Z

Tested on Aqua Data Studio 14.0.0-beta-33 Build #: 32981 on Ubuntu 12.04( Mongo DB 2.4.3) and Windows XP ( Mongo DB 2.0.9).

I had a test case which caused "different number of columns". It is due to not standard type of _id column. I prepare test scripset:

insert into unioncoll values({_id:{"right":1,"left":2}})

select * from unioncoll

union

select * from ( SELECT * FROM unioncoll)

--The SELECT statements have a different number of columns

-- parts of the query select nexts:

select * from unioncoll

--_id.right _id.left

--1 2

--Error in next statement

select * from(SELECT * FROM unioncoll)

--_id

--(null)

emil.goicovici 2013-06-26T17:47:06Z

These queries become invalid because the support for sub-queries on the FROM clause has been disabled (see issue #9420).

Search Tips

Aqua Data Studio / nhilam

Errors with the Union Clause in mongodb

Issue #8728

Completion

1 issue link

Issue #9420

Search Tips

Aqua Data Studio / nhilam

Title

Errors with the Union Clause in mongodb

Issue #8728

Completion

1 issue link

Issue #9420