Re: [sqlite] Bug: float granularity breaking unique contraint?

[David Empson]

> On 2/11/2018, at 8:23 AM, James K. Lowden  wrote:
> 
> On Wed, 31 Oct 2018 23:05:19 -0300
> Bernardo Sulzbach  wrote:
> 
>> So if you are inserting integers
>> into a real column, you are going to store integers. However, when you
>> select from it they are presented as reals and mix up (looking as if
>> there were duplicates [...]
> 
> I don't think that explanation holds water.  
> 
> sqlite> 
> DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL
> UNIQUE);INSERT INTO TestReal values (9223372036854775807);INSERT INTO
> TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values
> (9223372036854775807 - 2);INSERT INTO TestReal values
> (9223372036854775807 - 3);sqlite>...>...>...>...> 
> 
> sqlite> select cast(A as integer) from TestReal;
> 9223372036854775807
> 9223372036854775807
> 9223372036854775807
> 9223372036854775807
> 
> sqlite> select hex(A) from TestReal;
> 392E323237323033363835343738652B3138
> 392E323237323033363835343738652B3138
> 392E323237323033363835343738652B3138
> 392E323237323033363835343738652B3138
> 
> sqlite> select count(*), hex(A) from TestReal group by hex(A);
> 4|392E323237323033363835343738652B3138
> 
> sqlite> .schema TestReal
> CREATE TABLE TestReal(A REAL
> UNIQUE);
> sqlite> 
> 
> Curiouser and curiouser.  

Continuing with James’s test case above, the following shows that the integer 
values are actually stored in the database as real.

select A,typeof(A) from TestReal;
9.22337203685478e+18|real
9.22337203685478e+18|real
9.22337203685478e+18|real
9.22337203685478e+18|real

Testing with smaller values:

INSERT INTO TestReal values(1); INSERT INTO TestReal values(1);
Error: UNIQUE constraint failed: TestReal.A

That did what I expected.

SELECT cast(A as integer) from TestReal;
9223372036854775807
9223372036854775807
9223372036854775807
9223372036854775807
1

INSERT INTO TestReal values(9223372036854775807);
Error: UNIQUE constraint failed: TestReal.A

The UNIQUE constraint works if the integer exactly matches the real-to-integer 
conversion of existing values in the column.

INSERT INTO TestReal values(9223372036854775807+1);

No error this time. The UNIQUE constraint didn’t pick that 
(9223372036854775807+1) will match existing values in the column when converted 
to real.

SELECT cast(A as integer) from TestReal;
9223372036854775807
9223372036854775807
9223372036854775807
9223372036854775807
1
9223372036854775807

Therefore it looks like the UNIQUE test is being done using the type of the 
value being inserted, rather than the type that will be stored in the column.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[David Empson]

Apart from the SQLITE_OK vs SQLITE_ROW/DONE check on the sqlite3_step() call 
mentioned already, you also have the third parameter to sqlite_prepare_v2() 
wrong: nByte = NULL will translate to nByte = 0 which is documented as “no 
prepared statement is generated”. Therefore stmt is not valid and 
sqlite3_step() returns SQLITE_MISUSE.

Try -1 instead of NULL.

