search

NOAA-OWP / wres

Code and scripts for the Water Resources Evaluation Service
Other
2 stars 1 forks source link

As a user, I want to declare evaluations with arbitrary feature names and have them succeed in all circumstances #98

Open epag opened 3 weeks ago

epag commented 3 weeks ago

Author Name: James (James) Original Redmine Issue: 111090, https://vlab.noaa.gov/redmine/issues/111090 Original Date: 2022-12-27

Given an evaluation that uses arbitrary feature names, including reserved characters that are unsanitary for SQL, such as "volcano, iceland" When that evaluation is executed against a database Then it should succeed (other factors being equal to success)

epag commented 3 weeks ago

Original Redmine Comment Author Name: James (James) Original Date: 2022-12-27T16:15:33Z

Evidence to the contrary around #100674-10 through #100674-14.

To reproduce, adapt system test scenario512 so that the @pizza_near_volcano_observed_in_celsius.csv@ looks like this:

value_date,variable_name,location,location_description,measurement_unit,value
0079-08-24T13:00:00Z,🍕,"volcano, iceland","volcano, near mud mountain",℃,29.0
0079-08-24T14:00:00Z,🍕,"volcano, iceland","volcano, near mud mountain",℃,443.0
0079-08-24T15:00:00Z,🍕,"volcano, iceland","volcano, near mud mountain",℃,1117.0

( edit: although, strictly speaking, it doesn't require this data to reproduce the issue, only to demonstrate a succeeding evaluation once fixed, because the issue is triggered by the following declaration alone, not the data. )

And the feature declaration like this:

        <feature left="volcano, iceland" right="mountain" />
</code>

Which will produce #100674-12.

epag commented 3 weeks ago

Original Redmine Comment Author Name: James (James) Original Date: 2022-12-27T16:18:58Z

The solution is to avoid performing feature intersection in a temporary database table via the @ProjectScriptGenerator@, since this requires the feature names to be sanitized upfront. From what I recall, a prepared statement cannot be used for a temporary table because it doesn't stick around long enough, something like that.

epag commented 3 weeks ago

Original Redmine Comment Author Name: James (James) Original Date: 2022-12-27T16:22:14Z

From what I recall, there isn't a straightforward fix for this one, it will require significant refactoring of the feature intersection script.