SQLite Forum

Bug: MSVC uses intrinsic versions of ceil and floor for floating-point modes precise and fast (diagnosed)
Login

Bug: MSVC uses intrinsic versions of ceil and floor for floating-point modes precise and fast (diagnosed)

(1.1) By Erlend E. Aasland (erlendaasland) on 2021-01-01 01:15:25 edited from 1.0 [source]

I'm trying to compile the nightly build (2020-12-30) on Win10 using Visual Studio 2017, with SQLITE_ENABLE_MATH_FUNCTIONS defined. I run into three errors:

>sqlite3.c(119997): error C2099: initializer is not a constant
>sqlite3.c(119997): warning C4047: 'initializing': 'FuncDef *' differs in levels of indirection from 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)'
>sqlite3.c(119997): warning C4047: 'initializing': 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)' differs in levels of indirection from 'char [5]'
>sqlite3.c(119998): error C2099: initializer is not a constant
>sqlite3.c(119998): warning C4047: 'initializing': 'FuncDef *' differs in levels of indirection from 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)'
>sqlite3.c(119998): warning C4047: 'initializing': 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)' differs in levels of indirection from 'char [8]'
>sqlite3.c(119999): error C2099: initializer is not a constant
>sqlite3.c(119999): warning C4047: 'initializing': 'FuncDef *' differs in levels of indirection from 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)'
>sqlite3.c(119999): warning C4047: 'initializing': 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)' differs in levels of indirection from 'char [6]'

Lines 119996 to 119999 is:

#ifdef SQLITE_ENABLE_MATH_FUNCTIONS
    MFUNCTION(ceil,              1, ceil,      ceilingFunc ),
    MFUNCTION(ceiling,           1, ceil,      ceilingFunc ),
    MFUNCTION(floor,             1, floor,     ceilingFunc ),

