date:20161011

Re: [sqlite] Backward cursor support?

2016-10-11 Thread Keith Medcalf


No -- only navigable database support navigation (ie, hierarchical, network 
extended, etc).  Relational Databases do not support scrollable cursors except 
through trickery and deception.

You can save the primary key(s) values and re-issue the query with the ORDER BY 
reversed (row values make this easier).  Or you can do the select into a temp 
table, then play with retrieving the various rows from the temp table using the 
rowid to make it "appear" you are going backwards and forwards.  Or you can 
combine the two and generate the primary keys (keyset) into a temporary table, 
then use the rowid or the keyset to simulate scrolling and recreate the rowset 
on demand with an appropriate query (again, row values make this easier).

https://www.sqlite.org/draft/rowvalue.html
http://use-the-index-luke.com/blog/2014-05/what-i-learned-about-sqlite-at-a-postgresql-conference


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Igor Korot
> Sent: Tuesday, 11 October, 2016 21:42
> To: Discussion of SQLite Database; General Discussion of SQLite Database
> Subject: [sqlite] Backward cursor support?
> 
> Hi,
> Does SQLite support backward cursor? Something like step_back()?
> 
> If not, what would be a way to do it?
> 
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] Backward cursor support?

2016-10-11 Thread Igor Korot

Hi,
Does SQLite support backward cursor? Something like step_back()?

If not, what would be a way to do it?

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

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf


#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1

makes it work properly.  neither NDEBUG nor SQLITE_DEBUG explicitly defined.

Over to Richard ...

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Keith Medcalf
> Sent: Tuesday, 11 October, 2016 19:22
> To: SQLite mailing list
> Subject: Re: [sqlite] LIMIT doesn't return expected rows
> 
> 
> By default, I do not see the query being flattened.  Flattening it
> manually produces the same right results independant of something config.h
> does...
> 
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit
> 3;
> 1
> 2
> 3
> sqlite> select i.id from i, m where i.id=m.id order by flags desc limit 3;
> select i.id from i, m where i.id=m.id order by flags desc limit 3;
> 6
> 5
> 7
> 
> > Well I can reproduce the wrong answer.  If I compile the amalgamation
> > code:
> >
> >   with no config.h I get the same result as you,
> >   but with the following config.h, the result is correct
> >
> >  (this is with the code from the head of trunk):
> >
> > #ifndef _CONFIG_H
> > #define _CONFIG_H
> >
> > // Values of WINVER and _WIN32_WINNT for various minimum levels of Win32
> > Compatability
> > //
> > // WIN100x0A00  WIN6 0x0600  W2K  0x0500 NT4
> > 0x0400
> > //  VISTA0x0600  WXP  0x0501 W95
> > 0x0400
> > //  W2K8 0x0600  W2K3 0x0502 NT4E
> > 0x0401
> > //  WIN7 0x0601  W98
> > 0x0410
> > //  WIN8 0x0602  WME
> > 0x0490
> > //  WIN810x0603
> >
> > #ifndef _WIN32_WINNT
> > #define _WIN32_WINNT 0x0600
> > #endif
> > #ifndef WINVER
> > #define WINVER _WIN32_WINNT
> > #endif
> >
> > // General Platform Compilation Support Options
> >
> > #define HAVE_MALLOC_USABLE_SIZE 1
> > #define HAVE_USLEEP 1
> >
> > // *** SQLITE GENERAL CONFIGURATION OPTIONS ***
> >
> > // #define SQLITE_DEFAULT_AUTOMATIC_INDEX  1   //
> default:
> > 1
> > // #define SQLITE_DEFAULT_AUTOVACUUM   0   //
> default:
> > 0
> > #define SQLITE_DEFAULT_CACHE_SIZE   65536   // 256 MB
> > // #define SQLITE_DEFAULT_FILE_FORMAT  4   //
> default:
> > 4
> > // #define SQLITE_DEFAULT_FILE_PERMISSIONS 0644//
> default:
> > 0644
> > #define SQLITE_DEFAULT_FOREIGN_KEYS 1   // default:
> 0
> > // #define SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT   4194304 //
> default:
> > -1
> > // #define SQLITE_DEFAULT_LOCKING_MODE 0   //
> default:
> > 0
> > // #define SQLITE_DEFAULT_MEMSTATUS1   //
> default:
> > 1
> > #define SQLITE_DEFAULT_PAGE_SIZE4096// default:
> > 4096 max: 65536
> > // #define SQLITE_DEFAULT_SYNCHRONOUS  2   //
> default:
> > 2
> > // #define SQLITE_DEFAULT_WAL_SYNCHRONOUS  2   //
> default:
> > same as default synchronous
> > // #define SQLITE_DEFAULT_WORKER_THREADS   4   //
> default:
> > 0
> > #define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256 // default:
> > 1000 pages
> > #define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755// default:
> > 0755
> > #define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1   // default:
> 0
> > #define SQLITE_DEFAULT_TEMP_CACHE_SIZE  65536   // default:
> > 500 pages
> > #define SQLITE_DEFAULT_SHARED_CACHE 0   // default:
> 0
> > #define SQLITE_DEFAULT_MMAP_SIZE0   // default:
> 0
> > // #define SQLITE_LIKE_DOESNT_MATCH_BLOBS  1   //
> default:
> > undefined
> > // #define SQLITE_SORTER_PMASZ 64  //
> default:
> > 250
> > // #define SQLITE_EXTRA_DURABLE1   // Extra
> > DirSync's default not defined
> >
> >
> > // *** SQLITE FEATURE CONFIGURATION OPTIONS ***
> >
> > //#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
> > #define SQLITE_ENABLE_8_3_NAMES 1
> > // #define SQLITE_ENABLE_ATOMIC_WRITE 1
> > #define SQLITE_ENABLE_API_ARMOR 1   // Enable
> API
> > Armour
> > #define SQLITE_ENABLE_COLUMN_METADATA 1
> > #define SQLITE_ENABLE_COSTMULT 1
> > #define SQLITE_ENABLE_CURSOR_HINTS 1
> > #define SQLITE_ENABLE_DBSTAT_VTAB 1
> > #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
> > #define SQLITE_ENABLE_FTS3 1
> > #define SQLITE_ENABLE_FTS3_PARENTHESIS 1
> > // #define SQLITE_DISABLE_FTS3_UNICODE 1
> > #define SQLITE_ENABLE_FTS4 1
> > #define SQLITE_ENABLE_FTS5 1
> > // #define SQLITE_ENABLE_ICU 1 // Set in
> > BUILD Command additional Libs required
> > #define SQLITE_ENABLE_JSON1 1   // Enable
> > JSON1 -- when standard extension
> > #define SQLITE_ENABLE_LOAD_EXTENSION 1
> > #define SQLITE_ENABLE_LOCKING_STYLE 1
> > #define

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf


By default, I do not see the query being flattened.  Flattening it manually 
produces the same right results independant of something config.h does...

SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
1
2
3
sqlite> select i.id from i, m where i.id=m.id order by flags desc limit 3;
select i.id from i, m where i.id=m.id order by flags desc limit 3;
6
5
7

> Well I can reproduce the wrong answer.  If I compile the amalgamation
> code:
> 
>   with no config.h I get the same result as you,
>   but with the following config.h, the result is correct
> 
>  (this is with the code from the head of trunk):
> 
> #ifndef _CONFIG_H
> #define _CONFIG_H
> 
> // Values of WINVER and _WIN32_WINNT for various minimum levels of Win32
> Compatability
> //
> // WIN100x0A00  WIN6 0x0600  W2K  0x0500 NT4
> 0x0400
> //  VISTA0x0600  WXP  0x0501 W95
> 0x0400
> //  W2K8 0x0600  W2K3 0x0502 NT4E
> 0x0401
> //  WIN7 0x0601  W98
> 0x0410
> //  WIN8 0x0602  WME
> 0x0490
> //  WIN810x0603
> 
> #ifndef _WIN32_WINNT
> #define _WIN32_WINNT 0x0600
> #endif
> #ifndef WINVER
> #define WINVER _WIN32_WINNT
> #endif
> 
> // General Platform Compilation Support Options
> 
> #define HAVE_MALLOC_USABLE_SIZE 1
> #define HAVE_USLEEP 1
> 
> // *** SQLITE GENERAL CONFIGURATION OPTIONS ***
> 
> // #define SQLITE_DEFAULT_AUTOMATIC_INDEX  1   // default:
> 1
> // #define SQLITE_DEFAULT_AUTOVACUUM   0   // default:
> 0
> #define SQLITE_DEFAULT_CACHE_SIZE   65536   // 256 MB
> // #define SQLITE_DEFAULT_FILE_FORMAT  4   // default:
> 4
> // #define SQLITE_DEFAULT_FILE_PERMISSIONS 0644// default:
> 0644
> #define SQLITE_DEFAULT_FOREIGN_KEYS 1   // default: 0
> // #define SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT   4194304 // default:
> -1
> // #define SQLITE_DEFAULT_LOCKING_MODE 0   // default:
> 0
> // #define SQLITE_DEFAULT_MEMSTATUS1   // default:
> 1
> #define SQLITE_DEFAULT_PAGE_SIZE4096// default:
> 4096 max: 65536
> // #define SQLITE_DEFAULT_SYNCHRONOUS  2   // default:
> 2
> // #define SQLITE_DEFAULT_WAL_SYNCHRONOUS  2   // default:
> same as default synchronous
> // #define SQLITE_DEFAULT_WORKER_THREADS   4   // default:
> 0
> #define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256 // default:
> 1000 pages
> #define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755// default:
> 0755
> #define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1   // default: 0
> #define SQLITE_DEFAULT_TEMP_CACHE_SIZE  65536   // default:
> 500 pages
> #define SQLITE_DEFAULT_SHARED_CACHE 0   // default: 0
> #define SQLITE_DEFAULT_MMAP_SIZE0   // default: 0
> // #define SQLITE_LIKE_DOESNT_MATCH_BLOBS  1   // default:
> undefined
> // #define SQLITE_SORTER_PMASZ 64  // default:
> 250
> // #define SQLITE_EXTRA_DURABLE1   // Extra
> DirSync's default not defined
> 
> 
> // *** SQLITE FEATURE CONFIGURATION OPTIONS ***
> 
> //#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
> #define SQLITE_ENABLE_8_3_NAMES 1
> // #define SQLITE_ENABLE_ATOMIC_WRITE 1
> #define SQLITE_ENABLE_API_ARMOR 1   // Enable API
> Armour
> #define SQLITE_ENABLE_COLUMN_METADATA 1
> #define SQLITE_ENABLE_COSTMULT 1
> #define SQLITE_ENABLE_CURSOR_HINTS 1
> #define SQLITE_ENABLE_DBSTAT_VTAB 1
> #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
> #define SQLITE_ENABLE_FTS3 1
> #define SQLITE_ENABLE_FTS3_PARENTHESIS 1
> // #define SQLITE_DISABLE_FTS3_UNICODE 1
> #define SQLITE_ENABLE_FTS4 1
> #define SQLITE_ENABLE_FTS5 1
> // #define SQLITE_ENABLE_ICU 1 // Set in
> BUILD Command additional Libs required
> #define SQLITE_ENABLE_JSON1 1   // Enable
> JSON1 -- when standard extension
> #define SQLITE_ENABLE_LOAD_EXTENSION 1
> #define SQLITE_ENABLE_LOCKING_STYLE 1
> #define SQLITE_ENABLE_MEMORY_MANAGEMENT 1   // Enable
> Memory Management (sqlite3_release_memory)
> // #define SQLITE_ENABLE_MEMSYS3 1
> // #define SQLITE_ENABLE_MEMSYS5 1
> #define SQLITE_ENABLE_MODULE_COMMENTS 1
> #define SQLITE_ENABLE_PREUPDATE_HOOK 1
> // #define SQLITE_ENABLE_RBU 1 // Enable
> Resumable Bulk Update
> #define SQLITE_ENABLE_RTREE 1
> // #define SQLITE_RTREE_INT_ONLY 1
> // #define SQLITE_ENABLE_SESSION 1 // Enable
> the SESSION feature
> // #define SQLITE_ENABLE_SNAPSHOT 1// Enable
> the SNAPSHOT feature
> #define SQLITE_ENABLE_STAT_VTAB 1

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf


