Re: [sqlite] possible ordering issue since 3.7.15

[Jay A. Kreibich]

On Sat, Apr 13, 2013 at 11:25:41PM +0700, Dan Kennedy scratched on the wall:
> On 04/13/2013 11:22 PM, kenichi ishigaki wrote:
> >Hi.
> >
> >I received a report that the result of the following SQL has changed
> >since 3.7.15.
> >I haven't looked into the sqlite source yet, but can we call this a bug?
> 
> No. No "ORDER BY" clause means that the results are delivered in
> an undefined order. So both results are the same.

  In fact, SQLite has a "PRAGMA reverse_unordered_selects" configuration
  that changes the default ordering.  This specifically exists to test
  applications and make sure they don't assume a specific ordering in
  queries that lack an ORDER BY clause.
  
  You might consider this to test other areas of your application.


  http://sqlite.org/pragma.html#pragma_reverse_unordered_selects

When enabled, this PRAGMA causes SELECT statements without an ORDER
BY clause to emit their results in the reverse order of what they
normally would. This can help debug applications that are making
invalid assumptions about the result order.

SQLite makes no guarantees about the order of results if a SELECT
omits the ORDER BY clause. Even so, the order of results does not
change from one run to the next, and so many applications
mistakenly come to depend on the arbitrary output order whatever
that order happens to be. However, sometimes new versions of SQLite
will contain optimizer enhancements that will cause the output
order of queries without ORDER BY clauses to shift. When that
happens, applications that depend on a certain output order might
malfunction. By running the application multiple times with this
pragma both disabled and enabled, cases where the application makes
faulty assumptions about output order can be identified and fixed
early, reducing problems that might be caused by linking against a
different version of SQLite.



  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.7.16.2

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 13/04/13 14:55, jose isaias cabrera wrote:
> Can this problem also from a DB file shared via Windows Shared folder

An effective way to corrupt your data is to use a networked filesystem.
They do not provide the exact required semantics as a local filesystem
provides.  Most of the time you'll get away with it, but every now and
then you'll experience data corruption.

Then like so many before you'll post to this mailing list, where you'll be
pointed at the following two links :-)

  http://www.sqlite.org/faq.html#q5
  http://www.sqlite.org/howtocorrupt.html  (section 2.1)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlFp78AACgkQmOOfHg372QRcWQCfcPTuzBjSGNUeYR6xwQSFyvDe
G8kAnim8px5ZTDow9KcbqebHBu3Nod/2
=llAx
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible ordering issue since 3.7.15

[Walter Hurry]

On Sun, 14 Apr 2013 01:22:46 +0900, kenichi ishigaki wrote:

> Hi.
> 
> I received a report that the result of the following SQL has changed
> since 3.7.15.
> I haven't looked into the sqlite source yet, but can we call this a bug?
> 
> Regards,
> 
> Kenichi Ishigaki
> 
> -
> create table cd (id integer primary key, title unique, year);
> insert into cd (title, year) values ('foo', 2000);
> insert into cd (title, year) values ('bar', 2001);
> select * from cd;
> select title from cd;

If you don't specify "order by", *any* relational db will return the 
results in any order it chooses.

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

Re: [sqlite] SQLite version 3.7.16.2

[jose isaias cabrera]

"D. Richard Hipp" said...


SQLite version 3.7.16.2 is now available on the SQLite website

  http://www.sqlite.org/

The 3.7.16.2 patch release contains a two-character change in the Windows 
OS interface that fixes a long-standing race condition that could lead to 
database corruption.   This bug has apparently existed in the code every 
since version 3.0.0.  The corruption opportunity arises as follows:


(1) A process crashes or is killed while in the middle of a COMMIT, 
leaving behind a hot journal that needs to be recovered.
(2) Two or more new processes attach to the database and try to run the 
recovery at the same time.


If step (2) happens on a fast multi-core machine, and you are unlucky, 
then the xCheckReservedLock() method of the OS interface object might 
return a false positive result, deceiving one of processes into thinking 
that the recovery had already been accomplished and causing that process 
to delete the rollback journal and thereby corrupting the database.


Note that this flaw was present in the Windows interface only.  The 
unix/posix OS interface works correctly.  For unix, the only thing that 
has changed between version 3.7.16.1 and 3.7.16.2 is the version number.


We have an application that uses a shared SQLite file and many times during 
the day multiple PCs connect to that file at the same time.  Some to get 
data others to COMMIT data.  Can this problem also from a DB file shared via 
Windows Shared folder with connection from two different PC?  We had a mayor 
problem, one day where we lost all of the data updates.  We had to recover 
the previous night file and re-enter the lost data.  So, I am wondering if 
this was the cause.  Thanks.


josé 


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

Re: [sqlite] possible ordering issue since 3.7.15

[Dan Kennedy]

On 04/13/2013 11:22 PM, kenichi ishigaki wrote:

Hi.

I received a report that the result of the following SQL has changed
since 3.7.15.
I haven't looked into the sqlite source yet, but can we call this a bug?


No. No "ORDER BY" clause means that the results are delivered in
an undefined order. So both results are the same.

Suspect they are different as SQLite 3.7.15 and later figures out
that a full scan on the index on "title" might be quicker than a
full scan of the table b-tree (as the index is smaller on disk)
for the second query.

Dan.







Regards,

Kenichi Ishigaki

-
create table cd (id integer primary key, title unique, year);
insert into cd (title, year) values ('foo', 2000);
insert into cd (title, year) values ('bar', 2001);
select * from cd;
select title from cd;
-

(result prior 3.7.15)
-
1|foo|2000
2|bar|2001
foo
bar
-

(result 3.7.15 and newer)
-
1|foo|2000
2|bar|2001
bar
foo
-
___
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

[sqlite] possible ordering issue since 3.7.15

[kenichi ishigaki]

Hi.

I received a report that the result of the following SQL has changed
since 3.7.15.
I haven't looked into the sqlite source yet, but can we call this a bug?

Regards,

Kenichi Ishigaki

-
create table cd (id integer primary key, title unique, year);
insert into cd (title, year) values ('foo', 2000);
insert into cd (title, year) values ('bar', 2001);
select * from cd;
select title from cd;
-

(result prior 3.7.15)
-
1|foo|2000
2|bar|2001
foo
bar
-

(result 3.7.15 and newer)
-
1|foo|2000
2|bar|2001
bar
foo
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite port to android

[nobre]

Android does have SQLite builtin, however it is under a rather large amount
of abstractions that does not correlate directly to the SQLite API, making
it difficult to port code between platforms. Another issue is, each
manufacturer / device / firmware version have a different build of SQLite,
some have ICU, some doesn't, some have FTS, some doesn't. 
To include your own copy of SQLite in Android, you should use the
Android-NDK, add sqlite3.c into the JNI folder in your project, and use a
wrapper to access the API via Java (I recommend sqlite4java)



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite-port-to-android-tp68194p68198.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite port to android

[nobre]

Android does have SQLite builtin, however it is under a rather large amount
of abstractions that does not correlate directly to the SQLite API, making
it difficult to port code between platforms. Another issue is, each
manufacturer / device / firmware version have a different build of SQLite,
some have ICU, some doesn't, some have FTS, some doesn't.
To include your own copy of SQLite in Android, you should use the
Android-NDK, add sqlite3.c into the JNI folder in your project, and use a
wrapper to access the API via Java (I recommend sqlite4java)



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite-port-to-android-tp68194p68197.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite in Google Summer of Code 2013

[Clemens Ladisch]

Dann Luciano wrote:
> sqlite is part of the Google Summer of Code 2013?

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