Any pointers to what's wrong here? My background is Linux/macOS, so I'm on thin ice with Visual Studio and Windows. (BTW, VS 2017 is a requirement, which is why I'm not using the most recent VS version.)

(2) By Richard Hipp (drh) on 2020-12-31 13:49:22 in reply to 1.0 [link] [source]

I'm still using VS 2015 (19.00.24215.1). And the build works fine and all the tests pass on that compiler. I don't know why 2017 is failing. Maybe Joe has a solution.

(3) By Larry Brasfield (LarryBrasfield) on 2020-12-31 16:59:50 in reply to 1.0 [link] [source]

FWIW: Visual Studio 2019 compiler produces the same errors at the same place. I have yet to discern why line 120001 and subsequent uses of MFUNCTION macro do not produce the same error while being identical with respect to signatures and data declarations. Still investigating.

(4.1) By Larry Brasfield (LarryBrasfield) on 2020-12-31 18:36:29 edited from 4.0 in reply to 3 [link] [source]

I post the following as clues resulting from my investigation, not as a solution.

If these two functions definitions follow #include <math.h>: static double vsCeil( double a ) { return ceil(a); } static double vsFloor( double a ) { return floor(a); } and the VS20{17,19} choking lines: MFUNCTION(ceil, 1, ceil, ceilingFunc ), MFUNCTION(ceiling, 1, ceil, ceilingFunc ), MFUNCTION(floor, 1, floor, ceilingFunc ), are replaced with: MFUNCTION(ceil, 1, vsCeil, ceilingFunc ), MFUNCTION(ceiling, 1, vsCeil, ceilingFunc ), MFUNCTION(floor, 1, vsFloor, ceilingFunc ), , then sqlite3.c compiles under VS2019 without producing the errors reported by the OP. Why this is remains a mystery to me. The next line of code: MFUNCTION(trunc, 1, trunc, ceilingFunc ), compiles fine, and as far as I can see (while looking at preprocessor output with the same preprocessing options and definitions as for the failing compile), the ceil, floor and trunc functions are declared identically. (ie: __declspec(dllimport) double __cdecl ceil( double _X); __declspec(dllimport) double __cdecl floor( double _X); __declspec(dllimport) double __cdecl trunc( double _X); ). Yet replacing 'ceil' or 'floor' in the 3 failing lines with 'trunc' also eliminates the VS2019 complaints (while being mathematically incorrect.)

It's enough to make me wonder if those functions are resolving to an FPU intrinsic or some such goofiness, such that their names do not resolve to the address of an executable function. I'll have to look into this further.

(5.1) By Richard Hipp (drh) on 2020-12-31 18:42:13 edited from 5.0 in reply to 4.1 [link] [source]

Deleted

(6) By Keith Medcalf (kmedcalf) on 2020-12-31 19:41:11 in reply to 3 [link] [source]

Microsoft (R) C/C++ Optimizing Compiler Version 19.28.29335 for x64

handles it just fine.

That is the compiler included in the Visual Studio Build Tools 2019 16.8.3.

MinGW64 GCC 9.1.0 also has no problems.

What version of the compiler is allegedly having problems and what memory model (x86/x64) is being used? Calling something "Visual Studio 2019" is sort of like saying "the car has a problem" without specifying the make and model.

(7) By Keith Medcalf (kmedcalf) on 2020-12-31 19:58:34 in reply to 6 [link] [source]

And the executable contains the functions ...

sqlite> select * from pragma_compile_options where compile_options like 'comp%' or compile_options like '%math%';
┌───────────────────────┐
│    compile_options    │
├───────────────────────┤
│ COMPILER=msvc-1928    │
│ ENABLE_MATH_FUNCTIONS │
└───────────────────────┘
sqlite> select * from pragma_function_list where name in ('ceil', 'ceiling', 'floor');
┌─────────┬─────────┬──────┬──────┬──────┬─────────┐
│  name   │ builtin │ type │ enc  │ narg │  flags  │
├─────────┼─────────┼──────┼──────┼──────┼─────────┤
│ ceil    │ 1       │ s    │ utf8 │ 1    │ 2099200 │
│ ceiling │ 1       │ s    │ utf8 │ 1    │ 2099200 │
│ floor   │ 1       │ s    │ utf8 │ 1    │ 2099200 │
└─────────┴─────────┴──────┴──────┴──────┴─────────┘

(8) By Larry Brasfield (LarryBrasfield) on 2020-12-31 20:34:57 in reply to 7 [link] [source]

Interesting, maybe. Perhaps stranger than either of us yet understands.

I was using compiler version 19.27.29111 for x64, almost 4 months "old".

My omission is closer to neglecting to say when in a given model/year's production run the vehicle was made. This is not to say that the revision level is unimportant. It may yet be shown to be critical.

Updating to Visual Studio 16.8.3, (CL v19.28.29335 for the 64-bit toolset), followed by a restart, does not clear the OP's reported errors with my build options.

With my hacky work-around, (to get an executable), I get this from the pragma_compile_options virtual table: ┌─────────────────────────────┐ │ compile_options │ ├─────────────────────────────┤ │ COMPILER=msvc-1928 │ │ DEFAULT_FOREIGN_KEYS │ │ DEFAULT_SYNCHRONOUS=3 │ │ DEFAULT_WORKER_THREADS=3 │ │ ENABLE_BYTECODE_VTAB │ │ ENABLE_COLUMN_METADATA │ │ ENABLE_DBSTAT_VTAB │ │ ENABLE_FTS4 │ │ ENABLE_FTS5 │ │ ENABLE_GEOPOLY │ │ ENABLE_JSON1 │ │ ENABLE_MATH_FUNCTIONS │ │ ENABLE_PREUPDATE_HOOK │ │ ENABLE_RTREE │ │ ENABLE_SESSION │ │ ENABLE_STMTVTAB │ │ ENABLE_UNKNOWN_SQL_FUNCTION │ │ LIKE_DOESNT_MATCH_BLOBS │ │ MAX_TRIGGER_DEPTH=100 │ │ OMIT_DEPRECATED │ │ OMIT_SHARED_CACHE │ │ TEMP_STORE=2 │ │ THREADSAFE=0 │ │ UNTESTABLE │ │ USE_ALLOCA │ │ USE_URI │ └─────────────────────────────┘ Will you please provide your compile options to aid my further investigation?

(9) By Keith Medcalf (kmedcalf) on 2020-12-31 21:43:35 in reply to 8 [link] [source]

I will note, however, that I have had my own "compiled in" extension which provided all the builtin math functions since forever, called by essentially the same method (calling a generic SQLite3 interface function and passing a pointer to the math function in the user_data). When the these became available I simply added an #if !defined(SQLITE_MATH_FUNCTIONS) so that the builtins were used and my addins were not registered.

That code is here http://www.dessus.com/files/sqlmath.c

SQLite version 3.35.0 2020-12-31 19:46:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select * from pragma_compile_options;
┌────────────────────────────────┐
│        compile_options         │
├────────────────────────────────┤
│ ALLOW_COVERING_INDEX_SCAN      │
│ ALLOW_URI_AUTHORITY            │
│ COMPILER=msvc-1928             │
│ DEFAULT_CACHE_SIZE=-1048576    │
│ DEFAULT_FOREIGN_KEYS           │
│ DEFAULT_PAGE_SIZE=4096         │
│ DEFAULT_RECURSIVE_TRIGGERS     │
│ DEFAULT_WAL_AUTOCHECKPOINT=256 │
│ DEFAULT_WAL_SYNCHRONOUS=1      │
│ DEFAULT_WORKER_THREADS=8       │
│ ENABLE_8_3_NAMES=1             │
│ ENABLE_API_ARMOR               │
│ ENABLE_BYTECODE_VTAB           │
│ ENABLE_COLUMN_METADATA         │
│ ENABLE_COLUMN_USED_MASK        │
│ ENABLE_COSTMULT                │
│ ENABLE_CURSOR_HINTS            │
│ ENABLE_DBSTAT_VTAB             │
│ ENABLE_FTS3                    │
│ ENABLE_FTS3_PARENTHESIS        │
│ ENABLE_FTS4                    │
│ ENABLE_FTS5                    │
│ ENABLE_JSON1                   │
│ ENABLE_LOAD_EXTENSION          │
│ ENABLE_LOCKING_STYLE=1         │
│ ENABLE_MATH_FUNCTIONS          │
│ ENABLE_MEMORY_MANAGEMENT       │
│ ENABLE_PREUPDATE_HOOK          │
│ ENABLE_RBU                     │
│ ENABLE_RTREE                   │
│ ENABLE_SESSION                 │
│ ENABLE_SNAPSHOT                │
│ ENABLE_STAT4                   │
│ ENABLE_STMT_SCANSTATUS         │
│ ENABLE_UNKNOWN_SQL_FUNCTION    │
│ ENABLE_UPDATE_DELETE_LIMIT     │
│ EXPLAIN_ESTIMATED_ROWS         │
│ EXTRA_INIT=core_init           │
│ LIKE_DOESNT_MATCH_BLOBS        │
│ MAX_ATTACHED=15                │
│ MAX_TRIGGER_DEPTH=100          │
│ MAX_WORKER_THREADS=16          │
│ SOUNDEX                        │
│ STAT4_SAMPLES=64               │
│ TEMP_STORE=1                   │
│ THREADSAFE=1                   │
│ USE_DATETIME_NEW               │
│ USE_PRECISE_TIME               │
│ USE_URI                        │
└────────────────────────────────┘
sqlite>

Also note that I build using nmake and not the hooey-gooey (if you can even use the hooey-gooey -- I know not because I don't use hooey-gooey's).

(10.2) By Keith Medcalf (kmedcalf) on 2020-12-31 22:28:17 edited from 10.1 in reply to 9 [link] [source]

nmake parameters look like this:

USE_AMALGAMATION=1 SYMBOLS=0 OPTIMIZATIONS=3 USE_CRT_DLL=0 LIBTCL= USE_RPCRT4_LIB=1 OPT_FEATURE_FLAGS="-DSQLITE_UDL_CAPABLE_PARSER" OPTS="-D_HAVE_SQLITE_CONFIG_H -DSQLITE_EXTRA_INIT=core_init"

and my config.h contains this:

#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 0x0A00
#endif
#ifndef WINVER
#define WINVER _WIN32_WINNT
#endif


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

// #define SQLITE_DEFAULT_AUTOMATIC_INDEX      1               // default: 1
// #define SQLITE_DEFAULT_AUTOVACUUM           0               // default: 0

// Set Default Page Cache Size and Temp Cache Size based on Memory Model

#if defined(_WIN64)
#define SQLITE_DEFAULT_CACHE_SIZE        -1048576           // 1024 MB default: 2 MB
#define SQLITE_DEFAULT_TEMP_CACHE_SIZE    -262144           //  256 MB default:  500 pages
#else
#define SQLITE_DEFAULT_CACHE_SIZE         -262144           //  256 MB default:  2 MB pages
#define SQLITE_DEFAULT_TEMP_CACHE_SIZE     -65536           //   64 MB default:   500 pages
#endif

#define SQLITE_DEFAULT_DEFENSIVE            1               // default:  undefined or off
// #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_MMAP_SIZE            0               // default: 0
#define SQLITE_DEFAULT_PAGE_SIZE            4096            // default: 4096 max: 65536
// #define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755            // default: 0755
#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1               // default: 0
// #define SQLITE_DEFAULT_SHARED_CACHE         0               // default: 0
// #define SQLITE_DEFAULT_SYNCHRONOUS          2               // default: 2
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256             // default: 1000 pages
// #define SQLITE_DEFAULT_WAL_SYNCHRONOUS      2               // default: same as default synchronous
#define SQLITE_DEFAULT_WORKER_THREADS       8               // 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 RBU_ENABLE_DELTA_CKSUM 1                            // Enable RBU Update Checksum (disabled by default)
// #define SQLITE_64BIT_STATS 1
#define SQLITE_ALLOW_URI_AUTHORITY 1                        // Allow Authority (Host) in URI
#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1                  // Allow Covering Index Scans
// #define SQLITE_CASE_SENSITIVE_LIKE 1                        // Like is case sensitive (default is not case sensitive)
#define SQLITE_COUNTOFVIEW_OPTIMIZATION 1                   // Additional Optimization for count()
// #define SQLITE_DISABLE_DIRSYNC 1                            // Do not sync directories
// #define SQLITE_DISABLE_FTS3_UNICODE 1                       // Disable Unicode in FTS3
// #define SQLITE_DISABLE_LFS 1
// #define SQLITE_DISABLE_PAGECACHE_OVERFLOW_STATS 1           // Disable Overflow Page Statistics
#define SQLITE_DQS 0                                        // Double Quoted Strings (0=None/1=DML/2=DDL/3=Both)
#define SQLITE_INTROSPECTION_PRAGMAS 1                      // Add Instropsection Pragma
#define SQLITE_ENABLE_8_3_NAMES 1                           // Enable 8.3 Names
#define SQLITE_ENABLE_API_ARMOR 1                           // Enable API Armour
// #define SQLITE_ENABLE_ATOMIC_WRITE 1                        // Enable Atomic Writes
#define SQLITE_ENABLE_BYTECODE_VTAB 1                       // Enable Bytecode VTAB
#define SQLITE_ENABLE_COLUMN_METADATA 1                     // Add Column Metadata Functions
#define SQLITE_ENABLE_COLUMN_USED_MASK 1                    // Enable Column Used Hints
#define SQLITE_ENABLE_COSTMULT 1                            // Use Cost Multiplication Factors
#define SQLITE_ENABLE_CURSOR_HINTS 1                        // Output Cursor Hints to B-Tree Layer
#define SQLITE_ENABLE_DBPAGE_VTAB 1                         // Include DBPAGE Virtual Table
#define SQLITE_ENABLE_DBSTAT_VTAB 1                         // Include DBSTAT Virtual Table
#define SQLITE_ENABLE_DESERIALIZE 1                         // Include Serialize/Deserialize API
#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1                    // Add Comments to Explain
#define SQLITE_ENABLE_FTS3 1                                // Include FTS3 Extension
#define SQLITE_ENABLE_FTS3_PARENTHESIS 1                    // FTS3 Options
#define SQLITE_ENABLE_FTS4 1                                // Include FTS4 Extension
#define SQLITE_ENABLE_FTS5 1                                // Include FTS5 Extension
#define SQLITE_ENABLE_GEOPOLY 1                             // Include the GEOPOLY Extension
// #define SQLITE_ENABLE_HIDDEN_COLUMNS 1                      // Allow Hidden Columns
// #define SQLITE_ENABLE_ICU 1                                 // Set in BUILD Command additional Libs required
#define SQLITE_ENABLE_JSON1 1                               // Enable JSON1 Extension
#define SQLITE_ENABLE_LOAD_EXTENSION 1                      // Enable the Load Extension Interface
#define SQLITE_ENABLE_LOCKING_STYLE 1                       // Locking Style (1 == Normal)
#define SQLITE_ENABLE_LSM1 1                                // Enable LSM1
#define SQLITE_ENABLE_MATH_FUNCTIONS 1                      // Enable Math Functions
#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1                   // Enable Memory Management (sqlite3_release_memory)
#define SQLITE_ENABLE_MEMSTATVTAB 1                         // Enable the memstat vtab
// #define SQLITE_ENABLE_MEMSYS3 1                             // Include MEMSYS3
// #define SQLITE_ENABLE_MEMSYS5 1                             // Include MEMSYS5
#define SQLITE_ENABLE_MODULE_COMMENTS 1                     // Add Module Comments to Explain
// #define SQLITE_ENABLE_MULTITHREADED_CHECKS 1                // Enable Multithreaded Checks when using MUTLITHREADING config
#define SQLITE_ENABLE_NORMALIZE 1                           // Enable the sqlite3_normalized_sql interface
#define SQLITE_ENABLE_PREUPDATE_HOOK 1                      // Enable the pre-commit hooks
// #define SQLITE_ENABLE_QPSG                                  // Enable Query Planner Stability Guarantee
#define SQLITE_ENABLE_RBU 1                                 // Enable Resumable Bulk Update
#define SQLITE_ENABLE_RTREE 1                               // Include the RTREE Extension
// #define SQLITE_RTREE_INT_ONLY 1                             // Make RTREE Integer Only (rather than float32)
#define SQLITE_ENABLE_SESSION 1                             // Enable the SESSION feature
// #define SQLITE_ENABLE_SETLK_TIMEOUT 1                       // Use File Control to manage lock acquisition
#define SQLITE_ENABLE_SNAPSHOT 1                            // Enable the SNAPSHOT feature
// #define SQLITE_ENABLE_SORTER_REFERENCES 1                   // Enable Sorter References
#define SQLITE_ENABLE_STAT_VTAB 1                           // Enable dbstat_register called from shell
#define SQLITE_ENABLE_STAT1 1                               // Enable Statistics 1
#define SQLITE_ENABLE_STAT2 1                               // Enable Statistics 2
#define SQLITE_ENABLE_STAT3 1                               // Enable Statistics 3
#define SQLITE_ENABLE_STAT4 1                               // Enable Statistics 4
#define SQLITE_ENABLE_STMTVTAB 1                            // Enable Stmt VTAB Extension
#define SQLITE_ENABLE_STMT_SCANSTATUS 1                     // Enable Statement ScanStatus
#define SQLITE_ENABLE_UNIONVTAB 1                           // Enable unionvtab
#define SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION 1                // Allow Explain to work for Unknown Functions
// #define SQLITE_ENABLE_UNLOCK_NOTIFY 1                       // See Documentation before enabling
#define SQLITE_ENABLE_UPDATE_DELETE_LIMIT 1                 // Requires Special Amalgamation / Parser Support
// #define SQLITE_ENABLE_VFSSTAT 1                             // Enable vfsstat extension
#define SQLITE_EXPLAIN_ESTIMATED_ROWS 1                     // Add Estimated Rows to Explain
// #define SQLITE_LIKE_DOESNT_MATCH_BLOBS      1               // default: 0 (undefined)
// #define SQLITE_MMAP_READWRITE 1                             // mmaps are writeable as well as readable
// #define SQLITE_SECURE_DELETE 1                              // Enable Overwriting of deleted records
#define SQLITE_SOUNDEX 1                                    // Include the soundex builtin
#define SQLITE_STAT4_SAMPLES 64                             // default: 24 samples
#define SQLITE_THREADSAFE 1                                 // 0 = Single Threaded, 1 = Serialized, 2 = Multithreaded
#define SQLITE_TEMP_STORE 1                                 // 0 = Files Always, 1 = Files, 2 = Memory, 3 = Memory Always
#define SQLITE_TRUSTED_SCHEMA 0                             // Schema is untrusted -- default trusted
#define SQLITE_USE_URI 1                                    // Enable URI Filenames


//  *** 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_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_MEMORY                   0               // default: 0
// #if defined(_WIN64)
// #define SQLITE_MAX_MEMORY                   4294967296      // 4 GB on x64
// #else
// #define SQLITE_MAX_MEMORY                   1073741824      // 1 GB on x32
// #endif
// #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          32766           // default: 32766
// #define SQLITE_MAX_SCHEMA_RETRY             50              // default: 50
#define SQLITE_MAX_WORKER_THREADS           16              // default: 8
// #define YYSTACKDEPTH                        100             // default: 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
// #if defined(_WIN64)
// #define SQLITE_WIN32_HEAP_INIT_SIZE 1073741824              // Initial Win32 Heap Size = 1 GB
// #else
// #define SQLITE_WIN32_HEAP_INIT_SIZE 268435456               // Initial Win32 Heap Size = 256 MB
// #endif
// #define SQLITE_WIN32_HEAP_MAX_SIZE 0                        // Max Win32 Heap Size (No Limit)
// #define SQLITE_WIN32_HEAP_FLAGS 0
// #define SQLITE_DIRECT_OVERFLOW_READ 1                       // Do Not PageCache Overflow Pages
// #define SQLITE_DEFAULT_LOOKASIDE 1200,100                   // Default LookAside Allocation
// #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


// *** 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


// *** Custom Additions/Changes/Features ***

#define SQLITE_NOW_STABILITY_STMT 1                         // Make 'now' stable within a statement, not only for a step
#define WHERE_PATH_SIMPLE 50                                // Paths to remember for  2-way joins
#define WHERE_PATH_COMPLEX 100                              // Paths to remember for >2-way joins
#define SQLITE_USE_QUADMATH 1                               // Use 128-bit Float Intermediates if available
#define SQLITE_USE_PRECISE_TIME 1                           // Use GetSystemTimePreciseAsFileTime
#define SQLITE_USE_DATETIME_NEW 1                           // Use New Datetime Functions

// For cache modes only use 1 of RANDOM / SEQUENTIAL / WRITETHROUGH / NOBUFFER
// For WINCE always use RANDOM, default if "Microsoft Magical Mode" if none selected

#define SQLITE_WIN32_FILE_RANDOM 1                          // Force Windows RANDOM access cache behaviour
// #define SQLITE_WIN32_FILE_SEQUENTIAL 1                      // Force Windows SEQUENTIAL access cache behaviour
// #define SQLITE_WIN32_FILE_WRITETHROUGH 1                    // Turn off Delayed Writes
// #define SQLITE_WIN32_FILE_NOBUFFER 1                        // Disable use of Filesystem Cache


// Performance Optimizations

// #define SQLITE_THREADSAFE                   0                   // Remove Thread Safety Mutexes
// #define SQLITE_DEFAULT_MEMSTATUS            0                   // Remove Memory Status from SQLITE3_MALLOC
#define SQLITE_DEFAULT_WAL_SYNCHRONOUS      1                   // Reduce Synchronous to NORMAL in WAL mode
// #define SQLITE_DIRECT_OVERFLOW_READ         1                   // Do not cache overflow pages in SQLite pagecache
#define SQLITE_LIKE_DOESNT_MATCH_BLOBS      1                   // Disable LIKE matching for BLOBS
// #define SQLITE_MAX_EXPR_DEPTH               0                   // Disable Parser Depth Checking
// #define SQLITE_OMIT_DECLTYPE                1                   // Omit Declaration Type from Cursors
// #define SQLITE_OMIT_DEPRECATED              1                   // Omit Deprecated Code
// #define SQLITE_OMIT_PROGRESS_CALLBACK       1                   // Omit Progress Callback loop counter
// #define SQLITE_OMIT_SHARED_CACHE            1                   // Omit Shared Cache


// Compiler and Platform specifics

#define HAVE_FDATASYNC 1                                        // Platform has FDATASYNC
#define HAVE_GMTIME_R 1                                         // Platform has GMTIME_R
#define HAVE_LOCALTIME_S 1                                      // Platform has LOCALTIME_S
#define HAVE_USLEEP 1                                           // Platform has usleep
#define HAVE_UTIME 1                                            // Platform has utime
#define HAVE_STDINT_H 1                                         // Platform has stdint.h
#define HAVE_INTTYPES_H 1                                       // Platform has inttypes.h

#if defined(_WIN32) && defined(__GNUC__)                        // Only for GCC (MinGW on Windows)
#ifndef UNICODE_STRING_MAX_BYTES
#define UNICODE_STRING_MAX_BYTES ((WORD)65534)                  // Reset Unicode String Bytes Maximum
#endif
#ifndef UNICODE_STRING_MAX_CHARS
#define UNICODE_STRING_MAX_CHARS (32766)                        // Reset Unicode String Characters Maximum
#endif
#define HAVE_ISNAN 1                                            // Platform has isnan function
#define SQLITE_USE_MALLOC_H 1                                   // Platform has malloc.h
#define SQLITE_USE_MSIZE 1                                      // Platform malloc_usable_size is _msize
#define __USE_MINGW_ANSI_STDIO 1                                // Use ANSI StdIO
#endif

#define SQLITE_HAVE_ZLIB 1                                      // Platform has ZLIB
#define SQLITE_WIN32_USE_UUID 1                                 // Use RPCRT4 UUID Functions

#undef LONGDOUBLE_TYPE                                          // Clear the LONGDOUBLE_type

#if defined(__GNUC__) && defined(SQLITE_USE_QUADMATH)
#define LONGDOUBLE_TYPE __float128                              // Use GCC extension type if we want quadmath
#else
#undef SQLITE_USE_QUADMATH
#define LONGDOUBLE_TYPE long double                             // else long double is long double
#endif

#endif

My own extensions and core_init function are appended to the end of the amalgamation code by modification of the mksqlite3c.tcl script so do not affect anything in the base func.c code ... (which is only modified such that the sum context uses a LONGDOUBLE_TYPE real accumulator so that precision is not lost if the input is not sorted by magnitude).

MinGW64 command line looks like this:

gcc -s -O3 -pipe -D_HAVE_SQLITE_CONFIG_H -DSQLITE_EXTRA_INIT=core_init -DSQLITE_HAVE_ZLIB -Itsrc -march=native -mtune=native -m64 -mthreads -Wl,-Bstatic,--nxcompat,--dynamicbase,--high-entropy-va,--image-base,0x140000000 DummyExport.c shell.c sqlite3.c -ladvapi32 -lrpcrt4 -lwinmm -lz -static-libgcc -o gcc/64/sqlite3.exe

DummyExport.c is simply a dummy export because Windows does not handle executables properly and does not actually do high-entropy ASLR properly unless the executable has an exported symbol.

void __declspec(dllexport) DummyExport(void) {}

(11) By Keith Medcalf (kmedcalf) on 2020-12-31 22:40:22 in reply to 9 [link] [source]

Here is the resulting pragma_function_list:

sqlite> select * from pragma_function_list order by name collate nocase, builtin desc, narg;
┌───────────────────────────┬─────────┬──────┬─────────┬──────┬─────────┐
│           name            │ builtin │ type │   enc   │ narg │  flags  │
├───────────────────────────┼─────────┼──────┼─────────┼──────┼─────────┤
│ aavg                      │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ abs                       │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ acos                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ acosh                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ aggbitmask                │ 0       │ w    │ utf8    │ -1   │ 2099200 │
│ aggmd2                    │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggmd4                    │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggmd5                    │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha1                   │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha256                 │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha2_256               │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha2_384               │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha2_512               │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha384                 │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha3_224               │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha3_256               │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha3_384               │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha3_512               │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ aggsha512                 │ 0       │ a    │ utf8    │ -1   │ 2099200 │
│ ascw                      │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ ascw                      │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ asin                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ asinh                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ atan                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ atan2                     │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ atanh                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ avg                       │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ avg                       │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ avg                       │ 0       │ w    │ utf8    │ 2    │ 2097152 │
│ bitmask                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ bm25                      │ 0       │ s    │ utf8    │ -1   │ 0       │
│ ceil                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ ceiling                   │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ changes                   │ 1       │ s    │ utf8    │ 0    │ 2097152 │
│ char                      │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ chgsign                   │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ chrw                      │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ ci                        │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ ci                        │ 0       │ w    │ utf8    │ 2    │ 2097152 │
│ clrbits                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ coalesce                  │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ compress                  │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ computerfqdn              │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ computername              │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ copysign                  │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ cos                       │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ cosh                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ count                     │ 1       │ w    │ utf8    │ 0    │ 2097152 │
│ count                     │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ covar                     │ 0       │ a    │ utf8    │ 2    │ 2097152 │
│ cume_dist                 │ 1       │ w    │ utf8    │ 0    │ 2097152 │
│ current_date              │ 1       │ s    │ utf8    │ 0    │ 2097152 │
│ current_date              │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ current_time              │ 1       │ s    │ utf8    │ 0    │ 2097152 │
│ current_time              │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ current_timestamp         │ 1       │ s    │ utf8    │ 0    │ 2097152 │
│ current_timestamp         │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ date                      │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ date                      │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ datetime                  │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ datetime                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ datetimesec               │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ decimal                   │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ decimal_add               │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ decimal_cmp               │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ decimal_mul               │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ decimal_sub               │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ decimal_sum               │ 0       │ w    │ utf8    │ 1    │ 2099200 │
│ degrees                   │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ delta_apply               │ 0       │ s    │ utf8    │ 2    │ 2097152 │
│ delta_create              │ 0       │ s    │ utf8    │ 2    │ 2097152 │
│ delta_output_size         │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ dense_rank                │ 1       │ w    │ utf8    │ 0    │ 2097152 │
│ doesfileexist             │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ edit                      │ 0       │ s    │ utf8    │ 1    │ 0       │
│ edit                      │ 0       │ s    │ utf8    │ 2    │ 0       │
│ editdist3                 │ 0       │ s    │ utf8    │ 1    │ 2048    │
│ editdist3                 │ 0       │ s    │ utf8    │ 2    │ 2048    │
│ editdist3                 │ 0       │ s    │ utf8    │ 3    │ 2048    │
│ elapsedtime               │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ epsilon                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ errorifnull               │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ eval                      │ 0       │ s    │ utf8    │ 1    │ 524288  │
│ eval                      │ 0       │ s    │ utf8    │ 2    │ 524288  │
│ exp                       │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ exponent                  │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ fabs                      │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ feq                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ fge                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ fgt                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ first_value               │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ firstnotnull              │ 0       │ a    │ utf8    │ 1    │ 2097152 │
│ fle                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ flip                      │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ floor                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ flt                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ fne                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ fold                      │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ frac                      │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ fromhex                   │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ fts3_tokenizer            │ 0       │ s    │ utf8    │ 1    │ 524288  │
│ fts3_tokenizer            │ 0       │ s    │ utf8    │ 2    │ 524288  │
│ fts5                      │ 0       │ s    │ utf8    │ 1    │ 0       │
│ fts5_decode               │ 0       │ s    │ utf8    │ 2    │ 0       │
│ fts5_decode_none          │ 0       │ s    │ utf8    │ 2    │ 0       │
│ fts5_expr                 │ 0       │ s    │ utf8    │ -1   │ 0       │
│ fts5_expr_tcl             │ 0       │ s    │ utf8    │ -1   │ 0       │
│ fts5_fold                 │ 0       │ s    │ utf8    │ -1   │ 0       │
│ fts5_isalnum              │ 0       │ s    │ utf8    │ -1   │ 0       │
│ fts5_rowid                │ 0       │ s    │ utf8    │ -1   │ 0       │
│ fts5_source_id            │ 0       │ s    │ utf8    │ 0    │ 0       │
│ gavg                      │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ geopoly_area              │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ geopoly_bbox              │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ geopoly_blob              │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ geopoly_ccw               │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ geopoly_contains_point    │ 0       │ s    │ utf8    │ 3    │ 2099200 │
│ geopoly_debug             │ 0       │ s    │ utf8    │ 1    │ 524288  │
│ geopoly_group_bbox        │ 0       │ a    │ utf8    │ 1    │ 2099200 │
│ geopoly_json              │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ geopoly_overlap           │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ geopoly_regular           │ 0       │ s    │ utf8    │ 4    │ 2099200 │
│ geopoly_svg               │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ geopoly_within            │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ geopoly_xform             │ 0       │ s    │ utf8    │ 7    │ 2099200 │
│ getfileattributes         │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ glob                      │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ globu                     │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ group_concat              │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ group_concat              │ 1       │ w    │ utf8    │ 2    │ 2097152 │
│ hex                       │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ hexw                      │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ highlight                 │ 0       │ s    │ utf8    │ -1   │ 0       │
│ hunstime                  │ 0       │ s    │ utf8    │ 0    │ 2097152 │
│ hypot                     │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ ieee754                   │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ ieee754                   │ 0       │ s    │ utf8    │ 2    │ 2097152 │
│ ieee754_exponent          │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ ieee754_from_blob         │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ ieee754_mantissa          │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ ieee754_to_blob           │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ if                        │ 0       │ s    │ utf8    │ 3    │ 2099200 │
│ ifnull                    │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ iif                       │ 1       │ s    │ utf8    │ 3    │ 2099200 │
│ instr                     │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ ipaddrblob                │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ ipblobaddr                │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ ipsubnetcontains          │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ isclr                     │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ ismaskclr                 │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ ismaskset                 │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ isset                     │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ j0                        │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ j1                        │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ jn                        │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ json                      │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ json_array                │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ json_array_length         │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ json_array_length         │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ json_extract              │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ json_group_array          │ 0       │ w    │ utf8    │ 1    │ 3147776 │
│ json_group_object         │ 0       │ w    │ utf8    │ 2    │ 3147776 │
│ json_insert               │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ json_object               │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ json_patch                │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ json_quote                │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ json_remove               │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ json_replace              │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ json_set                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ json_type                 │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ json_type                 │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ json_valid                │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ julianday                 │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ kurt                      │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ kurtp                     │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ lag                       │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ lag                       │ 1       │ w    │ utf8    │ 2    │ 2097152 │
│ lag                       │ 1       │ w    │ utf8    │ 3    │ 2097152 │
│ last_insert_rowid         │ 1       │ s    │ utf8    │ 0    │ 2097152 │
│ last_value                │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ lastnotnull               │ 0       │ a    │ utf8    │ 1    │ 2097152 │
│ ldexp                     │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ lead                      │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ lead                      │ 1       │ w    │ utf8    │ 2    │ 2097152 │
│ lead                      │ 1       │ w    │ utf8    │ 3    │ 2097152 │
│ length                    │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ like                      │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ like                      │ 1       │ s    │ utf8    │ 3    │ 2099200 │
│ likelihood                │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ likely                    │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ likeu                     │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ likeu                     │ 0       │ s    │ utf8    │ 3    │ 2099200 │
│ ln                        │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ load_extension            │ 1       │ s    │ utf8    │ 1    │ 524288  │
│ load_extension            │ 1       │ s    │ utf8    │ 2    │ 524288  │
│ log                       │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ log                       │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ log10                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ log2                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ lookupname                │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ lookupsid                 │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ lower                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ loweru                    │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ lsmode                    │ 0       │ s    │ utf8    │ 1    │ 0       │
│ ltrim                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ ltrim                     │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ m_1_pi                    │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_2_pi                    │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_2_sqrtpi                │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_deg2rad                 │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_e                       │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_ln10                    │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_ln2                     │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_log10e                  │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_log2e                   │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_pi                      │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_pi_2                    │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_pi_4                    │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_rad2deg                 │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_sqrt1_2                 │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ m_sqrt2                   │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ mantissa                  │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ match                     │ 0       │ s    │ utf8    │ 2    │ 0       │
│ matchinfo                 │ 0       │ s    │ utf8    │ 1    │ 0       │
│ matchinfo                 │ 0       │ s    │ utf8    │ 2    │ 0       │
│ max                       │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ max                       │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ md2                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ md2_query                 │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ md4                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ md4_query                 │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ md5                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ md5_query                 │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ median                    │ 0       │ a    │ utf8    │ 1    │ 2097152 │
│ min                       │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ min                       │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ mod                       │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ money                     │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ mprint                    │ 0       │ s    │ utf8    │ 0    │ 2097152 │
│ mprint                    │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ mprint1                   │ 0       │ s    │ utf8    │ 0    │ 2097152 │
│ mprint1                   │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ mprint2                   │ 0       │ s    │ utf8    │ 0    │ 2097152 │
│ mprint2                   │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ mprint3                   │ 0       │ s    │ utf8    │ 0    │ 2097152 │
│ mprint3                   │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ next_char                 │ 0       │ s    │ utf8    │ 3    │ 2097152 │
│ next_char                 │ 0       │ s    │ utf8    │ 4    │ 2097152 │
│ next_char                 │ 0       │ s    │ utf8    │ 5    │ 2097152 │
│ nth_value                 │ 1       │ w    │ utf8    │ 2    │ 2097152 │
│ ntile                     │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ nullif                    │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ offsets                   │ 0       │ s    │ utf8    │ 1    │ 0       │
│ olddatetime               │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ olddatetimems             │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ oldtime                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ oldunlocalize             │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ oldunlocalizems           │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ optimize                  │ 0       │ s    │ utf8    │ 1    │ 0       │
│ percent_rank              │ 1       │ w    │ utf8    │ 0    │ 2097152 │
│ percentile                │ 0       │ a    │ utf8    │ 2    │ 2097152 │
│ pi                        │ 1       │ s    │ utf8    │ 0    │ 2099200 │
│ pow                       │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ power                     │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ prefix_length             │ 0       │ s    │ utf8    │ 2    │ 0       │
│ printf                    │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ proper                    │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ pstoken                   │ 0       │ s    │ utf8    │ -1   │ 2048    │
│ quote                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ radians                   │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ random                    │ 1       │ s    │ utf8    │ 0    │ 2097152 │
│ randomblob                │ 1       │ s    │ utf8    │ 1    │ 2097152 │
│ randomv                   │ 0       │ s    │ utf8    │ -1   │ 2097152 │
│ range                     │ 0       │ a    │ utf8    │ 1    │ 2097152 │
│ rank                      │ 1       │ w    │ utf8    │ 0    │ 2097152 │
│ readfile                  │ 0       │ s    │ utf8    │ 1    │ 524288  │
│ recsize                   │ 0       │ s    │ utf16be │ -1   │ 2099200 │
│ recsize                   │ 0       │ s    │ utf16le │ -1   │ 2099200 │
│ recsize                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ regexp                    │ 0       │ s    │ utf8    │ 2    │ 2097152 │
│ replace                   │ 1       │ s    │ utf8    │ 3    │ 2099200 │
│ rms                       │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ rot13                     │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ round                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ round                     │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ roundda                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ rounddd                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ rounddt                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ rounddu                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ roundha                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ roundhd                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ roundhe                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ roundho                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ roundht                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ roundhu                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ row_number                │ 1       │ w    │ utf8    │ 0    │ 2097152 │
│ rtreecheck                │ 0       │ s    │ utf8    │ -1   │ 0       │
│ rtreedepth                │ 0       │ s    │ utf8    │ 1    │ 0       │
│ rtreenode                 │ 0       │ s    │ utf8    │ 2    │ 0       │
│ rtrim                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ rtrim                     │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ sem                       │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ sem                       │ 0       │ w    │ utf8    │ 2    │ 2097152 │
│ setbits                   │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha1                      │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha1_query                │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ sha256                    │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha256_query              │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ sha2_256                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha2_256_query            │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ sha2_384                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha2_384_query            │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ sha2_512                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha2_512_query            │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ sha384                    │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha384_query              │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ sha3_224                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha3_256                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha3_384                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha3_512                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha3_query                │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ sha3_query                │ 0       │ s    │ utf8    │ 2    │ 526336  │
│ sha512                    │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sha512_query              │ 0       │ s    │ utf8    │ 1    │ 526336  │
│ shell_add_schema          │ 0       │ s    │ utf8    │ 3    │ 0       │
│ shell_escape_crnl         │ 0       │ s    │ utf8    │ 1    │ 0       │
│ shell_idquote             │ 0       │ s    │ utf8    │ 1    │ 0       │
│ shell_int32               │ 0       │ s    │ utf8    │ 2    │ 0       │
│ shell_module_schema       │ 0       │ s    │ utf8    │ 1    │ 0       │
│ shell_putsnl              │ 0       │ s    │ utf8    │ 1    │ 0       │
│ sigdigits                 │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ sign                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ sin                       │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ sinh                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ skew                      │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ skewp                     │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ snippet                   │ 0       │ s    │ utf8    │ -1   │ 0       │
│ soundex                   │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ spellfix1_editdist        │ 0       │ s    │ utf8    │ 2    │ 2048    │
│ spellfix1_phonehash       │ 0       │ s    │ utf8    │ 1    │ 2048    │
│ spellfix1_scriptcode      │ 0       │ s    │ utf8    │ 1    │ 2048    │
│ spellfix1_translit        │ 0       │ s    │ utf8    │ 1    │ 2048    │
│ sqlar_compress            │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ sqlar_uncompress          │ 0       │ s    │ utf8    │ 2    │ 2097152 │
│ sqlite_compileoption_get  │ 1       │ s    │ utf8    │ 1    │ 2097152 │
│ sqlite_compileoption_used │ 1       │ s    │ utf8    │ 1    │ 2097152 │
│ sqlite_log                │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ sqlite_offset             │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ sqlite_source_id          │ 1       │ s    │ utf8    │ 0    │ 2097152 │
│ sqlite_version            │ 1       │ s    │ utf8    │ 0    │ 2097152 │
│ sqrt                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ stdev                     │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ stdev                     │ 0       │ w    │ utf8    │ 2    │ 2097152 │
│ stdevp                    │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ stdevp                    │ 0       │ w    │ utf8    │ 2    │ 2097152 │
│ strdup                    │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ strfilter                 │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ strftime                  │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ strpos                    │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ strpos                    │ 0       │ s    │ utf8    │ 3    │ 2099200 │
│ strtaboo                  │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ substr                    │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ substr                    │ 1       │ s    │ utf8    │ 3    │ 2099200 │
│ substring                 │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ substring                 │ 1       │ s    │ utf8    │ 3    │ 2099200 │
│ sum                       │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ tan                       │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ tanh                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ time                      │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ time                      │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ timebeginperiod           │ 0       │ s    │ utf8    │ -1   │ 526336  │
│ timeendperiod             │ 0       │ s    │ utf8    │ -1   │ 526336  │
│ timemodifier              │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ title                     │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ tobesttype                │ 0       │ s    │ utf8    │ 1    │ 2097152 │
│ tointeger                 │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ tokenhasname              │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ tokenhassid               │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ toreal                    │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ total                     │ 1       │ w    │ utf8    │ 1    │ 2097152 │
│ total_changes             │ 1       │ s    │ utf8    │ 0    │ 2097152 │
│ trim                      │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ trim                      │ 1       │ s    │ utf8    │ 2    │ 2099200 │
│ trunc                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ trunc                     │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ trunc                     │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ typeof                    │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ typos                     │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ ulp                       │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ ulps                      │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ unaccent                  │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ uncompress                │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ unhex                     │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ unicode                   │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ unifuzz                   │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ unixinstant               │ 0       │ s    │ utf8    │ 0    │ 2097152 │
│ unixtime                  │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ unknown                   │ 1       │ s    │ utf8    │ -1   │ 2099200 │
│ unlikely                  │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ unlocaldate               │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ unlocalize                │ 0       │ s    │ utf8    │ -1   │ 2099200 │
│ unzorder                  │ 0       │ s    │ utf8    │ 3    │ 0       │
│ upper                     │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ upperu                    │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ username                  │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ usersid                   │ 0       │ s    │ utf8    │ 0    │ 2099200 │
│ usleep                    │ 0       │ s    │ utf8    │ 1    │ 0       │
│ uuid                      │ 0       │ s    │ utf8    │ 0    │ 2097152 │
│ uuid_blob                 │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ uuid_str                  │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ uuidblob                  │ 0       │ s    │ utf8    │ 0    │ 2097152 │
│ uuidstring                │ 0       │ s    │ utf8    │ 0    │ 2097152 │
│ var                       │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ var                       │ 0       │ w    │ utf8    │ 2    │ 2097152 │
│ varp                      │ 0       │ w    │ utf8    │ 1    │ 2097152 │
│ varp                      │ 0       │ w    │ utf8    │ 2    │ 2097152 │
│ writefile                 │ 0       │ s    │ utf8    │ -1   │ 524288  │
│ y0                        │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ y1                        │ 0       │ s    │ utf8    │ 1    │ 2099200 │
│ yn                        │ 0       │ s    │ utf8    │ 2    │ 2099200 │
│ zeroblob                  │ 1       │ s    │ utf8    │ 1    │ 2099200 │
│ zipfile                   │ 0       │ a    │ utf8    │ -1   │ 0       │
│ zipfile_cds               │ 0       │ s    │ utf8    │ -1   │ 0       │
│ zorder                    │ 0       │ s    │ utf8    │ -1   │ 0       │
└───────────────────────────┴─────────┴──────┴─────────┴──────┴─────────┘

(12) By Erlend E. Aasland (erlendaasland) on 2020-12-31 23:41:25 in reply to 8 [link] [source]

Visual Studio Community 2017 version 15.9.30, MSC v1916

Toolchains, build types:

  • Win32, debug build: fails
  • Win32, release build: fails
  • x64, debug build: fails
  • x64, release build: fails

compile_options:

COMPILER=msvc-1916
ENABLE_FTS4
ENABLE_FTS5
ENABLE_JSON1
THREADSAFE=1

This is, obviously, what compile_options contains before adding SQLITE_ENABLE_MATH_FUNCTIONS to the preprocessor defines list, as the build fails when SQLITE_ENABLE_MATH_FUNCTIONS is defined.

(13) By Keith Medcalf (kmedcalf) on 2020-12-31 23:48:28 in reply to 8 [link] [source]

Try compiling with SQLITE_DISABLE_INTRINSIC defined.

SqliteInt.h yanks in intrinsics which includes the __ceil / __floor / __trunc functions. Though I cannot see how this works differently for me vs you.

/*
** Make sure that the compiler intrinsics we desire are enabled when
** compiling with an appropriate version of MSVC unless prevented by
** the SQLITE_DISABLE_INTRINSIC define.
*/
#if !defined(SQLITE_DISABLE_INTRINSIC)
#  if defined(_MSC_VER) && _MSC_VER>=1400
#    if !defined(_WIN32_WCE)
#      include <intrin.h>
#      pragma intrinsic(_byteswap_ushort)
#      pragma intrinsic(_byteswap_ulong)
#      pragma intrinsic(_byteswap_uint64)
#      pragma intrinsic(_ReadWriteBarrier)
#    else
#      include <cmnintrin.h>
#    endif
#  endif
#endif

Especially as I modified func.c to add either #pragma function(ceil, floor, trunc) or #pragma intrinsic(ceil, floor, trunc), neither of which made a whit of difference and did not seems to make MSVC cry.

(14) By Keith Medcalf (kmedcalf) on 2021-01-01 00:02:46 in reply to 13 [link] [source]

Fascinating. There are intrinsic forms of ceil and floor, but not of trunc (at least so testing shows). However the compiler even when explicitly told numerous times by numerous redundant methods to use the intrinsic it insists on using the library version.

(17) By Larry Brasfield (LarryBrasfield) on 2021-01-01 00:17:06 in reply to 14 [link] [source]

You've got your finger on it. It suffices to get #pragma function(ceil, floor) into the sqlite3.c compilation before the address of either "function" is taken. Then, whether SQLITE_DISABLE_INTRINSIC is #define'd or not, those 3 errors vanish.

(19) By Erlend E. Aasland (erlendaasland) on 2021-01-01 00:27:27 in reply to 17 [link] [source]

Yes, that works over here as well, regardless of fp mode.

(21) By Erlend E. Aasland (erlendaasland) on 2021-01-01 00:49:50 in reply to 17 [link] [source]

Will this "issue" be transferred to the bug tracker, or is it regarded as a "non-issue"?

(22.1) By Larry Brasfield (LarryBrasfield) on 2021-01-01 01:02:47 edited from 22.0 in reply to 21 [link] [source]

The "bug tracker" is not visible outside of the SQLite dev group. Bugs are reported in this forum. Although Richard Hipp has already noticed this thread, it may still be worthwhile for you to edit the thread title to something like: "Bug: ceil and floor function addresses cannot be taken in MSVC (diagnosed)" (I suggest you do it because you, as the thread originator, are privileged to do so.)

I doubt the SQLite developers will regard this as a non-issue. The Microsoft C compiler has been extensively catered to already. This is just more of the same.

(23) By Erlend E. Aasland (erlendaasland) on 2021-01-01 01:11:55 in reply to 22.1 [link] [source]

Great, thanks!

(25.1) By Larry Brasfield (LarryBrasfield) on 2021-01-01 16:28:18 edited from 25.0 in reply to 23 [link] [source]

Early today, Richard fixed this problem. Today's pre-release download includes it.

I took a look at the assembler code generated by the VS2019 C compiler [a] for the two wrapper functions added to make addressable code for ceil and floor. Such code is in the math library, (for use when code generation does use the intrinsic forms), reached from those wrappers via a lone jump instruction.

[a. That would be "Microsoft (R) C/C++ Optimizing Compiler Version 19.28.29335 for x64", Keith. ]

(26) By stonebig on 2021-01-01 16:55:10 in reply to 25.1 [link] [source]

would it be possible to have, in the future, the amalgation source code of the dev "trunk" branch ? It's way simpler to compile with the amalgation.

(27) By Richard Hipp (drh) on 2021-01-01 16:59:39 in reply to 26 [link] [source]

That is available in the Prerelease Snapshot which is uploaded from time to time. If you need the very latest trunk version, just run:

make sqlite3.c

Or on Windows:

nmake /f Makefile.msc sqlite3.c

(28) By stonebig on 2021-01-01 17:20:09 in reply to 27 [link] [source]

Yes ! Thank you so much.

cl sqlite3.c -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_API=__declspec(dllexport) -link -dll -out:sqlite3.dll

and this work on Python-3.10.0a3 / Windows 10 20H2

with datas(criteria, qty) as( values ('a', 1) ,('a', 2) , ('a', 3), ('a', 4) ,('a', 5), ('b',1), ('b',3) ) , intermed(criteria, squared) as (select criteria, power(qty-avg(qty) over (partition by criteria) , 2) from datas )

select criteria, sqrt(sum(squared)/count(*)) as stddev from intermed group by criteria

(29) By stonebig on 2021-01-01 17:38:35 in reply to 27 [link] [source]

proof that it works is there : https://github.com/winpython/winpython/issues/915

(31) By stonebig on 2021-01-01 18:57:55 in reply to 29 [link] [source]

on a imprecision test:

pydef py_sin(s): "sinus function : example loading module, handling input/output as strings" import math as py_math return ("%s" % py_math.sin(s*1));

WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 30000 )

