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

[GRASS GIS]

#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>
GRASS GIS <https://grass.osgeo.org>