sqlite - All Reports with description

  %  package req sqlite3
  3.5.1
  % sqlite3 db hl7journal.db
  %  db busy {puts busy; expr 0}
  %  db eval {select count(*) from incoming}
  busy
  database is locked

Here the callback script is only invoked once, even though it returns 0. If we put this code in a procedure it works as desired/expected:

  % proc b args {puts busy; after 1000; expr 0}
  % db  busy b
  % db eval {select count(*) from incoming}
  busy
  busy
  busy
  ^C

I humbly suggest the following changes:

* mention the extra argument in the documentation of the Tcl interface

* forward the error from the busy callback to Tcl (replacing the "database is locked")

* enhance errorInfo to make the invokation of the busy callback apparent.

Currently, I'm getting this errorInfo:

  % db busy {puts hi; after 1000; return 0}
  %  db eval "select count(*) from incoming"
  hi
  database is locked
  %  set errorInfo
  bad option "0": must be -code, -errorcode, or -errorinfo
      while executing
  "return 0 0"
      invoked from within
  "db eval "select count(*) from incoming""

It would be nicer to get something like

  bad option "0": must be -code, -errorcode, or -errorinfo
      while executing
  "return 0 0"
      from busy callback
  "puts hi; after 1000; return 0 0"
      invoked from within
  "db eval "select count(*) from incoming""

As a result of this bug, it is difficult to distinguish between fatal and transient ATTACH errors, particularly when sqlite3_step() is used.

I am attaching a test program that demonstrates the problem.

    vtab6-2.2...
    Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
	 Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
    vtab6-2.3... Ok
    vtab6-2.4...
    Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
	 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
    vtab6-2.5...
    Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
	 Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
    vtab6-2.6...
    Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
	 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
    vtab6-3.1... Ok
    vtab6-3.2... Ok
    vtab6-3.3... Ok
    vtab6-3.4... Ok
    vtab6-3.5... Ok
    vtab6-3.6... Ok
    vtab6-3.7... Ok
    vtab6-3.7... Ok
    vtab6-4.1... Ok
    vtab6-4.2... Ok
    vtab6-4.3... Ok
    vtab6-4.4... Ok
    vtab6-4.6... Ok
    vtab6-4.7... Ok
    vtab6-4.8... Ok
    vtab6-4.9... Ok
    vtab6-4.10... Ok
    vtab6-7.1...
    Expected: [1 999 999 2 131 130 999]
	 Got: [1 999 999 999 999 999 999]
    vtab6-8.1... Ok
    vtab6-8.2... Ok
    vtab6-8.3... Ok
    vtab6-9.1... Ok
    vtab6-9.1.1...
    Expected: []
	 Got: [2 22 {}]
    vtab6-9.2...
    Expected: []
	 Got: [2 22 {}]
    vtab6-10.1... Ok

Parentheses in a FROM statement seem to mess with the ability to use table aliases in the "what" part. Here is an example:

Start SQLite:

    $ sqlite3 employee.db
    SQLite version 3.5.4
    Enter ".help" for instructions

create a couple of tables and populate them with test data:

    sqlite> create table person (id integer, name text, employerid integer);
    sqlite> create table employer (id integer, name text);
    sqlite> insert into person (id, name, employerid) values (1, "Dave", 1);
    sqlite> insert into employer (id, name) values (1, "ACME");

Run a simple query with no parentheses in the FROM statement:

    sqlite> select b.id from person as a inner join employer as b on a.employerid = b.id;
    1

Everything works as expected. Now, repeat that query with parentheses:

    sqlite> select b.id from (person as a inner join employer as b on a.employerid = b.id);
    SQL error: no such column: b.id

There you have it.

This may be related to ticket #1822, although that ticket deals with aliases and subqueries. This problem seems to be more fundamental.

Many thanks,

-- Dave

Since there is no an API to retrieve number of records in result set and as prepare/step is far to slow, suggestion is to create one API, with maximum optimization for the purpose.

But you can't do that, because after each sqlite_step(), you lost in memory data fields fetched for each row, so the way is counting rows after iteration.

Also, tickets should be used for reporting bugs only.

You could also try a "SELECT count(*) ..." query to determine the expected size of a result set.

As the above poster points out, there is no way to efficiently add this capability to SQLite (at least, no more efficiently than evaluating a count(*) query before the "real" query).

Dan.

Dan: Since this feature should speed-up performance of a SQLite wrapper, "SELECT count(*) ..." is a real problem (require careful parsing to redesine gived query). "SELECT count(*) ..." itself is reasonably fast.

Building sqlite succeeds with _XOPEN_SOURCE defined, but it causes _POSIX_C_SOURCE to be defined which leads to failing to define F_FULLFSYNC which prevents use of the fullfsync pragma.

thanks in advance.

I will detect error in MSVC v.6.0 (optimization: Maximize Speed mode). Look accessPayload() function in amalgamation project (the same in btree.c in separeted project).

This is fragment from sqlite.cod (listing generated by MSVC compiler). Look my comment "//SLA: ..."

<<<<<< BEGIN LISTING
;	COMDAT _accessPayload
_TEXT	SEGMENT
_pCur$ = 8
_offset$ = 12
_amt$ = 16
_pBuf$ = 20
_skipKey$ = 24
_eOp$ = 28
_nKey$ = -12
_iIdx$ = -8
_pPage$ = -4
_pBt$ = -4
_ovflSize$62039 = 24
_nextPage$62040 = 12
_pDbPage$62056 = -12
_accessPayload PROC NEAR				; COMDAT

; 29385: ){

  00000	83 ec 0c	 sub	 esp, 12			; 0000000cH
  00003	53		 push	 ebx
  00004	55		 push	 ebp
  00005	56		 push	 esi

; 29386:   unsigned char *aPayload;
; 29387:   int rc = SQLITE_OK;
; 29388:   u32 nKey;
; 29389:   int iIdx = 0;
; 29390:   MemPage *pPage = pCur->pPage;     /* Btree page of current cursor entry */

  00006	8b 74 24 1c	 mov	 esi, DWORD PTR _pCur$[esp+20]
  0000a	57		 push	 edi
  0000b	33 db		 xor	 ebx, ebx
  0000d	8b 7e 1c	 mov	 edi, DWORD PTR [esi+28]

; 29391:   BtShared *pBt;                   /* Btree this cursor belongs to */
; 29392:
; 29393:   assert( pPage );
; 29394:   assert( pCur->eState==CURSOR_VALID );
; 29395:   assert( pCur->idx>=0 && pCur->idx<pPage->nCell );
; 29396:   assert( offset>=0 );
; 29397:   assert( cursorHoldsMutex(pCur) );
; 29398:
; 29399:   getCellInfo(pCur);

  00010	56		 push	 esi
  00011	89 5c 24 18	 mov	 DWORD PTR _iIdx$[esp+32], ebx
  00015	89 7c 24 1c	 mov	 DWORD PTR _pPage$[esp+32], edi
  00019	e8 00 00 00 00	 call	 _getCellInfo

; 29400:   aPayload = pCur->info.pCell + pCur->info.nHeader;

  0001e	8b 4e 24	 mov	 ecx, DWORD PTR [esi+36]

; 29401:   nKey = (pPage->intKey ? 0 : pCur->info.nKey);

  00021	8a 47 03	 mov	 al, BYTE PTR [edi+3]
  00024	33 ed		 xor	 ebp, ebp
  00026	83 c4 04	 add	 esp, 4
  00029	66 8b 6e 38	 mov	 bp, WORD PTR [esi+56]
  0002d	03 e9		 add	 ebp, ecx
  0002f	84 c0		 test	 al, al
  00031	74 08		 je	 SHORT $L80642
  00033	33 c0		 xor	 eax, eax
  00035	89 44 24 10	 mov	 DWORD PTR _nKey$[esp+28], eax
  00039	eb 0a		 jmp	 SHORT $L80643
$L80642:
  0003b	8b 4e 2c	 mov	 ecx, DWORD PTR [esi+44]
  0003e	8b 4e 28	 mov	 ecx, DWORD PTR [esi+40]
  00041	89 4c 24 10	 mov	 DWORD PTR _nKey$[esp+28], ecx
$L80643:

; 29402:
; 29403:   if( skipKey ){

  00045	8b 4c 24 30	 mov	 ecx, DWORD PTR _skipKey$[esp+24]	//SLA:  ecx:=skipKey

; 29404:     offset += nKey;

  00049	8b 7c 24 24	 mov	 edi, DWORD PTR _offset$[esp+24]	//SLA:  edi:=offset
  0004d	3b cb		 cmp	 ecx, ebx
  0004f	74 02		 je	 SHORT $L62032
  00051	03 f9		 add	 edi, ecx				//SLA:  offset += skipKey   !!!
$L62032:

; 29405:   }
; 29406:   if( offset+amt > nKey+pCur->info.nData ){

  00053	8b 54 24 28	 mov	 edx, DWORD PTR _amt$[esp+24]
  00057	8b 46 30	 mov	 eax, DWORD PTR [esi+48]
  0005a	03 44 24 10	 add	 eax, DWORD PTR _nKey$[esp+28]
  0005e	8d 0c 17	 lea	 ecx, DWORD PTR [edi+edx]		//SLA:  edi have (offset+skipKey) !!!
  00061	3b c8		 cmp	 ecx, eax
  00063	76 0d		 jbe	 SHORT $L62033
  00065	5f		 pop	 edi
  00066	5e		 pop	 esi
  00067	5d		 pop	 ebp

; 29407:     /* Trying to read or write past the end of the data is an error */
; 29408:     return SQLITE_ERROR;

  00068	b8 01 00 00 00	 mov	 eax, 1
  0006d	5b		 pop	 ebx

; 29510: }
<<<<<<< END LISTING

So, optimization imposible !!! We need disable optimization in MSVC project :(

Best regards, Vyacheslav.

At this point we recommend that you not use MSVC 6.0 to build SQLite or any other project for that matter. If you must use MSVC 6.0, at least disable optimization.

To be fair to MSVC, other compilers also have bugs. Ticket #2469 is an example of an optimizer bug in GCC which has since been fixed.

Thank you for reporting this problem. But this really is a bug in MSVC, not in SQLite, so there is nothing that we can do about it.

It creates named mutexes which, by design, are global rather than process-specific as intended. This might be minimally acceptable except that the function reuses the same name every time it attempts to create a SQLITE_MUTEX_FAST or SQLITE_MUTEX_RECURSIVE.

The result is that every call returns the exact same semaphore to every thread in every process using sqlite3. Once this mutex is owned by one process, other processes calling sqlite3_mutex_enter() will be blocked. Much the same is true for the static mutexes. Every process ends up using the exact same SQLITE_MUTEX_STATIC_MASTER, SQLITE_MUTEX_STATIC_MEM, etc.

There's another flaw that is fairly minor compared with the above: in an attempt to avoid concurrency when creating the static mutexes, this function uses an API call that is thoroughly deprecated.

The attached patch remedies all of these issues. Since the logic that protects the creation of the static mutexes may not be self-evident, here's an explanation:

The existence (or non-existence) of a given named mutex is itself a semaphore. If the isInit flag is false, the code attempts to create a mutex whose name is unique to that process. If the attempt is successful, there are two possibilities: (1) either the current thread is the first to reach this code & may proceed; (2) or while the current thread was making its preparations, another thread created the mutex, did the init, then closed the mutex. Testing isInit immediately after creating the mutex determines which possibility is valid.

If mutex creation fails due to a duplicate name, then another thread is currently performing the init. In this case, the current thread simply has to wait a while until the other thread is done & isInit becomes true.

Submitted by Rich Walsh (richATe-vertise.com)

Currently, os2Truncate() does nothing useful. It moves the pointer associated with a file handle but has no effect on the file's size. The patch provides code that will change the actual size of a file on the disk.

os2FullPathname() is overly complex and fails in marginal cases. E.g. for file "c:\programs\myapp\mydata.db", it fails to return a fully-qualified path when given "c:mydata.db". The patch replaces existing code with a single API call that always returns a valid result.

Submitted by Rich Walsh (richATe-vertise.com)

./configure --prefix=/my/private/sqlite/sqlite-3.5.4
... # success
make
... # success
make install
...
tclsh ./tclinstaller.tcl 3.5
error deleting "/usr/share/tcl8.4/sqlite3": not owner
    while executing
"file delete -force $LIBDIR/sqlite3"
    (file "./tclinstaller.tcl" line 17)
make: *** [tcl_install] Error 1

1. If you run make install as root.
2. If you use ./configure --disable-tcl

make install as root will end up copying files into the system's library directory and is almost certainly not what you want if you specified your own --prefix.

We've prepared a patch for SQLite 3.5.x to allow applications to install a watch_hook into the database, that will be invoked everytime the database is changed with exact details about the change that was performed.

1. I am unconvinced that this patch solves a problem that needs solving. It is vitally important to a project like SQLite that we work to avoid clutter and cruft. That means that any change must have a compelling rational or else it is rejected.

2. The patches are against version 3.5.4. There were many changes to the core for 3.5.5 and the patches no longer work.

SQL error: wrong number of arguments to function glob()

You can reproduce this on Mac OS X 10.5 with the following:

$ sqlite3 test.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> create table Identifiers ( ident text );
sqlite> insert into Identifiers values ( "foo?" );
sqlite> insert into Identifiers values ( "foo" );
sqlite> insert into Identifiers values ( "foo_" );
sqlite> select * from Identifiers where ident like "foo\_" escape "\";
foo_
sqlite> select * from Identifiers where ident glob "foo\?" escape "?";
SQL error: wrong number of arguments to function glob()

   select * from identifiers where ident glob 'foo[?]';

Note also the use of single-quotes instead of double-quotes around the string literal. SQL requires single-quotes.

// init sqlite3_prepare_v2( db,
"SELECT [file_id],[file_name],[file_mime],[file_type],"
"[file_size],[date_created],[date_accessed],[data] "
"FROM file_cache WHERE [file_id] = ? LIMIT 1;",
-1, &sqQueryStatement, &unused );

// query function -- called repeatedly (usually second or third run starts to cause big delays)

/* begin function */

sqlite3_reset( sqQueryStatement );
sqlite3_bind_int( sqQuerystatement, 1, 292 );
sqlite3_step( sqQueryStatement );

bla = sqlite3_column_int( sqQueryStatement, 0 );

/* ... */

char *erm;

sqlite3_exec( db, "UPDATE file_cache SET [date_accessed] = DATETIME( 'NOW', 'LOCALTIME' ) WHERE [file_id] = 292", NULL, NULL, &erm );

/* ... */

sqlite3_clear_bindings( sqQueryStatement );

/* end function */

If sqlite3_exec() is commented out, consecutive calls to the function run in less than a millisecond. With sqlite3_exec() included, sqlite3_reset() call in this function takes > 100 ms to complete.

Tested: Windows Vista Ultimate 32 bit, Visual Studio 2005 8.0.50727.876

   nm -A .libs/libsqlite3.so.0.8.6 | grep sqlite3ResetPrngState

which shows no entry point. And:

   nm -A .libs/random.o  | grep sqlite3ResetPrngState

which also shows no entry point.

1. Database was created with SQLite 3.3.4. Around 20 standard fieds and one BLOB.

2. The only one existed table was dumped with shell of SQLite 3.5.4. SQL script seems to be coorrect.

3. Opened SQLite 3.5.4 and read script in new DB. The error "String or BLOB exceed size limit" are sown for several lines. Many records missing.

4. Attempted to dump table with shell of version 3.3.6 (have no more 3.3.4 shell) and read into new DB with 3.5.4 shell The same errors are shown.

The same steps was attempted with 3.3.6. shell only. All seems to be correct.

The log below was generated by the original SQLite3.exe 3.5.4 downloaded from the web-site. It shows that the entry 'A-123' is present in the table but is not matched by the query. It also demonstrates that non-matching entries are found instead.

  DROP TABLE IF EXISTS fts3;
  CREATE VIRTUAL TABLE fts3 using fts3 (a);

  INSERT INTO fts3 VALUES ('A-123');
  SELECT * FROM fts3;
  A-123
  SELECT * FROM fts3 WHERE fts3 MATCH 'A-123';

  INSERT INTO fts3 VALUES ('A-12');
  SELECT * FROM fts3 WHERE fts3 MATCH 'A-123';
  A-12

  INSERT INTO fts3 VALUES ('A-1');
  SELECT * FROM fts3 WHERE fts3 MATCH 'A-123';
  A-12
  A-1

Here is the SQL only (without output, for easy reproduction):

  DROP TABLE IF EXISTS fts3;
  CREATE VIRTUAL TABLE fts3 using fts3 (a);

  INSERT INTO fts3 VALUES ('A-123');
  SELECT * FROM fts3;
  SELECT * FROM fts3 WHERE fts3 MATCH 'A-123';

  INSERT INTO fts3 VALUES ('A-12');
  SELECT * FROM fts3 WHERE fts3 MATCH 'A-123';

  INSERT INTO fts3 VALUES ('A-1');
  SELECT * FROM fts3 WHERE fts3 MATCH 'A-123';

My sincere apologies if this has caused doubt or uncertainties to anyone!

For example, how to run the test incrvacuum-ioerr-1.31.4

PS: You cannot run individual tests. You have to run an entire script.

.load test_schema.so

create virtual table x using schema;

-- this produces results

select * from x where dflt_value is null;

-- this produces a segmentation fault

select * from x lhs left outer join x rhs on lhs.dflt_value = rhs.dflt_value;

  static i64 doubleToInt64(double r){
    ...
    if( r<(double)minInt ){
      return minInt;
    }else if( r>(double)maxInt ){
      return minInt;     <-- is this correct, shouldn't it be maxInt?
    }else{
      return (i64)r;
    }
  }

maybe adding a small comment in there would clarify this issue

That leaves user completely clueless what mistake in his sql he actually made.

Some evidence of confusion can be found here: http://bugs.php.net/bug.php?id=33117

1. WORKS: CREATE TABLE my_table ("my_column" integer NOT NULL);
2. GIVES ERROR: ALTER TABLE my_table ADD "my_other_column" integer NOT NULL;

(2) gives the error: "SQL error: Cannot add a NOT NULL column with default value NULL"

This is inconsistent, either both cases should work, or both should give an error.

If a column can't have a default NULL value, how it will be added to a table with existing rows ?

How that columns will have the default column value is there´s no default and has the NOT NULL constraint ?

Please let me know how to run individual tests, so I can debug these and submit fix suggestions.

Thanks.

Thanks.

TCL/TK is not installed, as I don't need it, and don't really want to install it

Sequence of commands to reproduce :

tar -xzvf sqlite-3.5.4.tar.gz

cd sqlite-3.5.4

mkdir bld

cd bld

../configure --disable-tcl --without-tcl

make

The last command dies with

cp parse.c opcodes.c keywordhash.h tsrc

tclsh ../tool/mksqlite3c.tcl

make: tclsh: Command not found

make: *** [sqlite3.c] Error 127

root@slack12:~/install/sqlite-3.5.4/bld# ls ..

Roman

#include <stdio.h>

#include "sqlite3.h"

int main() {

  sqlite3 *db;

  sqlite3_stmt *stmt;

  sqlite3_open("test.db", &db);

  sqlite3_prepare(db, "SELECT 1", -1, &stmt, NULL);

  sqlite3_step(stmt);

  sqlite3_exec(db, "CREATE TABLE t(a INT)", NULL, NULL, NULL);

  printf("%s\n", sqlite3_errmsg(db));

  return -1;

}

CREATE TABLE isn't commited. There is no BEGIN, CREATE TABLE doesn't return an error, so I think table should be created.

Example:

-all rows:

sqlite> select vend_city,vend_state,vend_zip from vendors;

vend_city vend_state vend_zip

---------- ---------- ----------

Bear Town MI 44444

Anytown OH 44333

Dollsville CA 99999

New York NY 11111

London N16 6PS

Paris 45678

-Using ||, the 2 last rows with NULL states do not show on the output:

sqlite> select vend_city ||','|| vend_state ||','|| vend_zip from vendors;

vend_city ||','|| vend_state ||','|| vend_zip

Anytown,OH,44333

Dollsville,CA,99999

New York,NY,11111

   NULL || <expr>

evaluates to NULL, regardless of the value of <expr>. This is in accordance with the SQL standard.

Use the coalesce() or ifnull() functions to work around this.

-fPIC is needed when building extensions (some platforms don't need this or don't care --- x86-64 does)

  diff --git a/test/loadext.test b/test/loadext.test
  index 81e152f..2a7fa2e 100644
  --- a/test/loadext.test
  +++ b/test/loadext.test
  @@ -64,7 +64,7 @@ if {![file exists $testextension]} {
     set srcdir [file dir $testdir]/src
     set testextsrc $srcdir/test_loadext.c
     if {[catch {
  -    exec gcc -Wall -I$srcdir -I. -g -shared $testextsrc -o $testextension
  +    exec gcc -Wall -fPIC -I$srcdir -I. -g -shared $testextsrc -o $testextension
     } msg]} {
       puts "Skipping loadext tests: Test extension not built..."
       puts $msg

  4 errors out of 61527 tests
  Failures on these tests: exclusive-ioerr-2.280.4
  exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4
  All memory allocations freed - no leaks

details:

  exclusive-ioerr-2.280.3... Ok
  exclusive-ioerr-2.280.4...
  Expected: [8ee59fe0b5bc391ecc5002539379b063]
       Got: [35e56178ad878809d4789c5797265a23]
  exclusive-ioerr-2.281.1... Ok
  exclusive-ioerr-2.281.2... Ok
  exclusive-ioerr-2.281.3... Ok
  exclusive-ioerr-2.281.4...
  Expected: [95762fb35ef83ddba65f681325346ef2]
       Got: [1c20c63d975ad85b395a5e7701d785c2]
  exclusive-ioerr-2.282.1... Ok
  exclusive-ioerr-2.282.2... Ok
  exclusive-ioerr-2.282.3... Ok
  exclusive-ioerr-2.282.4...
  Expected: [2c5ea7db8424f38d17cdff41056da0e0]
       Got: [c02841c40d3e6e0579922745bd9c0260]
  exclusive-ioerr-2.283.1... Ok

[...]

    incrvacuum-ioerr-1.31.4...
    Expected: [ea55042a449c3b4759730a882e8271a0]
	 Got: [9951814984696d0811cacbe862060af8]
    incrvacuum-ioerr-1.32.1... Ok

For example, I've gotten SQLite as part of my OS or another program, and I don't know what value of SQLITE_MAX_COLUMN was used to compile this SQLite. I'm trying to track down a bug in my code, and if this value was too small, that would explain it. (In truth, it's probably not the cause, but I'd like to rule it out.)

I'd like a way to print the value of these limits at runtime. It doesn't have to have a stable API -- it would mostly be useful for debugging. If the command-line client had a command ".limits" that printed all of these values, that would be super.

"shell.c:364: error: format not a string literal and no format arguments"

See http://mail.kde.org/pipermail/kde-dashboard/2008-January/008494.html

Any SQLite newer is addected as well. The patch below fixes misused fprintf.

Index: shell.c
===================================================================
--- shell.c (revision 761314)
+++ shell.c (working copy)
@@ -361,7 +361,7 @@
output_c_string(p->out, z);
}
if( bSep ){
- fprintf(p->out, p->separator);
+ fprintf(p->out, "%s", p->separator);
}
}

  exclusive-ioerr-2.2.1... Ok

  ./testfixture: wrong # args: should be "cksum db"
      while executing
  "ifcapable vacuum {
    do_ioerr_test ioerr-2 -cksum true -sqlprep {
      BEGIN;
      CREATE TABLE t1(a, b, c);
      INSERT INTO t1 VALUES(1, randstr(50,..."
      (file "../test/ioerr.test" line 58)
      invoked from within
  "source $testdir/ioerr.test"
      (file "../test/exclusive3.test" line 50)
      invoked from within
  "source $testfile"
      ("foreach" body line 5)
      invoked from within
  "foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
      set tail [file tail $testfile]
      if {[lsearch -exact $EXCLUDE $tail]>=0} continue
  ..."
      ("for" body line 7)
      invoked from within
  "for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} {
    if {$Counter%2} {
      set ::SETUP_SQL {PRAGMA default_synchronous=off;}
    } else ..."
      (file "..//test/all.test" line 85)

  io-4.1...
  Expected: [3]
     Got: [2]
  io-4.2.1... Ok
  io-4.2.2... Ok
  io-4.2.3...
  Expected: [3]
     Got: [2]
  io-4.3.1... Ok

Let me know how to run individual test cases and how this might be fixed.

Here's how I built sqlite using latest CVS. If something is wrong here, let me know and I'll rebuild/retest.

I'm building on latest Fedora fc7.

Thanks.

_______

  net1#uname -a
  Linux net1.coolsurf.com 2.6.23.12-52.fc7 #1 SMP Tue Dec 18 20:27:10 EST 2007 x86_64 x86_64 x86_64 GNU/Linux

  net1#build_sqlite
  mkdir -p /build/work/sqlite-3.5.4
  cd /build/work/sqlite-3.5.4
  unset CDPATH
  export CFLAGS='-pipe -O3 -g -DSQLITE_DISABLE_DIRSYNC=1 -Wall'
  rm -rf bld
  cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update .
  mkdir bld
  cd bld
  ../configure --prefix=/common/pkgs/sqlite-3.5.4 --enable-tcl --with-tcl=/usr/lib64 --enable-threadsafe --enable-threads-override-locks
  make
  groupadd vuser || /bin/true
  useradd -M -g vuser -d /vhost/davidfavor.com/users/david -s /bin/zsh   david  || /bin/true
  useradd -M -g vuser -d /vhost/livefeast.com/users/yemiah -s /bin/zsh yemiah || /bin/true
  chown david:vuser -R ..
  su -c "make test" david

  '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -DSQLITE_MEMDEBUG'

   exclusive-malloc-1.transient.746...make: *** [fulltest]
      Segmentation fault

Failures when 'make fulltest' built with CFLAGS of

   '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1'

   Skipping malloc tests: not compiled with -DSQLITE_MEMDEBUG...
   6 errors out of 61998 tests
   Failures on these tests: exclusive-ioerr-2.280.4
      exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4
      incrvacuum-ioerr-1.31.4 io-4.1 io-4.2.3
   All memory allocations freed - no leaks
   Maximum memory usage: 14376554 bytes

Pre 3.5.x builds work fine on Fedora. If you're open to debugging all these, I'd like to go through and resolve all these one by one, so Fedora has a clean build/fulltest.

Please let me know how to run each test individually and I'll try to figure out the problem with each.

Thanks.

We've checked and the same error appears using sqlite administrator (which is a Delphi development too, but using ZEOS). Since it goes wrong within sqlite3step this might be a sqlite isue?

albert drent aducom software

If you can provide a specific example of what is not working we can investigate this problem further. But based on the information provide, we are unable to reproduce the problem and suspect the problem is in the application, not in SQLite.

The string is somewhat like 'insert into demo (myval) value (1e19)'

Will come back here. Albert

It appears to be a problem in used C compiler. Following script is tested in sqlite 3-5-4 shell:

create table t1 (ID integer primary key, v1 double); insert into t1(v1) values(1e18); insert into t1(v1) values(1e19);

This works correctly on linux compiled with GNU compiler 4.1.2. On windows with MinGW 3.2.0 works correctly too, however it fails when shell is compiled with BCC 5.5.1. Without detail check I can only assume there is a problem in representing and using 8 and 6 bytes floats together in SQLite without explicit casting BCC may require. Or this may be a limitation of BCC 5.5.1 or a bug. Without detail check those are most probably causes.

I assume then, that this is a bug in BCC. I do not have access to BCC so there is nothing I can do to fix it. If you can suggest patches that will work around the BCC bug (for example, some unnecessary explicit casts) we will consider adding them to the core. But I am afraid it will be up to you to supply those patches, since we do not have BCC available to experiment with and SQLite works perfectly for every compiler we have access to.

Unfortunately, I have no more time to test any DLL since I do not using any I did not compiled myself. As I understand vac, that one from official SQLite site have this bug. In the signature of DLL can be seen which compiler was used for compilation.

BCC 5.5.1 is free and can freely be downloaded from official Borland site by anyone. This is I prepared to add to the ticket, but you can add yourself. I have spent all my spare time for now. Since it do not break anything it will be probably included in official version:

/*
** The MEM structure is already a MEM_Real. Try to also make it a
** MEM_Int if we can.
*/
SQLITE_PRIVATE void sqlite3VdbeIntegerAffinity(Mem *pMem){
  assert( pMem->flags & MEM_Real );
  assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) );
  pMem->u.i = pMem->r; // <- This causing termination for values >= 1e19
  if( ((double)pMem->u.i)==pMem->r ){
    pMem->flags |= MEM_Int;
  }
}

/*
** The MEM structure is already a MEM_Real. Try to also make it a
** MEM_Int if we can.
*/
SQLITE_PRIVATE void sqlite3VdbeIntegerAffinity(Mem *pMem){
  assert( pMem->flags & MEM_Real );
  assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) );

#ifndef __BORLANDC__
  pMem->u.i = pMem->r;
  if( ((double)pMem->u.i)==pMem->r ){
    pMem->flags |= MEM_Int;
  }
#endif
}

