[Kexi] LIMIT 1 in Select Clause

[Jarosław Staniek]

Sharan Rao said the following, On 2007-12-12 23:26:
> On Tuesday 04 Dec 2007 7:17:42 pm Jarosław Staniek wrote:
>> Sharan Rao said the following, On 2007-12-01 21:17:
>>>  I was facing problems with the function Connection::querySingleString()
>>> in Sybase, as the " LIMIT " clause is not supported.
> 
> Hmm, new thing I noticed in the Limit 1 clause. ( I should have noticed it 
> before, but I tested the clause only in commandline tsql , and not Kexi, 
> before committing :( ),
> 
> Sybase gives a warning ( in Sybase Central ) if I execute a query of the type
>  "Select Top 1 fooColumn from fooTable where fooClause"
> 
> Warning: "The result returned is non-deterministic"
> Of course, that's because there's no specific ordering specified. Now, 
> unfortunately, this is returned as an error in freetds/dblib !!
> 
> Now, we do have a columnNumber being passed to Connection::querySingleString() 
> as a param, but would an "Order by " clause on that column be right in the
> case if there are multiple rows ? 

 > I guess querySingleString() is not intended
> to be used where multiple row/multiple column results are returned :) ( as 
> the documentation suggests )

Good question;
There can be multiple columns of course, otherwise 'uint column'argument would 
not make much sense.
There can be multiple rows. I don't know whether Sybase raises the 
warning/error based on the semantics of the statement or after it know there 
are many rows.
The case when we have many rows (and when addLimitTo1==true) whould be when we 
indeed know that all the values of the column #column are the same, so we 
agree on the optimization.

Using ORDER BY is indeed good idea. We can use it by adding:

  QString::fromLatin1(" ORDER BY %1").arg(column) to the statement.

But now, what about statements already having ORDER BY part?

There are two simple options for such statements:
1. set addLimitTo1 to false (so drop optimization)
2. add another boolean 'ordered' flag so in case of Sybase driver we can force 
addLimitTo1 to false.

For 2. we're getting more and more flags, but I already think about having a 
data structure or a QFlag combining multiple options...

-- 
regards / pozdrawiam, Jaroslaw Staniek
  Sponsored by OpenOffice Polska (http://www.openoffice.com.pl/en) to work on
  Kexi & KOffice (http://www.kexi.pl/en, http://www.koffice.org/kexi)
  KDE Libraries for MS Windows (http://windows.kde.org)