gbif / occurrence

Occurrence store, download, search
Apache License 2.0
22 stars 15 forks source link

SQL validation returns sql that cannot be validated #357

Open MortenHofft opened 3 weeks ago

MortenHofft commented 3 weeks ago

It seems odd that the SQL validator accepts a query, then rewrites it as invalid sql

e.g.

{
  "format": "SQL_TSV_ZIP", 
  "sql": "SELECT \n  \"kingdom\", \"kingdomkey\", \"phylum\", \"phylumkey\", \"class\", \"classkey\", \"family\", \"familykey\", \"genus\", \"genuskey\", \"species\", \"specieskey\", \"year\",\n  COUNT(*) AS occurrences, MIN(COALESCE(coordinateUncertaintyInMeters, 1000)) AS minCoordinateUncertaintyInMeters, MIN(GBIF_TemporalUncertainty(eventDate)) AS minTemporalUncertainty\nFROM\n  occurrence\nWHERE \n  countryCode = 'PL'\nGROUP BY\n  \"kingdom\", \"kingdomkey\", \"phylum\", \"phylumkey\", \"class\", \"classkey\", \"family\", \"familykey\", \"genus\", \"genuskey\", \"species\", \"specieskey\", \"year\"" 
}

is accepted and rewritten as

{
  "sql": "SELECT kingdom, kingdomkey, phylum, phylumkey, class, classkey, family, familykey, genus, genuskey, species, specieskey, year, COUNT(*) occurrences, MIN(CASE WHEN coordinateuncertaintyinmeters IS NOT NULL THEN coordinateuncertaintyinmeters ELSE 1000 END) mincoordinateuncertaintyinmeters, MIN(GBIF_TEMPORALUNCERTAINTY(eventdate)) mintemporaluncertainty\nFROM occurrence\nWHERE occurrence.countrycode = 'PL'\nGROUP BY occurrence.kingdom, occurrence.kingdomkey, occurrence.phylum, occurrence.phylumkey, occurrence.class, occurrence.classkey, occurrence.family, occurrence.familykey, occurrence.genus, occurrence.genuskey, occurrence.species, occurrence.specieskey, occurrence.year",
  "notificationAddresses": [],
  "sendNotification": false,
  "type": "OCCURRENCE",
  "format": "SQL_TSV_ZIP"
}

which in turn is invalid if you pass it back to the validator