Well I can reproduce the wrong answer.  If I compile the amalgamation code:

  with no config.h I get the same result as you, 
  but with the following config.h, the result is correct

 (this is with the code from the head of trunk):

#ifndef _CONFIG_H
#define _CONFIG_H

// Values of WINVER and _WIN32_WINNT for various minimum levels of Win32 
Compatability
//
// WIN100x0A00  WIN6 0x0600  W2K  0x0500 NT4  0x0400
//  VISTA0x0600  WXP  0x0501 W95  0x0400
//  W2K8 0x0600  W2K3 0x0502 NT4E 0x0401
//  WIN7 0x0601  W98  0x0410
//  WIN8 0x0602  WME  0x0490
//  WIN810x0603

#ifndef _WIN32_WINNT
#define _WIN32_WINNT 0x0600
#endif
#ifndef WINVER
#define WINVER _WIN32_WINNT
#endif

// General Platform Compilation Support Options

#define HAVE_MALLOC_USABLE_SIZE 1
#define HAVE_USLEEP 1

// *** SQLITE GENERAL CONFIGURATION OPTIONS ***

// #define SQLITE_DEFAULT_AUTOMATIC_INDEX  1   // default: 1
// #define SQLITE_DEFAULT_AUTOVACUUM   0   // default: 0
#define SQLITE_DEFAULT_CACHE_SIZE   65536   // 256 MB
// #define SQLITE_DEFAULT_FILE_FORMAT  4   // default: 4
// #define SQLITE_DEFAULT_FILE_PERMISSIONS 0644// default: 0644
#define SQLITE_DEFAULT_FOREIGN_KEYS 1   // default: 0
// #define SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT   4194304 // default: -1
// #define SQLITE_DEFAULT_LOCKING_MODE 0   // default: 0
// #define SQLITE_DEFAULT_MEMSTATUS1   // default: 1
#define SQLITE_DEFAULT_PAGE_SIZE4096// default: 4096 
max: 65536
// #define SQLITE_DEFAULT_SYNCHRONOUS  2   // default: 2
// #define SQLITE_DEFAULT_WAL_SYNCHRONOUS  2   // default: same 
as default synchronous
// #define SQLITE_DEFAULT_WORKER_THREADS   4   // default: 0
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256 // default: 1000 
pages
#define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755// default: 0755
#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1   // default: 0
#define SQLITE_DEFAULT_TEMP_CACHE_SIZE  65536   // default: 500 
pages
#define SQLITE_DEFAULT_SHARED_CACHE 0   // default: 0
#define SQLITE_DEFAULT_MMAP_SIZE0   // default: 0
// #define SQLITE_LIKE_DOESNT_MATCH_BLOBS  1   // default: 
undefined
// #define SQLITE_SORTER_PMASZ 64  // default: 250
// #define SQLITE_EXTRA_DURABLE1   // Extra 
DirSync's default not defined


// *** SQLITE FEATURE CONFIGURATION OPTIONS ***

