Recent

Author Topic: SQLite3 - comparing integers  (Read 2910 times)

Birger52

  • Sr. Member
  • ****
  • Posts: 309
SQLite3 - comparing integers
« on: November 26, 2020, 01:16:24 pm »
I'm playing around a little with IP-adresses.
Defining them as cardinals (32bit positive integer - Qword?) and calculating them from the visual representation a.b.c.d as
Code: Pascal  [Select][+][-]
  1. ipNr := ((a*256+b)*256+c)*256+d;
I have a SQLite3 database, where i register nets (ranges), and with it the ISP - and a possibility to register the net as used for bots or ban the whole net from accessing my sites.
This registration uses the four numbers - a,b,c,d from the visual rep. of the IP addresses.

So I need to find which net - if any - a given IP belongs to

In SQL I have this comparison
Code: Pascal  [Select][+][-]
  1.   qry := 'SELECT id, isp_id, ban, bot FROM nets WHERE '''+IntToStr(ipNr)+''' BETWEEN ((a1*256+b1)*256+c1)*256+d1 AND ((a2*256+b2)*256+c2)*256+d2';
  2.  
But it is not doing the job.
The ipNr looks to be correct.
Could be IntToStr() - it's not defined for cardinal, but is for Qword, so I figure it should be OK (expecting compiler to complain if not).
There is no way to know what SQLite makes of the numbers in the BETWEEN statement. According to documentation, it should revert to 64bit integer whenever possible or "in doubt" - and that would be OK.
But it doesn't actually find any matches, even when it should.

Any idea's how to solve this?
Or to figure out why it does not give the expected results?
Lazarus 2.0.8 FPC 3.0.4
Win7 64bit
Playing and learning - strictly for my own pleasure.

Birger52

  • Sr. Member
  • ****
  • Posts: 309
Re: SQLite3 - comparing integers
« Reply #1 on: November 26, 2020, 04:21:06 pm »
Turned the bucket upside down.
Changed db to register cardinals rather than individual bytes for IP adresses.
Then the select changes to
Code: Pascal  [Select][+][-]
  1.   qry := 'SELECT id, isp_id, ban, bot FROM nets WHERE '''+IntToStr(aReq.ipNr)+''' BETWEEN ip1 AND ip2';
  2.  
Of course need to calculate the ipNr before storing, and to convert to string when it needs to be shown
Code: Pascal  [Select][+][-]
  1.   function IP2String(ipNr:cardinal) : string;
  2.   var
  3.     nr : byte;
  4.     exp : shortint = 3;
  5.     nom : integer;
  6.   begin
  7.     result := '';
  8.     while exp > -1 do begin
  9.       nom := Floor(IntPower(256, exp));
  10.       nr := Floor(ipNr/nom);
  11.       if exp < 3 then result += '.';
  12.       result += IntToStr(nr);
  13.       ipNr := ipNr - nr*nom;
  14.       exp -= 1;
  15.       end;
  16.   end;
  17.  

So my problem solved.
It doesn't explain, why the comparison with the calculation in the SELECT query doesn't function, tho.
So if anybody has explanations ... ;)
Lazarus 2.0.8 FPC 3.0.4
Win7 64bit
Playing and learning - strictly for my own pleasure.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 - comparing integers
« Reply #2 on: November 26, 2020, 04:42:11 pm »
Try the following
Code: SQL  [Select][+][-]
  1. SELECT a1, b1, ((a1*256+b1)*256+c1)*256+d1 AND ((a2*256+b2)*256+c2)*256+d2 FROM nets

Notice that the 3rd field with AND is taken as one?
Maybe that's also going on in the WHERE.

Try putting an extra ( and ) around the first and last part.
Code: SQL  [Select][+][-]
  1. BETWEEN (((a1*256+b1)*256+c1)*256+d1) AND (((a2*256+b2)*256+c2)*256+d2)
(not sure if that helps)

But I'm not even sure BETWEEN can take expressions. According to the doc it takes a value_from and value_to. That's not an expression.

You could try
Code: SQL  [Select][+][-]
  1. WHERE ' + IntToStr(aReq.ipNr) + '>=(((a1*256+b1)*256+c1)*256+d1) AND ' + IntToStr(aReq.ipNr) + '<=(((a2*256+b2)*256+c2)*256+d2)

Edit: According to this chart BETWEEN can take expressions.
https://sqlite.org/lang_expr.html
So try the extra ( and ) around it so the AND stands apart.
« Last Edit: November 26, 2020, 04:44:56 pm by rvk »

Birger52

  • Sr. Member
  • ****
  • Posts: 309
Re: SQLite3 - comparing integers
« Reply #3 on: November 27, 2020, 04:46:33 pm »
According to
https://www.sqlitetutorial.net/sqlite-between/
BETWEEN do take expressions:
"test_expression BETWEEN low_expression AND high_expression"
I can't find any definition @ sqlite.org (not any definition of any operators actually...)
But I can find that "optimization" replaces BETWEEN with two comparisons so
x BETWEEN z AND z is evaluated as x >= y AND x <=z (which is what you suggests to try), with the exception, that x is only evaluated once.
Also - if the extra () was needed, it should raise an error, as the expressions does not make sense, if the were needed and omitted.

According to https://www.sqlite.org/datatype3.html#operators operands are treated like numbers (whatever that is) - from the rest of the text, I would expect 64bit integers wen possible - which results implies, they are not.
(affinity for a,b,c and d was integer, and they are input as IntToStr(a) - where a is 0 to 255 - so there is no reason for SQLite3 to think they are anything but integers. Even if treated as TEXT, they should - according to the above link - each be converted to 64 bit integers before doing the calculations)

I have changed to use actually 32bit positive integers, so SQLite3 does not do the calculations only the comparisons, and it is functioning.
Lazarus 2.0.8 FPC 3.0.4
Win7 64bit
Playing and learning - strictly for my own pleasure.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 - comparing integers
« Reply #4 on: November 27, 2020, 09:01:07 pm »
You asked for a possible explanation so I tried to find one.

In this fiddle it actually works for SQLite.
http://sqlfiddle.com/#!5/c208b/5/0

Btw. Using '3232258055' instead of 3232258055 (without quotes) doesn't give a result.

So I guess quoting you ipnr as text isn't the right way here.
Try it without and it should work.

Maybe because your first argument is TEXT the comparison is done as text and not as INT.
« Last Edit: November 27, 2020, 11:31:55 pm by rvk »

Birger52

  • Sr. Member
  • ****
  • Posts: 309
Re: SQLite3 - comparing integers
« Reply #5 on: November 28, 2020, 08:35:50 pm »
Thank you for your effort ;)
And for the link to sqlfiddle - didn't know about that one.

Looks like you are right - the problem is that the operands are not the same type (finding a text value between to integers.
I interpret https://www.sqlite.org/datatype3.html#compaff as the text should be converted to integer, as one of the operands is an integer. (tried to change the BETWEEN to to separate comparisons http://sqlfiddle.com/#!5/c208b/12 - but that does not function either).
Even if I am wrong and the result of the calculation is converted to a string, the comparison should actually still be correct.
But it isn't.

Lazarus 2.0.8 FPC 3.0.4
Win7 64bit
Playing and learning - strictly for my own pleasure.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 - comparing integers
« Reply #6 on: November 28, 2020, 08:56:57 pm »
Even if I am wrong and the result of the calculation is converted to a string, the comparison should actually still be correct.
Could be that the expression after BETWEEN is converted to text without calculation because the value before BETWEEN is a TEXT field.

With this fiddle id*0 is 0 and id*2 is 4 so '3' BETWEEN id*0 AND id*2 should give the records, but they don't.
http://sqlfiddle.com/#!5/b5362/5693

So my guess is that mixing TEXT and INTEGER in the BETWEEN clause can't be done.

But that's why you asked if you could see what exactly SQLite is comparing with each other  :D
(But I'm not aware that there is such option)

Birger52

  • Sr. Member
  • ****
  • Posts: 309
Re: SQLite3 - comparing integers
« Reply #7 on: November 29, 2020, 02:38:03 pm »
I think there may be a problem with the fiddle site.
I have not tried the last example - but the this one: http://sqlfiddle.com/#!5/9f743/2 is actually the one I use in my app
Code: Pascal  [Select][+][-]
  1. qry := 'SELECT id, ip1, ip2 FROM nets WHERE '''+IntToStr(ipNr)+''' BETWEEN ip1 AND ip2';
  2.  
and it is doing as it should here. (ipNr is cardinal defined in Lazarus/FreePascal, ip1 and ip2 integer values in SQLite3).
So sqlfiddle does not give the same resullts, as I get here.
(sqlfiddle also returns (null) for id - which I believe can never be true for a auto_increment primary key)

Referring to the code above, when SQLite3 was calculating the ip1 and ip2 values from the four bytes - like your fiddle http://sqlfiddle.com/#!5/c208b/5/0 - it did not give the right results here - it does when they are calculated outside SQLite3

Note that in my app, all values that are not from the database are single quoted - so kind of always strings.

Quote
Could be that the expression after BETWEEN is converted to text without calculation because the value before BETWEEN is a TEXT field.
What I get from https://www.sqlite.org/datatype3.html#compaff, is that SQLite3 will attempt to use the affinity for the values fetched from the database - which would be the exact opposite - always integers.
Also Optimization says that  x BETWEEN y AND z is evaluated as x>=y AND x<=z - so there should be a lot of other instances, that does not give expected results.

Quote
So my guess is that mixing TEXT and INTEGER in the BETWEEN clause can't be done.
Looks like that is the case with sqlfiddle - but it is functioning just fine in apps.
I'm using a free version of Valentina Studio - https://valentina-db.com/en/valentina-studio-overview which I an wholeheartedly recommend - and it converts as it should and gives correct expected results.
(I didn't test the original problem in Valentina - think I'll try to recreate, when I get some time...)
Lazarus 2.0.8 FPC 3.0.4
Win7 64bit
Playing and learning - strictly for my own pleasure.

Birger52

  • Sr. Member
  • ****
  • Posts: 309
Re: SQLite3 - comparing integers
« Reply #8 on: November 29, 2020, 10:20:46 pm »
Found a little time to convert my nets table back to having individual bytes.
Results like this:


Quote
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
 );

Lazarus 2.0.8 FPC 3.0.4
Win7 64bit
Playing and learning - strictly for my own pleasure.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 - comparing integers
« Reply #9 on: November 29, 2020, 10:28:39 pm »
I couldn't find with what version of sqlite sqlfiddle was working.

I found another.
https://www.db-fiddle.com/f/xjJGqmFVMeM14YdtrfjWsr/3
This one really says version 3.30 at the top.

Btw. The auto_increment was a leftover from an example of another database. For sqlite it should be autoincrement but they suggest you don't use that.

Here is the example with autoincrement.
https://www.db-fiddle.com/f/xjJGqmFVMeM14YdtrfjWsr/4

A haven't got time now to test with the real deal but it's really strange you have different results from the online ones.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 - comparing integers
« Reply #10 on: November 30, 2020, 12:45:59 pm »
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

This is the fiddle https://www.db-fiddle.com/f/mWjoWeHyizY2VNnfrdb5Mf/0

All results are the same there (although you had an extra ")" in the second to last select).

As I see it, it goes wrong when you use TEXT before BETWEEN and a calculation/expression as BETWEEN values.
I think SQLite can't determine that it needs to use TEXT affinity after the calculation.
I think it does it before calculation and it will check with * sign etc. (which of course goes wrong).

A very simple example of this is here
https://www.db-fiddle.com/f/dVkJX58zWa54bAr1Za4T1t/0

This doesn't work
SELECT * FROM test WHERE '2' = id*2
( this will check '2' against 'id*2' )

This works
SELECT * FROM test WHERE 2 = id*2
( this will check 2 against id*2 )

Birger52

  • Sr. Member
  • ****
  • Posts: 309
Re: SQLite3 - comparing integers
« Reply #11 on: November 30, 2020, 01:33:53 pm »
In SQLit3 autoincrement do exist, but it is recommended to not use it - using INTEGER PRIMARY KEY has the same effect, except it should reuse free numbers (deleted rows) as well as the next unused number.
(https://www.sqlite.org/autoinc.html)
AUTOINCREMENT uses more CPU, RAM and disk-space.


It is not only in the BETWEEN things goes wrong.
It is actually anywhere there is a comparison of a string and an expression, where the expression should yield an integer value.
Looks like the affinity is determined before the calculation.


Found a solution. Results of calculations should be CAST(expr AS ..) when you know what you expect

Quote
Select id, ip1, ip2 FROM iptest WHERE '3291753226' BETWEEN CAST(((a1*256+b1)*256+c1)*256+d1 AS NUMBER) AND CAST(((a2*256+b2)*256+c2)*256+d2 AS NUMBER)

Select id, ip1, ip2 FROM iptest WHERE '3291753226'>=CAST(((a1*256+b1)*256+c1)*256+d1 AS NUMBER) AND '3291753226'<=CAST(((a2*256+b2)*256+c2)*256+d2 AS NUMBER)
Both of which gives the expected results (tested in Valentina)
Lazarus 2.0.8 FPC 3.0.4
Win7 64bit
Playing and learning - strictly for my own pleasure.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 - comparing integers
« Reply #12 on: November 30, 2020, 01:47:43 pm »
It is not only in the BETWEEN things goes wrong.
It is actually anywhere there is a comparison of a string and an expression, where the expression should yield an integer value.
Looks like the affinity is determined before the calculation.
Yes. I mentioned that in my last post. If also goes wrong with =.
And you could check it with the simple Fiddle I gave https://www.db-fiddle.com/f/dVkJX58zWa54bAr1Za4T1t/0

Found a solution. Results of calculations should be CAST(expr AS ..) when you know what you expect

Quote
Select id, ip1, ip2 FROM iptest WHERE '3291753226' BETWEEN CAST(((a1*256+b1)*256+c1)*256+d1 AS NUMBER) AND CAST(((a2*256+b2)*256+c2)*256+d2 AS NUMBER)

Select id, ip1, ip2 FROM iptest WHERE '3291753226'>=CAST(((a1*256+b1)*256+c1)*256+d1 AS NUMBER) AND '3291753226'<=CAST(((a2*256+b2)*256+c2)*256+d2 AS NUMBER)
Both of which gives the expected results (tested in Valentina)
Yes. That kinda proves SQLite couldn't really determine the affinity.

BTW. I would still prefer to pass the ipnr as NUMBER instead of TEXT.
In that case there is no need for the CAST.
So using WHERE 3291753226 just works in all cases.

Because you are still not sure if with your last solution, the comparison is done by TEXT or NUMBER.
And '328' is still in between '32723533' and '329247384628438672'.

Check the fiddle https://www.db-fiddle.com/f/fvtZbjKANHq9pUF1QPZjKt/0
SELECT * FROM test WHERE '328' BETWEEN '32723533' AND '329247384628438672'
still gives all the results

I do think it will still convert to NUMBER if one is a NUMBER (so you would be safe) but I would still be more clear in that case and just use ip as NUMBER.

Birger52

  • Sr. Member
  • ****
  • Posts: 309
Re: SQLite3 - comparing integers
« Reply #13 on: November 30, 2020, 04:57:38 pm »
My "solution" is to insert the 32bit unsigned numbers (cardinal or QWord) in the database. This removes the need for SQL to calculate anything, and the comparison with strings works as it should.
Downside is that it needs to be converted to the 4xbyte when needed visually.

I have always used quotes around any kind of data, that is not read from the database in queries.
That is the reason for the '''+IntToStr(ipNr)+''' (set a single quote around the number) - and of course is part of the problem. I have always done so, because I thought it was a requirement from the database.
But it does work just fine without the quote.

But it isn't. Not in SQLite3, anyway.
;)
So I learn something today.
« Last Edit: November 30, 2020, 06:21:50 pm by Birger52 »
Lazarus 2.0.8 FPC 3.0.4
Win7 64bit
Playing and learning - strictly for my own pleasure.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 - comparing integers
« Reply #14 on: November 30, 2020, 05:03:40 pm »
Funny. I never use quotes around numbers  :D
Maybe that's because other databases are not that loose with affinity as SQLite.
Sometimes that's an upside. You always (need to) know if you're dealing with numbers or text.

That makes using SQLite sometimes really weird. Especially when dealing with dates.
You really need to know the ins and outs of SQLite regarding conversions to its own internal storage.

 ;)

 

TinyPortal © 2005-2018