LIMIT with OFFSET is not working in android platform

classic Classic list List threaded Threaded
33 messages Options
12
Reply | Threaded
Open this post in threaded view
|

LIMIT with OFFSET is not working in android platform

Sathish Kumar
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Richard Hipp-3
On 5/16/18, Sathish Kumar <[hidden email]> wrote:

> 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).

Unable to reproduce the problem.  The example text came through garbled.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
Hi,
    Please find the below example.

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> select count(*) from test;
7
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 test
CREATE TABLE test (id integer primary key autoincrement, name text);

sqlite> PRAGMA table_info(test);
0|id|integer|0||1
1|name|text|0||0
sqlite>

Thanks,
Sathish

On Wed, May 16, 2018 at 3:43 PM, Richard Hipp <[hidden email]> wrote:

> On 5/16/18, Sathish Kumar <[hidden email]> wrote:
> > 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).
>
> Unable to reproduce the problem.  The example text came through garbled.
>
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Richard Hipp-3
On 5/16/18, Sathish Kumar <[hidden email]> wrote:
> Hi,
>     Please find the below example.
>
> 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> select count(*) from test;
> 7

How is it that you have 7 rows in the table after only doing 2 INSERTs?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
I just skipped those statements in mail, but i inserted 7 rows.

On Wed 16 May, 2018, 4:06 PM Richard Hipp, <[hidden email]> wrote:

> On 5/16/18, Sathish Kumar <[hidden email]> wrote:
> > Hi,
> >     Please find the below example.
> >
> > 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> select count(*) from test;
> > 7
>
> How is it that you have 7 rows in the table after only doing 2 INSERTs?
>
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Richard Hipp-3
On 5/16/18, Sathish Kumar <[hidden email]> wrote:
> I just skipped those statements in mail, but i inserted 7 rows.

Please send *exactly* the sequence of commands that you think are
producing an incorrect answer.  Please leave nothing to chance, or to
interpretation.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
Hi,
 Sorry, Please find the full sequence below, it is taken from S9 device
running with Android 8.0.

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> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> select count(*) from test;
7
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 3;
2|a
3|a
sqlite> select * from test limit 2 offset 5;
2|a
3|a
sqlite> select * from test limit 2,5;
2|a
3|a
4|a
5|a
6|a
sqlite> select * from test limit 5,2;
2|a
3|a
sqlite> select * from test limit 5,3;
2|a
3|a
4|a
sqlite> select * from test;
1|a
2|a
3|a
4|a
5|a
6|a
7|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> .schema test
CREATE TABLE test (id integer primary key autoincrement, name text);

sqlite> PRAGMA table_info(test);
0|id|integer|0||1
1|name|text|0||0

Thanks,
Sathish

On Wed, May 16, 2018 at 4:19 PM, Richard Hipp <[hidden email]> wrote:

> On 5/16/18, Sathish Kumar <[hidden email]> wrote:
> > I just skipped those statements in mail, but i inserted 7 rows.
>
> Please send *exactly* the sequence of commands that you think are
> producing an incorrect answer.  Please leave nothing to chance, or to
> interpretation.
>
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
Samsung S9 device with Android 8.0

On Wed, May 16, 2018 at 4:50 PM, Sathish Kumar <[hidden email]>
wrote:

> Hi,
>  Sorry, Please find the full sequence below, it is taken from S9 device
> running with Android 8.0.
>
> 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> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> select count(*) from test;
> 7
> 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 3;
> 2|a
> 3|a
> sqlite> select * from test limit 2 offset 5;
> 2|a
> 3|a
> sqlite> select * from test limit 2,5;
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> sqlite> select * from test limit 5,2;
> 2|a
> 3|a
> sqlite> select * from test limit 5,3;
> 2|a
> 3|a
> 4|a
> sqlite> select * from test;
> 1|a
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> 7|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> .schema test
> CREATE TABLE test (id integer primary key autoincrement, name text);
>
> sqlite> PRAGMA table_info(test);
> 0|id|integer|0||1
> 1|name|text|0||0
>
> Thanks,
> Sathish
>
> On Wed, May 16, 2018 at 4:19 PM, Richard Hipp <[hidden email]> wrote:
>
>> On 5/16/18, Sathish Kumar <[hidden email]> wrote:
>> > I just skipped those statements in mail, but i inserted 7 rows.
>>
>> Please send *exactly* the sequence of commands that you think are
>> producing an incorrect answer.  Please leave nothing to chance, or to
>> interpretation.
>>
>> --
>> D. Richard Hipp
>> [hidden email]
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Richard Hipp-3
On 5/16/18, Sathish Kumar <[hidden email]> wrote:
> Samsung S9 device with Android 8.0