First of all, thank you for attempt to solve the issue. I will first write several facts:

1. Statically linked SQLite 3.5.4 commandline shell (aka sqlite3.exe) is compiled and SQL script worked fine on Windows with MinGW 3.2.3, however not with BCC 5.5.1 (without any special compiler options).

2. Library (aka sqlite3.dll) compiled with MinGW 2.95 (from official SQLite site), MinGW 3.2.3, BCC 5.5.1 and probably other compilers for Windows does FAIL executing provided SQL script only with large floats >=1e19. With MinGW 3.2.3, library was created from official SQLite 3.5.4 distribution with following script:

sh configure

make dll

3. Both (integer and double) have 8 byte size in BCC. BCC is ANSI C complient compiler.

4. It is not the fact that BCC corrupt right-hand side value. It fail trying to convert values >=1e19 and assign it to int64 variable. This will raise invalid floating-point operation exception for most C compilers.

5. Since front-end application use only DLL (compiled by several different C compilers, including provided from offical SQLite site) to execute an SQL command by sqlite_exec(), it is nothing which connect the bug and the application itself (made in Delphi or C).

Current attempt in 4705 will not work from two reasons:

1. The same conversion logic is used.

2. Variable definition in the middle of the function in not allowed by ANSI C. This will fail not only on BCC, but on any ANSI C compiler.

According to upper, conclusion is clear that this is not a bug in BCC, but in all C compiles for Windows - or in conversion double to integer currently used.

Thank you for your time.

Sasa Zeman

Or perhaps this is not a compiler issue at all, but an instance of Delphi messing with FPU registers, as was suggested by the anonymous comment on 2008-Jan-09.

Please note that I do not own a machine that runs windows, and I am unwilling to purchase one for the purpose of fixing this issue. So I cannot download and install BCC 5.5.1 or Delphi. And thus, I cannot reproduce the problem here. So if you want this problem resolved, you are going to need to suggest code for resolving it. Completely disabling the sqlite3VdbeIntegerAffinity() function is not an acceptable resolution because that breaks quite a few other things.

  sqlite> create table t1 (ID integer primary key, v1 double);
  sqlite> insert into t1(v1) values(1e18);
  sqlite> insert into t1(v1) values(1e19);
  sqlite> select * from t1;
  1|1.0e+18
  2|1.0e+19

Could someone please post a little C-code which exhibits the problem? Thanks!

Thank you for your time.

