sqlite - Recently changed and active tickets w/descriptions

I have a big table in my DB. One column is a number which, in my case, can be either 0 or 1

The statement

update info set new=0 where type='book'

always takes about 60 seconds to run (even though 'type' is indexed)

If I change the statement to

update info set new=0 where type='book' and new=1

it's usually almost instantaneous.

It looks as if SQLite is rewriting the row even though no actual data is being changed, so maybe the update code could be optimized to only rewrite the row if necessary.

I ported the database to mysql running on the same host, and the request takes a couple of seconds after having set the variable group_concat_max_len to a "decent" value (200,000 or so). See attached database for details.

.schema

SELECT * FROM slow_concat;

10 mins for SQLite

2.5 secs for MySQL

Quick hack ==> replacing line 770 of file printf.c with something like this might do the trick:

p->nAlloc = (int)szNew + (int)szNew % 8192;

p->nAlloc = ((int)szNew + 4095) & ~4095;

Not sure small gadgets with lowmem will enjoy this change though...

    /* Space to hold the rollback journal is allocated in increments of
    ** this many bytes.
    **
    ** The size chosen is a little less than a power of two.  That way,
    ** the FileChunk object will have a size that almost exactly fills
    ** a power-of-two allocation.  This mimimizes wasted space in power-of-two
    ** memory allocators.
    */
    #define JOURNAL_CHUNKSIZE ((int)(1024-sizeof(FileChunk*)))

The problem with this design decision, is that the memory journal almost always is written in 1024 (or page size) chunks, Since only 1020 bytes are available, there will almost always be at least 2 chunks needed with every read/write.

Recommend that testing be done for various sizes, and that it be made pagesize dependent. Informal testing shows that a larger chunk size, even up to 4* page size reduces churning in the memjrnlRead & memjrnlWrite routines

diff -u date.c.orig date.c

--- date.c.orig 2009-06-25 15:24:38.000000000 +0400
+++ date.c      2009-08-03 15:57:21.000000000 +0400
@@ -844,6 +844,7 @@
 **   %w  day of week 0-6  sunday==0
 **   %W  week of year 00-53
 **   %Y  year 0000-9999
+**   %y  year 00-99
 **   %%  %
 */
 static void strftimeFunc(
@@ -883,6 +884,9 @@
         case 'Y':
           n += 8;
           break;
+        case 'y':
+          n += 4;
+          break;
         case 's':
         case 'J':
           n += 50;
@@ -968,6 +972,10 @@
           sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
           break;
         }
+        case 'y': {
+          sqlite3_snprintf(3,&z[j],"%02d",x.Y % 100); j+=sqlite3Strlen30(&z[j]);
+          break;
+        }
         default:   z[j++] = '%'; break;
       }
     }

Exemple:
UPDATE tb SET field = newFieldValue;
takes 1082 ms and performs many written to disk!

And...
UPDATE tb SET field = newFieldValue WHERE field != newFieldValue;
takes 14 ms and makes little written to disk!