> On 21/06/2018, at 12:44 PM, Igor Korot  wrote:
> 
> Hi, guys,
> I put in this code:
> 
>if( sqlite3_prepare_v2( m_db, "PRAGMA
> schema_version", NULL, , NULL ) == SQLITE_OK )
>{
>if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
>{
>m_schema = sqlite3_column_int( stmt, 0 );
>pimpl->m_dbName = sqlite_pimpl->m_catalog;
>}
>else
>{
>}
>}
>else
>{
>}
> 
> The call to sqlite3_step() failed - it returned 21.
> 
> Anyone sees any issues?
> 
> Thank you.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON1 extension / json_patch / no such function

[David Empson]

json_patch() was added to the json1 extension in SQLite 3.18.0, but the other 
functions were there in earlier versions as far back as SQLite 3.9.0. Looking 
at https://sqlitestudio.pl/index.rvt?act=changelog it appears the latest 
version of SQLiteStudio (3.1.1) is using SQLite 3.15.0, so that is consistent 
with it not supporting json_patch().

As for your own program, are you compiling the SQLite 3.18.0 (or later) 
amalgamation and linking it directly into your program, or are you using a 
version of SQLite which is supplied by your OS or a library, which might be an 
older version?

You can check which version of SQLite your program is using with:

SELECT sqlite_version()

> On 25/06/2017, at 8:21 PM, Robert M. Münch  
> wrote:
> 
> Hi, I'm trying to use the json_patch function in a query and tried in 
> SQLiteStudio and my own program and both times I get a "Error: Database no 
> such function: json_patch"
> 
> The query I want to do is:
> 
> UPDATE json_products SET json_value = json_patch(json_value,'{"col-1":1000}')
> 
> json_extract works and json_value too. So I'm really wondering why json_patch 
> is missing. I checked the sources, and the code is there.
> 
> I really don't have an idea what's up here.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem compiling 3.17.0 in MSVS 12

[David Empson]

I expect WinZip defaults to converting text files from Unix to DOS line 
endings, or you opened and saved the file with something which does the same 
conversion.

I don’t have WinZip handy, but using other tools…

My copy of sqlite3.c extracted from sqlite-autoconf-317.tar.gz using Take 
Command’s built-in untar command, or with a Windows port of Unix tar/gunzip 
utilities, produces a file with Unix line endings, and the expected fciv result 
of 1efd683943e0d2bce1495b3413e2e235.

If I then run it through a LF to CRLF translation, that copy has an fciv result 
of b0245450e4f27eeefc0da4a871833eb1, which matches Bart’s result.

There should be no difference between Unix and DOS line endings as far as the 
compiler is concerned, so if your re-downloaded and re-extracted sqlite3.c is 
compiling OK with nmake, it probably means your original extracted copy was 
modified up in some way.

If the errors turn up again when compiling the newly downloaded file with MSVS 
IDE, perhaps the IDE itself is the one messing up the file?

I’d investigate this as follows:

1. Use WinZip to extract a fresh copy from the .tar.gz file.
2. Take a copy of the resulting sqlite-autoconf-317 folder.
3. Do your compilation of the second copy of sqlite3.c using the MSVC IDE. 
Presumably this will get the errors again.
4. Now try compiling the second copy using nmake.
5. If nmake also gets errors, try compiling the first copy (extracted but never 
opened) with nmake.

If (4) gets errors but (5) doesn’t then the MSVS IDE modified the file in the 
process of opening or compiling it. You could then use file comparison tools to 
work out what changed.

If (3) gets errors but (4) doesn’t then MSVS IDE has something different in its 
compilation environment which is affecting how sqlite3.c is compiled, e.g. 
different header files or definitions which are conflicting with something in 
sqlite3.c.

> On 15/02/2017, at 2:14 PM, Bart Smissaert  wrote:
> 
> No idea why that is. Could the WinZip alter the file?
> Another thing is that if I run that sqlite3.c from the MSVS IDE I get the
> same errors.
> 
> RBS
> 
> On Wed, Feb 15, 2017 at 1:11 AM, Cezary H. Noweta 
> wrote:
> 
>> Hello,
>> 
>> On 2017-02-15 01:56, Bart Smissaert wrote:
>> 
>>> Downloaded again and now it looks more healthy:
>>> [...]
>>> b0245450e4f27eeefc0da4a871833eb1 sqlite3.c
>>> 
>> 
>> Still not an original one! MD5 of an original ``sqlite3.c'' is
>> 1efd683943e0d2bce1495b3413e2e235.
>> 
>> 
>> -- best regards
>> 
>> Cezary H. Noweta


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem compiling 3.17.0 in MSVS 12

[David Empson]

My thinking is that something before line 27461 in sqlite3.c which changed 
between versions 3.16.2 and 3.17.0 has bumped into a conflict with something 
defined for your compiler on that computer (e.g. from a Windows header file).

If so, you should still be able to compile 3.16.2, which you can download from 
whichever of these links you need:

https://www.sqlite.org/2017/sqlite-amalgamation-3160200.zip
https://www.sqlite.org/2017/sqlite-autoconf-3160200.tar.gz

> On 15/02/2017, at 11:15 AM, Bart Smissaert <bart.smissa...@gmail.com> wrote:
> 
> The strange thing is that nil changed other than moving from version 3.16.2
> to 3.17.0.
> There is no serious problem as I can compile on the other PC, but would
> like to figure out what is going on here.
> Will see if I can look at the pre-processor output.
> 
> RBS
> 
> 
> On Tue, Feb 14, 2017 at 10:07 PM, David Empson <demp...@emptech.co.nz>
> wrote:
> 
>>> On 15/02/2017, at 10:23 AM, Bart Smissaert <bart.smissa...@gmail.com>
>> wrote:
>>> 
>>> -c sqlite3.c
>>> sqlite3.c
>>> sqlite3.c(16114) : error C2059: syntax error : 'if'
>>> sqlite3.c(16117) : error C2059: syntax error : '}'
>>> sqlite3.c(27461) : error C2143: syntax error : missing ';' before '{'
>>> sqlite3.c(27464) : error C2065: 'db' : undeclared identifier
>> 
>> 
>> A clue which might help: the error output shows a constant stream of
>> problems starting at line 27461, which is in the function just after the
>> first use of SQLITE_SKIP_UTF8, in sqlite3Utf8CharLen(), on line 27418. The
>> errors reported for lines 16114 and 16117 (in the definition of that macro)
>> are probably related to the expansion of that macro, not its definition,
>> and the compiler has got lost after line 27418, resulting in cascading
>> errors.
>> 
>> I had a quick look at the raw hex data of my expansion of the autoconf
>> copy of sqlite3.c and I can’t see anything around the macro definition or
>> expansion which looks like it might confuse a C compiler. Line endings are
>> 0x0A (LF, UNIX \n) and everything appears to be ASCII.
>> 
>> As it seems this is only happening on one computer for one person, and
>> assuming the source file is not a bad copy on that computer, I’d suspect
>> the compiler installation or perhaps some configuration for the compiler
>> which is not overridden by the makefile, which is resulting in abnormal
>> behaviour (e.g. it may have a nonstandard include search path, or some
>> extra preprocessor symbols defined).
>> 
>> If reinstalling the compiler doesn’t help, perhaps have a look at the
>> preprocessor output to see what it is actually trying to compile around
>> source line 27418?
>> 
>> ___
>> 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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem compiling 3.17.0 in MSVS 12

[David Empson]

> On 15/02/2017, at 10:23 AM, Bart Smissaert  wrote:
> 
> -c sqlite3.c
> sqlite3.c
> sqlite3.c(16114) : error C2059: syntax error : 'if'
> sqlite3.c(16117) : error C2059: syntax error : '}'
> sqlite3.c(27461) : error C2143: syntax error : missing ';' before '{'
> sqlite3.c(27464) : error C2065: 'db' : undeclared identifier


A clue which might help: the error output shows a constant stream of problems 
starting at line 27461, which is in the function just after the first use of 
SQLITE_SKIP_UTF8, in sqlite3Utf8CharLen(), on line 27418. The errors reported 
for lines 16114 and 16117 (in the definition of that macro) are probably 
related to the expansion of that macro, not its definition, and the compiler 
has got lost after line 27418, resulting in cascading errors.

I had a quick look at the raw hex data of my expansion of the autoconf copy of 
sqlite3.c and I can’t see anything around the macro definition or expansion 
which looks like it might confuse a C compiler. Line endings are 0x0A (LF, UNIX 
\n) and everything appears to be ASCII.

As it seems this is only happening on one computer for one person, and assuming 
the source file is not a bad copy on that computer, I’d suspect the compiler 
installation or perhaps some configuration for the compiler which is not 
overridden by the makefile, which is resulting in abnormal behaviour (e.g. it 
may have a nonstandard include search path, or some extra preprocessor symbols 
defined).

If reinstalling the compiler doesn’t help, perhaps have a look at the 
preprocessor output to see what it is actually trying to compile around source 
line 27418?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[David Empson]

> On 27/01/2017, at 9:09 PM, Kim Gräsman <kim.gras...@gmail.com> wrote:
> 
> On Thu, Jan 26, 2017 at 10:08 PM, David Empson <demp...@emptech.co.nz 
> <mailto:demp...@emptech.co.nz>> wrote:
>> 
>>> On 26/01/2017, at 8:46 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
>>> 
>>>> …
>>>>  {"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>>>> };
>> 
>> The ANSI/ISO C 1990 standard states this in section 6.5.7, under Constraints:
>> 
>> “All the expressions in an initializer for an object that has static storage 
>> duration or in an initializer list for an object that has aggregate or union 
>> type shall be constant expressions.”
> 
> But this object doesn't have static storage duration, does it?
> `scalars` is just a local variable in a function:
> https://sourcecodebrowser.com/sqlite3/3.6.21/icu_8c_source.html#l00449 
> <https://sourcecodebrowser.com/sqlite3/3.6.21/icu_8c_source.html#l00449>
> 
> unless I'm looking at the wrong version.

That version does have the text quoted above.

The problem is that ANSI/ISO C 1990 stipulates that an initializer for any 
object of aggregate type, whether or not it is static, must be constant.

It doesn’t matter that this variable has auto storage - it is an aggregate type 
(array of structs), and the initializer for an aggregate type must be constant 
if a compiler is enforcing ANSI/ISO C 1990 rules (or is based on C90 but hasn’t 
implemented extensions to allow a non-constant initializer for an aggregate 
type).

As I noted, C99 changed the rules and does allow a non-constant initializer in 
this case.

The code in question has been there since May 2007, so the fact that nobody 
reported it until now suggests that almost nobody who uses the ICU extension is 
doing so with a strict ANSI C90 compiler (or a compiler set to enforce C90 
rules).

Perhaps SQLite’s test procedure should be enforcing strict ANSI C mode? If this 
is already being done, then the compiler(s) used might not be enforcing this 
particular rule.

> Again, it would be nice to see the actual warning from MSVC.

Indeed.

As it happens, I have an installation of Visual C++ 2008 so can test this, not 
using icu.c as I don’t have necessary support files installed, but I can create 
a mockup using a copy of the sqlite3IcuInit function with stubs for everything 
it references.

Using default options from the command line, my test file compiles without 
error.

Therefore Visual C++ 2008 does have extensions to ANSI C90 which allow a 
non-constant initializer for an aggregate type with auto storage. (I also 
tested Visual C++ 98 and it worked there too.)

However if I tell Visual C++ to enable strict ANSI C mode by adding the /Za 
command line option, I get an error:


[C:\p\test]cl /Za test.c
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 15.00.30729.01 for 80x86
Copyright (C) Microsoft Corporation.  All rights reserved.

test.c
test.c(45) : error C2097: illegal initialization


This is consistent with the rule I noted from ANSI/ISO C 1990.

For reference, here is my test.c:


typedef struct { int a; } sqlite3;
typedef struct { int b; } sqlite3_context;
typedef struct { int c; } sqlite3_value;

#define SQLITE_ANY  1
#define SQLITE_DETERMINISTIC2
#define SQLITE_UTF164
#define SQLITE_UTF8 8

#define SQLITE_OK   0

void icuRegexpFunc(sqlite3_context*c, int i, sqlite3_value**vpp) { }
void icuCaseFunc16(sqlite3_context*c, int i, sqlite3_value**vpp) { }
void icuLikeFunc(sqlite3_context*c, int i, sqlite3_value**vpp) { }
void icuLoadCollation(sqlite3_context*c, int i, sqlite3_value**vpp) { }

int sqlite3_create_function(sqlite3 *db, const char *zName, int nArg, int enc, 
void *pContext,
void 
(*xFunc)(sqlite3_context*,int,sqlite3_value**), int i, int j) {
  return SQLITE_OK;
}

int sqlite3IcuInit(sqlite3 *db){
  struct IcuScalar {
const char *zName;/* Function name */
int nArg; /* Number of arguments */
int enc;  /* Optimal text encoding */
void *pContext;   /* sqlite3_user_data() context */
void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
  } scalars[] = {
{"regexp", 2, SQLITE_ANY|SQLITE_DETERMINISTIC,  0, icuRegexpFunc},

{"lower",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, icuCaseFunc16},
{"lower",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, icuCaseFunc16},
{"upper",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, icuCaseFunc16},
{"upper",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, icuCaseFunc16},

{"lower",  1

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[David Empson]

> On 26/01/2017, at 8:46 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> 
> Ziemowit Laski wrote:
>> Visual C++
> 
> Which one?
> 
>> correctly catches this.
> 
> Oh?  What exactly is illegal about this?
> 
>>  struct IcuScalar {
>>const char *zName;/* Function name */
>>int nArg; /* Number of arguments */
>>int enc;  /* Optimal text encoding */
>>void *pContext;   /* sqlite3_user_data() context 
>> */
>>void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
>>  } scalars[] = {
>>...
>>{"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>>  };

The ANSI/ISO C 1990 standard states this in section 6.5.7, under Constraints:

“All the expressions in an initializer for an object that has static storage 
duration or in an initializer list for an object that has aggregate or union 
type shall be constant expressions.”

In this case the code is trying to initialize a field of an auto struct using 
the db parameter passed to the function. That is not a constant expression, and 
it is in an initializer list for an object that has aggregate type (whether or 
not the object has static storage duration), so is disallowed under ANSI/ISO C 
1990.

Later versions of the C standard removed the bit about aggregate or union 
types, leaving only the static restriction, e.g. from section 6.7.8 of the 
draft C99 standard:

"All the expressions in an initializer for an object that has static storage 
duration shall be constant expressions or string literals.”

Visual C++ is based on C90, and assuming Wikipedia has the details right, it 
wasn’t until Visual C++ 2013 that Microsoft started making changes to support 
C99.

Should SQLite be aiming for the 1990 version of ANSI/ISO C as a baseline, for 
widest compatibility, or is it OK to drop older compilers and require C99 
compliance?

The only obvious reference I found in the SQLite documentation was 
http://www.sqlite.org/howtocompile.html which mentions “ANSI-C”. That is 
generally understood to mean the ANSI C 1989 standard, which was adopted 
internationally as ISO 9899:1990.

-- 
David Empson
demp...@emptech.co.nz

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem with sqlite 4

[David Empson]

> On 3/01/2017, at 4:48 AM, claude.del-vi...@laposte.net wrote:
> 
> Hi, 
> 
> The problem described here occurs both with the x32 and x64 versions of the 
> expert personal 4 (Windows 10). Hereafter, a little database to show the bug. 
> 
> The table "sample" is used to store words occurring in texts. Texts are 
> identified by an id number. 
> 
> CREATE TABLE IF NOT EXISTS sample ( 
> textid INT, 
> word VARCHAR(100), 
> UNIQUE (textid,word) 
> ); 
> 
> CREATE INDEX [word index] ON [sample] ([word]); 
> 
> INSERT INTO sample VALUES 
> (1,"hello"), 
> (1,"world"), 
> (1,"apple"), 
> (1,"fruit"), 
> (2,"fruit"), 
> (2,"banana"), 
> (3,"database") 
> ; 
> 
> Now, one wants to list all the tuples corresponding to the texts containing 
> the word "fruit". In the table above, only the texts 1 and 2 contains the 
> word "fruit". Therefore, the expected result must be : 
> 
> RecNo textid word 
> - -- -- 
> 1 1 apple 
> 2 1 fruit 
> 3 1 hello 
> 4 1 world 
> 5 2 banana 
> 6 2 fruit 
> 
> The following SQL request should achieve the goal : 
> 
> SELECT l2.textid, l2.[word] 
> FROM sample AS l1, sample AS l2 
> WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) 
> ; 
> 
> But il does not since it delivers the wrong answer : 
> 
> RecNo textid word 
> - -- - 
> 1 1 fruit 
> 2 1 fruit 
> 3 1 fruit 
> 4 1 fruit 
> 5 2 fruit 
> 6 2 fruit 
> 
> However, by adjoining in the SELECT part of the above request either a 
> constant string or the command DISTINCT , then the result becomes correct ! 
> 
> SELECT "happy new year", l2.textid, l2.[word] 
> FROM sample AS l1, sample AS l2 
> WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) 
> ; 
> 
> RecNo 'happy new year' textid word 
> -  -- -- 
> 1 happy new year 1 apple 
> 2 happy new year 1 fruit 
> 3 happy new year 1 hello 
> 4 happy new year 1 world 
> 5 happy new year 2 banana 
> 6 happy new year 2 fruit 
> 
> SELECT DISTINCT l2.textid, l2.[word] 
> FROM sample AS l1, sample AS l2 
> WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) 
> ; 
> 
> RecNo textid word 
> - -- -- 
> 1 1 apple 
> 2 1 fruit 
> 3 1 hello 
> 4 1 world 
> 5 2 banana 
> 6 2 fruit 
> 
> Thank you for your reading. Please, notice that this "strange" behavior does 
> not occur with the version 3 of Sqlite expert personal. 
> 
> Claude Del Vigna 


SQLite Expert Personal is a third party product which uses the SQLite database 
engine. It is not using “SQLite 4” (which is in early development stages and 
not been released), but will be using some version of SQLite 3.

This mailing list is not an appropriate place to get support for products which 
use SQLite, but this looks like odd behaviour with SQLite itself, which may be 
worth investigating further.

The current version of SQLite Expert Personal is 4.2.0, available here:

http://www.sqliteexpert.com/download.html

They don’t appear to give any clues as to which version of SQLite the 
application is using.

I downloaded the 32-bit version, ran it under Windows 7 and tried the SQL you 
specified, and it produced the same result. 

CREATE TABLE IF NOT EXISTS sample ( 
textid INT, 
word VARCHAR(100), 
UNIQUE (textid,word) 
); 

CREATE INDEX [word index] ON [sample] ([word]); 

INSERT INTO sample VALUES 
(1,"hello"), 
(1,"world"), 
(1,"apple"), 
(1,"fruit"), 
(2,"fruit"), 
(2,"banana"), 
(3,"database") 
; 

SELECT l2.textid, l2.[word] 
FROM sample AS l1, sample AS l2 
WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) 
; 

textid  word
1   fruit
1   fruit
1   fruit
1   fruit
2   fruit
2   fruit

Executing this command in SQLite Expert Personal 4.2.0:

SELECT sqlite_version();

reports it is using version 3.15.2 of the SQLite database engine (as a DLL 
installed alongside the application), which was the latest version until 
version 3.16.0 was released today.

Repeating the same test using the SQLite command line tool (version 3.15.2) 
does NOT produce the same behaviour. Here is what I get for the final select 
statement:

SELECT l2.textid, l2.[word] 
FROM sample AS l1, sample AS l2 
WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) 
; 

1|apple
1|fruit
1|hello
1|world
2|banana
2|fruit

Therefore the problem is somehow specific to SQLite Expert Personal 4.2.0 (or 
the 32-bit DLL of SQLite 3.15.2), or maybe something in the way it has 
configured the SQLite database engine.

Going back to SQLite Expert Personal, I checked the output of EXPLAIN QUERY 
PLAN and EXPLAIN and they appear to be identical to the command line tool.

First, SQLite Expert:

EXPLAIN QUERY PLAN
SELECT l2.textid, l2.[word] 
FROM sample AS l1, sample AS l2 
WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid]) 
; 

selectidorder   fromdetail
0   0   0   SEARCH TABLE sample AS l1 USING INDEX word index 
(word=?)
0   1   1   SEARCH TABLE sample AS l2 USING COVERING INDEX 
sqlite_autoindex_sample_1 (textid=?)

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

[David Empson]

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

SQLite version 3.15.0 2016-10-12 15:15:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read test.sql
select sqlite_source_id();
2016-10-12 15:15:30 61f0526978af667781c57bcc87510e4524efd0d8
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
6
5
7

> On 13/10/2016, at 5:11 AM, Richard Hipp  wrote:
> 
> On 10/11/16, Keith Medcalf  wrote:
>> 
>> #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
>> 
>> makes it work properly.  neither NDEBUG nor SQLITE_DEBUG explicitly defined.
>> 
>> Over to Richard ...
> 
> Should now be fixed on trunk and in the latest Prerelease Snapshot at
> https://sqlite.org/download.html
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[David Empson]

Following up: same for the Mac distribution of 3.14.2 command line tool. Using 
the pre-release snapshot of 3.15.0 from the main download page to build the 
sqlite3 3.15.0 on the Mac, I get the same answer: 1,2,3.

SQLite version 3.15.0 2016-10-10 14:34:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read test.sql
select sqlite_source_id();
2016-10-10 14:34:00 aebe429e52ffef026cb0803fb164339d61bd2e88
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
1
2
3


> On 12/10/2016, at 1:23 PM, David Empson <demp...@emptech.co.nz> wrote:
> 
> Keith - using your example, I get the same result as Tobias: the second 
> select produces 1,2,3. This is with the sqlite3.exe Windows command line tool 
> for SQLite 3.14.2 downloaded from sqlite.org. Same sqlite_source_id() too. 
> I’m not set up to build SQLite from source, so can’t easily test 3.15.0, but 
> If I do the same with a copy of 3.8.11.1 I have handy I get the correct 
> result: 6, 5, 7.
> 
> SQLite version 3.14.2 2016-09-12 18:50:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .read test.sql
> select sqlite_source_id();
> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> create table i (id integer primary key autoincrement, flags integer);
> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> create table m (id integer);
> insert into m values (1),(2),(3),(4),(5),(6),(7);
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
> 1
> 2
> 3
> 
>> On 12/10/2016, at 12:59 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>> 
>> SQLite version 3.14.2 2016-09-12 18:50:49
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .read \\test.sql
>> select sqlite_source_id();
>> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
>> create table i (id integer primary key autoincrement, flags integer);
>> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
>> create table m (id integer);
>> insert into m values (1),(2),(3),(4),(5),(6),(7);
>> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
>> 6
>> 5
>> 7
>> 1
>> 2
>> 3
>> 4
>> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
>> 6
>> 5
>> 7
>> 
>> I just compiled 3.14.2 from my source repository and it works correctly.  
>> 
>> Can you check the result of "select sqlite_source_id();", which should be:
>> 
>> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
>> 
>> https://www.sqlite.org/src/info/29dbef4b8585f753
>> 
>>> -Original Message-
>>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>>> On Behalf Of Tobias Ellinghaus
>>> Sent: Tuesday, 11 October, 2016 11:41
>>> To: sqlite-users@mailinglists.sqlite.org
>>> Subject: Re: [sqlite] LIMIT doesn't return expected rows
>>> 
>>> Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
>>>> This was fixed September 7.  The fix appears in 3.14.2 and also on the
>>>> current 3.15.0.
>>> 
>>> Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the
>>> last query? I am asking as that's the version I am using (installed from
>>> Debian/sid) and I get "1, 2, 3" here.
>>> 
>>>> https://www.sqlite.org/releaselog/3_14_2.html
>>>> 
>>>> The ORDER BY LIMIT optimization is not valid unless the inner-most IN
>>>> operator loop is actually used by the query plan. Ticket
>>>> https://sqlite.org/src/info/0c4df46116e90f92
>>>> 
>>>> 
>>>> SQLite version 3.15.0 2016-10-10 14:48:36
>>>> Enter ".help" for usage hints.
>>>> Connected to a transient in-memory database.
>>>> Use ".open FILENAME" 

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