Sasa

  +  static const i64 maxInt = (((i64)0x7fffffff)<<32)|0xffffffff;
  +  static const i64 minInt = ((i64)0x80000000)<<32;
  +
  +  if( r<(double)minInt ){
  +    return minInt;
  +  }else if( r>(double)maxInt ){

runs faster on my compiler if you do this:

  +  static const double maxInt = (((i64)0x7fffffff)<<32)|0xffffffff;
  +  static const double minInt = ((i64)0x80000000)<<32;
  +
  +  if( r<minInt ){
  +    return minInt;
  +  }else if( r>maxInt ){

• The sqlite3.exe console provided on the SQLite web-site is not affected by the problem, it executes the SQL above with no errors.
• The precompiled sqlite3.dll from the SQLite web-site does show the problem (version 3.5.4 tested) when executing the SQL through sqlite3_exec().
• The DISQLite3 Delphi library does not exhibit the problem (same tests as with sqlite3.dll). DISQLite3 is compiled with Borland's C Compiler, so I conclude that it is not a BCC problem.

Cause of the problem with sqlite3.dll is a FPU exception, probably generated by converting 1e19 to a 64-bit integer in the line identified above. AFAIK, Windows -- contrary to Linux -- has FPU exceptions turned on by default, which would explain why Linux users do not see it.

A solution is to disable FPU exceptions for Win applications which use sqlite3.dll. I'd generally recommended to do so, since there are other parts in the SQLite code which depend on the FPU not raising exceptions. SQLite is, by the way, not the odd one out with this recommendation: The Delphi help recommends to do so when using OpenGL as well.

In Delphi, this call disables all FPU exceptions. Run it before calling any SQLite functions:

  Set8087CW($133F); { Disable all FPU exceptions. }

BCC has these available:

  unsigned int _control87(unsigned int newcw, unsigned int mask);
  unsigned int _controlfp(unsigned int newcw, unsigned int mask); /* For MS compatability. */

With FPU exceptions disabled, the SQL executes correctly using sqlite3.dll 3.5.4.

Since I can not recompile sqlite3.dll exactly as the downloaded version, I can not test if changes in [4706] still require to disable FPU exceptions.

That explains why GNU compiled SQLite shell works correctly on Windows too, but not its DLL - GNU force disabling FPU exceptions at program beginning to preserve multi-platfom compatibility. Changes in [4706] solve the issue in this case. Compiled DLL from latest CVS works correctly. If exist similar coversion in the SQLite code, that should be solved as well.

Current souluton in [4706] is suggested to be permanent since SQLite is DB engine, not a math computation library. Thus enabling/disabling FPU exceptions ($133F/$1332) can be a bit of problem if front-end application is math oriented and take explicit care of FPU exceptions. And second reason is connected - not to pressing user of SQLite to set unnecesary FPU settings which is highly depent on his compiler/RAD. And last and most important - preserve multi-platform compatibility.

Thank you once again. As well, thanks to author(s) of SQLite for solving the issue.

Sasa

  +  if( r<(double)minInt ){
  +    return minInt;
  +  }else if( r>(double)maxInt ){
  +    return minInt;  <============= should return maxInt here

also consider making minInt and maxInt const doubles instead of const ints.

I did consider making the minInt and maxInt constants doubles, but that would mean that the conversion from integer to double would occur on the build machine, which might do the conversion differently than the target machine. It seems safer to do the conversion at runtime. We might reconsider this in the future.

  CREATE TABLE x(id integer primary key, a TEXT NULL);
  INSERT INTO x (a) VALUES ('first');
  CREATE TABLE tempx(id integer primary key, a TEXT NULL);
  INSERT INTO tempx (a) VALUES ('t-first');
  CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;

One leak is caused by "CREATE TABLE tempx", a second one by "CREATE VIEW tv1".

The above SQL is a digest of select7.test, select7-2.1.

I therefore consider this issue fixed and I will now close this ticket.

If you do want details, please let me know (I'll recheck this ticket tomorrow I guess).

If you could just let me know which tests caused the leaks you fixed in [4712] I'd be more than glad the port these test as well and let you know my findings.

  print "** comments following the "case" for each opcode in the vdbe.c"

Works for me with:

  print "** comments following the \"case\" for each opcode in the vdbe.c"

There appears to be a TCL dependency even though I've specified --disable-tcl. Stock OpenBSD does not supply TCL and I have no desire to install it.

SELECT * FROM table WHERE name LIKE "&aring;%"

we will not get a match for names starting on &Aring; (which is uppercase for &aring;).

To recreate:

For THREADSAFE builds could either configure be changed to detect the functions gmtime_r and localtime_r or change os_unix.c to explicitly #define HAVE_LOCALTIME_R and HAVE_GMTIME_R?

It seems that both -DHAVE_USLEEP=1, -DHAVE_FDATASYNC=1 and -DOS_UNIX=1 are detected correctly by configure but not used by the generated Makefile. As result, UNIX builds of sqlite3 via ./configure do not use usleep() and fdatasync() and do not define OS_UNIX.

I don't know whether the lack of fdatasync() versus the default fsync() affects anyone.

Please apply the following patch which corrects the problem with "./configure && make". Thank you.

Index: configure
===================================================================
RCS file: /sqlite/sqlite/configure,v
retrieving revision 1.45
diff -u -3 -p -r1.45 configure
--- configure	27 Nov 2007 14:50:07 -0000	1.45
+++ configure	5 Jan 2008 07:41:00 -0000
@@ -18520,9 +18520,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then
     OS_UNIX=0
     OS_WIN=0
     OS_OS2=1
-    TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1"
+    CFLAGS="$CFLAGS -DOS_OS2=1"
     if test "$ac_compiler_gnu" == "yes" ; then
-      TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap"
+      CFLAGS="$CFLAGS -Zomf -Zexe -Zmap"
       BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe"
     fi
   else
@@ -18530,14 +18530,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then
     OS_WIN=1
     OS_OS2=0
     tclsubdir=win
-    TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1"
+    CFLAGS="$CFLAGS -DOS_WIN=1"
   fi
 else
   OS_UNIX=1
   OS_WIN=0
   OS_OS2=0
   tclsubdir=unix
-  TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1"
+  CFLAGS="$CFLAGS -DOS_UNIX=1"
 fi


@@ -19392,7 +19392,7 @@ fi
 echo "$as_me:$LINENO: result: $ac_cv_func_usleep" >&5
 echo "${ECHO_T}$ac_cv_func_usleep" >&6
 if test $ac_cv_func_usleep = yes; then
-  TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1"
+  CFLAGS="$CFLAGS -DHAVE_USLEEP=1"
 fi


@@ -19491,7 +19491,7 @@ fi
 echo "$as_me:$LINENO: result: $ac_cv_func_fdatasync" >&5
 echo "${ECHO_T}$ac_cv_func_fdatasync" >&6
 if test $ac_cv_func_fdatasync = yes; then
-  TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"
+  CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1"
 fi


Index: configure.ac
===================================================================
RCS file: /sqlite/sqlite/configure.ac,v
retrieving revision 1.31
diff -u -3 -p -r1.31 configure.ac
--- configure.ac	27 Nov 2007 14:50:07 -0000	1.31
+++ configure.ac	5 Jan 2008 07:41:00 -0000
@@ -310,9 +310,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then
     OS_UNIX=0
     OS_WIN=0
     OS_OS2=1
-    TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1"
+    CFLAGS="$CFLAGS -DOS_OS2=1"
     if test "$ac_compiler_gnu" == "yes" ; then
-      TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap"
+      CFLAGS="$CFLAGS -Zomf -Zexe -Zmap"
       BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe"
     fi
   else
@@ -320,14 +320,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then
     OS_WIN=1
     OS_OS2=0
     tclsubdir=win
-    TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1"
+    CFLAGS="$CFLAGS -DOS_WIN=1"
   fi
 else
   OS_UNIX=1
   OS_WIN=0
   OS_OS2=0
   tclsubdir=unix
-  TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1"
+  CFLAGS="$CFLAGS -DOS_UNIX=1"
 fi

 AC_SUBST(BUILD_EXEEXT)
@@ -565,13 +565,13 @@ AC_SUBST(TARGET_DEBUG)
 #########
 # Figure out whether or not we have a "usleep()" function.
 #
-AC_CHECK_FUNC(usleep, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1"])
+AC_CHECK_FUNC(usleep, [CFLAGS="$CFLAGS -DHAVE_USLEEP=1"])

 #--------------------------------------------------------------------
 # Redefine fdatasync as fsync on systems that lack fdatasync
 #--------------------------------------------------------------------

-AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"])
+AC_CHECK_FUNC(fdatasync, [CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1"])

 #########
 # Generate the output files.

On the Linux man page, it claims that fdatasync is more efficient than fsync:

"Unfortunately, fsync() will always initiate two write operations: one for the newly written data and another one in order to update the modification time stored in the inode. If the modification time is not a part of the transaction concept fdatasync() can be used to avoid unnecessary inode disk write operations."

As (contrived) example:

  CREATE TABLE t1 ( c1 blob, c2 integer, c3 integer );
  CREATE INDEX i1 on t1(c2,c3);
  CREATE INDEX i2 on t1(c3);

T1 populated with 4096 rows, c1 being 64K random blobs (to make c2, c3 access slower in this case), c2 and c3 being small random integers.

Now:

  sqlite> select sum(c3) from t1;
  sum(c3)
  ----------
  519895

is very slow (several seconds). A table scan is done.

Forcing use of an index, in a way that I know all rows will be included:

  sqlite> select sum(c3) from t1 where c2<1000;
  sum(c3)
  ----------
  519895

This is instantaneous.

It seems to me that if a table scan has to be performance, it makes sense to grab the data from an index whenever possible. ideally the most densely packed index.

(BTW; this is contrived, I know putting the blob as the last column will greatly speed things up).

I'm building on Solaris 10u4 and the full incantation for configure is as follows (most of this is for readline): ../sqlite-3.5.4/configure --prefix=/home/tfb/packages/sqlite-3.5.4 --enable-static=no --with-readline-lib="-L/opt/sfw/lib -R/opt/sfw/lib -lreadline -lcurses" --with-readline-inc=-I/opt/sfw/include --disable-tcl. I use gmake (which is GNU make, in /usr/sfw/bin= on Solaris 10) to compile.

This is not urgent for me - I will just stick with 3.5.3 for now - but it would be nice to avoid mandatory dependence on TCL I think.

Thanks

--tim

  In the #4666 checkin comment is:

  +** Change the value of the P3 operand for a specific instruction.
  +*/
  +void sqlite3VdbeChangeP5(Vdbe *p, int addr, u8 val){

  but should be: .... P5 .... operand

Please advise.

Thanks Tim

Following patch fixes things:

--- sqlite-3.5.4/Makefile.in    Thu Dec 13 19:17:42 2007
+++ sqlite-3.5.4-mingw-fix/Makefile.in  Wed Jan  2 11:37:50 2008
@@ -322,7 +322,7 @@
                -o $@ $(TOP)/src/shell.c libsqlite3.la \
                $(LIBREADLINE) $(TLIBS)

-sqlite3$(EXE): $(TOP)/src/shell.c sqlite3.c sqlite3.h
+sqlite3$(TEXE):        $(TOP)/src/shell.c sqlite3.c sqlite3.h
        $(LTLINK) $(READLINE_FLAGS) -o $@                          \
                -DSQLITE_MAX_SQL_LENGTH=1000000000                  \
                -USQLITE_THREADSAFE -DSQLITE_THREADSAFE=0           \
@@ -577,7 +577,7 @@
          -e 's,$$,\\n",' \
          $(TOP)/tool/spaceanal.tcl >spaceanal_tcl.h
        $(LTLINK) -DTCLSH=2 -DSQLITE_TEST=1 $(TEMP_STORE)\
-                -o sqlite3_analyzer$(EXE) $(TESTSRC) $(TOP)/src/tclsqlite.c \
+                -o sqlite3_analyzer$(TEXE) $(TESTSRC) $(TOP)/src/tclsqlite.c \
                libtclsqlite3.la $(LIBTCL)

$ gcc -v Reading specs from /usr/lib/gcc/i686-pc-cygwin/3.4.4/specs Configured with: /usr/build/package/orig/test.respin/gcc-3.4.4-3/configure --verbose --prefix=/usr --exec-prefix=/usr --sysconfdir=/etc --libdir=/usr/lib --libexecdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-languages=c,ada,c++,d,f77,pascal,java,objc --enable-nls --without-included-gettext --enable-version-specific-runtime-libs --without-x --enable-libgcj --disable-java-awt --with-system-zlib --enable-interpreter --disable-libgcj-debug --enable-threads=posix --enable-java-gc=boehm --disable-win32-registry --enable-sjlj-exceptions --enable-hash-synchronization --enable-libstdcxx-debug Thread model: posix gcc version 3.4.4 (cygming special, gdc 0.12, using dmd 0.125) $

$ CFLAGS=-mno-cygwin ./configure --disable-tcl --enable-threadsafe

$ make

A) The make appears to build sqlite3.exe just fine, without errors or warnings. This binary does work from cmd.exe, BUT not from within the bash cygwin shell for some reason, unlike other Windows native binaries I've built.

Next...

$ make install

B) The

cc sqlite3.c -o sqlite3

fails to rebuild sqlite3.exe correctly with the -mno-cygwin option. The output follows:

rm -rf tsrc mkdir -p tsrc cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btmutex.c ./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c ./src/date.c ./src/delete.c ./src/expr.c ./src/func.c ./src/hash.c ./src/hash.h ./src/insert.c ./src/journal.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem1.c ./src/mem2.c ./src/mem3.c ./src/mutex.c ./src/mutex_os2.c ./src/mutex_unix.c ./src/mutex_w32.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in ./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h ./src/hash.h ./src/sqliteLimit.h ./src/mutex.h opcodes.h ./src/os.h ./src/os_common.h ./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc cp: warning: source file `./src/btree.h' specified more than once cp: warning: source file `./src/hash.h' specified more than once cp: warning: source file `./src/sqliteInt.h' specified more than once cp: warning: source file `./src/vdbe.h' specified more than once cp: warning: source file `./ext/fts1/fts1.h' specified more than once cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once cp: warning: source file `./src/vdbeInt.h' specified more than once rm tsrc/sqlite.h.in tsrc/parse.y cp parse.c opcodes.c keywordhash.h tsrc tclsh ./tool/mksqlite3c.tcl cc sqlite3.c -o sqlite3 /usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../libcygwin.a(libcmain.o):(.text+0xab): undefined reference to `_WinMain@16' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 $

  ./configure
  make sqlite3.c

  grep sqlite3Fts3Init sqlite3.c

    extern int sqlite3Fts3Init(sqlite3*);
    rc = sqlite3Fts3Init(db);

If you compile sqlite3.c with -DSQLITE_ENABLE_FTS3, then sqlite3Fts3Init is unresolved.

For some reason, sqlite3Fts3Init and fts3.c was not included in the sqlite3.c amalg.

It used to work correctly in 3.5.4.

You have to run ext/fts3/mkfts3amal.tcl

Shouldn't that be: This folder contains source code to the third full-text search [...]

If both fts2 and fts3 are considered to be the "second full-text search extension for SQLite", the README files could maybe explain the situation.

  test cast-3.{14,18,24} fail on freebsd-6.3-PRERELEASE2:
  cast-3.14...^M
  Expected: [9223372036854774784]^M
       Got: [9223372036854773760]^M
  cast-3.18...^M
  Expected: [-9223372036854774784]^M
       Got: [-9223372036854773760]^M
  cast-3.24...^M
  Expected: [9223372036854774784]^M
       Got: [9223372036854773760]^M
  I used tcl8.4 from ports with no threads and here was the config line:
  ../sqlite-3.5.4/configure --prefix=/home/marc/local --with-tcl=/usr/local/lib/tcl8.4/

This was built on an ibm t30 laptop

Diff:
http://megasource.no-ip.org/PTstuff/btree.c.morefix
http://megasource.no-ip.org/PTstuff/btree.c.fix (the original fix, just to silence the warning)

To subscribe to the list, send a message to: <sqlite-users-subscribe@sqlite.org>

For help and a description of available commands, send a message to: <sqlite-users-help@sqlite.org>

This happens because truncation of '-journal' file. I see some ways to reduce fragmentaion: 1. Increase database file size by greater pieces (not by PAGESIZE). 2. SQLite can save '-journal' file in another folder(logical disc). 3. Preallocation of database file(must increase INSERT speed).

  CREATE TABLE foo(a,b);
  INSERT INTO foo (a, b) VALUES (1,2);

SQLite returns inconsistent column names when using the DISTINCT clause:

  SELECT DISTINCT foo.A, foo.B FROM foo;
  foo.A|foo.B
  1|2

  SELECT DISTINCT a, b FROM foo;
  a|b
  1|2

  SELECT DISTINCT * FROM foo;
  a|b
  1|2

  SELECT DISTINCT foo.* FROM foo;
  a|b
  1|2

Compared with SELECT without DISTINCT:

  SELECT foo.A, foo.B FROM foo;
  a|b
  1|2

  SELECT a, b FROM foo;
  a|b
  1|2

  SELECT * FROM foo;
  a|b
  1|2

  SELECT foo.* FROM foo;
  a|b
  1|2

$ grep sqlite3ext.h sqlite3.c #define SQLITE_CORE 1 /* Disable the API redefinition in sqlite3ext.h / /************** Include sqlite3ext.h in the middle of loadext.c **************/ /************** Begin file sqlite3ext.h **************************************/ ** @(#) $Id: sqlite3ext.h,v 1.17 2007/08/31 16:11:36 drh Exp $ /************** End of sqlite3ext.h ******************************************/ #include "sqlite3ext.h" /************** Include sqlite3ext.h in the middle of fts3_tokenizer.c *******/ /************** Begin file sqlite3ext.h **************************************/ ** @(#) $Id: sqlite3ext.h,v 1.17 2007/08/31 16:11:36 drh Exp $ /************** Include sqlite3.h in the middle of sqlite3ext.h **************/ /************** Continuing where we left off in sqlite3ext.h *****************/ /************** End of sqlite3ext.h ******************************************/

Compare to earlier versions such as SQLite 3.5.2:

#define SQLITE_CORE 1 /* Disable the API redefinition in sqlite3ext.h / /************** Include sqlite3ext.h in the middle of loadext.c **************/ /************** Begin file sqlite3ext.h **************************************/ ** @(#) $Id: sqlite3ext.h,v 1.17 2007/08/31 16:11:36 drh Exp $ /************** End of sqlite3ext.h ******************************************/

  #ifndef SQLITE_CORE
    #include "sqlite3ext.h"
    SQLITE_EXTENSION_INIT1
  #endif

You've added fts3.c to the amalgamated source file of SQLite, starting with version 3.5.3.

Now "sqlite3ext.h" is needed by two of the amalgamated files:

• loadext.c: It includes "sqlite3ext.h" and SQLITE_CORE must be defined first.
• fts3.c: It includes "sqlite3ext.h" and I think SQLITE_CORE must be defined first since the FTS3 module is being built into the core of SQLite and not as an extension.

There are actually many issues:

1. I think the amalgamated source file should never include "sqlite3ext.h". Instead its should copy/paste its contents when needed. This is done for loadext.c but not for fts3.c, resulting in the following error when building sqlite3.c without "sqlite3ext.h":
   sqlite3.c:80297:26: error: sqlite3ext.h: No such file or directory
sqlite3.c:80335: error: expected ',', ';', 'asm' or '__attribute__' before 'static'
sqlite3.c:86299: error: expected ';', ',' or ')' before '*' token
   I solved this by making sure SQLITE_CORE is defined when processing the contents of fts3.c, whether SQLITE_OMIT_LOAD_EXTENSION is defined or not, by moving:
   #ifndef SQLITE_OMIT_LOAD_EXTENSION
   after the block starting with:
   #define SQLITE_CORE 1 /* Disable the API redefinition in sqlite3ext.h */
/************** Include sqlite3ext.h in the middle of loadext.c **************/

2. The FTS3 module in the amalgamated source file is built into the resulting library when SQLITE_CORE is not defined, whether SQLITE_ENABLE_FTS3 is defined or not:
   #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_FTS3)

#if defined(SQLITE_ENABLE_FTS3) && !defined(SQLITE_CORE)
# define SQLITE_CORE 1
#endif
[...]
   I'm not sure that's on purpose. Do you want the FTS3 module always built into the resulting SQLite library? If so, I think it would be more readable not to trigger this by defining SQLITE_CORE. Or at the very least it could be triggered by defining SQLITE_ENABLE_FTS3 if SQLITE_CORE is defined in a single place - not all over the FTS3 source files.

Is there any chance this ticket is opened again? Should I open a new ticket instead ?

What compiler are you using? Will it #include a file even when it is not wanted, as in:

  #if 0
  #include "foo.h"
  #endif

or

  #ifdef SOME_UNDEFINED_MACRO
  #include "bar.h"
  #endif

$ gcc -I. -DSQLITE_OMIT_LOAD_EXTENSION shell.c sqlite3.c -o shell -lpthread
sqlite3.c:81710:26: error: sqlite3ext.h: No such file or directory
sqlite3.c:81748: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘static’
sqlite3.c:87815: error: expected ‘;’, ‘,’ or ‘)’ before ‘*’ token

  gcc -I. -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_CORE shell.c sqlite3.c -o shell -lpthread

That's a bug. The workaround is simple, though - always compile with -DSQLITE_CORE

As you've found out, if you choose to build with FTS3 support you do not have to #define SQLITE_CORE on the compile line, as it defines it for you:

  gcc -I. -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_ENABLE_FTS3 shell.c sqlite3.c -o shell -lpthread

How is your remark related to the use case exposed above?

The sqlite bug is explained in detail in the 2007-Dec-30 17:53:42 comment.

The fts3.c source code in the amalgamated source file needs to be compiled with SQLITE_CORE if we want it:

• not to include "sqlite3ext.h",
• and not to build the FTS3 module unless SQLITE_OMIT_LOAD_EXTENSION is defined.

So yes, the solution is to define SQLITE_CORE when building the amalgamated source file:

• either from the command line, in which case this requirement needs to be documented,
• or within the amalgamated source file itself, in which case
  #define SQLITE_CORE 1
  should be added before any reference to _sqlite3ext.h".

Given the database formed by running these statements:

  create table stuff(a,b,c,d);
  insert into stuff values(1,2,3,4);
  create temp view v1 as select random()%100,
    random()%100, random()%1000, random()%10000
     from stuff x, stuff y;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  create index stuff_b on stuff(b);
  create index stuff_c on stuff(c);
  create index stuff_d on stuff(d);
  analyze;

Using sqlite.org's sqlite3-3.5.4.bin, this query takes 47 seconds:

  select c from stuff where a=23 group by c;

while this query takes just 2 seconds:

  select c from stuff where a=23 group by +c;

It is more efficient in this case to do a full table scan instead of using the INDEX on column c.

--- src/where.c 12 Dec 2007 17:42:53 -0000      1.266
+++ src/where.c 23 Dec 2007 22:48:37 -0000
@@ -1514,6 +1514,12 @@ static double bestIndex(
     flags = 0;
   }

+  if( pWC && pWC->nTerm>0 && pOrderBy ){
+    /* Reduce cost if both an ORDER/GROUP BY exists with a WHERE. */
+    cost /= 100; /* A very rough guess. */
+    WHERETRACE(("... WHERE + ORDER BY decreases cost to: %.9g\n", cost));
+  }
+
   /* If the table scan does not satisfy the ORDER BY clause, increase
   ** the cost by NlogN to cover the expense of sorting. */
   if( pOrderBy ){

    /* 1 */  SELECT c FROM stuff WHERE a=23 GROUP BY c;
    /* 2 */  SELECT c FROM stuff WHERE a=23 GROUP BY +c;

Query 1 runs in about 40 seconds and query 2 in about 1.5 seconds on my macbook. But with the patch, both queries run in about 1.5 seconds. Fair enough. But now consider these two queries:

    /* 3 */  SELECT c FROM stuff WHERE a!=23 GROUP BY c;
    /* 4 */  SELECT c FROM stuff WHERE a!=23 GROUP BY +c;

In this case, query 3 runs in 42 seconds on an unpatched version of 3.5.4 and query 4 runs in about 109 seconds. So in cases where the WHERE clause is not particularly selective, the first version is faster than the second by a good margin. On a patched version of 3.5.4, both queries 3 and 4 run in about 110 seconds.

So it seems to me that the patch is robbing Peter to pay Paul. It makes ORDER BY queries with very selective WHERE clauses run faster but at the expense of making queries with unselective WHERE clauses running slower.

But notice this: in the current (unpatched) implementation, the programmer at least has the ability to select a different algorithm by the judicious placement of a "+" sign. After the patch, this is no longer possible. The patch forces the second algorithm to be used in all cases, even cases where it is slower.

It seems to me to be better to leave things as they are since the current approach at least allows the programmer to override SQLite's algorithm selection if SQLite chooses incorrectly.

The only way, it seems to me, to automatically choose the correct algorithm is to devise some test that will determine (in advance) whether or not the WHERE clause weeds out many or few rows from the result set. I'm thinking that determination is going to be very hard (or impossible) to do without first doing a full table scan.

(Side note: the query timings difference is less pronounced if you use PRAGMA temp_store=memory, in which case query 3 running on an unpatched 3.5.4 takes just 50% more time to run than query 4 on my machine.)

But you raise a good point in that if there's a wrong guess in the selectivity bias it would be nice to be able to manually override it.

How much do you hate this type of syntax that some other databases use?

  select c from stuff where a!=23 group by /*+stuff_c*/ c;

SQLite does not currently offer a way to pick a specific index. I think it would be quite useful.

  create table sqlite_stat2(
    where_clause_md5 BLOB primary key,
    where_clause     TEXT,
    rows_examined    INT,
    rows_true        INT
  );

where the last 2 columns are cumulative for each query. The statistics option could be enabled/disabled via a PRAGMA sqlite_collect_statistics.

The where_clause column could be a string generated fairly easily from the walking the parse tree of the resolved Select statement's pWhere. This way the where_clause is normalized and a single query with many subselects could generate more than 1 where_clause, and different queries that happen to use the same normalized where clause would update the same entry in the stat2 table. where_clause normalization would strip off aliases and only refer to the original table and column names.

For example the 2 queries below:

  -- CREATE TABLE t1(a, b);
  -- CREATE TABLE t2(b, c);
  SELECT t1.a*c as AC, t2.b-a as BA FROM t1, t2 WHERE AC>BA;
  SELECT *, t1.a Foo FROM t2, t1 WHERE Foo*c > t2.b - t1.a;

would generate the same normalized where_clause string "(T1.A*T2.C)>(T2.B-T1.A)". The table information is already encoded within it.

The generated VDBE code would have to generate Mem counters that would be incremented by each WHERE test, and lazily updated at the end of transactions or periodically written to the stat2 table to minimize disk use, as this information is not critical.

One could also manually set the stat2 table with statistical values they would like their queries to use even if PRAGMA sqlite_collect_statistics=off;

Any time the schema is changed, the entire sqlite_stat2 table would be cleared.

When I run a query on all records of one table I got the a.m. error message and no results. I was able to query all details by RowID up to and above a small range of only 105 records (out of 345767 ). No problem below and none above. Then I renamed the table, created another table and "insert into .. select from .." one into the other in two steps. ("where RowID between .. and .. or RowID between .. and .." popped up the error message again but running two queries one after the other worked fine.

That's my workaround.

It is a pitty that I cannot send you an example but I do not know how to shrink the database to only remain with the route cause of trouble. I dropped the other two tables and deleted all records before and after the malformed part but now I cannot vacuum without getting again that message.

1. How may I got this malformed records into my database? How to avoid repetition?
2. How can I eliminate a malformed part out of the database without having to recreate almost everything from scrap?

As soon as the malformed records are touched every command stops.

Isn't there a possibility to put such records into quarantine ?! This would really help to isolate problems and would hopefully give me a chance to send you the damaged records for your analysis to find out what may have happened. The isolation of records may be related to "check integrity" of the database. Another good idea may be to give sqlite.exe a chance to extract isolated part under quaratine from one into another database. - I keep my damaged database for such a case for a while, expecting such a command with the next release.

• http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption
• http://www.sqlite.org/atomiccommit.html
• http://www.sqlite.org/lockingv3.html (Section 6.0)

To restore data as much as you can from a corrupt database file, do this:

   sqlite3 corrupt.db .dump | sqlite3 restored.db

This ticket reads more like a support request than a bug report. Or perhaps it is a feature request - requesting that corruption be "quarantined". Quarantining bad records merely masks errors and gives no new capabilities so that is not something we are interested in doing. For technical support see

• http://www.sqlite.org/support.html

  SELECT h.id, h.url, h.title, h.rev_host, h.visit_count,
  (SELECT MAX(visit_date) FROM moz_historyvisits WHERE place_id = h.id
  AND visit_type NOT IN(0,4)),
  f.url, null, null
  FROM moz_places h
  LEFT OUTER JOIN moz_favicons f ON h.favicon_id = f.id
  WHERE h.id IN
  (SELECT DISTINCT p.id
          FROM moz_places p
          JOIN moz_historyvisits ON place_id = p.id
          WHERE hidden <> 1 AND visit_type NOT IN (0,4)
          ORDER BY visit_date DESC
          LIMIT 10)
  ORDER BY 6 DESC;

  SELECT h.id, h.url, h.title, h.rev_host, h.visit_count,
  (SELECT visit_date FROM moz_historyvisits WHERE place_id = h.id
   AND visit_type NOT IN(0,4) ORDER BY visit_date DESC LIMIT 1),
  f.url, null, null
  FROM moz_places h
  LEFT OUTER JOIN moz_favicons f ON h.favicon_id = f.id
  WHERE h.id IN
  (SELECT DISTINCT p.id
          FROM moz_places p
          JOIN moz_historyvisits ON place_id = p.id
          WHERE hidden <> 1 AND visit_type NOT IN (0,4)
          ORDER BY visit_date DESC
          LIMIT 10)
  ORDER BY 6 DESC;

Will this be fixed to use the index with max or is that the expected behaviour?

The current optimizer recognizes queries of the form:

    SELECT min(x) FROM table;
    SELECT max(x) FROM table;

And it causes special VDBE code to be generated for these cases. The presence of a WHERE clause defeats the current optimization. We need to modify the optimizer to recognize queries of the form:

    SELECT min(x) FROM table WHERE expr

And convert them into:

    SELECT x FROM table WHERE expr AND x NOT NULL
     ORDER BY x LIMIT 1

But we only want to do this optimization if the ORDER BY can be evaluated using an index. If we have to accumulate the entire result set, sort it, then take the largest entry, that is more work than just keeping track of the largest entry as we loop through the result set.

Notice the addition of the "x NOT NULL" term in the WHERE clause. This is critical to preserving correct semantics. The query optimizer currently does not know how to deal with a NOT NULL. It just evaluates all rows, tests them individually for NULL, and throws out those that match. This would work in most cases, but would slow down in a min() where there were many NULL entries. The current implemention of the min() optimization knows to skip over the NULL entries in a single operation. The WHERE generator part of the optimizer will need to be enhanced to recognize NOT NULL constraints and skip over them.

All of this is a substantial change. The min()/max() optimization function will be rewritten from scratch. Significant and non-trivial modifications will need to be made to the code that looks for indices and generates the WHERE clause constraints. There are many opportunities to make coding mistakes, so we will need to spend a lot of time in testing before we put these changes into production.

So, we will be working the problem. But do not expect an overnight fix.

I suppose that while we are redesigning the min/max optimizer, we might as well also fix it so that

   SELECT arbitrary_expr(max(x)) FROM table WHERE expr;

gets converted into

   SELECT arbitrary_expr(x) FROM table WHERE expr AND x NOT NULL
    ORDER BY x DESC LIMIT 1;

  SELECT min(x) from table WHERE expr

being converted to:

  SELECT x FROM table WHERE expr AND x NOT NULL ORDER BY x LIMIT 1

There's no need for the "x NOT NULL" condition considering NULL is returned by min() (or max for that matter) when no rows match.

  sqlite> .nullvalue <null>

  sqlite> select min(a) from (select 123 as a) where a=7;
  <null>

  sqlite> select min(a) from (select NULL as a) where a=7;
  <null>

Even with this in mind, you can see that the rewritten query still does not return the same result in this case:

  sqlite> select a from (select 123 as a) where a=7 order by 1 limit 1;
  -- no rows returned

Logic would have to be added to return a NULL row in the event the WHERE clause matches no rows.

  SELECT min(x) from table WHERE expr

If column x or the WHERE expr can make use of an index, then the min query should be converted to:

  SELECT x from (
    SELECT x FROM table WHERE expr
      ORDER BY x LIMIT 1
  ) UNION ALL SELECT NULL LIMIT 1

which ought to cover all the corner cases, even if the WHERE matches no rows.

Given:

  create table stuff(a,b,c,d);
  insert into stuff values(1,2,3,4);
  create temp view v1 as select random()%100,
    random()%100, random()%1000, random()%10000
     from stuff x, stuff y;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  create index stuff_b on stuff(b);
  create index stuff_c on stuff(c);
  create index stuff_d on stuff(d);
  analyze;

In the following example the existing min() implementation which uses a full table scan:

  sqlite> explain query plan select min(c) from stuff where a=12345;
  0|0|TABLE stuff

  sqlite> select min(c) from stuff where a=12345;
  (null)
  CPU Time: user 1.388087 sys 0.216014

is faster than performing the select min->order by/limit transform which uses an index:

  sqlite> explain query plan select c from stuff where a=12345 order by 1 limit 1;
  0|0|TABLE stuff WITH INDEX stuff_c ORDER BY

  sqlite> explain query plan select c from (select c from stuff where a=12345 order by c limit 1) union all select null limit 1;
  0|0|TABLE stuff WITH INDEX stuff_c ORDER BY
  0|0|TABLE  AS sqlite_subquery_82F83F8_
  CPU Time: user 0.000000 sys 0.000000

  sqlite> select c from (select c from stuff where a=12345 order by c limit 1) union all select null limit 1;
  (null)
  CPU Time: user 15.880993 sys 15.400962

Note that a=12345 is always false in this data set.

Had a=23 been used instead, we can see that the transformed select would be much faster than the original min() select:

  sqlite> select min(c) from stuff where a=23;
  -999
  CPU Time: user 1.552097 sys 0.148009

  sqlite> select c from (select c from stuff where a=23 order by c limit 1) union all select null limit 1;
  -999
  CPU Time: user 0.004001 sys 0.000000

I don't think WHERE clause cost estimation can be done accurately without using a statistical method based on historical WHERE clause hit percentages.

  -- sqlite 3.5.4
  sqlite> select min(c) from stuff where a=12345;
  (null)
  CPU Time: user 1.532095 sys 0.132008

  -- as of Check-in [4687]
  sqlite> select min(c) from stuff where a=12345;
  (null)
  CPU Time: user 23.041440 sys 16.369023

Here's a sample of the new bug:

.mode csv select 'a=1,234', 'b=5';

gives: a=1,234,b=5

but should give: "a=1,234",b=5

I'm using sqlite 3.4.0 on Mac OS X 10.5.1.

CSV (comma separated values) output should show value1,value2,value3 etc. If a value contains a comma, then csv should encapsulate that value in quotes: "value,1",value2,value3. And if a value contains a quote, csv should also encapsulate that value in quotes and show the quote as a double quote, eg "value,1","value ""2""",value3

Sqlite3 seems to ignore the need for quotes in a value that contains an equals "=". I don't know if it also fails with other characters.

Needless to say, this messes up the output considerably, placing output in the wrong column.

"Supported unary prefix operators are these:

- + ! ~ NOT"

There is no ! operator in sqlite

I am sorry if this problem already has its own ticket, or if I missed some configuration in sqlite3/dump, but I could not find any such.

Snippet from .dump output:

sqlite3 test.db3 .dump | od  --address-radix=x --format=x1a

000060 0d 0a 49 4e 53 45 52 54 20 49 4e 54 4f 20 22 74
        cr  nl   I   N   S   E   R   T  sp   I   N   T   O  sp   "   t
000070 65 73 74 31 22 20 56 41 4c 55 45 53 28 31 2c 27
         e   s   t   1   "  sp   V   A   L   U   E   S   (   1   ,   '
000080 61 6c 0d 0a 70 68 61 27 29 3b 0d 0a 43 4f 4d 4d
         a   l  cr  nl   p   h   a   '   )   ;  cr  nl   C   O   M   M
000090 49 54 3b 0d 0a
         I   T   ;  cr  nl

Perhaps the extra control character was accidently loaded into the database?

Unfortunately, neither of these options works particularly well when trying to create a resource file so that the DLL can display the proper version information within Windows. I've tried many different ways of disassembling the integer version number, but limitations in the resource compiler unfortunately prevent them from working.

As a result, I've been forced for the time being to define SQLITE_VERSION_MAJOR, SQLITE_VERSION_MINOR, and SQLITE_VERSION_PATCH with manually-given values of x, y, and z respectively in order to accomplish this task. It would be really nice if these could be generated automatically for sqlite.h when running configure in the same way that VERSION and VERSION_NUMBER are so that setting the values manually wouldn't be required for the future.

Would you be willing to do that? Thanks in advance.

#
## extrvers.awk
## Extract verison parts from sqlite3.h
#
## Usage:
#  %GNU_AWK% -f extrvers.awk sqlite3.h >sqlite3.h.new
# 	rm / del sqlite3.h
# 	mv / ren sqlite3.h.new sqlite3.h
#
#
## Ignore any previous defines
/^#define SQLITE_VERSION_(MAJOR|MINOR|PATCH)/{
	next
}
## generate extra #define MAJOR/MINOR/PATH lines
/^#define[[:blank:]]+SQLITE_VERSION[[:blank:]]/{
	split(substr($3,2,length($3) - 2),tmp,".")
	print "#define SQLITE_VERSION_MAJOR " tmp[1]
	print "#define SQLITE_VERSION_MINOR " tmp[2]
	print "#define SQLITE_VERSION_PATCH " tmp[3]
}
## Repeat all other lines untouched
{
	print
}

Secondly, if you are working for Mozilla, you will get much faster service if you identify yourself as such.

To whoever posted it, you can contact me at ryanvm [at] gmail [dot] com. Thanks again for help!

# Copyright (C) 2007 by Kees Nuyt, Rotterdam, Netherlands
# The author of this code dedicates any and all copyright
# interest in this code to the public domain. I make this
# dedication for the benefit of the public at large and
# to the detriment of my heirs and successors. I intend
# this dedication to be an overt act of relinquishment in
# perpetuity of all present and future rights to this
# code under copyright law.
#

Cheers! "Kees Nuyt" <k [dot] nuyt [at] zonnet [dot] nl>

You can see the final script here: http://mxr.mozilla.org/mozilla/source/db/sqlite3/src/sqlite-version.py

At this point, you can close the bug if you want. I'll leave that up to you to decide.

In other words, why should we fix this? What problem is it causing?

It is considered to be be good practice to respect user's LDFLAGS. A user might want to have all executables and libraries built with identical LDFLAGS.

> What LDFLAGS settings might a user want to carry through into lemon?

A user might have LDFLAGS="-Wl,-O1,--hash-style=gnu,--sort-common"

You can read http://lwn.net/Articles/192082/.

Users can also use some other flags.

> In other words, why should we fix this? What problem is it causing?

It slightly increases the size of lemon executable and it slightly decreases performance.

Is it ever possible that the lack of LDFLAGS support might result in lemon getting the wrong answer?

> SQLite build process. It is not a deliverable. If it runs a

> little slower or uses a little more memory, nobody cares.

CFLAGS are respected when lemon is being built, so for consistency LDFLAGS also should be respected.

(The comment above was not created by me.)

  addr1 = sqlite3VdbeCurrentAddr(v);
  sqlite3ExprCode(pParse, pExpr);
  addr2 = sqlite3VdbeCurrentAddr(v);
  if( addr2>addr1+1 || sqlite3VdbeGetOp(v, addr1)->opcode==OP_Function ){

sqlite3VdbeGetOp() can return a NULL pointer (if there have been allocation failures AND (0 <= addr1 < v->nOp) does not hold).

This can occur if there are memory allocation failures which prevent sqlite3ExprCode() from adding any vdbe operations (so addr2 is the same as addr1 which is equal to v->nOp).

The solution is probably to include a test for allocation failures in the if condition, ie:

  addr1 = sqlite3VdbeCurrentAddr(v);
  sqlite3ExprCode(pParse, pExpr);
  addr2 = sqlite3VdbeCurrentAddr(v);
  if( !v->db->mallocFailed && (addr2>addr1+1 ||
                               sqlite3VdbeGetOp(v, addr1)->opcode==OP_Function ) ){

This is with .mode list and separator , But behaves similarly for .mode csv

Also if one outputs a table with NULL values to a file, then re-imports that file, again .import does not recognise the values as NULL, but as "NULL".

Everything here also applies to empty strings in files, e.g. instead of "NULL" using nothing...

This is a showstopper for us since we want to import a large amount of data with many tables containing NULL values. I can't see any valid reason for .import not to recognise the same syntax as the command line.

Note that something like:

sqlite3 my.db insert into MY_TABLE values (1,"foo","bar",NULL)

..works fine. It is just .import that appears to be broken.

The workaround is to simply insert a unique string where ever you want a NULL value, and then run an update that replaces those strings with real NULL values. If you inserted the string 'NULL' then do this after the .import

update t set field = null where field = 'NULL';

You will have to repeat this for each field in your table that might contain the 'NULL' string.

Recently (see the DeviceSQL thread) it got really bad. Would moderation be unacceptable during these periods of time where people feel the need to protect their ego's?

The sqlite mailing list is primarily about sqlite (well, and lemon), not a marketing vector for other products? Surely they have their own lists and resources for that?

** The sqlite3_reset() function is called to reset a ** [sqlite3_stmt | compiled SQL statement] object. ** back to it's initial state, ready to be re-executed.

Should be:

** The sqlite3_reset() function is called to reset a ** [sqlite3_stmt | compiled SQL statement] object ** back to its initial state, ready to be re-executed.

If in doubt, see http://www.bristol.ac.uk/arts/skills/grammar/grammar_tutorial/page_13.htm

Using the amalgamation can also mask missing #include files from .c files which cause compile errors when compiling in the "traditional" .c->.o manner. Such missing header files in certain .c source files may work in the amalgamation due to some other pre-pended .c file that just happened to have included the header file.

Also, on some platforms the C compiler cannot compile the sqlite3.c amalgamation with -g (debug) because it is too big.

Try running configure in a directory separate from the source tree:

   ../sqlite/configure; make testfixture

  make clean
  make distclean
  ./configure --disable-shared
  make test

But I get the same errors.

So I run the old tree and the new tree's alter.c through gcc -E and here's the difference:

--- ../../sqlite/alter.old      2007-12-13 11:04:15.000000000 -0500
+++ alter.new   2007-12-13 11:04:25.000000000 -0500
@@ -599,7 +599,7 @@
 HashElem *sqlite3HashFindElem(const Hash*, const void *pKey, int nKey);
 void sqlite3HashClear(Hash*);
 # 154 "./src/sqliteInt.h" 2
-# 1 "./src/parse.h" 1
+# 1 "./parse.h" 1
 # 155 "./src/sqliteInt.h" 2
 # 1 "/usr/include/stdio.h" 1 3 4
 # 28 "/usr/include/stdio.h" 3 4
@@ -3532,7 +3532,7 @@
       do {
         zCsr += len;
         len = sqlite3GetToken(zCsr, &token);
-      } while( token==TK_SPACE );
+      } while( token==145 );
       ((void) (0));
     } while( token!=19 && token!=115 );

@@ -3582,7 +3582,7 @@
       do {
         zCsr += len;
         len = sqlite3GetToken(zCsr, &token);
-      }while( token==TK_SPACE );
+      }while( token==145 );
       ((void) (0));
 # 147 "./src/alter.c"
       dist++;

If I remove src/parse.h as is well and "make test" now builds.

Documented in this ticket in case someone else happens to have the same problem in the future.

with "Undefined symbol 'OP_StackDepth'" message.

opcodes.h and opcodes.c havn't 'OP_StackDepth' definition currently.

Sorry for my short English.

Try running "make clean" before rebuilding. If that fails, manually remove opcodes.c and opcodes.h from your build directory.

  article ::= blocks FIN.
  blocks ::= block.
  blocks ::= blocks block.
  block ::= NEWLINE.
  block ::= stanza.
  block ::= heading.
  block ::= stanza heading.
  heading ::= HEADING_START text HEADING_END.
  stanza ::= text.
  stanza ::= stanza NEWLINE text.
  text ::= textpiece.
  text ::= text textpiece.
  textpiece ::= TEXT.
  textpiece ::= LINK.

==11727== Conditional jump or move depends on uninitialised value(s)
==11727==    at 0x804ADBB: CompressTables (lemon.c:3947)
==11727==    by 0x8051A2C: main (lemon.c:1482)

3925 void CompressTables(lemp)
3926 struct lemon *lemp;
3927 {
3928   struct state *stp;
3929   struct action *ap, *ap2;
3930   struct rule *rp, *rp2, *rbest;
3931   int nbest, n;
3932   int i;
3933   int usesWildcard;
3934
3935   for(i=0; i<lemp->nstate; i++){
3936     stp = lemp->sorted[i];
3937     nbest = 0;
3938     rbest = 0;
3939     usesWildcard = 0;
3940
3941     for(ap=stp->ap; ap; ap=ap->next){
3942       if( ap->type==SHIFT && ap->sp==lemp->wildcard ){
3943         usesWildcard = 1;
3944       }
3945       if( ap->type!=REDUCE ) continue;
3946       rp = ap->x.rp;
3947       if( rp->lhsStart ) continue;

  ==12904== Conditional jump or move depends on uninitialised value(s)
  ==12904==    at 0x80523AF: CompressTables (lemon.c:3947)
  ==12904==    by 0x804B015: main (lemon.c:1482)

--- tool/lemon.c        5 Oct 2007 16:16:36 -0000       1.51
+++ tool/lemon.c        12 Dec 2007 19:02:37 -0000
@@ -2100,6 +2100,7 @@ to follow the previous rule.");
           psp->prevrule = 0;
        }else{
           int i;
+          rp->lhsStart = 0;
           rp->ruleline = psp->tokenlineno;
           rp->rhs = (struct symbol**)&rp[1];
           rp->rhsalias = (char**)&(rp->rhs[psp->nrhs]);

If the lemon is built with the following patch and your run parse.y against it, lemon will generate a parse.c that is 275868 bytes in size:

diff -u -3 -p -r1.51 lemon.c
--- tool/lemon.c        5 Oct 2007 16:16:36 -0000       1.51
+++ tool/lemon.c        13 Dec 2007 23:37:39 -0000
@@ -2100,6 +2100,7 @@ to follow the previous rule.");
           psp->prevrule = 0;
        }else{
           int i;
+          rp->lhsStart = 0xffff; // *** change to zero and rerun
           rp->ruleline = psp->tokenlineno;
           rp->rhs = (struct symbol**)&rp[1];
           rp->rhsalias = (char**)&(rp->rhs[psp->nrhs]);

I generate a database using your schema and SQLite version 3.1.3 and it works just fine with SQLite version 3.5.3. Unable to recreate the problem...

This bug appears to be in all prior versions of SQLite 3.

The original problem description follows:

sql "create table products (code text primary key, former_code text, typy text, name text, warehouse text)" sql "create table transform (code text, name text, new_code text, former_code text, type text)"

no indices (there are in newer version of my code, but simpler the case the better)

And now sql code, which corrupts my data (contains a bit of REBOL code):

sql trim/lines {
 update or replace products
   set code = (select new_code from transform where transform.code=products.code),
        type = (select type from transform where transform.code=products.code)
 where exists
  (select code from transform where transform.code=products.code)
}

So - what you suggest now? Is that a bug? Or am I behaving really badly to my data? :-)

Tried with 3.5.2, 3.5.3, Windows Vista 32bit ....

PS: This text is from my original post to ml - tried to subscribe to ml two times, no luck receiving any message, so posting directly to bugbase. Sorry if the bug is already known ...

Here's a screenshot:

corrupted data?

Cheers, Petr

This is likely an encoding problem. Are you inserting UTF-8 text into the database? And handling the returned data as UTF-8?

In products table I have product numbers starting with "9", which are going to be translated into other numbers:

9xxxx.xx ---> 1xxxx.xx

9xxxx.xxA --> 1xxxx.xxA

9xxxx.xxBZ--> 9xxxx.xx

I import all data from Excel .csv files. Encoding should be OK imo, as data live in one file. However, I noticed "bug" in my logic. Simply put, to make my life easier, I imported 9xxxx.xxBZ codes into my transform table, thinking that when those codes are at the end of the table, those are OK. But - my update clause (posted in my first request), does not "join data" that safe way, so actually what happens is - if the clause reaches 9xxxx.xxBZ code, it updates it to 9xxxx.xx code, but this code is then once again translated to 1xxxx.xx later in the process. If I don't import 9xxxx.xxBZ codes to the end of the table, my data remain being OK.

Where can I send my database? I don't want it being public. Thanks a lot ...

Petr

corruption data example

Just open the database and apply query as stored in query.txt

  rowid 156 missing from index sqlite_autoindex_products_1
  rowid 159 missing from index sqlite_autoindex_products_1
  rowid 160 missing from index sqlite_autoindex_products_1
  rowid 162 missing from index sqlite_autoindex_products_1
  rowid 163 missing from index sqlite_autoindex_products_1

Tested with CVS [4603] .

One reason this feature (renaming views) is not a high priority is because a view can be dropped and recreated with a different name efficiently. This was not the case with tables.

  sqlite/test/tkt2822.test -> 1.1

  do_test tkt2820-1.1 {
  do_test tkt2820-1.3 {
  do_test tkt2820-1.3 {

This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):

   if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
-                    | SQLITE_OPEN_SUBJOURNAL) ){
+                    | SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_DELETEONCLOSE) ){

    ls -U | grep etliqs_ | while read i; do rm $i; done

That will have to run for 20 minutes or so to clear out all of the dead files.

This problem is serious enough to warrant a new release once we get it fixed.

I don't see these temp file on UNIX - which OS is affected by this bug and under what circumstance?

  find /var/tmp -type f -iname "etliqs_*" -print0 | xargs -r0 rm

(yes, you can simplify this a whole lot if you make some assumptions but why bother be unsafe?)

As it stands now, SQLite does not compiled with SQLITE_OMIT_SUBQUERY defined.

This ensures that any system messages that expect arguments do not try to grab the argument from some random memory location.

ref: http://blogs.msdn.com/oldnewthing/archive/2007/11/28/6564257.aspx

FormatMessageA returns the text in the local ANSI codepage. FormatMessageW should be used on NT systems, and either result should be converted to the SQLite UTF-8 default.

http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/os_win.c&v=1.118

Search for FormatMessageA (only 1 instance)

  -    FORMAT_MESSAGE_FROM_SYSTEM,
  +    FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS,

No breakage, ensures that no crashes with some messages (e.g. filesystem errors). The encoding issue should be addressed separately.

static void winDlError(sqlite3_vfs *pVfs, int nBuf, char *zBufOut){
  int error = GetLastError();
#if OS_WINCE
  if( error>0x7FFFFFF ){
    sqlite3_snprintf(nBuf, zBufOut, "OsError 0x%x", error);
  }else{
    sqlite3_snprintf(nBuf, zBufOut, "OsError %d", error);
  }
#else
  if( isNT() ){
    LPWSTR zWinTemp = NULL;
    DWORD dwLen = FormatMessageW(
      FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS,
      NULL,
      error,
      0,
      (LPWSTR) &zWinTemp,
      0,
      0
    );
    if (dwLen > 0) {
      char * zOut = unicodeToUtf8(zWinTemp);
      LocalFree(zWinTemp);
      sqlite3_snprintf(nBuf, zBufOut, "%s", zOut);
      free(zOut);
    }
  }else{
    LPSTR zWinTemp = NULL;
    DWORD dwLen = FormatMessageA(
      FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS,
      NULL,
      error,
      0,
      (LPSTR) &zWinTemp,
      0,
      0
    );
    if (dwLen > 0) {
      char * zOut = mbcsToUtf8(zWinTemp);
      LocalFree(zWinTemp);
      sqlite3_snprintf(nBuf, zBufOut, "%s", zOut);
      free(zOut);
    }
  }
#endif
}

Code:

int WINAPI WinMain(HINSTANCE h1, HINSTANCE h2, LPSTR lpCMD, int nSCMD) {

sqlite3* db; sqlite3_stmt * stm;

sqlite3_enable_shared_cache(1); sqlite3_open("db", &db); sqlite3_prepare_v2(db, "ATTACH DATABASE db2 AS d", 24, &stm, 0); sqlite3_step(stm); }

The problem:

the sqlite3_step(stm) execute for an infinite time.

MA

  #include "sqlite3.h"

  int main (int argc, char **argv){
    sqlite3* db;
    sqlite3_stmt *stm;

    sqlite3_enable_shared_cache(1);
    sqlite3_open("db", &db);
    sqlite3_prepare_v2(db, "ATTACH DATABASE db2 AS d", 24, &stm, 0);
    sqlite3_step(stm);

    return 0;
  }

And the sqlite3_step() did not block for any amount of time. Can you confirm that the code above produces the bug in your environment?

Thanks.

MA

query not return values.

Perhaps you have some whitespace on one side of the '43043553' value in the database. Whitespace is significant to SQLite.

1. If an ORDER BY term is a constant integer k then sort by the k-th column of the result set.

2. If an ORDER BY term is a simple identifer (like "x", not "x.y" and not "x.y.z") and if there if the k-th column uses that same identifer as an AS alias, the sort by the k-th column.

3. Otherwise, evaluate the expression which is in the ORDER BY term and sort by the resulting value.

For a compound query, the expression in step (3) must exactly match one of the result columns. Also, the three steps are attempted first on the left-most SELECT. If there is a match, the process stops there. If no match is found, the next SELECT to the right is tried. This repeats as necessary until a match is found or until you run out of SELECT statement in which case there is an error.

This algorithm differs from all prior versions of SQLite (1.0.0 through 3.5.3) by the addition of step (2). Adding step (2) brings SQLite much closer to the SQL standard. I believe that SQLite is now a superset of the SQL standard. In a compound query, SQL only looks at the left-most SELECT and does not fail over to SELECT statements to the right looking for a match. The difference in SQLite can be considered an extension.

The revised algorithm is mostly compatible with the way SQLite has always operated before. But there are a few obscure corner cases where there is a difference. An example of the difference is the following:

    CREATE TABLE a(x,y);
    INSERT INTO a VALUES(1,8);
    INSERT INTO a VALUES(9,2);

    SELECT x AS y FROM a ORDER BY y;

In older versions of SQLite, the SELECT statement above would return 9, 1 since the ORDER BY term evaluated to the expression a.y by rule (3) In the next release, because of the addition of rule (2) above, the result will be 1, 9.

Starting from version 3.4.2 I receive errors with queries like:

  SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field;

or even

  SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field;

error is:

  ORDER BY term number 1 does not match any result column

Tables are created by:

  CREATE TABLE a (field);
  CREATE TABLE b (field);

Please note that the above queries worked fine with sqlite 3.2.x or 3.3.x.

it's odd that this one doesn't work as well:

  create table t1(a);
  create table t2(b);

  select t1.a from t1 union all select t2.b from t2 order by a;

  SQL error: ORDER BY term number 1 does not match any result column

It appears as though the /src/select.c (Line1499) changed from:

   if( iCol<0 && mustComplete ){

to:

    }else if( mustComplete ){

in version 1.336 of this file - http://www.sqlite.org/cvstrac/filediff?f=sqlite/src/select.c&v1=1.335&v2=1.336

And this change results in this bug.

  create table x1(a INT, b INT, c INT);
  insert into x1 values(1, 2, 3);
  create table x2(a INT, b INT, c INT);
  insert into x2 values(9, 0, 4);
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;

SQLite version 3.5.3

  sqlite> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
  x1.b|a
  9|0
  2|1

versus:

  mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
  +------+------+
  | b    | a    |
  +------+------+
  |    2 |    1 |
  |    9 |    0 |
  +------+------+

Also, for this SQL:

  create table x1(a INT, b INT, c INT);
  insert into x1 values(1, 2, 3);
  create table g2(x INT, y INT, z INT);
  insert into g2 values(9, 0, 4);

sqlite 3.5.3 produces:

  sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y;
  x1.b|a
  9|0
  2|1

compared to an error on other databases that only use the first SELECT to determine ORDER BY/GROUP BY column names:

  mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y;
  ERROR 1054 (42S22): Unknown column 'y' in 'order clause'

  CREATE TABLE foo(b);

These are fine:

  SELECT 3 b union select b from foo order by b;
  3

  SELECT b from foo union SELECT 3 b order by b;
  3

These have issues:

  SELECT b from foo union SELECT 3 b order by +b;
  SQL error: ORDER BY term number 1 does not match any result column

  SELECT 3 b union select b from foo order by +b;
  SQL error: ORDER BY term number 1 does not match any result column

  CREATE TABLE t1(b);
  INSERT INTO t1 VALUES(-3);
  INSERT INTO t1 VALUES(2);

  -- works, of course
  select b from t1 order by b*b;
  2
  -3

  -- expression does not work in compound SELECT
  select b from t1 union all select b from t1 order by b*b;
  SQL error: ORDER BY term number 1 does not match any result column

  -- an alias does not help

  select b as b from t1 union all select b from t1 order by b*b;
  SQL error: ORDER BY term number 1 does not match any result column

  select b as b from t1 union all select b as b from t1 order by b*b;
  SQL error: ORDER BY term number 1 does not match any result column

SQLite version 3.5.3
Enter ".help" for instructions
sqlite> create table foo(a);
sqlite> insert into foo values(1);
sqlite> .header on

sqlite> select foo.a from foo;
a
1

sqlite> select foo.a from foo union all select foo.a from foo;
a
1
1

sqlite> select foo.a from foo union all select foo.a from foo order by 1;
foo.a    <=====
1
1

sqlite> select foo.a from foo union all select foo.a from foo group by 1;
a
1
1

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg29790.html

http://marc.info/?l=sqlite-users&m=119708950929249&q=p3

  create table t1(a INT, b INT, c INT);
  insert into t1 values(1, 2, 4);
  insert into t1 values(2, -1000, 5);
  select a, a+b AS c from t1 order by c;

sqlite 3.5.3:

  a|c
  1|3
  2|-998

mysql and postgres:

   a |  c
  ---+------
   2 | -998
   1 |    3

sqlite/test/tkt2822.test 1.2 -> 1.3

  -    SELECT a+1, b+1 FROM t1 UNION ALL SELECT a, c FROM t2 ORDER BY a+1;
  +    SELECT a, b, c FROM t1 UNION ALL SELECT a AS x, b, c FROM t2 ORDER BY x;

It was a useful extension.

From latest CVS:

  sqlite> select 3 as A, 6 as B union select 1 as B, 5 as A order by A;
  1|5
  3|6

  sqlite> select 3 as A, 6 as B union select 1 as B, 5 as A order by +A;
  SQL error: 1st ORDER BY term does not match any column in the result set

  sqlite> select 3 as A, 6 as B union select 1 as B, 5 as A order by A+1;
  SQL error: 1st ORDER BY term does not match any column in the result set

  sqlite> select 3 as A, 6 as B union select 1 as B, 5 as A order by 9*A-A*A;
  SQL error: 1st ORDER BY term does not match any column in the result set

  select * from (select 3 as A, 6 as B union select 1 as B, 5 as A) order by 9*A-A*A;
  1|5
  3|6

In a simple SELECT, if an ORDER BY term does not exactly match a result set expression, we can add an additional "virtual" result column to contain the ORDER BY term. But we cannot do that in a compound SELECT because we would have to add such "virtual" columns to all SELECTs in the compound and the ORDER BY expression typically only makes sense for one of them.

  mysql> select 3 as A, 6 as B union select 1 as B, 5 as A order by -A*A;
  +---+---+
  | A | B |
  +---+---+
  | 3 | 6 |
  | 1 | 5 |
  +---+---+

It's a useful extension, but not a show stopper. One could manually do to the transformation identified above to make the compound query a subquery and move the ORDER BY to the parent. I don't know if it is any less efficient.

You'll almost certainly find log_n is going to be fairly fast (especially for large n.)

I personally would prefer some sort of 'virtual' index though, that could be a hash or actually from a user-supplied function so that I can index large blobs by some function (i.e. a hash).

And yes, this would be an incompatible file-format change and it's not clear how to update an index when the function isn't loaded (i.e. db reopened with that function.) Perhaps mark the index as 'stale' and ignore it until the function loads then you can do the updates.

Of course this starts to get quite complicated.

database table is locked

In sqlite rolback is called (I'v checked in older version), but in test.db there is no table 't'.

Tested on win2k.

   sqlite>  select -9223372036854775808>=-9223372036854775808;
   1

This is the correct answer.

   sqlite>  select -9223372036854775809>=-9223372036854775808;
   1

-9223372036854775809 is not an 8-byte integer. The smallest possible 8-byte integer is -9223372036854775808. The -9..809 value is a 64-bit IEEE floating point number: -9.22337203685488e+18. In order to perform the comparison, SQLite converts the number on the right to floating point value. Due to the limited precision of floating point numbers, both values are the same and so the values compare equal to one another. Hence the result shown above is correct.

   sqlite>  select -9223372036854875808>=-9223372036854775808;
   0

This is the correct answer.

No bugs seen here.

  CREATE TEMP TABLE abc(a, b, c);
  CREATE TABLE main.abc(a, b, c);
  CREATE INDEX main.abc_i ON abc(a, b, c);

The attached script demonstrates the problem.

Normal practise for file like apis is that if close returns an error, the handle remains open. Please either make SQLite match other apis, or update the documentation to mention that the blob handle is always closed no matter what the return.

It is probably also a good to mention that the return of close must be checked since that is where errors are returned (ie view it as a commit).

The Linux close(2) man page contains advise that translates to sqlite3_blob_close as well http://linux.die.net/man/2/close

Can you please give users the option of not defining _XOPEN_SOURCE at all?

+#ifndef SQLITE_DONT_DEFINE_XOPEN_SOURCE
 #if !defined(_XOPEN_SOURCE) && !defined(__DARWIN__) && SQLITE_THREADSAFE
 #  define _XOPEN_SOURCE 500  /* Needed to enable pthread recursive mutexes */
 #endif
+#endif

    PTHREAD_MUTEX_RECURSIVE = PTHREAD_MUTEX_RECURSIVE_NP,

Since PTHREAD_MUTEX_RECURSIVE_NP is always available, you could avoid defining _XOPEN_SOURCE and use this code instead:

-        pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE);
+        pthread_mutexattr_settype(&recursiveAttr,
+#ifdef linux
+          PTHREAD_MUTEX_RECURSIVE_NP
+#else
+          PTHREAD_MUTEX_RECURSIVE
+#endif
+        );

• #define _XOPEN_SOURCE 500 and use PTHREAD_MUTEX_RECURSIVE
• #define _XOPEN_SOURCE 600 and use PTHREAD_MUTEX_RECURSIVE
• #define _GNU_SOURCE and use PTHREAD_MUTEX_RECURSIVE
• don't define anything and use PTHREAD_MUTEX_RECURSIVE_NP on linux, and PTHREAD_MUTEX_RECURSIVE elsewhere.

Unfortunately, since PTHREAD_MUTEX_RECURSIVE is an enum on Linux, so you can't use the #ifdef PTHREAD_MUTEX_RECURSIVE compile-time technique.

I get error:

Error 27 error C2040: 'localtime' : 'tm *(const time_t *)' differs in levels of indirection from 'int ()' d:\SubProjects\Sqlite\sqlite3.c 18574

but if I add code in line 7095:

struct tm *__cdecl localtime(const time_t *t);

then Success!

SQLITE_SECURE_DELETE overwrites with 0, but should use random data.

I'm not an expert in this area, but it also sounds like secure delete should involve random data then writing to disk several times in a row.

For the firefox bug that relates to this, please see https://bugzilla.mozilla.org/show_bug.cgi?id=405925

Thanks,

-Seth

It's better to encrypt the whole database file.

I don't think writing random bits or writing multiple times will help any. There are too many layers (operating system, file system, disk driver, and disk controller) in between SQLite and the oxide for SQLite to really have much control over what is truly stored on media. SQLite might write the same page multiple times, for example, but the OS would likely coalese the writes into a single write. Perhaps we could force multiple writes using fsync() but that would be a huge performance penalty. And even if we did, just because we write to the same offset in some file does not mean that the information is written to the same place on mass storage. Some filesystems (YAFFS) intentionally spread the writes around to different places for the purpose of wear leveling. And even if the operating system doesn't do this, then on-drive disk controller might.

What problem, exactly, are you trying to solve? The existing SQLITE_SECURE_DELETE is adequate to prevent casual snooping. It is not, as you observe, adequate to prevent snooping from major governments. Against a determined adversary, the only sure defense is to physically shred the disk platter.

I don't think it's worth the effort and perf hit to attempt to protect Firefox profile data from forensic data recovery. It's really hard to get right, especially at the app level, and we don't want to give users a false sense of security by trying.

Any given data is likely to appear in many physical locations on the disk due to swapping, journaling, automated backups, and continuous defragmentation [1] .

When you try to overwrite part of a file, you find yourself fighting against optimizations in operating systems and in disks themselves [2] . Data also appears in RAM, which can survive power cycling [2] and is even harder to control.

Zeroing sounds reasonable; it will protect against casual snooping, and depending on the file system, might protect against forensic software. But I don't think we should go beyond zeroing (e.g. multiple overwrites) in an attempt to defeat forensic analysis of the physical hard drive and RAM.

Extremely paranoid users can use operating system features to encrypt an entire hard drive or automatically do multiple overwrites every time data on disk as it is changed.

[1] http://en.wikipedia.org/wiki/Disk_wipe [2] http://en.wikipedia.org/wiki/Data_remanence

thanks,

-Seth

Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table:

  CREATE TABLE a (b COLLATE unknown);

Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Simple queries like "SELECT * FROM a;" work fine.

But subselects, in their most basic form and with no sorting or comparisons, result in an error:

  sqlite> INSERT INTO a VALUES ('one');

  sqlite> SELECT * FROM a, (SELECT * FROM a);
  SQL error: no such collation sequence: unknown

  sqlite> SELECT * FROM (SELECT * FROM a);
  SQL error: no such collation sequence: unknown

  sqlite> SELECT *, * FROM a;
  one|one

This is surprising because the collation sequence should not matter to the queries. In fact, the union without the subselect works just fine and without errors.

To demonstrate, here is the explain output of a table with a registered collation sequence. No mention of the collation name here:

  sqlite> CREATE TABLE b (b collate nocase);
  sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b);
  0|Goto|0|17|
  1|Integer|0|0|
  2|OpenRead|0|3|
  3|SetNumColumns|0|1|
  4|Integer|0|0|
  5|OpenRead|2|3|
  6|SetNumColumns|2|1|
  7|Rewind|0|14|
  8|Rewind|2|13|
  9|Column|0|0|
  10|Column|2|0|
  11|Callback|2|0|
  12|Next|2|9|
  13|Next|0|8|
  14|Close|0|0|
  15|Close|2|0|
  16|Halt|0|0|
  17|Transaction|0|0|
  18|VerifyCookie|0|4|
  19|TableLock|0|3|b
  20|Goto|0|1|
  21|Noop|0|0|

Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table:

  CREATE TABLE a (b COLLATE unknown);

Now open this table in the default SQLite3 CLI. Up to here, everything works as expected.

Next issue "PRAGMA collation_list;" and notice that "unknown" lists next to the other registered collations, even though "unknown" is not registered with the default SQLite3 CLI:

  sqlite> PRAGMA collation_list;
  0|unknown
  1|NOCASE
  2|BINARY

Responses from the mailing list indicate that this is not the expected behaviour. "PRAGMA collation_list;" should list registered collations only.

Converting the GIFs to PNG images saves up to 181 KB (64%) of the documentation storage space:

  Documentation images as is (mostly GIF): 381 KB == 100%
  Documentation images as PNG:             292 KB ==  77%
  Documentation images as PNG 16 colors:   137 KB ==  36%

16 colors are more than plenty -- the diagrams show no visibility degradation. You might even cut it down to 8 colors to save even more ...

atomiccommit.html:

<p>SQLite allows a single <a href="c3ref/sqlite3.html">database connection</a> to talk to two or more database files simultaneously through the use of the <a href="/lang_attach.html">ATTACH DATABASE</a> command.

---

and <a href="#section_3.10">step 3.10</a>.

---

During recovery at <a href=section_4_2>step 4.2</a> SQLite locates

lang_insert.html:

---

single keyword <a href=lange_replace.html>REPLACE</a> as an

    idx = allocateSpace(pPage, sz);
    assert( idx>0 );

For the older versions (ours is derived from 3.3.8) the solution is quite simple:

    idx = allocateSpace(pPage, sz);
    /* Check for memory allocation errors - pre v 3.5.0 */
    if (sqlite3MallocFailed()) return SQLITE_NOMEM;
    assert( idx>0 );

For later versions you probably need something more like:

    if (idx == 0) return SQLITE_NOMEM;

replacing the assert.

This was found running test join-4.4 (from 3.3.8).

But we do try to share the 'benefits'!

Take a look at NPTL or the previous incarnation, LinuxThreads, to see how difficult it is to implement a reliable and efficient recursive mutex on different CPU architectures. It simply can't be done without dropping into architecture-specific assembler.

Here is a fairly comprehensive list of cache coherency info (circa 2003) for various CPUs:

http://linux.derkeiler.com/Mailing-Lists/Kernel/2003-09/3187.html

$ rm -f foo.db

$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format;
1
sqlite> PRAGMA legacy_file_format=OFF;
sqlite> PRAGMA legacy_file_format;
0
sqlite> CREATE TABLE abc(a,b,c);
sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc);
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc WITH INDEX abc_i ORDER BY
sqlite> vacuum;
sqlite> .q

$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format;
1
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc WITH INDEX abc_i ORDER BY
sqlite> vacuum;
sqlite> .q

But if connected to another time, the DESC index is not picked up...

$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format;
1
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc

The documentation has been clarified. Two new test cases have been inserted into the test suite to verify that the operation is correct.

  format 0x%X $res

is this a 64-bit sign-extension quirk wrt to tcl?

  --- a/test/io.test
  +++ b/test/io.test
  @@ -449,6 +449,7 @@ if {$::tcl_platform(platform)=="unix"} {
       set blob [read $fd 4]
       close $fd
       binary scan $blob i res
  +    set res [expr $res & 0xFFFFFFFF]
       format 0x%X $res
     } {0xFFFFFFFF}
   }

Well, I have a large text file 'text2.text' (~1.5mg), which contains:

20091208
20216510
20149599
20250484
20211910
20199980
........
20183528
20151994
20179184
20077607

182500 lines in total. The meaning of these numbers is a date in YYYYMMDD format. Numbers are random and unsorted at all.

The next file is 'test1.text' is the same as 'test2.text' but without the last 1000 lines. The 'diff' between these files shows the difference in last 1000 lines.

Than I execute script:

@echo off
if exist test.db del test.db
rem
rem Load data into table 'a'
rem It prints 181500
sqlite3 test.db "create table a (i1 text)"
sqlite3 test.db ".import test1.text a"
sqlite3 test.db "select count(*) from a"
rem
rem Load data into table 'b'
rem It prints 182500
sqlite3 test.db "create table b (i1 text)"
sqlite3 test.db ".import test2.text b"
sqlite3 test.db "select count(*) from b"
rem
rem PROBLEM IS HERE
rem Now I am trying to merge the tables 'a' and 'b' via UNION.
rem It should print '182500' but it prints '16384' !!!
sqlite3 test.db "create table c (i1 text)"
sqlite3 test.db "insert into c select * from b union select * from a"
sqlite3 test.db "select count(*) from c"
rem
rem Just a check that EXCEPT also has the same bug.
rem It should print 1000, but it prints '0' !!!
sqlite3 test.db "select count(*) from (select * from c except select * from b)"

I suggest that UNION and EXCEPT has some kind of memory overflow. And also it depends on how strong the numbers in the source text file are shuffled and how wide is the range of the numbers.

I can attach my files 'test1.text' and 'test2.text' if needed.

My system: CPU Intel Core 2 Duo, 1.06GHz, 2GB RAM.

Operating system: Windows XP SP2 (Version 5.1.2600)

You can use UNION ALL to to get the union of two tables without the implied DISTINCT. There is no way to omit the implied DISTINCT from EXCEPT. You will probably need to refactor your query to use the NOT IN operator.

$amount = sprintf( "%d.%02d", rand( 1, 10000 ), rand( 0, 99 ) );

so, it can take 9999999 possible values, which is more than 16384 for sure.

And the behaviour of the EXCEPT was completely the same as I've described the the original message of this bug report. That's why I've decided that the key of the problem is just in the amount of data, not in values, and I've reduced the database structure for the bug report to make it more clear (I left only one field 'date'), but the 'bug' (I mean this strange behaviour of the EXCEPT and UNION) still happens in the reduced database structure (with only one field 'date'). So DISTINCT function will not merge all values of 'amount' field into 16384 unique values.

What do you think?

  + CFLAGS="-Os -DSQLITE_ENABLE_FTS3=1 -DSQLITE_THREADSAFE=0"
  + NAME=sqlite3-$VERS-osx-x86.bin
  + echo '***** '"COMPILING $NAME..."
  + gcc $CFLAGS -Itsrc sqlite3.c tsrc/shell.c -o $NAME -ldl -lpthread

  +CFLAGS="-Os -DSQLITE_ENABLE_FTS3=1 -DSQLITE_THREADSAFE=0"
  +echo '***** '"COMPILING sqlite3-$VERS.bin..."
  +gcc $CFLAGS -Itsrc sqlite3.c tsrc/shell.c -o sqlite3 -ldl -lpthread

ldd ./sqlite3-3.5.3.bin

        linux-gate.so.1 =>  (0xb7f1d000)
        libdl.so.2 => /lib/libdl.so.2 (0xb7f05000)
        libpthread.so.0 => /lib/i686/libpthread.so.0 (0xb7ef2000)
        libc.so.6 => /lib/i686/libc.so.6 (0xb7dc5000)
        /lib/ld-linux.so.2 (0xb7f1e000)

Unrelated - why are the shared libs and binary chmod'ed to be not executable?

  chmod 644 sqlite3-$VERS.bin.gz
  chmod 644 tclsqlite3.so
  chmod 644 sqlite3.so

I included a patch for Makefile.in and configure.ac to start using the detected nm instead of the hardcoded 'nm'.

A better approch to cross-compiling is to use the amalgamation. Build the amalgamation using:

    make sqlite3.c

Then compile sqlite3.c for your target system using whatever cross-compiler you want.

It looks as if this allocation error will also result in leaking the old WhereTerm array addressed through pWC->a (if this was not static).

The correction is probably to re-instate the old value on error before returning from whereClauseInsert(), ie to add the line:

  pWC->a = pOld;

before the return 0.

  if( db->mallocFailed ) return;

Unfortunately initialising some of the local variables can already reference null pointers:

  WhereTerm *pTerm = &pWC->a[idxTerm];
  ExprMaskSet *pMaskSet = pWC->pMaskSet;
  Expr *pExpr = pTerm->pExpr;

If pWC->a is NULL the third of these initialisations causes problems.

This happened here in the two calls:

    whereSplit(&sOr, pExpr, TK_OR);
    exprAnalyzeAll(pSrc, &sOr);

from exprAnalyze(), early in the section beginning:

  /* Attempt to convert OR-connected terms into an IN operator so that
  ** they can make use of indices.  Example:

where the allocation in whereClauseInsert() (called by 5 depths of whereSplit()) failed while processing "SELECT y FROM t9\nWHERE x=(SELECT x FROM t9 WHERE y=1)\n OR x=(SELECT x FROM t9 WHERE y=2)\n OR x=(SELECT x FROM t9 WHERE y=3)\n OR x=(SELECT x FROM t9 WHERE y=4)\n OR x=(SELECT x FROM t9 WHERE y=5".

The fix is to delay initialising the variables until after the check for mallocFailed.

  /* Add constraints to reduce the search space on a LIKE or GLOB
  ** operator.
  ...
    pStr2 = sqlite3ExprDup(db, pStr1);
    if( pStr2 ){
      assert( pStr2->token.dyn );
      ++*(u8*)&pStr2->token.z[nPattern-1];
    }

A memory allocation error (in sqlite3StrNDup() called from sqlite3ExprDup()) can leave pStr2->token.z as NULL. Dereferencing this to increment an element brings a segment violation.

sqlite3ExprDup() currently has no way of returning an error.

  sqlite3DequoteExpr(db, pRight);
  z = (char *)pRight->token.z;
  for(cnt=0; (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2]; cnt++){}

A memory allocation error (in sqlite3StrNDup(0 called from sqlite3TokenCopy() called from sqlite3DequoteExpr()) can leave pRight->token.z as NULL. Dereferencing z in the for statement brings a segment violation.

  make TOP=`pwd` BCC=gcc TCC=gcc AR=ar RANLIB=echo NAWK=gawk -f \
    main.mk sqlite3.h sqlite3.c fts3amal.c
  cat fts3amal.c >> sqlite3.c
  gcc -DSQLITE_THREADSAFE -DSQLITE_API=static -DSQLITE_PRIVATE=static \
    -DSQLITE_EXTERN=static -DSQLITE_ENABLE_FTS3 -c sqlite3.c
  nm sqlite3.o | grep -v ' [trUbd] '

  00000004 C sqlite3_api
  00064da2 T sqlite3Fts3HashClear
  000652a4 T sqlite3Fts3HashFind
  00064d60 T sqlite3Fts3HashInit
  0006533b T sqlite3Fts3HashInsert
  00064b4c T sqlite3Fts3Init
  00066b34 T sqlite3Fts3InitHashTable
  000669bd T sqlite3Fts3PorterTokenizerModule
  0006702d T sqlite3Fts3SimpleTokenizerModule

May I suggest this as part of the release/test process:

  gcc --pthread -DNDEBUG -DSQLITE_THREADSAFE -DEXPERIMENTAL -DSQLITE_API=static -DSQLITE_PRIVATE=static -DSQLITE_EXTERN=static -c sqlite3.c
  nm sqlite3.o | grep -v ' [trUbd] '

If there is any output from the nm/grep then those symbols have not been scoped.

But I don't see EXPERIMENTAL #defined in the code anywhere. Also, --pthread does not work for gcc on Linux.

  $ gcc -DSQLITE_THREADSAFE -DSQLITE_API=static -DSQLITE_PRIVATE=static -DSQLITE_EXTERN=static -c sqlite3.c
  $ nm sqlite3.o | grep -v ' [trUbd] '
  0001d964 T invalidateCursorsOnModifiedBtrees

$ grep EXPERIMENTAL sqlite3.[ch]
sqlite3.c:****** EXPERIMENTAL - subject to change without notice **************
sqlite3.c:****** EXPERIMENTAL - subject to change without notice **************
sqlite3.c:****** EXPERIMENTAL - subject to change without notice **************
sqlite3.c:** This is an EXPERIMENTAL api and is subject to change or removal.
sqlite3.c:** EXPERIMENTAL - This is not an official function.  The interface may
sqlite3.c:/*** EXPERIMENTAL ***
sqlite3.c:******* THIS IS AN EXPERIMENTAL API AND IS SUBJECT TO CHANGE ******
sqlite3.c:****** EXPERIMENTAL - subject to change without notice **************
sqlite3.c:****** EXPERIMENTAL - subject to change without notice **************

sqlite3_compile() for an empty statement, like "" now returns a NULL pointer for the statement, but the function returns SQLITE_OK. In my applications I always assumed that if sqlite3_compile() returns SQLITE_OK, then there will be a valid statement.

Now if you sqlite3_step() on the NULL pointer, you get a segfault.

See also #2773.

  pE = sqlite3ExprAnd(pParse->db,*ppExpr, pE);
  if( pE ){
    *ppExpr = pE;
  }

The function sqlite3Expr() called by sqlite3ExprAnd() deletes both Expr parameters if it fails to create its result Expr.

Here the caller still has a pointer to the now freed Expr structure.

In our system it currently gets deleted again later, in a call from clearSelect() called from sqlite3SelectDelete() resulting in double free errors.

However, it is possible that this does not occur on current systems as ours is still based on version 3.3.8 (which still includes the bad (and no longer required) sqlite3ExprOrFree()).

The problem looks as if it slipped in when sqlite3Expr() was changed to delete its parameters on error.

The solution is to clear *ppExpr in this case, and this is most easily done by simply copying pE into it in all cases, even when NULL, ie make the line "*ppExpr = pE;" unconditional.

os_win.obj : error LNK2019: unresolved external symbol FormatMessageA referenced in function winDlError

The only occurence of FormatMessageA is in os_win.c. Windows Mobile doesn't support ASCII, only wide characters. Since LoadLibrary isn't supported in winDlOpen on Windows Mobile, I suggest the following patch for winDlError:

  #if OS_WINCE
  	if (nBuf > 0)
  		zBufOut[0] = '\0';
  #else
    FormatMessageA(
      FORMAT_MESSAGE_FROM_SYSTEM,
      NULL,
      GetLastError(),
      0,
      zBufOut,
      nBuf-1,
      0
    );
  #endif

http://pkg-config.freedesktop.org/wiki/

  Libs.private: -lpthread

http://www.sqlite.org/capi3ref.html

sqlite3SrcListAppend() in build.c can call sqlite3DbRealloc() which can fail under low-memory conditions. In this case it returns zero.

It is called by flattenSubquery() in select.c in the section following:

  /* Move all of the FROM elements of the subquery into the
  ** the FROM clause of the outer query.

Immediately (3 lines) after this call (after the loop) the return value is dereferenced in the start of the next loop:

        pSrc = sqlite3SrcListAppend(db, pSrc, 0, 0);
      }
      p->pSrc = pSrc;
      for(i=pSrc->nSrc-1; i-extra>=iFrom; i--){

I am also getting a 'double free' error during the cleanup in sqlite3SrcListAppend(), where the old (unextended) structure is freed by sqlite3SrcListDelete(). Apparently one of the pItem->zName strings has already been freed by the lines:

    sqlite3DeleteTable(pSubitem->pTab);
    sqlite3_free(pSubitem->zDatabase);
    sqlite3_free(pSubitem->zName);
    sqlite3_free(pSubitem->zAlias);

immediately before the call in this section of flattenSubquery(). Clearing these pointers after freeing gets rid of the double free error.

I can't offer a simple solution to the SEGV

The SQL statement is "SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );" from the join-8.3 runtest.

there are some broken links in the new website: Documentation -> SQL Syntax -> PRAGMA

and

http://www.sqlite.org/lang_droptable.html try to click on "CREATE TABLE" and "VACUUM".

For example: SQL Syntax segment could be its own pdf or could be merged into a pdf with the c++ API Reference.

)) PRAGMA: Does anyone have experience and good results optimizing sqlite performance using PRAGMA's? )) Other techniques: Any success stories on sqlite optimization methods of any type would be appreciated.

Here is a list of the PRAGMA's and example use from the sqlite documentation:

PRAGMA auto_vacuum; PRAGMA auto_vacuum = 0 | none | 1 | full | 2 | incremental;

PRAGMA cache_size; PRAGMA cache_size = Number-of-pages;

PRAGMA case_sensitive_like; PRAGMA case_sensitive_like = 0 | 1;

PRAGMA count_changes; PRAGMA count_changes = 0 | 1;

PRAGMA default_cache_size; PRAGMA default_cache_size = Number-of-pages;

PRAGMA default_synchronous;

PRAGMA empty_result_callbacks; PRAGMA empty_result_callbacks = 0 | 1;

PRAGMA encoding; PRAGMA encoding = "UTF-8"; PRAGMA encoding = "UTF-16"; PRAGMA encoding = "UTF-16le"; PRAGMA encoding = "UTF-16be";

PRAGMA full_column_names; PRAGMA full_column_names = 0 | 1;

PRAGMA fullfsync PRAGMA fullfsync = 0 | 1;

PRAGMA incremental_vacuum(N);

PRAGMA legacy_file_format; PRAGMA legacy_file_format = ON | OFF

PRAGMA locking_mode; PRAGMA locking_mode = NORMAL | EXCLUSIVE PRAGMA main.locking_mode=EXCLUSIVE;

PRAGMA page_size; PRAGMA page_size = bytes;

PRAGMA max_page_count; PRAGMA max_page_count = N;

PRAGMA read_uncommitted; PRAGMA read_uncommitted = 0 | 1;

PRAGMA short_column_names; PRAGMA short_column_names = 0 | 1;

PRAGMA synchronous; PRAGMA synchronous = FULL; (2) PRAGMA synchronous = NORMAL; (1) PRAGMA synchronous = OFF; (0)

PRAGMA temp_store; PRAGMA temp_store = DEFAULT; (0) PRAGMA temp_store = FILE; (1) PRAGMA temp_store = MEMORY; (2)

PRAGMA temp_store_directory; PRAGMA temp_store_directory = 'directory-name';

	@comp_code 		varchar(8),
	@begin_date  		datetime,
	@end_date     		datetime,
	@emp_short_name 	varchar(8),
	@emp_id 		int,
	@driver_time_in 		output,
	@driver_time_out 	output,
	@driver_beaktime_in 	output,
	@driver_beaktime_out 	output,
	@driver_lunchtime_in 	output,
	@driver_lunchtime_out 	output,
	@total_break_time   	output

}

AS

select @driver_time_in =driver_datetime_in,@driver_time_out =driver_datetime_out from driver_daily_log with (nolock) where company_code=@comp_code and emp_id=@emp_id and driver_datetime_in >=@begin_date and driver_datetime_out<@end_date and driver_datetime_out is not null

select @driver_breaktime_in=substring((CAST(break_time_in AS varchar(20))),12,8) ,@driver_breaktime_out=substring((CAST(break_time_out AS varchar(20))),12,8) from driver_daily_break_log with (nolock) where company_code=@comp_code and emp_id=@emp_id and driver_status='OB'.

select @driver_lunchtime_in=substring((CAST(break_time_in AS varchar(20))),12,8) ,@driver_lunchtime_out=substring((CAST(break_time_out AS varchar(20))),12,8) from driver_daily_break_log with (nolock) where company_code=@comp_code and emp_id=@emp_id and driver_status='OL'.

select @total_break_time =sum(total_break_time) from driver_daily_break_log with (nolock) where company_code=@comp_code and emp_id=@emp_id.

This procedure is giving me the following error :

SQL State 42000: Error 0 : Syntax error or access violation

  ./ext/fts3/fts3_hash.c: In function "fts3HashMalloc":
  ./ext/fts3/fts3_hash.c:38: warning: initialization makes pointer from integer without a cast

Also, can you move the following:

  #ifndef SQLITE_ENABLE_BROKEN_FTS2
  #error fts2 has a design flaw and has been deprecated.
  #endif

to be after this line:

  #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_FTS2)

in fts2.c?

That way you can build fts3 while having fts2 objects in the Makefile without defining -DSQLITE_ENABLE_BROKEN_FTS2 for an fts3-only build.

  ./ext/fts3/fts3_hash.c: In function "fts3HashMalloc":
  ./ext/fts3/fts3_hash.c:38: warning: initialization makes pointer from integer without a cast

On some 64-bit systems this will cause FTS to crash because an int is smaller than a pointer.

Nevermind - it was fixed.

sqlite3CodeRowTrigger() calls sqlite3ExprDup() which fails to deep copy the string p->pRight->token.z (and therefore everything thereafter).

sqlite3CodeRowTrigger() continues to call sqlite3ExprResolveNames()

sqlite3ExprResolveNames() calls walkExprTree() for the expression, which recursively calls walkExprTree() for pExpr->pRight which in turn calls nameResolverStep() which then tries to dereference pExpr->token.z[0] (for case TK_STRING) generating the SEGV.

The solution appears to be checking for allocation failures before calling nameResolverStep(), using the same early exit as when this fails. ie replacing:

      if( sqlite3ExprResolveNames(&sNC, whenExpr) ){

with:

      if( pParse->db->mallocFailed ||
          sqlite3ExprResolveNames(&sNC, whenExpr) ){

  create virtual table test using fts2(a);
  insert into test (a) values ('alpha');
  insert into test (a) values ('beta');
  select a from test where test match ('a:gamma* OR a:delta*');

sqlite3: ../sqlite/ext/fts3/fts3.c:478: dataBufferAppend: Assertion `nSource>0 && pSource!=((void *)0)' failed.

"Workaround" is to compile with -DNDEBUG=1, but the person who reported this to me was using the fts2 dll from sqlite.org.

Fix on the way.

This only happens when sqlite3 is compiled with the --enable-threads option, but not with --disable-threads.

  #0  0xbb987124 in sqlite3_step () from /usr/pkg/lib/libsqlite3.so.0
  #1  0xbb9a7307 in _sqlite_step_with_busyhandler ()
     from /usr/pkg/lib/python2.4/site-packages/pysqlite2/_sqlite.so
  #2  0xbb9a437b in pysqlite_cursor_executescript ()
     from /usr/pkg/lib/python2.4/site-packages/pysqlite2/_sqlite.so
  #3  0xbbb45baa in PyCFunction_Call () from /usr/pkg/lib/libpython2.4.so.1.0

The reason for the crash is that v->db is NULL. In the non-threaded case, it all works.

[1] <http://initd.org/pub/software/pysqlite/releases/2.3/2.3.5/pysqlite-2.3.5.tar.gz>

Have changed things so that SQLITE_MISUSE is returned whether the build is threadsafe or not. But this should also be fixed in Python.

  ...
      z = sqlite3NameFromToken(db, pName1);
      pTab = sqlite3LocateTable(pParse, z, 0);
  ...

In a low memory situation z can be returned as NULL.
sqlite3LocateTable() calls sqlite3FindTable() which promptly asserts on:

  assert( zName!=0 );

I expect the solution is to conditionalise some of the code in sqlite3Analyze():

  ...
      z = sqlite3NameFromToken(db, pName1);
      if (z){
        pTab = sqlite3LocateTable(pParse, z, 0);
        sqlite3_free(z);
        if( pTab ){
          analyzeTable(pParse, pTab);
        }
      }
  ...

memset(&yygotominor, 0, sizeof(yygotominor));

(added for the resolution of SQLite ticket #2172).

The lempar.c just include stdio.h, it would suffice to add stdlib.h to get the memset() declaration (even if all real parsers must include stdlib.h to get something really working).

I'm using preprocessed sources downloaded from here. DEBUG build of preprocessed sources works correctly, problem is only in RELEASE build. I'm using VC6.0 to compile.

Any idea what could be wrong? Thank you!

Large blobs work for me with both release and debug builds (not msvc though, gcc/linux).

  int main(int argc, char* argv[])
  {
	int rc;
	sqlite3* db;
	sqlite3_stmt* stmt;

	rc = sqlite3_open("n2.db3", &db);

	rc = sqlite3_prepare(db, "CREATE TABLE [ttt] ([bbb] BLOB)", -1, &stmt, 0 );
	rc = sqlite3_step(stmt);
	rc = sqlite3_reset(stmt);

	char text[10000],query[20000];
	strnset(text,'a',sizeof(text)-1);
	sprintf(query,"insert into [ttt] values (?)");

	rc = sqlite3_prepare(db, query, -1, &stmt, 0 );

	rc = sqlite3_bind_blob(stmt,1,text,sizeof(text), SQLITE_TRANSIENT);

	rc = sqlite3_step(stmt);
	rc = sqlite3_reset(stmt);

	rc = sqlite3_prepare(db, "select * from ttt", -1, &stmt, 0 );

	rc = sqlite3_step(stmt);

	if (rc == SQLITE_ROW)
	{
		printf("%s: OK",sqlite3_column_text(stmt,1));
	}
	else
	if (rc == SQLITE_DONE)
	{
		printf("DONE");
	}
	else
	{
		printf("Error %d",rc);
	}

	return 0;
}

But even without those fixes, I cannot reproduce the problem on Linux.

I've tried to debug it. It fails in btree.c, line 3056:

if( offset+amt > nKey+pCur->info.nData ){ /* Trying to read or write past the end of the data is an error */ return SQLITE_ERROR; }

there seems to be different values in release mode. My debugger does not show values of variables in release mode, so I can be wrong, but it seems in release offset is 5 and in debug it is 4. There can be something wrong with compilation, I'll try to figure this out tomorrow.

BTW compilation of static libraty in VC6.0 gives 185 warnings. I don't know if it is ok, it haven't caused problems in older sqlite

Thank you for your time.

I have also used boundschecker to check sqlite, and it detects many dangling pointers ! But the strange thing is that I cannot find why these pointers are dangling, here an example:

  In prepare.c@188
  pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName);

Boundchecker say that zMasterName is a dangling pointer, previously released here:

  in build.c@711:
  void sqlite3StartTable(
    Parse *pParse,   /* Parser context */
    Token *pName1,   /* First part of the name of the table or view */
    Token *pName2,   /* Second part of the name of the table or view */
    int isTemp,      /* True if this is a TEMP table */
    int isView,      /* True if this is a VIEW */
    int isVirtual,   /* True if this is a VIRTUAL table */
    int noErr        /* Do nothing if table already exists */
  ){
  }

It does not make sens for me, maybe it a false positive from boundchecker, but it is weird.

I don't know if these "errors" are related to the "blob" bug in release mode. I will try to debug these error with some "printf" in release mode.

Note: The provided dll (the one from the sqlite site) does not have this "bug".

In btree.c, line 3056:

  if( offset+amt > nKey+pCur->info.nData ){
    /* Trying to read or write past the end of the data is an error */
    return SQLITE_ERROR;
  }

After adding some printf around, It seems that the "speed optimization" compilation flag of VC6 changes the code order in a way that the offset variable is miss incremented !!

Two remarks:

• I've traced the calling function, sqlite3BtreeData, and the it call accessPayload with the good offset value
• VC6 produces an internal error: "fatal error C1001: INTERNAL COMPILER ERROR" in the accessPayload function, if I try to access the offset value before this line:

  aPayload = pCur->info.pCell + pCur->info.nHeader;

A dirty workaround could be to change the code order or the local var usage. I'm trying ....

I get the following assertion error:

./src/vdbe.c:503: sqlite3VdbeExec: Assertion `pTos<=&p->aStack[pc]' failed.

with the attached "script".

   CREATE TABLE t1(x);
   CREATE TRIGGER r1 BEFORE UPDATE ON t1 BEGIN
      SELECT raise(ignore);
   END;
   INSERT INTO t1 VALUES(1);
   INSERT INTO t1 VALUES(2);
   INSERT INTO t1 SELECT x+2 FROM t1;
   INSERT INTO t1 SELECT x+4 FROM t1;
   UPDATE t1 SET x=x+1;

The problem is that the code generator is generating VDBE code that fails to clear values from the VDBE stack. After a few iterations of the trigger, the VDBE stack overflows, triggering the assertion fault.

It may be that the person posting the previous comment did not compile their version 3.5.2 with -DSQLITE_DEBUG. Hence assert() statements were disabled. The assert() statement is correct, however. This is a real problem.

Should be a relatively easy fix. Either defer pushing the extra values until after the BEFORE triggers run. Or else pass an extra parameter into sqlite3CodeRowTrigger() to tell it how many values to pop from the stack when a RAISE(IGNORE) is encountered. Preferably the first.

I have long wanted to switch the VDBE from being a stack-based VM to being a register based VM. Notice that were the VDBE register based, this bug would have never come up....

   file delete -force test.db test.db-journal
   sqlite3 db test.db
   db eval {
     CREATE TABLE t1(x,y);
     INSERT INTO t1 VALUES(1,2);
     CREATE TABLE t2(a,b);
     INSERT INTO t2 VALUES(8,9);
   }
   db eval {SELECT * FROM t1} {
     db transaction {
       db eval {UPDATE t2 SET a=a*2}
     }
   }

The [db transaction] statement starts a transaction and it is suppose to commit the tranaction at the end of the code block. But because the transaction started while a query was active, the tranaction is unable to commit. The TCL interface never commits the tranaction nor does it give any kind of error indication.

It is unclear if an error should be returned or if the commit should be deferred until outer query finishes.

If the code within the [db transaction] block throws an error, we really need the transaction to rollback right away. Perhaps there should be a new API that cancels all pending queries. Perhaps a call to sqlite3_interrupt() would suffice for this. Need to investigate further....

Anyway here is the script :

  import pysqlite2.dbapi2 as sqlite
  cnx = sqlite.connect('testdb')
  cursor = cnx.cursor()
  cursor.execute('SELECT eid from Trinfo')
  nb = len(cursor.fetchall())
  cursor.execute("INSERT INTO Trinfo ( from_state, to_state, wf_info_for, eid, creation_date, modification_date ) VALUES ( 734, 735, 5, 758, 1, 1)")
  # comment this line and the final assertion is ok
  cursor.execute("CREATE TEMPORARY TABLE T58d712f777a7170cb8ce7d94cef119ce (C0 integer,C1 timestamp);")
  # get the same result with or without dropping the temp table
  #cursor.execute('DROP TABLE T58d712f777a7170cb8ce7d94cef119ce')
  cnx.rollback()
  cursor.execute('SELECT eid from Trinfo')
  nb3 = len(cursor.fetchall())
  assert nb3 == nb # FAIL

Notice that if you don't create the temporary table, everything run fine, else the insertion is not actually rollbacked.

    file delete -force test.db test.db-journal
    sqlite3 db test.db
    db eval {
      CREATE TABLE t1(a,b,c);
      INSERT INTO t1 VALUES(1,2,3);
      INSERT INTO t1 VALUES(4,5,6);
      INSERT INTO t1 SELECT a*2, b*2, c*2 FROM t1;
      INSERT INTO t1 SELECT a*4, b*4, c*4 FROM t1;
      INSERT INTO t1 SELECT a*8, b*8, c*8 FROM t1;
    }
    puts "begin=[md5 [db eval {SELECT * FROM t1}]]"
    db eval {BEGIN}
    db eval {INSERT INTO t1 VALUES('hello','out','there');}
    db eval {CREATE TEMPORARY TABLE T58d712f777(C0 integer,C1 timestamp)}
    puts "  mid=[md5 [db eval {SELECT * FROM t1}]]"
    db eval {ROLLBACK}
    puts "  end=[md5 [db eval {SELECT * FROM t1}]]"

The results are:

    begin=c17bc52be49269f8f2cd3a4df3caee6e
      mid=f86cc8ad5b925962323edc041bfaa09d
      end=c17bc52be49269f8f2cd3a4df3caee6e

Begin and end are equal so it appears to work using the TCL interface. I also observe that there are countless tests in the SQLite test suite that verify that this sort of thing works correctly.

Perhaps this is something to do with python after all.

(I realize this is a developer's page, but this is a real annoyance and I have no idea what to do about it!) Thanks, Liz

AIX system header files usually define this themselves (in /usr/include/ standards.h) but only if _XOPEN_SOURCE was previously undefined.

Has anyone else come across this bug? I'm willing to believe it's our build system if not; we can work around it by setting _ALL_SOURCE on the command-line, but I thought it might be useful to raise a bug anyway.

I do not have access to an AIX machine and so have no ability to debug this problem. If you have suggested patches we will consider them. Otherwise, there is not much we can do about this ticket.

And another problem: trying to build fts3_hash.c gives warnings about unknown parameters for sqlite3_malloc (used from fts3HashMalloc) because it never finds sqlite3.h from anywhere. On at least one 64-bit Solaris platform this has caused a segfault on execution, because the compiler guess for return type (int) was 32 bits, whereas the pointer it was trying to return was 64 bits.

  Thread 0 Crashed:
  0   libSystem.B.dylib             	0x00007fffffe00637 __bzero + 55
  1   org.sqlite.sqlite3            	0x0000000100051fc3 fts3HashMalloc + 46
  2   org.sqlite.sqlite3            	0x0000000100052465 sqlite3Fts3HashInsert + 316
  3   org.sqlite.sqlite3            	0x000000010004bb0e sqlite3Fts3Init + 126
  4   org.sqlite.sqlite3            	0x000000010002537c openDatabase + 808
  5   sqlite3                       	0x000000010000115c open_db + 37
  6   sqlite3                       	0x0000000100003af9 process_input + 938
  7   sqlite3                       	0x00000001000043a8 main + 1696
  8   sqlite3                       	0x000000010000099c start + 52

I didn't try the idea of changing malloc & friends to sqlite equivalents.

Also, Makefile.in should be updated so that fts2 and fts3 can build out of the box on all architectures without requiring them loaded as external modules.

Today i came across a use case where i would like client code to be able to delete an underlying sqlite3 db, but that code doesn't have immediate access to the file name of that db (without refactoring the db wrapper code).

An interesting feature addition would, IMO, be:

int sqlite3_unlink( sqlite3 * db, bool closeTheFile );

Unlinks the file associated with the given database. It does not alter the database in any way (thus is it a no-op on a :memory: database). The closeTheFile flag specifies whether the file handle associated with db should also be closed (and thus db must also be closed), or just unlinked (e.g., as temporary databases are unlinked right after creation but kept open).

After browsing through the VFS API a bit, i see that there is an xDelete function, but i'm not sure if its semantics require that the underlying file handle be closed. i don't see an extra xClose member of VFS, so i assume that xDelete also handles closing the file handle. If these were split into two features, sqlite3_unlink() could be implemented very easily.

:)

select * from t1 where y is NULL;

causes:

1301 assert( pTerm->eOperator & (WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) ); (gdb) bt

#0 0x005e17a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2

#1 0x006217d5 in raise () from /lib/tls/libc.so.6

#2 0x00623149 in abort () from /lib/tls/libc.so.6

#3 0x0061adb1 in __assert_fail () from /lib/tls/libc.so.6

#4 0x00849534 in bestVirtualIndex (pParse=0xbfe2d750, pWC=0xbfe2d1d0, pSrc=0x9a23d2c, notReady=18446744073709551615, pOrderBy=0x0, orderByUsable=1, ppIdxInfo=0x9a372bc) at ../../../../Source/sqlite-3.5.1-hp/src/where.c:1301

#5 0x0084a989 in sqlite3WhereBegin (pParse=0xbfe2d750, pTabList=0x9a23d28, pWhere=0x9a28ed8, ppOrderBy=0xbfe2d59c) at ../../../../Source/sqlite-3.5.1-hp/src/where.c:2074

#6 0x0082c6c6 in sqlite3Select (pParse=0xbfe2d750, p=0x9a28f28, eDest=4, iParm=0, pParent=0x0, parentTab=0, pParentAgg=0x0, aff=0x0) at ../../../../Source/sqlite-3.5.1-hp/src/select.c:3118

#7 0x0081f16c in yy_reduce (yypParser=0x9a33aa8, yyruleno=104) at parse.y:368

#8 0x00820f65 in sqlite3Parser (yyp=0x9a33aa8, yymajor=1, yyminor= {z = 0x9a2e120 ";", dyn = 0, n = 1}, pParse=0xbfe2d750) at parse.c:3419

#9 0x0082e877 in sqlite3RunParser (pParse=0xbfe2d750, zSql=0x9a2e100 "select * from t1 where y is NULL;", pzErrMsg=0xbfe2d74c) at ../../../../Source/sqlite-3.5.1-hp/src/tokenize.c:449

#10 0x008246a0 in sqlite3Prepare (db=0x9920010, zSql=0x9a2e100 "select * from t1 where y is NULL;", nBytes=-1, saveSqlFlag=0, ppStmt=0xbfe2d8e0, pzTail=0xbfe2d8e4) at ../../../../Source/sqlite-3.5.1-hp/src/prepare.c:525

#11 0x00824a2c in sqlite3LockAndPrepare (db=0x9920010, zSql=0x9a2e100 "select * from t1 where y is NULL;", nBytes=-1, saveSqlFlag=0, ppStmt=0xbfe2d8e0, pzTail=0xbfe2d8e4) at ../../../../Source/sqlite-3.5.1-hp/src/prepare.c:604

#12 0x00824bf9 in sqlite3_prepare (db=0x9920010, zSql=0x9a2e100 "select * from t1 where y is NULL;", nBytes=-1, ppStmt=0xbfe2d8e0, pzTail=0xbfe2d8e4) at ../../../../Source/sqlite-3.5.1-hp/src/prepare.c:658

#13 0x0084d862 in sqlite3_exec (db=0x9920010, zSql=0x9a2e100 "select * from t1 where y is NULL;", xCallback=0x8049b23 <callback>, pArg=0xbfe2da20, pzErrMsg=0xbfe2d9ac) at ../../../../Source/sqlite-3.5.1-hp/src/legacy.c:56

#14 0x0804d2e9 in process_input (p=0xbfe2da20, in=0x0) at ../../../../Source/sqlite-3.5.1-hp/src/shell.c:1663

#15 0x0804de80 in main (argc=5, argv=0xbfe2efd4) at ../../../../Source/sqlite-3.5.1-hp/src/shell.c:2001

Fails to accommodate WO_ISNULL flag.

sqlite3_stmt* stm;

sqlite3_open("db", &s1); sqlite3_open("db", &s2);

sqlite3_prepare_v2(s2, "SELECT * FROM sqlite_master", 27,&stm, NULL); sqlite3_step(stm); sqlite3_finalize(stm);

sqlite3_prepare_v2(s1, "CREATE TABLE C (c1)", 19, &stm, NULL); sqlite3_step(stm); sqlite3_finalize(stm);

sqlite3_prepare_v2(s2, "SELECT * FROM C", 15, &stm, NULL);

Last row return ERROR n.17 SQLITE_SCHEMA, But All previous statements are finalized.

Which call is returning SQLITE_SCHEMA? sqite3_prepare_v2() or sqlite3_step()?

Are any other processes accessing the same database?

Which version of SQLite are you using?

Can you post a complete program that demonstrates this?

For me, the final sqlite3_prepare_v2() returns 17 (SQLITE_SCHEMA). Up to this everything works as documented.

I run the posted code unchanged in a single process on Windows XP as main(). The database file does not exist prior to execution.

This program opens two connections to the database file. Runs a query in one which causes SQLite to read the schema. It then adds a table using the second connection. This changes the schema. Finally it tries to execute a query that references the new table using the first connection. This generates an error.

I was informed by DanK on the mailing list that this is the normal behavior of SQLite. I thought that the new prepare_v2 API was supposed check if the schema cache was stale, and reload it before generating a schema error, but he said it didn't. Apparently the poster thinks this behavior is strange as well.

I think this should be reopened, or re-closed as "works as designed". It should be reproducible (I suspect the test was done using a single connection) and maybe shouldn't have been closed the way it was.

[[[ ]]]

`~!@#$%^&*

()_+|{}:<>?

-=\[];',./

@@@ %%%

### ^^^

___---

Ho_mer

`~!@#$%^&*()_+|`{}:<>?-=\[];,./

_Storage-Room

@ # $

_ - +

% ^ &

asdf_qwerty

As all the data contains special charcters ,I am replacing them with SearchS = Trim(SearchS)

SearchS = Replace(SearchS, "'", "''")

SearchS = Replace(SearchS, "[", "[[]")

SearchS = Replace(SearchS, "%", "[%]")

SearchS = Replace(SearchS, "^", "[^]")

SearchS = Replace(SearchS, "#", "[#]")

SearchS = Replace(SearchS, "_", "[_]")

My SQL Query looks like this select * from table_name where name LIKE '%" & SearchS & "%'

eg . If I am trying to search for '%%%' my query will be

select * from table_name where name LIKE '% [%][%][%]%'

Which is returning zero results.

so how should I replace special characters for SQlite.

Thanks

vacuum does exactly what we need (creating a new db from an old one), but it re-uses the existing pragmas for page size, auto vacuum and reserved page size.

from sqlite.c, see sqlite3RunVacuum()

  sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain),
     sqlite3BtreeGetReserve(pMain));

dr hipp points out that the the operands to vacuum are unused. from sqlite.c:

sqlite3VdbeAddOp(v, OP_Vacuum, 0, 0);

one solution would be to allow the user to specify the page size, reserve page size, and autovacuum as optional params to vacuum.

he had an idea of using the signedness of the first operand to represent the autovacuum setting (since after a table is created, you can change the setting from auto to incremental, but you can't change it from none to auto (or none to incremental)

As an example I provide two outputs, the first one without a registered trace function and the second one with one (in which it cannot be seen where the exception cames from):

   > ./a.tcl
vorher
BUMMM
    while executing
"a"
    invoked from within
"db transaction {
        puts "vorher"
        a
        puts "nachher"
    }"
    ("uplevel" body line 1)
    invoked from within
"uplevel 1 [list db transaction {
        puts "vorher"
        a
        puts "nachher"
    }]"
    (procedure "b" line 2)
    invoked from within
"b"
    (file "./a.tcl" line 28)

   > ./a.tcl
BEGIN
vorher
ROLLBACK

    while executing
"db transaction {
        puts "vorher"
        a
        puts "nachher"
    }"
    ("uplevel" body line 1)
    invoked from within
"uplevel 1 [list db transaction {
        puts "vorher"
        a
        puts "nachher"
    }]"
    (procedure "b" line 2)
    invoked from within