//#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
#define SQLITE_ENABLE_8_3_NAMES 1
// #define SQLITE_ENABLE_ATOMIC_WRITE 1
#define SQLITE_ENABLE_API_ARMOR 1   // Enable API Armour
#define SQLITE_ENABLE_COLUMN_METADATA 1
#define SQLITE_ENABLE_COSTMULT 1
#define SQLITE_ENABLE_CURSOR_HINTS 1
#define SQLITE_ENABLE_DBSTAT_VTAB 1
#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
#define SQLITE_ENABLE_FTS3 1
#define SQLITE_ENABLE_FTS3_PARENTHESIS 1
// #define SQLITE_DISABLE_FTS3_UNICODE 1
#define SQLITE_ENABLE_FTS4 1
#define SQLITE_ENABLE_FTS5 1
// #define SQLITE_ENABLE_ICU 1 // Set in BUILD 
Command additional Libs required
#define SQLITE_ENABLE_JSON1 1   // Enable JSON1 -- 
when standard extension
#define SQLITE_ENABLE_LOAD_EXTENSION 1
#define SQLITE_ENABLE_LOCKING_STYLE 1
#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1   // Enable Memory 
Management (sqlite3_release_memory)
// #define SQLITE_ENABLE_MEMSYS3 1
// #define SQLITE_ENABLE_MEMSYS5 1
#define SQLITE_ENABLE_MODULE_COMMENTS 1
#define SQLITE_ENABLE_PREUPDATE_HOOK 1
// #define SQLITE_ENABLE_RBU 1 // Enable 
Resumable Bulk Update
#define SQLITE_ENABLE_RTREE 1
// #define SQLITE_RTREE_INT_ONLY 1
// #define SQLITE_ENABLE_SESSION 1 // Enable the 
SESSION feature
// #define SQLITE_ENABLE_SNAPSHOT 1// Enable the 
SNAPSHOT feature
#define SQLITE_ENABLE_STAT_VTAB 1   // Enable 
dbstat_register called from shell
#define SQLITE_ENABLE_STAT1 1
#define SQLITE_ENABLE_STAT2 1
#define SQLITE_ENABLE_STAT3 1
#define SQLITE_ENABLE_STAT4 1
// #define SQLITE_ENABLE_VFSSTAT 1 // Enable 
vftstat extension
// #define SQLITE_LIKE_DOESNT_MATCH_BLOBS  1   // default: 0 
(undefined)
#define SQLITE_STAT4_SAMPLES 64 // default: 24 
samples
// #define SQLITE_64BIT_STATS 1
// #define SQLITE_ENABLE_UNLOCK_NOTIFY 1   // See 
Documentation before enabling
// #define

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread David Empson

Following up: same for the Mac distribution of 3.14.2 command line tool. Using 
the pre-release snapshot of 3.15.0 from the main download page to build the 
sqlite3 3.15.0 on the Mac, I get the same answer: 1,2,3.

SQLite version 3.15.0 2016-10-10 14:34:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read test.sql
select sqlite_source_id();
2016-10-10 14:34:00 aebe429e52ffef026cb0803fb164339d61bd2e88
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
1
2
3


> On 12/10/2016, at 1:23 PM, David Empson  wrote:
> 
> Keith - using your example, I get the same result as Tobias: the second 
> select produces 1,2,3. This is with the sqlite3.exe Windows command line tool 
> for SQLite 3.14.2 downloaded from sqlite.org. Same sqlite_source_id() too. 
> I’m not set up to build SQLite from source, so can’t easily test 3.15.0, but 
> If I do the same with a copy of 3.8.11.1 I have handy I get the correct 
> result: 6, 5, 7.
> 
> SQLite version 3.14.2 2016-09-12 18:50:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .read test.sql
> select sqlite_source_id();
> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> create table i (id integer primary key autoincrement, flags integer);
> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> create table m (id integer);
> insert into m values (1),(2),(3),(4),(5),(6),(7);
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
> 1
> 2
> 3
> 
>> On 12/10/2016, at 12:59 PM, Keith Medcalf  wrote:
>> 
>> SQLite version 3.14.2 2016-09-12 18:50:49
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .read \\test.sql
>> select sqlite_source_id();
>> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
>> create table i (id integer primary key autoincrement, flags integer);
>> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
>> create table m (id integer);
>> insert into m values (1),(2),(3),(4),(5),(6),(7);
>> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
>> 6
>> 5
>> 7
>> 1
>> 2
>> 3
>> 4
>> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
>> 6
>> 5
>> 7
>> 
>> I just compiled 3.14.2 from my source repository and it works correctly.  
>> 
>> Can you check the result of "select sqlite_source_id();", which should be:
>> 
>> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
>> 
>> https://www.sqlite.org/src/info/29dbef4b8585f753
>> 
>>> -Original Message-
>>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>>> On Behalf Of Tobias Ellinghaus
>>> Sent: Tuesday, 11 October, 2016 11:41
>>> To: sqlite-users@mailinglists.sqlite.org
>>> Subject: Re: [sqlite] LIMIT doesn't return expected rows
>>> 
>>> Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
 This was fixed September 7.  The fix appears in 3.14.2 and also on the
 current 3.15.0.
>>> 
>>> Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the
>>> last query? I am asking as that's the version I am using (installed from
>>> Debian/sid) and I get "1, 2, 3" here.
>>> 
 https://www.sqlite.org/releaselog/3_14_2.html
 
 The ORDER BY LIMIT optimization is not valid unless the inner-most IN
 operator loop is actually used by the query plan. Ticket
 https://sqlite.org/src/info/0c4df46116e90f92
 
 
 SQLite version 3.15.0 2016-10-10 14:48:36
 Enter ".help" for usage hints.
 Connected to a transient in-memory database.
 Use ".open FILENAME" to reopen on a persistent database.
 sqlite> create table i (id integer primary key, flags integer);
 sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
 sqlite> create table m (id integer);
 sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
 sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
