SQLite Forum

Timeline
Login

15 forum posts by user howardk

2022-01-06
06:55 Reply: Support for blocking VFS (artifact: dfa5107ab0 user: howardk)

Post-holiday bump

2021-12-07
04:58 Reply: Support for blocking VFS (artifact: d0d0c281f7 user: howardk)

Any news on this? It would be very useful for many scenarios

2021-05-18
21:44 Reply: PRAGMAs as functions ? (artifact: 5c73419a95 user: howardk)

Doh! I saw that and entirely misread the pragma_foo() text and examples. My bad. Sorry about that, and thanks

21:40 Reply: sqlite3rebaser_*() not documented (artifact: db07666e7e user: howardk)

Ah. Thanks!

17:49 Post: sqlite3rebaser_*() not documented (artifact: f89cf23fb4 user: howardk)

Latest win32 dll downloads include sqlite3rebaser_* functions in sqlite3.def but they're not listed on https://sqlite.org/c3ref/funclist.html

One of those seems in error :-)

2021-05-16
23:05 Post: PRAGMAs as functions ? (artifact: 956e14ac8a user: howardk)

Does SQLite provide read access to (some/most/which) PRAGMAs?

I'm specifically interested in user_version with a secondary interest in other informational functions e.g.

sqlite> select user_version();
Error: no such function: user_version
sqlite> select data_version();
Error: no such function: data_version
sqlite> select application_id();
Error: no such function: application_id
sqlite> select page_size();
Error: no such function: page_size
sqlite> select cache_size();
Error: no such function: cache_size

plus statistics via sqlite3_config options per https://www.sqlite.org/c3ref/c_config_covering_index_scan.html

(which for some inexplicable reason are doc'd on the webpage named c_config_covering_index_scan.html)

Perhaps there's a compile option I've overlooked? Running with...

sqlite> pragma compile_options;
compile_options
COMPILER=gcc-5.2.0
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
THREADSAFE=0
2021-04-19
23:47 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: daed9e4df5 user: howardk)

if you can make the bit-mask a fixed value in the SQL Statement

Generally not. It might be possible in a handful of cases but that's more an exception than the rule.

The database predates SQLite's bitwise support, but once that was available we starting leveraging it pretty regularly (most tables have at least 1 bitfield, some have multiple). Reworking the design can pose significant impact. I'll consider it (in part or whole), if necessary. But I'm hoping it won't be necesssary

19:40 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: 1e06ce5ee7 user: howardk)

Sorry, when I said it's not significant I meant it is but it doesn't trump the process issues. That doesn't mean I don't get constant perf 'feedback' :P especially by folks who don't understand the process issues and don't worry about the (significant) cost to address those.

The goal is 'instantaneous' so anything over zero is undesirable. Necessary of course, but there's a strong recurring desire to be slimmer. I'm surprised bitwise expressions don't involve indexes(1) and was hoping SQLite could work more optimization magic

Adding indexes didn't help with some experimentation. I'd need to do a deeper redesign-think to see what options might exist, other than exploding INTEGER bitfield columns into multiple INTEGER boolean columns. But I'm really hoping I don't have to go there...

(1) we have a fixed system w/o adhoc queries so for perf reasons we aim for 100% index coverage

19:32 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: b10111d5f9 user: howardk)

I only use bitwise operators on columns designed to be bitfields. Even though INTEGER is int64 I only use it as uint64 (when bitfields are in play).

That's also why I only need equality comparisons (often !=0, less often =0, rarely =0x1234 or other multi-bit pattern)

I use expression-based and partial indexes for other reasons but after some quick experimentation they don't seem to help here. I'd need to deeper analysis to see if I could make it work, but it's a complex puzzle so fiddling here can have unintended consequences. IOW like many things in life, it's complicated

18:55 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: b145a44fbd user: howardk)

In many cases, using bit encoding to try and squeeze a bit of efficiency is the wrong tactic, better to have a set of binary fields that you can build indexes on if you really want to be able to select on things like this efficiently