What output do you see from the following commands:

  SELECT sqlite_source_id();
  PRAGMA compile_options;

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Dan Kennedy-4
In reply to this post by Sathish Kumar
On 05/16/2018 06:20 PM, Sathish Kumar wrote:
> Hi,
>   Sorry, Please find the full sequence below, it is taken from S9 device
> running with Android 8.0.

This is a bit strange. The VM code looks correct. Try running a [make
clean] to ensure you're getting a clean build.

If that doesn't work, please try building with SQLITE_DEBUG defined and
post the results of running the script below. It's working on Android 7
here.

Dan.

####################################################

create table test (id integer primary key autoincrement, name text);

insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');

.print "--- select count(*) from test;"
select count(*) from test;
.print "--- select * from test;"
select * from test;
.print "--- select * from test limit 10 offset 2;"
select * from test limit 10 offset 2;
.print "--- select * from test limit 10 offset 5;"
select * from test limit 10 offset 5;
.print "--- select * from test limit 2 offset 3;"
select * from test limit 2 offset 3;
.print "--- EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;"
EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
.print "--- SELECT * FROM test LIMIT 2 OFFSET 5;"
SELECT * FROM test LIMIT 2 OFFSET 5;
.print "--- with vdbe_trace"
PRAGMA vdbe_trace = 1;
SELECT * FROM test LIMIT 2 OFFSET 5;

####################################################


>
> 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> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> select count(*) from test;
> 7
> 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 3;
> 2|a
> 3|a
> sqlite> select * from test limit 2 offset 5;
> 2|a
> 3|a
> sqlite> select * from test limit 2,5;
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> sqlite> select * from test limit 5,2;
> 2|a
> 3|a
> sqlite> select * from test limit 5,3;
> 2|a
> 3|a
> 4|a
> sqlite> select * from test;
> 1|a
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> 7|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> .schema test
> CREATE TABLE test (id integer primary key autoincrement, name text);
>
> sqlite> PRAGMA table_info(test);
> 0|id|integer|0||1
> 1|name|text|0||0
>
> Thanks,
> Sathish
>
> On Wed, May 16, 2018 at 4:19 PM, Richard Hipp <[hidden email]> wrote:
>
>> On 5/16/18, Sathish Kumar <[hidden email]> wrote:
>>> I just skipped those statements in mail, but i inserted 7 rows.
>> Please send *exactly* the sequence of commands that you think are
>> producing an incorrect answer.  Please leave nothing to chance, or to
>> interpretation.
>>
>> --
>> D. Richard Hipp
>> [hidden email]
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
In reply to this post by Richard Hipp-3
sqlite> SELECT sqlite_source_id();

2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b


sqlite> PRAGMA compile_options;

COMPILER=clang-5.0.300080

DEFAULT_SYNCHRONOUS=2

DEFAULT_WAL_SYNCHRONOUS=2

ENABLE_FTS3

ENABLE_FTS5

ENABLE_JSON1

ENABLE_RTREE

SYSTEM_MALLOC

TEMP_STORE=3

THREADSAFE=1

sqlite>


On Wed, May 16, 2018 at 5:29 PM, Richard Hipp <[hidden email]> wrote:

> On 5/16/18, Sathish Kumar <[hidden email]> wrote:
> > Samsung S9 device with Android 8.0
>
> What output do you see from the following commands:
>
>   SELECT sqlite_source_id();
>   PRAGMA compile_options;
>
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
Hi Richard,
      Is there any issue with this compile option ?


Thanks,
Sathish


On Wed 16 May, 2018, 5:30 PM Sathish Kumar, <[hidden email]> wrote:

