crash in query which contains subselect

[Michal Seliga]

i am using sybase sql anywhere 10.0.1.3931
crash happens on both windows mobile (c++ application based on
ultralite) and windows xp (using dbisql connection to ultralite
database)

see following commands:

--error if they don't exist, just continue
drop table testmaster;
drop table testslave;

create table testmaster(masterid integer, masterval varchar(100),
primary key(masterid));
create table testslave(slaveid integer, masterid integer, slaveval
varchar(100), primary key(slaveid));
create table testslave2(slave2id integer, masterid integer, slave2val
varchar(100), primary key(slave2id));

insert into testmaster values (1,'test1');
insert into testslave values (1,1,'test1slave_1');
insert into testslave values (2,1,'test1slave_2');
insert into testslave2 values (1,1,'test1slave2_1');
insert into testslave2 values (2,1,'test1slave2_2');

select testmaster.masterid, slave.id, slave.val
from testmaster
left outer join (select first slaveid,slaveval
from testslave
where masterid=testmaster.masterid
order by slaveval asc
) as slave(id,val) on 1=1
left outer join testslave2 on
testslave2.masterid=testmaster.masterid

this will cause crash. dbisql says just 'connection was terminated'

this is not real world example, but its minimal set of tables and
conditions which cause error. in real application there is huge amount
of selects which are based on this schema and most of them is
generated. in their case dbisql reports this error, or its -309
(Memory error -- transaction rolled back), and in one case it also
runs select once without any problem and then on second try hangs
forever

it looks like some internal ultralite engine error to me.... i admit
that this select doesn't look very nice, but this is what we have to
work with

btw, just for your information, all this worked without any reported
error with ultralite from asa 9.0.2. problems started only when we
started to work on sql anywhere 10 version

could someone please check provided example and give me any ideas how
to solve this?

[Michal Seliga]

one more note. removing of this part

left outer join testslave2 on
testslave2.masterid=testmaster.masterid

will make this work corectly without crash. it seems that difficult
left outer join with subselect makes conflict with left outer joins
which appears after it

[Michal Seliga]

and one more note. it crashes with sql anywhere 12.0.0.2483 too

database created like this:
"%SQLANY12%\bin32\ulinit" -p "4096" -S "0" --max_hash_size=4 --
timestamp_increment=1 --utf8_encoding=off -y P:\Projekty\asa10help
\empty\empty.udb
"%SQLANY12%\bin32\dbisql" -ul -c "DBF=P:\Projekty\asa10help\empty
\empty.udb" test.sql

test.sql is:

create table testmaster(masterid integer, masterval varchar(100),
primary key(masterid));
create table testslave(slaveid integer, masterid integer, slaveval
varchar(100), primary key(slaveid));
create table testslave2(slave2id integer, masterid integer, slave2val
varchar(100), primary key(slave2id));

insert into testmaster values (1,'test1');
insert into testslave values (1,1,'test1slave_1');
insert into testslave values (2,1,'test1slave_2');
insert into testslave2 values (1,1,'test1slave2_1');
insert into testslave2 values (2,1,'test1slave2_2');

select testmaster.masterid, slave.id, slave.val
from testmaster
left outer join (select first slaveid,slaveval
from testslave
where masterid=testmaster.masterid
order by slaveval asc
) as slave(id,val) on 1=1