Support pushing down grouping keys with function

[jerryleooo]

These tests are done on PostgreSQL connector with TPC-H data.

Trino query: SELECT lower(o_clerk), SUM(o_totalprice) FROM orders GROUP BY LOWER(o_clerk);
Generated PostgreSQL query: SELECT "o_totalprice", "o_clerk" FROM "public"."orders" (full table scan)

Trino query: SELECT o_clerk, SUM(o_totalprice) FROM orders GROUP BY o_clerk;
Generated PostgreSQL query: SELECT "o_clerk", "_pfgnrtd_0" FROM (SELECT "o_clerk", sum("o_totalprice") AS "_pfgnrtd_0" FROM "public"."orders" GROUP BY "o_clerk") o (aggregation can be pushed down)

Not sure if this belongs to #7994

[assaf2]

lower(o_clerk) projection will be pushed down after #7994

[jerryleooo]

Hi @assaf2 thanks for the hard work!
I am reviewing #7994 but seems the projection is not pushed down for PostgreSQL connector, I guess there is some work that will be done in another PR?

Changes I tried: https://gist.github.com/jerryleooo/b2b92c2ce04a8ef78bfbb7e7ddc9a8f2

[assaf2]

When the rule PushProjectionIntoTableScan calls metadata.applyProjection(), the argument List<ConnectorExpression> projections should contain a io.trino.spi.expression.Call.
For example, I've changed the first query of io.trino.plugin.postgresql.TestPostgreSqlConnectorTest#testPredicatePushdown to SELECT regionkey, nationkey, lower(name) FROM nation WHERE name = #'ROMANIA' and got the following:
projections = {RegularImmutableList@21458} size = 3 0 = {Variable@21464} "nationkey::bigint" 1 = {Variable@21465} "regionkey::bigint" 2 = {Call@21466} "Call{schemaCatalogName=Optional.empty, name='lower', arguments=[name::varchar(25)]} ".

[slhmy]

Hi @assaf2 thanks for the hard work! I am reviewing #7994 but seems the projection is not pushed down for PostgreSQL connector, I guess there is some work that will be done in another PR?

Changes I tried: https://gist.github.com/jerryleooo/b2b92c2ce04a8ef78bfbb7e7ddc9a8f2

Hi, @jerryleooo. I'm trying similar things with PostgreSQL connector.
I pushed Like expression(which appears in the filter) down. And this cause myself changing applyFilter method in DefaultJdbcMetadata.java.
In this changes, I think we also missed something to be changed in DefaultJdbcMetadata.java. Maybe something in applyProjection.

Specifically we can follow implementation in applyAggregation, I think it's similar with aggregation pushdown.