Someone will tell me to use the second form but, the second form will be more slow if 'newFieldValue' is a subselect or something else.

  do_test lock3-1.1 {
    file mkdir tempdir/t1/t2/t3
    sqlite3 db2 ./tempdir/t1//t2/./t3//./../..//./../../tempdir/..//test.db//

suggest replacing it with

  do_test lock3-1.1 {
    sqlite3 db2 test.db

the same problem can be created from the commandline tool running under Vista

  Y:\SQLite3\Tools>dir tempdir\t1\t2\t3
   Volume in drive Y is Personal
   Volume Serial Number is BAC6-795F

  Y:\SQLite3\Tools>sqlite3 ./tempdir/t1//t2/./t3//./../..//./../../tempdir/..//test.db//
  SQLite version 3.6.16
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> create table t1 (c1);
  Unable to open database "./tempdir/t1//t2/./t3//./../..//./../../tempdir/..//test.db//": unable to open database

  Y:\SQLite3\Tools>sqlite3 ./tempdir/t1//t2/./t3//./../..//./../../tempdir/..//test.db
  SQLite version 3.6.16
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> create table t1 (c1);
  sqlite> .quit

  SQLite version 3.6.16
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> create table t(a);
  sqlite> insert into t values(1);
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> select * from (select * from t limit 1); -- this is fast
  1
  sqlite> select * from (select * from t limit 1) order by a; -- this is slow
  1

  SELECT * FROM t ORDER BY a LIMIT 1

which is a valid transformation, but not a productive one.

For example given:

  create table t(id, a text);

This query produces the correct result but evaluates the length function twice for each row.

  select a, length(a) as len
  from t where len < 100;

However this query produces incorrect results.

  select a, random() as rnd
  from t where rnd < 100;

Most of the results will display a value for rnd that is larger than 100, in violation of the where clause.

select random() from t order by 1; -- same with group by

This behaviour already caused implementation problems: #2251

SQLite version 3.3.8
Enter ".help" for instructions
sqlite> create table t(a);
sqlite> explain select distinct random() from t;
0|OpenEphemeral|1|0|keyinfo(1,BINARY)
1|Goto|0|16|
2|Integer|0|0|
3|OpenRead|0|2|
4|SetNumColumns|0|0|
5|Rewind|0|14|
6|Function|0|0|random(-1)
7|MakeRecord|-1|0|
8|Distinct|1|11|
9|Pop|2|0|
10|Goto|0|13|
11|IdxInsert|1|0|
12|Callback|1|0|
13|Next|0|6|
14|Close|0|0|
15|Halt|0|0|
16|Transaction|0|0|
17|VerifyCookie|0|1|
18|Goto|0|2|
19|Noop|0|0|

SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a);
sqlite> explain select distinct random() from t;
0|Trace|0|0|0||00|
1|OpenEphemeral|1|2|0|keyinfo(1,BINARY)|00|
2|Integer|0|2|0||00|
3|Integer|0|1|0||00|
4|Gosub|4|33|0||00|
5|Goto|0|35|0||00|
6|OpenRead|0|2|0|0|00|
7|Rewind|0|13|0||00|
8|Function|0|0|7|random(0)|00|
9|Sequence|1|8|0||00|
10|MakeRecord|7|2|9||00|
11|IdxInsert|1|9|0||00|
12|Next|0|8|0||01|
13|Close|0|0|0||00|
14|Sort|1|34|0||00|
15|Column|1|0|6||00|
16|Compare|5|6|1|keyinfo(1,BINARY)|00|
17|Jump|18|22|18||00|
18|Move|6|5|1||00|
19|Gosub|3|28|0||00|
20|IfPos|2|34|0||00|
21|Gosub|4|33|0||00|
22|Integer|1|1|0||00|
23|Next|1|15|0||00|
24|Gosub|3|28|0||00|
25|Goto|0|34|0||00|
26|Integer|1|2|0||00|
27|Return|3|0|0||00|
28|IfPos|1|30|0||00|
29|Return|3|0|0||00|
30|Function|0|0|10|random(0)|00|
31|ResultRow|10|1|0||00|
32|Return|3|0|0||00|
33|Return|4|0|0||00|
34|Halt|0|0|0||00|
35|Transaction|0|0|0||00|
36|VerifyCookie|0|1|0||00|
37|TableLock|0|2|0|t|00|
38|Goto|0|6|0||00|

Subsequently, the functions sqlite3_errcode() and sqlite3_errmsg() should return the correct appropriate error values as well, which they don't - instead something not matching the error is returned. One has to call sqlite3_reset() to get the correct values which should be unnecessary.

See the example output below :

  sqlite3_step: result rc = 19, errcode = 1, errmsg = SQL logic error or missing database
  sqlite3_reset: result rc = 19, errcode = 19, errmsg = PRIMARY KEY must be unique

Code for this example:

  #include <stdio.h>
  #include <stdlib.h>
  #include <unistd.h>
  #include <string.h>
  #include <sqlite3.h>

  int
  main(int argc, char **argv)
  {
      sqlite3 *db;
      int rc, errcode;
      sqlite3_stmt *stmt;
      const char *pztail;
      char *query;
      const char *errmsg;

      rc = sqlite3_open("test.db", &db);
      rc = sqlite3_exec(db, "create table t (x integer not null primary key)", NULL, NULL, NULL);
      rc = sqlite3_exec(db, "insert into t values(1)", NULL, NULL, NULL);
      query = "insert into t values(?)";
      rc = sqlite3_prepare_v2(db, query, strlen(query), &stmt, &pztail);
      if( rc!=SQLITE_OK ){
          fprintf(stderr, "SQL error: %d\n", rc);
          exit(1);
      }
      rc = sqlite3_bind_int(stmt, 1, 1);
      if( rc!=SQLITE_OK ){
          fprintf(stderr, "SQL error: %d\n", rc);
          exit(1);
      }
      rc = sqlite3_step(stmt);
      errcode = sqlite3_errcode(db);
      errmsg = sqlite3_errmsg(db);
      printf("sqlite3_step: result rc = %d, errcode = %d, errmsg = %s\n",
          rc, errcode, errmsg);
      rc = sqlite3_reset(stmt);
      errcode = sqlite3_errcode(db);
      errmsg = sqlite3_errmsg(db);
      printf("sqlite3_reset: result rc = %d, errcode = %d, errmsg = %s\n",
          rc, errcode, errmsg);
  }

  sqlite3_step: result rc = 19, errcode = 19, errmsg = constraint failed
  sqlite3_reset: result rc = 19, errcode = 19, errmsg = PRIMARY KEY must be unique

That is, the errcode is propagated but the errmsg is not. I have attached a patch which propagates the errmsg as well:

  sqlite3_step: result rc = 19, errcode = 19, errmsg = PRIMARY KEY must be unique
  sqlite3_reset: result rc = 19, errcode = 19, errmsg = PRIMARY KEY must be unique

The patch passes the regression tests but should be sanity-checked by someone who knows the SQLite codebase.

On the draw_loop function, mxx will not be set if it you pass a graph like this: "x {optx {loop a b}}". I'm setting mxx to 0 at the top of this function for now.

The second issue I found lives in the draw_stack function, here, the bypass_x variable is used multiple times before being defined. I don't have any graphs that cause trouble here, but I though it would be nice to point it.

When the amalgamation is generated through mksqlite3c.tcl, trims out repeated #include lines. However, if this #include occurs within a #if... block, then the include file is only included when the conditional compile dictates. The example of this is ctype.h. In version 3.6.13, a #include <ctype.h> exists inside an #if block which only compiles if SQLITE_ASCII is not defined. However, ctype.h is required for the FTS3 functions but the #include line that is part of this source file is trimmed out because the build script thinks it has already been included.

For my build, I have simply patched the way mksqlite3c.tcl works (see patch below) so that it only marks an include file as "seen" if it seen at the top level - i.e. not inside an #if block. Its probably not the best solution, and almost certainly not the best tcl code, but it solves the problem for me.

P.S. I'm afraid I can't get the formatting right on the patch file, so let me know if you want me to email it instead.

Patch:

The default behavior is an external sort intended to handle an arbitrarily large number of rows. We'd like to see - more aggressive in memory sorting - special case common sorting collations like simple integers, or strings with vanilla binary collations

  sqlite3_enable_shared_cache 1

  file delete -force test.db

  sqlite3 db test.db
  db eval {
    CREATE TABLE t1(a, b, c);
    INSERT INTO t1 VALUES(1, 2, 3);
  }
  db close

  sqlite3 dbr test.db -readonly 1
  dbr eval { SELECT * FROM t1 }

  sqlite3 dbw test.db -readonly 0
  dbr close

  # This throws an "attempt to write a read-only database" exception.
  #
  dbw eval { INSERT INTO t1 VALUES(4, 5, 6) }

  SQLite version 3.6.11
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> CREATE TABLE t(a);
  sqlite> SELECT * FROM t ORDER BY random(); -- this works
  sqlite> SELECT * FROM t UNION ALL SELECT * FROM t ORDER BY a; -- this works
  sqlite> SELECT * FROM t UNION ALL SELECT * FROM t ORDER BY random(); -- this doesn't
  SQL error: 1st ORDER BY term does not match any column in the result set

There's no reason for an error in last query

As an extension to SQL, SQLite allows ORDER BY terms to be arbitrary expressions on an simple query. This is a common extension implemented by many SQL database engines. But SQLite does not implement this extension for compound queries.

We will convert this ticket an enhancement request.

I'm attaching a patch that disables the optimization when LIMIT is used. (The patch is meant for demonstration purposes only, though it may well be a sufficient solution.) With the patch, the SELECT below takes 0.02 seconds, about the time it takes using versions 3.5.2 and earlier.

  SELECT
    DISTINCT
    j2.Value, j4.Value
    FROM tblFamily

    LEFT JOIN ndxParamValues_LocalizedString AS j1
      ON j1.FamilyId = tblFamily.FamilyId AND j1.ParamId = 1

    LEFT JOIN ndxLocalizedValues AS j2
      ON j2.UniqueID  = j1.Value AND j2.ParamId = 1

    LEFT JOIN ndxParamValues_LocalizedString AS j3
      ON j3.FamilyId = tblFamily.FamilyId AND j3.ParamId = 13

    LEFT JOIN ndxLocalizedValues AS j4
      ON j4.UniqueID  = j3.Value AND j4.ParamId = 13

    LIMIT 100;

EDIT: Test database available at http://cid-6c4c1a7c77d5eb1a.skydrive.live.com/self.aspx/Public/test.db.gz .

EDIT: Database on SkyDrive was missing. I added it again. I'm also changing the version for the ticket to 3.6.11, the latest version exhibiting the behavior.

  $ ./sqlite3 tmp.db
  SQLite version 3.6.11
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> create table t1( a int, b int);
  sqlite> create index t1_i on t1( a, b);
  sqlite> explain query plan select distinct a, b from t1;
  0|0|TABLE t1 WITH INDEX t1_i ORDER BY
  sqlite> explain query plan select distinct b, a from t1;
  0|0|TABLE t1

sqlite> .timeout 10000

sqlite> .restore /tmp/test.db

source database is busy

static void softHeapLimitEnforcer( void *NotUsed, sqlite3_int64 inUse, int allocSize)

It seems that the inUse parameter is now 'NotUsed2'.

The problem is this: the enforcer releases only as much memory as requested. This results in many, many calls when in tight situations, to the point of consuming most of the processing time. The routine should release slightly more memory than requested. In our tests, we found this to be very effective:

if (allocSize < 256*1024) { allocSize += inUse/8; } sqlite3_release_memory(allocSize);

I do want to report a bug because presumably, someone cares.

I am using SQLite to crunch data for use in a spreadsheet.

I run this sql from a file into sqlite:

  -- Drop existing tables
  DROP TABLE TMPDATA1 ;
  DROP TABLE TMPDATA2 ;
  -- Create main data table
  CREATE TABLE TMPDATA1 (
  mth char(8),
  tid  char(8),
  hrs numeric,
  atof char(1)
  );
  -- Create employee <-> team / tower lookup table
  CREATE TABLE TMPDATA2 (
  tid char(8) primary key,
  emp varchar(40),
  team varchar(40),
  twr varchar(40),
  mth char(8)
  );
  -- Set default options: use comma as a delimiter, turn headers on
  .mode list
  .separator ","
  .headers on
  -- Route messages to stdout (handy way to close a file)
  .output stdout
  -- Import data from csv files
  .import "TPOct2Mar.csv" tmpdata1 ;
  .import "EmpLookupTable - Lyndy.csv" tmpdata2 ;
  -- Drop any existing FTEDATA views
  DROP VIEW FTEDATA;
  -- Create the FTEDATA view - Month, Tower, TeamplayID, Category, Hours
  CREATE VIEW FTEDATA AS
  select
    a.mth	as mth
  , b.team as tea
  , b.twr	as twr
  , a.tid	as tid
  , a.atof as atof
  , a.hrs	as hrs
  from tmpdata1 a, tmpdata2 b
  where a.tid = b.tid
  and	b.mth like '%'||a.mth||'%'
  ;
  -- Drop any existing EMPDATA views
  DROP VIEW EMPDATA;
  -- Create the EMPDATA view - month, tower, no of employees
  CREATE VIEW EMPDATA AS
  select
    m as mth
  , g as tea
  , t	as twr
  , count(i) as cnt
  from
  (select distinct mth as m, tea as g, twr as t, tid as i from ftedata
  group by mth, tea, twr, tid)
  group by m, g, t
  ;
  -- Total hours by Month, Tower and Category
  .output atof.csv
  select Team, Tower, Month, Category, (Hours/TotalHours*HeadCount) As DerivedFTE from
  (
  select
    a.tea as Team
  , a.twr as Tower
  , a.mth as Month
  , a.cnt as HeadCount
  , b.atof as Category
  , sum(b.hrs) as Hours
  , (select sum(z.hrs) from ftedata z where a.twr = z.twr and a.mth = z.mth group by z.twr, z.mth) as TotalHours
  from empdata a, ftedata b
  where a.twr = b.twr
  and	a.mth = b.mth
  group by a.tea, a.twr, b.atof, a.mth
  order by a.tea, a.twr, b.atof, a.mth
  )
  ;
  -- Total headcount by Month and Tower
  .output headcount1.csv
  select distinct
    tea as Team
  , twr as Tower
  , sum(case mth when "Oct" then cnt else null end) as Oct
  , sum(case mth when "Nov" then cnt else null end) as Nov
  , sum(case mth when "Dec" then cnt else null end) as Dec
  , sum(case mth when "Jan" then cnt else null end) as Jan
  , sum(case mth when "Feb" then cnt else null end) as Feb
  , sum(case mth when "Mar" then cnt else null end) as Mar
  , sum(case mth when "Apr" then cnt else null end) as Apr
  , sum(case mth when "May" then cnt else null end) as May
  , sum(case mth when "Jun" then cnt else null end) as Jun
  , sum(case mth when "Jul" then cnt else null end) as Jul
  , sum(case mth when "Aug" then cnt else null end) as Aug
  , sum(case mth when "Sep" then cnt else null end) as Sep
  from empdata
  group by tea, twr
  ;
  -- Total headcount by Month and Team
  .output headcount2.csv
  select distinct
    tea as Team
  , sum(case mth when "Oct" then cnt else null end) as Oct
  , sum(case mth when "Nov" then cnt else null end) as Nov
  , sum(case mth when "Dec" then cnt else null end) as Dec
  , sum(case mth when "Jan" then cnt else null end) as Jan
  , sum(case mth when "Feb" then cnt else null end) as Feb
  , sum(case mth when "Mar" then cnt else null end) as Mar
  , sum(case mth when "Apr" then cnt else null end) as Apr
  , sum(case mth when "May" then cnt else null end) as May
  , sum(case mth when "Jun" then cnt else null end) as Jun
  , sum(case mth when "Jul" then cnt else null end) as Jul
  , sum(case mth when "Aug" then cnt else null end) as Aug
  , sum(case mth when "Sep" then cnt else null end) as Sep
  from empdata
  group by tea
  ;
  -- Quit SQLite
  .exit

I checked the contents of my FTEDATA table and there are figures for all teams for all months, however...

This query produces no results for one of the Teams for the month of February only:

  select Team, Tower, Month, Category, (Hours/TotalHours*HeadCount) As DerivedFTE from
  (
  select
    a.tea as Team
  , a.twr as Tower
  , a.mth as Month
  , a.cnt as HeadCount
  , b.atof as Category
  , sum(b.hrs) as Hours
  , (select sum(z.hrs) from ftedata z where a.twr = z.twr and a.mth = z.mth group by z.twr, z.mth) as TotalHours
  from empdata a, ftedata b
  where a.twr = b.twr
  and	a.mth = b.mth
  group by a.tea, a.twr, b.atof, a.mth
  order by a.tea, a.twr, b.atof, a.mth
  )
  ;

When I limit it to only include "Feb" though (see below), magically the figures are produced.

  select Team, Tower, Month, Category, (Hours/TotalHours*HeadCount) As DerivedFTE from
  (
  select
    a.tea as Team
  , a.twr as Tower
  , a.mth as Month
  , a.cnt as HeadCount
  , b.atof as Category
  , sum(b.hrs) as Hours
  , (select sum(z.hrs) from ftedata z where a.twr = z.twr and a.mth = z.mth group by z.twr, z.mth) as TotalHours
  from empdata a, ftedata b
  where a.twr = b.twr
  and	a.mth = "Feb"
  group by a.tea, a.twr, b.atof, a.mth
  order by a.tea, a.twr, b.atof, a.mth
  )
  ;

I have scrutinised the data and there are no errors there.

I originally developed this under 3.5.9 and the behaviour exists in that release. I tried 3.6.10 and it is there also.

You can mail it to "dan AT sqlite.org" if it is too large to attach here or you don't want it to be publicly available.

My `uname -a` is: Darwin iBook12.local 8.11.0 Darwin Kernel Version 8.11.0: Wed Oct 10 18:26:00 PDT 2007; root:xnu-792.24.17~1/RELEASE_PPC Power Macintosh powerpc

First problem: When running ./configure, it first says

  checking for correct TEA configuration... warning: requested TEA version "3.6", have "3.5"

AFAICT, the TEA version provided in the tarball is indeed 3.5 (tclconfig/tcl.m4 says dnl TEA_VERSION="3.5") but the code also requires 3.6 (e.g. configure.in says TEA_INIT([3.6])), so apparently this distro isn't quite in sync with itself.

Running ./configure also produces the warning

  config.status: WARNING:  'Makefile.in' seems to ignore the --datarootdir setting

which I have no idea whether it makes any difference, but I haven't seen it when building anything else (such as Tcl itself) before.

Second: Running make, I get a lot of warnings like

  ./generic/tclsqlite3.c: In function 'dotlockUnlock':
  ./generic/tclsqlite3.c:23577: warning: 'rc' may be used uninitialized in this function
  ./generic/tclsqlite3.c: In function 'plrStep':
  ./generic/tclsqlite3.c:90178: warning: 'i' may be used uninitialized in this function
[snip]
  ./generic/tclsqlite3.c: At top level:
  ./generic/tclsqlite3.c:96697: warning: 'sqlite3Fts3IcuTokenizerModule' declared 'static' but never defined

but I learnt from the FAQ that this was more or less to be expected, and shouldn't be a concern since SQLite has a thorough test suite, so no big deal here. Make finishes without error, but ends with writing:

  : libsqlite3.6.10.dylib
  If you have documentation to create, place the commands to
  build the docs in the 'doc:' target.  For example:
        xml2nroff sample.xml > sample.n
        xml2html sample.xml > sample.html

which is perhaps an indication that the makefile contains some boilerplate code that should probably not be there if not relevant.

Third problem: Having encountered several warnings, and read that the SQLite developers' philosophy is that code quality is ensured through rigorous test suites, the natural next step seemed to be to make test. This resulted in (manually wrapped and trimmed to avoid excessive line length in report)

  TCL_LIBRARY=`echo /Installerare/Tcl/tcl8.5a6/library` \
    DYLD_LIBRARY_PATH=".:/Users/lars/Library/Frameworks/Tcl.framework:" \
    TCLLIBPATH="." /tclsh8.5 `echo ./tests/all.tcl`
  /bin/sh: line 1: /tclsh8.5: No such file or directory
  make: *** [test] Error 127

Looking again at the tarball contents, I realise that there weren't any tests in it. Pity! But then why is there even a "test" make target? (That it thinks /tclsh8.5 is a perfectly good path to tclsh is also odd, but could be a consequence of other things going wrong.)

Fourth problem: The make install target.

Slightly inspired by the README, my configure invocation had been:

  ./configure --prefix=/Library/Tcl

but the makefile still wants to put sqlite itself in /usr/local/lib/sqlite3.6.10/= -- the only thing for which it honors the specified --prefix is the man page.

Finally: One of the things actually included in the tarball is the documentation for the TEA system in (I believe) doctools format, which on one hand is nice (it looks like a more up-to-date documentation than what I've encountered before), but also seems unnecessary for a distribution as streamlined as this one (no tests, nor any package documentation to speak of). Might it be that some packaging script is grabbing the wrong directory?

However c3ref/changes.html was not updated with this new fix: ... Because of this optimization, the deletions in "DELETE FROM table" are not row changes and will not be counted by the sqlite3_changes() or sqlite3_total_changes() functions, regardless of the number of elements that were originally in the table.

  select * from a natural join b natural join c

takes "forever"

  select * from a natural join (b natural join c)

takes appropriate time

It would be nice if the SQL parser would be clever enough to find the corresponding tables more efficiently. I think the need for brackets is annoying.

This also affects SQL-code-portability because other RDBMS handle the bracket-free code nicely.

  SQL error: near "/": syntax error

A manual check that sqlite is prepared to accept the first INSERT statement works, which means the error is anywhere between lines 2 to 8226.

The error message gives no indication of the line number the error is on in the sql file, and so it is very difficult to debug which INSERT statement is generating the error or why.

  filename:linenumber:columnnumber: text

filename doesn't make too much sense in this context.

  create table dogs(name varchar2(20) primary key, breed varchar2(20));

  insert into dogs values('Poo', 'Dandie');
  insert into dogs values('Dinmo', 'Dandie');
  insert into dogs values('Robby', 'Scotty');
  insert into dogs values('Laddy', 'Scotty');
  insert into dogs values('Craigy', 'Scotty');
  insert into dogs values('Chuy', 'Chihuahua');

  commit;

Works as expected on both Oracle and SQLite:

  select breed, count(*)
  from dogs
  group by breed;

  BREED                  COUNT(*)
  -------------------- ----------
  Dandie                        2
  Scotty                        3
  Chihuahua                     1

This one gets an error on Oracle and a result on SQLite:

  select breed, count(*), name
  from  dogs
  group by breed;
  -- Oracle:
  ERROR at line 1:
  ORA-00979: not a GROUP BY expression

SQLite:

  breed		count(*) 	name
  --------------------------------------------
  Chihuahua	1		Chuy
  Dandie		2		Dinmo
  Scotty		3		Craigy

I think the right result is an error.

-- Works as expected on both Oracle and SQLite. select breed, count(*) from dogs group by breed;

select breed, count(*), name from dogs group by breed;

Results in an error on Oracle, and this output on SQlite:

I hope that makes things clearer... Karl

   SELECT a, b, c, d FROM t1 GROUP BY a, b;

The result set will have distinct values for the a and b column. The c and d columns values will be selected arbitrarily from among those with the same a and b values.

I will change this to a "documentation" error so that we will remember to add the rationale for the way this works in the documentation.

When one really wants to pick up an arbitrary value, maybe they should be offered an arbitrary_value_from_group() aggregate function ;-) That way we could get the least surprising outcome (an error) when lacking the aggregate function, but you'd still be able to replicate the old behavior by using the function above. --DD

PS: or any other pseudo aggregate functions like first(), second(), last(), etc...

  SQLite version 3.6.10
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> create table t1(a);
  sqlite> create table t2(a);
  sqlite> create table t3(a);
  sqlite> explain select * from (select * from t1 union all select * from t2) as t, t3 using(a);
  0|Trace|0|0|0|explain select * from (select * from t1 union all select * from t2) as t, t3 using(a);|00|
  1|OpenEphemeral|0|1|0||00|
  2|Goto|0|34|0||00|
  3|SetNumColumns|0|1|0||00|
  4|OpenRead|3|2|0||00|
  5|Rewind|3|11|0||00|
  6|Column|3|0|1||00|
  7|MakeRecord|1|1|2||00|
  8|NewRowid|0|3|0||00|
  9|Insert|0|2|3||08|
  10|Next|3|6|0||01|
  11|Close|3|0|0||00|
  12|SetNumColumns|0|1|0||00|
  13|OpenRead|1|3|0||00|
  14|Rewind|1|20|0||00|
  15|Column|1|0|1||00|
  16|MakeRecord|1|1|2||00|
  17|NewRowid|0|3|0||00|
  18|Insert|0|2|3||08|
  19|Next|1|15|0||01|
  20|Close|1|0|0||00|
  21|SetNumColumns|0|1|0||00|
  22|OpenRead|2|4|0||00|
  23|Rewind|0|32|0||00|
  24|Rewind|2|31|0||00|
  25|Column|0|0|2||00|
  26|Column|2|0|3||00|
  27|Ne|3|30|2|collseq(BINARY)|6a|
  28|Column|0|0|4||00|
  29|ResultRow|4|1|0||00|
  30|Next|2|25|0||01|
  31|Next|0|24|0||01|
  32|Close|2|0|0||00|
  33|Halt|0|0|0||00|
  34|Transaction|0|0|0||00|
  35|VerifyCookie|0|3|0||00|
  36|TableLock|0|2|0|t1|00|
  37|TableLock|0|3|0|t2|00|
  38|TableLock|0|4|0|t3|00|
  39|Goto|0|3|0||00|

It takes 13 seconds to run on SQLite 3.2.1 and uses just 1.2M of memory. With 3.3.5+ from CVS it takes 185 seconds and uses 230M of memory.

  PRAGMA temp_store=MEMORY;

  CREATE TABLE n1(a integer primary key);
  INSERT INTO "n1" VALUES(1);
  INSERT INTO "n1" VALUES(2);
  INSERT INTO "n1" VALUES(3);
  INSERT INTO "n1" VALUES(4);
  INSERT INTO "n1" VALUES(5);
  INSERT INTO "n1" VALUES(6);
  INSERT INTO "n1" VALUES(7);
  INSERT INTO "n1" VALUES(8);
  INSERT INTO "n1" VALUES(9);
  INSERT INTO "n1" VALUES(10);
  INSERT INTO "n1" VALUES(11);
  INSERT INTO "n1" VALUES(12);
  INSERT INTO "n1" VALUES(13);
  INSERT INTO "n1" VALUES(14);
  INSERT INTO "n1" VALUES(15);
  CREATE VIEW vu as select v3.a a, v5.a-v2.a*v7.a b
                      from n1 v1,n1 v2,n1 v3,n1 v4,n1 v5,n1 v6,n1 v7;

  select a a, sum(b) T from vu where a=7 group by a;

It seems that SQLite 3.2.1 had a much more efficient GROUP BY algorithm that discarded unnecessary data as the view was traversed.

  select a a, sum(b) T from vu where a<4 group by a;

But both queries exhibit the same slowdown and memory increase, in any event.

Older versions of SQLite performed this operation extremely efficiently because grouping took place in the main traversal loop. I would think that the old algorithm could be used, but instead of keeping the intermediate results in memory, an index and a table in temp store could be used.

3.6.7: 140 seconds, 233M RAM used

3.3.5: 185 seconds, 230M RAM used

3.2.1: 13 seconds, 1.2M RAM used

Any plan to speed it up?

in fts3, tokenizer simple may take arguments, also it seems it isn't documented on http://www.sqlite.org/cvstrac/wiki?p=FtsUsage . The first argument in the table creation syntax (CREATE VIRTUAL TABLE t USING fts3(content, TOKENIZE simple arg0 arg1 ...); ) is ignored. A very simple patch is included.

Geoffrey SCOUTHEETEN

This behavior wasn't seen in 3.5.9.

   create table my_sequence(num integer primary key autoincrement);
   create table foo (name TEXT);
   create table bar (id integer, name text);

   create trigger foo_update AFTER INSERT ON foo
   for each row
   begin
   insert into my_sequence default values;
   delete from my_sequence where num=last_insert_rowid();
   insert into bar (id, name) values (last_insert_rowid(), OLD.name);
   end;

   SQL error: near "default": syntax error

Seems the parser does not expect it there...

	CREATE TABLE t1 (id, firstname, lastname);

	INSERT INTO t1 VALUES(1, 'John', 'Doe');

	SELECT DISTINCT
	  t1.id as ID,
	  t1.firstname as fname,
	  t1.lastname as lname,
	  t1.firstname||','||t1.lastname AS fullname
	FROM
	  t1
	WHERE
	  -- this works:
	  -- t1.firstname||','||t1.lastname IN ('John,Doe', 'Fred,Flintstone')
	  --
	  -- this one does not work in 3.6.2, but used to work in 3.6.1 and before:
	  fullname IN ('John,Doe', 'Fred,Flintstone')
	;

Apparently, the combination of using a column alias in the WHERE expression and the DISTINCT qualifier makes the query fail.

Using the full expression t1.firstname||','||t1.lastname instead of the column alias fullname produces the correct result, but may have a performance hit as the expression may get evaluated twice.

When leaving out the DISTINCT keyword, the query also returns a result (but this is obviously not a workaround as it is a different query).

Since the query just returns an empty result set without any error and used to work in previous versions, it's very hard to catch those queries, so even though a workaround exists, a quick fix would be very nice.

Could this be caused by Checkin [5637] attempting to fix Ticket #3343?

  SQLite version 3.6.2
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> create table t1(a);
  sqlite> create table t2(a);
  sqlite> select a from t1, t2;
  SQL error: ambiguous column name: a
  sqlite> select * from t1, t2 where a = 1;
  SQL error: ambiguous column name: a
  sqlite> select * from t1, t2 order by a;
  sqlite> select * from t1, t2 group by a;
  sqlite> select * from t1, t2 group by 1 having a = 1;
  SQL error: ambiguous column name: a

   4 OpenWrite        0    2
   5 SetNumColumns    0    2
   6 Rewind           0   15
   7 Rowid            0    0
   8 MemStore         0    0
   9 Column           0    0
  10 MakeRecord       1    0
  11 Insert           0    0
  12 MemLoad          0    0
  13 NotExists        0   15
  14 Next             0    7
  15 Close            0    0

Various places throughout the code that call sqlite3OsClose mostly ignore the result.

The unixClose implementation completely ignores the results of the close system call. Presumably the belief is that close won't return I/O errors since when the data of a file is cared about then fsync would have been called which would catch the I/O errors. (A comment to that effect in the code would make the assumption clear.) Note that it is possible to get EINTR which would then result in SQLite leaking file descriptors.

  pPg = pPager->pFirstSynced;

to:

  pPg = pPager->pFirstSyncedNotIndex;

  if (pPg == NULL) {
    pPg = pPager->pFirstSynced;
  }

Even though pFirstSyncedNotIndex was't completely properly calculated (not all index pages were marked as index pages) it did decrease cache misses. The decrease wasn't big (from 340560 to 340501 misses) but it still resulted in 5% performance improvement during index creation.

I'm aware of plans for a new sorter, but maybe even with new sorter recycling non index pages first will make sorting faster.

  create table indexes(docid int);
  create virtual table texts using fts3(reading);
  insert into texts values('text1');
  insert into indexes values(last_insert_rowid());

Running the following query:

  select *
    from indexes join texts on texts.docid = indexes.docid
   where texts.reading match 'text1';

fails with error message: SQL error: unable to use function MATCH in the requested context

Quoting Scott Hess from the mailing-list: "I suspect that what's happening is that somewhere SQLite is doing the join on docid first, because it is certainly going to be efficient, but since you can't MATCH outside the index, it's not the right choice. One option would be to implement MATCH outside the index, but that would be potentially VERY slow, and I don't think you can express "This join costs N if you choose this index, and N*1000 if you don't"."

Workaround: turn the query into

  select *
    from texts join indexes on texts.docid = indexes.docid
   where texts.docid IN (
      SELECT docid FROM texts WHERE reading match 'text1'
   );

Quoting the docs (Readme.tokenizers).

    CREATE VIRTUAL TABLE <table-name> USING fts3(
      <columns ...> [, tokenizer <tokenizer-name> [<tokenizer-args>]]
    );
-------------------------

Quoting the code.

  for(i=3; i<argc; ++i){
    if( startsWith(azArg[i],"tokenize") ){
      zTokenizer = azArg[i];
    }else{
-------------------------

Also please add to http://www.sqlite.org/lockingv3.html this sumamry by Christian Smith sent to the mailing list with message-id Pine.LNX.4.58.0509161006430.16877@localhost.localdomain>

In summary: SQLite uses multiple readers/single writer locking. A writer can operate concurrently with readers until it is ready to commit or spill data from it's cache. In this case, it waits for readers to finish, then gets an exclusive write lock and writes it's data. Thus, the following concurrency is available to SQLite:

  time ---->
Reader >-------------|
Reader     >-------------|
Reader       >----------|
Writer        >-------c***----|
Reader           >***********-------------|

  Key:
 - Executing query
 c Commit
 * Blocked by lock
 > Start of query
 | End of query

The last reader above is blocked from starting by the writer until the writer commits. If the writer commits before the last reader has finished, it is blocked.

t2's size is almoust 340 M (table exported to CSV is only 117 M). Same result is in 3.3.8 and 3.5.8.

Vaccuming both files with 3.3.8 increases original file size to about 94 M and decreases file t2 to 94 M. Vaccuming both files with 3.5.8 doesn't change file's size. So the one created with inserts is 86M and the one created with CREATE TABLE AS SELECT is 340M.

With other table (42 columns, 199523 rows, loooks like contains much less NULLs than the first table) CREATE TABLE AS SELECT also creates file larger than separate selects, but not as much larger (95M with inserts and 111M with CREATE TABLE AS SELECT).

The patch below make a slight modification to the output, prefixing all state numbers with s, and all rule numbers with r.

With this patch, looking for a parser state using vim is now as easy as hitting '*' ("find next identifier occurrence") on the number.

  Index: lemon.c
  ===================================================================
  --- lemon.c>--(revision 5868)
  +++ lemon.c>--(working copy)
  @@ -2845,10 +2845,10 @@
     int result = 1;
     switch( ap->type ){
       case SHIFT:
  -      fprintf(fp,"%*s shift  %d",indent,ap->sp->name,ap->x.stp->statenum);
  +      fprintf(fp,"%*s shift  s%d",indent,ap->sp->name,ap->x.stp->statenum);
         break;
       case REDUCE:
  -      fprintf(fp,"%*s reduce %d",indent,ap->sp->name,ap->x.rp->index);
  +      fprintf(fp,"%*s reduce r%d",indent,ap->sp->name,ap->x.rp->index);
         break;
       case ACCEPT:
         fprintf(fp,"%*s accept",indent,ap->sp->name);
  @@ -2858,11 +2858,11 @@
         break;
       case SRCONFLICT:
       case RRCONFLICT:
  -      fprintf(fp,"%*s reduce %-3d ** Parsing conflict **",
  +      fprintf(fp,"%*s reduce r%-3d ** Parsing conflict **",
           indent,ap->sp->name,ap->x.rp->index);
         break;
       case SSCONFLICT:
  -      fprintf(fp,"%*s shift  %d ** Parsing conflict **",$
  +      fprintf(fp,"%*s shift  s%d ** Parsing conflict **",
           indent,ap->sp->name,ap->x.stp->statenum);
         break;
       case SH_RESOLVED:
  @@ -2888,13 +2888,13 @@
     if( fp==0 ) return;
     for(i=0; i<lemp->nstate; i++){
       stp = lemp->sorted[i];
  -    fprintf(fp,"State %d:\n",stp->statenum);
  +    fprintf(fp,"State s%d:\n",stp->statenum);
       if( lemp->basisflag ) cfp=stp->bp;
       else                  cfp=stp->cfp;
       while( cfp ){
         char buf[20];
         if( cfp->dot==cfp->rp->nrhs ){
  -        sprintf(buf,"(%d)",cfp->rp->index);
  +        sprintf(buf,"(r%d)",cfp->rp->index);
           fprintf(fp,"    %5s ",buf);
         }else{
           fprintf(fp,"          ");

Since we are enhancing the parse.out file from lemon, I wonder if we might convert the output to be HTML. Well-structured HTML could still be human readable in an ordinary text editor. But if you then loaded it into a webbrowser, you could move around between states and rules by clicking on hyperlinks. Just a thought....

If the html is done well, the textual output can easily be generated from it. It might make it less easy to read the html directly though. I'll ponder about this for a bit.

I have the following DB set up

  CREATE TABLE [b] ([code] INTEGER, [txt] varchar (50));
  CREATE UNIQUE INDEX IDX_beta_code ON b (code);

  -- 66 data rows follow

  CREATE TABLE [a]([id] INTEGER, [ts] datetime, [code] INTEGER, [status] INTEGER);

  -- 42923 data rows follow

  CREATE UNIQUE INDEX IDX_alpha_id on [a] (id);
  CREATE INDEX IDX_alpha_code ON [a] (code);
  CREATE INDEX IDX_alpha_status on [a] (status);
  CREATE INDEX IDX_alpha_ts on [a] (ts);
  ANALYZE;

The following statement, meant to find latest item, match b.txt to it, takes ~6 seconds on test machine (arm xscale)

  select a.id,a.code,b.txt from a, b where a.code = b.code and status<>1 ORDER BY ts limit 0,1;

  explain query plan select a.id,a.code,b.txt from a, b where a.code = b.code and status<>1 ORDER BY ts limit 0,1;
  -- 0|1|TABLE b
  -- 1|0|TABLE a WITH INDEX IDX_alpha_code

however, if IDX_beta_code is dropped and recreated (without running another ANALYZE, without fix for #3033), same query runs for <0.1 second

  explain query plan select a.id,a.code,b.txt from a, b where a.code = b.code and status<>1 ORDER BY ts limit 0,1;
  -- 0|0|TABLE a WITH INDEX IDX_alpha_ts ORDER BY
  -- 1|1|TABLE b WITH INDEX IDX_beta_code

this is the current contents of the sqlite_stat1 table

  a|IDX_alpha_ts|42923 2
  a|IDX_alpha_status|42923 42923
  a|IDX_alpha_code|42923 10731
  a|IDX_alpha_id|42923 1
  b|IDX_beta_code|66 1

I've got a trace of the optimizer for both cases, will attach.

as a side note, behaviour re loop order is contrary to what's documented in http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning

eg:

  explain query plan select * from a where ts < datetime('now') AND status = 2 order by ts;
  -- 0|0|TABLE a WITH INDEX IDX_alpha_status

I could probably also kludge this and drop and recreate the misused indices every time my app opens the database, but that would be messy.

correction does the right thing when b table scan appears expensive, not when b index seems huge

  SELECT * FROM t1 WHERE a<(SELECT datetime('now')) AND +status=2 ORDER BY ts;

I think your best bet it to ask for help optimizing your queries on the mailing list.

I just don't see a way I can force ordering on an INNER JOIN after analyze was run.

If you want to know how to force full table scans or to force particular orders of joins or use of particular indices, then take you questions to the SQLite mailing list. This is not the appropriate forum.

  sqlite> CREATE TABLE x (s integer, u integer, n integer);
  /* s = same, u = unique, n = null */

( insert 1000 rows where s=1, u=[1..1000] and n=NULL )

  sqlite> create index x_s on x (s);
  sqlite> create index x_u on x (u);
  sqlite> create index x_n on x (n);
  sqlite> analyze x;
  sqlite> select * from sqlite_stat1;
  x|x_n|1000 1
  x|x_u|1000 1
  x|x_s|1000 1000

I would expect x_n to have the same values as x_s.

Compressed, the database is about 16 MB. I would be more than willing to post the database. Just let me know if it's needed.

PATCH:

---- CUT --------------------------------------------------------------------

--- C:/lemon.c Wed Jun 13 15:02:37 2007
+++ D:/Den/Lemon/lemon.c Wed Jun 13 16:25:22 2007
@@ -2911,7 +2911,11 @@
c = *cp;
*cp = 0;
path = (char *)malloc( strlen(argv0) + strlen(name) + 2 );
- if( path ) sprintf(path,"%s/%s",argv0,name);
+ #ifdef __WIN32__
+ if( path ) sprintf(path,"%s\\%s",argv0,name);
+ #else
+ if( path ) sprintf(path,"%s/%s",argv0,name);
+ #endif
*cp = c;
}else{
extern char *getenv();
@@ -2920,11 +2924,19 @@
path = (char *)malloc( strlen(pathlist)+strlen(name)+2 );
if( path!=0 ){
while( *pathlist ){
- cp = strchr(pathlist,':');
+ #ifdef __WIN32__
+ cp = strchr(pathlist,';');
+ #else
+ cp = strchr(pathlist,':');
+ #endif
if( cp==0 ) cp = &pathlist[strlen(pathlist)];
c = *cp;
*cp = 0;
- sprintf(path,"%s/%s",pathlist,name);
+ #ifdef __WIN32__
+ sprintf(path,"%s\\%s",pathlist,name);
+ #else
+ sprintf(path,"%s/%s",pathlist,name);
+ #endif
*cp = c;
if( c==0 ) pathlist = "";
else pathlist = &cp[1];

---- CUT --------------------------------------------------------------------

=============================================================================
SUGGESTION 1:

Why we allocate parser with mallocProc parameter of ParseAlloc function
and free with freeProc of ParseFree function?

We do this because we want what parser is user-allocatable
with USER-DEFINED-MEMORY-ALOCATION-WAY but not with "malloc"/"free" from stdlib... am i right?

If so... why we still allocate memory for parser stack with "realloc" function?

It's bad for solutions where is no stdlib.

My suggestion is

FIRST WAY:

To add to yyParser struct 3 variables like

void *mem_alloc_fn;
void *mem_realloc_fn;
void *mem_free_fn;

and add 3 directives like

%memory_alloc
%memory_realloc
%memory_free

and if it declared - use it for allocating/free/reallocating memory in parser.

and
- void ParseAlloc(void *(*mallocProc)(size_t));
will now as void *ParseAlloc();
- void ParseFree(void *pParser, void (*freeProc)(void));
will now as void ParseFree(void *pParser);

OR SECOND WAY (very simple):

To add to yyParser struct 1 variable like
void *mem_realloc_fn;

- void *ParseAlloc(void *(*mallocProc)(size_t));
will now as void *ParseAlloc(void *(*mallocProc)(size_t), void *(*reallocProc)(void *, size_t));

store reallocProc in mem_realloc_fn in yyParser

and in yyGrowStack something like this:
... yyGrowStack (...)
{
....
if(pParser->mem_realloc_fn != NULL)
{
pNew = pParser->mem_realloc_fn(p->yystack, newSize*sizeof(pNew[0]));
}
else
{
pNew = realloc(p->yystack, newSize*sizeof(pNew[0]));
}
....
}

and use it for reallocating memory in parser.

In this ways - memory allocating in parser is under FULL user control.

=============================================================================
SUGGESTION 2:

I build lemon with VC 8.0 with option /Wp64 (Detect 64-Bit Portability Issues)
and have warnings. Type int, size_t, pointer and unsigned long have diferent size on x32 and x64 platforms.

Can you fix type difference, please?

Only you can choice better way for this - type conversion OR change type of 'warning' variables.

WARNINGS:

d:\den\lemon\lemon.c(1331) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(1337) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(1455) : warning C4113: 'int (__cdecl )()' differs in parameter lists from 'int (__cdecl *)(const void *,const void *)'
d:\den\lemon\lemon.c(1578) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1578) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1581) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1581) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1586) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1586) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1588) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1588) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1590) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1590) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1592) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1592) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1595) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1595) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1596) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1596) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1624) : warning C4311: 'type cast' : pointer truncation from 'char **' to 'unsigned long'
d:\den\lemon\lemon.c(1624) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1628) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1628) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1629) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1629) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size
d:\den\lemon\lemon.c(1658) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(1661) : warning C4267: '+=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(1774) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1774) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1785) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1785) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long'
d:\den\lemon\lemon.c(1883) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(2722) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(3171) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(3173) : warning C4018: '>=' : signed/unsigned mismatch
d:\den\lemon\lemon.c(3184) : warning C4267: '+=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(3340) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(3346) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data
d:\den\lemon\lemon.c(3542) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data

