Re: [sqlite] ORDER BY Does not work
yes, I am sure. I tested with 3.1.3, with the same results. > > I'll eventually get to this. If Dan's fix is correct, though, > the problem was introduced after 3.2.2. Are you sure you are > not using code out of CVS? >
Re: [sqlite] ORDER BY Does not work
On Fri, 2005-08-12 at 12:56 +0300, mike cariotoglou wrote: > I have found a bug in the way sqlite treats ORDER BY clauses. > select kinapo.ekey, kinapo.polhths > from eidh inner join kinapo on eidh.ekey=kinapo.ekey > order by eidh.ekey, kinapo.polhths > == > I'll eventually get to this. If Dan's fix is correct, though, the problem was introduced after 3.2.2. Are you sure you are not using code out of CVS? In any event, adding a unary "+" operator to the first term of the ORDER BY clause will likely work around the problem by defeating the optimizer: ORDER BY +eidh.ekey, kinapo.polhths ^-- unary "+" here -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] ORDER BY Does not work
In the file where.c, near the bottom of the function isSortingIndex there is a line: if( j>=nTerm || (i>=pIdx->nColumn && pIdx->onError!=OE_None) ){ Comment out the second part of the expression as follows: if( j>=nTerm /* || (i>=pIdx->nColumn && pIdx->onError!=OE_None) */){ At the moment I'm thinking that second part should just be removed, but I'm not completely sure - maybe it should be replaced with something else. At any rate it's just an optimization, so it is safe to comment it out. --- mike cariotoglou <[EMAIL PROTECTED]> wrote: > I have found a bug in the way sqlite treats ORDER BY clauses. > to reproduce the bug, run this script against an empty database (memory db > would do): > > === > CREATE TABLE Eidh ( > ekey VARCHAR(12) NOT NULL, > perigrafh VARCHAR(30) NOT NULL, > PRIMARY KEY(ekey)); > > > INSERT INTO Eidh VALUES('0001','bla'); > > CREATE TABLE KinApo ( > ekey VARCHAR(12) NOT NULL , > date DATE, > polhths INTEGER); > > INSERT INTO KinApo VALUES('0001',38353,40); > INSERT INTO KinApo VALUES('0001',38353,30); > INSERT INTO KinApo VALUES('0001',38353,20); > > select kinapo.ekey, kinapo.polhths > from eidh inner join kinapo on eidh.ekey=kinapo.ekey > order by eidh.ekey, kinapo.polhths > == > > versin 3.2.2 of sqlite gives the following result set, which is unordered: > > 0001 40 > 0001 30 > 0001 20 > > also, EXPLAIN shows that no sorting is taking place. > > This is a SERIOUS malfunction! > can you pls verify this in other environments (I am using dll 3.2.2, locally > compiled), > and do something about it. > > > > __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail
Re: [sqlite] ORDER BY Does not work
I can verify this bug on Darwin/Mac OS X with both Apples 3.1.3 and DarwinPorts 3.2.2; -Markus On 12.08.2005, at 11:56, mike cariotoglou wrote: I have found a bug in the way sqlite treats ORDER BY clauses. to reproduce the bug, run this script against an empty database (memory db would do): === CREATE TABLE Eidh ( ekey VARCHAR(12) NOT NULL, perigrafh VARCHAR(30) NOT NULL, PRIMARY KEY(ekey)); INSERT INTO Eidh VALUES('0001','bla'); CREATE TABLE KinApo ( ekey VARCHAR(12) NOT NULL , date DATE, polhths INTEGER); INSERT INTO KinApo VALUES('0001',38353,40); INSERT INTO KinApo VALUES('0001',38353,30); INSERT INTO KinApo VALUES('0001',38353,20); select kinapo.ekey, kinapo.polhths from eidh inner join kinapo on eidh.ekey=kinapo.ekey order by eidh.ekey, kinapo.polhths == versin 3.2.2 of sqlite gives the following result set, which is unordered: 0001 40 0001 30 0001 20 also, EXPLAIN shows that no sorting is taking place. This is a SERIOUS malfunction! can you pls verify this in other environments (I am using dll 3.2.2, locally compiled), and do something about it. --- Markus W. Weissmann http://www.mweissmann.de/ http://www.opendarwin.org/~mww/
[sqlite] ORDER BY Does not work
I have found a bug in the way sqlite treats ORDER BY clauses. to reproduce the bug, run this script against an empty database (memory db would do): === CREATE TABLE Eidh ( ekey VARCHAR(12) NOT NULL, perigrafh VARCHAR(30) NOT NULL, PRIMARY KEY(ekey)); INSERT INTO Eidh VALUES('0001','bla'); CREATE TABLE KinApo ( ekey VARCHAR(12) NOT NULL , date DATE, polhths INTEGER); INSERT INTO KinApo VALUES('0001',38353,40); INSERT INTO KinApo VALUES('0001',38353,30); INSERT INTO KinApo VALUES('0001',38353,20); select kinapo.ekey, kinapo.polhths from eidh inner join kinapo on eidh.ekey=kinapo.ekey order by eidh.ekey, kinapo.polhths == versin 3.2.2 of sqlite gives the following result set, which is unordered: 0001 40 0001 30 0001 20 also, EXPLAIN shows that no sorting is taking place. This is a SERIOUS malfunction! can you pls verify this in other environments (I am using dll 3.2.2, locally compiled), and do something about it.