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

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

2016-10-12 Thread Tobias Ellinghaus

Am Mittwoch, 12. Oktober 2016, 12:11:11 CEST schrieb Richard Hipp:
> On 10/11/16, Keith Medcalf  wrote:
> > #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
> > 
> > makes it work properly.  neither NDEBUG nor SQLITE_DEBUG explicitly
> > defined.
> > 
> > Over to Richard ...
> 
> Should now be fixed on trunk and in the latest Prerelease Snapshot at
> https://sqlite.org/download.html

Thank you very much for the quick help. Much appreciated. :-)

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] LIMIT doesn't return expected rows

2016-10-12 Thread David Empson

Works for me building the sqlite3 command line tool from the prerelease 
snapshot, on both Mac and Windows.

SQLite version 3.15.0 2016-10-12 15:15:30
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-12 15:15:30 61f0526978af667781c57bcc87510e4524efd0d8
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

> On 13/10/2016, at 5:11 AM, Richard Hipp  wrote:
> 
> On 10/11/16, Keith Medcalf  wrote:
>> 
>> #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
>> 
>> makes it work properly.  neither NDEBUG nor SQLITE_DEBUG explicitly defined.
>> 
>> Over to Richard ...
> 
> Should now be fixed on trunk and in the latest Prerelease Snapshot at
> https://sqlite.org/download.html
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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-12 Thread Richard Hipp

On 10/11/16, Keith Medcalf  wrote:
>
> #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
>
> makes it work properly.  neither NDEBUG nor SQLITE_DEBUG explicitly defined.
>
> Over to Richard ...

Should now be fixed on trunk and in the latest Prerelease Snapshot at
https://sqlite.org/download.html

-- 
D. Richard Hipp
d...@sqlite.org
___
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 SQLI

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

2016-10-11 Thread Keith Medcalf

 // Use
