Re: [sqlite] possible bug 3.8.1 /3.8.3

[Adam Devita]

Good day all,

Thank you for your replies.

Yes, I can provide the data if required, although I don't think it is
needed, as the bug is in the user's code.   The point about what happens if
several timestamps have the same value is valid, and in this case, I think
is the explanation.

sqlite>  SELECT id, timestamp, data_blob FROM data WHERE timestamp =
(SELECT MIN(timestamp) FROM data WHERE stream_num = 2) ;
3|12946000654830|☻
4|12946000654830|☺
5|12946000654830|☺

sqlite>  SELECT id, timestamp, data_blob FROM data WHERE timestamp =
(SELECT MIN(timestamp) FROM data WHERE stream_num = 2) and stream_num = 2;
4|12946000654830|☺

sqlite> SELECT id, timestamp, data_blob FROM data WHERE stream_num = 2
order by timestamp asc;
4|12946000654830|☺


Obviously, there are several records with the same timetamp, and putting
the restriction on the stream num ensures that the right one is picked.

regards,
Adam




On Mon, Feb 3, 2014 at 12:47 PM, Richard Hipp  wrote:

> Can you provide data?  Without some sample data, we cannot tell if the
> answer SQLite is providing is right or wrong.
>
>
> On Mon, Feb 3, 2014 at 12:25 PM, Adam Devita  wrote:
>
> > Good day,
> >
> > I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
> > 3.8.3, which didn't work.  The observation is that
> >
> > This query:
> > SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
> > FROM data WHERE stream_num = ?) LIMIT 1
> >
> > seems to occasionally produce a wrong result (the content of data_blob is
> > incorrect given the values of stream_num)
> >
> > yet this query
> > SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp
> asc
> > LIMIT 1
> >
> > seems just fine, insofar as the same tests on the same data have not hit
> > any of the error condition / contradiction.
> >
> > in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
> > parameter ?
> >
> >
> > We are using an in memory database as a smarter queue where timestamp
> data
> > gets inserted, and if the db size is sufficient (30 to 40 records) the
> > above query lets us pop the earliest timestamp (which is stored as int64
> > via sqlite3_bind_int64).
> >
> > Is this a possible bug or am I missing something?  Using the backup api
> to
> > look at it from a file
> > sqlite>.schema
> > CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
> > stream_num TINYINT, source_seq_num TINYINT,
> > event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT
> NULL);
> >
> > sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
> > timestamp asc  LIMIT 1;
> > 4|☺
> > sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
> > MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
> > 3|☻
> > sqlite>
> >
> > regards,
> > Adam DeVita
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] possible bug 3.8.1 /3.8.3

[Richard Hipp]

Can you provide data?  Without some sample data, we cannot tell if the
answer SQLite is providing is right or wrong.


On Mon, Feb 3, 2014 at 12:25 PM, Adam Devita  wrote:

> Good day,
>
> I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
> 3.8.3, which didn't work.  The observation is that
>
> This query:
> SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
> FROM data WHERE stream_num = ?) LIMIT 1
>
> seems to occasionally produce a wrong result (the content of data_blob is
> incorrect given the values of stream_num)
>
> yet this query
> SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc
> LIMIT 1
>
> seems just fine, insofar as the same tests on the same data have not hit
> any of the error condition / contradiction.
>
> in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
> parameter ?
>
>
> We are using an in memory database as a smarter queue where timestamp data
> gets inserted, and if the db size is sufficient (30 to 40 records) the
> above query lets us pop the earliest timestamp (which is stored as int64
> via sqlite3_bind_int64).
>
> Is this a possible bug or am I missing something?  Using the backup api to
> look at it from a file
> sqlite>.schema
> CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
> stream_num TINYINT, source_seq_num TINYINT,
> event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT NULL);
>
> sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
> timestamp asc  LIMIT 1;
> 4|☺
> sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
> MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
> 3|☻
> sqlite>
>
> regards,
> Adam DeVita
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug 3.8.1 /3.8.3

[Igor Tandetnik]

On 2/3/2014 12:25 PM, Adam Devita wrote:

This query:
SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
FROM data WHERE stream_num = ?) LIMIT 1

seems to occasionally produce a wrong result (the content of data_blob is
incorrect given the values of stream_num)

yet this query
SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc
LIMIT 1

seems just fine


If timestamp is not unique - if there is more than one record carrying 
the smallest timestamp - then these queries might produce different rows.


The first query doesn't even restrict the result to a particular 
stream_num. If you have two records with the same timestamp but 
different stream_num, then the first query might return the "wrong" one 
(one where stream_num does not equal the bound parameter). The second 
query is only ambiguous if there are two records with the same timestamp 
and the same stream_num.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug 3.8.1 /3.8.3

[Dan Kennedy]

On 02/04/2014 12:25 AM, Adam Devita wrote:

Good day,

I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
3.8.3, which didn't work.  The observation is that

This query:
SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
FROM data WHERE stream_num = ?) LIMIT 1

seems to occasionally produce a wrong result (the content of data_blob is
incorrect given the values of stream_num)

yet this query
SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc
LIMIT 1

seems just fine, insofar as the same tests on the same data have not hit
any of the error condition / contradiction.

in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
parameter ?


We are using an in memory database as a smarter queue where timestamp data
gets inserted, and if the db size is sufficient (30 to 40 records) the
above query lets us pop the earliest timestamp (which is stored as int64
via sqlite3_bind_int64).

Is this a possible bug or am I missing something?  Using the backup api to
look at it from a file
sqlite>.schema
CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
stream_num TINYINT, source_seq_num TINYINT,
event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT NULL);

sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
timestamp asc  LIMIT 1;
4|☺
sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
3|☻
sqlite>


More than one entry in the table that matches the "timestamp = (SELECT 
MIN ...)" condition? Does the id=3 row show up in the output if you 
remove the LIMIT clause from the second query?






___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] possible bug 3.8.1 /3.8.3

[Adam Devita]

Good day,

I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
3.8.3, which didn't work.  The observation is that

This query:
SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
FROM data WHERE stream_num = ?) LIMIT 1

seems to occasionally produce a wrong result (the content of data_blob is
incorrect given the values of stream_num)

yet this query
SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc
LIMIT 1

seems just fine, insofar as the same tests on the same data have not hit
any of the error condition / contradiction.

in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
parameter ?


We are using an in memory database as a smarter queue where timestamp data
gets inserted, and if the db size is sufficient (30 to 40 records) the
above query lets us pop the earliest timestamp (which is stored as int64
via sqlite3_bind_int64).

Is this a possible bug or am I missing something?  Using the backup api to
look at it from a file
sqlite>.schema
CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
stream_num TINYINT, source_seq_num TINYINT,
event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT NULL);

sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
timestamp asc  LIMIT 1;
4|☺
sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
3|☻
sqlite>

regards,
Adam DeVita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users