Given the mapping

```
       column       |     type      |    mapping    
--------------------+---------------+---------------
...
last_name           |VARCHAR        |text           
last_name.keyword   |VARCHAR        |keyword        
...
```

the query `SELECT last_name IS NULL FROM test_emp GROUP BY 1` fails with 

```
{
  "error": {
    "root_cause": [
      {
        "type": "script_exception",
        "reason": "runtime error",
        "script_stack": [
          "org.elasticsearch.index.mapper.TextFieldMapper$TextFieldType.fielddataBuilder(TextFieldMapper.java:814)",
          "org.elasticsearch.index.fielddata.IndexFieldDataService.getForField(IndexFieldDataService.java:109)",
          "org.elasticsearch.index.query.SearchExecutionContext.lambda$lookup$2(SearchExecutionContext.java:503)",
          "org.elasticsearch.search.lookup.SearchLookup.getForField(SearchLookup.java:105)",
          "org.elasticsearch.search.lookup.LeafDocLookup$1.run(LeafDocLookup.java:72)",
          "org.elasticsearch.search.lookup.LeafDocLookup$1.run(LeafDocLookup.java:69)",
          "java.base/java.security.AccessController.doPrivileged(AccessController.java:318)",
          "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:69)",
          "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:27)",
          "org.elasticsearch.xpack.ql.expression.function.scalar.whitelist.InternalQlScriptUtils.docValue(InternalQlScriptUtils.java:37)",
          "InternalQlScriptUtils.isNull(InternalQlScriptUtils.docValue(doc,params.v0))",
          "                                                                      ^---- HERE"
        ],
        "script": "InternalQlScriptUtils.isNull(InternalQlScriptUtils.docValue(doc,params.v0))",
        "lang": "painless",
        "position": {
          "offset": 70,
          "start": 0,
          "end": 75
        }
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "test_emp",
        "node": "6a6CBLYYQFa6gqsxbMkn5Q",
        "reason": {
          "type": "script_exception",
          "reason": "runtime error",
          "script_stack": [
            "org.elasticsearch.index.mapper.TextFieldMapper$TextFieldType.fielddataBuilder(TextFieldMapper.java:814)",
            "org.elasticsearch.index.fielddata.IndexFieldDataService.getForField(IndexFieldDataService.java:109)",
            "org.elasticsearch.index.query.SearchExecutionContext.lambda$lookup$2(SearchExecutionContext.java:503)",
            "org.elasticsearch.search.lookup.SearchLookup.getForField(SearchLookup.java:105)",
            "org.elasticsearch.search.lookup.LeafDocLookup$1.run(LeafDocLookup.java:72)",
            "org.elasticsearch.search.lookup.LeafDocLookup$1.run(LeafDocLookup.java:69)",
            "java.base/java.security.AccessController.doPrivileged(AccessController.java:318)",
            "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:69)",
            "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:27)",
            "org.elasticsearch.xpack.ql.expression.function.scalar.whitelist.InternalQlScriptUtils.docValue(InternalQlScriptUtils.java:37)",
            "InternalQlScriptUtils.isNull(InternalQlScriptUtils.docValue(doc,params.v0))",
            "                                                                      ^---- HERE"
          ],
          "script": "InternalQlScriptUtils.isNull(InternalQlScriptUtils.docValue(doc,params.v0))",
          "lang": "painless",
          "position": {
            "offset": 70,
            "start": 0,
            "end": 75
          },
          "caused_by": {
            "type": "illegal_argument_exception",
            "reason": "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [last_name] in order to load field data by uninverting the inverted index. Note that this can use significant memory."
          }
        }
      }
    ]
  },
  "status": 400
}
```

`/translate` reveals that SQL does not use the `last_name.keyword` subfield in this case:

```
{
  "size": 0,
  "_source": false,
  "aggregations": {
    "groupby": {
      "composite": {
        "size": 1000,
        "sources": [
          {
            "4642bd56": {
              "terms": {
                "script": {
                  "source": "InternalQlScriptUtils.isNull(InternalQlScriptUtils.docValue(doc,params.v0))",
                  "lang": "painless",
                  "params": {
                    "v0": "last_name"
                  }
                },
                "missing_bucket": true,
                "value_type": "boolean",
                "order": "asc"
              }
            }
          }
        ]
      }
    }
  }
}
```

Explicitly using the `.keyword` subfield works (`SELECT last_name.keyword IS NULL FROM test_emp GROUP BY 1`).

Also some other queries like `SELECT CONCAT('Mr. ', last_name) FROM test_emp GROUP BY 1` work as expected.

It looks like only queries grouping on conditions that can be expressed as ES filters are affected by this issue.
x-pack/plugin/ql/src/main/java/org/elasticsearch/xpack/ql/expression/function/scalar/ScalarFunction.java
template = "{ql}.nullSafeCastNumeric({},{})";
            paramsBuilder.variable(nullSafeCastDataType.name());
        }
        return new ScriptTemplate(processScript(template), paramsBuilder.build(), dataType());
    }
    
    
    protected ScriptTemplate scriptWithGrouping(GroupingFunction grouping) {
        String template = PARAM;
        return new ScriptTemplate(processScript(template), paramsBuilder().grouping(grouping).build(), dataType());
    }
    protected ScriptTemplate scriptWithField(FieldAttribute field) {
        Params params = paramsBuilder().variable(field.name()).build();
        
        return field.dataType() != UNSIGNED_LONG
            ? new ScriptTemplate(processScript(Scripts.DOC_VALUE), params, dataType())
            : new ScriptTemplate(
                processScript(format("{ql}.", "nullSafeCastToUnsignedLong({})", Scripts.DOC_VALUE)),
                params,
                UNSIGNED_LONG
            );
    }
    protected String processScript(String script) {
        return formatTemplate(script);
    }
    protected String formatTemplate(String template) {
        return Scripts.formatTemplate(template);
    }
}