Please... Please...Please... :-))) it's not soo strong.

Thanks. Fred.

  %  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""

  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

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).

   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....

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

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?

  ATTACH 'db' AS aux1;
  ATTACH 'db' AS aux2;
  CREATE TABLE aux1.abc(a, b, c);
  CREATE TABLE aux2.abc(a, b, c);

   CREATE TABLE t1(x,y);
   INSERT INTO t1 VALUES(1,2);
   CREATE TABLE t2(z);
   INSERT INTO t2 VALUES(1);
   SELECT (SELECT y FROM t1 WHERE x=min(z)) FROM t2;

Problem reported on the mailing list.

  SQLite v3.4.2
  CREATE TABLE t1(x,y);
  CREATE TABLE t2(z);
  INSERT INTO t1 VALUES(1,21);
  INSERT INTO t1 VALUES(2,22);
  INSERT INTO t1 VALUES(3,23);
  INSERT INTO t2 VALUES(3);
  INSERT INTO t2 VALUES(2);
  INSERT INTO t2 VALUES(1);

What you wanted to do:

  SELECT y FROM t1 WHERE x=(SELECT min(z) FROM t2);
  21    -- works as expected

What you did:

  SELECT (SELECT y FROM t1 WHERE x=min(z)) FROM t2;
  SQL error near line []: misuse of aggregate function min()

  .header on
  .mode column

  CREATE TABLE Main (pk INTEGER PRIMARY KEY, name VARCHAR);
  CREATE TABLE OptA (pk INTEGER PRIMARY KEY, alpha VARCHAR);
  CREATE TABLE OptB (pk INTEGER PRIMARY KEY, beta VARCHAR);

  INSERT INTO Main VALUES (1, 'One');
  INSERT INTO Main VALUES (2, 'Two');
  INSERT INTO Main VALUES (3, 'Three');
  INSERT INTO Main VALUES (4, 'Four');

  INSERT INTO OptA VALUES (1, 'Alpha1');
  INSERT INTO OptA VALUES (4, 'Alpha4');

  INSERT INTO OptB VALUES (2, 'Beta2');
  INSERT INTO OptB VALUES (4, 'Beta4');

  SELECT * FROM Main LEFT JOIN OptA USING (pk) LEFT JOIN OptB USING (pk);

  SELECT * FROM Main LEFT JOIN OptB USING (pk) LEFT JOIN OptA USING (pk);

  SELECT Main.pk, name, alpha, beta
    FROM Main LEFT JOIN OptA ON Main.pk = OptA.pk
              LEFT JOIN OptB ON Main.pk = OptB.pk;

