System.Data.SQLite

Hello Everyone. I've been using System.Data.SQLite for a while now (3+ years). I've used older versions of the lib but two months ago I got the latest release (v. 1.0.66). It's been working fine for simple queries (plain selects of selects with one subselect) as it always did. But right now I've just found a bug. It keeps on repeating itself and consistently giving back the same wrong data. I run a somewhat complex select on the database and part of the data is trimmed during the select. I can't tell why. Let me show you it more clearly.

The table description is:

CREATE TABLE "Errors" (   
"ID" INTEGER PRIMARY KEY NOT NULL,	    
"Category" TEXT,  
"Date" TEXT,		    
"Time" TEXT,			    
"Geo" TEXT,			    
"ErrorCountry" TEXT,			    
"ErrorCode" TEXT,		    
"AssociatedUser" TEXT,		    
"AssociatedQuote" TEXT,	    
"SummaryMessage" TEXT,	    
"ErrorMessage" TEXT
)

The query I'm running against it is: (I know it's dirty, but does the work!):

select DayMax, MonthMax, YearMax from (select Max(cast(substr(Date, 0, 2) as integer)) as DayMax, MonthMax, YearMax from errors c, (select Max(cast(substr(b.Date, 4,2) as integer)) as MonthMax, Max(cast(substr(b.Date, 7, 4) as integer)) as YearMax from  errors b,  (select Max(cast(substr(Date, 7, 4) as integer)) as YearMax from errors) a  where cast(substr(b.Date, 7, 4) as integer) = a.YearMax) where cast(substr(c.Date, 4, 2) as integer) = MonthMax and cast(substr(c.Date, 7, 4) as integer) = YearMax)

The bogus case happens when I get the sample date: 21/6/2010

SQLiteman returns this result:

21 6 2010

What happens is that during execution DayMax returns "2" instead of "21". This makes my application work incorrectly (since it's a totally different date). Any clue why this is happening? I'll try downloading a previous version to check if it's a version issue. I know the select is working right because I tried it on the database using SQLiteman and it returns the data as expected.

 Hello everyone.

It's still an open issue but I managed to get it working by using v1.0.60.  I can confirm version 1.0.65 did not work also.

Indices to substr() are 1-based, so your invocation substr(Date, 0, 2) is bogus. It should be substr(Date, 1, 2). A quick test with the sqlite command-line program confirms this:

sqlite> select substr('21/6/2010', 1, 2);
21
sqlite> select substr('21/6/2010', 0, 2);
2