Re: Distinct subselect bug?

[Knut Anders Hatlen <Kn...@Sun.COM>]

Hi,

I browsed the derby user archives and didn't see any responses to your
(very old) article. This is definitely a bug and should be filed in
Jira. I wasn't able to find a Jira issue for this particular bug, so
I'll look into it tomorrow and file an issue if no one points out
something obvious that I have missed.

For the record: I can verify that the reported behaviour also is
present in the latest development version of Derby.

-- 
Knut Anders

Andrew Shuttlewood <an...@futureroute.co.uk> writes:

> While trying out some queries recently, we came across some weird
> issues.
>
> We sometimes work off of queries that are provided by the user, so we
> heavily use subselects. This is a recent one
>
> select distinct(col1) from (select col1,col2,col3,col4 from table) t
>
> Interestingly enough, this doesn't actually work.
>
> I've attached a sample CSV file to demonstrate some sample data.
>
> With this query
>
> select distinct name from (select name,ID from names) n
>
> And a schema of ID:string, Name: String
>
> I get
>
> "Carl"
> "Anna"
> "Julian"
> "Julian"
> "Julian"
> "Andrew"
> "Andrew"
> "Andrew"
> "Ben"
> "George"
> "George"
> "George"
> etc.
>
> if I omit the other column (ie, 'select distinct name from (select name
> from names) n' then it works fine, and it also works okay if I flatten
> the query.
>
> It ALSO works okay if I do an ORDER BY on the top entry. ie
>
> select distinct name from (select name,ID from names) n ORDER BY name
>
> I assume this is a bug?
> names.csv: attached