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.
drop schema test_schema;
create schema test_schema;
set schema test_schema;
CREATE TABLE employees
( employee_id INT(6),
first_name VARCHAR(20),
last_name VARCHAR(25) NOT NULL
) ;
CREATE TABLE job_history
( employee_id INT(6) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL
) ;
INSERT INTO employees VALUES ( 101 , 'Steven', 'King' );
INSERT INTO employees VALUES ( 102, 'Neena', 'Kochhar' );
INSERT INTO employees VALUES ( 108, 'Nancy', 'Greenberg' );
INSERT INTO employees VALUES ( 109, 'Daniel', 'Faviet' );
INSERT INTO employees VALUES ( 110, 'John', 'Chen' );
INSERT INTO job_history VALUES ( 101, DATE '1993-01-13', DATE '1998-07-24', 'IT_PROG' );
INSERT INTO job_history VALUES ( 102, DATE '1989-09-21', DATE '1993-10-27', 'AC_ACCOUNT' );
INSERT INTO job_history VALUES ( 103, DATE '1993-10-28', DATE '1997-03-15', 'AC_MGR' );
INSERT INTO job_history VALUES ( 104, DATE '1996-02-17', DATE '1999-12-19', 'MK_REP' );
