SQL Queries

SQL Queries

1. Spotfire makes the following in order to identify column values for drill down

SELECT DISTINCT m1.applicant_ethnicity_name AS APPLICANTETHNICITYNAME FROM morgages_large m1 WHERE (m1.applicant_ethnicity_name IS NOT NULL) ORDER BY APPLICANTETHNICITYNAME

{
  "error": {
    "root_cause": [
      {
        "type": "planning_exception",
        "reason": "Fail to plan items {UnplannedExec[Distinct[]]@1:8}"
      }
    ],
    "type": "planning_exception",
    "reason": "Fail to plan items {UnplannedExec[Distinct[]]@1:8}"
  },
  "status": 500
}

2. Used by spotfire when restricting by text

`SELECT action_taken_name AS ACTIONTAKENNAME, purchaser_type_name FROM morgages_mapped m1 WHERE (QUERY('Commercial','fields=m1.purchaser_type_name')) AND (m1.state_name = 'Massachusetts')``

The use of an alias in the fields parameter causes the query to return no results i.e. the alias is not handled. 

```
"query_string" : {
    "query" : "Commercial",
    "fields" : [
      "m1.purchaser_type_name^1.0"
    ],
    "type" : "best_fields",
    "default_operator" : "or",
    "max_determinized_states" : 10000,
    "enable_position_increments" : true,
    "fuzziness" : "AUTO",
    "fuzzy_prefix_length" : 0,
    "fuzzy_max_expansions" : 50,
    "phrase_slop" : 0,
    "escape" : false,
    "auto_generate_synonyms_phrase_query" : true,
    "fuzzy_transpositions" : true,
    "boost" : 1.0
  }
```

3. Query fails

`SELECT AVG(population/number_of_owner_occupied_units) FROM morgages_mapped WHERE state_abbr = 'CA'`


{
  "error": {
    "root_cause": [
      {
        "type": "sql_illegal_argument_exception",
        "reason": "Does not know how to convert argument Div[] for function Avg[]"
      }
    ],
    "type": "sql_illegal_argument_exception",
    "reason": "Does not know how to convert argument Div[] for function Avg[]"
  },
  "status": 500
}

4. SELECT AVG(CAST(MONTH(application_date) AS INT)) FROM mortgages

[Code: , SQL State: bad_server]  Server encountered an error [Does not know how to convert argument MonthOfYear[Europe/Lisbon] for function Avg[]]. [SqlIllegalArgumentException[Does not know how to convert argument MonthOfYear[Europe/Lisbon] for function Avg[]]
    at org.elasticsearch.xpack.sql.planner.QueryTranslator.field(QueryTranslator.java:428)
    at org.elasticsearch.xpack.sql.planner.QueryTranslator$Avgs.toAgg(QueryTranslator.java:760)
    at org.elasticsearch.xpack.sql.planner.QueryTranslator$Avgs.toAgg(QueryTranslator.java:756)
    at org.elasticsearch.xpack.sql.planner.QueryTranslator$SingleValueAggTranslator.asAgg(QueryTranslator.java:845)
    at org.elasticsearch.xpack.sql.planner.QueryTranslator$AggTranslator.apply(QueryTranslator.java:834)
    at org.elasticsearch.xpack.sql.planner.QueryTranslator.toAgg(QueryTranslator.java:184)
    at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.addAggFunction(QueryFolder.java:432)
    at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:363)
    at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:231)
    
5. Use of an alias from a SELECT in a WHERE clause fails e.g.

SELECT respondent_id, applicant_income_000s/loan_amount_000s AS income_to_earnings_ratio FROM mortgages WHERE applicant_income_000s/loan_amount_000s > 1 ORDER BY income_to_earnings_ratio

Found 1 problem(s)
line 1:111: Unknown column [income_to_earnings_ratio]