db selection: integer overflow

[mlennert]

I don't have the time to look into it right now, but there seems to be some serious overflow happening in vector db selection:

v.info -c communes
[...]
INTEGER|REVENU_FIS
[...]

Directly in sqlite I get the following:

sqlite> select min(REVENU_FIS), max(REVENU_FIS), sum(REVENU_FIS) from communes;
0|7558043447|857584291001

However, using GRASS tools, I get:

db.select sql="select min(REVENU_FIS), max(REVENU_FIS), sum(REVENU_FIS) from communes"
min(REVENU_FIS)|max(REVENU_FIS)|sum(REVENU_FIS)
0|-1031891145|-1409168199

and

v.db.univar communes col=REVENU_FIS
Reading column values...
Number of values: 36610
Minimum: -2001804970
Maximum: 2098650536
Range: 4100455506
Mean: 20726578.6176728
Arithmetic mean of absolute values: 21930820.0978148
Variance: 7.76208158145393e+15
Standard deviation: 88102676.3580649
Coefficient of variation: 4.25071006571934
Sum: 758800043193

[sbl]

Not sure if this is related:

But also here GRASS tools give a buffer overflow whlie sqlite3 works:

This is my query example:

g.copy vector="geonames_NC,geonames_NC_cp"
v.db.addcolumn map=geonames_NC_cp columns="test integer"
cats=$(v.db.select -c geonames_NC_cp columns=cat where=cat< 25000)
echo "UPDATE geonames_NC_cp SET test = 1 WHERE cat in (${cats});" > db_test.sql

Then db.execute results in a core dump:

db.execute input=db_test.sql

while

sqlite3 /grassdata/nc_spm_08_grass/sbl/sqlite/sqlite.db < db_test.sql

works without issues...

[mlennert]

Replying to sbl:

Not sure if this is related:

But also here GRASS tools give a buffer overflow whlie sqlite3 works:

I was speaking about integer value overflow, not buffer overflow.

This is my query example:

g.copy vector="geonames_NC,geonames_NC_cp"
v.db.addcolumn map=geonames_NC_cp columns="test integer"
cats=$(v.db.select -c geonames_NC_cp columns=cat where=cat< 25000)
echo "UPDATE geonames_NC_cp SET test = 1 WHERE cat in (${cats});" > db_test.sql

Then db.execute results in a core dump:

db.execute input=db_test.sql

while

sqlite3 /grassdata/nc_spm_08_grass/sbl/sqlite/sqlite.db < db_test.sql

works without issues...

I cannot reproduce this with current trunk.

db.execute input=db_test.sqlDBMI-SQLite erreur de pilote :
Error in sqlite3_prepare():
near "1": syntax error

DBMI-SQLite erreur de pilote :
Error in sqlite3_prepare():
near "1": syntax error

ERREUR :Error while executing: 'UPDATE geonames_NC_cp SET test = 1 WHERE
        cat in (1'

and

sqlite3 /data/GRASS/DATA7/nc_spm_08/user1/sqlite/sqlite.db < db_test.sql 
Error: near line 1: near "2": syntax error

so similar behaviour.

Here's a reproducible example of my issue:

db.execute sql="create table testbug (value integer)"
db.execute sql="insert into testbug values (0), (10), (2147483646), (2147483647), (2147483648)"
db.select sql="select * from testbug"
value
0
10
2147483646
2147483647
-2147483648

but thinking about it, I realize that this is not a bug, as in the SQL standard "integer" is 4 bytes, so with a limit of 2147483647. The issue in my original post is rather that sqlite3 does not enforce type and so it does not really respect the SQL standard in that sense.

One has to define the value field as 'real' or 'double precision' to be able to go beyond that limit. It might be worth thinking about the introduction of a bigint type in GRASS. AFAIU, currently there are only the following types:

DB_C_TYPE_STRING DB_C_TYPE_INT DB_C_TYPE_DOUBLE DB_C_TYPE_DATETIME

But then again, as DB_C_TYPE_DOUBLE does work for really large numbers, I'm not sure that it is worth the hassle to introduce DB_C_TYPE_BIGINT (which would entail to also introduce DB_SQL_TYPE_BIGINT).

So, closing this bug as invalid.

@sbl: I don't know where your issue comes from, but if you can confirm it (as I can't), then please open a separate bug ticket as it seems to be something else.

[sbl]

My bad. Categoryies have to be comma separated. So it should be:

cats=$(v.db.select -c geonames_NC_cp columns=cat where=cat< 25000 | tr '\n' ',' | sed 's/,$g')

I have at the moment no other system for confirmation at hand, unfortunately...