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
//
// WIN10 0x0A00 WIN6 0x0600 W2K 0x0500 NT4 0x0400
// VISTA 0x0600 WXP 0x0501 W95 0x0400
// W2K8 0x0600 W2K3 0x0502 NT4E 0x0401
// WIN7 0x0601 W98 0x0410
// WIN8 0x0602 WME 0x0490
// WIN81 0x0603
#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_MEMSTATUS 1 // default: 1
#define SQLITE_DEFAULT_PAGE_SIZE 4096 // 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_SIZE 0 // default: 0
// #define SQLITE_LIKE_DOESNT_MATCH_BLOBS 1 // default:
undefined
// #define SQLITE_SORTER_PMASZ 64 // default: 250
// #define SQLITE_EXTRA_DURABLE 1 // 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 SQLITE_ENABLE_UPDATE_DELETE_LIMIT 1 // Requires
Special Amalgamation / Parser Support
#define SQLITE_SOUNDEX 1
// #define SQLITE_DISABLE_LFS 1
// #define SQLITE_DISABLE_PAGECACHE_OVERFLOW_STATS 1
// #define SQLITE_DISABLE_DIRSYNC 1
// #define SQLITE_CASE_SENSITIVE_LIKE 1
// #define SQLITE_NOW_STABILITY_STEP 1 // Make 'now'
stable only within step, not entire statement
// #define SQLITE_SECURE_DELETE 1
#define SQLITE_TEMP_STORE 2 // 0 = Files
Always, 1 = Files, 2 = Memory, 3 Memory Always
#define SQLITE_USE_URI 1 // Enable URI
Filenames
#define SQLITE_ALLOW_URI_AUTHORITY 1 // Allow Authority
(Host) in URI
// #define SQLITE_MMAP_READWRITE 1 // mmaps are
writeable as well as readable
// *** SQLITE MAXIMUMS AND LIMITS CONFIGURATION ***
// #define SQLITE_FTS3_MAX_EXPR_DEPTH 15 // default: 12
#define SQLITE_MAX_ATTACHED 15 // default: 10
max: 62
// #define SQLITE_MAX_COLUMN 2000 // default: 2000
max: 32767
// #define SQLITE_MAX_COMPOUND_SELECT 500 // default: 500
// #define SQLITE_MAX_EXPR_DEPTH 1000 // default: 1000
// #define SQLITE_FTS3_MAX_EXPR_DEPTH 12 // default: 12
// #define SQLITE_MAX_FUNCTION_ARG 100 // default: 100
max: 127
// #define SQLITE_MAX_LENGTH 0x3fffffff // default:
1000000000 max: 2147483647 (2^31-1)
// #define SQLITE_MAX_LIKE_PATTERN_LENGTH 16384 // default: 50000
// #define SQLITE_MAX_MMAP_SIZE 0x7fff0000 // default:
0x7fff0000
// #define SQLITE_MAX_PAGE_COUNT 1073741823 // default:
1073741823 max: 2147483646 (2^31-2)
// #define SQLITE_MAX_SQL_LENGTH 131072 // default:
1000000 max: 2^30
// #define SQLITE_MAX_TRIGGER_DEPTH 1000 // default: 1000
// #define SQLITE_MAX_VARIABLE_NUMBER 999 // default: 999
// #define SQLITE_MAX_SCHEMA_RETRY 50 // default: 50
// #define SQLITE_MAX_WORKER_THREADS 8 // default: 11
// #define YYSTACKDEPTH 100 // defautl: 100
// *** SQLITE OPERATING SYSTEM AND INTERNALS CONFIGURATION ***
// #define SQLITE_OS_OTHER 0
#define SQLITE_OS_WIN 1
#define SQLITE_OS_WINNT 1
// #define SQLITE_OS_WINCE 1
// #define SQLITE_OS_WINRT 1
#define SQLITE_WIN32_MALLOC 1 // Use Win32 Heap
Allocator
#define SQLITE_WIN32_HEAP_CREATE 1 // Use Separate
Win32 Heap
// #define SQLITE_WIN32_MALLOC_VALIDATE 1 // Validate
Win32 Heap during SQLITE_DEBUG assert
#define SQLITE_WIN32_HEAP_INIT_SIZE 268435456 // Initial Win32
Heap Size = 256 MB
#define SQLITE_WIN32_HEAP_MAX_SIZE 0 // Max Win32 Heap
Size (No Limit)
// #define SQLITE_WIN32_HEAP_FLAGS 0
// #define SQLITE_WIN32_FILE_SEQUENTIAL 1 // Force Windows
SEQUENTIAL access cache behaviour
#define SQLITE_WIN32_FILE_RANDOM 1 // Force Windows
RANDOM access cache behaviour
// #define SQLITE_WIN32_FILE_WRITETHROUGH 1 // Force Windows
WRITE-THROUGH Behaviour
// #define SQLITE_DIRECT_OVERFLOW_READ 1 // Do Not
PageCache Overflow Pages
// #define SQLITE_SYSTEM_MALLOC 1 // 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.
>
> 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" 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
>
> --
> David Empson
> demp...@emptech.co.nz
> Snail mail: P.O. Box 27-103, Wellington 6141, New Zealand
>
> _______________________________________________
> 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
