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
And let's scroll a bit more to 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:
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 = 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 BETWEEN 124 AND 125;
It seems fine if both Loops are passed in the WHERE clause
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 :
Any general guidelines to prevent from those annoying corruptions? (I've already checked that one here: http://www.sqlite.org/howtocorrupt.html)