SQL Server Mobile Bug?
We've migrated a .NET CF 1.0 App with SQL CE 2.0 to .NET CF 2.0 and SQL Server Mobile and we were wondering about the new behavior. A sample to reproduce:
create table persons ( id int identity primary key,
firstname nvarchar(100),
lastname nvarchar(100));
create table logins ( id int identity primary key,
personid int not null,
password nvarchar(100));
insert into persons(firstname, lastname) values ('Scott', 'Tiger');
insert into persons(firstname, lastname) values ('Bill', 'Gates');
insert into persons(firstname, lastname) values ('Test', 'Man');
insert into logins(personid, password) values (2, 'bill');
insert into logins(personid, password) values (1, 'tiger');
The Query
select p.firstname, p.lastname from persons p inner join logins l on p.id = l.personid
returns as expected
Scott Tiger
Bill Gates
But when I'm trying to sort the result with "order by p.lastname, p.firstname":
select p.firstname, p.lastname from persons p inner join logins l on p.id = l.personid order by p.lastname, p.firstname
I'm getting
Bill Gates
Bill Gates
Test Man
Test Man
Scott Tiger
Scott Tiger
I've tested with 3.0.5206.0 (included in VS 2005), 3.0.5207.0 (included in SQL Server 2005 Dev. Ed.) and 3.0.5214.0 (just released SQL Server Mobile SDK -http://www.microsoft.com/downloads/details.aspx?FamilyId=5BD8ABAA-5813-4DB3-B23A-24551DE2ECC1&displaylang=en
)
The bug also appears when I'm adding referential integrity between the two tables:
create table persons ( id int identity primary key,
firstname nvarchar(100),
lastname nvarchar(100));
create table logins ( id int identity primary key,
personid int references persons(id),
password nvarchar(100));
Can you confirm and how do I inform Microsoft?