select sum(sin(x)) , sum(py_sin(x)) from cnt

gives : 1.0597896088923804 , 1.0597896088923986

vs for sqlserver 1.05978960889238

==> so having the math function included is slightly better for precision.

sqlserver code:

WITH cnt(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM cnt where x < 30000 )

select sum(sin(x)) from cnt option (MAXRECURSION 31000) -- sqlserver 2019 (standard) is limited to 32767 recursions

(32.1) By Keith Medcalf (kmedcalf) on 2021-01-02 03:09:52 edited from 32.0 in reply to 31 [link] [source]

The most reasonably correct answer I can compute is 1.05978960889239021277933445

This is obtained using the Quadmath library to calculate the sin function and accumulate the sum, then print the result (rounded to fit a 80-bit extended IEEE-floating value) to 26 decimal places.

Performing the computation entirely in double precision (MSVC) gives the following result:

sqlite> WITH cnt(x)
   ...>   AS (
   ...>          SELECT 1
   ...>       UNION ALL
   ...>          SELECT x + 1
   ...>            FROM cnt
   ...>           WHERE x < 30000
   ...>      )
   ...> select printf('%!.26f', sum(sin(x))), ulps(1.05978960889239021277933445,sum(sin(x)))
   ...>   from cnt;
┌───────────────────────────────┬────────────────────────────────────────────────┐
│ printf('%!.26f', sum(sin(x))) │ ulps(1.05978960889239021277933445,sum(sin(x))) │
├───────────────────────────────┼────────────────────────────────────────────────┤
│ 1.0597896088923786095392642   │ 53.0                                           │
└───────────────────────────────┴────────────────────────────────────────────────┘