[David Empson]

Keith - using your example, I get the same result as Tobias: the second select 
produces 1,2,3. This is with the sqlite3.exe Windows command line tool for 
SQLite 3.14.2 downloaded from sqlite.org. Same sqlite_source_id() too. I’m not 
set up to build SQLite from source, so can’t easily test 3.15.0, but If I do 
the same with a copy of 3.8.11.1 I have handy I get the correct result: 6, 5, 7.

SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read test.sql
select sqlite_source_id();
2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
1
2
3

> On 12/10/2016, at 12:59 PM, Keith Medcalf  wrote:
> 
> SQLite version 3.14.2 2016-09-12 18:50:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .read \\test.sql
> select sqlite_source_id();
> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> create table i (id integer primary key autoincrement, flags integer);
> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> create table m (id integer);
> insert into m values (1),(2),(3),(4),(5),(6),(7);
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
> 6
> 5
> 7
> 
> I just compiled 3.14.2 from my source repository and it works correctly.  
> 
> Can you check the result of "select sqlite_source_id();", which should be:
> 
> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> 
> https://www.sqlite.org/src/info/29dbef4b8585f753
> 
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Tobias Ellinghaus
>> Sent: Tuesday, 11 October, 2016 11:41
>> To: sqlite-users@mailinglists.sqlite.org
>> Subject: Re: [sqlite] LIMIT doesn't return expected rows
>> 
>> Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
>>> This was fixed September 7.  The fix appears in 3.14.2 and also on the
>>> current 3.15.0.
>> 
>> Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the
>> last query? I am asking as that's the version I am using (installed from
>> Debian/sid) and I get "1, 2, 3" here.
>> 
>>> https://www.sqlite.org/releaselog/3_14_2.html
>>> 
>>> The ORDER BY LIMIT optimization is not valid unless the inner-most IN
>>> operator loop is actually used by the query plan. Ticket
>>> https://sqlite.org/src/info/0c4df46116e90f92
>>> 
>>> 
>>> SQLite version 3.15.0 2016-10-10 14:48:36
>>> Enter ".help" for usage hints.
>>> Connected to a transient in-memory database.
>>> Use ".open FILENAME" to reopen on a persistent database.
>>> sqlite> create table i (id integer primary key, flags integer);
>>> sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
>>> sqlite> create table m (id integer);
>>> sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
>>> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
>> DESC;
>>> 6
>>> 5
>>> 7
>>> 1
>>> 2
>>> 3
>>> 4
>>> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
>> DESC
>>> limit 3; 6
>>> 5
>>> 7
>> 
>> Tobias
>> 
>> [...]
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] json1 as loadable extension