> sqlite> SELECT sqlite_source_id();
>
> 2017-06-08 14:26:16
> 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b
>
>
> sqlite> PRAGMA compile_options;
>
> COMPILER=clang-5.0.300080
>
> DEFAULT_SYNCHRONOUS=2
>
> DEFAULT_WAL_SYNCHRONOUS=2
>
> ENABLE_FTS3
>
> ENABLE_FTS5
>
> ENABLE_JSON1
>
> ENABLE_RTREE
>
> SYSTEM_MALLOC
>
> TEMP_STORE=3
>
> THREADSAFE=1
>
> sqlite>
>
>
> On Wed, May 16, 2018 at 5:29 PM, Richard Hipp <[hidden email]> wrote:
>
>> On 5/16/18, Sathish Kumar <[hidden email]> wrote:
>> > Samsung S9 device with Android 8.0
>>
>> What output do you see from the following commands:
>>
>>   SELECT sqlite_source_id();
>>   PRAGMA compile_options;
>>
>> --
>> D. Richard Hipp
>> [hidden email]
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
Hi Richard,
    Further update on this issue.

    I am observing this issue only with the library build using ndk-build
via terminal and the library built via android studio gradle works fine. Is
there any difference in compiling sqlite using gradle and via ndk-build via
terminal ?


Thanks,
Sathish

On Thu, May 17, 2018 at 8:07 AM, Sathish Kumar <[hidden email]>
wrote:

> Hi Richard,
>       Is there any issue with this compile option ?
>
>
> Thanks,
> Sathish
>
>
> On Wed 16 May, 2018, 5:30 PM Sathish Kumar, <[hidden email]>
> wrote:
>
>> sqlite> SELECT sqlite_source_id();
>>
>> 2017-06-08 14:26:16 0ee482a1e0eae22e08edc8978c9733
>> a96603d4509645f348ebf55b579e89636b
>>
>>
>> sqlite> PRAGMA compile_options;
>>
>> COMPILER=clang-5.0.300080
>>
>> DEFAULT_SYNCHRONOUS=2
>>
>> DEFAULT_WAL_SYNCHRONOUS=2
>>
>> ENABLE_FTS3
>>
>> ENABLE_FTS5
>>
>> ENABLE_JSON1
>>
>> ENABLE_RTREE
>>
>> SYSTEM_MALLOC
>>
>> TEMP_STORE=3
>>
>> THREADSAFE=1
>>
>> sqlite>
>>
>>
>> On Wed, May 16, 2018 at 5:29 PM, Richard Hipp <[hidden email]> wrote:
>>
>>> On 5/16/18, Sathish Kumar <[hidden email]> wrote:
>>> > Samsung S9 device with Android 8.0
>>>
>>> What output do you see from the following commands:
>>>
>>>   SELECT sqlite_source_id();
>>>   PRAGMA compile_options;
>>>
>>> --
>>> D. Richard Hipp
>>> [hidden email]
>>>
>>
>>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Richard Hipp-3
On 5/18/18, Sathish Kumar <[hidden email]> wrote:
> Hi Richard,
>     Further update on this issue.
>
>     I am observing this issue only with the library build using ndk-build
> via terminal and the library built via android studio gradle works fine. Is
> there any difference in compiling sqlite using gradle and via ndk-build via
> terminal ?

Try building with compiler optimizations disabled (-O0) and see if
that makes a difference.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
Ok, I will try.

On Fri 18 May, 2018, 7:15 PM Richard Hipp, <[hidden email]> wrote:

> On 5/18/18, Sathish Kumar <[hidden email]> wrote:
> > Hi Richard,
> >     Further update on this issue.
> >
> >     I am observing this issue only with the library build using ndk-build
> > via terminal and the library built via android studio gradle works fine.
> Is
> > there any difference in compiling sqlite using gradle and via ndk-build
> via
> > terminal ?
>
> Try building with compiler optimizations disabled (-O0) and see if
> that makes a difference.
>
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
Hi Richard,
     Yes, it works if i disable the compiler optimization. Below is what i
used in my Android.mk file. Will it create any side effects if we disable
compiler optimization ?

LOCAL_CFLAGS += -O0

