It may work as an "optimization fence" on Postgres but it is not a fence on Calcite nor do we intend it to be. (We don't have any optimization fences.) Vladimir Sitnikov was just trying something, and as it is a valid SQL query, it should have worked and not thrown an assertion error.
Your patch is a good start, but you also need to fix RelBuilder.sortLimit, and add a test to RelBuilderTest. That bug is present, and will be hit by people using the RelBuilder API if you don't fix it.
In SqlToRelConverter.convertOrder, convert an offset of 0 to null then I think the trivial sort will get skipped.