query finds a maximum value which is NOT in the table

(1) By jvd (jeroenvandael) on 2021-01-17 09:37:33 [source]

Hello,

Can someone shed light on the problem below?

In c# I created a simple SQLite database with about 900.000 records in one table, each record having 19 fields.

I use 2 queries:

Q1 = SELECT MAX("fld0") FROM table where ("fld1" = 'val1' or "fld1" = 'val2' or "fld1" = 'val3');
Q2 = SELECT MAX("fld0") FROM table where (                   "fld1" = 'val2'                   );

What can be the reason, that Q2 gives an higher maximum value than Q1?

Even worse: that maximum value found by Q2 can not be found in the table.

It's all done on a windows 10 64 bit I7 laptop.

Any suggestion will be highly appriciated.

Kind regards,

jeroen van Dael

(2) By Keith Medcalf (kmedcalf) on 2021-01-17 10:43:51 in reply to 1 [link] [source]

Run PRAGMA INTEGRITY_CHECK and make sure you have not corrupted the database.

(4) By jvd (jeroenvandael) on 2021-01-18 09:31:26 in reply to 2 [link] [source]

PRAGMA INTEGRITY_CHECK returned 'OK' The database is quite 'fresh', was only queried, no changes made by query or by direct edit. Looks like the database is OK and not currupted.

(3.1) By Keith Medcalf (kmedcalf) on 2021-01-17 10:56:43 edited from 3.0 in reply to 1 [link] [source]

If the database is not corrupt, run the following queries and see if they shed light:

select quote(fld1) from table group by fld1;
select quote(fld1), typeof(fld0), count(*), max(fld0) from table group by fld1, typeof(fld0);

Perhaps fld0 and fld1 contain data of a different type than you think it does.

(5) By jvd (jeroenvandael) on 2021-01-18 10:18:57 in reply to 3.1 [link] [source]

Hello Keith:

Thank you for your time and suggestions. They were of great help.

The database is OK and the max values found with your queries are OK also.

Why I didn't find one of them earlier, I don't know, but I think the problem is solved now  and my trust in SQLite is restored.

Thank you so much again, regards, Jeroen van Dael


 
Conclusion: 

select quote(fld1) from table group by fld1; 
This query shows the three values which are to be expected for this field, no other rubbish.

Your 2nd query:
select quote(fld1), typeof(fld0), count(*), max(fld0) from table group by fld1, typeof(fld0);

Output:
quote(fld1): gives again the above mentioned three values which are to be expected for this field.
typeof(fld0): all are 'real'
count(*): for each (fld1) the same count, being 1/3 of the total amount of records in the table, which is OK
max(fld0): gives the 3 different maximum values for each of the three typeof(fld0) types. They are OK.