Yep, it turned out to be a bug (http://www.sqlite.org/src/info/38cb5df375).
Thanks for the info, Pavel! I didn't know that. 2010/10/6 Pavel Ivanov <paiva...@gmail.com> > I can't say anything about your particular issue with the LIMIT > clause, maybe that's a bug. But > > > Another solution is to use UNION instead of UNION ALL. But I can't use > that, > > because UNION does not respect ORDER BY in sub-statements (not sure if > it's > > a correct behavior). > > Do you know that SELECT ... FROM (SELECT ... ORDER BY ...) doesn't > have to respect your ORDER BY clause? UNION ALL doesn't have to > respect your ORDER BY clause either. So the fact that your query > behaves exactly that you want it to is a random coincidence and you > shouldn't rely on it. It's best for you to execute first select, > retrieve all rows and then execute the second select to retrieve the > remaining rows you need. > > > Pavel > > On Wed, Oct 6, 2010 at 12:22 PM, Yuri G <groovy...@gmail.com> wrote: > > Hi, everyone, > > > > This looks like a bug to me: > > > > --sql: > > > > CREATE TABLE t(a INTEGER); > > > > INSERT INTO "t" VALUES(1); > > INSERT INTO "t" VALUES(2); > > INSERT INTO "t" VALUES(3); > > INSERT INTO "t" VALUES(4); > > > > SELECT * FROM ( > > SELECT > > a > > FROM t > > WHERE a<=2 > > ORDER BY a) > > > > UNION ALL > > > > SELECT * FROM ( > > SELECT > > a > > FROM t > > WHERE a>2) > > > > LIMIT 1; > > > > --result: > > 1 > > 3 > > 4 > > > > --expected: > > 1 > > > > If I remove ORDER BY from the first SELECT, it gives the expected number > of > > rows. It looks like LIMIT limits only of the results of querying the > first > > SELECT statement because of ORDER BY. > > Another solution is to use UNION instead of UNION ALL. But I can't use > that, > > because UNION does not respect ORDER BY in sub-statements (not sure if > it's > > a correct behavior). > > > > What I'm trying to do is get all names which match the search string. I > need > > to show all names starting with search string and then show all other > > results which contain search string sorting results in each "group". > > Something like this: > > > > SELECT * FROM > > ( > > SELECT > > name > > FROM names > > WHERE name LIKE 'a%' > > ORDER BY name > > ) > > > > UNION ALL > > > > SELECT * FROM > > ( > > SELECT > > name > > FROM all_patients > > WHERE name LIKE '%a%' AND > > name NOT LIKE 'a%' > > ORDER BY name > > ) > > LIMIT 100 > > > > In this case LIMIT does not work as expected. > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
