SQLite: Inconsistency of SELECT Query Results

280 views Asked by At

I'm facing something really weird, unexpected behavior, about some inconsistencies among different SELECT Queries and I have no idea whether this can come from the SQLite library, corrupted data, etc.

I have a database file with the following table, named ResultTable10 and with the structure as follows:

  • INTEGER: Idx (PrimaryKey)
  • INTEGER: TimeStamp
  • INTEGER: Desynchronisation
  • INTEGER: Loop
  • INTEGER: Step
  • REAL : ConditionValue
  • INTEGER: LimitId
  • TEXT : 2KLINE1

If I am using the most violent and brutal SELECT query:

SELECT * FROM ResultTable10 

Then I am scrolling through the data using SQLite Browser (http://sqlitebrowser.org/)

And let's go to, for instance Loop = 124

Data at Loop = 124

And let's scroll a bit more to Loop = 125 Data at Loop = 125

According to the data provider the number of rows for a given Loop value is always the same (and the values of the column Step for a giver Loop number are always same either, and by the way, same order too).

However, I found out that apparently if I'm only sticking to the SQL SELECT Query Results, this actually not ~that~ true: Row Count Different

It seems that the minimum value of the field Step for the two different loops (respectively Loop 124 and 125) is not the same (respectively Step )

Furthermore the following queries targeting the Step = 508 are returning inconsistent results as well:

SELECT * FROM ResultTable10 WHERE Step = 508 AND Loop = 125;

Here I can clearly see what I got before when I was scrolling the whole set of data. SELECT * FROM ResultTable10 WHERE Step = 508 AND Loop = 125;

SELECT * FROM ResultTable10 WHERE Step = 508 AND Loop = 124;

But now the query on the Loop = 124 does not show any result about what I've seen previously. SELECT * FROM ResultTable10 WHERE Step = 508 AND Loop = 124;

SELECT * FROM ResultTable10 WHERE Step = 508 AND Loop BETWEEN 124 AND 125;

It seems fine if both Loops are passed in the WHERE clause SELECT * FROM ResultTable10 WHERE Step = 508 AND Loop BETWEEN 124 AND 125;

Does anybody else have already experienced this inconsistency, any potential fix, workaround (except rewriting all the data or performing massive query)?

Possibly where does this behavior come from? (Data Corruption, typos in my SQL statements, etc.)

EDIT: It seems that I'm having some corruption issues, I tried to dig a bit more with the System.Data.SQLite .NET library and I ended up with some queries on that : System.Data.SQLite .NET Library

Any general guidelines to prevent from those annoying corruptions? (I've already checked that one here: http://www.sqlite.org/howtocorrupt.html)

0

There are 0 answers