Evaluate how SQL interacts with Painless

[stu-elastic]

In order to allow SQL to write to the Painless internal representation via an IR builder, we need to look at how parsed SQL interacts with Painless to get good API ideas.

Base class is InternalSqlScriptUtils registered in sql_whitelist.txt.

We can check QueryTranslatorTests eg testChronoFieldBasedDateTimeFunctionsWithMathIntervalAndGroupBy to get a sense of the script generation which takes place through the ScriptWeaver interface.

Every scalar function implements it's own script. For something like SELECT f1(f2(f3(f4…..)each function generates its portion of the ScriptTemplate which is passed to the next one in the chain resulting the the final script.

The parser lives in org.elasticsearch.xpack.sql.parser, SqlParser, ExpressionBuilder. SqlBase.g4 is the antlr4 grammar.

cc: @jdconrad @matriv
Related: #49869

[elasticmachine]

Pinging @elastic/es-core-infra (:Core/Infra/Scripting)

[stu-elastic]

SQL goes through an Analysis phase, verification phase, optimization phase, query translation phase. We'd wanna hook into the query translation phase. Instead of painless scripts being the output of that phase, we'd hook directly into an IR builder.

[stu-elastic]

Related: #49672

[stu-elastic]

This is being addressed with the compiler rework.