Alexey Naidyonov wrote:
>
> It seems there is a weird bug somewhere in query
> parser. Please check
> following script:
>
> >---
> drop table test_bug
> //
> drop table test_bug1
> //
> create table test_bug (
> id integer, val varchar(100), tstamp timestamp
> )
> //
> create table test_bug1(
> id integer, tstamp timestamp
> )
> //
> select t_id, t_val from (
> select a.id as t_id, a.val as t_val
> from test_bug a
> where a.tstamp = (
> select max(b.tstamp) from test_bug1 b where a.id=b.id)
> and a.id > 10 and a.id < 20
> ) order by t_val
works, result the correct results
> select t_id, t_val from (
> select a.id as t_id, a.val as t_val
> from test_bug a
> where a.tstamp = (
> select max(b.tstamp) from test_bug1 b where a.id=b.id)
> and a.id > 10 and a.id < 20
> union
> select a.id as t_id, a.val as t_val
> from test_bug a
> where a.tstamp = (
> select max(b.tstamp) from test_bug1 b where a.id=b.id)
> and a.id > 20 and a.id < 30
> ) order by t_val
results in -8010 which should not happen, because it can be handled
as correlated subquery
will be corrected in the next kernel version
> select t_id, t_val from (
> select a.id as t_id, a.val as t_val
> from test_bug a
> where a.tstamp = (
> select max(b.tstamp) from test_bug1 b)
> and a.id > 10 and a.id < 20
> union
> select a.id as t_id, a.val as t_val
> from test_bug a
> where a.tstamp = (
> select max(b.tstamp) from test_bug1 b where a.id=b.id)
> and a.id > 20 and a.id < 30
> ) order by t_val
this works correctly
>
> As you can see, first query works fine, but second
> fails with an error
> -8010 (Table name must be in from list) at the first occurance of
> "a.id=b.id". More mysteriously, query will work again with first
> occurance of "where a.id=b.id" removed (see third query). All three
> queries work fine in Oracle.
with the next kernel version even the second one should work
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
