Hi All,

I have installed Firebird RC1 on FreeBSD 4.4, and during testing of a
number of existing applications I uncovered a problem with some SQL syntax
that works in IB 5.6, but not in FB RC1.

I have simplified the problem down to a degenerate case;

create table test1 (id integer);
create table test2 (id integer);

select id from test1
left join test2 on test2.id = test1.id
order by id;

Interbase 5.6 returns an empty result set, while FB returns the following
error;

Dynamic SQL Error
-SQL error code = -204
-internal gds software consistency check (table/view TEST2)
-ID

It appears that the "order by id" is causing it to get confused.

The following query does work, as expected;

select id from test1
left join test2 on test2.id = test1.id
order by test1.id;


However, the following query returns the same error as the first one;

select id from test1
left join test2 on test2.id = test1.id
order by 1;

which seems wrong to me - why isn't it allowing the "order by 1" to specify
the first column in the result set?

I don't know if this is a bug in the parser or not. I understand that the
fully-specified query is "the most correct", however I don't see what is
wrong with the last query.

Perhaps this bug has already been submitted to Firebird?


Regards,

Chris Heberle
Intouch Technology
http://www.intouch-usa.com
USA: 805 682 9999
Australia: 03 5754 1169