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 > // > // 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
