Hi All, OFFSET clause is not working as expected in android platform. [ ISSUE ] : eg. select * from test limit 2 offset 5;
[ Expected Result ] : This query should return two rows starting from index 6. [ Actual Result ] : It always returns rows starting from index 2, even though it had enough rows in the table. (Please see the below example, it has all the informations). I cross checked with Mac os behaviour to confirm and there this query is working as expected. I took the latest code from sqlite site and compiled for android platform using below compilation flags. # If using SEE, uncomment the following: # LOCAL_CFLAGS += -DSQLITE_HAS_CODEC #Define HAVE_USLEEP, otherwise ALL sleep() calls take at least 1000ms LOCAL_CFLAGS += -DHAVE_USLEEP=1 # Enable SQLite extensions. LOCAL_CFLAGS += -DSQLITE_ENABLE_FTS5 LOCAL_CFLAGS += -DSQLITE_ENABLE_RTREE LOCAL_CFLAGS += -DSQLITE_ENABLE_JSON1 LOCAL_CFLAGS += -DSQLITE_ENABLE_FTS3 # This is important - it causes SQLite to use memory for temp files. Since # Android has no globally writable temp directory, if this is not defined the # application throws an exception when it tries to create a temp file. # LOCAL_CFLAGS += -DSQLITE_TEMP_STORE=3 LOCAL_CFLAGS += -DHAVE_CONFIG_H -DKHTML_NO_EXCEPTIONS -DGKWQ_NO_JAVA LOCAL_CFLAGS += -DNO_SUPPORT_JS_BINDING -DQT_NO_WHEELEVENT -DKHTML_NO_XBL LOCAL_CFLAGS += -U__APPLE__ LOCAL_CFLAGS += -DHAVE_STRCHRNUL=0 LOCAL_CFLAGS += -DSQLITE_USE_URI=1 LOCAL_CFLAGS += -Wno-unused-parameter -Wno-int-to-pointer-cast LOCAL_CFLAGS += -Wno-uninitialized -Wno-parentheses LOCAL_CPPFLAGS += -Wno-conversion-null ifeq ($(TARGET_ARCH), arm) LOCAL_CFLAGS += -DPACKED="__attribute__ ((packed))" else LOCAL_CFLAGS += -DPACKED="" endif I verified with *3.19.3, 3.23 and 3.23.1* versions and i am able to reproduce this issue with all these versions. Please let me know if i need to enable any flags to make OFFSET clause work in android. sqlite> create table test (id integer primary key autoincrement, name text); sqlite> insert into test (name) values('a'); sqlite> insert into test (name) values('a'); sqlite> ... sqlite> select * from test limit 10 offset 2;2|a 3|a 4|a 5|a 6|a 7|a sqlite> select * from test limit 10 offset 5;2|a 3|a 4|a 5|a 6|a 7|a sqlite> select * from test limit 2 offset 5;2|a 3|a sqlite> select * from test limit 5,3;2|a 3|a 4|a sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- -------------0 Init 0 14 0 00 1 Integer 2 1 0 00 2 Integer 5 2 0 00 3 MustBeInt 2 0 0 00 4 OffsetLimit 1 3 2 00 5 OpenRead 0 383 0 2 00 6 Rewind 0 13 0 00 7 IfPos 2 12 1 00 8 Rowid 0 4 0 00 9 Column 0 1 5 00 10 ResultRow 4 2 0 00 11 DecrJumpZero 1 13 0 00 12 Next 0 7 0 01 13 Halt 0 0 0 00 14 Transaction 0 0 44 0 01 15 Goto 0 1 0 00 sqlite> sqlite> .schema testCREATE TABLE test (id integer primary key autoincrement, name text); sqlite> PRAGMA table_info(test); 0|id|integer|0||11|name|text|0||0 sqlite> Thanks, Sathish _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users