Hi all,
I'm sorry about the inconvenience. I sent the e-mail below to [EMAIL
PROTECTED]
but I do not know if it's the correct e-mail to tell about possible
bugs.
So, I'm just forwarding my yesterday's e-mail in sqlite-users@sqlite.org
. Please check the message below, any help is appreciated.
Begin forwarded message:
> From: Alessandro Merolli <[EMAIL PROTECTED]>
> Date: December 4, 2008 5:33:10 PM GMT-02:00
> To: [EMAIL PROTECTED]
> Subject: Access violation in SQLITE3.DLL when using views joining
> with other views ...
>
> Hi all,
>
> I'm facing some problems with SQLite since version 3.6.x was
> released.
> Until now, I was working with version 3.5.4 without errors but, I
> need to upgrade to version 3.6 because it seems more efficient in
> terms of database locking, performance, pragma options, etc...
>
> Last week I upgraded to version 3.6.6.2 and I'm still getting an
> access violation with the following setup:
>
> - I'm working in Microsoft Windows XP SP3 (although the error was
> reproduced in Linux and MacOS environments)
>
> - The database structure is defined like this:
> CREATE TABLE Element (
> Code INTEGER PRIMARY KEY,
> Name VARCHAR(60)
> );
>
> CREATE TABLE ElemOr (
> CodeOr INTEGER NOT NULL,
> Code INTEGER NOT NULL,
> PRIMARY KEY(CodeOr,Code)
> );
>
> CREATE TABLE ElemAnd (
> CodeAnd INTEGER,
> Code INTEGER,
> Attr1 INTEGER,
> Attr2 INTEGER,
> Attr3 INTEGER,
> PRIMARY KEY(CodeAnd,Code)
> );
>
> - The data used in the test is:
> INSERT INTO Element VALUES(1,'Elem1');
> INSERT INTO Element VALUES(2,'Elem2');
> INSERT INTO Element VALUES(3,'Elem3');
> INSERT INTO Element VALUES(4,'Elem4');
> INSERT INTO Element VALUES(5,'Elem5');
> INSERT INTO ElemOr Values(3,4);
> INSERT INTO ElemOr Values(3,5);
> INSERT INTO ElemAnd VALUES(1,3,1,1,1);
> INSERT INTO ElemAnd VALUES(1,2,1,1,1);
>
> - And the views which are causing the access violation are defined
> like this:
> CREATE VIEW ElemView1 AS
> SELECT
> CAST(Element.Code AS VARCHAR(50)) AS ElemId,
> Element.Code AS ElemCode,
> Element.Name AS ElemName,
> ElemAnd.Code AS InnerCode,
> ElemAnd.Attr1 AS Attr1,
> ElemAnd.Attr2 AS Attr2,
> ElemAnd.Attr3 AS Attr3,
> 0 AS Level,
> 0 AS IsOrElem
> FROM Element JOIN ElemAnd ON ElemAnd.CodeAnd=Element.Code
> WHERE ElemAnd.CodeAnd NOT IN (SELECT CodeOr FROM ElemOr)
> UNION ALL
> SELECT
> CAST(ElemOr.CodeOr AS VARCHAR(50)) AS ElemId,
> Element.Code AS ElemCode,
> Element.Name AS ElemName,
> ElemOr.Code AS InnerCode,
> NULL AS Attr1,
> NULL AS Attr2,
> NULL AS Attr3,
> 0 AS Level,
> 1 AS IsOrElem
> FROM ElemOr JOIN Element ON Element.Code=ElemOr.CodeOr
> ORDER BY ElemId, InnerCode;
>
> CREATE VIEW ElemView2 AS
> SELECT
> ElemId,
> ElemCode,
> ElemName,
> InnerCode,
> Attr1,
> Attr2,
> Attr3,
> Level,
> IsOrElem
> FROM ElemView1
> UNION ALL
> SELECT
> Element.ElemId || '.' || InnerElem.ElemId AS ElemId,
> InnerElem.ElemCode,
> InnerElem.ElemName,
> InnerElem.InnerCode,
> InnerElem.Attr1,
> InnerElem.Attr2,
> InnerElem.Attr3,
> InnerElem.Level+1,
> InnerElem.IsOrElem
> FROM ElemView1 AS Element
> JOIN ElemView1 AS InnerElem ON Element.Level=0 AND
> Element.InnerCode=InnerElem.ElemCode
> ORDER BY ElemId, InnerCode;
>
> - Note that ElemView2 uses the ElemView1 joining itself. When I
> query for data in ElemView1 (SELECT * FROM ElemView1) it returns
> with success; but, when I query ElemView2 (SELECT * FROM ElemView2)
> it fails, and an access violation occurs. Here is the stack in
> Visual Studio 2005 SP1:
> msvcr80d.dll!memcpy(unsigned char * dst=0x00ad3120, unsigned
> char * src=0x1b8c71e0, unsigned long count=36) Line 188
> sqlite3.dll!sqlite3VdbeMemShallowCopy(Mem * pTo=0x00ad3120,
> const Mem * pFrom=0x1b8c71e0, int srcType=256) Line 41982 + 0xf bytes
> sqlite3.dll!sqlite3VdbeExec(Vdbe * p=0x00ac4c40) Line 47446 +
> 0x3e bytes
> sqlite3.dll!sqlite3Step(Vdbe * p=0x00ac4c40) Line 45476 + 0x9
> bytes
> sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt=0x00ac4c40) Line
> 45542 + 0x9 bytes
> [application stack - ommited]
> Seems that the pointer pFrom from sqlite3VdbeMemShallowCopy is
> invalid.
>
> - The compiler options used are:
> SQLITE_THREADSAFE=1;
> TEMP_STORE=3;
> SQLITE_DEFAULT_CACHE_SIZE=65568;
> SQLITE_DEFAULT_TEMP_CACHE_SIZE=65568;
> SQLITE_MAX_ATTACHED=30;
> SQLITE_ENABLE_COLUMN_METADATA
>
> - I'm using the "amalgamation" version of the source code to
> compile/link the sqlite3.dll
>
> Please, let me know if you need more details.
>
> Thanks for the help,
> Merolli.
>
>
>
>
