[Bug] Integer overflow occurred when trying to set 32bit field.

latot commented 1 month ago

What is the bug?

Sorry for the weird name, is the warning message, but what it means, is other story, I was trying to upload a sqlite file to PG, then I start getting this messages:

Warning 1: Integer overflow occurred when trying to set 32bit field.

But after further research I found is not caused due to an overflow of a value, in fact I really does not have any idea of why this happens, the sample is a subset of a bigger db, here is what I found.

A table with a row that has the max value will not trigger any warning
This only happens with a specific context of rows, for example
- Choose 60k of rows (like the sample) triggers it 28 times the warning
- For 56k of rows triggers 3 times
- For but for 55.9K of rows there will be no warning
if we duplicate a column, like selecting it two times from the sql param, we will duplicate the warnings
Any change to test this, must be done from SQLite, or the warning will stop showing

At least for now, I tested if the generated sqlite file is the same with :

sqlite3 a.sqlite .dump | sort | > a.dump
sqlite3 error.sqlite .dump | sort | > error.dump
diff error.dump a.dump

And seems no difference in this case, the warning seems off or maybe can affect in other circumstances.

Thx!

Steps to reproduce the issue

ogr2ogr a.sqlite error.sqlite

error.zip

Versions and provenance

GDAL 3.9.1, released 2024/06/22

Additional context

No response

rouault commented 1 month ago

I don't see this as a GDAL bug. The issue is that your SQLite table uses a "INT" field declaration, which is mapped by the OGR SQLite driver as a 32-bit integer field. It should use BIGINT instead.

Demo:

$ cat int64.csv
1,1234567890213
$ cat int64.csvt
Integer,Integer64
$ ogr2ogr int64.db int64.csv -nlt point
$ ogrinfo int64.db -al -q
Layer name: int64
OGRFeature(int64):1
  field_1 (Integer) = 1
  field_2 (Integer64) = 1234567890213
$ ogrinfo int64.db -sql "select sql from sqlite_master where name = 'int64'" -q

Layer name: SELECT
OGRFeature(SELECT):0
  sql (String) = CREATE TABLE 'int64' ( "ogc_fid" INTEGER PRIMARY KEY AUTOINCREMENT, 'GEOMETRY' BLOB, 'field_1' INTEGER, 'field_2' BIGINT)

latot commented 1 month ago

Hi!

grayshade had a similar idea in matrix, but after some tests seems to not be the case:

ogr2ogr b.sqlite error.sqlite -sql "select cast(avaluo_total as bigint) x from sample"
Warning 1: Integer overflow occurred when trying to set 32bit field.

Integer64 is like the closer one to bigint.

ogr2ogr b.sqlite error.sqlite -mapFieldType "INTEGER=INTEGER64"
Warning 1: Integer overflow occurred when trying to set 32bit field.

ogr2ogr b.sqlite error.sqlite -mapFieldType "ALL=INTEGER64" 
Warning 1: Integer overflow occurred when trying to set 32bit field.

ogr2ogr b.sqlite error.sqlite -mapFieldType "ALL=INTEGER64" -sql "select cast(avaluo_total as bigint) x from sample"
Warning 1: Integer overflow occurred when trying to set 32bit field.

rouault commented 1 month ago

grayshade had a similar idea in matrix, but after some tests seems to not be the case:

no that likely won't work, because of the weak typing system of SQLite. Your best chance is to dump, change the CREATE TABLE statement in the dump and re-import. Overriding driver type guess is a larger (and recurring) topic. I've just filed https://github.com/OSGeo/gdal/issues/10943

rouault commented 1 month ago

GeoPackage has a more standardized typing system than pure SQLite. Cf "Table 1. GeoPackage Data Types" of https://www.geopackage.org/spec/

latot commented 1 month ago

I still do not get very well something of this, why the warning does not happens for example, when you pick half of the data? (error.sqlite)

I also put above several cases where we do not get this warning, in a very inconsistent way.

latot commented 1 month ago

This includes very weird things, like if you create a sqlite file with only one row, that is the max value of the sample file, there will be no warning at all. There is a lot of this weird things https://github.com/OSGeo/gdal/issues/10938#issue-2567250350

rouault commented 1 month ago

I still do not get very well something of this, why the warning does not happens for example, when you pick half of the data? (error.sqlite)

it does:

$ ogrinfo -al error.sqlite >/dev/null
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.
Warning 1: Integer overflow occurred when trying to set 32bit field.

There is a lot of this weird things #10938 (comment)

Too complicated to digest for my limited aging brain. Please simplify to minimum & ready-made reproducers

latot commented 1 month ago

Okis, next time more examples! monday I can do this :3 Have a nice weekend!.

latot commented 1 month ago

mm, you are right, this seems to be a overflow on 32bit signed integer:

ogr2ogr sample.sqlite error.sqlite -overwrite -sql "select * from sample limit 55964"
Warning 1: Integer overflow occurred when trying to set 32bit field.

ogrinfo error.sqlite -sql "select * from sample limit 55964"
... a lot of printing
Warning 1: Integer overflow occurred when trying to set 32bit field.
OGRFeature(SELECT):55963
  avaluo_total (Integer) = 2147483647

As you said, the conversion features works too late to pick this values.

rouault commented 4 weeks ago

Closing as things behave as expected/designed in that situation, and filed enhancement and improved warning messages should help

OSGeo / gdal