Hello, I'm having a problem using the STATE_TTL hint from within sql-client. Here's a trivial example:

CREATE TABLE parts (
    `timestamp`        TIMESTAMP(3),
    `partid`           STRING,
    `region`           INT,
    WATERMARK FOR `timestamp` AS `timestamp`
) WITH ( 
    'connector' = 'filesystem', 
    'path' = 'file:///prototypes/left-join/parts.json', 
    'format' = 'json'
);

CREATE TABLE partners (
    `timestamp`        TIMESTAMP(3),
    `region`           INT,
    `partner`          STRING,
    WATERMARK FOR `timestamp` AS `timestamp`
) WITH ( 
    'connector' = 'filesystem', 
    'path' = 'file:///prototypes/left-join/regions.json', 
    'format' = 'json'
);

If I

select /*+ STATE_TTL('parts'='5m') */ * from parts left join partners on parts.region = partners.region;

sql-client (for Flink 1.19.1) says,

Flink SQL> select /*+ STATE_TTL('parts'='5m') */ * from parts left join partners on parts.region = partners.region;
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.ValidationException: The options of following hints cannot match the name of input tables or views:
`parts` in `STATE_TTL`

I'm sure I've followed the docs in https://nightlies.apache.org/flink/flink-docs-release-1.19/docs/dev/table/sql/queries/hints/#state-ttl-hints but nothing I do seems to work. Are the docs wrong? Does sql-client not work? I'd be very grateful for any clues since I'm completely bewildered. Thank you!

If you require TTL behavior specifically for the duration of a query without altering the table definition, you might need to consider alternative strategies such as post-query filtering based on timestamps or implementing a custom retention mechanism in your data processing pipeline.

Try it

select /*+ STATE_TTL('parts'='5m') */ * from (select * from parts) parts left join partners on parts.region = partners.region;

@D. Draco O'Brien @Hải Nguyễn thank you - both of those are helpful (although

state.ttl

isn't a valid option on the filesystem connector, but that's fine.) Can either of you comment on whether the docs https://nightlies.apache.org/flink/flink-docs-release-1.19/docs/dev/table/sql/queries/hints/#state-ttl-hints are wrong, and should be corrected, or if it's in fact a bug in

org.apache.flink.table.planner.plan.optimize.QueryHintsResolver.getNewStateTtlHintOptions()

or thereabouts? Many thanks again for your help.

I also think what you had should work from what I can tell.

@D. Draco O'Brien your last suggestion fails with the same

org.apache.flink.table.api.ValidationException

, both in sql-client and a Java Table API-based version. Either the code, or the docs, or both would seem in error.

ok, let’s skip the rewrite. I think your code is ok. But what about sql client version?

I've tested sql-client 1.19.1 vs Flink 1.19.1, and Table API 1.19.1 and 1.20.0 against runtime 1.19.1 and 1.20.0 respectively.

How did you install Flink 1.19.1?

I'm running the relevant flink image under docker compose, and exec'ing into the container to run its sql-client. The jar files have not been modified.

ok ..

The quickest way to do that change is in my pure Java Table API version, which fails identically for Flink 1.19.0. I don't think it's ever worked as documented. I'll raise an issue. Thank you!

It does look like a valid issue at this point

I've raised https://issues.apache.org/jira/browse/FLINK-36238 to track this.