Joining Main, OptA, and OptB omits Beta2:

  pk          name        alpha       beta
  ----------  ----------  ----------  ----------
  1           One         Alpha1
  2           Two
  3           Three
  4           Four        Alpha4      Beta4

Joining Main, OptB, and OptA omits Alpha1:

  pk          name        beta        alpha
  ----------  ----------  ----------  ----------
  1           One
  2           Two         Beta2
  3           Three
  4           Four        Beta4       Alpha4

Only by using ON instead of USING do we get the correct results:

  pk          name        alpha       beta
  ----------  ----------  ----------  ----------
  1           One         Alpha1
  2           Two                     Beta2
  3           Three
  4           Four        Alpha4      Beta4

I think this is basically the same issue as ticket #1637, but it's a more serious example. In that one, the query simply failed to compile. In this case, it seems to work, but gives you the wrong results.

I've also tried this script in PostgreSQL 8.0.13. All three queries give (the same) correct results.

  SELECT * FROM Main LEFT JOIN OptA USING (pk) LEFT JOIN OptB USING (pk);

into

  SELECT Main.pk, name, alpha, beta
    FROM Main LEFT JOIN OptA ON Main.pk = OptA.pk
              LEFT JOIN OptB ON OptA.pk = OptB.pk;

Here is a workaround to this bug that makes use of a subquery:

  select * from (SELECT * FROM Main LEFT JOIN OptA USING (pk))
    LEFT JOIN OptB USING (pk);

