v.in.ogr can't create tables in GRASS 7.4

[pierreroudier]

I don't seem to be able to import OGR sources (I tried both ESRI Shapefile and GPKG sources):

GRASS 7.4.0 (nz_soil_ph):~ > v.in.ogr in=river-flows.gpkg out=rivers --o
Check if OGR layer <river_flows> contains polygons...
 100%
WARNING: Vector map <rivers> already exists and will be overwritten
Creating attribute table for layer <river_flows>...
DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "ORDER": syntax error

DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "ORDER": syntax error

ERROR: Unable to create table: 'create table rivers (cat integer, ORDER
       double precision, NZREACH double precision, MALFCumecs double
       precision, MeanFlowCumecs double precision, Feb double precision,
       FRE3 double precision, SpecMeanFlowCumecs double precision,
       SpecMALFCumecs double precision, Shape_Length double precision)'

Here is more info about the dataset I'm using:

GRASS 7.4.0 (nz_soil_ph):~ > ogrinfo -al -so river-flows.gpkg
INFO: Open of `Dropbox/projects/nz-national-grid-ph/covariates/river-flows.gpkg'
      using driver `GPKG' successful.

Layer name: river_flows

[...]

Geometry: Multi Line String
Feature Count: 576277
Extent: (1090150.000000, 4748850.000000) - (2089110.000000, 6193370.000000)
Layer SRS WKT:
PROJCS["NZGD2000 / New Zealand Transverse Mercator 2000",
    GEOGCS["NZGD2000",
        DATUM["New_Zealand_Geodetic_Datum_2000",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            TOWGS84[0,0,0,0,0,0,0],
            AUTHORITY["EPSG","6167"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AUTHORITY["EPSG","4167"]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",0],
    PARAMETER["central_meridian",173],
    PARAMETER["scale_factor",0.9996],
    PARAMETER["false_easting",1600000],
    PARAMETER["false_northing",10000000],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AUTHORITY["EPSG","2193"]]
FID Column = fid
Geometry Column = geom
ORDER: Real (0.0)
NZREACH: Real (0.0)
MALFCumecs: Real (0.0)
MeanFlowCumecs: Real (0.0)
Feb: Real (0.0)
FRE3: Real (0.0)
SpecMeanFlowCumecs: Real (0.0)
SpecMALFCumecs: Real (0.0)
Shape_Length: Real (0.0)

This dataset is publicly available: ​https://data.mfe.govt.nz/layer/53309-river-flows/

[hellik]

ORDER: Real (0.0)

Order may be a SQL reserved word. try to rename it before

[pierreroudier]

Replying to hellik:

ORDER: Real (0.0)

Order may be a SQL reserved word. try to rename it before

Thanks -- that was indeed the problem! (This goes to show how limited my knoywledge of SQL is).

It would maybe pay off to put a warning about this in the v.in.ogr documentation for users who like me don't do SQL very well.

This is a patch that tries to address this:

--- v.in.ogr.html	2018-05-17 09:20:57.587213200 +1200
+++ v.in.ogr-pr.html	2018-05-17 09:30:55.141497148 +1200
@@ -411,19 +411,41 @@
 
 <h2>ERROR MESSAGES</h2>
 
-<dl>
-<dt>DBMI-DBF driver error: SQL parser error: syntax error,
-unexpected DESC, expecting NAME processing 'DESC'</dt>
-<dd>indicates that a column name corresponds to a reserved SQL word (here: 'DESC').
+<h3>SQL synthax errors</h3>
+
+Depending on the SQL driver, you can have errors such as:
+
+<div class="code"><pre>
+DBMI-SQLite driver error:
+Error in sqlite3_prepare():
+near "ORDER": syntax error
+</pre/</div>
+
+Or:
+
+<div class="code"><pre>
+DBMI-DBF driver error:
+SQL parser error:
+syntax error, unexpected DESC, expecting NAME processing 'DESC
+</pre/</div>
+
+This indicates that a column name in the input dataset corresponds to a reserved SQL word (here: 'ORDER' and 'DESC' respectively).
 A different column name should be used. The <b>columns</b> parameter can be used
-to assign different column names on the fly.</dd>
-<dt>Projection of dataset does not appear to match the current location.</dt>
-<dd>You need to create a location whose projection matches the data
+to assign different column names on the fly.
+
+See a list of SQL reserved words for SQLite (the default driver) <a href='https://www.sqlite.org/lang_keywords.html'>here</a>.
+
+<h3>Projection errors</h3>
+
+<div class="code"><pre>
+Projection of dataset does not appear to match the current location.
+</pre/</div>
+
+You need to create a location whose projection matches the data
 you wish to import. Try using <b>location</b> parameter to create a
 new location based upon the projection information in the file. If
 desired, you can then re-project it to another location
-with <em><a href="v.proj.html">v.proj</a></em>.</dd>
-</dl>
+with <em><a href="v.proj.html">v.proj</a></em>.
 
 <h2>REFERENCES</h2>
 

[neteler]

Reopening: please add the patch as attachment, thanks

(the ticket may be closed once the patch is applied, like this or in modified form and backported if needed)

[martinl]

G74:v.in.ogr could be more clever about such issues. Rename column to eg. ORDER_, print warning and continue.

[pierreroudier]

Patch for the v.in.ogr documentation

[pierreroudier]

Replying to martinl:

G74:v.in.ogr could be more clever about such issues. Rename column to eg. ORDER_, print warning and continue.

That would be a great feature -- with maybe a flag to control this behaviour: when using a flag such as say -s, v.in.ogr would add an underscore to any reserved SQL word rather than failing.

In SQLite you could also bracket those words, but this does not look like standard SQL (source: ​https://www.sqlite.org/lang_keywords.html)

[sbl]

Quoting identifiers (as also described in the link you provided) should work...

However, this would require significant changes, as modules (subsequent to v.in.ogr) are not necessarily able to handle quoted identifiers.

In addition, this SQL standard is not implemented in all DBMS equally. E.g. MySQL uses backticks for quoting (instead of quotes).

See also: #3071

[neteler]

Replying to pierreroudier:

Patch for the v.in.ogr documentation

Thanks, applied with modifications to trunk (r73530), relbr76 (r73531), and relbr74 (r73532).

Turning into enhancement ticket for "Rename column to eg. ORDER_, print warning and continue" suggestion from above.

[neteler]

For SQL reserved word collisions, see also #578, #1755, #3563

[martinl]

Ticket retargeted after milestone closed

[martinl]

Ticket retargeted after milestone closed