[David Empson]

> On 28/09/2016, at 4:22 AM, Mark Woodward  wrote:
> 
> I've built the JSON1 module as a loadable extension, and this loads fine if I 
> also compile sqlite myself. This is on windows as x86 (32 bit).
> 
> I cannot get any pre built version of sqlite3.dll to load the extension, I 
> get "'error during initialization: '" with no subsequent message. (Tried the 
> sqlite.org binary and Microsoft package).
> 
> The funny thing is that I can compile other extensions in exactly the same 
> way and they load fine, (tried csv).

Might this be due to the JSON1 extension being included and enabled in the DLL? 
I don’t use the DLL, but it looks like JSON1 is in there, based on the presence 
of many instances of “json” or “JSON” in a dump.

If so, your attempt to load the extension is failing because another extension 
of the same name has already been loaded.

The amalgamation (sqlite3.c) includes the JSON1 extension (among others) and 
enables it if built with -DSQLITE_ENABLE_JSON1.

I haven’t spotted documentation on which options are used to build the standard 
DLL.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[David Empson]

> On 8/09/2016, at 3:36 PM, David Empson <demp...@emptech.co.nz> wrote:
> 
> 
>> On 8/09/2016, at 3:23 PM, Rowan Worth <row...@dug.com> wrote:
>> 
>> We recently made sqlite's logging more visible in our application and we're
>> seeing a lot more "schema has changed" warnings than I'd expect, since our
>> app pretty much doesn't touch the schema except during DB creation. I know
>> the warnings are harmless, just curious where they are coming from :)
> 
> I found the same thing, with an obvious cause: every ATTACH DATABASE or 
> DETACH DATABASE triggers a schema change which requires recompiling every 
> prepared statement. SQLite does this automatically, 

[Oops, bumped the send button mid-sentence.]

SQLite does this automatically, but it outputs a message to the error log 
(which is not returned via the API as an error).

I ended up suppressing that specific error in my log handler as our application 
is regularly doing a lot of attach/detach operations and has a lot of prepared 
statements.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[David Empson]

> On 8/09/2016, at 3:23 PM, Rowan Worth <row...@dug.com> wrote:
> 
> We recently made sqlite's logging more visible in our application and we're
> seeing a lot more "schema has changed" warnings than I'd expect, since our
> app pretty much doesn't touch the schema except during DB creation. I know
> the warnings are harmless, just curious where they are coming from :)

I found the same thing, with an obvious cause: every ATTACH DATABASE or DETACH 
DATABASE triggers a schema change which requires recompiling every prepared 
statement. SQLite does this automatically, 
-- 
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

Re: [sqlite] 64-bit SQLite3.exe

[David Empson]

> On 10/08/2016, at 5:50 PM, Rousselot, Richard A 
>  wrote:
> 
> I guess it is a matter of support.  Can the people using unpatched, 
> unsupported 32-bit windows instances just live with SQLite 3.13 (or whatever 
> the cutover version)?  Are these 32-bit windows users really actively 
> updating SQLite?

You are missing an important point: it isn’t only the processor architecture, 
but the Windows installation architecture which matters.

Windows Vista, 7, 8, 8.1 and 10 are/were all available in 32-bit. All of them 
are still supported by Microsoft.

There are a lot of PCs with 64-bit processors running 32-bit Windows, because 
that is how they were supplied or originally set up. I’m not talking ten year 
old computers: my work PC is a 2011 HP with a 64-bit Core i5 that was supplied 
with 32-bit Windows 7 (we’ve upgraded these PCs to Windows 10, but it is still 
32-bit Windows 10). There will be many newer 64-bit PCs also running 32-bit 
Windows.

Numbers will dwindle over time as PCs are replaced (or the occasional OS 
reinstall), but there is probably still a significant number of PCs running a 
supported 32-bit Windows.

If the only distributed build of sqlite3.exe was 64-bit, I expect it would 
inconvenience a fair number of people on 32-bit Windows who use an up-to-date 
version of SQLite including the command line tools, but can't build it 
themselves. (I can build it, so wouldn’t mind if this happened.)

The 32-bit build runs on 64-bit Windows, and is only a limit for those who need 
to do things with the command line tool that require more than 2 GB of memory.

Having both 32-bit and 64-bit versions would be ideal, probably with a plan to 
phase out the 32-bit version, but it would mean more work for the SQLite 
developers in the meantime.

> Can the command line tool interact with a driver?  How does a 32-bit windows 
> user get SQLite3.exe to run on a legacy 16-bit (windows 3.1?) machine?

Anything that old is not supported by SQLite 3.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[David Empson]

> On 10/08/2016, at 3:30 PM, Rousselot, Richard A 
>  wrote:
> 
> As I said, I am not a software engineer.  I could spend a few hours figuring 
> this out and be fine but it will be painful for me.
> 
> I see no downsides in a 64-bit CLI.  The last 32-bit Intel CPU was the PIII 
> in 2004, no supported Windows OS requires 32-bit CPUs, the file size may be 
> marginally bigger but who cares on a PC.  The 64-bit version will, I assume, 
> happily work on DBs created in the 32-bit version.  And for those that need 
> 32-bit for their applications and drivers still have access to the 32-bit 
> DLL.  What am I missing?  Are windows command line tools 32-bit only?

A 32-bit installation of Windows cannot run 64-bit executables (ignoring VM 
solutions).

Because of the large installed base of 32-bit Windows, the Windows command line 
tools for SQLite needs to be available as 32-bit versions. If 64-bit versions 
were provided, they would need to be in addition to the 32-bit versions.

