Re: [sqlite] Bug? LIMIT in compound statement with UNION ALL seems to affect only the first SELECT statement if it has ORDER BY
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 > 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 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
Re: [sqlite] Bug? LIMIT in compound statement with UNION ALL seems to affect only the first SELECT statement if it has ORDER BY
Thanks, Igor. It works like a charm now. 2010/10/6 Igor Tandetnik > Yuri G wrote: > > 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 > > Looks like a bug to me, too. > > > 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". > > As a workaround, try something like this: > > SELECT name FROM names > WHERE name LIKE '%a%' > ORDER BY name NOT LIKE 'a%', name; > > -- > Igor Tandetnik > > > ___ > 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
Re: [sqlite] Bug? LIMIT in compound statement with UNION ALL seems to affect only the first SELECT statement if it has ORDER BY
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 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
Re: [sqlite] Bug? LIMIT in compound statement with UNION ALL seems to affect only the first SELECT statement if it has ORDER BY
Yuri G wrote: > 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 Looks like a bug to me, too. > 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". As a workaround, try something like this: SELECT name FROM names WHERE name LIKE '%a%' ORDER BY name NOT LIKE 'a%', name; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