>>> DESC;
 6
 5
 7
 1
 2
 3
 4
 sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
>>> DESC
 limit 3; 6
 5
 7
>>> 
>>> Tobias
>>> 
>>> [...]
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>>

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread David Empson

Keith - using your example, I get the same result as Tobias: the second select 
produces 1,2,3. This is with the sqlite3.exe Windows command line tool for 
SQLite 3.14.2 downloaded from sqlite.org. Same sqlite_source_id() too. I’m not 
set up to build SQLite from source, so can’t easily test 3.15.0, but If I do 
the same with a copy of 3.8.11.1 I have handy I get the correct result: 6, 5, 7.

SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read test.sql
select sqlite_source_id();
2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
1
2
3

> On 12/10/2016, at 12:59 PM, Keith Medcalf  wrote:
> 
> SQLite version 3.14.2 2016-09-12 18:50:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .read \\test.sql
> select sqlite_source_id();
> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> create table i (id integer primary key autoincrement, flags integer);
> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> create table m (id integer);
> insert into m values (1),(2),(3),(4),(5),(6),(7);
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
> 6
> 5
> 7
> 
> I just compiled 3.14.2 from my source repository and it works correctly.  
> 
> Can you check the result of "select sqlite_source_id();", which should be:
> 
> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> 
> https://www.sqlite.org/src/info/29dbef4b8585f753
> 
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Tobias Ellinghaus
>> Sent: Tuesday, 11 October, 2016 11:41
>> To: sqlite-users@mailinglists.sqlite.org
>> Subject: Re: [sqlite] LIMIT doesn't return expected rows
>> 
>> Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
>>> This was fixed September 7.  The fix appears in 3.14.2 and also on the
>>> current 3.15.0.
>> 
>> Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the
>> last query? I am asking as that's the version I am using (installed from
>> Debian/sid) and I get "1, 2, 3" here.
>> 
>>> https://www.sqlite.org/releaselog/3_14_2.html
>>> 
>>> The ORDER BY LIMIT optimization is not valid unless the inner-most IN
>>> operator loop is actually used by the query plan. Ticket
>>> https://sqlite.org/src/info/0c4df46116e90f92
>>> 
>>> 
>>> SQLite version 3.15.0 2016-10-10 14:48:36
>>> Enter ".help" for usage hints.
>>> Connected to a transient in-memory database.
>>> Use ".open FILENAME" to reopen on a persistent database.
>>> sqlite> create table i (id integer primary key, flags integer);
>>> sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
>>> sqlite> create table m (id integer);
>>> sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
>>> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
>> DESC;
>>> 6
>>> 5
>>> 7
>>> 1
>>> 2
>>> 3
>>> 4
>>> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
>> DESC
>>> limit 3; 6
>>> 5
>>> 7
>> 
>> Tobias
>> 
>> [...]
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf

SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read \\test.sql
select sqlite_source_id();
2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
6
5
7

I just compiled 3.14.2 from my source repository and it works correctly.  

Can you check the result of "select sqlite_source_id();", which should be:

2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6

https://www.sqlite.org/src/info/29dbef4b8585f753

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Tobias Ellinghaus
> Sent: Tuesday, 11 October, 2016 11:41
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] LIMIT doesn't return expected rows
> 
> Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
> > This was fixed September 7.  The fix appears in 3.14.2 and also on the
> > current 3.15.0.
> 
> Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the
> last query? I am asking as that's the version I am using (installed from
> Debian/sid) and I get "1, 2, 3" here.
> 
> > https://www.sqlite.org/releaselog/3_14_2.html
> >
> > The ORDER BY LIMIT optimization is not valid unless the inner-most IN
> > operator loop is actually used by the query plan. Ticket
> > https://sqlite.org/src/info/0c4df46116e90f92
> >
> >
> > SQLite version 3.15.0 2016-10-10 14:48:36
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> create table i (id integer primary key, flags integer);
> > sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> > sqlite> create table m (id integer);
> > sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
> > sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
> DESC;
> > 6
> > 5
> > 7
> > 1
> > 2
> > 3
> > 4
> > sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
> DESC
> > limit 3; 6
> > 5
> > 7
> 
> Tobias
> 
> [...]



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

Re: [sqlite] Using nested SELECT with json_each()

2016-10-11 Thread Jens Alfke

Thanks much! 

(I’d forgotten about EXPLAIN QUERY PLAN — I had tried regular EXPLAIN, but the 
long list of virtual-machine instructions was too much for my brain.)

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

Re: [sqlite] Using nested SELECT with json_each()

2016-10-11 Thread Clemens Ladisch

Jens Alfke wrote:
>   SELECT DISTINCT user.name
>   FROM user, json_each(user.phone)
>   WHERE json_each.value LIKE '704-%';

0|0|0|SCAN TABLE user
0|1|1|SCAN TABLE json_each VIRTUAL TABLE INDEX 1:
0|0|0|USE TEMP B-TREE FOR DISTINCT

> For my purposes it seems cleaner to use a nested SELECT with EXISTS:
>
>   SELECT user.name
>   FROM user
>   WHERE EXISTS( SELECT 1 FROM json_each(user.phone) WHERE json_each.value 
> like ‘704-%’ )