There are an awful lot of 32-bit installations of Windows. This includes a lot 
of 32-bit installations of Windows on 64-bit processors, which exist for many 
reasons including defaults offered by the manufacturer, lack of 64-bit drivers, 
corporate policy decisions, reduced memory footprint in limited machines, or 
the user requiring 32-bit Windows in order to be able to run legacy 16-bit 
software (again, ignoring VM solutions).

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Downloading SQLite issue

[David Empson]

I think we need more details to understand what is going wrong for you.

Exactly what did you download and where from?

This is the main download page for SQLite:

https://www.sqlite.org/download.html

On that page is a section titled "Precompiled Binaries for Mac OS X (x86)”. 
Under that is a single link to the current version of the bundle of precompiled 
Mac tools: https://www.sqlite.org/2016/sqlite-tools-osx-x86-313.zip

If that isn’t what you are trying to run, you’ll need to explain what you 
downloaded.

Assuming it was that file:

After expanding the zip file you should have a folder containing three files: 
sqldiff, sqlite3, and sqlite3_analyzer.

These are command line tools which would normally be run from a shell inside 
Terminal.app. If you try to run one from Finder (by double clicking the icon) 
on a recent version of OS X, you should get a warning from Gatekeeper that they 
can’t be opened because they are from an unidentified developer (not a problem 
if they are launched from the shell). If you get past the Gatekeeper warning 
using one of the standard methods, Terminal should be launched, open a new 
window, and run the program. (That’s what happens for me.)

The warning about a damaged file is an odd one to get for command line tools, 
because it usually appears when an application’s digital signature doesn’t 
match the application bundle (indicating the application was modified). The 
SQLite command line tools are not digitally signed, which suggests Gatekeeper 
is getting confused somehow.

I’ve seen mention of cases where this message can appear for documents as well 
as applications, e.g. the following case where someone was using a third party 
file system driver via OSX Fuse - all downloaded files (with the 
com.apple.quarantine extended attribute) produced that message if 
double-clicked when the file was on the unusual file system, but were OK if 
subsequently moved to a normal HFS+ volume.

http://apple.stackexchange.com/questions/129966/files-wrongly-considered-as-damaged-in-encfs-volume

> On 9/07/2016, at 1:19 AM, Alex Fender  wrote:
> 
> I tried with Chrome and Firefox. I am using the default unzip tool in Mac.
> I double click the zip folder and then it unpacks a new folder to my
> desktop. When I open that folder, I then see the Sqlite programs. I double
> click to open and it says damaged.
> 
> On Fri, Jul 8, 2016 at 7:52 AM, Simon Slavin  wrote:
> 
>> 
>> On 8 Jul 2016, at 1:20pm, Alex Fender  wrote:
>> 
>>> After I download and unzip. I click and open Sqlite3 by double clicking.
>>> When I open the file, it says damaged and move to the trash.
>> 
>> Works fine on my Mac.
>> 
>> Which web browser are you using to download ?
>> Which program is used when you unzip the file ?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max_page_count wont round-trip between shell and program

[David Empson]

> On 6/07/2016, at 8:55 AM, Ward WIllats  wrote:
> 
>> I have noticed that when I set max_page_count programatically to 16384 and 
>> read it back with the shell I get 1073741823.
>> If I set max_page_count with the shell to 16384 and read it back 
>> programmatically, the program gets back 1073741823.
>> Both the program and the shell can round-trip their own set/get cycle OK.
>> 
> 
> Oh wait, you're going to tell me the value is transient to the connection, 
> aren't you? And 1073741823 is some kind of max sentinel?

Looks like it. Testing with an empty database created from scratch:

$ sqlite3 test.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> pragma max_page_count;
1073741823
sqlite> pragma max_page_count=16384;
16384
sqlite> .quit

$ sqlite3 test.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> pragma max_page_count;
1073741823
sqlite> pragma max_page_count=16384;
16384
sqlite> vacuum;
sqlite> pragma max_page_count;
16384
sqlite> .quit

$ sqlite3 test.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> pragma max_page_count;
1073741823

Same results with SQLite 3.13.0.

Vacuum didn't help.

Incidentally, 1073741823 is 2^30-1 (0x3FFF). This isn’t an endian issue 
either, as those bytes look nothing like 16384=0x4000.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possibly missing feature in json1 extension

[David Empson]

Thanks Richard.

Tested and it works for me. I tried it with text, integer and null types as 
input.

It also removes one step in storing JSON-encoded integer values, since I no 
longer need to convert them to text.

