Re: [sqlite] "where not exists (union-select)" fails on 2nd where

[Markus Weissmann]

On 24.08.2005, at 03:21, Kurt Welgehausen wrote:


SELECT * FROM PRIM AS P
WHERE NOT EXISTS
(
SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID
UNION
SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID
);



I looks like correct SQL according to the SQLite docs,
but I don't understand why you coded the select that
way. You should get the same result from

   select * from prim as p
   where not exists (select refid from ref1
 where refid=p.id) and
 not exists (select refid from ref2
 where refid=p.id)

I would guess that this form would be more efficient
because if the first test fails, the second sub-select
should not be executed, perhaps saving a complete scan
of ref2.



perfect - thanks a lot!


-Markus

---
Markus W. Weissmann
http://www.mweissmann.de/
http://www.opendarwin.org/~mww/

[sqlite] "where not exists (union-select)" fails on 2nd where

[Markus Weissmann]

Hi folks,

I've got a little problem with a - at least I think so - correct SQL- 
statement:


three tables, two referencing the 1st one

---
SELECT * FROM PRIM AS P
WHERE NOT EXISTS
(
SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID
UNION
SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID
);
---

chokes with "SQL error: no such column: P.ID"; as long as I do not use
a WHERE statement in the 2nd 'inner' SELECT statement, it works as  
expected.


I've worked around this by creating a VIEW from the union of the two
referencing tables so I do not need the UNION in the original query.

Is this a bug or did I just not get the docs?


thanks,

-Markus


PS: sqlite really rocks & the C-API doc is very nice!

---
Markus W. Weissmann
http://www.mweissmann.de/
http://www.opendarwin.org/~mww/

Re: [sqlite] ORDER BY Does not work

[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/