0|0|0|SCAN TABLE user
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE json_each VIRTUAL TABLE INDEX 1:

> Is this going to be any less efficient?

The join and the correlated subquery are executed in the same way.

The first query stores all unique name values in a temporary index; the
second query is more efficient because it not only avoids this, but also
because the EXISTS implies a LIMIT 1.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Dan Kennedy


On 10/11/2016 07:57 PM, Simon Slavin wrote:

On 11 Oct 2016, at 1:44pm, Eric Minbiole  wrote:


Your problem is that although SQL accepts the clauses written in the order
you wrote them in, the LIMIT clause is processed before the ORDER BY clause.

Is that really true? I had always thought that the ORDER BY was processed
first, though I admit I don't see anything authoritative either way.

I didn't know.  Which is why I wrote about what was happening rather than what 
a specification said should happen.  The order in which these clauses are 
listed in the SQLite documentation suggests that ORDER BY should be performed 
/before/ LIMIT.


This is correct. In an SQL SELECT, the sorting is done before the limiting.

Dan.

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

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Tobias Ellinghaus

Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
> This was fixed September 7.  The fix appears in 3.14.2 and also on the
> current 3.15.0.

Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the 
last query? I am asking as that's the version I am using (installed from 
Debian/sid) and I get "1, 2, 3" here.

> https://www.sqlite.org/releaselog/3_14_2.html
> 
> The ORDER BY LIMIT optimization is not valid unless the inner-most IN
> operator loop is actually used by the query plan. Ticket
> https://sqlite.org/src/info/0c4df46116e90f92
> 
> 
> SQLite version 3.15.0 2016-10-10 14:48:36
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table i (id integer primary key, flags integer);
> sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> sqlite> create table m (id integer);
> sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC
> limit 3; 6
> 5
> 7

Tobias

[...]

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ajqvue Version 1.10 Released

2016-10-11 Thread dmp

Ajqvue Version 1.10 Released

The Ajqvue project is pleased to release v1.10 to the public. The
release marks a complete code review and cleanup. Updated libraries have
also been included with this release along with the Table Field Profiler
plugin. There have been many fixes in this release that stabilizes the
application.

The Ajqvue application is the recent result of replacing a 11 year old
Open Source program that has been challenged by a major player in the
database market for trademark infringement. Unable to defend the project
from such a mammoth that project is now dead, and replaced with Ajqvue.
As a result much of the old project as now been consolidated and resulted
in a much cleaner code base and website. An overview video of the
capabilities of the application is available at the link below. Ajqvue
uses the SQLite JDBC Driver and is included as a library with the
application.

http://ajqvue.com/docs/Videos/Ajqvue_Overview.mp4
https://github.com/xerial/sqlite-jdbc

Dana M. Proctor
Ajqvue Project Manager
http://ajqvue.com

Ajqvue provides an easy to use Java based user interface front-end
for viewing, adding, editing, or deleting entries in several mainstream
databases. A query frame allows the building of complex SQL statements
and a SQL Query Bucket for saving such. The application allows easy
sorting, searching, and import/export of table data. A plug-in framework
has allowed the inclusion of tools to visually build queries, profile and
plot data for analysis.

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

Re: [sqlite] Backward compatibility of indexes with "WHERE function()"

2016-10-11 Thread David Raymond

http://www.gocomics.com/pearlsbeforeswine/2016/06/22

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Sunday, October 09, 2016 4:33 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Backward compatibility of indexes with "WHERE function()"

...

Language rules are never quite strict rules... it's just a description 
and rule-based understanding of how people speak, and if that changes 
over time, so must the rules that describe it.
I cringe to think it, but probably some day these sentence parts would 
be found in an actual dictionary:
"And I was like, r u serius? And she wuz all like - whaeva!, l8er m8."

Fun. :)
Ryan

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

[sqlite] Using nested SELECT with json_each()

2016-10-11 Thread Jens Alfke

I’m writing code to generate SQL queries that use json_each to test the 
contents of a JSON array. The docs give this as an example:

SELECT DISTINCT user.name
FROM user, json_each(user.phone)
WHERE json_each.value LIKE '704-%';

I’m not happy with this, as it requires the use of DISTINCT to avoid producing 
duplicate results when a row has more than one matching array value, and it it 
complicates my SQL-generation code to have to add arbitrary numbers of ‘tables’ 
to the FROM clause (I might have to do multiple tests on multiple arrays in the 
JSON.)

For my purposes it seems cleaner to use a nested SELECT with EXISTS:

SELECT user.name
FROM user
WHERE EXISTS( SELECT 1 FROM json_each(user.phone) WHERE json_each.value 
like ‘704-%’ )

Is this going to be any less efficient? Or is there any other reason not to use 
it?

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

Re: [sqlite] display also the average score of a flim

2016-10-11 Thread Igor Tandetnik


On 10/9/2016 9:01 AM, Roelof Wobben wrote:

I have this sql script to display the top25 fiims based on the average
score of a flim.


I don't see where score figures in your query. You order films by the 
number of genres each is associated with. Is that what you meant?



Now I wonder if it's possible to also display the average score of a film.


Have you tried the obvious?

SELECT films.*, AVG(films_genres.score) ...

--
Igor Tandetnik

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

Re: [sqlite] Parallel access to read only in memory database

2016-10-11 Thread Howard Chu


Jens Alfke wrote:



