Found a little time to convert my nets table back to having individual bytes.
Results like this:
Select id, ip1, ip2 FROM iptest WHERE '719323156' BETWEEN ip1 AND ip2
Query time: 1 millisecond(s), Number of cursor's records: 1
Select id, ip1, ip2 FROM iptest WHERE 719323156 BETWEEN ip1 AND ip2
Query time: 0 millisecond(s), Number of cursor's records: 1
Select id, ip1, ip2 FROM iptest WHERE '719323156' BETWEEN ((a1*256+b1)*256+c1)*256+d1 AND ((a2*256+b2)*256+c2)*256+d2
Query time: 1 millisecond(s), Number of cursor's records: 0
Select id, ip1, ip2 FROM iptest WHERE 719323156 BETWEEN ((a1*256+b1)*256+c1)*256+d1 AND ((a2*256+b2)*256+c2)*256+d2
Query time: 1 millisecond(s), Number of cursor's records: 1
Select id, ip1, ip2 FROM iptest WHERE '719323156' BETWEEN (((a1*256+b1)*256+c1)*256+d1) AND (((a2*256+b2)*256+c2)*256+d2)
Query time: 1 millisecond(s), Number of cursor's records: 0
Select id, ip1, ip2 FROM iptest WHERE 719323156 BETWEEN (((a1*256+b1)*256+c1)*256+d1) AND (((a2*256+b2)*256+c2)*256+d2)
Query time: 0 millisecond(s), Number of cursor's records: 1
Select id, ip1, ip2 FROM iptest WHERE '719323156'>=(((a1*256+b1)*256+c1)*256+d1) AND '719323156'<=((a2*256+b2)*256+c2)*256+d2)
Query time: 0 millisecond(s), Number of cursor's records: 0
Select id, ip1, ip2 FROM iptest WHERE '719323156'>=ip1 AND '719323156'<=ip2
Query time: 1 millisecond(s), Number of cursor's records: 1
All queries that use integer as the outside DB data, gives the right/expected results.
The extra () around operands in .. BETWEEN .. AND .. makes no difference
Outside DB data can be text in .. BETWEEN .. AND .. if SQLite3 does not calculate actual 32bit integer from ipbytes.
Outside DB data as text in .. BETWEEN .. AND .. where SQLite3 converts byte to 32bit (or 64bit) integer does NOT give expected results.
But dissolving the .. BETWEEN .. AND .. to the actual two comparisons, do give the expected results, even with a text as the outside DB data
There may be a converting affinity problem in BETWEEN AND statement - or maybe a problem figuring out what affinity (datatype) to use.
It would be logical to use integer, as there are two calculations using integer from database tables, and there should be no problem converting the string representation of the number in the comparisons.
But even if TEXT is chosen ad the affinity, the comparisons should still give the right result.
To me it looks like SQLite3 has a problem figuring out the affinity of numbers it calculates from integers in its tables.
Example is chosen with numbers, that nave no problem with being presented in 32bit (not negative)
42.224.0.0 - 42.239.255.255 <=> 719323136 - 720371711
Have tested with
196.52.43.0 - 196.52.43.255 <=> 3291753216 - 3291753471 (negative as 32bit signed integer) to find ipNr 3291753216
with the same results
If anybody want to fiddle, here a little to work with...
DB:CREATE TABLE "iptest"(
"id" Integer NOT NULL PRIMARY KEY,
"a1" Integer NOT NULL,
"b1" Integer NOT NULL,
"c1" Integer NOT NULL,
"d1" Integer NOT NULL,
"a2" Integer NOT NULL,
"b2" Integer NOT NULL,
"c2" Integer NOT NULL,
"d2" Integer NOT NULL,
"ip1" Integer NOT NULL,
"ip2" Integer NOT NULL,
CONSTRAINT "unique_id" UNIQUE ( "id" ) )
INSERT INTO "iptest"(
"id",
"a1",
"b1",
"c1",
"d1",
"a2",
"b2",
"c2",
"d2",
"ip1",
"ip2"
) VALUES (
1,
196,
52,
43,
0,
196,
52,
43,
255,
3291753216,
3291753471
),
(
2,
42,
224,
0,
0,
42,
239,
255,
255,
719323136,
720371711
);