Hi, Thanks a lot for the test case! A simpler test case is:
drop table test; create table test(id int, name varchar); insert into test values(1, 'a'); (select id from test order by id) union (select id from test order by name); Regards, Thomas On Thu, Dec 26, 2013 at 9:57 PM, Ilya Belopolsky <[email protected]>wrote: > forgot attachment in original post > > > On Tuesday, December 24, 2013 12:24:05 PM UTC-5, Ilya Belopolsky wrote: >> >> When I execute the following command: >> >> (SELECT employee_id FROM employees ORDER BY employee_id) >> >> union >> >> (select employee_id FROM job_history ORDER BY job_id); >> >> >> I have attached a sql script to reproduce the schema, tables, and data >> >> >> The result set contains duplicates as if I used the "union all" command. >> Even if I use "union disctinct" explicitly, i get duplicates. >> >> >> The issue is related to the ORDER BY clause in the second select. The issue >> goes away if I use the column in the select clause (employee_id) as the >> order by column, but the same issue is present with any other column in the >> order clause. >> >> >> The issue is present regardless of the position of the Select subquery. >> >> >> Is this a bug or am I missing something? >> >> -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
