[gdal-dev] OGR SQL CAST and field name alias selects incorrect fields in some cases

[Eli Adam]

Hi all,

I'm trying to use OGR SQL to CAST and field name aliasing together and
getting incorrect results (or issuing incorrect commands) when
combined with two LEFT JOINs on MDB (both ODBC and MDB drivers).  The
results are incorrect in that the fields are populated with incorrect
information, specially, filling it with other fields that are not
selected at all.  I'm seeing this in windows close to trunk (ODBC) and
a statically compiled ubuntu 10.04 trunk from a few months ago (Access
MDB/Java/Jackess).  The data is somewhat messy so it could be a case
of ambiguous table/query names after some shortening is applied or
something similar although nothing jumps out in that regard to me.
Both the tables that are joined to have a field named DocumentName.
Also, the way I am using the CAST in this case isn't needed but I ran
into it and wanted to report it.

The simple case works as I expect:
ogr2ogr surveys_simply_cast2.shp Surveys.shp -sql "SELECT
CAST(surveyid AS character(255)) AS surveyid_alias, COMMENT FROM
Surveys" Warning 6: Normalized/laundered field name: 'surveyid_alias'
to 'surveyid_a'

Specifically, I get a field named surveyid_a that is character 255 and
has the correct content:
ogrinfo -al -so surveys_simply_cast2.shp surveys_simply_cast2
...
surveyid_a: String (255.0)
COMMENT: String (15.0)


Now I try the same thing with two LEFT JOINs to mdb/ODBC:
ogr2ogr surveys_output_two_fields.shp Surveys.shp -sql "SELECT
CAST(webimage AS character(255)) AS webimage_1,  CAST(SubdivisionName
AS character(255)) AS Subdivisio FROM Surveys LEFT JOIN
'ODBC:cygwinSurveys'.Local_Survey_Image ON
Surveys.surveyid=Local_Survey_Image.DocumentName LEFT JOIN
'ODBC:cygwinSurveys'.Survey ON Surveys.surveyid=Survey.DocumentName"

Warning 6: Field FileDate create as date field, though DateTime requested.

This gives me the fields that I expect:
ogrinfo -al -so surveys_output_two_fields.shp surveys_output_two_fields
...
webimage_1: String (255.0)
Subdivisio: String (255.0)

However, they are populated with incorrect information.  The field
webimage_1 is filled with values from the field
cygwinSurveys.Survey.Comments.  The field Subdivisio is filled with
values from cygwinSurveys.Survey.ImagePilot (or Net_PDF - yes there
are two fields with identical information).  Adding --debug ON shows
that these tables don't have FID columns (I could not see all the
debug content but that seemed to be the only non-routine information):
OGR_ODBC: Table Local_Survey_Image has no identified FID column.
...

On Ubuntu, the results are the same, my command is:
LD_LIBRARY_PATH=.:/usr/lib/jvm/java-6-openjdk/jre/lib/i386/client
ogr2ogr  /tmp/surveys_etl/surveys.shp /tmp/surveys_etl/Surveys.shp
-sql " SELECT Surveys.surveyid AS SURVEYID,DocumentName AS
DocumentNa,SurveyorKey as SurveyorKe,CAST(webimage AS character(255))
AS webimage_1,FileDate,Client, SurveyType,ClerkNumber AS
ClerkNumbe,CAST(SubdivisionName AS character(255)) AS Subdivisio FROM
Surveys LEFT JOIN '/tmp/surveys_etl/Surveys.mdb'.Survey ON
Surveys.surveyid=Survey.DocumentName LEFT JOIN
'/tmp/surveys_etl/Surveys.mdb'.Local_Survey_Image ON
Surveys.surveyid=Local_Survey_Image.DocumentName"

Excluding the CAST gives the correct results:
ogr2ogr surveys_output_no_cast.shp Surveys.shp -sql "SELECT webimage
AS webimage_1, SubdivisionName AS Subdivisio FROM Surveys LEFT JOIN
'ODBC:cygwinSurveys'.Local_Survey_Image ON
Surveys.surveyid=Local_Survey_Image.DocumentName LEFT JOIN
'ODBC:cygwinSurveys'.Survey ON Surveys.surveyid=Survey.DocumentName"

Trying to reintroduce the CAST with only one join works correctly (for
either join):
ogr2ogr surveys_output_singel_join.shp Surveys.shp -sql "SELECT
CAST(webimage AS character(255)) AS webimage_1 FROM Surveys LEFT JOIN
'ODBC:cygwinSurveys'.Local_Survey_Image ON
Surveys.surveyid=Local_Survey_Image.DocumentName"

ogr2ogr surveys_output_singleb.shp Surveys.shp -sql "SELECT
CAST(SubdivisionName AS character(255)) AS Subdivisio FROM Surveys
LEFT JOIN 'ODBC:cygwinSurveys'.Survey ON
Surveys.surveyid=Survey.DocumentName"

I can package this up for download by anyone interested, just contact
me offline for the link.  (I can also include the statically compiled
binary as well although it may only work on Ubuntu 10.04 with
Java/Jackess/etc and called setting LD_LIBRARY_PATH.)

Bests, Eli