Small. Fast. Reliable.
Choose any three.
Key: Priority 1 Priority 2 Priority 3+ Under Review
# Status Created By Subsys Due Date SCR Assigned Svr Pri Title
Description
Remarks
 
969 new 2004 Oct anonymous TclLib Pending     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.
 
1063 active 2005 Jan anonymous   Pending     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.

 
1078 active 2005 Jan anonymous   Pending     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 */
  }
}
 
1111 active 2005 Feb anonymous Unknown Pending     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;
 
1493 active 2005 Oct anonymous Parser Pending     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.
 
1598 review 2006 Jan anonymous   Pending     3 2 Incorrect case-insensitive comparison of non-latin UTF-8 characters
Sqlite incorrectly compares case-insensitivly UTF-8 non-latin characters. I created a patch that fixes this problem and posted it to the mailing list. I wonder if someone could review my patch and eventually include it in the main project.

Regards Stanislav Nikolov

2006-Jan-10 22:49:08 by drh:
The sqlite3_create_collation() and sqlite3_create_function() APIs exists for the purpose of allowing users to define comparisons and any other operations in any way they see fit. There is no need to make changes to the SQLite core to accomodate cyrillic comparisons. Indeed, there are good reasons not to, namely if we correctly compare cyrillic, we should also need to correctly compare chinese, japanese, and korean to name but a few. Very quickly the comparison functions can grow to be many many times larger than the rest of SQLite. We conclude, therefore, that this is all best left to the discretion of the programmer who uses SQLite in their project. Hence we provide the afore mentioned sqlite3_create_collation() and sqlite3_create_function() APIs.


2006-Jan-11 00:24:50 by anonymous:
Okay, let me try that again. First, the patch I created does not correspond only to Cyrillic letters, but also to the Greek and the accented characters up to U+044F. According to UNICODE.ORG there are actually five alphabets in the world (of which one does not use cases anymore) that have different cases:

(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


2006-Jan-11 00:33:19 by drh:
Please attach the patch to this ticket.


2006-Jan-11 00:52:38 by drh:
OK, I was able to reconstruct the patch from the mailing list.

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.


2006-Jan-11 03:10:09 by drh:
Based on what I can glean from http://www.unicode.org/Public/UNIDATA/CaseFolding.txt, the case folding table in the patch seems to be incomplete. A full unicode case folding table would need to be much larger.

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


2006-Jan-11 09:52:38 by anonymous:
I think that the size of the library could be effectivly shrunk if we don't use an array, because over 50% of the information is redundant. I think that the same effect could be achieved by changing sqlite3UpperToLower[] to a function, and in there to check for the ranges of the capital letters (that is, we need to check for 4-5 different regions and return x+20 ort x+1 for capital letters for example). I can try do that?


2006-Jun-08 10:32:06 by anonymous:
Has anybody worked on this lately? This is quite an issue if you happen to use non-latin chars.

Keep up the good work.

Anze


2006-Oct-11 10:58:15 by anonymous:
could you tell me how to run the patch for WindowXP?

Thanks a lot.

 
1797 active 2006 May anonymous TclLib Pending   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.
 
1809 active 2006 May anonymous CodeGen Pending     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?

 
2089 active 2006 Nov anonymous   Pending     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)?
 
2093 active 2006 Dec anonymous   Pending     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.
 
2122 review 2006 Dec anonymous   Fixed     1 3 Potential wrong string could stay in with convertUtf8Filename
sqlite3WinOpenExclusive uses the following code to convert the utf8 string to a os string:

  void *zConverted = convertUtf8Filename(zFilename);
  if( zConverted==0 ){
    return SQLITE_NOMEM;
  }
and use another string converted with another function to save the filename that will be deleted in windows ce:

#if OS_WINCE
  f.zDeleteOnClose = delFlag ? utf8ToUnicode(zFilename) : 0;
  f.hMutex = NULL;
#endif
also, the variable fileflags should be a DWORD (unsigned integer 32-bit) instead of a native C compiler integer.

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);
}
2006-Dec-21 00:48:28 by drh:
On wince, convertUtf8Filename() calls utf8ToUnicode() to do its work, so these routines end up computing exactly the same filename.


2006-Dec-21 01:30:33 by anonymous:
Yes, I figured out that they still calling the same function, but why converting / freeing then converting agains ? just save some CPU cycles (this could help a lot in Windows CE < 200 mhz, just like my app runs (windows ce 2.11, SH3 130MHz HP Jornada 680)!!! Please apply my patch to make it faster.
 
2127 active 2006 Dec anonymous   Pending     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.

 
2128 active 2006 Dec anonymous   Pending     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.

 
2171 review 2007 Jan anonymous   Fixed   drh 3 3 Fix a major leakage of token minors in lemon generated parsers
Fix a major leakage of token minors in lemon generated parsers due to the fact that lemon was not genmerating destructor code for elements in the RHS of rules without C-code.

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;
It appears that the change in [3593] causes yy_destructor() to be called more than once for tokens, while the one posted here (and applied to wireshark) does not. in the meanwhile I changed the wireshark copy back to have
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 :-)

 
2371 new 2007 May anonymous   Pending     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.
 
