SQLite Forum

Query returns incorrect result
Login

Query returns incorrect result

(1) By rittick (rittickgupta) on 2020-08-22 22:10:44 [link] [source]

I am using SQLLITE 3.18.2 . This is used with ORACLE's Berkeley DB 18.1 library

I have a table with text columns and the column ctl_ctor_lt_ident is used in an index to the table.

The following query returns an incorrect result -

select ctl_ctor_lt_ident from from control_table where ctl_ctor_lt_ident='95893A489680'

95893A489697

There are no rows with ctl_ctor_lt_ident=95893A489680

Is this a known bug ? Is a patch available

(2.1) By Larry Brasfield (LarryBrasfield) on 2020-08-23 13:00:01 edited from 2.0 in reply to 1 [link] [source]

I have followed this forum for enough years (preceding v3.18) to know that there is no such bug as "Finds non-existent table rows." That does not mean that such a thing could never happen. With the vast number of possible DB corruptions that might occur, many surprises could arise. With an eye toward that issue, I ask:

  1. Does this problem reproduce with a newly created and loaded DB? (The shell's .dump meta-command may help with this.)

  2. Have you tried rebuilding the index?

If either of those clears the problem, you may want to study How To Corrupt An SQLite Database File.

(3) By rittick (rittickgupta) on 2020-08-23 15:31:25 in reply to 2.1 [link] [source]

The index was corrupted. If I drop the index it does not return any rows. I get this problem frequently and did not encounter this issue with the earlier version- the application has not changed.

(4) By Richard Hipp (drh) on 2020-08-23 17:11:35 in reply to 3 [link] [source]

If I understand your initial post correctly, you are not using SQLite as delivered, but are instead using the Oracle BerkeleyDB storage engine that is coupled with Oracle's port of the SQLite parser and code generator. Oracle made significant changes to SQLite when they did that port. We are unable to support their work here. If my understanding is correct, then you should probably you should contact Oracle to get help with your problem.

(5) By rittick (rittickgupta) on 2020-08-23 20:02:48 in reply to 4 [link] [source]

Thank you . I will contact ORACLE,

(6) By Larry Brasfield (LarryBrasfield) on 2020-08-23 21:06:12 in reply to 3 [source]

You could run the integrity check pragma against your DB.

If your problem is due to a bug in Oracle's use or adaptation of SQLite, then you will undoubtedly have to take it up with Oracle (as Richard says.) However, the problem may be in your own code. If it is, Oracle is unlikely to be able to help.

To discover problems not in Oracle's domain, you should instrument heap usage to be sure that the heap relied upon by SQLite (whether modified by Oracle or not) is not corrupted during execution of your code. Running the integrity check pragma often can also narrow the range of the problem's origin. With some oft-run heap and DB integrity checks, you should be able to find a subset of your code and calls into the library which appears to be responsible for a failed check.

The problem you report is very fundamental. For that reason alone, primary suspicion should not fall upon code used by many others without seeing such problems. If you narrow the problem to a call into code other than yours, then you have a case ready for the "owner" of that code to debug. Otherwise, you would be asking somebody else to debug your code.