SQL: Support for aliases in QUERY

[elasticmachine]

Original comment by @gingerwizard:

Spotfire submits fieldnames with table aliases consistently. This is fine except when injected into our QUERY parameter.

For example, consider the following query:

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')

Notice here how we refer to 'fields=m1.purchaser_type_name'. This is passed directly to query string, resulting in no hits i.e.

"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
          }

Discussed with @costin and we might want to either support aliases here or extract field names from the string. Noting the lack of support and for discussion.

[nik9000]

I think this is worth implementing. I'm not really sure how to implement it though.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[wchaparro]

superceded by ES|QL