On Oct 9, 2016, at 10:41 AM, Howard Chu  wrote:

As for code freshness, I've seen no compelling new features from 3.8.x onward 
that would improve performance so there's been no reason to update further.


Perhaps, but there’s important new functionality in newer versions, such as 
partial indexes and indexes on expressions.

If it’s a personal project for you, or a tech demo, then it’s understandable 
for you to drop it when it stops being interesting; but if this is something 
intended for other people to use, they’re going to want to see it supported 
going forward (the way SQLCipher is, for instance.) It would be nice to add a 
note to the readme saying something like “FYI, this is based on SQLite 3.7.17 
and there are no plans to sync with newer versions.”


As with any open source project, if users want to see something change, the 
onus is on them to initiate those changes. Nobody associated with an open 
source project is ever obligated to proactively implement anything. 
particularly things that no user has asked for yet. SQLightning has many 
satisfied users already and none of them have requested the features you 
mentioned.


As for myself, it works for me in my personal builds of Mozilla Seamonkey.

Lack of development activity on a project doesn't mean it's dead and disused - 
it just means the project has accomplished its goals and its users are content.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Simon Slavin

On 11 Oct 2016, at 1:44pm, Eric Minbiole  wrote:

>> Your problem is that although SQL accepts the clauses written in the order
>> you wrote them in, the LIMIT clause is processed before the ORDER BY clause.
> 
> Is that really true? I had always thought that the ORDER BY was processed
> first, though I admit I don't see anything authoritative either way.

I didn't know.  Which is why I wrote about what was happening rather than what 
a specification said should happen.  The order in which these clauses are 
listed in the SQLite documentation suggests that ORDER BY should be performed 
/before/ LIMIT.

I did find this:

"If you combine LIMIT row_count with ORDER BY, MySQL ends the sorting as soon 
as it has found the first row_count rows of the sorted result"

Note the word 'sorted'.  This agrees with what I wrote above, in that it 
suggest that in MySQL ORDER BY must be processed first.

I'm happy to see that Keith says that the behaviour noted by Tobias was a bug 
and has been fixed.  I also note that a test for this combination of clauses 
appears in the test suite, so the bug shouldn't happen again.

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

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf


This was fixed September 7.  The fix appears in 3.14.2 and also on the current 
3.15.0.

https://www.sqlite.org/releaselog/3_14_2.html

The ORDER BY LIMIT optimization is not valid unless the inner-most IN operator 
loop is actually used by the query plan. Ticket 
https://sqlite.org/src/info/0c4df46116e90f92


SQLite version 3.15.0 2016-10-10 14:48:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table i (id integer primary key, flags integer);
sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
sqlite> create table m (id integer);
sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC 
limit 3;
6
5
7

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Tobias Ellinghaus
> Sent: Tuesday, 11 October, 2016 04:53
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] LIMIT doesn't return expected rows
> 
> Hello,
> 
> first let me mention that I am new to this list so apologies if my
> question
> came up before. I couldn't find anything though, and in #sqlite on
> Freenode I
> was pointed here, so here I am.
> 
> I am working on some code that creates a (potentially big) SQL query on
> the
> fly. My problem is, that I need to get a certain subset of the result the
> query
> gives. However, that somehow doesn't work with sqlite 3.14.2. In 3.8.5 it
> still worked according to someone on IRC testing it.
> 
> Example database:
> 
> sqlite> .dump i
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE i (id integer primary key autoincrement, flags integer);
> INSERT INTO "i" VALUES(1,1);
> INSERT INTO "i" VALUES(2,1);
> INSERT INTO "i" VALUES(3,1);
> INSERT INTO "i" VALUES(4,1);
> INSERT INTO "i" VALUES(5,5);
> INSERT INTO "i" VALUES(6,6);
> INSERT INTO "i" VALUES(7,4);
> COMMIT;
> sqlite> .dump m
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE m (id integer);
> INSERT INTO "m" VALUES(1);
> INSERT INTO "m" VALUES(2);
> INSERT INTO "m" VALUES(3);
> INSERT INTO "m" VALUES(4);
> INSERT INTO "m" VALUES(5);
> INSERT INTO "m" VALUES(6);
> INSERT INTO "m" VALUES(7);
> COMMIT;
> 
> Simplified example query – in reality there would be a couple of those
> inner
> SELECT which makes it hard to use JOIN.
> 
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
> DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> 
> Now I only want the first three values, 6, 5 and 7. However:
> 
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
> DESC
> LIMIT 0, 3;
> 1
> 2
> 3
> 
> It almost seems to limit the inner SELECT instead of the outer one.
> 
> Is that a bug in recent versions of sqlite? Or was that a bug before and I
> am
> doing something wrong?
> 
> Thanks for any insight and maybe a hint how to get what I want
> Tobias



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

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Eric Minbiole

> Your problem is that although SQL accepts the clauses written in the order
> you wrote them in, the LIMIT clause is processed before the ORDER BY clause.
>
>
Is that really true? I had always thought that the ORDER BY was processed
first, though I admit I don't see anything authoritative either way.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Simon Slavin

On 11 Oct 2016, at 11:52am, Tobias Ellinghaus  wrote:

> Now I only want the first three values, 6, 5 and 7. However:
> 
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC 
> LIMIT 0, 3;
> 1
> 2
> 3