Conceivably all LEFT JOIN chains could be transformed into the above form, but that would decrease performance due to the intermediate result set of the subquery. Having it work without the subquery is tricky since sqlite must deduce that the last USING (pk) is equivalent to the first pk in the chain of joined tables, namely Main.pk, and not OptA.pk.

Joe Wilson

  1) Open db A, attach empty db B.
  2) Using another connection from the same thread, set the
     encoding of B to be different from that of A. Add some
     data to B.
  3) Using the original connection, access database B. It assumes
     the encoding of A (and therefore mangling any text data).

The correct response is to return an error - "attached databases must use the same text encoding as main database".

Then:

Now try this;

You see ? if i use "GROUP BY", the field name contains tablename. Because i use "SELECT test.name" and not "SELECT name".

If i set an alias, i get alias, that's ok.

The trouble appears to be very high on Copix (http://wwW.copix.org). We create some DAO (Data Access Objects) automatically. The "groupBy" method doesn't works with SQLite...

Is this normal ? Mysql, PostgreSql, Oracle... doesn't need to create alias.

PS: I love this database :) Simple, nice, usefull, quick and easy

Regards

  sqlite> update foo set key=key+1;
  SQL error: cannot modify foo because it is a view

However, if a single trigger is added that contains a WHEN clause, then UPDATE statements that do not satisfy that WHEN clause silently succeed without raising any error.

  sqlite> select 'Before:'; select * from foo; update foo set key=key+1; select 'After:'; select * from foo;
  Before:
  1|42|forty-two|42.0
  2|69|sixty-nine|69.0
  After:
  1|42|forty-two|42.0
  2|69|sixty-nine|69.0

  create trigger foo_update instead of update on foo
  begin
	select raise(ABORT, 'invalid key')
	where old.key <> new.key;

	update foo_backing set num=new.num, str=new.str, float=new.float, dirty=1
		where key=new.key;
  end;

