can not find any result when filter exist in table function

[allendang001]

[allendang001]

[allendang001]

@kasiafi PTAL

[kasiafi]

@allendang001 in the first example, the filter predicate cfloat=1.1 is handled by Trino. The float type is mapped to trino real type, and the engine applies coercions which it finds necessary for the comparison. In the second example, the comparison is fully handled by mysql, so different semantics may apply.

Regarding the other pair of examples with ordering, please note that it should not be expected that the query returns ordered results if the pass-through query is ordered. As a general rule for SQL, only the top-level ordering holds at output. See https://trino.io/docs/current/connector/mysql.html?highlight=mysql#table-functions

[allendang001]

@kasiafi Yes, I understand this, the first problem, trino can't parse the sql in ptf, it's really hard to solve. But the second problem is actually caused by trino using the sql in ptf as a subquery. I feel that trino can let jdbc directly execute the SQL in ptf，what do you think about the second problem

[kasiafi]

trino can't parse the sql in ptf

The idea behind query pass-through is that Trino does not know or care about the passed query. This way, you can pass syntax which Trino does not understand (like here for Elasticsearch https://trino.io/docs/current/connector/elasticsearch.html?highlight=raw_query#raw-query-varchar-table). It also allows doing "forced pushdown" when you don't want Trino to get in the way.

If you want Trino to parse your query, it is probably not a good candidate for query pass-through.

the second problem is actually caused by trino using the sql in ptf as a subquery

This is just how SQL works. A table function invocation has the same range in a query as a subquery or a source table. They all follow the same rules concerning ordering. If you want to get ordered results, add ORDER BY to the top-level query.