SQLite Forum

Inconsistency in BETWEEN Query
Login

Inconsistency in BETWEEN Query

(1) By anonymous on 2021-10-21 12:38:55 [link] [source]

Hello I am working on an Android SQLite project and can not find a way to get right value, The program takes an input value from the user and searches it in the database, I want to do a query like; if the input value is 3; look through the col_a and col_b if the value is between col_a and col_b prints the col_c value "AA" and col_d value "BB". Like col_a < 3 <col_b prints AA, BB. If col_a < 7 < col_b prints CC, DD.

I am doing a query with the "BETWEEN" but it returns an inconsistent value.

My Table;
ID  |        col_a        |        col_b        |  col_c  |  col_d 
--------------------------------------------------------------------
1   |    4011110000000    |    4011119999999    |   AA    |   BB   
--------------------------------------------------------------------
2   |    4022220000000    |    4022229999999    |   CC    |   DD    
--------------------------------------------------------------------

My Query;
SELECT * FROM my_table WHERE 401111000001 BETWEEN col_a AND col_b

You can see the values in column col_a and col_b is 13 digits long and the query value is 12 digits long. The query returns the item with the ID:1. If I put a 13 digits long value(4011110000001) it also returns the row with ID:1. The problem is 12 digits long value is not in between among the values to be checked.

Android Java Code;
String sql = "SELECT * FROM my_table WHERE ? BETWEEN col_a  AND col_b";
Cursor cursor = db.rawQuery(sql, new String[] {String.valueOf(x)});

if (cursor.moveToNext()) {
    String element1 = cursor.getString(cursor.getColumnIndexOrThrow("col_c"));
    String element2 = cursor.getString(cursor.getColumnIndexOrThrow("col_d"));
    String element3 = cursor.getString(cursor.getColumnIndexOrThrow("ID"));
    cursor.close();
    Log.d(""," " +element1);
    Log.d(""," " +element2);
    Log.d(""," " +element3);
}

(2) By curmudgeon on 2021-10-21 13:40:53 in reply to 1 [link] [source]

What is the type of col_a and col_b. It looks as if the BETWEEN is treating the 3 values as TEXT.

(3) By anonymous on 2021-10-21 13:49:14 in reply to 2 [link] [source]

Yes col_a and col_b is TEXT.

(4) By Larry Brasfield (larrybr) on 2021-10-21 14:43:08 in reply to 3 [link] [source]

If you expect your text values to be compared as integers, you need to write something like CAST( my_text_resembling_a_number AS INT ) for each such value. The inequality operators (which set includes BETWEEN) will not do it for you.

(6) By anonymous on 2021-10-22 06:40:52 in reply to 4 [link] [source]

I can't use "int" because of the length of the numbers. If I cast the query as "long", can you guarantee that all query ranges will run without errors?

My Query will be; SELECT * FROM my_table WHERE 4011110000001 BETWEEN CAST(col_a AS LONG) AND CAST(col_b AS LONG)

(7) By Stephan Beal (stephan) on 2021-10-22 06:50:26 in reply to 6 [link] [source]

I can't use "int" because of the length of the numbers. If I cast the query as "long", can you guarantee that all query ranges will run without errors?

sqlite does not distinguish between "long" and "int". See:

https://www.sqlite.org/datatype3.html

(8) By anonymous on 2021-10-22 06:59:12 in reply to 7 [link] [source]

For SQLite the integer can be up to 8 bytes and will casting as "INTEGER" solve the problem without overflow?

My Query will be;

SELECT * FROM my_table WHERE 4011110000001 BETWEEN CAST(col_a AS INTEGER) AND CAST(col_b AS INTEGER)

(9) By Stephan Beal (stephan) on 2021-10-22 07:04:49 in reply to 8 [source]

For SQLite the integer can be up to 8 bytes and will casting as "INTEGER" solve the problem without overflow?

You can ask the foremost expert yourself: sqlite. If it behaves that way today you can be 99.99% certain it will behave that way in 10 years (the project places tremendous value on backwards compatibility).

sqlite> select cast(4011110000001 as integer);
4011110000001
sqlite> select cast(401111000000100 as integer);
401111000000100
sqlite> select cast(4011110000001000 as integer);
4011110000001000
sqlite> select cast(40111100000010000 as integer);
40111100000010000
sqlite> select cast(401111000000100000 as integer);
401111000000100000
sqlite> select cast(4011110000001000000 as integer);
4011110000001000000
sqlite> select cast(40111100000010000000 as integer);
9223372036854775807

(10.2) By ddevienne on 2021-10-22 07:18:41 edited from 10.1 in reply to 9 [link] [source]

And if your integers are even bigger, there's always the decimal extension.

You can't use indexes anymore, although you could still use a real column
that approximates your large integers for indexing, and apply the decimal_cmp
on the subset returned via the real index. You'd have to be careful of course,
since the real approximation could be above or below the integer, and that
complexity is probably necessary only for very large tables, as SQLite will be
fast enough even with full-scans in most cases most likely. FWIW. --DD

(5) By Gunter Hick (gunter_hick) on 2021-10-21 15:06:28 in reply to 1 [link] [source]

You need to read up on data types is SQLite and the concept of "affinity".

See https://sqlite.org/datatype3.html#sorting_grouping_and_compound_selects

In section 4.2. Type Conversions Prior To Comparison, the second bullet is "If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand."

So you are performing a string (lexical) comparison of (duplicate digits removed for clarity) "40100" <= "4011" <= "40199", which is obviously true; because "4011" comes after any string beginning with "4010" and before any string beginning with "4019".