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