2413 active 2007 Jun anonymous   Pending   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.

 
2547 active 2007 Aug danielk1977   Pending     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".

 
2558 active 2007 Aug anonymous   Pending     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

 
2652 active 2007 Sep drh   Pending     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()
 
2664 active 2007 Sep danielk1977   Pending     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
 
2755 active 2007 Nov anonymous   Pending     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

 
2766 active 2007 Nov drh   Pending     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....

 
2940 review 2008 Feb anonymous   Cannot_Reproduce     3 3 error codes and messages from functions don't get through step()
If I register a function with sqlite3_create_function() and it sets an error code with sqlite3_result_error_code() and an error message with sqlite3_result_error(), the error message is not visible (as the return from sqlite3_errmsg()) after calling sqlite3_step(), it is only visible after calling sqlite3_reset(). This is not the behavior that I would expect when using sqlite3_statement_prepare_v2(), although the documentation only explicitly describes what happens with error codes.

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;
}
2008-Feb-13 04:33:24 by danielk1977:
The sqlite3_result_error() function does two things - sets the error message and sets the error code to SQLITE_ERROR. So the call to sqite3_result_error() is overwriting the error code set by result_error_code(). Switch the order of the two calls and it will work the way you expect.

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


2008-Feb-13 22:29:53 by anonymous:
Yup, re-ordering setting the error-code and message 'fixes' that issue, although that should be documented somewhere.

The other issue remains.


2008-Apr-10 17:14:34 by drh:
UInable to reproduce the problem. Added tests to verify correct operation.


2008-Apr-22 20:24:35 by anonymous:
drh, the test case shown above (with the two lines in func() reversed, per earlier remarks) still shows the problem against version 3.5.8. Ticket #2371 has another test case that also shows the bug, along with a patch to fix it. Your own tests in [4983] don't catch the problem because DbObjCmd() in the Tcl bindings calls sqlite3_reset() on failed statements before calling sqlite3_errmsg(), which defeats the whole point of checking for the bug: the error message does show up correctly after sqlite3_reset() has been called.
 
2964 active 2008 Feb anonymous   Pending   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.
 
3051 review 2008 Apr anonymous   New     4 3 Even after setting pragma short_column_names the complete name is stil
After sql-query prepare one field (ID_Good) in example still has name o.ID_Good even after setting pragma short_column_names the complete name is still returned.

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

2008-Apr-13 12:05:10 by drh:
http://www.sqlite.org/pragma.html#pragma_short_column_names

You must also turn off the long_column_names pragma.


2008-May-02 01:21:57 by anonymous:
The original poster could not provide a clear example of the bug so i'm trying to do now. The bug exists even when short_column_names is set and when full_column_names is not set and is related to using an alias to a view.

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)


2008-Jun-11 13:00:36 by anonymous:
This is still broken with 3.5.9 but worked fine with 3.2.7.

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>
 
3115 active 2008 May anonymous   Pending     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.
 
3128 active 2008 May anonymous   Pending     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.

 
3274 active 2008 Aug anonymous   Pending     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.
 
3281 active 2008 Aug anonymous   New     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'
   );
 
3343 active 2008 Aug rdc   Pending     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|
 
3381 active 2008 Sep anonymous   Pending     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?

 
3446 active 2008 Oct anonymous   Pending     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.
 
3658 active 2009 Feb anonymous   Pending Universal   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.
 
3666 active 2009 Feb anonymous   Pending Universal   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);

 
3734 active 2009 Mar danielk1977 back Pending Universal   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) }
 
3857 active 2009 May anonymous   New Universal   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
 
3902 review 2009 Jun anonymous front Not_A_Bug Universal   3 2 The PRAGMA full_column_names is not working
(Sorry by my bad english)

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:

field1 field2 field3

when it would to return:

table.field1 table.field2 table.field3

If we use this command:

SELECT rowid,* FROM table

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

table.rowid field1 field2 field3

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:


I discovered that that line was there to use that block of code if there is an alias (AS) in the column, but it doesn't work. Instead I have made a workaround or solved it doing this line of code:

    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);
2009-Jun-08 12:35:00 by drh:
Use:

   PRAGMA short_column_names=OFF;
   PRAGMA full_column_names=ON;


2009-Jun-08 22:00:34 by anonymous:

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!


2009-Jun-09 00:57:51 by drh:
The documentation was wrong and has been corrected. See http://www.sqlite.org/draft/pragma.html#pragma_full_column_names


2009-Jun-09 01:42:20 by anonymous:
Then I don't know why the short_column_names does exist, because in the way the new documentation is written, if it is ON or OFF the result will be the same.

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?

 
3987 active 2009 Jul anonymous   Pending Universal   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.

 
4009 active 2009 Aug anonymous   Pending Universal   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...

Rows: 39