I wasn't sure why the logical plan wasn't correctly evaluated in this example.
I looked more deeply in the Flink base code and I checked that when calcite evaluate/estimate the number of rows for the query in object. For some reason it returns always 100 for any table source.
In Flink in fact, during the process of the program plan creation, for each transformed rule it is called the VolcanoPlanner class by the TableEnvironment.runVolcanoPlanner. The planner try to optimise and calculate some estimation by calling RelMetadataQuery.getRowCount
I reproduced the error by creating a failing test which should assert 0 as row count for relation table 'S' but it returns always 100.
Why this is happening? Does anyone has an answer to this issue?