LOCAL_CPPFLAGS += -O0


Thanks,
Sathish

On Fri, May 18, 2018 at 7:28 PM, Sathish Kumar <[hidden email]>
wrote:

> Ok, I will try.
>
> On Fri 18 May, 2018, 7:15 PM Richard Hipp, <[hidden email]> wrote:
>
>> On 5/18/18, Sathish Kumar <[hidden email]> wrote:
>> > Hi Richard,
>> >     Further update on this issue.
>> >
>> >     I am observing this issue only with the library build using
>> ndk-build
>> > via terminal and the library built via android studio gradle works
>> fine. Is
>> > there any difference in compiling sqlite using gradle and via ndk-build
>> via
>> > terminal ?
>>
>> Try building with compiler optimizations disabled (-O0) and see if
>> that makes a difference.
>>
>> --
>> D. Richard Hipp
>> [hidden email]
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Richard Hipp-3
On 5/21/18, Sathish Kumar <[hidden email]> wrote:
> Hi Richard,
>      Yes, it works if i disable the compiler optimization. Below is what i
> used in my Android.mk file. Will it create any side effects if we disable
> compiler optimization ?
>
> LOCAL_CFLAGS += -O0
>
> LOCAL_CPPFLAGS += -O0

Looks like you have found a bug in clang.  I recommend updating to the
latest version of the compiler that you can get your hands on, and see
if that doesn't fix the problem.

Running SQLite with compiler optimizations off will make it slower.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
Thanks, I will do that.

Thanks,
Sathish


On Mon 21 May, 2018, 9:34 PM Richard Hipp, <[hidden email]> wrote:

> On 5/21/18, Sathish Kumar <[hidden email]> wrote:
> > Hi Richard,
> >      Yes, it works if i disable the compiler optimization. Below is what
> i
> > used in my Android.mk file. Will it create any side effects if we disable
> > compiler optimization ?
> >
> > LOCAL_CFLAGS += -O0
> >
> > LOCAL_CPPFLAGS += -O0
>
> Looks like you have found a bug in clang.  I recommend updating to the
> latest version of the compiler that you can get your hands on, and see
> if that doesn't fix the problem.
>
> Running SQLite with compiler optimizations off will make it slower.
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Dominique Pellé
In reply to this post by Richard Hipp-3
On Mon, May 21, 2018 at 6:04 PM Richard Hipp <[hidden email]> wrote:

> > LOCAL_CFLAGS += -O0
> >
> > LOCAL_CPPFLAGS += -O0

> Looks like you have found a bug in clang.  I recommend updating to the
> latest version of the compiler that you can get your hands on, and see
> if that doesn't fix the problem.

> Running SQLite with compiler optimizations off will make it slower.

Either it is a bug in clang, or SQLite has an undefined behavior,
which could cause differences in debug and release mode.

Can you try to build with -fsanitize=undefined and see if it
reports anything?

Dominique
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT with OFFSET is not working in android platform

Sathish Kumar
In reply to this post by Sathish Kumar
Hi Richard,
     Today, i verified with android NDK 17 and It is working fine without
disabling compiler optimization.

Thanks,
Sathish

On Mon, May 21, 2018 at 9:41 PM, Sathish Kumar <[hidden email]>
wrote:

> Thanks, I will do that.
>
> Thanks,
> Sathish
>
>
> On Mon 21 May, 2018, 9:34 PM Richard Hipp, <[hidden email]> wrote:
>
>> On 5/21/18, Sathish Kumar <[hidden email]> wrote:
>> > Hi Richard,
>> >      Yes, it works if i disable the compiler optimization. Below is
>> what i
>> > used in my Android.mk file. Will it create any side effects if we
>> disable
>> > compiler optimization ?
>> >
>> > LOCAL_CFLAGS += -O0
>> >
>> > LOCAL_CPPFLAGS += -O0
>>
>> Looks like you have found a bug in clang.  I recommend updating to the
>> latest version of the compiler that you can get your hands on, and see
>> if that doesn't fix the problem.
>>
>> Running SQLite with compiler optimizations off will make it slower.
>> --
>> D. Richard Hipp
>> [hidden email]
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12