I do it for process efficiency, not runtime.

Schema changes are significant events (for non-technical reasons) whereas using a previously reserved bit is a trivial non-event. Using INTEGER as a logical BOOLEAN[64] avoids these non-technical issues.

There's also some runtime efficiency but that's not as significant to me.

18:51 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: f36af5376a user: howardk)

In one case I'm fetching data from table A that meets criteria in table B

SELECT * FROM A
INNER JOIN B ON B.a=A.rowid
WHERE B.name=? AND B.bitfield & 0x0040 != 0;

Assume table B has more columns than bitfield. Why do I need a table scan of B if I can CREATE INDEX x ON B(name, bitfield);? Table B has other columns so the index has greater density e.g. if there's 2 records per page for table B but 20 per index page. Table scans are significantly less efficient than indexes.

I'm not using bitwise operators for relational comparisons. I'm using an INTEGER column as a bitfield and only interested in (not)equality. ORDER BY doesn't help.

18:42 Reply: SQLite doesn't use indexes for bitwise clauses? (artifact: f83a4a3cef user: howardk)

Bitwise evaluation of the index value?

Functionally we only use bitwise operators when we use a column as a bitfield, and trying to do equivalent non-bitwise operations doesn't make much sense to me.

IF (x & 0x0040 != 0)

is equivalent to what without bitwise operators?

SQLite knows I specified a bitwise operator in the expression so the hope/expectation is it could do...something.

In theory I could specify some kind of hint e.g. *column == use bitwise index evaluation

SELECT * FROM table WHERE *x & ? != 0;

I could even live with a hint in the schema definition to indicate it supports bitwise operations (CREATE TABLE..N INTEGER NOT NULL HINT BITFIELD... or something in CREATE INDEX?). I'd rather see SQLite automagically handle it but the perf benefits should be significant enough I can justify additional markup on my part if necessary