and with MinGW64 GCC where the sin is a double giving double and the sum accumulator is __float128 "rounded even" into a double:

sqlite> WITH cnt(x)
   ...>   AS (
   ...>          SELECT 1
   ...>       UNION ALL
   ...>          SELECT x + 1
   ...>            FROM cnt
   ...>           WHERE x < 30000
   ...>      )
   ...> select printf('%!.26f', sum(sin(x))), ulps(1.05978960889239021277933445,sum(sin(x)))
   ...>   from cnt;
┌───────────────────────────────┬────────────────────────────────────────────────┐
│ printf('%!.26f', sum(sin(x))) │ ulps(1.05978960889239021277933445,sum(sin(x))) │
├───────────────────────────────┼────────────────────────────────────────────────┤
│ 1.05978960889239104403714     │ -4.0                                           │
└───────────────────────────────┴────────────────────────────────────────────────┘

So a lot of precision is lost when doing the sum with insufficient precision.

You have to be careful claiming something is more "precise" than something else, particularly when the computation method may be flawed.

Note also that in the github link the actual value is: 0.03873134997726595606822833

But the MSVC (all double) result is:

sqlite> WITH cnt(x)
   ...>   AS (
   ...>          SELECT 1
   ...>       UNION ALL
   ...>          SELECT x + 1
   ...>            FROM cnt
   ...>           WHERE x < 3000000
   ...>      )
   ...> select printf('%!.26f', sum(sin(x))), ulps(0.03873134997726595606822833,sum(sin(x)))
   ...>   from cnt;