"b"
    (file "./a.tcl" line 28)

********

A scritp that demostrates this behaviour is attached. The only workaround is not to trace.

Thanks

    for(i=0; rc==SQLITE_OK && i<db->nDb; i++){
      Btree *pBt = db->aDb[i].pBt;
      if( pBt && sqlite3BtreeIsInTrans(pBt) ){
        rc = sqlite3BtreeCommitPhaseOne(pBt, zMaster);
      }
    }
    sqlite3OsClose(&master);
    if( rc!=SQLITE_OK ){
      sqliteFree(zMaster);
      return rc;
    }

It seems like that last bit should be:

    if( rc!=SQLITE_OK ){
      sqlite3OsDelete(zMaster);
      sqliteFree(zMaster);
      return rc;
    }

  struct lockInfo *pLock = pFile->pLock;

and later in the function

  pLock = pFile->pLock;

In non-threadsafe builds (where transferOwnership is a no-op) it is redundant, but it's not hurting anybody.

Example session:

  # ./sqlite3
  SQLite version 3.5.1
  Enter ".help" for instructions
  sqlite> create table foo(a);
  sqlite> insert into foo values(1);
  sqlite> select count(a) from foo;
  Bus error (core dumped)

   diff -u -r1.110 vdbeapi.c
   --- src/vdbeapi.c       17 Oct 2007 01:44:21 -0000      1.110
   +++ src/vdbeapi.c       31 Oct 2007 18:15:44 -0000
   @@ -446,7 +446,7 @@
          pMem->flags = MEM_Agg;
          pMem->xDel = sqlite3_free;
          pMem->u.pDef = p->pFunc;
   -      if( nByte<=NBFS ){
   +      if( nByte<=NBFS && 0 ){
            pMem->z = pMem->zShort;
            memset(pMem->z, 0, nByte);
          }else{

I think it's an alignment problem. Without the patch, sqlite3_aggregate_context() is returning a pointer to a pre-allocated buffer. The problem is (I suspect) that the start of this buffer is not aligned to an 8-byte boundary as required by your architecture.

So I figure an SQLite bug, not a compiler problem.

Will fix shortly.

diff -u -3 -p -r1.130 vdbeInt.h
--- src/vdbeInt.h       28 Aug 2007 23:28:08 -0000      1.130
+++ src/vdbeInt.h       5 Nov 2007 14:39:04 -0000
@@ -123,6 +123,7 @@ struct Mem {
     FuncDef *pDef;      /* Used only when flags==MEM_Agg */
   } u;
   double r;           /* Real value */
+  char zShort[NBFS];  /* Space for short strings */
   sqlite3 *db;        /* The associated database connection */
   char *z;            /* String or BLOB value */
   int n;              /* Number of characters in string value, including '\0' */
@@ -130,7 +131,6 @@ struct Mem {
   u8  type;           /* One of SQLITE_NULL, SQLITE_TEXT, SQLITE_INTEGER, etc */
   u8  enc;            /* SQLITE_UTF8, SQLITE_UTF16BE, SQLITE_UTF16LE */
   void (*xDel)(void *);  /* If not null, call this function to delete Mem.z */
-  char zShort[NBFS];  /* Space for short strings */
 };
 typedef struct Mem Mem;

i'm working from the 3.5.1 (CVS version) of the amalgamation. The code was pulled from CVS sometime around Oct 27th 2007.

Platform: Kubuntu Linux 7.04, i386-32, gcc 4.1.2.

The compiler output says it all:

  gcc -c -DSQLITE_OMIT_FLOATING_POINT sqlite3.c
  sqlite3.c: In function 'bestVirtualIndex':
  sqlite3.c:65531: warning: integer constant is too large for 'long' type
  sqlite3.c: In function 'bestIndex':
  sqlite3.c:65600: warning: integer constant is too large for 'long' type
  sqlite3.c: In function 'sqlite3WhereBegin':
  sqlite3.c:66223: warning: integer constant is too large for 'long' type
  sqlite3.c:66248: warning: integer constant is too large for 'long' type
  sqlite3.c:66254: warning: integer constant is too large for 'long' type

These warnings would seem to indicate potentially serious problems, though i admittedly have not investigated whether overflows are really fatal in the affected contexts.

i'm working from the 3.5.1 (CVS version) of the amalgamation. The code was pulled from CVS sometime around Oct 27th 2007.

The compiler output says it all:

  gcc -c -DSQLITE_OMIT_ALTERTABLE sqlite3.c
  sqlite3.c:6909: warning: 'sqlite3AlterRenameTable' used but never defined
  sqlite3.c:6916: warning: 'sqlite3AlterFinishAddColumn' used but never defined
  sqlite3.c:6917: warning: 'sqlite3AlterBeginAddColumn' used but never defined

The relevant code is:

  sed -ne '6909p;6916p;6917p' sqlite3.c
  SQLITE_PRIVATE void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
  SQLITE_PRIVATE void sqlite3AlterFinishAddColumn(Parse *, Token *);
  SQLITE_PRIVATE void sqlite3AlterBeginAddColumn(Parse *, SrcList *);

Platform: Kubuntu Linux 7.04, i386, gcc 4.1.2.

CREATE TABLE test(Colour VARCHAR COLLATE NOCASE,SubType VARCHAR COLLATE NOCASE,weight DOUBLE, PRIMARY KEY (Colour,SubType));

BEGIN TRANSACTION; INSERT OR REPLACE INTO test VALUES('red', NULL, 100); INSERT OR REPLACE INTO test VALUES('red', NULL, 100); COMMIT

I understand that the NULL behaviour is hazy but this seems to not agree with the doc where it says: "nulls are distinct in a UNIQUE column".

   CREATE TABLE demo(x TEXT COLLATE "binary");

It is using the quote characters as part of the name. The quotes ought to be removed before searching for the collating sequence.

  static void codeReal(Vdbe *v, const char *z, int n, int negateFlag){
    assert( z || v==0 || sqlite3VdbeDb(v)->mallocFailed );
    if( z ){
      double value;
      char *zV;
      sqlite3AtoF(z, &value); //***** can this accidentally go beyond n bytes?
      if( negateFlag ) value = -value;
      zV = dup8bytes(v, (char*)&value);
      sqlite3VdbeOp3(v, OP_Real, 0, 0, zV, P3_REAL);
    }
  }

I was under the impression that both sqlite3AtoF and sqlite3Atoi64 required null terminated strings. Is `z' guaranteed to be null terminated in all cases? Could a random byte follow the number string buffer that happens to be a digit and cause the parsed number to be incorrect?

So at best you could drop the 'n' arg from codeReal and codeInteger, at worst, you'd have to take n bytes into consideration for the sqlite3Ato* functions or less desirable - allocate a buffer and copy the string to it and nil terminate it prior to calling the sqlite3Ato* functions.

I'm not comfortable with this not-a-valid-number-character assumption, but am too lazy to look for a counter example that might break it.

If you're confident that the z[n] byte always exists and reading that byte in sqlite3Ato* will not segfault or cause valgrind to complain, why not do this instead:

   char zn = z[n];
   z[n] = 0;
   ... do sqlite3Ato* stuff
   z[n] = zn;

It's fast, guaranteed to be safe and is more resiliant in the face of future possibly incompatible code changes in the parser.

Could the code benefit from be refactored to be cleaner. Probably so. But that would require hundreds of lines of carefully optimized code to be redesigned and rewritten. I have more important fish to fry right now and so I am not going to waste several days modifying code that works. It is what it is. Either show me a case where it fails or else learn to live with it.

  assert( SQLITE_MAX_SQL_LENGTH < SQLITE_MAX_LENGTH );

as part of the implementations of the OP_String8 and OP_HexBlob opcodes. This assertion is wrong, because the default definition of SQLITE_MAX_SQL_LENGTH is equal to SQLITE_MAX_LENGTH. It should be

  assert( SQLITE_MAX_SQL_LENGTH <= SQLITE_MAX_LENGTH );

Contrast the behavior of these two test files:

testA.c

  #define _FILE_OFFSET_BITS 64
  #include <stdio.h>
  #include <unistd.h>

testB.c

  #include <stdio.h>
  #define _FILE_OFFSET_BITS 64
  #include <unistd.h>

When preprocessed, testA.c defines a 64-bit off_t, testB.c doesn't:

  $ gcc -E testA.c | grep -E 'typedef .* (__)?off(64)?_t;'
  __extension__ typedef long int __off_t;
  __extension__ typedef __quad_t __off64_t;
  typedef __off64_t off_t;

  $ gcc -E testB.c | grep -E 'typedef .* (__)?off(64)?_t;'
  __extension__ typedef long int __off_t;
  __extension__ typedef __quad_t __off64_t;
  typedef __off_t off_t;

this on powerpc-linux (GNU libc 2.6.1). In general the standards (POSIX, SUS) specify that "feature test" macros are only effective if defined before the very first C-library header is included.

  if( vfsList==pVfs ){
    vfsList = pVfs->pNext;
  }else{
    sqlite3_vfs *p = vfsList;
    while( p->pNext && p->pNext!=pVfs ){
      p = p->pNext;
    }
    if( p->pNext==pVfs ){
      p->pNext = pVfs->pNext;
    }
  }

I believe "}else{" should be "}else if ( vfsList != NULL ){"

--disable-threads does not work. it is accepted as a valid option but no defs are added to the makefile -lgcc needs to be included in SHLIB_LD_LIBS pkgIndex.tcl is not built when -DSQLITE_THREADSAFE=0 is added manually, this causes the install target to fail.

Thanks for your help. Regards, Mayura.

   http://www.sqlite.org/support.html

for information on how to receive support for SQLite. Please use these ticketing system to report bugs only. Thanks.

  CREATE TABLE t1(a real);

If I perform a query like:

  select -3.14159265 * a from t1;

then the string "-3.14159265" will be converted to a double 50 million times via OP_Real -> sqlite3VdbeRealValue -> sqlite3AtoF.

  explain select -3.14 * a from t1;
  ...
  5|Real|0|0|-3.14    -- converts string to double each loop pass!
  6|Column|0|0|# t1.a
  7|RealAffinity|0|0|
  8|Multiply|0|0|
  9|Callback|1|0|
  10|Next|0|5|
  ...

This is actually quite computationally expensive and unnecessary. If OP_Real and OP_Int64 would store the double/int64 values into P1/P2 at expression parse time then they would not have to be reparsed to a double/int64 each pass in the loop at vdbe runtime.

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28390.html

http://marc.info/?l=sqlite-users&m=119266402408308&q=p3

Unfortunately pysqlite quite often calls this routine with a null statement and so can crash if you execute an empty query.

The obvious fix is to move the two marked lines below the null check.

static int sqlite3Step(Vdbe *p){ sqlite3 *db; int rc;

  /* Assert that malloc() has not failed */
->  db = p->db;
->  assert( !db->mallocFailed );

  if( p==0 || p->magic!=VDBE_MAGIC_RUN ){
    return SQLITE_MISUSE;
  }

In hindsight, the check for a null statement should perhaps be made in sqlite3_step rather than sqlite3Step. That would be more useful, as it appears to be the case that the mutex check causes an exception (I built the version I first observed this in without mutexes, and then fixed the bug in my copy of pysqlite)

14 seconds originally to just 1 second after this patch.

Assuming the fix is correct, can you please apply it? make test runs with 0 errors with this.

Thanks.

Index: src/expr.c
===================================================================
RCS file: /sqlite/sqlite/src/expr.c,v
retrieving revision 1.313
diff -u -3 -p -r1.313 expr.c
--- src/expr.c  18 Sep 2007 15:55:07 -0000      1.313
+++ src/expr.c  16 Oct 2007 23:54:17 -0000
@@ -1879,7 +1879,10 @@ void sqlite3ExprCode(Parse *pParse, Expr
       assert( pLeft );
       if( pLeft->op==TK_FLOAT || pLeft->op==TK_INTEGER ){
         Token *p = &pLeft->token;
-        char *z = sqlite3MPrintf(pParse->db, "-%.*s", p->n, p->z);
+        char *z = sqlite3_malloc(p->n+2);
+        z[0] = '-';
+        memcpy(z+1, p->z, p->n);
+        z[p->n+1] = 0;
         if( pLeft->op==TK_FLOAT ){
           sqlite3VdbeOp3(v, OP_Real, 0, 0, z, p->n+1);
         }else{

I'm curious, though, what kind of program you are running where this makes a 14x speed improvement. One wonders if you couldn't do 100x faster using sqlite3_bind_double() instead. We could go through and make patches like this one all over the place, in order to make this or that special case run a little faster. But that would make the library footprint larger. The sqlite3MPrintf() call in this context is there for reasons of space efficiency, not CPU time. Some years ago, we when through this huge effort to reduce the footprint of the library by replacing the expanded string construction code like you are inserting with instances of sqlite3MPrintf(). I am reluctant to undo that effort for a single special case that could probably be handled more efficiently using sqlite3_bind_double().

I use the multi-insert patch on the mailing list for tables that hold millions of rows of negative numbers, with anywhere from 3 to 20 columns.

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28337.html

  INSERT INTO BIGTABLE VALUES
   (-334712687065.09, -334712687065.12, -334712687065.13),
   (-334712687065.09, -334712687065.12, -334712687065.13),
   ... 10 thousand rows ...
   (-334712687065.09, -334712687065.12, -334712687065.13),
   (-334712687065.09, -334712687065.12, -334712687065.13);

With the mulit-insert patch and the patch in this ticket, it is 25% faster for inserting than using the equivalent INSERT commands within a transaction:

  BEGIN
  INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13);
  ... 10 thousand rows ...
  INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13);
  COMMIT;

I have to have no more than 10,000 rows per insert because it blows the stack due to sqlite3Select -> multiSelect recursion. I had to bump a few other sqliteLimits as well to get more than a few thousand rows at a time.

I suppose the replacement of the sqlite3MPrintf could be useful in other scenarios as well.

the code #if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) && !winceCreateLock(zFilename, pFile)

seems to be

#if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) && !winceCreateLock(zName, pFile)

Best regards, Yuri Noyanov

The symbol @X is also undocumented. From a source code comment I read that it "If the argument is of the form @X then substituted the token number of X, not the value of X". A short documentation example would help to understand where and how it can be useful to apply this syntax.

Are there other nice but undocumented Lemon goodies lacking documentation?

This is useful where you have a need to index large strings or blobs and the size penalty of having 2+ copies ends up being very expensive.

Consider the cost of the implied index on:

create table t1 ( c1 BLOB UNIQUE);

and store 100s of 1MB objects. The raw file ends up being twice as large as you ideally would like it to be.

WHEN to use such indexes isn't entirely clear to me, not is the syntax for doing it in general, 30s of thought I can come up with:

  create table t1 ( c1 BLOB &UNIQUE);

and say

  create index idx1 on t2(c1, &c2, c3);

(meaning c2 would be done via reference, c1 & c3 as copies of the data). It's FAR from clear what other databases do here (I didn't look).

This would also be useful in cases where I have multiple indexes on tables like email (headers) and values which can be very large (100s of bytes in some cases (Subject:) and I might have 5 or more indexes, means a 100-byte column will take over 600 bytes (+ padding)). Allowing for references would be slower in some cases but faster in others because of the smaller footprint and much grater CPU cache utilization.

Also, the random access to pages to check indexes constraints (unique indexes, also primary keys) will trash the entire database performance.

If you need small index disk usage, consider using hashes to your data keys. it&#180;s the best that you could do to solve your problem.

I wonder about a virtual index concept, where you can define a function that takes the column value and returns something to actually store in the index?

I'm told oracle has this feature, so whilst it's non-standard there is some precedent (albeit in a very different space).

typical strangeness is that SQLITE_FULL is returned from sqlite3_prepare_v2() (called immediately after a statement was created with sqlite3_mprintf). This happens even though there is a reasonable amount of disk space free (6G).

the compile line looks like:
cc -g -pg -O1 -march=i686 -msse2 -malign-double -m128bit-long-double -momit-leaf-frame-pointer -minline-all-stringops -D_XOPEN_SOURCE=520 '-DVERSION="0.10r276M"' -Isqlite/ -DSQLITE_OMIT_LOAD_EXTENSION -DTHREADSAFE=0 -DSQLITE_OMIT_EXPLAIN -DSQLITE_ENABLE_COLUMN_METADATA -c -o sqlite/sqlite3.o sqlite/sqlite3.c

and valgrind reports:
==15323== Use of uninitialised value of size 4
==15323== at 0x80585B7: insertElement (sqlite3.c:13072)
==15323== by 0x807366E: sqlite3HashInsert (sqlite3.c:13290)
==15323== by 0x809FE72: unixOpen (sqlite3.c:15403)
==15323== by 0x80579A7: sqlite3OsOpen (sqlite3.c:8210)
==15323== by 0x806B12F: sqlite3BtreeFactory (sqlite3.c:21317)
==15323== by 0x80767BA: openDatabase (sqlite3.c:71237)
==15323== by 0x8076BD1: sqlite3_open (sqlite3.c:71337)
==15323== by 0x804BEAE: db_init (dbmgr.c:81)
==15323== by 0x804CE64: main (main.c:59)

my dbmgr.c:81:db_init() is: res=sqlite3_open(filename, &system_db);

and system_db=NULL and filename="zomg.sqlite" (string literal). so the parameters seem normal. if I turn off -malign-double everything works fine. (this "bug" also seems to be on 3.4.1 amalgamation)

% sed -e 176\!d /usr/include/sys/types.h{linebreak} typedef enum { B_FALSE, B_TRUE } boolean_t;

% sed -e 111\!d tool/lemon.c
typedef enum {B_FALSE=0, B_TRUE} Boolean;

even when the enum is only set when it is not yet defined, I guess this results in conflicts in the code because Boolean may not equal boolean_t.

I suggest to rename B_FALSE and B_TRUE to something which makes it unique for the sqlite case.

flags = SQLITE_OPEN_READWRITE;

and when

  res = sqlite3_open_v2(sourcename, &conn, flags, NULL);

seems to leak 674 bytes per call

All calls to sqlite3_open_v2() need to be matched by a call to sqlite3_close(), even if an error occurs.

void (result_error_toobig)(sqlite3_context);

#define sqlite3_result_error_toobig sqlite3_api->result_error_toobig

Without these, externally loadded libs that would like to use error_toobig will not load into sqlite

   .header on

And the headers will appear.

However, upon implementing this, we found that the pointers passed to our collation function are not wide character (short-) aligned. On our platform we receive data mis-alignment errors when comparing the strings.

We have already implemented similiar sqlite wide string functions (WLIKE, WCONTAINS), and we never receive mis-aligned strings there (are those guaranteed to be aligned); it would be helpful if this restriction could be applied to collation callbacks.

For example (with defined UNICODE):

TCHAR sql[512];
_stprintf(sql, _T("INSERT INTO tab_SurveyedPoints (name, comment, code,")
		_T("coordinatetype, b, l, h, solutiontype, sigmah, sigmav)")
		_T(" VALUES ('%s','%s','%s',0,%lf,%lf,%lf,0,%lf,%lf);"),
		point.m_name.c_str(), point.m_description.c_str(), point.m_code.c_str(),
		point.m_coordinates.b, point.m_coordinates.l, point.m_coordinates.h,
		point.m_sigmah, point.m_sigmav);
int rc1 = sqlite3_prepare16(m_db, sqlfmt, -1, &stmt, (const void**)&pszTail);
rc != SQLITE_OK

But if I move the file to c:\My documents\Data_Jobs this works ok.

It's improbable behaviour, but I can't work around yet. Although, prepare() functions work ok as well in both cases.

Yuri Noyanov.

sqlite_prepare16() with the same code either works OK either doesn't work. depends on database filename or folder path. The database is opened OK in both cases (I used utf8 conversion). sql_prepare() works ok in both cases.

There have been many patches to the winCE code since 3.5.1 was released. The writer of this ticket previously reported two other problems that have already been fixed. Several other problems have also been fixed since 3.5.1. We are guessing that this "Can't write" problem will probably go away if the writer will simply get the latest code out of CVS. If not, then there is nothing really we can do about it unless we have patches to fix the problem.

  /* WinCE has no concept of a relative pathname, or so I am told. */

  sqlite3_snprintf(pVfs->mxPathname, zFull, "%s", zRelative);<br>

#endif

This doesn't seem to be correct unless SQLITE_OMIT_LOAD_EXTENSION is defined. If this value is not defined, then the function pointers are assigned in a structure (sqlite3_apis). As stated, they are not used by SQLite proper, but must be defined. In this case, I am unable to link without either defining the functions or building with SQLITE_OMIT_LOAD_EXTENSION defined.

No. If your adversary has the ability to invoke the interface that removes an authorizer, then you system is already pwned.

What you really need is the ability to [interp alias] the eval method into a safe interpreter. That way you can:

• Open the database in the main interpreter
• Set up the authorizer in the main interpreter to invoke a script in the main interpreter
• Set up the [interp alias] so that the safe interpreter can do [db eval ...] but not [db auth ...]

It seems like an "-interp" option on the "eval" method of the database connection object would likely be the right interface. Or perhaps there should be separate "safeeval" method. Either way, it has been years and years since I have done anything with safe interpreters so I will have to look into what needs to be done to make that happen.

currently you cannot use an interp alias since the target command runs in the target interp and all your variables and commands are in the wrong scope. this means one needs to load sqlite again in the new interp, and sqlite will not load in a safe interp so a regular interp is required.

to be useful, a -interp flag would need to execute in the current scope of the interp and not the global scope.

Putting #define _FILE_OFFSET_BITS 64 before the #include statements in shell.c fixes this problem under Linux.

I tested this solution with a 2.6 GB file under Ubuntu Feisty Fawn 7.04 with Linux kernel 2.6.20-16-generic.

  #define _FILE_OFFSET_BITS 64

isn't necessarily correct, better to tweak the makefile(s):

  CFLAGS += $(shell getconf LFS_CLFAGS)

(similar for LDFLAGS) should work on 32/64 bit machines and different platforms.

For shell.c I really wouldn't bother and think this should be left alone. Using .import for load more than 2GB makes me things the shell should be fleshed out and extended for this sort of abuse, handy as it is right now, it's not a full SQL shell but a minimalist shell and a useful example).

With all due respect, I don't understand the reluctance to enable a useful feature. Shouldn't the user decide whether it is reasonable for his application to import big files? I work in the health care industry processing insurance claims for several very large companies. There's nothing broken or wrong with my application -- big files are just the nature of the data I'm handling.

Neither of you has proposed any disadvantage to enabling LFS in the shell. If there are no drawbacks, why not turn it on? If the core library can open large database files, why shouldn't the shell be able to import large ones?

src/vdbe.c: In function `sqlite3VdbeExec': src/vdbe.c:1136: error: duplicate case value src/vdbe.c:921: error: previously used here src/vdbe.c:1655: error: duplicate case value src/vdbe.c:900: error: previously used here ...

Seems like my generated opcodes.h has lots of duplicates:

#define OP_Multiply 80 /* same as TK_STAR / #define OP_Pull 80 #define OP_Dup 71 #define OP_Lt 71 / same as TK_LT */

I'm running on XP with a c:\cygwin library available.

NB. i'm not a regular SQLLite user and was just trying the library out of curiosity so if no one else reports this then don't sweat it... it may be down to my environment screwing up the build process.

   --- mkopcodeh.awk-orig  2007-03-29 19:39:30.000000000 +0100
   +++ mkopcodeh.awk       2007-10-12 15:14:46.980260800 +0100
   @@ -41,7 +41,9 @@

    # Remember the TK_ values from the parse.h file
    /^#define TK_/ {
   -  tk[$2] = $3
   +  num = $3
   +  num += 0 # convert "22 " -> 22
   +  tk[$2] = num
    }

    # Scan for "case OP_aaaa:" lines in the vdbe.c file

ie. ensure that the keys are numeric.

With this patch, the build completes ok.

Thanks.

Cygwin uses the same version of GNU gawk as Linux. It's the exact same code.

It is intended to maximize disk IOs by splitting IO concerning the journal and the IO concerning the main DB storage. Typically we dedicate a specific disk channel for the journal, which may be a RAM disk like iRAM from Gigabyte, or a SATA2 disk with 16MB cache dedicated, or using a RAID controller with 256MB RAM with Backup Unit. The main DB storage resides on a different channel on SAS disks configured as RAID10. Separating journal and main data file is typically a feature available in common SQL servers : MySQL, MSSQL, ...

if( dwFlagsAndAttributes & FILE_FLAG_DELETE_ON_CLOSE ){ pFile->zDeleteOnClose = zConverted; }else ...

Since that flag is no longer set, it needs to read:

if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL | SQLITE_OPEN_SUBJOURNAL) ){ pFile->zDeleteOnClose = zConverted; }else ...