This patch adds/updates 76 lines across 4 files. I disclaim all copyright to these 76 lines.

  %destructor { ... }

I got this error message:

  Symbol name missing after 134583560estructor keyword

This is trivially fixed by replacing "Symbol name missing after %destructor keyword" with "Symbol name missing after %%destructor keyword" twice in lemon.c

UPDATE foo SET rowid='a string' WHERE 1

This results in a call to xUpdate with argv[0] equal the current rowid but argv[1] is 'a string'. While I'd be quite happy for rowids to be any SQLite type, the xRowid call only allows 64 bit integers. I believe SQLite should check the new rowid in a case like this is an integer and reject it, rather than calling xUpdate with the bad value. (I also just checked with rowid=3.4 and rowid=NULL and they get passed through as is as well)

A workaround is to document that the xUpdate method must check the new rowid is an integer type.

The latter part does not appear to be true ("when it destroys the virtual table"). I can't find any code that does actually that. (eg vtab.c:496 definitely doesn't, nor does vtab.c:76)

Usually the former case happens. However some operations have their error codes ignored (eg xClose). This can result in the zErrMsg pointing to a message but no error code returned upstream (which would clear the message). Finally as far as I can tell the responsibility for freeing sqlite3_vtab is with the xDisconnect/xDestroy callbacks since the corresponding xCreate/xConnect callbacks allocated it. Consequently there is no way for SQLite to even access zErrmsg since it would be a member of a freed structure after xDisconnect/xDestroy returned.

