MSI Query tests

[EA Durbin]

I've written some tests to debug the MSI Installer in wine, in which I have 
tested various SQL statements against the America's Army installer database 
and iterated through the queries. Here are the results.

It appears that in my attachment, where.txt, which is a standard where sql 
statement,
SELECT * FROM `Media` WHERE `LastSequence` > 1 that it returns the SQL 
statement correctly.

Then in orderby.txt i query
SELECT * FROM `Media` ORDER BY `LastSequence` and it returns the results as 
expected.

Then in compoundsql.txt I query SELECT * FROM `Media` WHERE `LastSequence` > 
1 ORDER BY `LastSequence`, which is just as the statement appears in 
InstallFiles().

In this query it ignores the WHERE CLAUSE and uses what is to the far right 
, the ORDER BY LastSequence statement, which first grabs the LastSequence of 
0.

In conclusion, the actual problem in InstallFiles() is the inability to 
handle the compound SQL statement with a WHERE and an ORDER BY together.

Unfortunately I don't understand wine's SQL underpinnings enough to where I 
can implement a solution. I've looked it over and it appears somewhat 
cryptic, so I will need some help.

[EA Durbin]

I've found a simple solution to this, not a fix for the SQL bug, but a fix 
for InstallFiles().

If you notice in where.txt, Im selecting * from media WHERE LastSequence > 
1. The default sort order for the results is by the table key of DiskId, 
which follows that of LastSequence.

Simply dropping the ORDER BY statement from the query in InstallFiles() 
causes it to return the results in order by table key of DiskId, using the 
WHERE statement where LastSequence > file->Sequence, so it never returns the 
LastSequence of 0, thus fixing the InstallFiles() bug.
> From: "EA Durbin" 
> To: [e-mail]
> CC: [e-mail]
> Subject: MSI Query tests
> Date: Thu, 01 Jun 2006 19:07:53 -0500
>
> I've written some tests to debug the MSI Installer in wine, in which I have 
> tested various SQL statements against the America's Army installer database 
> and iterated through the queries. Here are the results.
>
> It appears that in my attachment, where.txt, which is a standard where sql 
> statement,
> SELECT * FROM `Media` WHERE `LastSequence` > 1 that it returns the SQL 
> statement correctly.
>
> Then in orderby.txt i query
> SELECT * FROM `Media` ORDER BY `LastSequence` and it returns the results as 
> expected.
>
> Then in compoundsql.txt I query SELECT * FROM `Media` WHERE `LastSequence`
> > 1 ORDER BY `LastSequence`, which is just as the statement appears in
>
> InstallFiles().
>
> In this query it ignores the WHERE CLAUSE and uses what is to the far right 
> , the ORDER BY LastSequence statement, which first grabs the LastSequence 
> of 0.
>
> In conclusion, the actual problem in InstallFiles() is the inability to 
> handle the compound SQL statement with a WHERE and an ORDER BY together.
>
> Unfortunately I don't understand wine's SQL underpinnings enough to where I 
> can implement a solution. I've looked it over and it appears somewhat 
> cryptic, so I will need some help.
> << compoundsql.txt >>
> << where.txt >>
> << orderby.txt >>
>

[EA Durbin]

or not, LastSequence of 0, is being returned in DiskId of 22 further down in 
the text. which is not greater than 1, I guess the WHERE statement is 
broken, as its returning a 0, when the query is SELECT * FROM `Media` where 
`LastSequence` > 1.
> From: "EA Durbin" 
> To: [e-mail]
> CC: [e-mail]
> Subject: RE: MSI Query tests
> Date: Thu, 01 Jun 2006 19:22:31 -0500
>
> I've found a simple solution to this, not a fix for the SQL bug, but a fix 
> for InstallFiles().
>
> If you notice in where.txt, Im selecting * from media WHERE LastSequence >
> - The default sort order for the results is by the table key of DiskId,
>
> which follows that of LastSequence.
>
> Simply dropping the ORDER BY statement from the query in InstallFiles() 
> causes it to return the results in order by table key of DiskId, using the 
> WHERE statement where LastSequence > file->Sequence, so it never returns 
> the LastSequence of 0, thus fixing the InstallFiles() bug.
> > From: "EA Durbin" 
> > To: [e-mail]
> > CC: [e-mail]
> > Subject: MSI Query tests
> > Date: Thu, 01 Jun 2006 19:07:53 -0500
> >
> > I've written some tests to debug the MSI Installer in wine, in which I 
> > have tested various SQL statements against the America's Army installer 
> > database and iterated through the queries. Here are the results.
> >
> > It appears that in my attachment, where.txt, which is a standard where sql 
> > statement,
> > SELECT * FROM `Media` WHERE `LastSequence` > 1 that it returns the SQL 
> > statement correctly.
> >
> > Then in orderby.txt i query
> > SELECT * FROM `Media` ORDER BY `LastSequence` and it returns the results 
> > as expected.
> >
> > Then in compoundsql.txt I query SELECT * FROM `Media` WHERE `LastSequence`
> > > 1 ORDER BY `LastSequence`, which is just as the statement appears in
> >
> > InstallFiles().
> >
> > In this query it ignores the WHERE CLAUSE and uses what is to the far 
> > right , the ORDER BY LastSequence statement, which first grabs the 
> > LastSequence of 0.
> >
> > In conclusion, the actual problem in InstallFiles() is the inability to 
> > handle the compound SQL statement with a WHERE and an ORDER BY together.
> >
> > Unfortunately I don't understand wine's SQL underpinnings enough to where 
> > I can implement a solution. I've looked it over and it appears somewhat 
> > cryptic, so I will need some help.
> > << compoundsql.txt >>
> > << where.txt >>
> > << orderby.txt >>
> >

[Mike McCormack]

EA Durbin wrote:
> SELECT * FROM `Media` WHERE `LastSequence` > 1 that it returns the SQL 
> statement correctly.
>
> Then in orderby.txt i query
> SELECT * FROM `Media` ORDER BY `LastSequence` and it returns the results 
> as expected.
>
> Then in compoundsql.txt I query SELECT * FROM `Media` WHERE 
> `LastSequence` > 1 ORDER BY `LastSequence`, which is just as the 
> statement appears in InstallFiles().
>
> In this query it ignores the WHERE CLAUSE and uses what is to the far 
> right , the ORDER BY LastSequence statement, which first grabs the 
> LastSequence of 0.

Thanks for the analysis.

Do you think you could add a test case to dlls/msi/tests/db.c that shows 
the correct behaviour of the above query on Windows for me?  If you can 
do that, I'll have a go at fixing it...

Mike