[FLINK-32447] table hints lost when they inside a view referenced by an external query

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.17.1
• Fix Version/s: 1.18.0, 1.17.2
• Component/s: Table SQL / Planner
• Labels: pull-request-available
• Assignee: lincoln lee
• Reporter: lincoln lee
• Created: 27/Jun/23 14:23
• Updated: 29/Jun/23 04:26
• Resolved: 28/Jun/23 04:27

Description

Table hints will lost when they inside a view referenced by an external query, this is due to the upgrading of calcite-1.28 (affected by CALCITE-4640 which changed the default implementation of SqlDialect suppresses all table hints).
This can be reproduced by adding a new case to current

OptionsHintTest

:

+
+  @Test
+  def testOptionsHintInsideView(): Unit = {
+    util.tableEnv.executeSql(
+      "create view v1 as select * from t1 /*+ OPTIONS(k1='#v111', k4='#v444')*/")
+    util.verifyExecPlan(s"""
+                           |select * from t2 join v1 on v1.a = t2.d
+                           |""".stripMargin)
+  }

wrong plan which lost table hints(dynamic options):

Join(joinType=[InnerJoin], where=[(a = d)], select=[d, e, f, a, b, c], leftInputSpec=[NoUniqueKey], rightInputSpec=[NoUniqueKey])
:- Exchange(distribution=[hash[d]])
:  +- LegacyTableSourceScan(table=[[default_catalog, default_database, t2, source: [OptionsTableSource(props={k3=v3, k4=v4})]]], fields=[d, e, f])
+- Exchange(distribution=[hash[a]])
   +- Calc(select=[a, b, (a + 1) AS c])
      +- LegacyTableSourceScan(table=[[default_catalog, default_database, t1, source: [OptionsTableSource(props={k1=v1, k2=v2})]]], fields=[a, b])

We should use

AnsiSqlDialect

instead to reserve table hints.

Issue Links

• links to: GitHub Pull Request #22881
• links to: GitHub Pull Request #22896