The use case for this is for systems that do not support 64bit integers (e.g. lack of compiler feature, embedded system), db's of small data size, and systems without large file support. The patch allows compiling with -DSQLITE_INT64_TYPE=int -DSQLITE_32BIT_ROWID for such a system.

load ./lib/libtclsqlite[info sharedlibextension] sqlite MEMORY_DB :memory: MEMORY_DB onecolumn "PRAGMA empty_result_callbacks=1" puts [MEMORY_DB version] MEMORY_DB eval "create table xyz (col1,col2)" MEMORY_DB copy ignore win_pol /home/centadm/win_pol4.csv \t MEMORY_DB eval "select * from xyz" sqlite_array { puts "Here in the callback" foreach sqlite_value $sqlite_array(*) { puts "$sqlite_value $sqlite_array($sqlite_value)" } }

The data file win_pol4.csv consists of two columns, tab seperated. DATA1 DATA2

And the output: -bash-3.00$ tclsh test_sqlite.tcl

3.3.5

    while executing
"MEMORY_DB copy ignore win_pol /home/centadm/win_pol4.csv \t"
    (file "test_sqlite.tcl" line 5)
-bash-3.00$ pwd
/home/centadm
-bash-3.00$ ls -l /home/centadm/win_pol4.csv
-rw-r--r--  1 centadm centadm 12 May  5 14:21 /home/centadm/win_pol4.csv
-bash-3.00$ more /home/centadm/win_pol4.csv
DATA1   DATA2

