sqlite-users@mailinglists.sqlite.org wrote:
> Your schema implies that there can be more than one TIME for any GNAME and AN
> combination (otherwise the primary key would not need to include alle three
> fields). This contradicts your statement that AN and TIME are "the same
> order".
> (consider the tuples ("T1",1,1) and ("T2",1,2); the AN field compares equal,
> so
> ORDER BY AN is free to return the T2 row before the T1 row).
>
> Which query specifically would you have in mind that relies on your assertion?
>
> Also, if your application requires that rows be returned in a specifc order,
> your MUST specify this with en ORDER BY clause and not rely on the visitation
> order. The visitation order may change due to a number of factors including
> the
> SQLite version, the "shape" of your data, running ANALYZE and maybe more.
About the PRIMARY KEY you are correct; that is my mistake.
The specific query is this one:
SELECT `ART`.`MID` FROM `XPOST`, `ART` USING(`AN`) WHERE `XPOST`.`TIME` >= ?1
AND `XPOST`.`GNAME` = ?2;
(The (GNAME,AN) combinations are actually unique, for any value of AN there is
exactly one value of TIME. Probably TIME doesn't really belong in XPOST at all;
I originally put it there due to this confusion I had and then forgot to remove
it; that is also why it is part of the primary key even though it shouldn't be.
The next version of my software would probably fix that.)
The above query implements the NEWNEWS command of NNTP. RFC 3977 says "the
order of the response has no specific significance and may vary from response
to response in the same session"; so, in order that SQLite can choose the most
efficient query plan without requiring a specific order, there is no ORDER BY
clause.
(There is another variant of that query without the second part of the WHERE
clause, used if "NEWNEWS *" is specified. NEWNEWS followed by anything other
than * or a single newsgroup currently results in a 503 error in this
implementation.)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
