Feature Geometry Encoding Testing returns java.lang.NullPointerException

[phidrho]

Hi,

while trying to validate my GPKG file (attached) I get only one test passed (geopackage Version) and all other skipped. Only information that I got is for feature Geometry Encoding Testing where I got Reason of failure: java.lang.RuntimeException: java.lang.NullPointerException. Documentation says that this test should throw SQL Exception:

public void featureGeometryEncodingTesting(
    String thisTableName,
    String thisColumnName,
    String geomType,
    Integer srs_id,
    byte z_flag,
    byte m_flag)
  throws SQLException

• Edit: I requested for attachment removal because I corrected some errors that I found, and will re-upload a new example as soon as I prepare it. test.zip

[phidrho]

It seems that there is some bug related to tests when indexes are used in SQLite/GPKG file.

<test-method status="FAIL" signature="initCommonFixture(org.testng.ITestContext)[pri:0, instance:org.opengis.cite.gpkg12.core.DataContentsTests@20f50c7b]" name="initCommonFixture" is-config="true" duration-ms="0" started-at="2024-04-25T13:07:54Z" finished-at="2024-04-25T13:07:54Z">
          <params>
            <param index="0">
              <value>
                <![CDATA[org.testng.TestRunner@57c70b96]]>
              </value>
            </param>
          </params>
          <exception class="java.sql.SQLException">
            <message>
              <![CDATA[[SQLITE_CORRUPT]  The database disk image is malformed (malformed database schema (idx_un-adrese_cestice) - near "(": syntax error)]]>
            </message>
          </exception> <!-- java.sql.SQLException -->
          <reporter-output>
          </reporter-output>
        </test-method> <!-- initCommonFixture -->

Here is my DDL for this index, and as I've investigated it seems that It's not recommended to use "UNIQUE" keyword if expressions are used (ifnull() in my case).

CREATE UNIQUE INDEX "idx_un-adrese_cestice" ON ADRESE_CESTICE (
    CESTICA_ID COLLATE RTRIM,
    ifnull(ADRESA_OPISNA, '¤_¤_¤') COLLATE NOCASE,
    ifnull(NASELJE, '¤_¤_¤') COLLATE NOCASE,
    ifnull(NASELJE_MBR, '¤_¤_¤') COLLATE RTRIM,
    ifnull(ULICA, '¤_¤_¤') COLLATE NOCASE,
    ifnull(ULICA_RBR, '¤_¤_¤') COLLATE RTRIM
);

When I tried to remove UNIQUE keyword:

CREATE INDEX "idx_un-adrese_cestice" ON ADRESE_CESTICE (
    CESTICA_ID COLLATE RTRIM,
    ifnull(ADRESA_OPISNA, '¤_¤_¤') COLLATE NOCASE,
    ifnull(NASELJE, '¤_¤_¤') COLLATE NOCASE,
    ifnull(NASELJE_MBR, '¤_¤_¤') COLLATE RTRIM,
    ifnull(ULICA, '¤_¤_¤') COLLATE NOCASE,
    ifnull(ULICA_RBR, '¤_¤_¤') COLLATE RTRIM
);

then I got another error on "COLLATE" keyword. There should not be any problems with this syntax according to official graph:

There is a note about SQLite version in official documentation that may be important (I don't know what version is used for tests):

Indexes on expression will not work with versions of SQLite prior to [version 3.9.0](https://sqlite.org/releaselog/3_9_0.html) (2015-10-14).

[dstenger]

Thanks for reporting. We will do further investigation.

[phidrho]

I have successfully narrowed down the issue. Problem with validation happens when expression is used inside index.

I am attaching zip file with 3 GPKG files:

1) OK_minimal_vector_gpkg.gpkg

• minimal GPKG with 1 geometry point_geom_tbl, and 1 attribute table attribute_data_tbl
• validation works fine 2) OK_minimal_vector_gpkg_idx.gpkg
• same GPKG as in 1), but I added simple index on geometry table: CREATE INDEX idx_text_geom_tbl ON point_geom_tbl (text_data);
• validation works fine 3) BUG_minimal_vector_gpkg_ifnull_idx.gpkg
• same GPKG as in 1), but I added index which uses ifnull expression on geometry table: CREATE INDEX idx_text_geom_tbl ON point_geom_tbl (ifnull(text_data, '¤'));
• validation doesn't work

GPKG VALIDATION BUG.zip