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 _______________________________________________ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev