Opened 5 years ago

Closed 5 years ago

#4346 closed defect (fixed)

The SQLite GDAL driver recognizes datatypes incorrectly.

Reported by: asuchander Owned by: warmerdam
Priority: normal Milestone: 1.9.0
Component: default Version: 1.8.1
Severity: normal Keywords:
Cc: winkey

Description

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.

Attachments (1)

db.sqlite.zip (753.3 KB) - added by asuchander 5 years ago.
DB that illustrates this issue.

Download all attachments as: .zip

Change History (4)

comment:1 Changed 5 years ago by Even Rouault

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

comment:2 Changed 5 years ago by winkey

Cc: winkey added

Changed 5 years ago by asuchander

Attachment: db.sqlite.zip added

DB that illustrates this issue.

comment:3 Changed 5 years ago by Even Rouault

Milestone: 1.9.0
Resolution: fixed
Status: newclosed

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

Note: See TracTickets for help on using tickets.