Re: Ambiguous column name in order by statement

[Thomas Mueller]

Hi,

According to my test, the test case works for HSQLDB and SQLite. It fails for PostgreSQL, Apache Derby, and MySQL:

drop table test;

create table test(id int);

insert into test values(1);

select * from test a, test b order by id;

So, if I change the behavior, H2 will be incompatible with SQLite and HSQLDB.

Could you tell me why it is important for you that the statement fails?

Regards,

Thomas

On Tue, Aug 7, 2012 at 9:57 AM, Niko Paltzer <nik...@gmail.com> wrote:

Hi,

please consider the following setup:

create table table_1 (
field_1 number(10)
);

create table table_2 (
field_1 number(10)
);

insert into table_1 values (1);
insert into table_1 values (2);

insert into table_2 values (3);
insert into table_2 values (4);

The following statement orders the elements according to the column in table_1:

select * from table_1, table_2
order by field_1;

Oracle 10/11 will raise an error (ORA-00918) that the column is ambiguously defined.

Is it possible to configure the H2 such that it behaves like Oracle, i.e. not accepting the select statement?

Thank you in advance.

Best regards, Niko

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/A84Z7a9Fp_cJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Niko Paltzer]

Hi Thomas,

thank you for the quick reply.

We use the H2 for testing because it is very convenient. But in the end, all the tests are also executed against an Oracle database.

I understand that you can't change the default behaviour but maybe you can integrate it into the Oracle-compatibility-mode.

Best regards, Niko