Distinct Problem

[GoogleCodeExporter]

I've this very simple table

    CREATE TABLE employee (
      id BIGINT NOT NULL,
      code_one VARCHAR(10),
      code_two VARCHAR(12),
      PRIMARY KEY (id)
    );

And, lets say I would like to return the code_one and code_two ordered by the 
sum of these codes, for example

    select distinct e.code_one, e.code_two 
        from employee as e 
    order by (coalesce(convert(e.code_one, BIGINT),0) + coalesce(convert(e.code_two, BIGINT),0))

However, whenever I execute this SQL I get the following error:

    Order by expression "(COALESCE(CONVERT(E.CODE_ONE,BIGINT), 0) + COALESCE(CONVERT(E.CODE_TWO,BIGINT), 0))" must be in the result list in this case; SQL statement:
    select distinct e.code_one, e.code_two 
    from employee as e 
    order by (coalesce(convert(e.code_one, BIGINT),0) + coalesce(convert(e.code_two, BIGINT),0)) [90068-168] 90068/90068

It is important to say that the same problem DOES NOT happen in 
Oracle/MySQL/HSQLDB/etc databases.

I would appreciate if you could fix this issue.
Thanks,
Mateus M. da Costa

Original issue reported on code.google.com by mmdcosta on 22 Apr 2013 at 5:27

[GoogleCodeExporter]

from the code:

  /**
     * The error with code <code>90068</code> is thrown when the given
     * expression that is used in the ORDER BY is not in the result list. This
     * is required for distinct queries, otherwise the result would be
     * ambiguous.
     * Example of wrong usage:
     * <pre>
     * CREATE TABLE TEST(ID INT, NAME VARCHAR);
     * INSERT INTO TEST VALUES(2, 'Hello'), (1, 'Hello');
     * SELECT DISTINCT NAME FROM TEST ORDER BY ID;
     * Order by expression ID must be in the result list in this case
     * </pre>
     * Correct:
     * <pre>
     * SELECT DISTINCT ID, NAME FROM TEST ORDER BY ID;
     * </pre>
     */

Original comment by noelgrandin on 6 May 2013 at 1:56

• Changed state: Invalid

[GoogleCodeExporter]

Ok,
My query would be ambiguous. However if I include "id" in the result list the 
result won't be ambiguous, but the i'll have the same error.

INSERT INTO employee VALUES (1,1,1)
INSERT INTO employee VALUES (2,1,1)

select e.id, e.code_one, e.code_two 
        from employee as e 
    order by (coalesce(convert(e.code_one, BIGINT),0) + coalesce(convert(e.code_two, BIGINT),0))

Thanks,
Mateus M. da Costa

Original comment by mmdcosta on 6 May 2013 at 7:52

[GoogleCodeExporter]

> but the i'll have the same error.

The query you provided in your last comment works for me.

Original comment by thomas.t...@gmail.com on 7 May 2013 at 5:00

[GoogleCodeExporter]

Sorry, I forgot the distinct clause.

The result won't be ambiguous, but the i'll have the same error.

select distinct  e.id, e.code_one, e.code_two 
        from employee as e 
    order by (coalesce(convert(e.code_one, BIGINT),0) + coalesce(convert(e.code_two, BIGINT),0))

Thanks,
Mateus M. da Costa

Original comment by mmdcosta on 7 May 2013 at 11:31

[GoogleCodeExporter]

For the following query, PostgreSQL also throws an exception. Instead of 
logging an issue if you are not sure if it really is an issue, I suggest to use 
StackOverflow or the H2 Google Group instead.

drop table employee;
CREATE TABLE employee (
      id BIGINT NOT NULL,
      code_one VARCHAR(10),
      code_two VARCHAR(12),
      PRIMARY KEY (id)
    );
INSERT INTO employee VALUES (1,1,1);
INSERT INTO employee VALUES (2,1,1);

select e.id, e.code_one, e.code_two 
        from employee as e 
    order by (coalesce(cast(e.code_one as bigint),0) + coalesce(cast(e.code_two as bigint),0));

select distinct  e.id, e.code_one, e.code_two 
        from employee as e 
    order by (coalesce(cast(e.code_one as bigint),0) + coalesce(cast(e.code_two as bigint),0));
-- fails in most databases

Original comment by thomas.t...@gmail.com on 29 Jul 2013 at 8:24