[GRASS-dev] [GRASS GIS] #3108: db selection: integer overflow

GRASS GIS GRASS GIS developer
1

#3108: db selection: integer overflow
--------------------------------------------+-------------------------
Reporter: mlennert | Owner: grass-dev@…
     Type: defect | Status: new
Priority: normal | Milestone: 7.0.5
Component: Database | Version: svn-trunk
Keywords: v.db.univar db.select overflow | CPU: Unspecified
Platform: Unspecified |
--------------------------------------------+-------------------------
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
}}}

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3108&gt;
GRASS GIS <https://grass.osgeo.org>

2

#3108: db selection: integer overflow
--------------------------+--------------------------------------------
  Reporter: mlennert | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone: 7.0.5
Component: Database | Version: svn-trunk
Resolution: | Keywords: v.db.univar db.select overflow
       CPU: Unspecified | Platform: Unspecified
--------------------------+--------------------------------------------

Comment (by 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...

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3108#comment:1&gt;
GRASS GIS <https://grass.osgeo.org>

3

#3108: db selection: integer overflow
--------------------------+--------------------------------------------
  Reporter: mlennert | Owner: grass-dev@…
      Type: defect | Status: closed
  Priority: normal | Milestone: 7.0.5
Component: Database | Version: svn-trunk
Resolution: invalid | Keywords: v.db.univar db.select overflow
       CPU: Unspecified | Platform: Unspecified
--------------------------+--------------------------------------------
Changes (by mlennert):

* status: new => closed
* resolution: => invalid

Comment:

Replying to [comment:1 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.

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3108#comment:2&gt;
GRASS GIS <https://grass.osgeo.org>

4

#3108: db selection: integer overflow
--------------------------+--------------------------------------------
  Reporter: mlennert | Owner: grass-dev@…
      Type: defect | Status: closed
  Priority: normal | Milestone: 7.0.5
Component: Database | Version: svn-trunk
Resolution: invalid | Keywords: v.db.univar db.select overflow
       CPU: Unspecified | Platform: Unspecified
--------------------------+--------------------------------------------

Comment (by 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...

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3108#comment:3&gt;
GRASS GIS <https://grass.osgeo.org>