[sqlite] SELECT * with ORDER BY returning duplicate rows.

To add: The second query Seg-faults in the sqlite 3.8.8 dll near the end. The other two runs to end. It seems to be a NULL reference, the error ref: -- Last Script Error: Exception Executing Script: Access violation at address 61C11C1E in module 'sqlite3.dll'. Read of address

[sqlite] sqlite3 (or sqlite4) performance on NFS

>From what I understand; - Read-Only data - Data doesn't change frequently - Central repository for data - Network latency causing issues My two cents on this is to keep a database revision ID kicking around and do a SQLite backup of the remote data to a local storage medium. At application

[sqlite] sqlite3 (or sqlite4) performance on NFS

On 2015-04-11 03:30 PM, Peng Yu wrote: > Hi, > > I know that sqlite3 is not recommended to be used on NFS. But I only > use sqlite3 for read only (the only time that I write is to load the > initial data to the data base). With this restriction, NFS should be > fine as the storage since no file

[sqlite] SELECT * with ORDER BY returning duplicate rows.

Hi I am using V3.8.9 of SQLite3.exe on Windows 7 (64bit). I have a table in a database which shows the following behaviour. The table, 'RawDataSamples', has columns ChannelID, SeqIndex and then Sample_1 .. Sample_30. Select count(*) from RawDataSamples order by ChannelID, SeqIndex will

[sqlite] sqlite3 (or sqlite4) performance on NFS

On 11 Apr 2015, at 2:30pm, Peng Yu wrote: > I am wondering for my limited usage scenario, is it possible to make > sqlite3 on NFS as fast as sqlite3 on local storage (such as disable > file locking). No. Data will flow to a local disk much faster than it can flow across your network. But

[sqlite] Select query becomes distinct on where matches unique with null value

On 2015-04-11 06:12 PM, Simon Slavin wrote: > On 11 Apr 2015, at 4:13pm, Keith Medcalf wrote: > >> Interestingly if you run analyze, it works properly ... > Oh my. I don't like the idea that ANALYZE changes the result set. > > Simon. Yes, that would be worrisome... but not to worry, the bug

[sqlite] SELECT * with ORDER BY returning duplicate rows.

This problem is also fixed on trunk. Thanks for the report. On 4/11/15, Stephen Lee wrote: > Hi > > > > I am using V3.8.9 of SQLite3.exe on Windows 7 (64bit). > > > > I have a table in a database which shows the following behaviour. > > The table, 'RawDataSamples', has columns ChannelID,

[sqlite] Select query becomes distinct on where matches unique with null value

This problem is fixed on trunk. Thanks for the report. On 4/11/15, Mike Gladysch wrote: > Hi, > > I expected that SQLite misbehaves in current versions. Since 3.8.4.3 > (bundled with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next > update of PHP there is SQLite 3.8.7.2 bundled. > >

[sqlite] sqlite3 (or sqlite4) performance on NFS

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/11/2015 06:30 AM, Peng Yu wrote: > I am wondering for my limited usage scenario, is it possible to > make sqlite3 on NFS as fast as sqlite3 on local storage (such as > disable file locking). The latency is what is getting you. SQLite uses

[sqlite] Select query becomes distinct on where matches unique with null value

On 11 Apr 2015, at 4:13pm, Keith Medcalf wrote: > Interestingly if you run analyze, it works properly ... Oh my. I don't like the idea that ANALYZE changes the result set. Simon.

[sqlite] Select query becomes distinct on where matches unique with null value

Yep, it's a Bug since at least 3.8.8. Minimal Test-case to reproduce: create table t( c1 integer primary key, c2 integer, c3 integer, UNIQUE (c2, c3) ); insert into t values( 1,null,'a'); insert into t values( 2,null,'a'); insert into t values( 3,'xxx','a'); select * from t; --

[sqlite] Select query becomes distinct on where matches unique with null value

Mike Gladysch wrote: > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' That is not valid SQL. > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) How to reproduce: create table t(x, y, unique(x, y)); insert into t values(null, 1); insert

[sqlite] Select query becomes distinct on where matches unique with null value

On 11 April 2015 at 07:07, Mike Gladysch wrote: > Hi, . > Data: > 1;1;null;test;null;null > 2;2;null;test;null;null > 3;3;null;test;null;null > 4;4;something;test;null;null > > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' > > Delivers different

[sqlite] Select query becomes distinct on where matches unique with null value

Interestingly if you run analyze, it works properly ... --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From:

[sqlite] sqlite3 (or sqlite4) performance on NFS

Hi, I know that sqlite3 is not recommended to be used on NFS. But I only use sqlite3 for read only (the only time that I write is to load the initial data to the data base). With this restriction, NFS should be fine as the storage since no file locking is needed. I remember that sqlite3 on NFS

[sqlite] Select query becomes distinct on where matches unique with null value

Hi, I expected that SQLite misbehaves in current versions. Since 3.8.4.3 (bundled with PHP 5.5.20 and PHP 5.6.4) all is still ok, but with next update of PHP there is SQLite 3.8.7.2 bundled. Table: 6 colums including an primary on col1, an unique on col2 (not null), an unique on col3

[sqlite] Request: Metadata about C API constants and functions

Thanks, Richard I found (https://www.sqlite.org/docsrc/timeline) & https://www.sqlite.org/docsrc/artifact/5c48dd261dbe5804 very useful. Last year I was parsing 100M of XML stuff and loading it into an Sqlite database. A bit of hammering required as the XML had minor errors like missing end