> On 18/06/2016, at 1:04 AM, Richard Hipp <d...@sqlite.org> wrote:
> 
> New json_quote() function added on a branch
> (https://www.sqlite.org/src/info/2c3714aebf5e40e3).  If there is no
> pushback, and if this fixes David's problem, then this will get merged
> to trunk and appear in the next release.
> 
> On 6/16/16, David Empson <demp...@emptech.co.nz> wrote:
>> I'm working with SQLite 3.13.0, and am the process of adding a new table to
>> a database:
>> 
>> CREATE TABLE settings(key TEXT PRIMARY KEY NOT NULL, value TEXT)
>> 
>> This table will hold arbitrarily named application defined settings. For the
>> value column I’d like to use JSON for every row, as some of the settings
>> will be structured (either as arrays or objects, possibly with nested
>> substructures). The json1 extension seems to cover most of what I need
>> (without having a separate JSON library outside of SQLite), but I've run
>> into a problem which might point to a missing function.
>> 
>> I can easily deal with arrays and objects using functions like json_array(),
>> but I'm having trouble with simple values, particularly strings: there
>> appears to be no function to turn an SQLite text value into a simple JSON
>> text value, without putting it in an array or object.
>> 
>> e.g. this is fine:
>> 
>> sqlite> SELECT json_array('one',2,'we"ird');
>> ["one",2,"we\"ird"]
>> 
>> but I can't find a way to convert just the SQLite text 'one' into the JSON
>> text "one" (or 'we"ird' into "we\"ird") without the array (or object)
>> wrapper.
>> 
>> A function like json_value(value) would solve the problem. It would be a
>> single argument function based on the implementation of json_array(), which
>> doesn't output the square brackets. It should also support NULL and numeric
>> arguments, like json_array().
>> 
>> Converting a simple JSON value back to native SQLite types is easy:
>> json_extract(json,'$'), or I can use json_each() or json_tree() to parse the
>> JSON values without knowing their structure.
>> 
>> Perhaps json_set() should be able to handle this? I tried something like
>> this:
>> 
>> sqlite> SELECT json_set('null', '$', 'test');
>> test
>> 
>> It copies the string in the third parameter, but doesn't output valid JSON
>> because the quotes haven't been added. Is this a bug?
>> 
>> 
>> As an interim solution, I can modify a local copy of the json1 extension to
>> add my proposed function, but it would be nice if this was standard in a
>> later version.
>> 
>> Here is a draft implementation:
>> 
>> static void jsonValueFunc(
>>  sqlite3_context *ctx,
>>  int argc,
>>  sqlite3_value **argv
>> ){
>>  JsonString jx;
>> 
>>  jsonInit(, ctx);
>>  jsonAppendValue(, argv[0]);
>>  jsonResult();
>>  sqlite3_result_subtype(ctx, JSON_SUBTYPE);
>> }
>> with this definition in the aFunc[] array:
>> 
>>{ "json_value",  -1, 0,   jsonValueFunc },
>> 
>> 
>> A workaround I've found is to use  json_array('text') wrapped in SQLite
>> functions to strip the square brackets off the array, but that seems ugly.
>> 
>> An alternative solution would be that I only use JSON for the complex
>> settings and leave the simple ones stored using SQLite native types, but
>> that either means adding a column to track which ones are JSON, or having
>> inherent knowledge for each setting, which could lead to compatibility
>> problems and potential ambiguity if a future update changes to using JSON
>> for an existing setting, and an existing value happens to be valid JSON,
>> such as the word 'true'.
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possibly missing feature in json1 extension

[David Empson]

I'm working with SQLite 3.13.0, and am the process of adding a new table to a 
database:

CREATE TABLE settings(key TEXT PRIMARY KEY NOT NULL, value TEXT)

This table will hold arbitrarily named application defined settings. For the 
value column I’d like to use JSON for every row, as some of the settings will 
be structured (either as arrays or objects, possibly with nested 
substructures). The json1 extension seems to cover most of what I need (without 
having a separate JSON library outside of SQLite), but I've run into a problem 
which might point to a missing function.

I can easily deal with arrays and objects using functions like json_array(), 
but I'm having trouble with simple values, particularly strings: there appears 
to be no function to turn an SQLite text value into a simple JSON text value, 
without putting it in an array or object.

e.g. this is fine:

sqlite> SELECT json_array('one',2,'we"ird');
["one",2,"we\"ird"]

but I can't find a way to convert just the SQLite text 'one' into the JSON text 
"one" (or 'we"ird' into "we\"ird") without the array (or object) wrapper.

A function like json_value(value) would solve the problem. It would be a single 
argument function based on the implementation of json_array(), which doesn't 
output the square brackets. It should also support NULL and numeric arguments, 
like json_array().

Converting a simple JSON value back to native SQLite types is easy: 
json_extract(json,'$'), or I can use json_each() or json_tree() to parse the 
JSON values without knowing their structure.

Perhaps json_set() should be able to handle this? I tried something like this:

sqlite> SELECT json_set('null', '$', 'test');
test

It copies the string in the third parameter, but doesn't output valid JSON 
because the quotes haven't been added. Is this a bug?


As an interim solution, I can modify a local copy of the json1 extension to add 
my proposed function, but it would be nice if this was standard in a later 
version.

Here is a draft implementation:

static void jsonValueFunc(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
  JsonString jx;

  jsonInit(, ctx);
  jsonAppendValue(, argv[0]);
  jsonResult();
  sqlite3_result_subtype(ctx, JSON_SUBTYPE);
}
with this definition in the aFunc[] array:

{ "json_value",  -1, 0,   jsonValueFunc },


A workaround I've found is to use  json_array('text') wrapped in SQLite 
functions to strip the square brackets off the array, but that seems ugly.

An alternative solution would be that I only use JSON for the complex settings 
and leave the simple ones stored using SQLite native types, but that either 
means adding a column to track which ones are JSON, or having inherent 
knowledge for each setting, which could lead to compatibility problems and 
potential ambiguity if a future update changes to using JSON for an existing 
setting, and an existing value happens to be valid JSON, such as the word 
'true'.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write-Ahead Logging -- documentation error

[David Empson]

After some head scratching, I’ll retract my previous message. The documentation 
(as revised last week) is correct.

The previous version of wal.html mentioning “read mark” had led me down the 
wrong path in understanding which bit of the WAL was protected while a read 
lock was active.

There is only one mark per read lock. The source code calls it aReadMark, but 
the documentation calls it “end mark”.

Checkpoint can proceed up to the earliest end mark, because at that point the 
main database has the version of each page as seen by the reader with the 
earliest end mark (and that reader won’t need to reference the WAL any more). 
Other readers with later end marks can still use later parts of the WAL to 
access later versions of pages.

Once the read lock with the earliest end mark is removed, checkpoint can 
proceed up to the next earliest end mark. If it catches up with the end of the 
WAL (no read locks, or no data written during the last read locks) then the WAL 
can reset to writing new data near the beginning.

> On 27/05/2016, at 7:06 PM, David Empson <demp...@emptech.co.nz> wrote:
> 
>> On 26/05/2016, at 3:17 AM, Jeffrey Mattox <j...@mac.com> wrote:
>> 
>> This page:
>> https://www.sqlite.org/wal.html
>> Contains the phrase, "... the checkpoint must stop when it reaches a page in 
>> the WAL that is past the read mark of any current reader."
>> 
>> The term, "read mark" is not defined on that page.  Should that be "end 
>> mark”?
> 
> It appears the page has already been edited to that effect, but it doesn’t 
> make sense now.
> 
> "A checkpoint can run concurrently with readers, however the checkpoint must 
> stop when it reaches a page in the WAL that is past the end mark of any 
> current reader. The checkpoint has to stop at that point because otherwise it 
> might overwrite part of the database file that the reader is actively using.”
> 
> Readers are actively using pages BEFORE the end mark.
> 
> The previous text was correct, but the term “read mark” needs to be 
> documented.
> 
> Looking at the source code, there are two points involved in each read lock 
> for WAL mode: aReadMark[K] specifies the start of the read lock (which I 
> assume can increase as the read progresses, but I haven’t looked deeper to 
> confirm that), and mxFrame for the reader specifies the upper bound (which 
> will be the "end mark” in the documentation).
> 
> Checkpointing can operate on frame numbers that are less than or equal to the 
> minimum used aReadMark[].

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write-Ahead Logging -- documentation error

[David Empson]

> On 26/05/2016, at 3:17 AM, Jeffrey Mattox  wrote:
> 
> This page:
> https://www.sqlite.org/wal.html
> Contains the phrase, "... the checkpoint must stop when it reaches a page in 
> the WAL that is past the read mark of any current reader."
> 
> The term, "read mark" is not defined on that page.  Should that be "end mark”?

It appears the page has already been edited to that effect, but it doesn’t make 
sense now.

"A checkpoint can run concurrently with readers, however the checkpoint must 
stop when it reaches a page in the WAL that is past the end mark of any current 
reader. The checkpoint has to stop at that point because otherwise it might 
overwrite part of the database file that the reader is actively using.”

Readers are actively using pages BEFORE the end mark.

The previous text was correct, but the term “read mark” needs to be documented.

Looking at the source code, there are two points involved in each read lock for 
WAL mode: aReadMark[K] specifies the start of the read lock (which I assume can 
increase as the read progresses, but I haven’t looked deeper to confirm that), 
and mxFrame for the reader specifies the upper bound (which will be the "end 
mark” in the documentation).

Checkpointing can operate on frame numbers that are less than or equal to the 
minimum used aReadMark[].

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request - RowCount

[David Empson]

> On 14/12/2014, at 4:17 am, Simon Slavin  wrote:
> 
> 
> On 13 Dec 2014, at 12:38pm, Richard Hipp  wrote:
> 
>> Also, if there are indices available, SQLite attempts to count the smallest
>> index (it has to guess at which is the smallest by looking at the number
>> and declared datatypes of the columns) and counting the smallest index
>> instead, under the theory that a smaller index will involve less I/O.
> 
> Would it not be faster to just count the number of pages each index takes up 
> ?  Uh ... no.
> Wow.  You really don't like storing counts or sizes, do you ?

That wouldn't work, because leaf nodes in a B-tree contain a variable number of 
used entries. It is necessary to visit each leaf node to find out how many 
entries that page contains.

The reason for using the "smallest" index is that each leaf node will hopefully 
cover more records than the leaf nodes from the main table, therefore less I/O 
is required.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 joins on one table

[David Empson]

On 30/09/2014, at 12:04 pm, Paul Sanderson  wrote:

> I two tables of the form
> 
> create table1 (person1 text, person2 text)
> create table2 (person text, picture blob)
> 
> Is it possible to create a join so I can get a resultant dataset of the form
> 
> person1, person1picture, person2, person2picture

SELECT person1, p1.picture, person2, p2.picture FROM table1 JOIN table2 AS p1 
ON table1.person1 = p1.person JOIN table2 AS p2 ON table1.person2 = p2.person;

The trick is to use table aliases (AS) to allow joining twice to the same table 
with different criteria, and then picking columns from the appropriate instance.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems uploading CSV into sqlite3 DB

[David Empson]

I get similar results on OS X 10.9.4 if I try to use the version of SQLite 
supplied with the operating system (3.7.13) to import a CSV.

The simple ".mode csv" and ".import  tab1" method works if I run the 
copy of SQLite 3.8.5 I built myself from the source code, or if I use the Mac 
OS X precompiled SQLite 3.8.5 shell from the web site. (I prefer to build my 
own copy because the one on the web site doesn't include the readline library, 
so if used interactively it can't recall previous command lines with up-arrow.)

The problem is likely to be that the method you are using to launch sqlite3, 
which is using the system's default search path to locate sqlite3, and that 
will be the Apple-supplied version (3.7.13) which is located at 
/usr/bin/sqlite3. It appears that version doesn't import CSVs properly.

The easiest way to avoid the problem is to specify the path to your downloaded 
copy of sqlite3.

Assuming you have put the downloaded sqlite3 in your Applications folder, then 
to run it from Terminal you can use this command:

$ /Applications/sqlite3 newDB.db < my_script_file

On 4/09/2014, at 3:24 am, Carlos A. Gorricho (HGSAS) 
 wrote:

> ​When I start sqlite double clicking the icon in the Mac Applications
> folder:
> 
> Last login: Wed Sep  3 10:13:32 on ttys000
> 
> HEPTAGON-GROUP-SAS:~ carlosgorricho$ /Applications/sqlite3 ; exit;
> 
> SQLite version 3.8.5 2014-06-04 14:06:34
> 
> Enter ".help" for usage hints.
> 
> Connected to a *transient in-memory database*.
> 
> Use ".open FILENAME" to reopen on a persistent database.
> 
> sqlite>
> 
> ​
> When starting sqlite on a Terminal Window:
> 
> Last login: Wed Sep  3 10:11:42 on ttys002
> 
> HEPTAGON-GROUP-SAS:~ carlosgorricho$ sqlite3
> 
> SQLite version 3.7.13 2012-07-17 17:46:21
> 
> Enter ".help" for instructions
> 
> Enter SQL statements terminated with a ";"
> 
> sqlite>
> 
> 
> I downloaded the only precompile binary currently available for Mac in
> www.sqlite.org website.
> 
> To perform the job described earlier, I run the Terminal version in batch
> mode. That is, I created a script file with all the .commands to upload de
> CSV file, and ran it on a new database:
> 
> $ sqlite3 newDB.db < my_script_file
> 
> 
> When I got the tab1 error message, I reverted to performing the job from
> inside the database, creating first the table and then uploading the data.
> The result was the huge single-record-field DB I shared earlier.
> 
> I would venture to say I did this from the Terminal sqlite version, but I
> am not certain...
> 
> Thanks in advance for your comments and guidance. Please let me know if I
> can do anything to help you in other fronts.
> 
> 
> 
> 
> Saludos/Cheers,

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing RFC4180-compliant CSV output

[David Empson]

On 24/07/2014, at 10:15 pm, Peter Waller  wrote:

> I too am sad that CRLF is mandated in the specification and still in wide use 
> because of Windows.

It may be nothing to do with Windows. CRLF is the Internet standard for the end 
of a line, as mentioned in RFC2234 and mandated by most if not all Internet 
standards, including RFC822 (e-mail and similarly structured text, where CRLF 
is required at the end of each header line), HTTP and other text-based 
protocols.

RFC4180 specifying CRLF as the end of line in CSV files is consistent with 
other RFCs and with Internet standards.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A Potential Bug

[David Empson]

In this case, sqlite3VdbeAllocUnpackedRecord is called with pSpace = 0 and 
szSpace = 0.

The calculated value of nOff will also be 0, since pSpace is 0. nByte must be 
greater than zero, as it is the sum of two positive terms.

Therefore the test "if( nByte>szSpace+nOff )" will be true, and the code path 
taken will allocate memory.

Not a bug.

On 16/07/2014, at 1:31 pm, Dongpeng Xu  wrote:

> Hi, all,
> 
> I am using our automatic bug finding tool to scan the source code of
> sqlite. The tool is designed to find potential null dereference bug. It
> issues warning for the function sqlite3VdbeAllocUnpackedRecord.
> 
> SQLITE_PRIVATE UnpackedRecord *sqlite3VdbeAllocUnpackedRecord(
>  KeyInfo *pKeyInfo,  /* Description of the record */
>  char *pSpace,   /* Unaligned space available */
>  int szSpace,/* Size of pSpace[] in bytes */
>  char **ppFree   /* OUT: Caller should free this pointer */
> ){
>  UnpackedRecord *p;  /* Unpacked record to return */
>  int nOff;   /* Increment pSpace by nOff to align it */
>  int nByte;  /* Number of bytes required for *p */
> 
>  /* We want to shift the pointer pSpace up such that it is 8-byte aligned.
>  ** Thus, we need to calculate a value, nOff, between 0 and 7, to shift
>  ** it by.  If pSpace is already 8-byte aligned, nOff should be zero.
>  */
>  nOff = (8 - (SQLITE_PTR_TO_INT(pSpace) & 7)) & 7;
>  nByte = ROUND8(sizeof(UnpackedRecord)) + sizeof(Mem)*(pKeyInfo->nField+1);
>  if( nByte>szSpace+nOff ){
>p = (UnpackedRecord *)sqlite3DbMallocRaw(pKeyInfo->db, nByte);
>*ppFree = (char *)p;
>if( !p ) return 0;
>  }else{
>p = (UnpackedRecord*)[nOff];
>*ppFree = 0;
>  }
> 
>  p->aMem = (Mem*)&((char*)p)[ROUND8(sizeof(UnpackedRecord))];
>  assert( pKeyInfo->aSortOrder!=0 );
>  p->pKeyInfo = pKeyInfo;
>  p->nField = pKeyInfo->nField + 1;
>  return p;
> }
> 
> The suspicious context is in the function sqlite3VdbeSorterInit, it calls
> sqlite3VdbeAllocUnpackedRecord as below:
> pSorter->pUnpacked = sqlite3VdbeAllocUnpackedRecord(pCsr->pKeyInfo, 0, 0,
> );
> 
> The second and third parameters are set to zero. However, in
> sqlite3VdbeAllocUnpackedRecord, p is set to pSpace + nOff if nByte <=
> szSpace + nOff and will be dereferenced later.
> 
> I am wondering whether this is a real bug. Is there a concrete execution
> path that reach the dereference point? Any comments are welcome. Thanks!
> 
> Sincerely,
> Dongpeng

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding WHERE to query --> database disk image is malformed

[David Empson]

On 25/06/2014, at 8:29 am, Jerry Krinock <je...@ieee.org> wrote:
> On 2014 Jun 24, at 00:06, Clemens Ladisch <clem...@ladisch.de> wrote:
>> Is there any other 0x0A byte in the good file?
> 
> No, only that one.

That leads to a plausible theory: had you done anything with the "good" 
database along the lines of storing it in a version control system, or 
including it with source files in some other kind of bulk processing, or 
uploaded/downloaded it via FTP?

The most likely explanation is that it got processed by something which thought 
it should be treated as ASCII text and was doing a spurious LF-to-CR 
translation. If there was only one 0x0A byte in the "good" file, then that is 
the only one which would have been modified.

> According to the git history for my project, I committed the corrupt file 
> about 10 days ago.  Still trying to figure out how that happened.  Thank you 
> guys for all of the clues.

-- 
David Empson
demp...@emptech.co.nz
Snail mail: P.O. Box 27-103, Wellington 6141, New Zealand

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance regression with multiple lower bound tests

[David Empson]

On 16/06/2014, at 11:36 pm, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Jun 16, 2014 at 5:07 AM, David Empson <demp...@emptech.co.nz> wrote:
> 
>> It appears SQLite 3.8.1 removed an optimisation where earlier versions of
>> the query planner were checking for two or more "lower bound" comparisons
>> against the key for an index, and combining them so the greater of the two
>> values was used as a lower bound.
>> 
>> 
> There never has been any such optimization in SQLite.  If it picked the
> better lower bound in 3.8.0, then that was purely by luck.

OK thanks, that makes sense.

> I suggest you rewrite your query.  Instead of
> 
> ... WHERE x BETWEEN ?1 AND ?2 AND x>?3
> 
> Consider using
> 
> ... WHERE x BETWEEN max(?1,?3) AND ?2 AND x>?3

I assume that was supposed to be WHERE x BETWEEN max(?1,?3) AND ?2.

I agree, that seems a reasonable solution. Something like that was on 
tomorrow's todo list.

> Also, when running EXPLAIN, please first give the command-line shell the
> ".explain" command in order to set output formatting up to show the program
> listing in a more readable format.

Noted, thanks for the tip.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help forming query

[David Empson]

On 18/06/2014, at 5:54 pm, David M. Cotter  wrote:

> i have a table with a numeric column (not the key column)
> i want to obtain from this table a list of unique numbers appearing in that 
> one column
> 
> some cells in the column may have nothing, some may have duplicate numbers eg:
> 
>> 1
>> 1
>> 1
>> 4
>> _
>> _
>> 4
>> _
> 
> note that "_" means "no data". i want to get a list with [1, 4] as the 
> result.  what is the proper SQLite query for this?

SELECT DISTINCT column FROM table;

This will return a row for each unique value in table.column, with the values 
in no particular order.

Eliminating the "no data" entry can be done by checking the results, or if you 
want to eliminate it automatically you could use something like:

SELECT DISTINCT column FROM table WHERE column not NULL;

This assumes your "no data" is represented as NULL. If you have used something 
else to represent "no data" then you would need to compare against that.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance regression with multiple lower bound tests

[David Empson]

On 16/06/2014, at 11:36 pm, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Jun 16, 2014 at 5:07 AM, David Empson <demp...@emptech.co.nz> wrote:
> 
>> It appears SQLite 3.8.1 removed an optimisation where earlier versions of
>> the query planner were checking for two or more "lower bound" comparisons
>> against the key for an index, and combining them so the greater of the two
>> values was used as a lower bound.
>> 
>> 
> There never has been any such optimization in SQLite.  If it picked the
> better lower bound in 3.8.0, then that was purely by luck.

OK thanks, that makes sense.

> I suggest you rewrite your query.  Instead of
> 
>... WHERE x BETWEEN ?1 AND ?2 AND x>?3
> 
> Consider using
> 
>... WHERE x BETWEEN max(?1,?3) AND ?2 AND x>?3

I assume that was supposed to be WHERE x BETWEEN max(?1,?3) AND ?2.

I agree, that seems a reasonable solution. Something like that was on 
tomorrow's todo list.

> Also, when running EXPLAIN, please first give the command-line shell the
> ".explain" command in order to set output formatting up to show the program
> listing in a more readable format.

Noted, thanks for the tip.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance regression with multiple lower bound tests

[David Empson]

I've recently noticed a major drop in performance in one part of our main 
application at work, and have managed to track it down to a change in recent 
versions of SQLite.

We are storing a log in a simple SQLite table, and have a viewing screen which 
allows the user to browse through the log.

CREATE TABLE log (rowid INTEGER PRIMARY KEY NOT NULL, type INTEGER, line TEXT);

The rowid incorporates the timestamp, allowing us to quickly locate particular 
times by calculating the rowid from the desired time.

I noticed the log view was getting progressively slower the further I got into 
the table. By the time it got to row 100K+ it was taking over 100 ms to move up 
or down rows. In older versions the same operation was nearly instant. With 
some instrumentation I confirmed that SQLite was taking a long time to return 
the first row in each query, and that this had started some time between SQLite 
3.7.17 and SQLite 3.8.4.3. I also tried 3.8.5, which was the same as 3.8.4.3.

This is the general form of the query we use (including our own custom 
functions, which are defined as SQLITE_DETERMINISTIC when using SQLite 3.8.3 or 
later):

SELECT rowid, type, time_from_rowid(rowid) || line FROM log WHERE rowid BETWEEN 
calc_rowid(?1) and calc_rowid(?2) AND rowid > ?3 ORDER BY rowid LIMIT ?4;

The parameters are ?1 = minimum rowid for the entire view, ?2 = maximum rowid 
for the entire view, ?3 = rowid immediately preceding the first line to 
display, ?4 = number of rows to fetch at once (typically 100). I confirmed our 
functions were not part of the problem by substituting externally calculated 
values, and reduced the SELECT to only return rowid. The performance was 
unchanged.

I examined the statement using EXPLAIN QUERY PLAN and EXPLAIN, checked it with 
several SQLite versions, and have worked out what happened.

For SQLite 3.8.0.2 and earlier, the integer primary key index is used to start 
at whichever is the greater of calc_rowid(?1) and ?3, then the table is scanned 
returning rows until the row limit or calc_rowid(?2) is reached.

For SQLite 3.8.1 and later, the integer primary key index is used to start at 
calc_rowid(?1), then the table is scanned ignoring all rows until ?3 is reached 
(taking a significant amount of time when there are hundreds of thousands of 
rows), then rows are returned until the row limit or calc_rowid(?2) is reached.

It appears SQLite 3.8.1 removed an optimisation where earlier versions of the 
query planner were checking for two or more "lower bound" comparisons against 
the key for an index, and combining them so the greater of the two values was 
used as a lower bound.

I was able to repeat this with an even simpler table using the command line 
tool, and two greater-than comparisons (so it isn't specific to BETWEEN 
combined with greater-than). I've included the output to EXPLAIN QUERY PLAN and 
EXPLAIN to assist, and have inserted blank lines as a reading aid.

slow.db3 (about 8.5 MB, containing just 1048576 sequential rowids).
https://www.dropbox.com/l/4xTnsxfRithPnHOAZQwtSt?

% sqlite3 slow.db3
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.

sqlite> .schema
CREATE TABLE log(rowid integer primary key not null);

sqlite> explain query plan select rowid from log where rowid > 0 and rowid < 
100 order by rowid limit 10;
0|0|0|SEARCH TABLE log USING INTEGER PRIMARY KEY (rowid>? AND rowid explain select rowid from log where rowid > 0 and rowid < 100 order 
by rowid limit 10;
0|Init|0|14|0||00|
1|Noop|0|0|0||00|
2|Integer|10|1|0||00|
3|OpenRead|0|2|0|0|00|
4|SeekGT|0|12|2||00|
5|Integer|100|3|0||00|
6|Rowid|0|4|0||00|
7|Ge|3|12|4||6b|
8|Copy|4|5|0||00|
9|ResultRow|5|1|0||00|
10|IfZero|1|12|-1||00|
11|Next|0|6|0||00|
12|Close|0|0|0||00|
13|Halt|0|0|0||00|
14|Transaction|0|0|1|0|01|
15|TableLock|0|2|0|log|00|
16|Integer|0|2|0||00|
17|Goto|0|1|0||00|

sqlite> explain query plan select rowid from log where rowid > 0 and rowid > 
80 and rowid < 100 order by rowid limit 10;
0|0|0|SEARCH TABLE log USING INTEGER PRIMARY KEY (rowid>? AND rowid explain select rowid from log where rowid > 0 and rowid > 80 and 
rowid < 100 order by rowid limit 10;
0|Init|0|15|0||00|
1|Noop|0|0|0||00|
2|Integer|10|1|0||00|
3|OpenRead|0|2|0|0|00|
4|SeekGT|0|13|2||00|
5|Integer|100|3|0||00|
6|Rowid|0|4|0||00|
7|Ge|3|13|4||6b|
8|Le|6|12|4||6c|
9|Copy|4|7|0||00|
10|ResultRow|7|1|0||00|
11|IfZero|1|13|-1||00|
12|Next|0|6|0||00|
13|Close|0|0|0||00|
14|Halt|0|0|0||00|
15|Transaction|0|0|1|0|01|
16|TableLock|0|2|0|log|00|
17|Integer|0|2|0||00|
18|Integer|80|6|0||00|
19|Goto|0|1|0||00|

sqlite> .timer on

sqlite> select rowid from log where rowid > 80 and rowid < 100 order by 
rowid limit 10;
81
82
83
84
85
86
87
88
89
800010
Run Time: real 0.004 user 0.00 sys 0.00

sqlite> select rowid from log where rowid > 0 and rowid > 80 and rowid < 
100 order by rowid limit 10;
81
82
83

Re: [sqlite] Corrupted database files

[David Empson]

On 5/06/2014, at 11:21 pm, Lasse Jansen <la...@lasselog.com> wrote:

> Hi,
> 
> we have a Mac app that uses CoreData which internally uses SQLite. Some of
> the queries are not expressible within CoreData, so we send them manually
> using the sqlite library that comes with Mac OS X. Now some of our users
> have reported that their database file got corrupted and after some
> researching I think it's because of multiple copies of SQLite being linked
> into the same application as described here:
> 
> http://www.sqlite.org/howtocorrupt.html
> 
> Even though we link CoreData to our application and CoreData uses sqlite
> internally we still have to explicitly link libsqlite as the CoreData
> version of sqlite is inaccessible due to the usage of two-level-namespacing.
> 
> So I have two questions:
> 1. Can this be solved without dropping CoreData?
> 2. If not, is there a workaround that we could use until we replaced
> CoreData with something of our own?

One possibility would be to structure your application so that it spawns a 
subprocess (not just another thread), then one process uses CoreDate while the 
other uses SQLite directly. Separate processes should avoid the issue with 
other locks in the same process being broken by a close.

Of course that will add more complexity due to needing to do some kind of 
inter-process communication, but it might be a manageable solution while you 
factor out CoreData.

Another idea which might be worth pursuing, but probably not in a reasonable 
timeframe: file a bug report with Apple, requesting that they add a means for 
applications to directly invoke the SQLite instance inside CoreData (with 
sufficient evidence of the problem you are encountering to explain why this 
design flaw in CoreData prevents safe independent use of SQLite), or extend 
CoreData as required so that you don't need to work around it.

> I'm thinking of this:
> As the problem seems to occur due to calling close() and we only use
> libsqlite for read-only access, would just not closing the read-only
> database connection prevent the corruption?

Probably not, because when CoreData closes its connection, your read-only 
connection via the second instance of SQLite will have broken locks from then 
on. If CoreData opens the database again, you could get access collisions and 
read incomplete data, due to your reader not being blocked while a CoreData 
write is in progress.

-- 
David Empson
demp...@emptech.co.nz
Snail mail: P.O. Box 27-103, Wellington 6141, New Zealand

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64bit compatibility warnings

[David Empson]

On 10/04/2013, at 11:06 AM, Nico Williams  wrote:

> On Mon, Apr 8, 2013 at 8:52 AM, Alexandr Němec  wrote:
>>> The first warning is harmless and results from a prior datatype change.
>>> Dan has already fixed that one.  The other four appear to be due to an
>>> MSVC compiler bug, since every (i64%int) operation will always yield a value
>>> that can fit in an int, no?
>> 
>> Ok, thank for this comment. Of course, you are right, although I wouldn't
>> call it a compiler bug. The (i64%int) operation, gives an int result, but
>> allocated into an i64 value, so this is why the compiler reports the
>> warning. But thanks, it is obvious now, that these warnings can be ignored.
> 
> The compiler is complaining about an int64->int conversion, not the reverse.
> 
> Why on Earth would going from an int (64-bit or smaller) to an int64
> cause a problem?
> 
> No, this is a compiler bug.

It is not a compiler bug. It is a failure of the compiler to deduce that the 
warning is unnecessary.

One of the lines in question is:

Line 71133 iBuf = p->iReadOff % p->nBuffer;

iBuf is an int.

p->iReadOff is an i64.

p->nBuffer is an int.

C's usual arithmetic conversions specify that if either operand of a binary 
operator is an integer type larger than int then the other operand is first 
converted to the larger type. Therefore p->nBuffer is converted from int to i64 
before doing the modulo operation.

We now have i64 % i64, producing a result of type i64.

The statement then stores that i64 result into an int. i64 conversion to int 
without a cast produces the warning in MSVC (if int is 32-bit).

If the compiler was smarter, it would pay attention to the fact that the 
modulus cannot exceed the range of an int, therefore the warning is not 
necessary.

The only reason I can see not to have an explicit cast is that it risks hiding 
a future bug if the types of the variables are changed.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users