Small. Fast. Reliable.
Choose any three.
Status color codes: Active Bugs Active Issues Fixed Tested Deferred Closed
# Type Status Created By Subsys Changed Assigned Svr Pri Title
Description
Remarks
 
651 new active 2004 Mar anonymous   2009 Aug   5 4 Update optimization
Possible optimization on updates

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.

 
4009 code active 2009 Aug anonymous   2009 Aug   2 1 group_concat too slow
A call to group_concat takes endless time to complete when the original table contains 100,000+ entries.

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;

2009-Aug-04 15:54:11 by anonymous:
Cannot upload database (2MB compressed)


2009-Aug-06 06:59:07 by anonymous:
I let the select run until I got the result.

10 mins for SQLite

2.5 secs for MySQL


2009-Aug-06 12:33:09 by anonymous:
After having quickly looked at the code, it seems that groupConcatStep is called every time a tuple is added. sqlite3StrAccumAppend is then called which does a new memory allocation and copy of the current buffer with the content to add. That's a bit overkill. In the reallocation phase, maybe should you add a chunk of memory big enough to avoid a malloc at each call, but small enough to avoid a not_enough_memory_like message.

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

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


2009-Aug-06 14:15:08 by anonymous:
tested successfully from 10 mins to 6 secs if changing line 770 with

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

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

 
4010 code active 2009 Aug anonymous back 2009 Aug   4 4 review design decision on JOURNAL_CHUNKSIZE in memjournal.c
memjournal.c has the comment on JOURNAL_CHUNKSIZE

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

 
4008 new active 2009 Aug anonymous func 2009 Aug   1 1 2-digit year number in strftime() function by new 'y' switch
The 2-digits year number is needed for compatibility with other RDBMS.

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;
       }
     }
 
3998 new active 2009 Jul anonymous func 2009 Aug   3 3 Optimization for UPDATE statement
When I perform an UPDATE command, even if the field value is not being changed, the sqlite does recordings on disc, making the process slow.

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.

2009-Jul-29 03:31:19 by anonymous:
"the second form will be more slow if 'newFieldValue' is a subselect or something else." You can't assume that. It depends on the efficiency of the SQLite query optimizer. I've worked on other database products where I wouldn't hesitate to use the second form with 'newFieldValue' being a subselect. Do more tests and see what happens! HTH, TC
 
3989 code active 2009 Jul anonymous test 2009 Jul   4 4 lock3 has funky filepath causing problems on windows
lock3.test, version 1.4 has a real funny filepath in test 1.1

  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
2009-Jul-25 13:31:38 by danielk1977:
In what respect is it causing problems on windows?


2009-Jul-25 14:41:45 by anonymous:
It's the final / causing the problems, and this line doesn't test SQLite anyways, its testing the underlying file system

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
 
3987 code active 2009 Jul anonymous   2009 Jul   4 3 limit + order by speed
Seems like last query sorts entire table instead just one row:

  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
2009-Jul-24 14:43:39 by danielk1977:
SQLite is probably transforming the query to this internally:

  SELECT * FROM t ORDER BY a LIMIT 1

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

 
3343 code active 2008 Aug rdc   2009 Jul   1 3 incorrect results for queries using alias names
SQLite is generating code that re-evaluates the expression associated with an alias name when the alias name is used in a condition in the where clause. This is inefficient for all functions, and leads to incorrect results for any function that can return different values for each invocation.

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.

2008-Aug-28 18:50:01 by anonymous:
Reevaluation occurs in many plpaces in sqlite, not only in WHERE. For example:

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

This behaviour already caused implementation problems: #2251


2009-Jul-13 12:51:36 by anonymous:
It used to work better:

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|
random() function is evaluated only once, but in current version DISTINCT generate random() twice (there are also twice as much opcodes), which may be slower and can produce in incorrect result:

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|
 
2371 code new 2007 May anonymous   2009 Jul   2 2 sqlite3_errcode() and sqlite3_errmsg() return unexpected results
The manual says that sqlite3_step() directly returns explicit error code if the query has been prepared with the new API sqlite3_prepare_v2().

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);
  }
2008-Feb-04 20:54:31 by anonymous:
On 3.5.5 this is a little better:

  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.


2009-Jul-08 13:38:50 by anonymous:
Can somebody fix this old bug?


2009-Jul-10 13:27:12 by anonymous:
Please provide estimation date to fix this bug.
 
