The SQLite GDAL driver recognizes datatypes incorrectly.

[asuchander]

When recognizing datatypes in SQLite GDAL erroneously looks only for INTEGER, FLOAT, BLOB, TEXT, and VARCHAR, and generally uses the actual type rather than the declared type.

The sqlite3_column_decltype() function returns the DECLARED type of the column, which should be considered before the type affinity of the column. The test case here is that if you have a table that is declared with a decimal column, but the first row contains an integer, SQLITE assumes that all rows contain integers for that column. However, if the declared type is DECIMAL it should know better (and should use a REAL for the data instead, though in the code below I use TEXT.)

The DECIMAL declared type is yet one example in this case.

Effectively (for DECIMAL support) the check for declared type should contain a specific test for the declared type of decimal, like so:

if( osGeomColumn.size()

&& EQUAL(oField.GetNameRef(),osGeomColumn) ) continue;

int nColType = sqlite3_column_type( hStmt, iCol ); const char * pszDeclType = sqlite3_column_decltype(hStmt, iCol); CPLDebug("SQLITE", "decltype(%s) = %s", oField.GetNameRef(), pszDeclType ? pszDeclType : "null"); if (pszDeclType != NULL) {

if (EQUAL(pszDeclType, "INTEGER"))

nColType = SQLITE_INTEGER;

else if (EQUAL(pszDeclType, "FLOAT"))

nColType = SQLITE_FLOAT;

else if (EQUAL(pszDeclType, "DECIMAL"))

nColType = SQLITE_TEXT;

else if (EQUAL(pszDeclType, "BLOB"))

nColType = SQLITE_BLOB;

else if (EQUAL(pszDeclType, "TEXT")

EQUAL(pszDeclType, "VARCHAR"))

nColType = SQLITE_TEXT;

}

While there may be other types that fall into the same issue, this one serves to illustrate the overall bug.

[Even Rouault]

Could you attach to the ticket a small database that illustrates the issue ?

[asuchander]

DB that illustrates this issue.

[Even Rouault]

r23383 /trunk/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitelayer.cpp: SQLite: map 'DECIMAL' columns to OGR real type (#4346)