× Heads up!

Aqua Data Studio / nhilam

Follow
IDE for Relational Databases
×
Slava Gryb
slavakiev reported 2013-10-16T17:50:22Z  · SachinPrakash last modified 2013-11-06T19:20:50Z

Issues with ORDER BY when DISTINCT in WHAT


Dev
Emil Goicovici
emil.goicovici
QA
Tariq Rahiman
tariqrahiman
Priority Low
Complexity Unknown
Component MongoSQL
Version 14.0

Tested in Aqua Data Studio 14.0.0-rc-44 Build #: 34314 on Ubuntu 12.04( Mongo DB 2.4.3)  and Windows XP ( Mongo DB 2.0.9).

Seems ORDER BY in  Distinct realized inside program by sorting result set. It needs improvements.       
 
Run next scriptset:
 
drop collection baseball
go
INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Zimmer" },
"colors": [ "blue", "gray" ],
"stats":
[
{
"year": 1904,
"wins": 100,
"mostRbis": 1000,
"grade": "A",
"battingAvg": 0.300
},
{
"year": 1987,
"wins":80,
"mostRbis":200,
"grade":"A",
"battingAvg":0.267
}
]
} )
go
INSERT INTO baseball
VALUES (
{
"teamName": "Cubs",
"city": "Luis",
"valuation": 100,
"managerName": { "first": "Dale", "last": "Sveum" },
"colors": [ "blue", "green","third" ],
"stats":
[
{
"year": 2008,
"wins": 60
 
},
{
"year": 1997,
"wins":100
}
]
} )
go
INSERT INTO baseball
VALUES (
{
"teamName": "Cubs-2",
"city": "Chicago",
"valuation": 10,
"managerName": { "first": "John", "last": "Belamy" },
"colors": [ "black", "grey" ],
"stats":
[
{
"year": 1008,
"wins": 0
 
},
{
"year": 1965,
"wins":2
}
]
} )
go
insert into baseball(valuation,city,managerName,stats,colors) values (null,null,null,null,null) 
go
insert into baseball(valuation) values (-10000) 
go
 
1. Sorting of records works wrong:
select  distinct managerName  from baseball
order by managerName  
--works wrong
go
select  managerName  from baseball
order by managerName   
-- without using distinct works correct
go
2.  When alias is in WHAT but not used in ORDER BY result is wrong:
select  distinct stats[0].year as d from baseball
order by stats[0].year 
--without using alias in order by works wrong
go
select  distinct stats[0].year from baseball
order by stats[0].year 
--without alias works correct
go
select  distinct stats[0].year as d from baseball
order by d desc
--with using alias in order by  works correct
go
3.  When record is in WHAT ORDER BY subdoc does not work
select distinct managerName from baseball
order by managerName.first  
--works wrong
go
select  managerName  from baseball
order by managerName.first desc  
--without distinct works correct
go
 
2 attachments
  •  
  •  
  • 1 share
  •  
All Comments (4) Change History
Sort 

Issue #10474
Closed
Fixed
Resolved 2013-11-06T19:20:50Z
 
 
Completion
No due date
Fixed Build ADS 14.0.3-11 (mongo-jdbc 1.3.9)
No time estimate

About AquaClusters Privacy Policy Support Version - 19.0.2-4 AquaFold, Inc Copyright © 2007-2017