> Default System Heap (default if no other specified)
> // #define SQLITE_MALLOC_SOFT_LIMIT 1024
> // #define SQLITE_POWERSAFE_OVERWRITE 0
> // #define SQLITE_4_BYTE_ALIGNED_MALLOC 1
> // #define SQLITE_USE_ALLOCA 1 // Use
> AllocA to Allocate Parse object os Stack
> #define SQLITE_USE_QUADMATH 1   // Use 128-bit
> Floats if available
> 
> 
> // *** SQLITE OMIT FEATURES ***
> 
> // #define SQLITE_OMIT_ALTERTABLE
> // #define SQLITE_OMIT_ANALYZE
> // #define SQLITE_OMIT_ATTACH
> // #define SQLITE_OMIT_AUTHORIZATION
> // #define SQLITE_OMIT_AUTOINCREMENT
> // #define SQLITE_OMIT_AUTOINIT
> // #define SQLITE_OMIT_AUTOMATIC_INDEX
> // #define SQLITE_OMIT_AUTORESET
> // #define SQLITE_OMIT_AUTOVACUUM
> // #define SQLITE_OMIT_BETWEEN_OPTIMIZATION
> // #define SQLITE_OMIT_BLOB_LITERAL
> // #define SQLITE_OMIT_BTREECOUNT
> // #define SQLITE_OMIT_BUILTIN_TEST
> // #define SQLITE_OMIT_CAST
> // #define SQLITE_OMIT_CHECK
> // #define SQLITE_OMIT_COMPILEOPTION_DIAGS
> // #define SQLITE_OMIT_COMPLETE
> // #define SQLITE_OMIT_COMPOUND_SELECT
> // #define SQLITE_OMIT_DATETIME_FUNCS
> // #define SQLITE_OMIT_DECLTYPE
> // #define SQLITE_OMIT_DEPRECATED
> // #define SQLITE_OMIT_DISKIO
> // #define SQLITE_OMIT_EXPLAIN
> // #define SQLITE_OMIT_FLAG_PRAGMAS
> // #define SQLITE_OMIT_FLOATING_POINT
> // #define SQLITE_OMIT_FOREIGN_KEY
> // #define SQLITE_OMIT_GET_TABLE
> // #define SQLITE_OMIT_INCRBLOB
> // #define SQLITE_OMIT_INTEGRITY_CHECK
> // #define SQLITE_OMIT_LIKE_OPTIMIZATION
> // #define SQLITE_OMIT_LOAD_EXTENSION
> // #define SQLITE_OMIT_LOCALTIME
> // #define SQLITE_OMIT_LOOKASIDE
> // #define SQLITE_OMIT_MEMORYDB
> // #define SQLITE_OMIT_MERGE_SORT
> // #define SQLITE_OMIT_OR_OPTIMIZATION
> // #define SQLITE_OMIT_PAGER_PRAGMAS
> // #define SQLITE_OMIT_PRAGMA
> // #define SQLITE_OMIT_PROGRESS_CALLBACK
> // #define SQLITE_OMIT_QUICKBALANCE
> // #define SQLITE_OMIT_REINDEX
> // #define SQLITE_OMIT_SCHEMA_PRAGMAS
> // #define SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
> // #define SQLITE_OMIT_SHARED_CACHE
> // #define SQLITE_OMIT_SHUTDOWN_DIRECTORIES
> // #define SQLITE_OMIT_SUBQUERY
> // #define SQLITE_OMIT_TCL_VARIABLE
> // #define SQLITE_OMIT_TEMPDB
> // #define SQLITE_OMIT_TRACE
> // #define SQLITE_OMIT_TRIGGER
> // #define SQLITE_OMIT_TRUNCATE_OPTIMIZATION
> // #define SQLITE_OMIT_UTF16
> // #define SQLITE_OMIT_VACUUM
> // #define SQLITE_OMIT_VIEW
> // #define SQLITE_OMIT_VIRTUALTABLE
> // #define SQLITE_OMIT_WAL
> // #define SQLITE_OMIT_WSD
> // #define SQLITE_OMIT_XFER_OPT
> 
> // *** SQLITE DEBUGGING FEATURES ***
> 
> // #define SQLITE_DEBUG 1
> // #define SQLITE_ENABLE_EXPENSIVE_ASSERT 1
> // #define SQLITE_ENABLE_OVERSIZE_CELL_CHECK 1
> // #define SQLITE_ENABLE_SELECTTRACE 1 // Enable
> Select Trace (.selecttrace 0x100) needs SQLITE_DEBUG
> // #define SQLITE_ENABLE_SQLLOG 1  // Enable
> SQLITE_CONFIG_SQLLOG (see documentation)
> // #define SQLITE_ENABLE_STMT_SCANSTATUS 1 // Enable
> Collection of Statement Scan Status
> // #define SQLITE_ENABLE_WHERETRACE 1
> // #define SQLITE_IOTRACE 1
> // #define SQLITE_MEMDEBUG 1
> // #define SQLITE_REVERSE_UNORDERED_SELECTS 1
> // #define SQLITE_USE_FCNTL_TRACE 1// Enable
> extra vfslog fcntrl trace
> // #define SQLITE_YYTRACKMAXSTACKDEPTH 1
> 
> #if defined(_WIN32) && defined(__GNUC__)
> #define UNICODE_STRING_MAX_BYTES ((WORD) 65534)
> #define UNICODE_STRING_MAX_CHARS (32767)
> #define HAVE_FDATASYNC 1
> #define HAVE_GMTIME_R 1
> #define HAVE_LOCALTIME_S 1
> #define HAVE_ISNAN 1
> #define HAVE_MALLOC_USABLE_SIZE 1
> #define HAVE_USLEEP 1
> #define HAVE_UTIME 1
> #endif
> 
> #if defined(_MSC_VER)
> #define HAVE_FDATASYNC 1
> #define HAVE_GMTIME_R 1
> #define HAVE_LOCALTIME_S 1
> #define HAVE_MALLOC_USABLE_SIZE 1
> #define HAVE_USLEEP 1
> #define HAVE_UTIME 1
> #endif
> 
> #if defined(__GNUC__) && defined(SQLITE_USE_QUADMATH)
> #define LONGDOUBLE_TYPE __float128
> #endif
> #endif
> 
> 
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of David Empson
> > Sent: Tuesday, 11 October, 2016 18:41
> > To: SQLite mailing list
> > Subject: Re: [sqlite] LIMIT doesn't return expected rows
> >
> > 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.
> >
> > SQLit

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

