sqlite - Known Issues with descriptions

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

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.

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

  line 0
  line 2

in 3.1.1 beta, you get the following error:

  SQL error: no such column: bb.a

SCRIPT:

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

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

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

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

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

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

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

Regards Stanislav Nikolov

(from http://www.unicode.org/reports/tr21/tr21-5.html#Introduction) Case is a normative property of characters in specific alphabets (Latin, Greek, Cyrillic, Armenian, and archaic Georgian) whereby characters are considered to be variants of a single letter. These variants, which may differ markedly in shape and size, are called the uppercase letter (also known as capital or majuscule) and the lower­case letter (also known as small or minuscule). The uppercase letter is generally larger than the lowercase letter. Alphabets with case differences are called bicameral; those without are called unicameral.

Therefore, I don't think someone will need support for case-insensitive comparison for Japanese, Chinese or Korean characters and I guess that adding support for the remaining Armenian alphabet is a matter of minutes and will not add up to the complexity of the code. Of course, perhaps it is possible for every project and/or developer to design their own "collation schemes" but I don't find it very practical. I can't really see the reason behind rejecting the patch. Perhaps you could actually look at it ?

Regards, Stanislav Nikolov

I observer that as written it increases the size of the SQLite library by a little over 4KiB. That might not seem like much, but embedded device manufacturers (that is to say, most of my paying customers) are very sensitive to this kind of library size growth. I will look into reducing the size somewhat and getting it into a future release as a compile-time option.

Perhaps somebody with more experience in unicode case folding can comment.

Keep up the good work.

Anze

Thanks a lot.

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

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

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

3.3.5

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

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

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

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

should read

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

However the result is the same:

-bash-3.00$ tclsh test_sqlite.tcl 3.3.5

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

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

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

should be

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

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

  PRAGMA temp_store=MEMORY;

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

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

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

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

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

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

3.6.7: 140 seconds, 233M RAM used

3.3.5: 185 seconds, 230M RAM used

3.2.1: 13 seconds, 1.2M RAM used

Any plan to speed it up?

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.

  void *zConverted = convertUtf8Filename(zFilename);
  if( zConverted==0 ){
    return SQLITE_NOMEM;
  }

#if OS_WINCE
  f.zDeleteOnClose = delFlag ? utf8ToUnicode(zFilename) : 0;
  f.hMutex = NULL;
#endif

the following patch correct those things:

int sqlite3WinOpenExclusive(const char *zFilename, OsFile **pId, int delFlag){
  winFile f;
  HANDLE h;
  DWORD fileflags;
  void *zConverted = convertUtf8Filename(zFilename);
  if( zConverted==0 ){
    return SQLITE_NOMEM;
  }
  assert( *pId == 0 );
  fileflags = FILE_FLAG_RANDOM_ACCESS;
#if !OS_WINCE
  if( delFlag ){
    fileflags |= FILE_ATTRIBUTE_TEMPORARY | FILE_FLAG_DELETE_ON_CLOSE;
  }
#endif
  if( isNT() ){
    int cnt = 0;
    do{
      h = CreateFileW((WCHAR*)zConverted,
         GENERIC_READ | GENERIC_WRITE,
         0,
         NULL,
         CREATE_ALWAYS,
         fileflags,
         NULL
      );
    }while( h==INVALID_HANDLE_VALUE && cnt++ < 2 && (Sleep(100), 1) );
  }else{
#if OS_WINCE
    return SQLITE_NOMEM;
#else
    int cnt = 0;
    do{
      h = CreateFileA((char*)zConverted,
        GENERIC_READ | GENERIC_WRITE,
        0,
        NULL,
        CREATE_ALWAYS,
        fileflags,
        NULL
      );
    }while( h==INVALID_HANDLE_VALUE && cnt++ < 2 && (Sleep(100), 1) );
#endif /* OS_WINCE */
  }
  if( h==INVALID_HANDLE_VALUE ){
    sqliteFree(zConverted);
    return SQLITE_CANTOPEN;
  }
  f.h = h;
#if OS_WINCE
  f.zDeleteOnClose = delFlag ? (WCHAR*) zConverted : 0;
  f.hMutex = NULL;
  if (!delFlag)
#endif
    sqliteFree(zConverted);
  TRACE3("OPEN EX %d \"%s\"\n", h, zFilename);
  return allocateWinFile(&f, pId);
}

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.

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.

This is a copy of a fix mafe to the lemon parser used by the wireshark project http://wireshark.org

http://anonsvn.wireshark.org/viewvc/viewvc.py/trunk/tools/lemon/lemon.c?r1=20442&r2=20441&pathrev=20442

--- trunk/tools/lemon/lemon.c	2007/01/15 19:11:29	20441
+++ trunk/tools/lemon/lemon.c	2007/01/15 19:48:06	20442
@@ -3245,6 +3245,8 @@
   for(i=0; i<rp->nrhs; i++) used[i] = 0;
   lhsused = 0;

+  if (! rp->code) rp->code = "\n";
+
   append_str(0,0,0,0);
   for(cp=rp->code; *cp; cp++){
     if( safe_isalpha(*cp) && (cp==rp->code || (!safe_isalnum(cp[-1]) && cp[-1]!='_')) ){
@@ -3875,7 +3877,7 @@

   /* Generate code which execution during each REDUCE action */
   for(rp=lemp->rule; rp; rp=rp->next){
-    if( rp->code ) translate_code(lemp, rp);
+		translate_code(lemp, rp);
   }
   for(rp=lemp->rule; rp; rp=rp->next){
     struct rule *rp2;

if (! rp->code)  rp->code = "\n";

   append_str(0,0,0,0);
   for(cp=rp->code; *cp; cp++){

but there's an issue with this the, generated parser gets

#line 0 "./dtd_grammar.lemon"

instead of the number pointing to the line where the rule is.

I'll keep investigating and keep you POSTed (literally :-)

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

See the example output below :

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

Code for this example:

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

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

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

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

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

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

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

PATCH:

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

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

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

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

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

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

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

It's bad for solutions where is no stdlib.

My suggestion is

FIRST WAY:

To add to yyParser struct 3 variables like

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

and add 3 directives like

%memory_alloc
%memory_realloc
%memory_free

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

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

OR SECOND WAY (very simple):

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

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

store reallocProc in mem_realloc_fn in yyParser

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

and use it for reallocating memory in parser.

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

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

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

Can you fix type difference, please?

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

WARNINGS:

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

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

Thanks. Fred.

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

  .header on
  .mode column

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

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

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

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

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

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

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

Joining Main, OptA, and OptB omits Beta2:

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

Joining Main, OptB, and OptA omits Alpha1:

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

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

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

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

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

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

into

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

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

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

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

Joe Wilson

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

Problem reported on the mailing list.

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

What you wanted to do:

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

What you did:

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

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

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

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

   > ./a.tcl
BEGIN
vorher
ROLLBACK

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

********

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

Thanks

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

The error codes set with sqlite3_result_error_code() never seems to be visible either as the return from sqlite3_step() or from sqlite3_errcode(), even after calling sqlite3_reset();

Here is my sample code.

#include "sqlite3.h"
#include <stdio.h>

int func(sqlite3_context *context, int nargs, sqlite3_value**args)
{
sqlite3_result_error_code(context, SQLITE_ABORT);
sqlite3_result_error(context, "shoot", -1);
}

int main()
{
  sqlite3 *db = NULL;
  sqlite3_stmt *stmt = NULL;
  char **err = NULL;
  sqlite3_open(":memory:", &db);
  sqlite3_create_function(db, "foo", 1, SQLITE_UTF8, NULL, func, NULL, NULL);
  sqlite3_prepare_v2(db, "SELECT foo('')", -1, &stmt, NULL);
  printf("step %d\n", sqlite3_step(stmt));
  printf("step error code %d message %s\n", sqlite3_errcode(db), sqlite3_errmsg(db));
  sqlite3_reset(stmt);
  printf("reset error code %d message %s\n", sqlite3_errcode(db), sqlite3_errmsg(db));
  sqlite3_finalize(stmt);
  sqlite3_close(db);
  return 0;
}

Maybe sqlite3_result_error() should only set the error code if it has not yet been set when it is called...

The other issue remains.

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.

sql-query text and test database is here http://rapidshare.com/files/107078408/sqlite-alias.bug.tar.bz2.html

You must also turn off the long_column_names pragma.

Take the following db structure:

CREATE TABLE [TT] ( [ID] INTEGER, [P] INTEGER, [D] INTEGER, [S] varcHAR(100), [F] FLOAT, [Good_Id] INTEGER);

CREATE VIEW ViewTT as Select * from TT;

Set PRAGMA short_column_names to 1 and full_column_names to 0

Now do the query with the table directly:

Select o.Good_ID from TT o

The returned field name will be Good_ID (This is Ok)

Now do the query with the view:

Select o.Good_ID from ViewTT o

The returned field name will be o.Good_ID (This is NOT Ok)

Broken:

  sqlite3 test.db
  SQLite version 3.5.9
  Enter ".help" for instructions
  sqlite> create table test ( a text);
  sqlite> insert into test values ('abcdefg');
  sqlite> .header on
  sqlite> select * from test;
  a
  abcdefg
  sqlite> PRAGMA short_column_names;
  short_column_names
  1
  sqlite> PRAGMA full_column_names;
  full_column_names
  0
  sqlite> select test.a from test;
  a
  abcdefg
  sqlite> create view test_v as select * from test;
  sqlite> select test_v.a from test_v;
  test_v.a
  abcdefg
  sqlite>

Correct:

  sqlite3 test.db
  SQLite version 3.2.7
  Enter ".help" for instructions
  sqlite> create table test (a text);
  sqlite> insert into test values ('abcdefg');
  sqlite> .header on
  sqlite> select * from test;
  a
  abcdefg
  sqlite> pragma short_column_names;
  short_column_names
  1
  sqlite> pragma full_column_names;
  full_column_names
  0
  sqlite> select test.a from test;
  a
  abcdefg
  sqlite> create view test_v as select * from test;
  sqlite> select test_v.a from test_v;
  a
  abcdefg
  sqlite>

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

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.

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

For example given:

  create table t(id, a text);

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

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

However this query produces incorrect results.

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

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

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

This behaviour already caused implementation problems: #2251

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

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

	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?

This behavior wasn't seen in 3.5.9.

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

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

I run this sql from a file into sqlite:

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

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

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

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

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

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

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

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

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

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

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

When I use the "PRAGMA full_column_names = 1" command, the requested field names does not retturn with the table name if we use a command like this:

SELECT * FROM table

It returns:

when it would to return:

If we use this command:

SELECT rowid,* FROM table

The result comes with the table name only in the rowid field:

I noticed in code that on select.c where it has this line:

    if( pEList->a[i].zName ){

it evaluates to false on rowid but evaluates to true on the other fields, then executes the following:

      char *zName = pEList->a[i].zName;
      sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_TRANSIENT);

that does not add the table name to the fields. The code that does it is after the following line:

    }else if( (p->op==TK_COLUMN || p->op==TK_AGG_COLUMN) && pTabList ){

WHAT I MADE TO SOLVE THIS:

    if( pEList->a[i].zName && (sqlite3StrNICmp(pEList->a[i].zName, (char*)p->span.z, p->span.n) != 0){

I added the code

   (sqlite3StrNICmp(pEList->a[i].zName, (char*)p->span.z, p->span.n) != 0)

to the line, that evaluates wether the expression token string (p->span.z) is equal to the alias name of the expression (pEList->a[i].zName).

I hope that some programmer put this to the sqlite code.

And thank you for your work, I like too much this piece of code.

    The diff file follows bellow:
--- C:/sqlite3/sqlite-source-3_6_14_2/select.c	Mon May 25 08:49:40 2009
+++ C:/sqlite3/sqlite-source-3_6_14_2/select.c	Mon Jun 08 08:01:30 2009
@@ -1045,9 +1045,11 @@
     Expr *p;
     p = pEList->a[i].pExpr;
     if( p==0 ) continue;
-    if( pEList->a[i].zName ){
+    // Check wether there is an alias name for the expression or column.
+    if( pEList->a[i].zName && (sqlite3StrNICmp(pEList->a[i].zName, (char*)p->span.z, p->span.n) != 0)){
       char *zName = pEList->a[i].zName;
       sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_TRANSIENT);
+    // Check wether this is a table column.
     }else if( (p->op==TK_COLUMN || p->op==TK_AGG_COLUMN) && pTabList ){
       Table *pTab;
       char *zCol;
@@ -1064,16 +1066,17 @@
       }else{
         zCol = pTab->aCol[iCol].zName;
       }
-      if( !shortNames && !fullNames ){
-        sqlite3VdbeSetColName(v, i, COLNAME_NAME,
-            sqlite3DbStrNDup(db, (char*)p->span.z, p->span.n), SQLITE_DYNAMIC);
-      }else if( fullNames ){
+      if ( shortNames ) {
+        sqlite3VdbeSetColName(v, i, COLNAME_NAME, zCol, SQLITE_TRANSIENT);
+      }else if( fullNames || pTabList->nSrc>1 ){
         char *zName = 0;
         zName = sqlite3MPrintf(db, "%s.%s", pTab->zName, zCol);
         sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_DYNAMIC);
-      }else{
-        sqlite3VdbeSetColName(v, i, COLNAME_NAME, zCol, SQLITE_TRANSIENT);
+      } else {  //( !shortNames && !fullNames )
+        sqlite3VdbeSetColName(v, i, COLNAME_NAME,
+            sqlite3DbStrNDup(db, (char*)p->span.z, p->span.n), SQLITE_DYNAMIC);
       }
+    // If we falls here, it is a expression without an alias. ex.: SELECT 1+1 From table
     }else{
       sqlite3VdbeSetColName(v, i, COLNAME_NAME,
           sqlite3DbStrNDup(db, (char*)p->span.z, p->span.n), SQLITE_DYNAMIC);
@@ -3209,7 +3212,7 @@
               pExpr->span = pExpr->token;
               pExpr->span.dyn = 0;
             }
-            if( longNames ){
+            if( longNames || pTabList->nSrc>1 ){
               pNew = sqlite3ExprListAppend(pParse, pNew, pExpr, &pExpr->span);
             }else{
               pNew = sqlite3ExprListAppend(pParse, pNew, pExpr, &pRight->token);

   PRAGMA short_column_names=OFF;
   PRAGMA full_column_names=ON;

Related to the documentation, for the queries WITH JOINS, when:

  PRAGMA short_column_names=ON;
  PRAGMA full_column_names=OFF;
  SQLite may return: only column names
  And it really returns: only column names

  PRAGMA short_column_names=ON;
  PRAGMA full_column_names=ON;
  SQLite may return: only column names
  And it really returns: only column names

  PRAGMA short_column_names=OFF;
  PRAGMA full_column_names=ON;
  SQLite may return: table and column names
  And it really returns: table and column names

  PRAGMA short_column_names=OFF;
  PRAGMA full_column_names=OFF;
  SQLite may return: table and column names
  BUT IR RETURNS: only column names      <<<----

The Documentation:

Result columns are named by applying the following rules in order:

1. If there is an AS clause on the result, then the name of the column is the right-hand side of the AS clause.

2. If the result is a general expression, not a just the name of a source table column, then the name of the result is a copy of the expression text.

3. If the short_column_names pragma is ON, then the name of the result is the name of the source table column without the source table name prefix: COLUMN.

-> 4. If both pragmas short_column_names and full_column_names are OFF then case (2) applies for simple queries and case (5) applies for joins.

5. The name of the result column is a combination of the source table and source column name: TABLE.COLUMN

And another thing:

When we have SIMPLE QUERIES, the configuration bellow:

  PRAGMA short_column_names=ON;
  PRAGMA full_column_names=ON;

may return the table name together with the column names. I understand that the short_column_names is used only for joins. It is what we understand reading the docs (above), and it is logical.

The code that I made works like the documentation. Please, take a look at the diff.

I will make new checking to the code changes at this night cause I think that there is code that is not needed there. Bye!

In the old way the short_column_names had a reason to exist.

And if someone want that the simple queries return only column names and the join queries return the table name with the columns... what configuration we may use?

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

  SELECT * FROM t ORDER BY a LIMIT 1

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

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

.schema

SELECT * FROM slow_concat;

10 mins for SQLite

2.5 secs for MySQL

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

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

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

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