There's always the new-type option (BITFIELD instead of INTEGER) but I expect you're even less of a fan than I am (and I'm not :P)

2021-04-18
01:35 Post: SQLite doesn't use indexes for bitwise clauses? (artifact: 6a9e5b1b09 user: howardk)

REPRO: Create a table with an index covering a not-null integer. EXPLAIN QUERY PLAN SELECT * FROM table WHERE... reports the index is used for WHERE n=? and WHERE n>? but not WHERE n & ? != 0

This is with SQLite 3.34.1

sqlite> .schema resource
CREATE TABLE Resource(_ResourceID INTEGER PRIMARY KEY NOT NULL,_Revision INTEGER NOT NULL DEFAULT 1,_WorkId INTEGER NOT NULL DEFAULT 0,Package INTEGER NOT NULL,"Index" INTEGER NOT NULL,Language TEXT NOT NULL COLLATE NOCASE);
CREATE UNIQUE INDEX IDX_Resource_Package_Index__WorkId ON Resource(Package, "Index", _WorkId);
CREATE INDEX IDX_Resource__WorkId ON Resource(_WorkId) WHERE _WorkId<>0;
sqlite>
sqlite>
sqlite>
sqlite> explain query plan select * from resource where package=?;
QUERY PLAN
`--SEARCH TABLE resource USING INDEX IDX_Resource_Package_Index__WorkId (Package=?)
sqlite>
sqlite>
sqlite> explain query plan select * from resource where package>?;
QUERY PLAN
`--SEARCH TABLE resource USING INDEX IDX_Resource_Package_Index__WorkId (Package>?)
sqlite>
sqlite>
sqlite> explain query plan select * from resource where package & ? != 0;
QUERY PLAN
`--SCAN TABLE resource
sqlite>
sqlite>
sqlite> explain query plan select * from resource where package & ? = 0;
QUERY PLAN
`--SCAN TABLE resource

We use bitwise logic in multiple places. Usually in the form x & ? != 0 or x & ? = 0, and less often testing against non-zero e.g. x & ? = 0x1408

I was little surprised the indexes aren't leveraged for bitwise comparisons. Is there a reason why not or is it just an oversight that can be addressed in vNext?

2021-01-09
06:58 Reply: Compiler warnings in shell.c for amalgamation 3.34.0 (artifact: d679313277 user: howardk)

Yes they're all harmless, but we use /WX so all warnings are treated as errors. I worked around it by switched to /W2 to compile this but it's noise if anyone else builds with /W4 so thought it'd be polite to add tweak the code to not warn.

My bad not including the compiler version and settings. Listed below

Key option to repro is /W4. Switch to /W2 and poof! No warnings

3001>Microsoft (R) C/C++ Optimizing Compiler Version 19.28.29395.4 for x64
3001>Copyright (C) Microsoft Corporation.  All rights reserved.
3001>   /FC
...various /I... options omitted for brevity...
3001>   /D_WIN64
3001>   /D_AMD64_
3001>   /DAMD64
3001>   /DCONDITION_HANDLING=1
3001>   /DNT_UP=1
3001>   /DNT_INST=0
3001>   /DWIN32=100
3001>   /D_NT1X_=100
3001>   /DWINNT=1
3001>   /D_WIN32_WINNT=0x0A00
3001>   /DWINVER=0x0A00
3001>   /D_WIN32_IE=0x0A00
3001>   /DWIN32_LEAN_AND_MEAN=1
3001>   /DDEVL=1
3001>   /DNDEBUG
3001>   /D_DLL=1
3001>   /D_MT=1
3001>   /wd4127
3001>   /wd4100
3001>   /DSQLITE_ENABLE_API_ARMOR
3001>   -D_ARM_WINAPI_PARTITION_DESKTOP_SDK_AVAILABLE
3001>   /D_USE_DEV11_CRT
3001>   /D__WRL_CONFIGURATION_LEGACY__
3001>   /DEXECUTABLE_WRITES_SUPPORT=0
3001>   /c
3001>   /Zc:wchar_t-
3001>   /Zl
3001>   /Zp8
3001>   /Gy
3001>   /W4
3001>   /d1import_no_registry
3001>   /EHs-c-
3001>   /GR-
3001>   /GF
3001>   /GS
3001>   /Z7
3001>   /Oxs
3001>   /GL
3001>   /Z7
3001>   /Zc:rvalueCast
3001>   /w15043
3001>   /std:c++17
3001>   /Zc:noexceptTypes
3001>   -D_CONST_RETURN=
3001>   -D_CRT_SECURE_NO_WARNINGS
3001>   -D_CRT_NON_CONFORMING_SWPRINTFS
3001>   -D_CRT_NONSTDC_NO_WARNINGS
3001>   -D_NO_CPPLIB_VER
3001>   -D_CRT_STDIO_ARBITRARY_WIDE_SPECIFIERS
3001>   /D_CRT_STDIO_INLINE=extern
3001>   /D_NO_CRT_STDIO_INLINE
3001>   /D_ACRTIMP_ALT=
3001>   /D_ALLOW_MSC_VER_MISMATCH
3001>   /D_ALLOW_ITERATOR_DEBUG_LEVEL_MISMATCH
3001>   /D_ALLOW_RUNTIME_LIBRARY_MISMATCH
3001>   /D_SILENCE_STDEXT_HASH_DEPRECATION_WARNINGS
3001>   /D_STL_EXTRA_DISABLED_WARNINGS=4239
3001>   /D_SILENCE_TR1_NAMESPACE_DEPRECATION_WARNING
3001>   /D_SILENCE_ALL_CXX17_DEPRECATION_WARNINGS
3001>   /D_SILENCE_TR2_SYS_NAMESPACE_DEPRECATION_WARNING
3001>   /D_HAS_FUNCTION_ALLOCATOR_SUPPORT=1
3001>   /D_SILENCE_STDEXT_ALLOCATORS_DEPRECATION_WARNING
3001>   /D_HAS_STD_BYTE=0
3001>   /D_ENFORCE_MATCHING_ALLOCATORS=0
3001>   /D_HAS_FUNCTION_ALLOCATOR_SUPPORT=1
3001>   /D_SILENCE_STDEXT_ALLOCATORS_DEPRECATION_WARNING
3001>   /D_FULL_IOBUF
3001>   /d1nodatetime
3001>   /d2AllowCompatibleILVersions
3001>   /d2Zi+
3001>   /ZH:SHA_256
3001>   /wd4986
3001>   /wd4987
3001>   /wd4471
3001>   /wd4369
3001>   /wd4309
3001>   /wd4754
3001>   /wd4427
3001>   /d2DeepThoughtInliner-
3001>   /Zc:tlsGuards-
3001>   /Wv:19.23
3001>   /d2Qvec-mathlib-
3001>   /d2Qvec-sse2only
3001>   /Gw
3001>   /d1ignorePragmaWarningError
3001>   /wd4316
3001>   /wd4973
3001>   /DDONT_DISABLE_PCH_WARNINGS_IN_WARNING_H
3001>   /d2FH4
3001>   /Brepro
3001>   -D_HAS_MAGIC_STATICS=1
3001>   /Qspectre
3001>   /wd5045
3001>   /d2guardspecanalysismode:v1_0
3001>   /d2guardspecmode2
3001>   /guard:cf
3001>   /d1guard:xfg
3001>   /d2guardxfg
3001>   /D_CONTROL_FLOW_GUARD_XFG=1
3001>   /d2guardehcont
3001>   .\shell.c
3001>shell.c
3001>d:\repo\sqlite3\shell\shell.c(4776): warning C4242: '=': conversion from 'int' to 'char', possible loss of data
3001>errors in directory d:\repo\sqlite3\shell
3001>d:\repo\sqlite3\shell\shell.c(4776) : error C4242: '=': conversion from 'int' to 'char', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(4797): warning C4242: '=': conversion from 'int' to 'char', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(4797) : error C4242: '=': conversion from 'int' to 'char', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(13100): warning C4242: 'function': conversion from 'sqlite3_int64' to 'int', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(13100) : error C4242: 'function': conversion from 'sqlite3_int64' to 'int', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(13110): warning C4242: 'function': conversion from 'sqlite3_int64' to 'int', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(13110) : error C4242: 'function': conversion from 'sqlite3_int64' to 'int', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(13127): warning C4242: '=': conversion from 'sqlite3_int64' to 'int', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(13127) : error C4242: '=': conversion from 'sqlite3_int64' to 'int', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(18155): warning C4242: '=': conversion from 'sqlite3_int64' to 'int', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(18155) : error C4242: '=': conversion from 'sqlite3_int64' to 'int', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(18247): warning C4242: '=': conversion from 'int' to 'char', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(18247) : error C4242: '=': conversion from 'int' to 'char', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(18251): warning C4242: '=': conversion from 'int' to 'char', possible loss of data
3001>d:\repo\sqlite3\shell\shell.c(18251) : error C4242: '=': conversion from 'int' to 'char', possible loss of data
2020-12-31
22:02 Post: Compiler warnings in shell.c for amalgamation 3.34.0 (artifact: deaac7d699 user: howardk)

FYI compiling the 3.34.0 amalgamation for x64 with /W4 produces warnings:

shell.c(4776) : error C4242: '=': conversion from 'int' to 'char', possible loss of data
shell.c(4797) : error C4242: '=': conversion from 'int' to 'char', possible loss of data
shell.c(13100) : error C4242: 'function': conversion from 'sqlite3_int64' to 'int', possible loss of data
shell.c(13110) : error C4242: 'function': conversion from 'sqlite3_int64' to 'int', possible loss of data
shell.c(13127) : error C4242: '=': conversion from 'sqlite3_int64' to 'int', possible loss of data
shell.c(18155) : error C4242: '=': conversion from 'sqlite3_int64' to 'int', possible loss of data
shell.c(18247) : error C4242: '=': conversion from 'int' to 'char', possible loss of data
shell.c(18251) : error C4242: '=': conversion from 'int' to 'char', possible loss of data