┌───────────────────────────────┬────────────────────────────────────────────────┐
│ printf('%!.26f', sum(sin(x))) │ ulps(0.03873134997726595606822833,sum(sin(x))) │
├───────────────────────────────┼────────────────────────────────────────────────┤
│ 0.03873134997718130634325461  │ 12199.0                                        │
└───────────────────────────────┴────────────────────────────────────────────────┘

which has a huge error due to the lack of a high precision accumulator, but in this case the high precision accumulator is spot on

sqlite> WITH cnt(x)
   ...>   AS (
   ...>          SELECT 1
   ...>       UNION ALL
   ...>          SELECT x + 1
   ...>            FROM cnt
   ...>           WHERE x < 3000000
   ...>      )
   ...> select printf('%!.26f', sum(sin(x))), ulps(0.03873134997726595606822833,sum(sin(x)))
   ...>   from cnt;
┌───────────────────────────────┬────────────────────────────────────────────────┐
│ printf('%!.26f', sum(sin(x))) │ ulps(0.03873134997726595606822833,sum(sin(x))) │
├───────────────────────────────┼────────────────────────────────────────────────┤
│ 0.03873134997726595390998838  │ 0.0                                            │
└───────────────────────────────┴────────────────────────────────────────────────┘

What is the significance you might ask? Well, it depends on what the result of the calculation is used for. If it is for the gap in a spark plug, a variance in the 14th decimal digit is not of significance. However, if it is calculating the trajectory of an Interconntiental Ballistic Missle, it could be the difference between the destruction of New York -vs- Beijing.