A TCL Error is returned from the copy command, no message tho. I have used catch to capture the command and verified that there is no data going into the table.

Also, PRAGMA empty_result_callbacks=1 still doesn't seem to work in the tcllib. If you catch the COPY command above, you still never see the "Here in the callback" message.

The line MEMORY_DB copy ignore win_pol /home/centadm/win_pol4.csv \t

should read

MEMORY_DB copy ignore xyz /home/centadm/win_pol4.csv \t

However the result is the same:

-bash-3.00$ tclsh test_sqlite.tcl 3.3.5

    while executing
"MEMORY_DB copy ignore xyz /home/centadm/win_pol4.csv \t"
    (file "test_sqlite.tcl" line 7)
-bash-3.00$

    zSql = sqlite3_mprintf("SELECT * FROM '%q'", zTable);
    if( zSql==0 ){
      Tcl_AppendResult(interp, "Error: no such table: ", zTable, 0);
      return TCL_ERROR;
    }
    nByte = strlen(zSql);
    rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0);
    sqlite3_free(zSql);
    if( rc ){
      Tcl_AppendResult(interp, "Error: ", sqlite3_errmsg(pDb->db), 0);
      nCol = 0;
    }else{
      nCol = sqlite3_column_count(pStmt); <--- RETURNING 0 FOR COLUMN COUNT, HAVE VERIFIED TABLE HAS TWO COLUMNS
    }
    sqlite3_finalize(pStmt);
    if( nCol==0 ) {
      return TCL_ERROR;       <--- NO ERROR MESSAGE RETURNED
    }

rc = sqlite3_prepare(pDb->db, zSql,0, &pStmt, 0);

should be

rc = sqlite3_prepare(pDb->db, zSql,-1, &pStmt, 0);

#ifdef __WIN32__ cp = strrchr(argv0,'\\'); + ds = ';'; #else cp = strrchr(argv0,'/'); + ds = ':'; #endif if( cp ){ c = *cp; @@ -2812,7 +2815,7 @@ path = (char *)malloc( strlen(pathlist)+strlen(name)+2 ); if( path!=0 ){ while( *pathlist ){ - cp = strchr(pathlist,':'); + cp = strchr(pathlist,ds); if( cp==0 ) cp = &pathlist[strlen(pathlist)]; c = *cp; *cp = 0;

  --- lemon.c.orig        2005-10-18 11:27:55.753467000 +0200
  +++ lemon.c     2005-10-18 11:29:11.897825400 +0200
  @@ -2791,13 +2791,16 @@
   {
     char *pathlist;
     char *path,*cp;
  +  char ds;
     char c;
     extern int access();

   #ifdef __WIN32__
     cp = strrchr(argv0,'\\');
  +  ds = ';';
   #else
     cp = strrchr(argv0,'/');
  +  ds = ':';
   #endif
     if( cp ){
       c = *cp;
  @@ -2812,7 +2815,7 @@
       path = (char *)malloc( strlen(pathlist)+strlen(name)+2 );
       if( path!=0 ){
         while( *pathlist ){
  -        cp = strchr(pathlist,':');
  +        cp = strchr(pathlist,ds);
           if( cp==0 ) cp = &pathlist[strlen(pathlist)];
           c = *cp;
           *cp = 0;

  line 0
  line 2

in 3.1.1 beta, you get the following error:

  SQL error: no such column: bb.a

SCRIPT:

  create table test1 (a int);
  insert into test1 values (0);
  insert into test1 select max(a)+1 from test1;
  insert into test1 select max(a)+1 from test1;

  create table test2 (a int, b text);
  insert into test2 select a,'line ' || a from test1;

  select test2.b from (select test1.a from test1 where a%2 = 0) as bb   join test2 on bb.a = test2.a;

  select test2.b 
  from (select test1.a as a from test1 where a%2=0) as bb
       join test2 on bb.a=test2.a;

The optimization does comparisons directly on serialized data instead of deserializing and then comparing.

It also inlines sqlite3GetVarint32 partly. The way the __inline keyword is used needs to be made portable.

As long as it doesn't contain any fundamental logic flaws any bugs should be possible to fix.

- The code that collapses cases for default destructors erroneously assumes that all symbols have the same type.

- If a reduction rule doesn't have code, then the RHS symbols will not have their destructors called.

- The default destructor shouldn't be called on the auto-generated "error" symbol

- In the internal function "append_str", zero-length strings may be returned un-terminated.

I have some proposed fixes that I'll try to attach to this ticket.

Here is a simple grammar that demonstrates the problem:

%include { extern int line_number; extern const char *file_name; }

result(r) ::= TOKEN(s). { printf("BAD: Got a token on line '%d'\n", line_number); printf("BAD: \tFile = '%s'\n", file_name); r = s; }

The first bug is that the "%d" in the first printf is interpreted by the append_str function, when it shouldn't be, producing code that looks like: printf("BAD: Got a token on line '0d'\n", line_number); I believe that the solution is to have append_str() NOT do %d substitution when it is copying the code.

The second bug is that the "s" in the "%s" format is being interpreted as a symbolic name, producing code that looks like: printf("BAD: \tFile = '%yymsp[0].minor.yy0'\n", file_name);

I believe that the solution is to have translate_code() ignore symbolic names inside of quoted strings.

Here is a script that you can use to reproduce the issue. As you can see the results are quite different.

#----------->
load ./lib/tclsqlite-3.0.8.so sqlite3
puts [info patchlevel]
sqlite3 db :memory:
db eval "create table t1(a,b);"
puts "before 3.0.8 select, no pragma"
db eval "select * from t1;" x {
        puts "x(*) = $x(*)"
}
db eval "PRAGMA empty_result_callbacks=1"
puts "before 3.0.8 select, yes pragma"
db eval "select * from t1;" x {
        puts "x(*) = $x(*)"
}
db close
load ./lib/tclsqlite-2.8.15.so Tclsqlite
sqlite db2 :memory:
db2 eval "create table t1(a,b);"
puts "before 2.8.15 select, no pragma"
db2 eval "select * from t1;" x {
        puts "x(*) = $x(*)"
}
db2 eval "PRAGMA empty_result_callbacks=1"
puts "before 2.8.15 select, yes pragma"
db2 eval "select * from t1;" x {
        puts "x(*) = $x(*)"
}
db2 close
puts "done"
# <-------------------

and the results:

$ tclsh test_sqlite.tcl
8.4.3
before 3.0.8 select, no pragma
before 3.0.8 select, yes pragma
before 2.8.15 select, no pragma
before 2.8.15 select, yes pragma
x(*) = a b
done