Could Lemon be altered to emit the %if/%ifndef/%else/%endif's as their C preprocessor equivalents instead of just not generating the if'd out parse code? This way the amalgamation could work with all OMITs.

If you want to use OMIT macros, then you need to compile from the canonical source code - not the amalgamation.

  Why do you think it is SQLite error ??

An error saving the context: NSError "An error occurred while saving." Domain=NSCocoaError Domain Code=134030 UserInfo={NSUnderlyingException = disk I/O error; }

    Why do you think it is SQLite error ??

I used the attached script to create a 5.5 GB database on HFS+, then checked it using the integrity check and everything seemed Ok.

If it's not a simple file-system limitation, is is possible for you to post some code that demonstrates the issue? Thanks.

Here is the patch solving all(for me) filesystem errors on WindowsCE.

sqlite-amalgamation-3_5_1.ce.patch
----------------------------------
--- ./sqlite3.c.orig	Wed Oct  3 18:08:44 2007
+++ ./sqlite3.c	Mon Oct  8 17:04:16 2007
@@ -18730,17 +18730,24 @@
   }
   if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
                     | SQLITE_OPEN_SUBJOURNAL) ){
+#if OS_WINCE
+    dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN;
+#else
     dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY
                                | FILE_ATTRIBUTE_HIDDEN
                                | FILE_FLAG_DELETE_ON_CLOSE;
+#endif
   }else{
     dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL;
   }
   if( flags & (SQLITE_OPEN_MAIN_DB | SQLITE_OPEN_TEMP_DB)){
     dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS;
-  }else{
+  }
+#if !OS_WINCE
+  else{
     dwFlagsAndAttributes |= FILE_FLAG_SEQUENTIAL_SCAN;
   }