NB: I have modified the context in func.c for the SUM and TOTAL functions to use a LONGDOUBLE_TYPE for the floating point accumulator. For GCC I have redefined LONGDOUBLE_TYPE to be __float128 rather than long double. MSVC completely ignores all requests for extended precision and treats a "long double" as a mere "double" on x64 platforms. Results will vary depending on compiler and platform.

(30) By stonebig on 2021-01-01 18:56:07 in reply to 27 [link] [source]

on a imprecision test:

pydef py_sin(s): "sinus function : example loading module, handling input/output as strings" import math as py_math return ("%s" % py_math.sin(s*1));

WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 30000 )

select sum(sin(x)) , sum(py_sin(x)) from cnt

gives : 1.0597896088923804 , 1.0597896088923986

vs for sqlserver 1.05978960889238

==> so having the math function included is slightly better for precision.

sqlserver code:

WITH cnt(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM cnt where x <=30000 )

select sum(sin(x)) from cnt
option (MAXRECURSION 31000) -- sqlserver 2019 (standard) is limited to 32767 recursions

(15) By Erlend E. Aasland (erlendaasland) on 2021-01-01 00:05:57 in reply to 13 [link] [source]

Using strict floating-point model resolves the issue for all the four configurations (32-bit/64-bit, release/debug). Using precise or fast floating-point models fails for all configurations.

(18) By Larry Brasfield (LarryBrasfield) on 2021-01-01 00:26:29 in reply to 15 [link] [source]

This is probably inherently correlated with the intrinsic aspect of ceil and floor. The corresponding FPU (floating point unit) instructions are going to be faster and/or more precise, the former by avoiding a function call and the latter by often using the FPU's higher precision intermediate results.

(20) By Erlend E. Aasland (erlendaasland) on 2021-01-01 00:29:18 in reply to 18 [link] [source]

Yes, that makes sense.

(24) By Keith Medcalf (kmedcalf) on 2021-01-01 01:25:54 in reply to 15 [link] [source]

Fascinating, but I am using /fp:precise and am getting no errors.

On the gripping hand, I am generating a completely stand-alone executable/load-library that is not dependent on any runtime (other than the subsystem runtime, msvcrt).

Mind you, I have a Xeon processor which uses AVR2 for everything, so mayhaps that makes a difference.

(16) By Erlend E. Aasland (erlendaasland) on 2021-01-01 00:14:52 in reply to 13 [link] [source]

BTW, compiling with SQLITE_DISABLE_INTRINSIC has no effect.