3913 code active 2009 Jun anonymous tcl 2009 Jun   4 4 Some variables in bubble-generator.tcl are being used before being set
Hi there, I have converted your bubble-generator (found in http://www.sqlite.org/docsrc/artifact/1558) to python and now I'm noticing there are two minor issues on it.

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.

 
3857 code active 2009 May anonymous   2009 May   3 3 Lemon parser generator: No configurable location for lempar.c
While working on the Gentoo ebuild for the Lemon parser generator, I found that I had to patch the sources in order to get lemon to look in /usr/share/lemon for its default parser template file, lempar.c. Lemon appears to look in the directory of the grammar file it's working on, and possibly its own directory, for this file. When it is installed in a directory in the $PATH it appears to also look in the $PATH. The $PATH is not a very good place to install this file, as it is only for executables. Lemon should look for this file in a location configurable at compile time or it will be difficult for distributions to package it. There is at least one program, namely the Netsurf web browser, which needs lemon in order to compile and does not provide its own lempar.c. Please fix this. Related bug: #1656
 
3840 warn active 2009 May anonymous build 2009 May   3 4 Missing header files in amalgamation
I build direct from the complete source code version (the one even "the SQLite developers do not use" according to the download page!), but I think this is a general issue since I've checked the pre-generated download also.

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:


--- mksqlite3c.tcl (revision 4146)
+++ mksqlite3c.tcl (revision 4147)
@@ -134,8 +134,9 @@
# sqlite3.c output file. If any #include statements are seen,
# process them approprately.
#
+set deflevel 0
proc copy_file {filename} {
- global seen_hdr available_hdr out addstatic
+ global seen_hdr available_hdr out addstatic deflevel
set tail [file tail $filename]
section_comment "Begin file $tail"
set in [open $filename r]
@@ -158,11 +159,20 @@
section_comment "Continuing where we left off in $tail"
}
} elseif {![info exists seen_hdr($hdr)]} {
- set seen_hdr($hdr) 1
+ if {$deflevel == 0} {
+ set seen_hdr($hdr) 1
+ }
puts $out $line
}
} elseif {[regexp {^#ifdef __cplusplus} $line]} {
+ incr deflevel
puts $out "#if 0"
+ } elseif {[regexp {^\s*#\s*if} $line]} {
+ incr deflevel
+ puts $out $line
+ } elseif {[regexp {^\s*#\s*endif} $line]} {
+ incr deflevel -1
+ puts $out $line
} elseif {[regexp {^#line} $line]} {
# Skip #line directives.
} elseif {$addstatic && ![regexp {^(static|typedef)} $line]} {
 
3749 new active 2009 Mar anonymous   2009 Mar   5 4 Condition for indexes
It would be very nice to have condition like WHERE column_x = 5 in CREATE INDEX statement, so we can create conditional UNIQUE constraint. For example we have table with columns global and name and we want to set UNIQUE constraint for name, but only in case of global = 1 - in this case we would simply create UNIQUE index with proper condition.

This feature is already present in PostgreSQL, I don't know how about other databases.
 
3742 new active 2009 Mar aswift   2009 Mar drh 3 4 Performance improvements for common ORDER BY clauses
Please investigate potential performance enhancements with ORDER BY clauses, particularly those that do not utilize an index.

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

 
3734 code active 2009 Mar danielk1977 back 2009 Mar   3 3 A read/write db connection may connect to a read-only shared cache.
See the following Tcl script:

  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) }
 
3708 new active 2009 Mar anonymous   2009 Mar   3 4 UNION + ORDER BY
  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

SQL requires that all ORDER BY terms exactly match a column in the result set. random() is not a column in the result set.

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.

 
3128 code active 2008 May anonymous   2009 Mar   3 3 DISTINCT -> GROUP BY can lead to performance degradation
With [4538] , a DISTINCT is rewritten as a GROUP BY if possible because GROUP BY can use an index. However, if the results are limited by LIMIT, this approach can be much, much slower; for example, the SELECT below went from 0.02 seconds (in 3.5.2 and earlier) to 3.10 seconds (in 3.5.3 and later).

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.

 
3678 new active 2009 Feb anonymous front 2009 Mar   3 5 index not selected with re-ordered distinct columns
This is based upon an up-to-date cvs build (as of 2009-02-24). If a select distinct is made with columns in a different order than an index, the query planner uses no index. Obviously, since the order that the columns are selected does not affect the results, the query planner should have used the index.

  $ ./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
2009-Mar-06 02:45:45 by shane:
One risk with this change is that it will change the "unspecified" order of the result set and potentially break existing code. Arguably any code that depends on SQLite returning a result set in a particular order without specifing an ORDER BY clause is already "broken".
 
3670 new active 2009 Feb anonymous cli 2009 Feb   5 3 Shell: The "backup_timeout" method for backup/restore operations
The feature wiil be very useful.
2009-Feb-22 01:17:09 by anonymous:
Care to explain? The destination is held exclusively until finish is called. The source is acquired for each step and obeys the busy handler. The number of pages copied (and hence I/O done) is controlled by the parameter you specify to step.


2009-Feb-22 10:41:49 by anonymous:
timeout is ignored. When source database is locked we getting busy error immediately:

sqlite> .timeout 10000

sqlite> .restore /tmp/test.db

source database is busy

 
3666 code active 2009 Feb anonymous   2009 Feb   3 1 softHeapLimitEnforcer called too many times
In malloc.c, the softHeapLimitEnforcer function releases the amount of requested memory. The older signature of the function was:

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

 
3658 code active 2009 Feb anonymous   2009 Feb   1 2 Queries return inconsistent results
I will say up front I am not expecting commercial support as it is clearly stated this is not an option.

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.

2009-Feb-16 04:57:00 by danielk1977:
Thanks for the bug report. Are you able to provide the data used to demonstrate the problem?

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.


2009-Feb-18 23:17:42 by anonymous:
Hi Dan, I have emailed you the data as requested for privacy reasons. Good luck and thanks.
 
3659 build active 2009 Feb anonymous build 2009 Feb   4 3 Building sqlite-tea: TEA version, install location, no tests
Downloaded sqlite-3_6_10-tea.tar.gz today and went ahead to compile it. This seemes to have produced a working extension, but I hit some snags along the way.

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?

 
3650 doc active 2009 Feb anonymous   2009 Feb   4 3 documentation error in c3ref/changes.html
lang_delete.html says: Prior to SQLite version 3.6.5, the truncate optimization also meant that the sqlite3_changes() and sqlite3_total_changes() interfaces and the count_changes pragma will not actually return the number of deleted rows. That problem has been fixed as of version 3.6.5.

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.

 
3647 new active 2009 Feb anonymous front 2009 Feb   4 3 parsing natural join over more than 2 tables is too inefficient
  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.

 
3639 new active 2009 Feb anonymous vfs 2009 Feb   3 1 Syntax error doesn't report a line number
When an attempt is made to import a SQL file containing 8226 INSERT statements, the following error is reported:

  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.

2009-Feb-05 00:29:24 by anonymous:
As a suggestion, the GNU error reporting format can be used. It is

  filename:linenumber:columnnumber: text

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

 
3629 doc active 2009 Feb anonymous front 2009 Feb   4 4 No error when selecting inappropriate columns when grouping.
Selecting a column not mentioned in a group by clause causes the column to be returned in an otherwise correct result set with what appears to be an existing but randomly selected value for that column. Here is an example:

  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.

2009-Feb-02 16:55:01 by anonymous:
My apologies for the way the query results appear. Trying again:

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

BREED COUNT(*)
Dandie 2
Scotty 3
Chihuahua 1

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

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

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

I hope that makes things clearer... Karl


2009-Feb-02 16:58:18 by drh:
This question comes up from time to time. Most other database engines do throw an error if a column appears in the result set of an aggregate query which is not a GROUP BY column. But some people prefer that this not be an error, because by not throwing an error they can get results that are distinct on some subset of columns in the result set:

   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.


2009-Feb-02 17:10:57 by anonymous:
I work with Karl and stumbled on this by entering an incorrect query, and I suspect most people do not expect arbitrary values from the group to be picked up, so returning on error would be the best behavior IMHO.

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


2009-Feb-03 08:44:48 by anonymous:
For me problem with this query is the "will be selected arbitrarily" part. I think database behaviour should be deterministic, and this query isn't. I guess it would be fine if there was some documented rule for chosing additional columns (for example max or min value).
 
3600 code active 2009 Jan anonymous   2009 Jan   5 4 Unnecessary ephemeral table
In SELECT below UNION ALL causes creation of ephemeral table. This can be avoided just like it is avoided without join.

  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|
 
1809 code active 2006 May anonymous CodeGen 2009 Jan   1 3 Huge slowdown/increased memory use when using GROUP BY on big dataset
This seemingly nonsensical query is a greatly reduced test case taken from several queries I use with SQLite 3.2.1. The real example joins various huge tables and much more complicated views. I'd like to upgrade beyond SQLite 3.2.1, but this is a showstopper.

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.

2006-May-13 03:01:28 by anonymous:
Seeing as this ticket concerns the GROUP BY statement it would make more sense to have an example like this:

  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.


2006-May-13 15:09:39 by anonymous:
This GROUP BY slowdown/memory increase is not specific to VIEWs. I repeated the test against a comparably sized table with the same results. You'll see this effect for any SELECT operating on a large number of rows using GROUP BY.


2006-May-13 16:44:04 by anonymous:
The slowdown first appears in SQLite 3.2.6 in check-in [2662] .


2006-May-24 13:19:29 by anonymous:
Here's an example to show an actual real-life use of GROUP BY in SQLite <= 3.2.5... Imagine performing mathematical operations on every combination of rows in several large tables for statistical analysis. The GROUP BY algorithm change in 3.2.6 now makes using GROUP BY on huge cross joins not usable for this purpose because it creates an intermediate result set of the product of all cross joins - several times larger than the size of the (already huge) database itself. Indexing is not useful in this case because there is nothing to index by design. All table rows must be traversed.

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.


2009-Jan-06 23:44:46 by anonymous:
Non-indexed GROUP BY query performance has regressed considerably from 3.2.1:

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?

 
3537 code active 2008 Dec anonymous   2008 Dec   5 4 sqlite3_bind_text + SQLITE_TRANSIENT + different encoding
If sqlite3_bind_text() or sqlite3_bind_text16() is called with SQLITE_TRANSIENT on database with different encoding data is copied twice. First time in sqlite3VdbeMemSetStr() possibly allocating new memory, second time in sqlite3VdbeMemTranslate() allways with call to sqlite3DbMallocRaw(). In that case sqlite3VdbeMemTranslate() can copy original data avoiding sqlite3VdbeMemGrow() + memcpy() in sqlite3VdbeMemSetStr().
 
3496 code active 2008 Nov anonymous   2008 Nov   4 4 FTS3: simple tokenizer ignores its first argument
Hello,

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

 
3446 code active 2008 Oct anonymous   2008 Oct   2 3 select hex(randomblob(8)) as a, count(*) from Table where b = a;
The query in the summary will return an empty value for the first result (the hex) if performed on an empty table, and will work if the table is not empty.

This behavior wasn't seen in 3.5.9.

2008-Oct-17 16:30:58 by drh:
The behavior of your query is undefined. Probably SQLite should be issuing an error rather than returning whatever it does.
 
3386 code active 2008 Sep anonymous   2008 Sep   4 4 insert default values does not work at the start of a trigger
Trying to emulate a sequence in a trigger with this:

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

 
3381 code active 2008 Sep anonymous   2008 Sep   2 1 SELECT DISTINCT returns incorrect results with alias in WHERE expr
When using column aliases in a SELECT DISTINCT query, using those column aliases in the WHERE expression does not work. The following example used to work in 3.6.1 and before, but no longer works in 3.6.2 (i.e. it produces an empty result set in 3.6.2):

	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?

 
3360 code active 2008 Sep anonymous   2008 Sep   4 4 undetected ambiguous column name
  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
 
3330 new active 2008 Aug anonymous   2008 Aug   3 4 faster large updates
Below is part of the code I'm using to implement ALTER DROP COLUMN. First version was based on UPDATE implementation. After removing fifo code runs significantly faster (8% and 11% on two test tables). Probably similar implementation can be used for updates that don't change rowid (I guess most of them don't).

   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
 
3310 code active 2008 Aug anonymous   2008 Aug   3 4 VFS xClose docs need update, code ignores returns mostly
The documentation for VFS xClose doesn't mention that the implementor must close even if they are returning an error.

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.

 
3303 new active 2008 Aug anonymous   2008 Aug   3 3 faster index creation for large tables
Currently pager_recycle() doesn't distinguish between index pages and data pages. I belive that keeping index pages in memory as long as it's possible will increase performance. To test it I'v changed implementation of pager_recycle from:

  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.

 
3285 new active 2008 Aug anonymous   2008 Aug drh 5 5 Add a module_list pragma to display all modules installed
Below is the code to display all installed modules with module_list pragma.

#ifndef SQLITE_OMIT_VIRTUALTABLE
  else if( sqlite3StrICmp(zLeft, "module_list")==0 ){
    int i = 0;
    HashElem *p;
    sqlite3VdbeSetNumCols(v, 2);
    pParse->nMem = 2;
    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seq", P4_STATIC);
    sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", P4_STATIC);
    for(p=sqliteHashFirst(&db->aModule); p; p=sqliteHashNext(p)){
      Module *pMod = (Module *)sqliteHashData(p);
      sqlite3VdbeAddOp2(v, OP_Integer, i++, 1);
      sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pMod->zName, 0);
      sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 2);
    }
  }
#endif
2008-Aug-11 19:24:43 by anonymous:
I would also appreciate API to enumerate modules similar to sqlite3_vfs_find().
 
3281 code active 2008 Aug anonymous   2008 Aug   2 3 FTS3 and JOIN, cannot use MATCH operator
Using a fts3-enabled sqlite and the following database:

  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'
   );
 
3282 doc active 2008 Aug anonymous   2008 Aug   5 4 VFS xAccess says parameter is flags - should be flag (singular)
The documentation and function prototypes for VFS xAccess says the parameter is flags (plural). This implies that flag values can be orred together, but they can't - a single flag is passed in.
 
3274 code active 2008 Aug anonymous   2008 Aug   1 1 "LIKE" does not work when searching for Arabic text
I have a select query that uses LIKE. I am searching for an Arabic word. If I use '=' it is working correctly, but if I replace '=' with LIKE, it is returning all word combination of the same length. Note that the same code is working perfectly for English, but not for Arabic. Regards,
2008-Aug-06 13:51:07 by danielk1977:
Can you post an example db and query? Thanks.
 
3266 doc active 2008 Aug anonymous   2008 Aug shess 2 3 "TokenizeR" or "Tokenize"? Docs conflict code
Documentation says that the user needs to specify the "tokenizer" clause when creating an FTS vtable. In fact, FTS expects "tokenize". If "tokenizer" is used, it is silently ignored (no SQL errors), and a default tokenizer is used.

Quoting the docs (Readme.tokenizers).


When creating a new full-text table, FTS3 allows the user to select the text tokenizer implementation to be used when indexing text by specifying a "tokenizer" clause as part of the CREATE VIRTUAL TABLE statement:

    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{
-------------------------
 
1431 doc active 2005 Sep anonymous   2008 Jun mihailim 1 1 Please can FAQ 7 reference http://www.sqlite.org/lockingv3.html
FAQ item 7 is unclear on when concurrent readers and writers may block. Please add a reference to http://www.sqlite.org/lockingv3.html which makes this clear.

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.

 
3115 code active 2008 May anonymous   2008 Jun   3 2 CREATE TABLE AS SELECT - file size
Input table was created with sqlite 3.3.8 using CREATE TABLE ... and INSERT INTO ... . There are 481 column and 95412 rows in the table. page_size is 32768. Created file size is about 86 M. After commands:
sqlite> attach t2 as t2;
sqlite> create table t2.t2 as select * from t1;

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

2008-Jun-12 14:12:38 by anonymous:
The problem is caused by real numbers. With separate inserts they ary converted to ints if possible. With CREATE TABLE AS SELECT zAffinity in OP_MakeRecord isn't set and sqlite3VdbeIntegerAffinity() isn't called.
 
3140 new active 2008 May anonymous   2008 May   4 3 [PATCH] Make the default FTS3 tokenizer UTF8-aware
The patch at https://bugzilla.mozilla.org/attachment.cgi?id=321766 is a cheap-and-cheerful way to enable UTF8 support for the default FTS3 "simple" tokenizer, by tokenizing all non-ASCII, non-punctuation UTF8 characters as its own token.
 
3117 code active 2008 May anonymous   2008 May   5 4 Lemon parser automaton listing improvement
The listing of the generated parser automaton (the .out file) is very useful in finding the cause of parse conflicts. When navigating that file, it is frequently necessary to jump from a reduce action to the used rule, or from a shift action to the target state - or the reverse of these. Simply searching for the number is not an ideal method, because it is ambiguous within that file.

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,"          ");
2008-May-15 11:13:33 by anonymous:
Severity/priority should be 5/4. I'm sure I set these, but they probably got lost in a human-computer interface snafu. Would love to change it, but can't now - Vincent Zweije <vzweije@wcc-group.com>


2008-May-15 13:04:09 by drh:
This is CVSTrac. Anybody (including anonymous) can change anything about any ticket at any time. It is completely open. So, yes you can still change the severity/priority...

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


2008-May-19 11:01:16 by anonymous:
Ah, right. Updating severity/priority now.

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.


2008-May-19 13:04:29 by anonymous:
It would be very useful if a ** Parsing conflict ** is encountered to print out the first (or any) example of the conflict on the same line. Lemon knows what caused the conflict, while a user has to deduce it.
 
3058 new active 2008 Apr anonymous   2008 Apr   3 5 optimizer chooses wrong indicies
I missed this while reporting #3033, here optimizer opts for a course much more expensive (no sorting index) unless IDX_beta_code appears to be huge.

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

2008-Apr-16 00:54:28 by drh:
Why don't you just not run ANALYZE?


2008-Apr-16 13:08:30 by anonymous:
I could not run analyze but then most of my indices don't get used effectively(tends to pick a fairly useless index on simple selects)

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


2008-Apr-16 13:14:47 by drh:

  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.


2008-Apr-16 14:12:45 by anonymous:
part of the problem here is that I actually want it to do a full scan on table a.

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


2008-Apr-16 15:16:13 by drh:
Tickets are for reporting bugs and requesting features. We will consider your request for enhancements to ANALYZE to be a feature request. It will be acted upon at low priority.

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.

 
3036 code active 2008 Apr anonymous   2008 Apr   4 4 ANALYZE considers all NULL values unique
The code that generates the sqlite_stat1 table during an analysis appears to consider all NULL values unique. If I'm reading the code right, this appears to be because of a "Ne" VDBE op-code that does not have the SQLITE_NULLEQUAL bit set (src/analysis.c:173). The end result is that an index on a column that contains nothing but NULL values will generate an extremely strong index rating, which can confuse the query engine.

  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.

 
2964 code active 2008 Feb anonymous   2008 Mar drh 1 1 Access Violation from VACUUM with 3.5.6 on Corrupt Database
After submitting the previous ticket ( #2954 ), I updated my source from 3.3.4 to 3.5.6. After updating, I was working with the same 500 MB database describe in that ticket, and found that performing a VACUUM on the corrupt database causes an access violation on a memory read. The sqlite3.exe utility (version 3.5.6) exhibits the same behavior. The 3.3.4 utility (and source) does not exhibit this behavior, and instead does what I would expect by returning the error it ran into, "database disk image is malformed".
2008-Feb-27 14:06:04 by anonymous:
Through a very long process, I was able to reduce the size of the database from 500 MB down to 40 MB. This is with all of the valid entries deleted, and just the corrupt entries (those that could not be deleted). After I got the database down to approx 40 MB it would not get any smaller, even when I deleted many more records.

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.


2008-Mar-20 20:10:41 by anonymous:
+1 for this issue. I ran into it while performing a manual VACUUM on an 80 MB database.


2008-Mar-20 21:17:15 by drh:
Someone please send me a database that exhibits this behavior and I will look into the problem.


2008-Mar-21 20:32:37 by anonymous:
I sent you a link to a database that you downloaded from me and you were able to recreate the issue with that database. Just let me know if I need to post the database again.
 
2413 code active 2007 Jun anonymous   2008 Feb drh 1 1 1 bug and 2 suggestions in lemon
Hello, ...
Sorry for my english :-) and if i post this with Severity/Priority error.
I found some not serious bug and have some suggetions.
=============================================================================
BUG FIX:
lemon.c for Win32. It not found lempar.c - backslash-bug.
function:
PRIVATE char *pathsearch(argv0,name,modemask);

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

Ups ... drh, sorry - title change.


2008-Feb-08 07:19:46 by anonymous:
Yes... it's will very good - FIRST SUGGESTION - add %memory_alloc/realloc/free

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

Thanks. Fred.

 
2917 code active 2008 Feb anonymous   2008 Feb   4 4 Tcl interface - busy callback confusion script/procedure
In the Tcl interface the "busy" method doesn't work if a script is supplied instead of a procedure:

  %  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
2008-Feb-01 12:31:45 by anonymous:
After researching this a little it appears this happens because the busy callback is invoked with an extra argument. The extra argument leads to an error but that error is only visible through errorInfo, not the result.

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""
 
2853 new active 2007 Dec anonymous   2008 Jan   2 3 optimizer fails to use an index on MAX subquery
i have these 2 identical queries with an index on (place_id,visit_date), the second query is about 2x fast the the first, while i'd expect that the MAX is faster than a limited order by clause... It's like the index is mis-used with MAX

  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;
2007-Dec-20 04:13:44 by anonymous:
Can you supply the schema of these tables and all related indexes?


2007-Dec-20 10:55:29 by danielk1977:
Correct. SQLite does not use the index to optimize the max() in the first query. But it does use it to optimize ORDER BY in the second.


2007-Dec-21 00:54:10 by anonymous:
the schema is that of mozilla firefox 3 places.sqlite, with an added index on moz_historyvisits(place_id,visit_date), this is extracted from a i bug i was working on. Do you need that i append the full schema here?

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


2007-Dec-21 15:59:58 by drh:
We will be enhancing the min()/max() optimization to be able to cover the case you describe above. But this will take some time to get right. If you are in a hurry, it seems like modifying your SQL is the easiest way to go.

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;


2007-Dec-31 06:41:03 by anonymous:
Regarding:

  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.


2007-Dec-31 07:06:22 by anonymous:
Given:

  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.


2008-Jan-01 19:21:51 by anonymous:
This ticket is related to WHERE cost estimation in ticket #2857.

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.


2008-Jan-05 18:15:39 by anonymous:
I appreciate that you have more refinements pending, but this query in particular has regressed from sqlite 3.5.4 using the schema mentioned above:

  -- 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
 
2771 code active 2007 Nov anonymous   2007 Nov   4 4 Lemon: Generated parser needs stdlib.h (not in default template)
I tested a simple do-nothing parser just to get lemon output, and this doesn't compile (if warnings treated as errors) because of non declaration of the memset() function for the following statment:

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

2007-Nov-14 21:02:25 by anonymous:
Sorry, the needed header is string.h, not stdlib.h :-)
 
2766 code active 2007 Nov drh   2007 Nov   1 1 TCL transaction started from within a query does not commit
This is a problem with the TCL interface. Consider the following TCL script:

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

 
2755 code active 2007 Nov anonymous   2007 Nov   3 3 trace interfere with transaction Tcl interface
When using the transaction method of the Tcl interface to the SQLite with a registered "trace" function, the stack trace is lost in case an error occurs inside the transaction.

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


First:

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


Second:

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

 
2729 doc active 2007 Oct anonymous   2007 Oct   1 1 Lemon: %fallback, %wildcard, and @X uncodumented
I noticed that the lemon documentation does not mention the %fallback and %wildcard directives. Both are in the code and are apparently doing useful work in SQLite's parse.y. Can other users benefit from them as well?

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?

 
2664 code active 2007 Sep danielk1977   2007 Sep   1 1 attaching the same db twice in shared-cache mode fails
The following SQL script can cause an assert() to fail in shared-cache mode.

  ATTACH 'db' AS aux1;
  ATTACH 'db' AS aux2;
  CREATE TABLE aux1.abc(a, b, c);
  CREATE TABLE aux2.abc(a, b, c);
See also #2653
 
2652 code active 2007 Sep drh   2007 Sep   1 1 Aggregate function cannot be used from within a subquery
The following SQL fails:

   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.

2007-Sep-23 16:01:09 by anonymous:
Your syntax appears to be incorrect.

  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()
 
2558 code active 2007 Aug anonymous   2007 Aug   2 3 Multiple JOIN USING() gives incorrect results
I'm having a problem joining multiple tables with USING. It appears to work, but the results are incorrect. Here is an example to illustrate the problem. I believe the three SELECT statements should be equivalent, but they produce three different results.

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

2007-Aug-08 17:34:27 by anonymous:
The problem is that SQLite is transforming

  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

 
2547 code active 2007 Aug danielk1977   2007 Aug   5 3 Changing db encoding of an attached db can confuse shared cache mode.
This is quite obscure, but in shared-cache mode:

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

 
2545 code active 2007 Jul anonymous   2007 Jul   1 4 Group by returns table name with field name
imaginate a table:
create table test (
id INTEGER PRIMARY KEY,
name varchar(50) not null,
age integer not null
);

Then:

insert into test (name,age) values ('foo',22);
insert into test (name,age) values ('foo',23);
insert into test (name,age) values ('bar',22);
insert into test (name,age) values ('bar',35);
insert into test (name,age) values ('bar',72);

Now try this;

sqlite> .headers on
sqlite> select test.name, test.age from test order by name;
name|age
bar|22
bar|35
bar|72
foo|22
foo|23
sqlite> select test.name, test.age from test group by name;
test.name|test.age
bar|72
foo|23

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.

2007-Jul-31 15:54:52 by anonymous:
There's probably 4 other tickets reporting this. I don't think it will get fixed. The workaround is to use aliases (AS "whatever") for the selected columns.


2007-Jul-31 23:02:19 by anonymous:
Ok, we have created a special support for SQLite.

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

Regards

 
2282 code active 2007 Apr anonymous   2007 Apr   3 4 Update on view with no matching trigger does not raise error
Attempting to update a view with no triggers properly fails with the error

  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
2007-Apr-18 21:50:00 by anonymous:
Your desired behavior can be accomplished by changing your trigger to:

  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;


2007-Apr-24 20:26:46 by anonymous:
I have come up with a sample patch that partially fixes this problem -- specifically, it raises an error if any rows affected by an update/delete against a view are not caught by any triggers. It does not handle uncaught inserts, however, because I couldn't quite figure out how to make that work (much of the logic for updates and deletes is almost identical, whereas the code for inserts is quite different.)

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

 
2214 code active 2007 Feb anonymous   2007 Feb   5 4 lemon generates bad error messages for %destructor
When using incorrect syntax for %destructor, lemon generates a bad error message. When I wanted to use %default_destructor, but used %destructor instead:

  %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

 
2128 code active 2006 Dec anonymous   2006 Dec   4 3 virtual table code doesn't verify type of rowid (calling xUpdate)
The virtual tables code doesn't verify the type of rowid when calling update. For example I used the following query:

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.

 
2127 code active 2006 Dec anonymous   2006 Dec   2 3 Virtual tables do not always free zErrmsg
The documentation for virtual tables and in particular the sqlite3_vtab structure says "The SQLite core will free and zero the content of zErrMsg when it delivers the error message text to the client application or when it destroys the virtual table."

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.

 
2093 code active 2006 Dec anonymous   2006 Dec   2 3 sqlite3_vtab_cursor doesn't have errMsg
The sqlite3_vtab_cursor structure doesn't have a zErrMsg pointer. Only the containing vtable does. This means that operations on cursor objects that have an error have to set the error on the vtable not the cursor. Unfortunately this means that there are race conditions since two different cursors on the same vtable could have errors at the same time. If the cursors are in different threads then a crash or worse can happen.
 
2089 code active 2006 Nov anonymous   2006 Nov   3 3 Decouple sqlite_int64 from other 64bit datatypes
Currently sqlite3 makes the (valid) assumption that sqlite_int64 (or i64, u64) is 64 bit wide, matches with Tcl_WideInt and has the same datasize (and byte order) than double. The following patch fixes this and allows sqlite_int64 to be any integral type, e.g. a 32bit int (with the limitations of the reduced datatype size).

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.

2006-Nov-29 01:13:07 by anonymous:
Hm, now I wanted to add the patch file but I don't get the formatting right without editing the file and removing empty lines. How am I supposed to add a patch file (created with diff -ru)?
 
1797 code active 2006 May anonymous TclLib 2006 May drh 1 1 COPY command doesn't work in tclsqlite 3.3.5
The COPY command doesn't seem to work in the tcl sqlite lib. This same script and datafile works in version 3.2.7.

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.

2006-May-05 17:57:42 by anonymous:
Clarification:

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$


2006-May-05 19:46:56 by anonymous:
I have narrowed it down to the code here in tclsqlite.c:

    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
    }


2006-May-16 17:51:28 by anonymous:
I found the problem. The first sqlite3_prepare under DB_COPY should have -1 as it's third argument. When this was change from a 0 to -1 the copy command works in tclsqlite.

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

should be

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


2006-May-16 18:01:11 by anonymous:
There is also another reference (the insert statement) to the prepare statement under DB_COPY that needs to change it's third argument from 0 to -1.


2006-Sep-27 16:24:53 by anonymous:
The same problem is present with version 3.3.7 over here. However, the indicated patch seem to work.
 
1493 code active 2005 Oct anonymous Parser 2005 Oct   3 3 lemon: pathsearch uses wrong directory separator under Win32
The pathsearch function in lemon.c uses a semicolon (;) to separate the directories in the path. Under Win32 systems this should be a colon (:).
2005-Oct-18 09:37:10 by anonymous:
--- 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;


2005-Oct-18 09:39:00 by anonymous:
More readable version of the patch:

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


2005-Oct-19 14:05:27 by anonymous:
Cygwin and other Posix emulation layers on Windows require ':' for path separators, so you cannot blindly rely on __WIN32__ to make this determination.
 
1111 code active 2005 Feb anonymous Unknown 2005 Jun   3 1 no such column error with a subselect as a table
Execute the following script. In 3.0.8, you get the following results:

  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;
2005-Feb-21 17:48:20 by anonymous:
Tested in 3.1.3, issue still exists


2005-Jun-12 22:09:08 by drh:
Workaround:

  select test2.b 
  from (select test1.a as a from test1 where a%2=0) as bb
       join test2 on bb.a=test2.a;
 
1262 new active 2005 May anonymous VDBE 2005 May   5 3 5-15% performance increase for slow joins
Attaching an optimization to sqlite3VdbeRecordCompare() that gives a speedup of 5-15% for certain SELECTs with join.

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.

2005-May-22 12:52:08 by anonymous:
I don't have the neccessary infrastructure to run the unit tests, so it's likely that unit tests fail since I only have done limited testing of the code.

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


2005-May-22 19:56:29 by anonymous:
Looks like negative doubles don't compare as easy as I thought. Please wait for a fix before comitting.


2005-May-22 21:25:49 by anonymous:
Attaching file that passes the unit tests.
 
1078 code active 2005 Jan anonymous   2005 Jan   2 3 Lemon destructor bugs that don't affect sqlite
I found a few bugs Lemon's destructor handling code. I don't think that they affect sqlite, but the do affect other grammars.

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

2005-Jan-14 13:33:52 by drh:
Do you also have some test grammars? That would really be helpful.


2005-Jan-14 17:14:15 by anonymous:
Sure. Here is one grammar that will demonstrate the "Tokens leak when rule has no code" bug:

 
%token_type { char * }
%token_destructor 
{ 
    printf("Deleting token '%s' at %x\n", $$, (int)$$); 
    free($$); 
}
result ::= nt. 
nt ::= FOO BAR.
Running the following code against the grammar should theoretically show 2 allocations and two destructions. It won't though, unless you modify the rule for nt to have an empty body, like:
nt ::= FOO BAR. {}
char *mkStr(const char *s)
{
    printf("Allocating '%s' at 0x%x\n", s, (int)(s));
    return strdup(s);
}

int main(int argc, char **argv)
{
    void *parser = ParseAlloc(malloc);
    Parse(parser, FOO, mkStr("foo"));
    Parse(parser, BAR, mkStr("bar"));
    Parse(parser, 0, 0);
    ParseFree(parser, free);
    return 0;
}



2005-Jan-14 17:50:26 by anonymous:
Here is another test grammar. This one demonstrates (a) default destructors being called on the 'error' symbol, and (b) problems with default destructors being called on the wrong symbol type.

%token_type { char * }
%token_destructor { delete [] $$; }
%default_destructor { delete $$; }

%type result { int }
%destructor result { }
result ::= fooStruct barStruct. { }

%type fooStruct { Foo * }
fooStruct(lhs) ::= FOO(f). { lhs = new Foo(f); }

%type barStruct { Bar * }
barStruct(lhs) ::= BAR(b). { lhs = new Bar(b); }
Here is the code generated by lemon (with comments added & removed for clarity):

typedef union {
  ParseTOKENTYPE yy0;
  int yy4;
  Bar * yy5; 
  Foo * yy7; 
  int yy15;
} YYMINORTYPE;

static const char *const yyTokenName[] = { 
  "$",             "FOO",           "BAR",           "error",       
  "result",        "fooStruct",     "barStruct",   
};


static void yy_destructor(YYCODETYPE yymajor, YYMINORTYPE *yypminor){
  switch( yymajor ){
    case 1:
    case 2:
{ delete [] (yypminor->yy0); }
      break;
    case 3: /* error */
    case 5: /* fooStruct of type "Foo *"  */ 
    case 6: /* barStruct of type "Bar *"  */ 
#line 3 "typeBug.y"
{ delete (yypminor->yy5); }  /* Yikes! yy5 is a "Bar *" */
#line 308 "typeBug.c"
      break;
    case 4:
#line 6 "typeBug.y"
{ }
#line 313 "typeBug.c"
      break;
    default:  break;   /* If no destructor action specified: do nothing */
  }
}
 
1063 code active 2005 Jan anonymous   2005 Jan   1 3 Lemon bug: Strings in rule code should not be interpreted
There are two related bugs in the lemon parser related to processing code snippets defined in rule actions.

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.

 
969 code new 2004 Oct anonymous TclLib 2005 Jan   3 3 PRAGMA empty_result_callbacks not working in tclsqlite-3.0.8.so part 2
Referencing ticket # 967, I stated that it was the tclsqlite code that was not functioning properly, not the ./sqlite3 executable.

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
2006-May-16 18:29:44 by anonymous:
This is still a problem in the 3.3.5 version of the tclsqlite library. The tclsqlite.c code never calls the callback code on empty results when PRAGMA empty_result_callbacks=1 is set.
Rows: 74