SQLite Forum

2 queries asking the same, give different answers
Login

2 queries asking the same, give different answers

(1.1) By jvd (jeroenvandael) on 2021-01-17 08:49:18 edited from 1.0 [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.

In the table three groups of records can be distinguished based on three different values of one and the same field "fld1".

one group having a field "fld1" = 'val1'
second group having a field "fld1" = 'val2'
third group having a field "fld1" = 'val3'

In total these three groups make the above mentioned number of 900.00 records.

I use 3 queries:

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

Query Q1 queries the same 900.00 records as query Q2 and there outputs are identical.

What can be the reason, that Q3 gives an higher outcome than Q2?
Even worse: the higher outcome of Q3 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.1) By Gerry Snyder (GSnyder) on 2021-01-16 18:34:36 edited from 2.0 in reply to 1.0 [link] [source]

Try

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

and see if that gives any clue as to what is going on.

(3) By jvd (jeroenvandael) on 2021-01-17 09:04:21 in reply to 2.1 [link] [source]

I tried your suggestion, but the query gives no result at all. I don't know what that can mean.

Note that I updated my thread with a 3rd query. The query finds a maximum value, which is not in the table. Maybe I have to rename my thread in that way.

(4) By Larry Brasfield (LarryBrasfield) on 2021-01-17 14:41:05 in reply to 3 [link] [source]

I tried your suggestion, but the query gives no result at all. I don't know what that can mean.

For your query [a], it means that the 2nd (EXCEPT ...) subquery returns the same set as, or a superset of, the 1st subquery.

[a. SELECT MAX("fld0"), fld0 FROM table EXCEPT SELECT MAX("fld0"), fld0 FROM table where ("fld1" = 'val1' or "fld1" = 'val2' or "fld1" = 'val3'); ]

Sometimes, a good way to debug a complex query is to break it down into its component subqueries, run those, and see what their individual result sets are.

(5) By Eric M (eminbiole) on 2021-01-18 19:54:12 in reply to 1.1 [link] [source]

I can't see any obvious reason why Q3 should ever return a value larger than Q2.

As such, I wonder if the issue is caused by database corruption. In particular, if Q3 uses an index for the search, but Q1/Q2 don't (or vice versa), then a corrupted index could be the culprit.

As a test, I'd suggest running the following command:

PRAGMA integrity_check;

If it returns a single row with the value "ok", then no corruption was detected. However, if it returns anything else, then it indicates that there is database corruption, and might explain the issue.

(6) By David Raymond (dvdraymond) on 2021-01-19 16:33:04 in reply to 1.1 [link] [source]

What is the query plan for each of the three queries? Do they all use the same index?

Alternatively, by chance, is there a mix of numeric and textual data in fld0? And what is fld0's declared type? Since text is "greater than" numeric, do you have the case where the max for the whole table is a string, whereas the max for only a limited subset is a number?

(7) By Gerry Snyder (GSnyder) on 2021-01-19 18:37:46 in reply to 1.1 [link] [source]

If you are still struggling, try

SELECT MAX(fld0), typeof(fld0), fld1, typeof(fld1)

in each of your queries

It seems that either the database is corrupt, or not all data are the same type.