SQL: query with PIVOT fails when aliasing the subquery

[bpintea]

Aliasing the subquery of a query with PIVOT:

sql> SELECT * FROM (SELECT languages, gender, salary FROM test_emp) as foo PIVOT (AVG(salary) FOR gender IN ('M', 'F'));

fails with below exception:

Server error [Server sent bad type [illegal_argument_exception]. Original type was [Composite source names must be unique, found duplicates: [18219]]. [java.lang.IllegalArgumentException: Composite source names must be unique, found duplicates: [18219]
        at org.elasticsearch.search.aggregations.bucket.composite.CompositeAggregationBuilder.validateSources(CompositeAggregationBuilder.java:193)
        at org.elasticsearch.search.aggregations.bucket.composite.CompositeAggregationBuilder.<init>(CompositeAggregationBuilder.java:80)
        at org.elasticsearch.xpack.sql.querydsl.agg.Aggs.asAggBuilder(Aggs.java:90)
        at org.elasticsearch.xpack.sql.execution.search.SourceGenerator.sourceBuilder(SourceGenerator.java:68)
        at org.elasticsearch.xpack.sql.execution.search.Querier.query(Querier.java:110)
        at org.elasticsearch.xpack.sql.plan.physical.EsQueryExec.execute(EsQueryExec.java:59)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$sql$5(SqlSession.java:164)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:63)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$physicalPlan$4(SqlSession.java:160)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:63)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:156)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:63)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:144)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:63)
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:277)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:63)
        at org.elasticsearch.client.node.NodeClient.lambda$executeLocally$0(NodeClient.java:89)
        at org.elasticsearch.tasks.TaskManager$1.onResponse(TaskManager.java:144)
        at org.elasticsearch.tasks.TaskManager$1.onResponse(TaskManager.java:138)
        at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$0(TransportFieldCapabilitiesAction.java:88)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:101)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:97)
        at org.elasticsearch.client.node.NodeClient.lambda$executeLocally$0(NodeClient.java:89)
        at org.elasticsearch.tasks.TaskManager$1.onResponse(TaskManager.java:144)
        at org.elasticsearch.tasks.TaskManager$1.onResponse(TaskManager.java:138)
        at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:261)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:247)
        at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleResponse(TransportService.java:1049)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.processResponse(TransportService.java:1127)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1107)
        at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:54)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:47)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:30)
        at org.elasticsearch.action.ActionRunnable.lambda$supply$0(ActionRunnable.java:58)
        at org.elasticsearch.action.ActionRunnable$2.doRun(ActionRunnable.java:73)
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:769)
        at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:835)
]]

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[astefan]

The same error message happens for sub-selects as well (no PIVOT present): SELECT a.gender, count(*), a.m FROM (SELECT gender, MAX(salary) m FROM test_emp GROUP BY gender ORDER BY gender) a GROUP BY a.gender

[bpintea]

The same error message happens for sub-selects as well (no PIVOT present)

@astefan What version did you encounter this with? 7.13.0 seems to return without failure (but incorrect result), while current master (past #75517) fails it in the verifier.

[bpintea]

To further clarify the above statement: the non-PIVOT query given as example in the comment further up:

SELECT a.gender, count(*), a.m FROM (SELECT gender, MAX(salary) m FROM test_emp GROUP BY gender ORDER BY gender) a GROUP BY a.gender

returns in 7.13 with no failure (but incorrect result) and fails in 7.14 and above thanks to #75517.

The original failing PIVOT query:

SELECT * FROM (SELECT languages, gender, salary FROM test_emp) as foo PIVOT (AVG(salary) FOR gender IN ('M', 'F'))

still fails in 7.14 and later, though now with a different stack:

SqlIllegalArgumentException[Cannot resolve field extractor index for column [foo.languages{f}#149]]
	at org.elasticsearch.xpack.sql.querydsl.container.QueryContainer.columnMask(QueryContainer.java:209)
	at org.elasticsearch.xpack.sql.execution.search.Querier$BaseAggActionListener.<init>(Querier.java:405)
	at org.elasticsearch.xpack.sql.execution.search.Querier$CompositeActionListener.<init>(Querier.java:370)
	at org.elasticsearch.xpack.sql.execution.search.Querier.query(Querier.java:141)
	at org.elasticsearch.xpack.sql.plan.physical.EsQueryExec.execute(EsQueryExec.java:60)
	at org.elasticsearch.xpack.sql.plan.physical.LeafExec.execute(LeafExec.java:31)
	at org.elasticsearch.xpack.sql.session.SqlSession.lambda$sql$5(SqlSession.java:172)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:134)
	at org.elasticsearch.xpack.sql.session.SqlSession.lambda$physicalPlan$4(SqlSession.java:168)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:134)
	at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:164)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:134)
	at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:152)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:134)
	at org.elasticsearch.xpack.ql.index.IndexResolver.lambda$resolveAsMergedMapping$4(IndexResolver.java:298)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:134)

[elasticsearchmachine]

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