+#endif
   if( isNT() ){
     h = CreateFileW((WCHAR*)zConverted,
        dwDesiredAccess,
@@ -18786,7 +18793,7 @@
 #if OS_WINCE
   if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) ==
                (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)
-       && !winceCreateLock(zFilename, pFile)
+       && !winceCreateLock(zName, pFile)
   ){
     CloseHandle(h);
     free(zConverted);
@@ -18957,6 +18964,7 @@
 #if OS_WINCE
   /* WinCE has no concept of a relative pathname, or so I am told. */
   sqlite3_snprintf(pVfs->mxPathname, zFull, "%s", zRelative);
+  return SQLITE_OK;
 #endif

 #if !OS_WINCE && !defined(__CYGWIN__)

    insert into test (a, b, c) values (1, 2, 3);

MySQL allows to insert multiple with

    insert into test (a, b, c) values (1, 2, 3), (4, 5, 6), (7, 8, 9) -- etc

But SQLite is also capable of inserting multiple by using INSERT...SELECT:

    insert into test (a, b, c) select 1, 2, 3 union select 4, 5, 6 -- etc

It would be nice to make INSERT statement syntactically compatible with MySQL, allowing to insert multiple rows with VALUES clause.

It can be implemented by simply translating multiple 'VALUES ()()()' to 'select union' - no serious change required at all.

UNION would remove duplicate rows, and create an ephemeral table that you don't want because it's less efficient.

Your idea is a good one and could be implemented largely in the parser. The number of VDBE opcodes would be quite large for such a statement. I wonder if that would present a problem.

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28337.html

  --- os_win.c-orig       2007-10-07 22:04:02.000000000 +0200
  +++ os_win.c    2007-10-07 23:32:51.000000000 +0200
  @@ -1162,7 +1162,7 @@
   #if OS_WINCE
     if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) ==
                  (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)
  -       && !winceCreateLock(zFilename, pFile)
  +       && !winceCreateLock(zName, pFile)
     ){
       CloseHandle(h);
       free(zConverted);

"Forcibly adding FILE_FLAG_RANDOM_ACCESS to the flags in os_win.c's winOpen fixed it. It looks like in 3.4.2, that flag was set on all files (journals and db's), while in 3.5, it (was) only being set on db's. Adding it to journals too improves performance almost tenfold. This may also help performance on desktop Windows."

char filename[MAX_PATH]; memset(filename, 0, sizeof(filename)); ::WideCharToMultiByte(CP_ACP, 0, jobfilename.c_str(), -1, filename, sizeof(filename)-1, 0, 0); int dbcreateres = sqlite3_open_v2(filename, &m_db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

dbcreateres == 14

changing the code to ::WideCharToMultiByte(CP_UTF8, ... fixes the issue... sorry for your time...