In simplified terms, I am trying to select the top N name/value pairs
according to value, then sort those N rows by name.
CREATE TABLE t1(x TEXT,y INT);
INSERT INTO t1 VALUES('a',1);
INSERT INTO t1 VALUES('b',2);
INSERT INTO t1 VALUES('c',3);
INSERT INTO t1 VALUES('d',4);
INSERT INTO t1 VALUES('e',5);
CREATE VIEW v1 AS SELECT x FROM t1 ORDER BY y DESC;
CREATE VIEW vv1 AS SELECT x FROM v1 LIMIT 3;
CREATE VIEW vv2 AS SELECT x FROM (SELECT x FROM v1 LIMIT 3) WHERE 1;
SELECT x FROM vv1; -- e,d,c -- OK --
SELECT x FROM vv1 ORDER BY x; -- a,b,c -- ?? --
SELECT x FROM vv2; -- e,d,c -- OK --
SELECT x FROM vv2 ORDER BY x; -- c,d,e -- OK --
Why does vv1 behave differently than vv2? With vv1, the query
planner/optimizer seems to push the LIMIT clause all the way to the "end"
of the query. Is this the desired behavior? With vv1, it seems wrong to
return two different sets of rows simply based on an ORDER BY of the view.
vv2 works as expected but it seems wrong to wrap the LIMIT-ed view as a
subquery and apply a trivial WHERE (removing the WHERE 1 gives the same
erronious results as vv1). Is this a bug or am I missing something and this
is the proper behavior?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
