On 10/5/15, Matt DeLand <matthew.deland at gmail.com> wrote: > The following SQL outputs more rows than expected. The behavior seems to > require the use of UNION ALL, ORDER BY (in the column being selected), and > an OFFSET.
Thanks for providing a test case. See https://www.sqlite.org/src/info/b65cb2c8d91f6685 for the ticket. > > Swapping the two clauses of the union also produces the wrong result, > though it's definitely necessary to order by x. > > The answer is also correct if you build an index on the queried column. > > Thanks for your help (and great software)! > Matt > > > > ``` > drop table if exists test; > create table test ( x ); > insert into test values(1); > insert into test values(2); > > -- expected output: a single row > -- 1|1 > -- > -- actual output: > -- 1|1 > -- 2|2 > > SELECT * FROM ( > VALUES(0,0) > ) > UNION ALL > SELECT * FROM ( > SELECT rowid, x FROM test ORDER BY x > ) > LIMIT 1 > OFFSET 1 > ; > > > -- test again after building index > > create index test_x on test(x); > > -- rerun query > > SELECT * FROM ( > VALUES(0,0) > ) > UNION ALL > SELECT * FROM ( > SELECT rowid, x FROM test ORDER BY x > ) > LIMIT 1 > OFFSET 1 > ; > > -- output as expected! > > ``` > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
