Environment setting to default Integer to Long not BigInteger

[KimOllivier]

When importing data from geopackage or sqlite any field defined as Integer in the DDL gets cast to BigInteger instead of (Long) Integer. This is a new incompatible behaviour since BigIntegers were included (ArcPro 3.2?). There are several tools that do not work with BigIntegers such as RelationshipClasses.

It would be helpful if there was a switch to turn off this unwelcome enhancement. There is a switch in Options/Map and Scene - but it doesn't work.

The only way to fix the problem is to define a FieldMapping on every copy operation and there are many of these that do not have FieldMapping as a parameter.

If the field is readonly such as OBJECTID then it cannot be changed at all. I see another user has used FME to fix this.

This is like a reverse single precision / double precision incompatibility!

Maybe a switch somewhere in the settings or the environment settings to retrofit a fix?

[BruceHarold]

Thanks Kimo I'll get this to the right team.

[Ranga_Tolapi]

Completely agree @KimOllivier 

In ArcGIS Pro 3.2, at least tools like Copy Features, Feature class to Geodatabase, etc... should honor the version of the pre-created target File Geodatabase.

If the pre-created target File Geodatabase version is 10.0 or below then data conversion/import tools should not introduce the new features of ArcGIS Pro 3.2 e.g., 64-bit OID, Big integer, Timestamp offset, Date only, Time only.

@SirishByreddy 

[KoryKramer]

Related is this bug that is currently being investigated: https://support.esri.com/en-us/bug/geopackage-feature-class-objectids-are-read-as-64bit-in-bug-00016... 

[MelanieWawryk]

ArcPro and AGOL  also do not work well with Big integer. We don't have anything that is a big integer and only use long. If you look at the layer in ArcCatelog it shows as long integer but the data design view of the same layer shows it as big integer. If we export to AGOL it gives a warning message about big integer and sure enough, it is big integer in AGOL. I guess the bug is in ArcPro 3.2.2. Please fix

[KimOllivier]

Not fixed in 3.3. Ah well, back to 3.1 so that I can attempt to port workflows from ArcMap!

There is a setting in Options/Map and Scene - but it doesn't work for me.

[DannyMac]

@KimOllivier 

After delving deeper into the issue of INTEGER vs BIGINT, I discovered the following information. ArcGIS Pro didn't support 64-bit integers until ArcGIS Pro 3.2. It's important to note that GeoPackage uses SQLite under the covers, and INT and INTEGER datatypes in SQLite have always been 64-bit. Consequently, with ArcGIS Pro 3.2 now supporting 64-bit integers using BIGINT and considering that an INTEGER is 64-bit in a GeoPackage, the columns are cast to BIGINT. From a technical standpoint, this behavior is correct, which could argue against it being classified as a bug. However, I believe a more effective approach would involve inspecting the column values before automatically casting them to BIGINT. For instance, ArcGIS could examine the values for all INTEGER columns first to determine if casting to BIGINT is necessary.

Here's an example query:

SELECT * FROM MyRoads
  WHERE MyRoads.LINK_ID < -2147483648
  OR MyRoads.LINK_ID > 2147483647;

If the query returns nothing, then casting to INTEGER would be more appropriate than using BIGINT.

[KimOllivier]

Thanks for that explanation. It does show why it is happening. But the option switch says "to use field types that are compatible with ArcGISPro 3.1", which are long integer. So even if it is correct to move 64 bit fields in Sqlite to BigInteger they do not work in Pro for many functions if they are cast that way. This must be a problem with other databases in Enterprise too. I suspect they had a very narrow view of this problem when implementing the option.

All I am asking for is for the switch to work everywhere.

[DannyMac]

@KimOllivier  I currently have an enterprise support ticket open for this issue, and I've referenced your post in my ticket to ensure that they are aware that I'm not the only one affected by this unexpected change. For us, it means we either need to update all of our application code to utilize BigInt, or we can modify all the columns after importing features and change them back to Integer. At the moment, we are choosing the latter option because none of the columns in our database would require a BigInt data type.