BUG REPORT

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG REPORT

Mr Max
To whom it may concern,

 

 

Whilst using an ODBC driver for SQLite acquired from:

 

http://www.ch-werner.de/sqliteodbc/

 

I came across a potential bug in SQLite.

 

I have an application running VB.NET on a Windows 7 32-bit machine and have
installed the sqliteodbc.exe from the website above. I am using a query to
check a date field between a range of dates, the exact query being:

 

SELECT ind.CUSTOMERU, ind.XTRANU, ind.DDATE, SUM(ind.DAMOUNT) as REVENUE,
MIN(inc.SURNAME) as CNAME

FROM INV_DETAIL ind inner JOIN CUSTOMER inc ON ind.CUSTOMERU=inc.UNIQ  WHERE
ind.DDATE BETWEEN '2018-02-01' AND '2018-02-28' AND ind.DTYPE='3'

AND ind.DAMOUNT<0 AND ind.SUBCONTRU<>'666' AND ind.SUBCONTRU<>'555' GROUP BY
ind.CUSTOMERU, ind.XTRANU ORDER BY ind.CUSTOMERU, ind.XTRANU;

 

 

When running this with the  ODBC driver it fails to return all the
appropriate record in the range. I tried the exact same query in a DB
Browser for Sqlite and it recovers 127 records only.

I have run the same query using ODBC and Access (office 2003) and it
recovers 138 records. Doing a manual filter of the records from the
INV_DETAIL table I can extract 138 records!!

The records  apparently omitted by SQLite are one with DDATE equal to the
start date of 2018-02-01. If I make the start date one day earlier the
SQLite query returns 138 records!!

 

I have attached a spreadsheet with the data from the report I am generating
and with the INV_DETAIL data for the whole month of Feb 2018.

 

 

Regards

Bob Maxwell

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BUG REPORT

Richard Hipp-3
On 8/15/18, Mr Max <[hidden email]> wrote:
> When running this with the  ODBC driver it fails to return all the
> appropriate record in the range. I tried the exact same query in a DB
> Browser for Sqlite and it recovers 127 records only.

What answer do you get when you run your query using the
officially-supported "sqlite3.exe" command-line tool available from
(https://www.sqlite.org/download.html)?

The "DB Browser for SQLite" is a third-party tool, about which I know
very little.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BUG REPORT

Chris Locke-3
In reply to this post by Mr Max
> I am using a query to check a date field between a range of dates

Can you provide example values of the date in your database?
Are you storing the EXACT date (eg, '2018-02-01 12:21'), or just the date?

> When running this with the  ODBC driver it fails to return all the
appropriate record in the range. I tried the exact same query in a
> DB Browser for Sqlite and it recovers 127 records only.

How many records were returned with the ODBC driver?



On Wed, Aug 15, 2018 at 10:16 AM Mr Max <[hidden email]> wrote:

> To whom it may concern,
>
>
>
>
>
> Whilst using an ODBC driver for SQLite acquired from:
>
>
>
> http://www.ch-werner.de/sqliteodbc/
>
>
>
> I came across a potential bug in SQLite.
>
>
>
> I have an application running VB.NET on a Windows 7 32-bit machine and
> have
> installed the sqliteodbc.exe from the website above. I am using a query to
> check a date field between a range of dates, the exact query being:
>
>
>
> SELECT ind.CUSTOMERU, ind.XTRANU, ind.DDATE, SUM(ind.DAMOUNT) as REVENUE,
> MIN(inc.SURNAME) as CNAME
>
> FROM INV_DETAIL ind inner JOIN CUSTOMER inc ON ind.CUSTOMERU=inc.UNIQ
> WHERE
> ind.DDATE BETWEEN '2018-02-01' AND '2018-02-28' AND ind.DTYPE='3'
>
> AND ind.DAMOUNT<0 AND ind.SUBCONTRU<>'666' AND ind.SUBCONTRU<>'555' GROUP
> BY
> ind.CUSTOMERU, ind.XTRANU ORDER BY ind.CUSTOMERU, ind.XTRANU;
>
>
>
>
>
> When running this with the  ODBC driver it fails to return all the
> appropriate record in the range. I tried the exact same query in a DB
> Browser for Sqlite and it recovers 127 records only.
>
> I have run the same query using ODBC and Access (office 2003) and it
> recovers 138 records. Doing a manual filter of the records from the
> INV_DETAIL table I can extract 138 records!!
>
> The records  apparently omitted by SQLite are one with DDATE equal to the
> start date of 2018-02-01. If I make the start date one day earlier the
> SQLite query returns 138 records!!
>
>
>
> I have attached a spreadsheet with the data from the report I am generating
> and with the INV_DETAIL data for the whole month of Feb 2018.
>
>
>
>
>
> Regards
>
> Bob Maxwell
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users