Re: [sqlite] ORDER BY Does not work

2005-08-12 Thread mike cariotoglou 
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

2005-08-12 Thread D. Richard Hipp 
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

2005-08-12 Thread Dan Kennedy 
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

2005-08-12 Thread Markus Weissmann 
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

2005-08-12 Thread mike cariotoglou 
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.