2016-10-11 Thread Keith Medcalf

// #define SQLITE_OMIT_INTEGRITY_CHECK
// #define SQLITE_OMIT_LIKE_OPTIMIZATION
// #define SQLITE_OMIT_LOAD_EXTENSION
// #define SQLITE_OMIT_LOCALTIME
// #define SQLITE_OMIT_LOOKASIDE
// #define SQLITE_OMIT_MEMORYDB
// #define SQLITE_OMIT_MERGE_SORT
// #define SQLITE_OMIT_OR_OPTIMIZATION
// #define SQLITE_OMIT_PAGER_PRAGMAS
// #define SQLITE_OMIT_PRAGMA
// #define SQLITE_OMIT_PROGRESS_CALLBACK
// #define SQLITE_OMIT_QUICKBALANCE
// #define SQLITE_OMIT_REINDEX
// #define SQLITE_OMIT_SCHEMA_PRAGMAS
// #define SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
// #define SQLITE_OMIT_SHARED_CACHE
// #define SQLITE_OMIT_SHUTDOWN_DIRECTORIES
// #define SQLITE_OMIT_SUBQUERY
// #define SQLITE_OMIT_TCL_VARIABLE
// #define SQLITE_OMIT_TEMPDB
// #define SQLITE_OMIT_TRACE
// #define SQLITE_OMIT_TRIGGER
// #define SQLITE_OMIT_TRUNCATE_OPTIMIZATION
// #define SQLITE_OMIT_UTF16
// #define SQLITE_OMIT_VACUUM
// #define SQLITE_OMIT_VIEW
// #define SQLITE_OMIT_VIRTUALTABLE
// #define SQLITE_OMIT_WAL
// #define SQLITE_OMIT_WSD
// #define SQLITE_OMIT_XFER_OPT

// *** SQLITE DEBUGGING FEATURES ***

// #define SQLITE_DEBUG 1
// #define SQLITE_ENABLE_EXPENSIVE_ASSERT 1
// #define SQLITE_ENABLE_OVERSIZE_CELL_CHECK 1
// #define SQLITE_ENABLE_SELECTTRACE 1 // Enable Select 
Trace (.selecttrace 0x100) needs SQLITE_DEBUG
// #define SQLITE_ENABLE_SQLLOG 1  // Enable 
SQLITE_CONFIG_SQLLOG (see documentation)
// #define SQLITE_ENABLE_STMT_SCANSTATUS 1 // Enable 
Collection of Statement Scan Status
// #define SQLITE_ENABLE_WHERETRACE 1
// #define SQLITE_IOTRACE 1
// #define SQLITE_MEMDEBUG 1
// #define SQLITE_REVERSE_UNORDERED_SELECTS 1
// #define SQLITE_USE_FCNTL_TRACE 1// Enable extra 
vfslog fcntrl trace
// #define SQLITE_YYTRACKMAXSTACKDEPTH 1

#if defined(_WIN32) && defined(__GNUC__)
#define UNICODE_STRING_MAX_BYTES ((WORD) 65534)
#define UNICODE_STRING_MAX_CHARS (32767)
#define HAVE_FDATASYNC 1
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1
#define HAVE_ISNAN 1
#define HAVE_MALLOC_USABLE_SIZE 1
#define HAVE_USLEEP 1
#define HAVE_UTIME 1
#endif

#if defined(_MSC_VER)
#define HAVE_FDATASYNC 1
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1
#define HAVE_MALLOC_USABLE_SIZE 1
#define HAVE_USLEEP 1
#define HAVE_UTIME 1
#endif

#if defined(__GNUC__) && defined(SQLITE_USE_QUADMATH)
#define LONGDOUBLE_TYPE __float128
#endif
#endif


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of David Empson
> Sent: Tuesday, 11 October, 2016 18:41
> To: SQLite mailing list
> Subject: Re: [sqlite] LIMIT doesn't return expected rows
> 
> 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 <demp...@emptech.co.nz> 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)

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 <demp...@emptech.co.nz> 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 <kmedc...@dessus.com> 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"

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 <kmedc...@dessus.com> 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] 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

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

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] 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] LIMIT doesn't return expected rows

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

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

15 matches

Site Navigation

Mail list logo

Footer information