Your problem is that although SQL accepts the clauses written in the order you 
wrote them in, the LIMIT clause is processed before the ORDER BY clause.

sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) LIMIT 0, 3;
1
2
3
sqlite> 

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

[sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Tobias Ellinghaus

Hello,

first let me mention that I am new to this list so apologies if my question 
came up before. I couldn't find anything though, and in #sqlite on Freenode I 
was pointed here, so here I am.

I am working on some code that creates a (potentially big) SQL query on the 
fly. My problem is, that I need to get a certain subset of the result the query 
gives. However, that somehow doesn't work with sqlite 3.14.2. In 3.8.5 it 
still worked according to someone on IRC testing it.

Example database:

sqlite> .dump i
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE i (id integer primary key autoincrement, flags integer);
INSERT INTO "i" VALUES(1,1);
INSERT INTO "i" VALUES(2,1);
INSERT INTO "i" VALUES(3,1);
INSERT INTO "i" VALUES(4,1);
INSERT INTO "i" VALUES(5,5);
INSERT INTO "i" VALUES(6,6);
INSERT INTO "i" VALUES(7,4);
COMMIT;
sqlite> .dump m
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE m (id integer);
INSERT INTO "m" VALUES(1);
INSERT INTO "m" VALUES(2);
INSERT INTO "m" VALUES(3);
INSERT INTO "m" VALUES(4);
INSERT INTO "m" VALUES(5);
INSERT INTO "m" VALUES(6);
INSERT INTO "m" VALUES(7);
COMMIT;

Simplified example query – in reality there would be a couple of those inner 
SELECT which makes it hard to use JOIN.

sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4

Now I only want the first three values, 6, 5 and 7. However:

sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC 
LIMIT 0, 3;
1
2
3

It almost seems to limit the inner SELECT instead of the outer one.

Is that a bug in recent versions of sqlite? Or was that a bug before and I am 
doing something wrong?

Thanks for any insight and maybe a hint how to get what I want
Tobias

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite db version questions

2016-10-11 Thread Simon Slavin


On 11 Oct 2016, at 9:52am, Werner Kleiner  wrote:

> I am a little bit confused about the sqlite db version numbers.

The version numbers you are seeing reported are not the version of the database 
file.  All those databases are in the format for SQLite version 3.  Instead the 
version numbers reported are the version of SQLite compiled into the program 
you're using to find out the version number.

SQLite is not a library or service stored on a central place on your computer.  
Each program which uses SQLite has SQLite compiled into it.  So you can have 
six different programs on your computer, and they can each implement slightly 
different versions of SQLite.

And that would be fine because no significant changes to the SQLite file format 
have been made for years.  As long as they are all contain version 3.0 or above 
of the source code, they can all read and write each-other's version 3.0 file 
format.

There are some features which have been introduced since version 3 file format 
was created.  For instance, in August 2013 partial indexes were introduced in 
SQLite version 3.8.0.  But if a particular database file does not use partial 
indexes, then even if it was created with a version later than that date, it 
can still be read and written using a version earlier than that date.

Hope this helps.

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

[sqlite] Sqlite db version questions

2016-10-11 Thread Werner Kleiner

Hello,
I am a little bit confused about the sqlite db version numbers.

1. On Windows 7 I have created a new db with command
sqlite3.exe test.db3
The sqlite3.exe is from date 2013-05-20 and the version number shows 3.7.17

2. Same computer but now I create a db with sqlite3.exe from 2016-09-12
 sqlite3.exe test1.db3
 The version number now shows 3.14.2

3. I open both db (test.db3 and test1.db3) with sqlitestudio 3.0.6 and do a:
Select sqlite_version(),
Now for both dbs the version number shows 3.8.10

So from what depends the version number? From the tool or program
which opens the db?

If I create a new db like in step 2 where I have version 3.14.2 and I
open it with an old
tool or program, then I cannot use the newer functions and fixes of
the new version?

Can someone please give me a little bit clearness?
thanks
Werner
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] display also the average score of a flim

2016-10-11 Thread Roelof Wobben


Hello,

I have this sql script to display the top25 fiims based on the average 
score of a flim.


SELECT films.*
FROM films
JOIN films_genres on films_genres.genre_id in (#{all_genres.join(' , ')})
WHERE films.id = films_genres.film_id and films.id !=2 AND 
count(films_genres.score) > 0

GROUP BY films.id
ORDER BY count(films_genres.film_id) DESC"

Now I wonder if it's possible to also display the average score of a film.


Regards,

Roelof

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

Re: [sqlite] Backward cursor support?

[sqlite] Backward cursor support?

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] Using nested SELECT with json_each()

Re: [sqlite] Using nested SELECT with json_each()

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] LIMIT doesn't return expected rows

[sqlite] Ajqvue Version 1.10 Released

Re: [sqlite] Backward compatibility of indexes with "WHERE function()"

[sqlite] Using nested SELECT with json_each()

Re: [sqlite] display also the average score of a flim

Re: [sqlite] Parallel access to read only in memory database

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] LIMIT doesn't return expected rows

Re: [sqlite] LIMIT doesn't return expected rows

[sqlite] LIMIT doesn't return expected rows

Re: [sqlite] Sqlite db version questions

[sqlite] Sqlite db version questions

[sqlite] display also the average score of a flim

25 matches

Site Navigation

Mail list logo

Footer information